Database Preparation
Prev | Chapter 9. Database Management | Next

## Database Preparation

Tigase uses generally the same database schema and the same set of stored procedures and functions on every database. However, the schema creation scripts and code for stored procedures is different for each database. Therefore the manual process to prepare database is different for each database system.

Starting with v8.0.0, most of the database tasks have been automated and can be called using simple text, or using interactive question and answer style. We DO NOT RECOMMEND going through manual operation, however we have kept manual activation of different databases to the Appendix. If you are interested in how we manage and update our database schemas, you may visit the the section called “Schema files maintenance” section of our Redmine installation for more detailed information.

- The DBSchemaLoader Utility
- Hashed User Passwords in Database
- Support for MongoDB

Appendix entries

- Manual installtion for MySQL
- Manual installtion for Derby
- Manual installtion for SQLServer
- Manual installtion for PostGRESQL

### Schema Utility

With the release of v8.0.0 calling the Tigase dbSchemaLoader utility now can be done using tasks instead of calling the specific method. Support for Derby, MySQL, PostgreSQL, MSSQL, and MongoDB is available.

In order to use this utility with any of the databases, you will need to first have the database environment up and running, and have established user credentials. You may use root or an account with administrator write privileges.

#### Operation & Variables

##### Operation

Operating the schema utility is quite easy! To use it run this command from the installation directory:

    ./scripts/tigase.sh [task] [params_file.conf] [options]

Operations are now converted to tasks, of which there are now three: `install-schema`, `upgrade-schema`, and `destroy-schema`.

- `upgrade-schema`: Upgrade the schema of the database specified in your `config.tdsl` configuration file. (options are ignored for this option)
- `install-schema`: Install a schema to database.
- `destroy-schema`: Destroy database and schemas. DANGEROUS

##### Options

Use the following options to customize. Options in bold are required, {potential options are in brackets}:

- `--help` Prints the help for the task.
- `-I` or `--interactive` - enables interactive mode which will prompt for parameters not defined.
- `-T` or `--dbType` - database type {derby, mongodb, mysql, postgresql, sqlserver}.
- `-C` or `--components` - Allows the specification of components for use when installing a schema.

#### Usage

##### upgrade-schema

This task will locate any schema versions above your current one, and will install them to the database configured in the `config.tdsl` file.

### Note

To use this utility, you must have Tigase XMPP server fully setup with a configured configuration file.

    ./scripts/tigase.sh upgrade-schema etc/tigase.conf

Windows users will need to run the command using the following command:

    java -cp "jars/*" tigase.db.util.SchemaManager "upgrade-schema" --config-file=etc/config.tdsl

##### install-schema

This task will install a schema using the parameters provided.

If you are setting up a server manually, we HIGHLY recommend using this method

    ./scripts/tigase.sh install-schema [Options]

This command will install tigase using a Derby database on one named `tigasedb` hosted on `localhost`. The username and password editing the database is `tigase_pass` and `root`. Note that `-J` explicitly adds the administrator, this is highly recommended with the `-N` passing the password.

If you are using a windows system, you need to call the program directly:

    java -cp "jars/*" tigase.db.util.SchemaManager "install-schema" [options]

###### Options

Options for schema installation are as follows, required options are in bold

- `--help`, Outputs the help.
- `-I`, `--interactive` - enables interactive mode, which will result in prompting for any missing parameters.
- `-C`, `--components=` - list of enabled components identifiers (+/-), possible values: [`amp`, `bosh`, `c2s`, `eventbus`, `ext-disco`, `http`, `mdns`, `message-archive`, `monitor`, `muc`, `pubsub`, `push`, `s2s`, `socks5`, `test`, `unified-archive`, `upload`, `ws2s`] (default: amp,bosh,c2s,eventbus,http,message-archive,monitor,muc,pubsub,s2s,ws2s). This is required for certain components like socks5.
- `-T`, `--dbType=` - database server type, possible values are: [`derby`, `mongodb`, `mysql`, `postgresql`, `sqlserver`] (required)
- `-D`, `--dbName=` - name of the database that will be created (by default it is `tigasedb`). (required)
- `-H`, `--dbHostname=` - address of the database instance (by default it is `localhost`). (required)
- `-U`, `--dbUser=` - name of the user that will be created specifically to access Tigase XMPP Server database (default is `tigase_user`). (required)
- `-P`, `--dbPass=` - password of the user that will be created specifically to access Tigase XMPP Server database (default is `tigase_pass`). (required)
- `-R`, `--rootUser=` - database root account username used to create user and database (default is `root`). (required)
- `-A`, `--rootPass=` - database root account password used to create user and database (default is `root`). (required)
- `-S`, `--useSSL` - enable SSL support for database connection (if the database supports it) (default is false).
- `-F`, `--file=` - comma separated list of SQL files that will be processed.
- `-Q`, `--query=` - custom queries to be executed, see the section called “Query function” for details.
- `-L`, `--logLevel=` - logger level used during loading process (default is `CONFIG`).
- `-J`, `--adminJID=` - comma separated list of administrator JID(s).
- `-N`, `--adminJIDpass=` - password that will be used for the entered JID(s) - one password for all configured JIDs.
- `--getURI=` - generate database URI (default is `false`).
- `--ignoreMissingFiles=` - force ignoring missing files errors (default is `false`).

