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