<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>Wildfire Database Schema Guide</title> <link href="style.css" rel="stylesheet" type="text/css"> </head> <body> <a name="top"></a> <h1>Wildfire Database Schema Guide</h1> <h2>Introduction</h2> <p> This document outlines the data type conventions and tables in the Jive Wildfire 2.1 database schema. Some information, like column indexes and foreign keys, is omitted. For this, please read the individual schema of the database you're interested in. <br> </p> <h2>Data Type Conventions</h2> <p> Date column type support varies widely across databases. Therefore, Wildfire specially encodes dates as VARCHAR values. Each date is a Java long value which is 0-padded to 15 characters. The long value is the internal representation of Java Date objects, which can be obtained with code such as the following: </p> <pre>long currentDate = new Date().getTime();</pre> <p> Boolean values are always represented a numeric values: 0 for false and 1 for true. </p> <h2>Database Tables</h2> <p> Below is a description of each of the tables in the Wildfire database schema. A <span class="primary-key">yellow row</span> denotes a primary key. </p> <ul> <li><a href="#jiveGroup">jiveGroup</a> </li> <li><a href="#jiveGroupProp">jiveGroupProp</a> </li> <li><a href="#jiveGroupUser">jiveGroupUser</a> </li> <li><a href="#jiveID">jiveID</a></li> <li><a href="#jiveOffline">jiveOffline</a><br> </li> <li><a href="#jivePrivate">jivePrivate</a> </li> <li><a href="#jiveUser">jiveUser</a><br> </li> <li><a href="#jiveUserProp">jiveUserProp</a> </li> <li><a href="#jiveRoster">jiveRoster</a> </li> <li><a href="#jiveRosterGroups">jiveRosterGroups</a> </li> <li><a href="#jivePrivacy">jivePrivacyList</a> </li> <li><a href="#jiveVCard">jiveVCard</a> </li> <li><a href="#jiveProperty">jiveProperty</a><br> </li> <li><a href="#jiveExtComponentConf">jiveExtComponentConf</a> </li> <li><a href="#jiveRemoteServerConf">jiveRemoteServerConf</a><br> </li> <li><a href="#mucRoom">mucRoom</a> </li> <li><a href="#mucRoomProp">mucRoomProp</a> </li> <li><a href="#mucAffiliation">mucAffiliation</a> </li> <li><a href="#mucMember">mucMember</a> </li> <li><a href="#mucConversationLog">mucConversationLog</a> </li> </ul> <br> <a name="jiveGroup"></a> <table class="dbtable"> <tbody> <tr> <th colspan="4">jiveGroup (user Group data)</th> </tr> <tr> <th>Column Name</th> <th>Type</th> <th>Length</th> <th>Description</th> </tr> <tr class="primary-key"> <td>groupName</td> <td>VARCHAR</td> <td>50</td> <td>Group Name (Primary Key)</td> </tr> <tr> <td>description</td> <td>VARCHAR</td> <td>255</td> <td>Group Description</td> </tr> </tbody> </table> <a href="#top" class="top">top of page</a> <br> <br> <br> <a name="jiveGroupProp"></a> <table class="dbtable"> <tbody> <tr> <th colspan="4">jiveGroupProp (name-value associations for a Group)</th> </tr> <tr> <th>Column Name</th> <th>Type</th> <th>Length</th> <th>Description</th> </tr> <tr class="primary-key"> <td>groupName</td> <td>VARCHAR</td> <td>50</td> <td>Group Name (Primary Key)</td> </tr> <tr class="primary-key"> <td>name</td> <td>VARCHAR</td> <td>100</td> <td>Group Property Name (Primary Key)</td> </tr> <tr> <td>propValue</td> <td>VARCHAR</td> <td>4000</td> <td>Group Property Value</td> </tr> </tbody> </table> <a href="#top" class="top">top of page</a> <br> <br> <br> <a name="jiveGroupUser"></a> <table class="dbtable"> <tbody> <tr> <th colspan="4">jiveGroupUser (associates Users with Groups)</th> </tr> <tr> <th>Column Name</th> <th>Type</th> <th>Length</th> <th>Description</th> </tr> <tr class="primary-key"> <td>groupName</td> <td>VARCHAR</td> <td>50</td> <td>Group Name (Primary Key)</td> </tr> <tr class="primary-key"> <td>username</td> <td>VARCHAR</td> <td>100</td> <td>User Name (Primary Key)</td> </tr> <tr class="primary-key"> <td>administrator</td> <td>NUMBER</td> <td>n/a</td> <td>Adminstrator (Boolean) (Primary Key)</td> </tr> </tbody> </table> <a href="#top" class="top">top of page</a> <br> <br> <br> <a name="jiveID"></a> <table class="dbtable"> <tbody> <tr> <th colspan="4">jiveID (used for unique ID sequence generation)</th> </tr> <tr> <th>Column Name</th> <th>Type</th> <th>Length</th> <th>Description</th> </tr> <tr class="primary-key"> <td>idType</td> <td>NUMBER</td> <td>n/a</td> <td>ID type (e.g., Group, User, Roster) (Primary Key)</td> </tr> <tr> <td>id</td> <td>NUMBER</td> <td>n/a</td> <td>Next available block of ID’s (Used for Database-Independent ID Sequence Generator)</td> </tr> </tbody> </table> <a href="#top" class="top">top of page</a> <br> <br> <br> <a name="jiveOffline"></a> <table class="dbtable"> <tbody> <tr> <th colspan="4">jiveOffline (offline message storage)</th> </tr> <tr> <th>Column Name</th> <th>Type</th> <th>Length</th> <th>Change</th> </tr> <tr class="primary-key"> <td>username</td> <td>VARCHAR</td> <td>32</td> <td>User Name (Primary Key)</td> </tr> <tr class="primary-key"> <td style="vertical-align: top;">messageID<br> </td> <td style="vertical-align: top;">NUMBER<br> </td> <td style="vertical-align: top;">n/a<br> </td> <td style="vertical-align: top;">ID of stored message (Primary Key)<br> </td> </tr> <tr> <td style="vertical-align: top;">creationDate<br> </td> <td style="vertical-align: top;">VARCHAR<br> </td> <td style="vertical-align: top;">15<br> </td> <td style="vertical-align: top;">Date message stored<br> </td> </tr> <tr> <td style="vertical-align: top;">messageSize<br> </td> <td style="vertical-align: top;">NUMBER<br> </td> <td style="vertical-align: top;">n/a<br> </td> <td style="vertical-align: top;">Size of message in bytes<br> </td> </tr> <tr> <td style="vertical-align: top;">message<br> </td> <td style="vertical-align: top;">TEXT<br> </td> <td style="vertical-align: top;">n/a<br> </td> <td style="vertical-align: top;">The message text<br> </td> </tr> </tbody> </table> <a href="#top" class="top">top of page</a> <br> <br> <br> <a name="jivePrivate"></a> <table class="dbtable"> <tbody> <tr> <th colspan="4">jivePrivate (Private data storage)</th> </tr> <tr> <th>Column Name</th> <th>Type</th> <th>Length</th> <th>Description</th> </tr> <tr class="primary-key"> <td>username</td> <td>VARCHAR</td> <td>32</td> <td>User Name (Primary Key)</td> </tr> <tr class="primary-key"> <td>name</td> <td>VARCHAR</td> <td>100</td> <td>Name of the private entry (Primary Key)</td> </tr> <tr class="primary-key"> <td>namespace</td> <td>VARCHAR</td> <td>200</td> <td>Namespace of the private entry (Primary Key)</td> </tr> <tr> <td>value</td> <td>TEXT</td> <td>n/a</td> <td>Value of the private data</td> </tr> </tbody> </table> <a href="#top" class="top">top of page</a> <br> <br> <br> <br> <a name="jiveUser"></a> <table class="dbtable"> <tbody> <tr> <th colspan="4">jiveUser (User data)</th> </tr> <tr> <th>Column Name</th> <th>Type</th> <th>Length</th> <th>Description</th> </tr> <tr class="primary-key"> <td>username</td> <td>VARCHAR</td> <td>32</td> <td>User Name (Primary Key)</td> </tr> <tr> <td>password</td> <td>VARCHAR</td> <td>32</td> <td>Password Data (plain-text or MD5 hash depending on settings)</td> </tr> <tr> <td>name</td> <td>VARCHAR</td> <td>100</td> <td>Name</td> </tr> <tr> <td>email</td> <td>VARCHAR</td> <td>100</td> <td>Email Address</td> </tr> <tr> <td>creationDate</td> <td>VARCHAR</td> <td>15</td> <td>Creation Date</td> </tr> <tr> <td>modificationDate</td> <td>VARCHAR</td> <td>15</td> <td>Last Modified Date</td> </tr> </tbody> </table> <a href="#top" class="top">top of page</a> <br> <br> <br> <a name="jiveUserProp"></a> <table class="dbtable"> <tbody> <tr> <th colspan="4">jiveUserProp (name-value associations for a User)</th> </tr> <tr> <th>Column Name</th> <th>Type</th> <th>Length</th> <th>Description</th> </tr> <tr class="primary-key"> <td>username</td> <td>VARCHAR</td> <td>32</td> <td>User Name (Primary Key)</td> </tr> <tr class="primary-key"> <td>name</td> <td>VARCHAR</td> <td>100</td> <td>User Property Name (Primary Key)</td> </tr> <tr> <td>propValue</td> <td>VARCHAR</td> <td>4000</td> <td>User Property Value</td> </tr> </tbody> </table> <a href="#top" class="top">top of page</a> <br> <br> <br> <a name="jiveRoster"></a> <table class="dbtable"> <tbody> <tr> <th colspan="4">jiveRoster (buddy rosters or lists)</th> </tr> <tr> <th>Column Name</th> <th>Type</th> <th>Length</th> <th>Description</th> </tr> <tr class="primary-key"> <td>rosterID</td> <td>NUMBER</td> <td>n/a</td> <td>ID of roster (Primary Key)</td> </tr> <tr> <td>username</td> <td>VARCHAR</td> <td>32</td> <td>User Name</td> </tr> <tr> <td style="vertical-align: top;">jid<br> </td> <td style="vertical-align: top;">TEXT<br> </td> <td style="vertical-align: top;">n/a<br> </td> <td style="vertical-align: top;">The address of the roster entry<br> </td> </tr> <tr> <td style="vertical-align: top;">sub<br> </td> <td style="vertical-align: top;">NUMBER<br> </td> <td style="vertical-align: top;">n/a<br> </td> <td style="vertical-align: top;">The subscription status of the entry<br> </td> </tr> <tr> <td style="vertical-align: top;">ask<br> </td> <td style="vertical-align: top;">NUMBER<br> </td> <td style="vertical-align: top;">n/a<br> </td> <td style="vertical-align: top;">The ask status of the entry<br> </td> </tr> <tr> <td style="vertical-align: top;">recv<br> </td> <td style="vertical-align: top;">NUMBER<br> </td> <td style="vertical-align: top;">n/a<br> </td> <td style="vertical-align: top;">Flag indicating the entry is a roster request that was received<br> </td> </tr> <tr> <td style="vertical-align: top;">nick<br> </td> <td style="vertical-align: top;">VARCHAR<br> </td> <td style="vertical-align: top;">255<br> </td> <td style="vertical-align: top;">The nickname assigned to this roster entry<br> </td> </tr> </tbody> </table> <a href="#top" class="top">top of page</a> <br> <br> <br> <a name="jiveRosterGroups"></a> <table class="dbtable"> <tbody> <tr> <th colspan="4">jiveRosterGroups (Groups of buddy entries in a roster)</th> </tr> <tr> <th>Column Name</th> <th>Type</th> <th>Length</th> <th>Description</th> </tr> <tr class="primary-key"> <td>rosterID</td> <td>NUMBER</td> <td>n/a</td> <td>Roster ID (Primary Key)</td> </tr> <tr class="primary-key"> <td>rank</td> <td>NUMBER</td> <td>n/a</td> <td>Position of the entry (Primary Key)</td> </tr> <tr> <td>groupName</td> <td>VARCHAR</td> <td>255</td> <td>The user defined name for this roster group</td> </tr> </tbody> </table> <a href="#top" class="top">top of page</a> <br> <br> <br> <a name="jivePrivacy"></a> <table class="dbtable"> <tbody> <tr> <th colspan="4">jivePrivacyList (Users privacy lists)</th> </tr> <tr> <th>Column Name</th> <th>Type</th> <th>Length</th> <th>Description</th> </tr> <tr class="primary-key"> <td>username</td> <td>VARCHAR</td> <td>32</td> <td>User Name (Primary Key)</td> </tr> <tr class="primary-key"> <td>name</td> <td>VARCHAR</td> <td>100</td> <td>Name of the privacy list (Primary Key)</td> </tr> <tr> <td>isDefault</td> <td>NUMBER</td> <td>n/a</td> <td>Flag indicating if this is the default privacy list of the user</td> </tr> <tr> <td>list</td> <td>TEXT</td> <td>n/a</td> <td>XML representation of the privacy list</td> </tr> </tbody> </table> <a href="#top" class="top">top of page</a> <br> <br> <br> <a name="jiveVCard"></a> <table class="dbtable"> <tbody> <tr> <th colspan="4">jiveVCard (vCard contact information)</th> </tr> <tr> <th>Column Name</th> <th>Type</th> <th>Length</th> <th>Description</th> </tr> <tr class="primary-key"> <td>username</td> <td>VARCHAR</td> <td>32</td> <td>User Name (Primary Key)</td> </tr> <tr class="primary-key"> <td>name</td> <td>VARCHAR</td> <td>100</td> <td>Name of the vCard entry (Primary Key)</td> </tr> <tr> <td>propValue</td> <td>TEXT</td> <td>n/a</td> <td>Value of the vCard entry</td> </tr> </tbody> </table> <a href="#top" class="top">top of page</a> <br> <br> <br> <a name="jiveProperty"></a> <table class="dbtable"> <tbody> <tr> <th colspan="4">jiveProperty (server properties)</th> </tr> <tr> <th>Column Name</th> <th>Type</th> <th>Length</th> <th>Description</th> </tr> <tr class="primary-key"> <td>name</td> <td>VARCHAR</td> <td>100</td> <td>Property Name (Primary Key)</td> </tr> <tr> <td>propValue</td> <td>TEXT</td> <td>n/a</td> <td>Value of the vCard entry</td> </tr> </tbody> </table> <a href="#top" class="top">top of page</a> <br> <br> <br> <a name="jiveExtComponentConf"></a> <table class="dbtable"> <tbody> <tr> <th colspan="4">jiveExtComponentConf (external components configuration)</th> </tr> <tr> <th>Column Name</th> <th>Type</th> <th>Length</th> <th>Description</th> </tr> <tr class="primary-key"> <td>subdomain</td> <td>VARCHAR</td> <td>255</td> <td>Subdomain of the external component (Primary Key)</td> </tr> <tr> <td>secret</td> <td>VARCHAR</td> <td>255</td> <td>Shared secret key of the external component</td> </tr> <tr> <td>permission</td> <td>VARCHAR</td> <td>10</td> <td>Permission that indicates if the component is allowed to connect to the server</td> </tr> </tbody> </table> <a href="#top" class="top">top of page</a> <br> <br> <br> <a name="jiveRemoteServerConf"></a> <table class="dbtable"> <tbody> <tr> <th colspan="4">jiveRemoteServerConf (remote servers configuration)</th> </tr> <tr> <th>Column Name</th> <th>Type</th> <th>Length</th> <th>Description</th> </tr> <tr class="primary-key"> <td>domain</td> <td>VARCHAR</td> <td>255</td> <td>Domain of the external component (Primary Key)</td> </tr> <tr> <td>remotePort</td> <td>NUMBER</td> <td>n/a</td> <td>Port of the remote server to connect to</td> </tr> <tr> <td>permission</td> <td>VARCHAR</td> <td>10</td> <td>Permission that indicates if the remote server is allowed to connect to the server</td> </tr> </tbody> </table> <a href="#top" class="top">top of page</a> <br> <br> <br> <a name="mucRoom"></a> <table class="dbtable"> <tbody> <tr> <th colspan="4">mucRoom (Groupchat room data)</th> </tr> <tr> <th>Column Name</th> <th>Type</th> <th>Length</th> <th>Description</th> </tr> <tr class="primary-key"> <td>roomID</td> <td>NUMBER</td> <td>n/a</td> <td>ID of room (Primary Key)</td> </tr> <tr> <td>creationDate</td> <td>VARCHAR</td> <td>15</td> <td>Creation Date</td> </tr> <tr> <td>modificationDate</td> <td>VARCHAR</td> <td>15</td> <td>Last Modified Date</td> </tr> <tr> <td>name</td> <td>VARCHAR</td> <td>50</td> <td>Name of the room used as the public ID</td> </tr> <tr> <td>naturalName</td> <td>VARCHAR</td> <td>255</td> <td>Natural name of the room</td> </tr> <tr> <td>description</td> <td>VARCHAR</td> <td>255</td> <td>Room Description</td> </tr> <tr> <td>canChangeSubject</td> <td>NUMBER</td> <td>n/a</td> <td>Flag indicating whether participants can change the subject</td> </tr> <tr> <td>maxUsers</td> <td>NUMBER</td> <td>n/a</td> <td>Max number of room occupants</td> </tr> <tr> <td>canChangeSubject</td> <td>NUMBER</td> <td>n/a</td> <td>Flag indicating whether participants can change the subject or not</td> </tr> <tr> <td>publicRoom</td> <td>NUMBER</td> <td>n/a</td> <td>Flag indicating whether the room will be listed in the directory or not</td> </tr> <tr> <td>moderated</td> <td>NUMBER</td> <td>n/a</td> <td>Flag indicating whether the room is moderated or not</td> </tr> <tr> <td>invitationRequired</td> <td>NUMBER</td> <td>n/a</td> <td>Flag indicating whether the room is members-only or not</td> </tr> <tr> <td>canInvite</td> <td>NUMBER</td> <td>n/a</td> <td>Flag indicating whether occupants can invite other users</td> </tr> <tr> <td>password</td> <td>VARCHAR</td> <td>50</td> <td>Password Data for joining the room</td> </tr> <tr> <td>canDiscoverJID</td> <td>NUMBER</td> <td>n/a</td> <td>Flag indicating whether real JID of occupants is public or not</td> </tr> <tr> <td>logEnabled</td> <td>NUMBER</td> <td>n/a</td> <td>Flag indicating whether room conversations are logged or not</td> </tr> <tr> <td>subject</td> <td>VARCHAR</td> <td>100</td> <td>Last known subject of the room</td> </tr> <tr> <td>rolesToBroadcast</td> <td>NUMBER</td> <td>n/a</td> <td>Binary representation of the roles to broadcast</td> </tr> <tr> <td>useReservedNick</td> <td>NUMBER</td> <td>n/a</td> <td>Flag indicating whether users can only join the room using their reserved nicknames</td> </tr> <tr> <td>canChangeNick</td> <td>NUMBER</td> <td>n/a</td> <td>Flag indicating whether occupants can change their nicknames in the room</td> </tr> <tr> <td>canRegister</td> <td>NUMBER</td> <td>n/a</td> <td>Flag indicating whether users are allowed to register with the room</td> </tr> </tbody> </table> <a href="#top" class="top">top of page</a> <br> <br> <br> <a name="mucRoomProp"></a> <table class="dbtable"> <tbody> <tr> <th colspan="4">mucRoomProp (name-value associations for a Groupchat room)</th> </tr> <tr> <th>Column Name</th> <th>Type</th> <th>Length</th> <th>Description</th> </tr> <tr class="primary-key"> <td>roomID</td> <td>NUMBER</td> <td>n/a</td> <td>ID of room (Primary Key)</td> </tr> <tr class="primary-key"> <td>name</td> <td>VARCHAR</td> <td>100</td> <td>Property Name (Primary Key)</td> </tr> <tr> <td>propValue</td> <td>VARCHAR</td> <td>4000</td> <td>Property Value</td> </tr> </tbody> </table> <a href="#top" class="top">top of page</a> <br> <br> <br> <a name="mucAffiliation"></a> <table class="dbtable"> <tbody> <tr> <th colspan="4">mucAffiliation (affiliation of room users)</th> </tr> <tr> <th>Column Name</th> <th>Type</th> <th>Length</th> <th>Description</th> </tr> <tr class="primary-key"> <td>roomID</td> <td>NUMBER</td> <td>n/a</td> <td>ID of room (Primary Key)</td> </tr> <tr class="primary-key"> <td>jid</td> <td>TEXT</td> <td>n/a</td> <td>User JID (Primary Key)</td> </tr> <tr> <td>affiliation</td> <td>NUMBER</td> <td>n/a</td> <td>Number representing the affiliation level</td> </tr> </tbody> </table> <a href="#top" class="top">top of page</a> <br> <br> <br> <a name="mucMember"></a> <table class="dbtable"> <tbody> <tr> <th colspan="4">mucMember (rooms members information)</th> </tr> <tr> <th>Column Name</th> <th>Type</th> <th>Length</th> <th>Description</th> </tr> <tr class="primary-key"> <td>roomID</td> <td>NUMBER</td> <td>n/a</td> <td>ID of room (Primary Key)</td> </tr> <tr class="primary-key"> <td>jid</td> <td>TEXT</td> <td>n/a</td> <td>User JID (Primary Key)</td> </tr> <tr> <td>nickname</td> <td>VARCHAR</td> <td>255</td> <td>Reserved nickname of the member</td> </tr> </tbody> </table> <a href="#top" class="top">top of page</a> <br> <br> <br> <a name="mucConversationLog"></a> <table class="dbtable"> <tbody> <tr> <th colspan="4">mucConversationLog (rooms conversations log)</th> </tr> <tr> <th>Column Name</th> <th>Type</th> <th>Length</th> <th>Description</th> </tr> <tr> <td>roomID</td> <td>NUMBER</td> <td>n/a</td> <td>ID of room</td> </tr> <tr> <td>sender</td> <td>TEXT</td> <td>n/a</td> <td>JID of the user that sent the message to the room</td> </tr> <tr> <td>nickname</td> <td>VARCHAR</td> <td>255</td> <td>Nickname used by the user when sending the message</td> </tr> <tr> <td>time</td> <td>VARCHAR</td> <td>15</td> <td>Date when the message was sent to the room</td> </tr> <tr> <td>subject</td> <td>VARCHAR</td> <td>50</td> <td>New subject changed with the message</td> </tr> <tr> <td>body</td> <td>TEXT</td> <td>n/a</td> <td>Body of the message</td> </tr> </tbody> </table> <a href="#top" class="top">top of page</a> </body> </html>