###### Query function

Should you decide to customize your own functions, or have specific information you want to put into the database, you can use the -query function to perform a single query step.

    ./scripts/tigase.sh install-schema -T mysql -D tigasedb -R root -A root -Q "CREATE TABLE tigasedb.EXTRA_TABLE (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(10) NOT NULL)"

Of course this would break the schema for tigasedb by adding an unexpected table, you will receive the following message:

    tigase.db.util.DBSchemaLoader       printInfo          WARNING       Database schema is invalid

But this is a demonstration how you may run a query through the database without the need to use another tool. Note that you will need to select the specific database for each query.

##### destroy-schema

This will destroy the database specified in the configuration file.

### Warning

THIS ACTION IS NOT REVERSIBLE

    ./scripts/tigase.sh destroy-schema etc/config.tdsl

Only use this if you wish to destroy a database and not have the information recoverable.

Windows users will need to call the method directly:

    java -cp "jars/*" tigase.db.util.SchemaManager "destroy-schema" etc/config.tdsl

##### A note about MySQL

If you are using these commands, you may result in the following error:

    tigase.util.DBSchemaLoader       validateDBConnection    WARNING    Table 'performance_schema.session_variables' does not exist

If this occurs, you will need to upgrade your version of MySQL using the following command:

    mysql_upgrade -u root -p --force

After entering the password and upgrading MySQL the schema error should no longer show when working with Tigase databases.

### Prepare the MySQL Database for the Tigase Server

This guide describes how to prepare MySQL database for connecting Tigase server.

The MySQL database can be prepared in many ways. Most Linux distributions contain tools which allow you to go through all steps from the shell command line. To make sure it works on all platforms in the same way, we will first show how to do it under MySQL command line client.

#### Configuring from MySQL command line tool

Run the MySQL command line client in either Linux or MS Windows environment and enter following instructions from the Tigase installation directory:

    mysql -u root -p

Once logged in, create the database for the Tigase server:

    mysql> create database tigasedb;

Add the `tigase_user` user and grant him access to the `tigasedb` database. Depending on how you plan to connect to the database (locally or over the network) use one of following commands or all if you are not sure:

- Grant access to tigase_user connecting from any network address.

      mysql> GRANT ALL ON tigasedb.* TO tigase_user@'%'
                  IDENTIFIED BY 'tigase_passwd';

- Grant access to tigase_user connecting from localhost.

      mysql> GRANT ALL ON tigasedb.* TO tigase_user@'localhost'
                  IDENTIFIED BY 'tigase_passwd';

- Grant access to tigase_user connecting from local machine only.

      mysql> GRANT ALL ON tigasedb.* TO tigase_user
                  IDENTIFIED BY 'tigase_passwd';

And now you can update user permission changes in the database:

    mysql> FLUSH PRIVILEGES;

### Important

It’s essential to enable log_bin_trust_function_creators option in MySQL server, for example by running:

    mysql> SET GLOBAL log_bin_trust_function_creators = 1;

##### Installing Schemas

Starting with v8.0.0 the Schemas are no longer linked, and will need to manually be installed in the following order.

Switch to the database you have created:

    mysql> use tigasedb;

### Note

We are assuming you run the mysql client in Linux from the Tigase installation directory, so all file links will be relative.

Next install the schema files:

    mysql> source database/mysql-common-0.0.1.sql;

