Preparation of database

Before you will be able to use Tigase PubSub Component you need to initialize database. We provide few schemas for this component for MySQL, PostgreSQL, SQLServer and DerbyDB.

They are placed in database/ directory of installation package and named in dbtype-pubsub-version.sql, where dbname in name of database type which this schema supports and version is version of a PubSub component for which this schema is designed.

You need to manually select schema for correct database and component and load this schema to database. For more information about loading database schema look into database preperation section of this guide.

Upgrade of database schema

Database schema for our components may change between versions and if so it needs to be updated before new version may be started. To upgrade schema please follow instructions from the database preperation section.


If you use SNAPSHOT builds then schema may change for same version as this are versions we are still working on.

Schema description

Tigase PubSub component uses few tables and stored procedures. To make it easier to identify tables and stored procedures used by PubSub component they are prefixed with tig_pubsub_.

Table tig_pubsub_service_jids

This table stores all jids for which PubSub component contains nodes.



Database ID of a service JID



Value of a service JID



SHA1 value of lowercased service JID

Used for proper bare JID comparison during lookup.

(N/A to PostgreSQL schema)

Table tig_pubsub_jids

This table stores all jids related to PubSub nodes, ie. subscriber, affiliates, creators, publishers, etc.



Database ID of a bare JID



Value of a bare JID



SHA1 value of lowercased bare JID

Used for proper bare JID comparison during lookup.

(N/A to PostgreSQL schema)

Table tig_pubsub_nodes

Table stores nodes tree structure and node configuration.



Database ID of a node



ID of service JID

References service_id from tig_pubsub_service_jids


Name of PubSub node



SHA1 of PubSub node name

Used for indexing and faster lookup.

(N/A to PostgreSQL schema)


Type of PubSub node

0 - collection

1 - leaf


Title of PubSub node



Description of a node



ID of JID of creator

References jid_id from tig_pubsub_jids


Timestamp of creation time



Serialized configuration of PubSub node



Points collection (parent node)

References node_id from tig_pubsub_nodes

Table tig_pubsub_affiliations

Table stores affiliations between PubSub nodes and jids.



ID of a node

References node_id from tig_pubsub_nodes


ID of a user JID

References jid_id from tig_pubsub_jids


Affiliation value


Table tig_pubsub_subscriptions

Table stores subscriptions of jids to PubSub nodes.



ID of a node

References node_id from tig_pubsub_nodes


ID of a user JID

References jid_id from tig_pubsub_jids


Subscription value



Id of a subscription


Table tig_pubsub_items

Table stores items of PubSub nodes.





ID of a node

References node_id from tig_pubsub_nodes


Id of an items



SHA1 of item id

Indexed and used for faster lookup

(N/A to PostgreSQL schema)


Creation date



ID of publisher JID

References jid_id from tig_pubsub_jids


Timestamp of last item modification



Item payload


PubSub Schema Changes

Tigase PubSub Component is currently version 3.3.0 which is introduced in Tigase server v8.0.0.

PubSub 3.2.0 Changes

PubSub v 3.2.0 adds a new procedure TigPubSubGetNodeMeta which supports PubSub metadata retrieval while conducting a disco#info query on nodes.

You will need to upgrade your database if you are not using v3.2.0 schema. Tigase will report being unable to load PubSub component if you do not have this schema version.

The MySQL schema can be found Here.

The Derby schema can be found Here.

The PostGRESQL schema can be found Here.

The MS SQL 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.

For instructions how to manually upgrade the databases, please refer to Tigase v7.1.0 Schema Updates section.

Upgrading older installations (pre-v3.0.0 Schema)

To update older installations of Tigase to the PubSub Schema v3.0.0 follow these instructions. Note this should be done before upgrading to PubSub v3.1.0.

Step by Step guide.

Prepare Old Database for Upgrade

