Importing Existing Data
Prev | Chapter 7. Database Management | Next

## Importing Existing Data

Information about importing user data from other databases.

### Connecting the Tigase Server to MySQL Database

Please before continuing reading of this manual have a look at the initial MySQL database setup. It will help you with database preparation for connecting with Tigase server.

The easiest way to setup Tigase server for connecting with MySQL database is to use the configuration wizards (configuration generators) which release you from manually editing the XML configuration file and allow you quickly regenerate the XML configuration file in case of problems.

The article describes an older way for using configuration generators which is a bit more difficult and doesn’t work on Windows systems. The guide below describes a new way to use them which is simpler and can be applied to Windows systems as well. It is using the init.properties file where you can put all your initial configuration parameters.

This guide describes MySQL database connection parameters.

This guide is actually very short as there are example configuration files which can be used and customized for your environment.

Unfortunately these files are not included yet in the server version 3.x binary release and you have to download them from the SVN repository using following links:

1. tigase-mysql.conf - the Tigase server startup file. The only difference from the default one is that it points to the file described below to load initial parameters.

2. init-mysql.properties - the file contains a few initial parameters which can/should be adjusted to your environment. Here is a content of the file with each line described:

       # Load standard set of the server components.
       # Look at the http://www.tigase.org/configuration-wizards
       # document for other possible values. Normally you don't
       # need to change this line.
       config-type=--gen-config-def
       # List of administrator accounts, please replace them with
       # administrator accounts in your installation
       --admins=admin@tigase.org,admin@test-d
       # The line says that the database used by the Tigase server is 'mysql'
       # Look at the configuration wizards article for different options
       # You can also put here a Java class name if you have a custom
       # implementation for a database connector.
       --user-db=mysql
       # The line contains the database connection string. This is database
       # specific string and for each kind of database it may look differently.
       # Below string is for MySQL database. Please modify it for your system.
       # MySQL connector requires connection string in the following format:
       # jdbc:mysql://[hostname]/[database name]?user=[user name]&password=[user password]
       --user-db-uri=jdbc:mysql://localhost/tigasedb?user=tigase_user&password=mypass
       # Virtual domains for your server installation, comma separated list of vhosts
       --virt-hosts=tigase.org,test-d,localhost
       # Select what packages you want to have logging switched for
       # The below setting is recommended for the initail setup and it is required
       # when asking for help with setting the server up
       --debug=server

Download both files and put them to your etc/ directory.

Edit the init-mysql.properties for your environment.

Remove the XML configuration file.

Start the server using following command:

    ./bin/tigase.sh start etc/tigase-mysql.conf

Ask more questions if you got stuck or need any help with this.

### Integrating Tigase Server with Drupal

Tigase supports integration with Drupal on many levels. At the moment this guide can work with Drupal version 4.x and 5.x. They may also work with Drupal 6.x but this version hasn’t been tested.

First of all, Tigase can authenticate users against a Drupal database which means you have the same user account for both Drupal website and the XMPP server. Moreover in such a configuration all account management is done via Drupal web interface like account creation, password change update user details and so on. Administrator can temporarily disable user account and this is followed by Tigase server too.

#### Connecting to Drupal Database

The best way to setup Tigase with Drupal database is via configuration wizards that is init.properties file where you can put initial setting for Tigase configuration.

If you look in etc/ directory of your Tigase installation you should find a few files there. The one we want to base our configuration on is: init-mysql.properties. In some older packages the file might be missing, then you can download it from the SVN repository.

If you look inside the file and strip all the comments you would see following lines:

    config-type=--gen-config-def
    --admins=admin@tigase.org,admin@tigase.net
    --user-db=mysql
    --user-db-uri=jdbc:mysql://localhost/tigasedb?user=tigase_user&password=mypass
    --virt-hosts=tigase.org,tigase.net
    --debug=server

All you need to connect to Drupal database are 2 following lines:

    --auth-db=drupal
    --auth-db-uri=jdbc:mysql://localhost/drupal?user=drupalusr&password=drupalpass

Let’s combine it in a single file called etc/init-drupal.properties:

    config-type=--gen-config-def
    --admins=admin@tigase.org,admin@tigase.net
    --auth-db=drupal
    --auth-db-uri=jdbc:mysql://localhost/drupal?user=drupalusr&password=drupalpass
    --user-db=mysql
    --user-db-uri=jdbc:mysql://localhost/tigasedb?user=tigase_user&password=mypass
    --virt-hosts=tigase.org,tigase.net
    --debug=server