You will need to repeat this process for the following files:

    mysql-common-0.0.1.sql
    mysql-common-0.0.2.sql
    mysql-server-7.0.0.sql
    mysql-server-7.1.0.sql
    mysql-server-8.0.0.sql
    mysql-muc-3.0.0.sql
    mysql-pubsub-3.1.0.sql
    mysql-pubsub-3.2.0.sql
    mysql-pubsub-4.0.0.sql
    mysql-http-api-2.0.0.sql

Other components may require installation such as:

    mysql-socks5-2.0.0.sql
    mysql-push-1.0.0.sql
    mysql-message-archiving-2.0.0.sql
    mysql-unified-archive-2.0.0.sql

###### Windows instructions:

On Windows you have probably to enter the full path, assuming Tigase is installed in C:\Program Files\Tigase:

    mysql> source c:/Program Files/Tigase/database/mysql-common-0.0.1.sql;
    mysql> source c:/Program Files/Tigase/database/mysql-common-0.0.2.sql;
    mysql> source c:/Program Files/Tigase/database/mysql-server-7.0.0.sql;
    and so on...

#### Configuring From the Linux Shell Command Line

Follow steps below to prepare the MySQL database:

Create the database space for the Tigase server:

    mysqladmin -p create tigasedb

Add the `tigase_user` user and grant access to the tigasedb database. Depending on how you plan to connect to the database (locally or over the network) use one of following commands or all if you are not sure:

##### Selective access configuration

###### Grant access to tigase_user connecting from any network address.

    echo "GRANT ALL ON tigasedb.* TO tigase_user@'%' \
                IDENTIFIED BY 'tigase_passwd'; \
                FLUSH PRIVILEGES;" | mysql -u root -pdbpass mysql

###### Grant access to tigase_user connecting from localhost.

    echo "GRANT ALL ON tigasedb.* TO tigase_user@'localhost' \
                IDENTIFIED BY 'tigase_passwd'; \
                FLUSH PRIVILEGES;" | mysql -u root -pdbpass mysql

###### Grant access to tigase_user connecting from local machine only.

    echo "GRANT ALL ON tigasedb.* TO tigase_user \
                IDENTIFIED BY 'tigase_passwd'; \
                FLUSH PRIVILEGES;" | mysql -u root -pdbpass mysql

##### Schema Installation

Load the proper mysql schemas into the database.

    mysql -u dbuser -p tigasedb < mysql-common-0.0.1.sql
    mysql -u dbuser -p tigasedb < mysql-common-0.0.2.sql
    etc..

You will need to repeat this process for the following files:

    mysql-common-0.0.1.sql
    mysql-common-0.0.2.sql
    mysql-server-7.0.0.sql
    mysql-server-7.1.0.sql
    mysql-server-8.0.0.sql
    mysql-muc-3.0.0.sql
    mysql-pubsub-3.1.0.sql
    mysql-pubsub-3.2.0.sql
    mysql-pubsub-4.0.0.sql
    mysql-http-api-2.0.0.sql

Other components may require installation such as:

    mysql-socks5-2.0.0.sql
    mysql-push-1.0.0.sql
    mysql-message-archiving-2.0.0.sql
    mysql-unified-archive-2.0.0.sql

#### Configuring MySQL for UTF-8 Support

In my.conf put following lines:

    [mysql]
    default-character-SET=utf8

    [client]
    default-character-SET=utf8

    [mysqld]
    init_connect='SET collation_connection = utf8_general_ci; SET NAMES utf8;'
    character-set-server=utf8
    default-character-SET=utf8
    collation-server=utf8_general_ci
    skip-character-set-client-handshake

Then connect to the database from the command line shell check settings:

    SHOW VARIABLES LIKE 'character_set_database';
    SHOW VARIABLES LIKE 'character_set_client';

If any of these shows something else then 'utf8' then you need to fix it using the command:

    ALTER DATABASE tigasedb DEFAULT CHARACTER SET utf8;

You can now also test your database installation if it accepts UTF-8 data. The easiest way to ensure this is to just to create an account with UTF-8 characters:

    call TigAddUserPlainPw('żółw@some.domain.com', 'żółw');

And then check that the account has been created:

    SELECT * FROM tig_users WHERE user_id = 'żółw@some.domain.com';

If the last command gives you no results it means there is still something wrong with your settings. You might also want to check your shell settings to make sure your command line shell supports UTF-8 characters and passes them correctly to MySQL:

    export LANG=en_US.UTF-8
    export LOCALE=UTF-8
    export LESSCHARSET='utf-8'

