Jive Messenger Database Schema Guide

Introduction

This document outlines the data type conventions and tables in the Jive Messenger 1.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. The Messenger 1.0 schema is documented separately as well a 1.0 to 1.1 database upgrade guide for those that need to know the changes to database schema. An upgrade script is provided for each database in the database/upgrade directory of the distribution that automatically upgrade Messenger 1.0 databases to 1.1.

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.


jiveChatbot (Chatbot addresses)
Column Name Type Length Description
chatbotID NUMBER n/a Chatbot ID (Primary Key)
description VARCHAR 4000 Chatbot Description
creationDate VARCHAR 15 Creation Date
modificationDate VARCHAR 15 Last Modified Date
 top of page


jiveDomain (Messenger account domains)
Column Name Type Length Description
domainID NUMBER n/a Domain ID (Primary Key)
name VARCHAR 255 Domain Name
description VARCHAR 4000 Domain Description
creationDate VARCHAR 15 Creation Date
modificationDate VARCHAR 15 Last Modified Date
 top of page


jiveGroup (user Group data)
Column Name Type Length Description
groupID NUMBER n/a Group ID (Primary Key)
name VARCHAR 255 Group Name
description VARCHAR 4000 Group Description
creationDate VARCHAR 15 Creation Date
modificationDate VARCHAR 15 Last Modified Date
 top of page


jiveGroupProp (name-value associations for a Group)
Column Name Type Length Description
groupID NUMBER n/a Group ID
name VARCHAR 100 Group Property Name
propValue VARCHAR 4000 Group Property Value
 top of page


jiveGroupUser (associates Users with Groups)
Column Name Type Length Description
groupID NUMBER n/a Group ID (Primary Key)
userID NUMBER n/a User ID (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
userID NUMBER n/a User ID (Primary Key)
messageID
NUMBER
n/a
ID of stored message (Primary Key)
creationDate
VARCHAR
15
Date message stored
size
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
userID NUMBER n/a User ID (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
userID NUMBER n/a User ID (Primary Key)
password VARCHAR 32 Password Data (plain-text or MD5 hash depending on settings)
name VARCHAR 100 Name
nameVisible NUMBER n/a Is Name Visible (Boolean)
email VARCHAR 100 Email Address
emailVisible NUMBER n/a Is Email Visible (Boolean)
creationDate VARCHAR 15 Creation Date
modificationDate VARCHAR 15 Last Modified Date
 top of page


jiveUserID (User account IDs - address reservations)
Column Name Type Length Description
username VARCHAR 30
User name (Primary Key)
domainID NUMBER n/a
The domain this account belongs to (Primary Key)
objectType NUMBER n/a
Address type (0 - user, 1 - chatbot)
objectID NUMBER n/a
ID of the corresponding account
 top of page


jiveUserProp (name-value associations for a User)
Column Name Type Length Description
userID NUMBER n/a User ID (Primary Key)
name VARCHAR 100 User Property Name
propValue NUMBER 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)
userID NUMBER n/a User ID
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
userID NUMBER n/a User ID (Primary Key)
name VARCHAR 100 Name of the vCard entry (Primary Key)
propValue TEXT n/a Value of the vCard entry
 top of page