Overview of technologies used so far

There are a number of application that work well on there own and are great for the purpose they are designed for. However everything links together some how and understanding how applications work on their own is only the start of things.

Whilst I have been learning about a number new applications, I have also been looking at how applications can work with each other to give an end to end feel of how certain Internet businesses or back end technologies really work.

There are hundreds or some times thousands of different way in which applications can link with each other within a business or over the Internet. Whether it be using a different storage database at the back end or it be writing a front end client in a different language, I have been documenting a number of working solutions already in my blog http://vidorsolutions.com

Some of the technologies I have learnt and experimented successfully with are as follows:

1. Grails application deployed into Glassfish application server, using PostgreSQL as a database. Then using tools such as PGAdmin to connect to my databases locally on my machine.

2. Ejabberd Instant Messaging server installed on a server instance in the cloud, configured to use postgreSQL as a data source. Then using PSI Desktop chat client to connect with Ejabberd account. Tools such as PGAdmin can also be used here to view the back end database, tables and structure.

To further extend this learning experience I then created a basic Android XMPP client application that could also connect to the Ejabberd Server instance.

A lot of different technologies going on here and a lot to learn about the applications alone if you've never used any of these. However once you see how all these work together you get a greater appreciation of how the end to end user experience works.

Remember for some instructions on how to use these check out my previous posts
http://vidorsolutions.com

Using PostgreSQL database driver within your Grails applications

By default Grails uses an in-memory database that stores data for your apps within volatile RAM. You can leave this as it is but most programmers like to know that their database is more peristent.

In order to use your own data sources you need to be looking at the DataSource.groovy file within /grails-app/conf directory. Here you specify the database that your application will use.

There are three sections within this file so you can specify different data sources for different purposes: production, test and development. In the example I will demonstrate using a different PostgreSQL database for each purpose however you can use three entirely different products if you wish.


Before you start configuring anything in the DataSource.groovy file ensure that you have completed the following:

1. Ensure PostgreSQL is installed


2. Download PostgreSQL JDBC driver and save it into your applications lib folder.

3. Ensure you have a PostgreSQL user to use for the database connection. If you do not wish to use default user, create new user with createuser command.


4. Create the relevant databases in PostgreSQL that your grails application will use. This can be done simply though PGAdmin or using createdb commands as the PostgreSQL user.

5. Return to the Datasource.groovy config file and use the following settings:

dataSource {
        pooled = true
        driverClassName = "org.postgresql.Driver"
        username = "postgres"
        password = "password"
}
hibernate {
    cache.use_second_level_cache=true
    cache.use_query_cache=true
    cache.provider_class='org.hibernate.cache.EhCacheProvider'
}
// environment specific settings
environments {
        development {
                dataSource {
                        dbCreate = "update" // one of 'create', 'create-drop','update'
                        url = "jdbc:postgresql://localhost:5432/devdb"
                }
        }
        test {
                dataSource {
                        dbCreate = "update"
                        url = "jdbc:postgresql://localhost:5432/testdb"
                }
        }
        production {
                dataSource {
                        dbCreate = "update"
                        url = "jdbc:postgresql://localhost:5432/proddb"
                }
        }
}


Please note that the following are examples of the different database purposes:

• Development db is when you use grails run-app to launch you application
• Test db is when you run grails unit and integration tests
• Production db is used when you upload the application to an app server such as glassfish etc.

Now run you application in whatever environment you wish and view data in your own PostgreSQL databases. If you not a SQL savey then learn how to use PGAdmin to view tables and database structure.

Configure Ejabberd chat server to use PostgreSQL ODBC

In order to make data persistent with in any application you can link it to a PostgreSQL relational database.
As default Ejabberd messaging server uses it's internal Mnesia database.

1. To change the default to use a postgres database we first need to create a new postgresql database on the server (this assumes you have PgSQL installed and configured)


run ./createdb ejabberd

2. Create the tables using the supplied script

./psql ejabberd < /opt/ejabberd-2.1.5/lib/ejabberd-2.1.5/priv/odbc/pg.sql

3. Add a user for the database

./createuser ejabberd

4. Configure ODBC options in ejabberd.cfg:

Scroll down to the section headed Database setup.

edit the following, remove the %% commenting


{odbc_server, {pgsql, "127.0.0.1", "ejabberd", "ejabberd", "yourpassword"}}.
5. Add _odbc to modules you wish to use the odbc database

e.g. mod_offline_odbc instead of mod_offline.

full list in ejabberd user guide

6. Under authentication comment out internal authentication :

%%{auth_method, internal}.

Then un comment {auth_method, odbc}.

7. Register a new user and appear online using PSI client or similar application

8. Add a user to allowed admin access control list in ejabberd.cfg


{acl, admin, {user, "username", "server"}}.

9. Confirm registered user and status in web admin:


http://serverIP:5280/admin/

10. Connect to database using PgAdmin to view tables with data and confirm ejabberd is now using PostgreSQL

Initial setup and install of PostgreSQL Database and connecting using pgadmin

PostgreSQL is the worlds most advanced open source relational database. PgSQL can be used as a standard database in its own right or can be used to store the data from a number of applications.
In this post I will explain how to install and configure PgSQL. Then go onto to explain how to connect to the data using PGAdmin.


Installing PostgreSQL:

 1. Download source code from http://www.postgresql.org/ftp/source/
2. Extract the downloaded contents: tar -xzvf postgresql-12... .tar.gz
3. ./configure , make, make install. (this may have to be sudo..)
4. create a postgres user : adduser postgres
5. log in as postgres user and initialise the database 
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data


This will initilise the database then you can start it any time using:


/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data
or
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l start

6. From in the bin, file create Database: ./createdb test


7. Access Database ./psql test

Use sql commands to add tables / data etc.

\q to quit

You now have a working database you can access.


Access the database from PgAdmin

To access the databases you have from a gui where you can see how the tables are structured and the data within them, pgadmin is a great opensource management tool.

1. Install pgadmin on your local machine by your preferred method, either source or packages. Source downloadable at  http://www.pgadmin.org/download/source.php .
app-get is also an easy install.

2. From the server side configure pg_hba.conf : add trusted hosts. The easiest thing to do is trust local host
host    all         all         192.168.0.0/24        md5


3. Then from your local machine port forward to your server


ssh postgres@serverIP -L 5432:localhost:5432

4. Launch pgadmin, add server. Connect to local host, rather than server IP

5. Manage all postgres databases / tables / data when connected.