It seems that MySQL 5.0.x also needs extra parameters in the connection string: '&useUnicode=true&characterEncoding=UTF-8' while MySQL 5.1.x seems to not need it but it doesn’t hurt to have it for both versions. You have to edit `etc/config.tdsl` file and append this to the database connection string.

For MySQL 5.1.x, however, you need to also update code for all database stored procedures and functions used by the Tigase. They are updated for Tigase version 4.4.x and up, however if you use an older version of the Tigase server, you can reload stored procedures using the file from SVN.

#### Other MySQL Settings Worth Considering

There are a number of other useful options, especially for performance improvements. Please note, you will have to review them as some of them may impact data reliability and are useful for performance or load tests installations only.

    # InnoDB seems to be a better choice
    # so lets make it a default DB engine
    default-storage-engine = innodb

Some the general MySQL settings which mainly affect performance:

    key_buffer = 64M
    max_allowed_packet = 32M
    sort_buffer_size = 64M
    net_buffer_length = 64K
    read_buffer_size = 16M
    read_rnd_buffer_size = 16M
    thread_stack = 192K
    thread_cache_size = 8
    query_cache_limit = 10M
    query_cache_size = 64M

InnoDB specific settings:

    # Keep data in a separate file for each table
    innodb_file_per_table = 1
    # Allocate memory for data buffers
    innodb_buffer_pool_size = 1000M
    innodb_additional_mem_pool_size = 100M
    # A location of the MySQL database
    innodb_data_home_dir = /home/databases/mysql/
    innodb_log_group_home_dir = /home/databases/mysql/
    # The main thing here is the 'autoextend' property
    # without it your data file may reach maximum size and
    # no more records can be added to the table.
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_log_file_size = 10M
    innodb_log_buffer_size = 32M
    # Some other performance affecting settings
    innodb_flush_log_at_trx_commit = 2
    innodb_lock_wait_timeout = 50
    innodb_thread_concurrency = 16

These settings may not be fully optimized for your system, and have been only tested on our systems. If you have found better settings for your systems, feel free to let us know.

##### Support for emoji and other icons

Tigase Database Schema can support emojis and other icons, however by using UTF-8 in `mysqld` settings will not allow this. To employ settings to support emojis and other icons, we recommend you use the following in your MySQL configuration file:

    [mysqld]
    character-set-server = utf8mb4
    collation-server = utf8mb4_bin
    character-set-client-handshake = FALSE

Doing this, Tigase XMPP Server Database will still use `utf8` character set, with `utf8_general_ci` as collation, and only fields which require support for emojis will be converted to `utf8mb4`.

### Note

If for some reason, with above settings applied to your MySQL instance, you still receive `java.sql.SQLException: Incorrect string value: ` you should add to your database URI passed in Tigase XMPP Server following configuration `&useUnicode=true&characterEncoding=UTF-8`. If even this fails too, then you may try adding `&connectionCollation=utf8mb4_bin` as a last resort. This changes situation from previous versions that shipped older MySQL JDBC connector.

### Note

Tigase XMPP Server databases should be created with `utf8_general_ci` collation as it will work properly and is fastest from `utf8mb4_general_ci` collations supported by MySQL

### Prepare the Derby Database for the Tigase Server

This guide describes how to prepare Derby database for connecting the Tigase server.

#### Basic Setup

Preparation of Derby database is quite simple, but the following assumptions are made

- `DerbyDB` - Derby database name
- `database/` directory contains all necessary schema files
- `jars/` and `libs/` directories contains Tigase and Derby binaries

##### General Approach

From the main Tigase directory execute following commands (Linux and Windows accordingly)

### Note

You must use these sql files on order FIRST!

Linux

    java -Dij.protocol=jdbc:derby: -Dij.database="DerbyDB;create=true" -cp libs/derby.jar:libs/derbytools.jar:jars/tigase-server.jar org.apache.derby.tools.ij database/derby-common-0.0.1.sql

Windows

    java -Dij.protocol=jdbc:derby: -Dij.database="DerbyDB;create=true" -cp libs\derby.jar;libs\derbytools.jar;jars\tigase-server.jar org.apache.derby.tools.ij "database\derby-common-0.0.1.sql"

