Basic schema filename layout consists of 3 basic parts:

For each component and version it’s possible (but not mandatory) to split all database related functionality into multiple files but it’s essential that they would be linked/included in the base file for particular database/component/version file. This allows separating Stored Procedures (-sp), base schema (-schema) and setting properties (-props). In principle the filename pattern looks as follows

<RDBMS_name>-<tigase_component>-schema-<version>[-<sub_schema>].sql

For example schema file for version 7.0.0 of Tigase Server for Derby looks as follow:

derby-server-schema-7.0.0-schema.sql

As mentioned before, we should support all versions matching old-stable, stable and master, which translates to two main versions behind current-version, that is version: current-version - 2). This results in having 3 versions of the schema in the repository at any given time (two of them being ``upgrades'' to the oldest, base schema):

For example with the release of version 8.0.0 this will translate to following versions:

As we keep at the most only 3 versions of the schema, after release of the version we need to adjust (flatten) the files to maintain structure defined in Files structure (it may happen, that there wouldn’t be any changes in the schema for particular version which will result in relatively empty current-version schema file – only setting current version for component with setVersion('component','<current-version></current-version>'); ).

For example we are about to release version 8.0.0. This results in the following versions of the schema (in the example for the server) in the repository:

After the release we specify the version of the next release in pom.xml (for example 8.1.0 and the same version will be the current-version making the oldest available version 7.1.0. Because of that we MUST incorporate all the changes in 7.1.0 onto 7.0.0 creating new base file with version 7.1.0, i.e.:

Following cases will be discussed with solid-version examples. Comments will be provided in-line Following assumptions are made:

There are two main control instructions (intended for SchemaLoader):

Each repository will have a table tig_schema_versions with the information about all installed components and it’s versions in that particular repository. There will be an associated stored procedure to obtain and set version:

It will be stored and maintained in the file named <RDBMS_name>-common-schema-<version>.sql

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:

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

mysql> FLUSH PRIVILEGES;

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

mysql> use tigasedb;

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

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

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

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...

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:

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

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

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

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

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

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

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.

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.

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

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

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

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"

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

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

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};'
    }
}

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.

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.

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

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

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.

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

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:

Example:

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

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.

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;

psql=# \connect tigasedb

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

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

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

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

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..

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

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

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.

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.

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

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

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

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

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

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

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

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

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.

To add a new user account we use a stored procedure: TigAddUserPlainPw(bareJid, password). As you can see there is this strange appendix to the procedure name: PlainPw. This procedure accepts plain passwords regardless how it is stored in the database. So it is safe and easy to use either for plain-text passwords or hashed in the DB. There are also versions of procedures without this appendix but they are sensitive on the data format and always have to pass password in the exact format it is stored in the database.

So, let’s add a new user account:

call TigAddUserPlainPw('test@example.com', 'test77');

If the result was 'Query OK', then it means the user account has been successfully created.

We can now test user authentication:

call TigUserLoginPlainPw('test@example.com', 'test77');

If authentication was successful the result looks like this:

+--------------------+
| user_id            |
+--------------------+
| 'test@example.com' |
+--------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

If authentication was unsuccessful, the result looks like this:

+---------+
| user_id |
+---------+
|    NULL |
+---------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

TigGetDBProperty is a function, not a procedure in MySQL database so we have to use select to call it:

select TigGetDBProperty('password-encoding');

Most likely output is this:

+---------------------------------------+
| TigGetDBProperty('password-encoding') |
+---------------------------------------+
| NULL                                  |
+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

Which means a default password encoding is used, in plain-text and thus no encoding. And we can actually check this in the database directly:

select uid, user_id, user_pw from tig_users where user_id = 'test@example.com';

And expected result with plain-text password format would be:

+-----+--------------------+---------+
| uid | user_id            | user_pw |
+-----+--------------------+---------+
|  41 | 'test@example.com' | test77  |
+-----+--------------------+---------+
1 row in set (0.00 sec)

Now let’s set password encoding to MD5 hash:

call TigPutDBProperty('password-encoding', 'MD5-PASSWORD');

'Query OK', means the password encoding has been successfully changed. Of course we changed the property only. All the existing passwords in the database are still in plain-text format. Therefore we expect that attempt to authenticate the user would fail:

call TigUserLoginPlainPw('test@example.com', 'test777');
+---------+
| user_id |
+---------+
|    NULL |
+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

We can fix this by updating the user’s password in the database:

call TigUpdatePasswordPlainPw('test@example.com', 'test777');
Query OK, 1 row affected (0.01 sec)

mysql> call TigUserLoginPlainPw('test@example.com', 'test777');
+--------------------+
| user_id            |
+--------------------+
| 'test@example.com' |
+--------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

The best way to setup Tigase with Drupal database is via the config.tdsl file where you can put initial setting for Tigase configuration.

If you look in etc/ directory of your Tigase installation you should find a the file there.

All you need to connect to Drupal database is set the following:

dataSource {
    'default-auth' () {
        uri = 'jdbc:mysql://localhost/drupal?user=drupalusr&password=drupalpass'
    }
}
authRepository {
    default () {
        cls = 'tigase.db.jdbc.DrupalWPAuth'
        'data-source' = 'default-auth'
    }
}

Typically, you will need to have drupal for authentication, and another for user repository. In this case, we will use SQL for user DB.