In database directory of Tigase installation you will find SQL files which will prepare old database schema for upgrade using following this naming pattern: <database_type>-pubsub-schema-3.0.0-pre-upgrade.sql Where <database_type> can be one of the following: mysql, sqlserver, ie. for MySQL you will find the file mysql-pubsub-schema-3.0.0-pre-upgrade.sql. You need to execute statements from this file on your source database, which will drop old procedures and functions used to access database and also this statements will rename old tables by adding suffix _1 to each of old tables. Example:

mysql -u tigase -p tigase_pubsub < database/mysql-pubsub-schema-3.0.0-pre-upgrade.sql
sqlcmd -S %servername% -U %root_user% -P %root_pass% -d %database% -i database\sqlserver-pubsub-schema-3.0.0-pre-upgrade.sql
Update Tigase PubSub Component

For this you need to copy the Tigase PubSub Component jar file to jars directory inside Tigase XMPP Server installation directory. It is also recommended to copy files from database directory of Tigase PubSub Component to database directory in Tigase XMPP Server installation directory.

If you happen to use one of the the distribution packaged (either installer or -dist-max flavored archive) then all required files are already available - both new schema files will be available in database/ directory as well as both versions of PubSub component will be present in jars/ directory - PubSub3 as tigase-pubsub.jar and PubSub2 as tigase-pubsub-2.2.0.jar.old (provided for compatibility reasons).

Load New Schema

In the database directory you will find files containing new schemas for:

  • MySQL - mysql-pubsub-schema-3.0.0.sql
  • PostgreSQL - postgresql-pubsub-schema-3.0.0.sql
  • MSSQL - sqlserver-pubsub-schema-3.0.0.sql
  • DerbyDB - derby-pubsub-schema-3.0.0.sql and

For most databases, with the exception of Derby, you only need to execute statements from the proper file. For example:

mysql -u tigase -p tigase_pubsub < database/mysql-pubsub-schema-3.0.0.sql
sqlcmd -S %servername% -U %root_user% -P %root_pass% -d %database% -i database\sqlserver-pubsub-schema-3.0.0.sql
psql -h $DB_HOST -q -U ${USR_NAME} -d $DB_NAME -f database/sqlserver-pubsub-schema-3.0.0.sql

For DerbyDB you need to execute the script and pass proper JDBC URI to database to which you want to load schema (if database does not exist, it will be created).


NOTE: It is possible to use same database which was used before - then after upgrade you will have new tables and old tables with _1 suffix.

Execute Migration Utility

In the /database directory you will find the file which you need to execute and pass arguments with JDBC URIs needed to connect to source and destination database. If you used dedicated tables for PubSub you will also need to pass a class name used to access database (value of pubsub-repo-class variable from etc/config.tdsl file).

Example for dedicated table used for PubSub:

database/ -in-repo-class tigase.pubsub.repository.PubSubDAO
-in 'jdbc:mysql://localhost/tigase_pubsub?user=tigase&password=passwd'
-out 'jdbc:mysql://localhost/tigase_pubsub?user=tigase&password=passwd'

Example for use without dedicated PubSub tables:

-in 'jdbc:mysql://localhost/tigase?user=tigase&password=passwd'
-out 'jdbc:mysql://localhost/tigase?user=tigase&password=passwd'

Example for use with dedicated tables in a Windows environment:

database/pubsub-db-migrate.cmd -in-repo-class tigase.pubsub.repository.PubSubDAO
-in 'jdbc:sqlserver://<hostname>\\<instance>:<port>;databaseName=<name>;user=tigase;password=tigase;schema=dbo;lastUpdateCount=false'
-out 'jdbc:sqlserver://<hostname>\\<instance>:<port>;databaseName=<name>;user=tigase;password=tigase;schema=dbo;lastUpdateCount=false'

During execution this utility will report information about migration of PubSub data to the new schema, and the same information will be store in pubsub_db_migration.log.


After successful migration you will have all data copied to new tables. Old tables will be renamed by adding suffix _1. After verification that everything works OK, you can delete old tables and it’s content as it want be used any more.