This will create Derby database named DerbyDB in the main Tigase directory and load common version for common v0.1.

You will need to repeat this process again in for following order:

    derby-common-0.0.1.sql
    derby-common-0.0.2.sql
    derby-server-7.0.0.sql
    derby-server-7.1.0.sql
    derby-server-8.0.0.sql
    derby-muc-3.0.0.sql
    derby-pubsub-3.1.0.sql
    derby-pubsub-3.2.0.sql
    derby-pubsub-4.0.0.sql
    derby-http-api-2.0.0.sql

Other components may require installation such as:

    derby-socks5-2.0.0.sql
    derby-push-1.0.0.sql
    derby-unified-archive-2.0.0.sql

#### Connecting Tigase to database

Once the database is setup, configure the `config.tdsl` file in Tigase and add the following configuration:

    dataSource {
        default () {
            uri = 'jdbc:derby:{location of derby database};'
        }
    }

### Prepare the MS SQL Server Database for the Tigase Server

This guide describes how to prepare the MS SQL Server database for connecting the Tigase server to it.

It’s expected that a working installation of Microsoft SQL Server is present. The following guide will describe the necessary configurations required for using MS SQL Server with Tigase XMPP Server.

#### Preparing MS SQL Server Instance

After installation of MS SQL Server an instance needs to be configure to handle incoming JDBC connections. For that purpose it’s required to open SQL Server Configuration Manager. In the left-hand side panel navigate to SQL Server Configuration Manager, then SQL Server Network Configuration → Protocols for ${INSTANCE_NAME}. After selecting instance in the right-hand side panel select TCP/IP and open Properties, in the Protocol tab in General section select Yes for Enabled property. In the IP Addresses tab select Yes for Active and Enabled properties of all IP Addresses that you want MS SQL Server to handle. Subsequently set the TCP Port property (if missing) to the default value - 1433. A restart of the instance may be required afterwards.

#### Configuration using MS SQL Server Management Studio

In order to prepare the database you can use either a wizard or execute queries directly in the Query Editor. Firstly you need to establish a connection to the MS SQL Server instance. From Object Explorer select Connect and in the Connect to Server dialog enter administrator credentials.

##### Using Wizards

- Create Login

  In the left-hand side panel select Security → Logins and from context menu choose New Login, in the Wizard window enter desired Login name, select SQL Server authentication and enter desired password subsequently confirming action with OK

- Create Database

  From the Object Explorer select Databases node and from context menu select New Database; in the Wizard window enter desired Database name and enter previously created Login name into Owner field; subsequently confirming action with OK.

##### Using Queries

From the Object Explorer root node’s context menu select New Query. In the Query windows execute following statements adjusting details to your liking:

    USE [master]
    GO

    CREATE DATABASE [tigasedb];
    GO

    CREATE LOGIN [tigase] WITH PASSWORD=N'tigase12', DEFAULT_DATABASE=[tigasedb]
    GO

    ALTER AUTHORIZATION ON DATABASE::tigasedb TO tigase;
    GO

#### Import Schema

From the File menu Select Open → File (or use Ctrl+O) and then open following files:

    sqlserver-common-0.0.1.sql
    sqlserver-common-0.0.2.sql
    sqlserver-server-7.0.0.sql
    sqlserver-server-7.1.0.sql
    sqlserver-server-8.0.0.sql
    sqlserver-muc-3.0.0.sql
    sqlserver-pubsub-3.1.0.sql
    sqlserver-pubsub-3.2.0.sql
    sqlserver-pubsub-4.0.0.sql
    sqlserver-http-api-2.0.0.sql

### Note

These files must be done sequentially! They are not linked, and so may need to be done one at a time.

Other components may require installation such as:

    sqlserver-socks5-2.0.0.sql
    sqlserver-push-1.0.0.sql
    sqlserver-message-archiving-2.0.0.sql
    sqlserver-unified-archive-2.0.0.sql

Subsequently select created database from the list of Available Databases (Ctrl+U) available on the toolbar and execute each of the opened files in the order listed above.

#### Configuring from command line tool