dataSource {
    default () {
        uri = 'jdbc:mysql://localhost/tigasedb?user=tigase_user&password=mypass'
    }
    'default-auth' () {
        uri = 'jdbc:mysql://localhost/drupal?user=drupalusr&password=drupalpass'
    }
}
userRepository {
    default () {}
}
authRepository {
    default () {
        cls = 'tigase.db.jdbc.DrupalWPAuth'
        'data-source' = 'default-auth'
    }
}

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.

Now run the Tigase server.

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

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

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 is almost identical to MySQL database setup. The only difference is the connection string which usually looks like:

dataSource {
    default () {
        uri = 'postgresql://localhost/tigasdb?user=tigase'
    }
}

Before version 8.0.0, user passwords were stored in plaintext in user_pw database field within tig_users table, but in plaintext. It was possible to enable storage of the MD5 hash of the password instead, however this limited authentication mechanism SASL PLAIN only. However an MD5 hash of a password is not really a secure method as it is possible to revert this mechanism using rainbow tables.

Therefore, we decided to change this and store only encrypted versions of a password in PBKDF2 form which can be easily used for SCRAM-SHA-1 authentication mechanism or SCRAM-SHA-256. SASL PLAIN mechanism can also used these encrypted passwords. The storage of encrypted passwords is now enabled by default in v8.0.0 of Tigase.

When upgrading from previous versions of Tigase, it is recommended that you first backup the database. Refer to the documentation of your database software to find out how to export a copy. Once the backup is made, it will be time to run the schema upgrade. Be sure that your schema is up to date, and should be v7.1.0 Schema.

To upgrade, use the new upgrade-schema task of SchemaManager:

You will need to configure the following switches:

The schema has changed for the main database, and the pubsub repository. In order to upgrade to the new schemas, you will need to do the following:

All three commands may be done at the same time in that order, it is suggested you make a backup of your current database to prevent data loss.

Tigase has made changes to its database to include primary keys in the tig_pairs table to improve performance of the Tigase server. This is an auto-incremented column for Primary Key items appended to the previous schema.

If you are installing a new version of v8.0.0 on a new database, the schema should automatically be installed.

First, shut down any running instances of Tigase to prevent conflicts with database editing. Then from command line use the DBSchemaLoader class to run the -schema-upgrade-to-7.1.sql file to the database. The command is as follows:

In a linux environment

java -cp "jars/*" tigase.db.util.DBSchemaLoader -dbHostname ${HOSTNAME} -dbType ${DB_TYPE} -rootUser ${ROOT_USER} -dbPass ${DB_USER_PASS} -dbName ${DB_NAME} -schemaVersion ${DB_VERSION} -rootPass ${ROOT_USER_PASS} -dbUser ${DB_USER}  -adminJID "${ADMIN_JID}" -adminJIDpass ${ADMIN_JID_PASS}  -logLevel ALL -file database/${DB_TYPE}-schema-upgrade-to-7-1.sql

In a windows environment

java -cp jars/* tigase.db.util.DBSchemaLoader -dbHostname ${HOSTNAME} -dbType ${DB_TYPE} -rootUser ${ROOT_USER} -dbPass ${DB_USER_PASS} -dbName ${DB_NAME} -schemaVersion ${DB_VERSION} -rootPass ${ROOT_USER_PASS} -dbUser ${DB_USER}  -adminJID "${ADMIN_JID}" -adminJIDpass ${ADMIN_JID_PASS}  -logLevel ALL -file database/${DB_TYPE}-schema-upgrade-to-7-1.sql

All variables will be required, they are as follows:

Please note that the SQL file for the update will have an associated database with the filename. i.e. postgresql-update-to-7.1.sql for postgresql database.

A finalized command will look something like this:

java -cp "jars/*" tigase.db.util.DBSchemaLoader -dbHostname localhost -dbType mysql -rootUser root -rootPass root -dbUser admin -dbPass admin -schemaVersion 7.1 -dbName Tigasedb -adminJID "admin@local.com" -adminJIDPass adminpass -logLevel ALL -file database/mysql-schema-upgrade-to-7.1.sql

Once this has successfully executed, you may restart you server. Watch logs for any db errors that may indicate an incomplete schema upgrade.

Tigase has had a change to the PubSub Schema, to upgrade to PubSub Schema v7.1 without having to reform your databases, use this guide to update your databases to be compatible with the new version of Tigase.

The PubSub Schema has been streamlined for better resource use, this change affects all users of Tigase. To prepare your database for the new schema, first be sure to create a backup! Then apply the appropriate PubSub schema to your MySQL and it will add the new storage procedure.

All these files should be in your /database folder within Tigase, however if you are missing the appropriate files, use the links below and place them into that folder.

The MySQL schema can be found Here.

The Derby schema can be found Here.

The PostGRESQL schema can be found Here.

The same files are also included in all distributions of v8.0.0 in [tigaseroot]/database/ . All changes to database schema are meant to be backward compatible.

You can use a utility in Tigase to update the schema using the following command from the Tigase root:

Use the following options to customize. Options in bold are required.:

As a result your final command should look something like this:

java -cp "jars/*" tigase.db.util.DBSchemaLoader -dbType mysql -dbName tigasedb -dbUser root -dbPass password -file database/mysql-pubsub-schema-3.1.0.sql