In theory you can load Tigase database schema to Drupal database and then both db-uris would have the same database connection string. More details about setting up and connecting to MySQL database can be found in the MySQL guide.

You need also to edit etc/tigase.conf file to point to your newly created properties file. The last line which looks like this:

    TIGASE_OPTIONS=" --property-file etc/init.properties "

should be changed to following:

    TIGASE_OPTIONS=" --property-file etc/init-drupal.properties "

Make sure you have Java-6 installed and JAVA_HOME is set to the correct location. If JAVA_HOME is not set you can add following line to etc/tigase.conf file:

    JAVA_HOME="/your/java-6-location"

The best way to check whether the variable is set correctly is with command:

    $ ls -l $JAVA_HOME/bin/java

should list you the file without error.

Check also if the logs/ directory is created in your Tigase server location as sometimes unpacking application doesn’t create empty directories.

Now when you have the configuration file and databases ready to use you can start the Tigase server with the following command:

    ./bin/tigase.sh start etc/tigase.conf

If you run the Tigase server installed from sources the command would be:

    ./scripts/tigase.sh start etc/tigase.conf

There are system startup scripts for Gentoo and Mandriva Linux on bin/ or scripts/ directory which can be used to automatically start the server when system starts.

Now you can register an account on your Drupal website and connect with an XMPP client using the account details.

Note! You have to enable plain password authentication in your XMPP client to connect to Tigase server with Drupal database

### Integrating Tigase Server With LibreSource

This document is still not finished.

Taken directly from LibreSource page:

LibreSource is a collaborative platform dedicated to both software development and community building. Based on Java/J2EE technology, LibreSource is a modular web server that users can customize online by combining resources and rights: wiki pages, forum, trackers, files, download areas, etc. All the tools are included and integrated.

#### Short Introduction

Integration between Tigase server and LibreSource is on a database level. It is implemented in the same way as integration with Drupal but with slightly more functions available.

Basically LibreSource system maintains own database with all it’s data and Tigase connects to LibreSource database to authenticate users. All user data specific to XMPP service are kept in separate tables which can be located in the same database as LibreSource data or in different database.

Current list of features included in the integration:

- XMPP users authentication against user data stored in LibreSource database.
- Recording XMPP user on-line status in LibreSource database. This can be displayed on the Web page as additional user info.
- Recording user last login time and this information can also be available on Web page.
- Checking user account status. So if the user account is disabled in LibreSource system then this user will not be able to login to XMPP service too.
- User account creation. This feature might be useful during testing time or user data transition from an old Tigase installation to LibreSource system. Please note! This feature should be normally disabled on live system. All user account management should be done from LibreSource system because of data caching.
- User account deletion. Please note! This feature should be normally disabled on live system. All user account management should be done from LibreSource system because of data caching.

A few assumptions:

1. LibreSource data are kept in PostgreSQL database - libresource and database user is demo.
2. For use in cases where Tigase data are stored in MySQL database name is tigase, database user is dbuser and database user password is dbpass

#### How to set Tigase up

Now we will focus on setting things up to have both services up and running together. Below is example of the most complex environment to run where LibreSource is using PostgreSQL database and Tigase is using a MySQL database. All basic user data needed for authentication is kept by LibreSource, so Tigase has to connect to PostgreSQL database also in order to authenticate users.

1. First you need LibreSource system up and running. Please refer to LS documentation for details.
2. Install Tigase server in the normal way including loading Tigase database schema. Database tables used by Tigase server use different naming convention so you can simply load Tigase DB schema to the same database as LibreSource data. It is also possible and recommended to keep Tigase data in separate database.

3. Using configuration wizards generate configuration for Tigase server to connect to LibreSource database. Here is the sample file with parameters for configuration wizard assuming following setup:

   - LibreSource data are kept in PostgreSQL database: libresource, user: demo
   - Tigase data are kept in MySQL database: tigase, user: dbuser, password: dbpass
   - No external components are connected to Tigase server

   - Tigase works for domain: domain.net

         ENC="-Dfile.encoding=UTF-8 -Dsun.jnu.encoding=UTF-8"
         DRV="-Djdbc.drivers=com.mysql.jdbc.Driver:org.postgresql.Driver"

         JAVA_OPTIONS="${ENC} ${DRV} -server -Xms100M -Xmx100M "
         TIGASE_CONFIG="etc/tigase-mysql-libresource.xml"
         TIGASE_OPTIONS="--gen-config-def \
           --user-db mysql \
           --user-db-uri jdbc:mysql://localhost/tigase?user=dbuser&password=dbpass&autoCreateUser=true \
           --auth-db libresource \
           --auth-db-uri jdbc:postgresql://localhost/libresource?user=demo \
           --virt-hosts domain.net,localhost "