Creation of the database and import of schema can be done from command line as well. In order to do that, execute following commands from the directory where Tigase XMPP Server is installed otherwise paths to the schema need to be adjusted accordingly:

    sqlcmd -S %servername% -U %root_user% -P %root_pass% -Q "CREATE DATABASE [%database%]"
    sqlcmd -S %servername% -U %root_user% -P %root_pass% -Q "CREATE LOGIN [%user%] WITH PASSWORD=N'%password%', DEFAULT_DATABASE=[%database%]"
    sqlcmd -S %servername% -U %root_user% -P %root_pass% -d %database% -Q "ALTER AUTHORIZATION ON DATABASE::%database% TO %user%;"
    sqlcmd -S %servername% -U %root_user% -P %root_pass% -d %database% -i database\sqlserver-schema-7-1-schema.sql
    sqlcmd -S %servername% -U %root_user% -P %root_pass% -d %database% -i database\sqlserver-schema-7-1-sp.sql
    sqlcmd -S %servername% -U %root_user% -P %root_pass% -d %database% -i database\sqlserver-schema-7-1-props.sql
    sqlcmd -S %servername% -U %root_user% -P %root_pass% -d %database% -i database\sqlserver-pubsub-schema-3.2.0.sql

Above can be automatized with provided script %tigase-server%\scripts\db-create-sqlserver.cmd (note: it needs to be executed from main Tigase XMPP Server directory due to maintain correct paths):

    $ scripts\db-create-sqlserver.cmd %database_servername% %database_name% %tigase_username% %tigase_password% %root_username% %root_password%

If no parameters are provided then the following defaults are used:

    %database_servername%=localhost
    %database_name%=tigasedb
    %tigase_username%=tigase
    %tigase_password%=tigase12
    %root_username%=root
    %root_password%=root

#### Tigase configuration - config.tdsl

Configuration of the MS SQL Server follows general database convention.

    dataSource {
        default () {
            uri = 'jdbc:[jtds:]sqlserver://db_hostname:port[;property=val]'
        }
    }

where any number of additional parameters can (and should) consist of:

- `databaseName` - name of the database
- `user` - username configured to access database
- `password` - password for the above username
- `schema` - name of the database schema
- `lastUpdateCount` - 'false' value causes all update counts to be returned, including those returned by server triggers

Example:

    dataSource {
        default () {
            uri = 'jdbc:sqlserver://hostname:1433;databaseName=tigasedb;user=tigase;password=tigase12;schema=dbo;lastUpdateCount=false'
        }
    }

#### JDBC: jTDS vs MS JDBC driver

Tigase XMPP Server supports two JDBC drivers intended to be used with Microsoft SQL Server - one created and provided by Microsoft itself and the alternative implementation - jTDS. Tigase is shipped with the latter in the distribution packages. Starting with the version 7.1.0 we recommend using jDTS driver that is shipped with Tigase as JDBC driver created by Microsoft can cause problems with some components in cluster installations. MS driver can be downloaded form the website: JDBC Drivers 4.0, 4.1 for SQL Server then unpack the archive. Copy sqljdbc_4.0/enu/sqljdbc4.jar file to ${tigase-server}/jars directory.

Depending on the driver used `uri` needs to be configured accordingly.

- Microsoft driver:

      dataSource {
          default () {
              uri = 'jdbc:sqlserver://...'
          }
      }

- jDTS driver

      dataSource {
          default () {
              uri = 'jdbc:jdts://...'
          }
      }

### Prepare the PostgreSQL Database for the Tigase Server

This guide describes how to prepare PostgreSQL database for connecting to Tigase server.

The PostgreSQL database can be prepared in many ways. Below are presented two possible ways. The following assumptions apply to both methods:

- `admin_db_user` - database user with admin rights
- `tigase_user` - database user for Tigase
- `tigasedb` - database for Tigase

#### Configuring from PostgreSQL Command Line Tool

Run the PostgreSQL command line client and enter following instructions:

Add the `tigase_user`:

    psql=# create role tigase_user with login password 'tigase123';

Next, Create the database for the Tigase server with `tigase_user` as owner of database:

    psql=# create database tigasedb owner tigase_user;

##### Schema Installation

Load database schema to initialize the Tigase server from the file that corresponds to the version of Tigase you want to use. First you need to switch to `tigasedb`.

    psql=# \connect tigasedb

Begin by applying the basic Schema

    psql=# \i database/postgresql-common-0.0.1.sql

