4. Database

4.1. Preparation of database

Before you will be able to use Tigase Message Archiving Component and store messages in particular database you need to initialize this 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-message-archiving-version.sql, where dbname in name of database type which this schema supports and version is version of a Message Archiving 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 Preparation section of Tigae XMPP Server Administration Guide

4.2. 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 Preparation of database section.

Note

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

4.3. Schema description

Tigase Message Archiving component uses few tables and stored procedures. To make it easier to find them on database level they are prefixed with tig_ma_.

4.3.1. Table tig_ma_jids

This table stores all jids related to stored messages, ie. from to and from attributes of archived stanzas.

Field	Description	Comments
jid_id	Database ID of a JID
jid	Value of a bare JID
jid_sha1	SHA1 value of lowercased bare JID	Used for proper bare JID comparison during lookup. (N/A to PostgreSQL schema)
domain	Domain part of a bare JID	Stored for easier lookup of messages owned by users of a particular domain

4.3.2. Table tig_ma_msgs

Table stores archived messages.

Field	Description	Comments
stable_id	Database ID of a message	Unique with matching owner_id
owner_id	ID of a bare JID of a message owner	References jid_id from tig_ma_jids
buddy_id	ID of a bare JID of a message recipient/sender (different than owner)	References jid_id from tig_ma_jids
ts	Timestamp of a message	Timestamp of archivization or delayed delivery
body	Body of a message
msg	Serialized message
stanza_id	ID attribute of archived message
is_ref	Marks if message is a reference to other message
ref_stable_id	stable_id of referenced message

4.3.3. Table tig_ma_tags

Table stores tags of archived messages. It stores one tag for many messages using tig_ma_msgs_tags to store relation between tag and a message.

Field	Description	Comments
tag_id	Database ID of a tag
owner_id	ID of a bare JID of a tag owner	ID of bare JID of owner for which messages with this tag were archived
tag	Actual tag value

4.3.4. Table tig_ma_msgs_tags

Table stores relations between tags and archived messages with this tags.

Field	Description	Comments
msg_owner_id	ID of a bare JID of a tag owner	ID of bare JID of owner for which messages with this tag were archived
msg_stable_id	Database ID of a message	Unique with matching msg_owner_id
tag_id	Database ID of a tag	References tag_id from tig_ma_tags