4. Database

4.1. Preparation of database

Before you will be able to use Tigase Unified Archive Component and store messages and events 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-unified-archive-version.sql, where dbname in name of database type which this schema supports and version is version of a Unified Archive 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 [Databse Preparation] section of preparation of database

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 instuctions 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 Unified Archive component uses few tables and stored procedures and it shares same tables with Tigase Message Archiving component - in fact it uses same tables with addition of few fields. Due to that used tables are prefixed with tig_ma_ and tig_ua_, but used stored procedures are prefixed with Tig_UA_.

4.3.1. Table tig_ma_jids

This table stores all jids related to stored messages and events, 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. (Not exists in 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 events.

Field	Description	Comments
stable_id	ID of a stored event	Unique with matching owner_id
owner_id	ID of a bare JID of a event owner	References jid_id from tig_ma_jids
buddy_id	ID of a bare JID of a event recipient/sender (different than owner)	References jid_id from tig_ma_jids
ts	Timestamp of a event	Timestamp of archivization or delayed delivery
body	Body of a message
msg	Serialized event
stanza_id	ID attribute of archived event
is_ref	Marks if message is a reference to other message
ref_stable_id	stable_id of referenced message
item-type	Event type	May be one of: 0 - message of unknown type, 1 - chat message, 2 - groupchat message, 132 - call, 128 - login, `129 - logout. (Added in UA schema)
offline	Marks offline events	0 - not offline 1 - offline and will be stored after delivery 2 - offline and will be removed after delivery

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	ID of a stored event	Unique with matching msg_owner_id
tag_id	Database ID of a tag	References tag_id from tig_ma_tags

4.3.5. Table tig_ua_for

Table stores resource of a sender of a message saved to the offline storage.

Field	Description	Comments
stable_id	ID of a stored event	Unique with matching owner_id
owner_id	ID of a bare JID of a event owner	References jid_id from tig_ma_jids
buddy_res	Resource part of a event recipient/sender JID

4.3.6. Table tig_ua_jingle

Table stores jingle metadata from a message saved to the storage.

Field	Description	Comments
stable_id	ID of a stored event	Unique with matching owner_id
owner_id	ID of a bare JID of a event owner	References jid_id from tig_ma_jids
direction	Direction of event	0 - sent by owner 1 - recevied by owner
offline	Marks call to offline	1 if was to offline user
sid	SID of a Jingle call
action	Jingle action
reason	Reason of action	Usually for termination/decline events

4.3.7. Table tig_ua_file_metadata

Table stores jingle metadata from a message saved to the storage.

Field	Description	Comments
stable_id	ID of a stored event	Unique with matching owner_id
owner_id	ID of a bare JID of a event owner	References jid_id from tig_ma_jids
url_hash	SHA1 of the URL
url	URL to download sent file
name	File name	If available
description	Description of a file	If available
media_type	Type of a file (MIMETYPE)	If available
size	File size	If available