4. A simpler example where all data (LibreSource and Tigase) are stored in the same database:

   - LibreSource data are kept in PostgreSQL database: libresource, user: demo
   - Tigase data are kept also in PostgreSQL database: libresource, user: demo
   - No external components are connected to Tigase server

   - Tigase works for domain: domain.net

         ENC="-Dfile.encoding=UTF-8 -Dsun.jnu.encoding=UTF-8"
         DRV="-Djdbc.drivers=com.mysql.jdbc.Driver:org.postgresql.Driver"

         JAVA_OPTIONS="${ENC} ${DRV} -server -Xms100M -Xmx100M "
         TIGASE_CONFIG="etc/tigase-mysql-libresource.xml"
         TIGASE_OPTIONS="--gen-config-def \
           --user-db pgsql \
           --user-db-uri jdbc:postgresql://localhost/libresource?user=demo&autoCreateUser=true \
           --auth-db libresource \
           --auth-db-uri jdbc:postgresql://localhost/libresource?user=demo \
           --virt-hosts domain.net,localhost "

Now, you can run Tigase as normal and it all works.

Note! You have to load Tigase database schema for user data. Please refer to guide for specific database: MySQL or PostgreSQL.

#### Migration From an old Tigase Installation to LibreSource

Tigase package includes additional tools to make it easier to manage and control you installation. One is used to change configuration settings - config.sh and another is used to manipulate user data in repository - repo.sh.

Depending on whether you use Tigase version built from sources or binary version these scripts might be available in either scripts/ or bin/ subdirectories. To make things simpler let’s assume they are stored in scripts/ directory.

Assuming you have an old Tigase server installation with number of users in MySQL database and you want to migrate all of them to LibreSource there are 2 steps involved:

1. User data migration
2. Changing your existing configuration

##### Data Migration

First we need to migrate user data used for authentication. That data will be used by both services: LibreSource and Tigase and they normally stored in LibreSource database. Therefore we have to use LibreSource database connector to handle the data (write or read). Tigase server will be using LibreSource database for reading only but during migration time we need to write user accounts to LS database. Sample command to migrate user accounts looks like this:

    ./scripts/repo.sh -sc tigase.db.jdbc.JDBCRepository \
      -su "jdbc:mysql://localhost/tigase?user=dbuser&password=dbpass" \
      -dc tigase.db.jdbc.LibreSourceAuth \
      -du "jdbc:postgresql://localhost/libresource?user=demo" \
      -cp

The above command will copy all user accounts from MySQL tigase database to libresource database. Please refer to repository management tool documentation for information how to migrate single or selected user accounts.

If you want to keep all Tigase server data in the same database you have to copy also all other user data like rosters, vCards and so on.

First thing we have to do is load the database schema for Tigase data. Because Tigase tables have distinct names from LibreSource, there is no danger for any conflict. As in the above example let’s assume LibreSource’s data is stored in libresource database and database user name is demo:

    psql -q -U demo -d libresource -f database/postgresql-schema.sql

Now we can load and transfer all user data from MySQL database to LibreSource:

    ./scripts/repo.sh -sc tigase.db.jdbc.JDBCRepository \
      -su "jdbc:mysql://localhost/tigase?user=dbuser&password=dbpass" \
      -dc tigase.db.jdbc.JDBCRepository \
      -du "jdbc:postgresql://localhost/libresource?user=demo" \
      -cp

This command looks siumilar to the previous one. Just a Java class used for handling destination database is different.

### MySQL Database Use

This guide describes how to configure Tigase server to use MySQL database as a user repository.

If you used an XML based user repository before you can copy all user data to MySQL database using repository management tool. All steps are described below.

#### MySQL Database Preparation

To load db schema to your MySQL instance first create database:

    mysqladmin -p create tigase

And then you can load database schema:

    mysql -u dbuser -p tigase < mysql-schema.sql

#### Server Configuration

Now you have to change configuration to load a jdbc module instead of XML based repository. Using configuration management script, first change class name handling repository.