Continue by adding the schema files listed below:

    postgresql-common-0.0.1.sql
    postgresql-common-0.0.2.sql
    postgresql-server-7.0.0.sql
    postgresql-server-7.1.0.sql
    postgresql-server-8.0.0.sql
    postgresql-muc-3.0.0.sql
    postgresql-pubsub-3.1.0.sql
    postgresql-pubsub-3.2.0.sql
    postgresql-pubsub-4.0.0.sql
    postgresql-http-api-2.0.0.sql

Other components may require installation such as:

    postgresql-socks5-2.0.0.sql
    postgresql-push-1.0.0.sql
    postgresql-message-archiving-2.0.0.sql
    postgresql-unified-archive-2.0.0.sql

#### Configuring From the Linux Shell Command Line

Follow steps below to prepare the PostgreSQL database:

First, add the `tigase_user`:

    createuser -U admin_db_user -W -D -R -S -P tigase_user

You will be asked for credentials for admin_db_user and password for new database user.

Create the database for the Tigase server with tigase_user as owner of database:

    createdb -U admin_db_user -W -O tigase_user tigasedb

##### Database Schema Installation

Load database schema to initialize the Tigase server

    psql -q -U tigase_user -W tigasedb -f database/postgresql-common-0.0.1.sql
    psql -q -U tigase_user -W tigasedb -f database/postgresql-common-0.0.2.sql
    etc..

Continue by adding the schema files listed below:

    postgresql-common-0.0.1.sql
    postgresql-common-0.0.2.sql
    postgresql-server-7.0.0.sql
    postgresql-server-7.1.0.sql
    postgresql-server-8.0.0.sql
    postgresql-muc-3.0.0.sql
    postgresql-pubsub-3.1.0.sql
    postgresql-pubsub-3.2.0.sql
    postgresql-pubsub-4.0.0.sql
    postgresql-http-api-2.0.0.sql

Other components may require installation such as:

    postgresql-socks5-2.0.0.sql
    postgresql-push-1.0.0.sql
    postgresql-message-archiving-2.0.0.sql
    postgresql-unified-archive-2.0.0.sql

### Note

The above commands should be executed from the main Tigase directory. The initialization schema file should be also available locally in database/ directory of your Tigase installation.

### Preparing Tigase for MongoDB

Tigase now supports MongoDB for auth, settings, and storage repositories. If you wish to use MongoDB for Tigase, please use this guide to help you.

#### Dependencies

To run Tigase MongoDB support library requires drivers for MongoDB for Java which can be downloaded from here. This driver needs to be placed in `jars/` directory located in Tigase XMPP Server installation directory. If you are using a dist-max distribution, it is already included.

#### Configuration

Note that fresh installations of MongoDB do not come with users or databases installed. Once you have setup MongoDB you will need to create a user to be used with Tigase. To do this, bring up the mongo console by running mongo.exe in a cmd window for windows, or run mongo in linux. Once connected, enter then following:

    use admin
    db.createUser( { user: "tigase",
                     pwd: "password",
                     customData: { employeeId: 12345 },
                     roles: [ "root" ]
                    }
                  )

Be sure to give this user a `root` role in order to properly write to the database. Once you receive a `user successfully created` message, you are ready to install tigase on MongoDB.

##### Configuration of user repository for Tigase XMPP Server

To configure Tigase XMPP Server to use MongoDB you need to set `dataSource` in etc/config.tdsl file to proper MongoDB URI pointing to which MongoDB database should be used (it will be created by MongoDB if it does not exist). `userRepository` property should not be set to let Tigase XMPP Server auto-detect proper implementation of `UserRepository`. Tigase XMPP Server will create proper collections in MongoDB if they do not exist so no schema files are necessary.

Example configuration of XMPP Server pointing to MongoDB database `tigase_test` in a local instance:

    dataSource {
        default () {
            uri = 'mongodb://user:pass@localhost/tigase_test'
        }
    }
    userRepository {
        default () {}
    }
    authRepository {
        default () {}
    }

If Tigase Server is not able to detect a proper storage layer implementation, it can be forced to use one provided by Tigase using the following lines in `etc/config.tdsl` file:

    userRepository {
        default () {
            cls = 'tigase.mongodb.MongoRepository'
        }
    }
    authRepository {
        default () {
            cls = 'tigase.mongodb.MongoRepository'
        }
    }

Every component should be able to use proper implementation to support MongoDB using this URI. Also MongoDB URI can be passed as any URI in configuration of any component.

##### Configuration for MUC

