Jive Messenger Database Schema Guide

Introduction

This document outlines the data type conventions and tables in the Jive Messenger 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.

Data Type Conventions

Date column type support varies widely across databases. Therefore, Jive Messenger 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: 

long currentDate = new Date().getTime();

Boolean values are always represented a numeric values: 0 for false and 1 for true.

Database Tables

Below is a description of each of the tables in the Jive Messenger database schema. A yellow row denotes a primary key.


jiveGroup (user Group data)
Column Name Type Length Description
groupName VARCHAR 50 Group Name (Primary Key)
description VARCHAR 255 Group Description
 top of page


jiveGroupProp (name-value associations for a Group)
Column Name Type Length Description
groupName VARCHAR 50 Group Name (Primary Key)
name VARCHAR 100 Group Property Name (Primary Key)
propValue VARCHAR 4000 Group Property Value
 top of page


jiveGroupUser (associates Users with Groups)
Column Name Type Length Description
groupName VARCHAR 50 Group Name (Primary Key)
username VARCHAR 32 User Name (Primary Key)
administrator NUMBER n/a Adminstrator (Boolean) (Primary Key)
 top of page


jiveID (used for unique ID sequence generation)
Column Name Type Length Description
idType NUMBER n/a ID type (e.g., Group, User, Roster) (Primary Key)
id NUMBER n/a Next available block of ID’s (Used for Database-Independent ID Sequence Generator)
 top of page


jiveOffline (offline message storage)
Column Name Type Length Change
username VARCHAR 32 User Name (Primary Key)
messageID
 NUMBER
 n/a
 ID of stored message (Primary Key)
creationDate
 VARCHAR
 15
 Date message stored
messageSize
 NUMBER
 n/a
 Size of message in bytes
message
 TEXT
 n/a
 The message text
 top of page


jivePrivate (Private data storage)
Column Name Type Length Description
username VARCHAR 32 User Name (Primary Key)
name VARCHAR 100 Name of the private entry (Primary Key)
namespace VARCHAR 200 Namespace of the private entry (Primary Key)
value TEXT n/a Value of the private data
 top of page



jiveUser (User data)
Column Name Type Length Description
username VARCHAR 32 User Name (Primary Key)
password VARCHAR 32 Password Data (plain-text or MD5 hash depending on settings)
name VARCHAR 100 Name
email VARCHAR 100 Email Address
creationDate VARCHAR 15 Creation Date
modificationDate VARCHAR 15 Last Modified Date
 top of page


jiveUserProp (name-value associations for a User)
Column Name Type Length Description
username VARCHAR 32 User Name (Primary Key)
name VARCHAR 100 User Property Name (Primary Key)
propValue VARCHAR 4000 User Property Value
 top of page


jiveRoster (buddy rosters or lists)
Column Name Type Length Description
rosterID NUMBER n/a ID of roster (Primary Key)
username VARCHAR 32 User Name
jid
 TEXT
 n/a
 The address of the roster entry
sub
 NUMBER
 n/a
 The subscription status of the entry
ask
 NUMBER
 n/a
 The ask status of the entry
recv
 NUMBER
 n/a
 Flag indicating the entry is a roster request that was received
nick
 VARCHAR
 255
 The nickname assigned to this roster entry
 top of page


jiveRosterGroups (Groups of buddy entries in a roster)
Column Name Type Length Description
rosterID NUMBER n/a Roster ID (Primary Key)
rank NUMBER n/a Position of the entry (Primary Key)
groupName VARCHAR 255 The user defined name for this roster group
 top of page


jiveVCard (vCard contact information)
Column Name Type Length Description
username VARCHAR 32 User Name (Primary Key)
name VARCHAR 100 Name of the vCard entry (Primary Key)
propValue TEXT n/a Value of the vCard entry
 top of page


jiveProperty (server properties)
Column Name Type Length Description
name VARCHAR 100 Property Name (Primary Key)
propValue TEXT n/a Value of the vCard entry
 top of page


mucRoom (Groupchat room data)
Column Name Type Length Description
roomID NUMBER n/a ID of room (Primary Key)
creationDate VARCHAR 15 Creation Date
modificationDate VARCHAR 15 Last Modified Date
name VARCHAR 50 Name of the room used as the public ID
naturalName VARCHAR 255 Natural name of the room
description VARCHAR 255 Room Description
canChangeSubject NUMBER n/a Flag indicating whether participants can change the subject
maxUsers NUMBER n/a Max number of room occupants
canChangeSubject NUMBER n/a Flag indicating whether participants can change the subject or not
publicRoom NUMBER n/a Flag indicating whether the room will be listed in the directory or not
moderated NUMBER n/a Flag indicating whether the room is moderated or not
invitationRequired NUMBER n/a Flag indicating whether the room is members-only or not
canInvite NUMBER n/a Flag indicating whether occupants can invite other users
password VARCHAR 50 Password Data for joining the room
canDiscoverJID NUMBER n/a Flag indicating whether real JID of occupants is public or not
logEnabled NUMBER n/a Flag indicating whether room conversations are logged or not
subject VARCHAR 100 Last known subject of the room
rolesToBroadcast NUMBER n/a Binary representation of the roles to broadcast
 top of page


mucAffiliation (affiliation of room users)
Column Name Type Length Description
roomID NUMBER n/a ID of room (Primary Key)
jid TEXT n/a User JID (Primary Key)
affiliation NUMBER n/a Number representing the affiliation level
 top of page


mucMember (rooms members information)
Column Name Type Length Description
roomID NUMBER n/a ID of room (Primary Key)
jid TEXT n/a User JID (Primary Key)
nickname VARCHAR 255 Reserved nickname of the member
 top of page


mucConversationLog (rooms conversations log)
Column Name Type Length Description
roomID NUMBER n/a ID of room
sender TEXT n/a JID of the user that sent the message to the room
nickname VARCHAR 255 Nickname used by the user when sending the message
time VARCHAR 15 Date when the message was sent to the room
subject VARCHAR 50 New subject changed with the message
body TEXT n/a Body of the message
 top of page