To see current settings run command:

    $ ./scripts/config.sh -c tigase-config.xml -print -key session_1/user-repo-class

As a result you should see something like:

    session_1/user-repo-class = tigase.db.xml.XMLRepository

You can see that current setting points to the XML repository implementation. To use jdbc module for connecting to MySQL database you have to set tigase.db.jdbc.JDBCRepository class (enter text below in one line):

    $ ./scripts/config.sh -c tigase-config.xml -print -key session_1/user-repo-class -value tigase.db.jdbc.JDBCRepository -set

As a result you will see new value set for the parameter:

    session_1/user-repo-class = tigase.db.jdbc.JDBCRepository

You have also to set the same value as authorization repository unless you want to use different authorization data source:

     $ ./scripts/config.sh -c tigase-config.xml -print -key session_1/auth-repo-class -value tigase.db.jdbc.JDBCRepository -set

And again as a result we can see:

    session_1/auth-repo-class = tigase.db.jdbc.JDBCRepository

Next step is to set the database connection string. Assuming you have database: tigase on localhost with database user: dbuser and password dbpass your connection string will look like this:

    jdbc:mysql://localhost/tigase?user=dbuser&password=dbpass

To set this in your configuration file, you have to the configuration management script 2 times. First for user data repository and second for authorization data repository:

    $ ./scripts/config.sh -c tigase-config.xml -print -key session_1/user-repo-url -value "jdbc:mysql://localhost/tigase?user=dbuser&password=dbpass" -set
    $ ./scripts/config.sh -c tigase-config.xml -print -key session_1/auth-repo-url -value "jdbc:mysql://localhost/tigase?user=dbuser&password=dbpass" -set

Note quotes around connection string. They are needed to make sure the shell won’t interpret special characters.

Now the configuration is ready to load the jdbc module and connect to your database.

One more thing you need to do is to tell JVM which jdbc driver to use to connect to database. Depending on your MySQL and jdbc installation it might be: com.mysql.jdbc.Driver. To set is as database driver you have to set is a jdbc.drivers property value. Usually you do this by adding -D parameter to Java call:

     $ java -Djdbc.drivers=com.mysql.jdbc.Driver tigase.server.XMPPServer

If you use tigase.sh script to run server you will have to add -Djdbc.drivers=com.mysql.jdbc.Driver to the startup script init.Properties property file to JAVA_OPTIONS values.

#### User Data Import

If you previously used an XML based user repository, you can import all data into a MySQL database using repository management tool. This is quite long command so let me list all required parameters first with brief explanation:

1. -cp copy content of the source repository to destination repository.
2. -sc tigase.db.xml.XMLRepository source repository class.
3. -su user-repository.xml source repository connection string - assuming your user repository is in user-repository.xml file.
4. -dc tigase.db.jdbc.JDBCRepository destination repository class.
5. -du "jdbc:mysql://localhost/tigase?user=dbuser&password=dbpass" destination repository connection string.

And now whole command. Enter all in one line:

    $ ./scripts/repo.sh -cp -sc tigase.db.xml.XMLRepository -su user-repository.xml -dc tigase.db.jdbc.JDBCRepository -du "jdbc:mysql://localhost/tigase?user=dbuser&password=dbpass"

For more information how to use command line administration tools refer to command line tools guide.

### PostgreSQL Database Use

This guide describes how to configure Tigase server to use PostgreSQL database as a user repository.

If you used an XML based user repository before you can copy all user data to PostgreSQL database using repository management tool. All steps are described below.

#### PostgreSQL Database Preparation

Create new database user account which will be used to connect to your database:

    # createuser
    Enter name of user to add: tigase
    Shall the new user be allowed to create databases? (y/n) y
    Shall the new user be allowed to create more new users? (y/n) y

Now using new database user account create database for your service:

    # createdb -U tigase tigasedb
    CREATE DATABASE

Now you can load the database schema:

    # psql -U tigase -d tigasedb -f postgresql-schema.sql

Now the database is ready for Tigase server to use.

#### Server Configuration

Server configuration is identical as MySQL database setup. The same jdbc module is used to connect to PostgreSQL database as for MySQL. The only difference is the connection string which usually looks like:

    jdbc:postgresql://localhost/tigasdb?user=tigase

So for more detailed guide how to change configuration refer to MySQL database use guide or if you look for a more automatic configuration file generation refer to configuration wizards page.

Prev | Up | Next
Importing User Data | Home | Schema Updates