By default, MUC component will use MongoDB to store data if Tigase is configured to use it as a default store. However, if you would like to use a different MongoDB database to store MUC message archive, you can do this by adding the following lines to `etc/config.tdsl` file:

    muc {
        'history-db-uri' = 'mongodb://user:pass@localhost/tigase_test'
    }

If MUC components fails to detect and use a proper storage layer for MongoDB, you can force it to use one provided by Tigase by using the following line in the `config.tdsl` file:

    muc {
        'history-db' = 'tigase.mongodb.muc.MongoHistoryProvider'
    }

##### Configuration for PubSub

By default, PubSub component will use MongoDB to store data if Tigase is configured to use it as a default store. However, if you would like to use a different MongoDB database to store PubSub component data, you can do this by adding the following lines to `etc/config.tdsl` file:

    pubsub {
        'pubsub-repo-url' = 'mongodb://user:pass@localhost/tigase_test'
    }

If the PubSub components fails to detect and use a proper storage layer for MongoDB, you can force it to use one provided by Tigase by using the following line in the `config.tdsl` file:

    pubsub {
        'pubsub-repo-class' = 'tigase.mongodb.pubsub.PubSubDAOMongo'
    }

##### Configuration for Message Archiving

By default, the Message Archiving component will use MongoDB to store data if Tigase is configured to use it as a default store. However, if you would like to use a different MongoDB database to store message archives, you can do this by adding the following lines to `etc/config.tdsl` file:

    'message-archive' {
        'archive-repo-uri' = 'mongodb://user:pass@localhost/tigase_test'
    }

If Message Archiving component fails to detect and use a proper storage layer for MongoDB, you can force it to use one provided by Tigase by using the following line in the `config.tdsl` file:

    'message-archive' {
        'archive-repo-class' = 'tigase.mongodb.archive.MongoMessageArchiveRepository'
    }

#### Schema Description

This description contains only basic description of schema and only basic part of it. More collections may be created if additional components of Tigase XMPP Server are loaded and configured to use MongoDB.

##### Tigase XMPP Server Schema

Basic schema for UserRespository and AuthRepository consists of two collections: . tig_users - contains list of users . tig_nodes - contains data related to users in tree-like way

`tig_users` collection contains the following fields:

Table 9.1. tig_users

Name | Description
--- | ---
_id | id of user which is SHA256 hash of users jid (raw byte array).
user_id | contains full user jid.
domain | domain to which user belongs for easier lookup of users by domain.
password | password of user (will be removed after upgrade to 8.0.0).

`tig_nodes` collection contains the following fields

Table 9.2. tig_nodes

Name | Description
--- | ---
_id | id of row auto-generated by MongoDB.
uid | id of user which is SHA256 hash of users jid (raw byte array).
node | full path of node in tree-like structure separated by / (may not exist).
key | key for which value for node is set.
value | value which is set for node key.

Tigase XMPP Server also uses additional collections for storage of Offline Messages

Table 9.3. msg_history collection

Name | Description
--- | ---
from | full user jid of message sender.
from_hash | SHA256 hash of message sender jid as raw byte array.
to | full users jid of message recipient.
to_hash | SHA256 hash of message recipient full jid as raw byte array.
ts | timestamp of message as date.
message | serialized XML stanza containing message.
expire-at | timestamp of expiration of message (if message contains AMP expire-at set).

<mongodb-schema-changes-8> <title>Additions for v8.0 Schema</title>

Due to changes in authentication and credentials storage in AuthRepository, we moved `password` field from `tig_users` collection to a newly created collection called `tig_user_credentials`.

This new collection has following fields:

Name | Description
--- | ---
_id | id of document automatically generated by MongoDB
uid | SHA256 hash of a user for which credentails are stored
username | username provided during authentication (or `default`)
account_status | name of an account state (copy of value stored in user document from`tig_users`)

Additionally for each mechanism we store separate field in this object, so for:

- `PLAIN` we have `PLAIN` field with value for this mechanism
- `SCRAM-SHA-1` we have `SCRAM-SHA-1` field with value for this mechanism
- etc…​

Upgrade is not done in one step, and rather will be done once a particular user will log in. During authentication if there is no data in `tig_user_credentials`, Tigase XMPP Server will check if `password` field in `tig_user` exists. If it does, and it is filled credentials will be migrated to the new collection.

</mongodb-schema-changes-8>

Prev | Up | Next
Schema files maintenance | Home | Hashed User Passwords in Database