# Database Schema Guide

## Introduction

This document outlines the data type conventions and tables in the Openfire 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, Openfire 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 Openfire database schema. A yellow row denotes a primary key.

- jiveGroup
- jiveGroupProp
- jiveGroupUser
- jiveID
- jiveOffline
- jivePrivate
- jiveUser
- jiveUserProp
- jiveRoster
- jiveRosterGroups
- jivePrivacyList
- jiveVCard
- jiveVersion
- jiveProperty
- jiveExtComponentConf
- jiveRemoteServerConf
- mucRoom
- mucRoomProp
- mucAffiliation
- mucMember
- mucConversationLog
- pubsubNode
- pubsubNodeJIDs
- pubsubNodeGroups
- pubsubAffiliation
- pubsubItem
- pubsubSubscription
- pubsubDefaultConf

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 | 100 | 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 | Plain-text password data
encryptedPassword | VARCHAR | 255 | Encrypted password data (default)
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

jivePrivacyList (Users privacy lists)
Column Name | Type | Length | Description
--- | --- | --- | ---
username | VARCHAR | 32 | User Name (Primary Key)
name | VARCHAR | 100 | Name of the privacy list (Primary Key)
isDefault | NUMBER | n/a | Flag indicating if this is the default privacy list of the user
list | TEXT | n/a | XML representation of the privacy list

top of page

jiveVCard (vCard contact information)
Column Name | Type | Length | Description
--- | --- | --- | ---
username | VARCHAR | 32 | User Name (Primary Key)
value | TEXT | n/a | Value of the vCard entry

top of page

jiveVersion (contains product version information)
Column Name | Type | Length | Description
--- | --- | --- | ---
name | VARCHAR | 50 | Name of the item that version information is being tracked for (Primary Key)
version | INTEGER | n/a | The version number

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 entry

top of page

jiveExtComponentConf (external components configuration)
Column Name | Type | Length | Description
--- | --- | --- | ---
subdomain | VARCHAR | 255 | Subdomain of the external component (Primary Key)
secret | VARCHAR | 255 | Shared secret key of the external component
permission | VARCHAR | 10 | Permission that indicates if the component is allowed to connect to the server

top of page

jiveRemoteServerConf (remote servers configuration)
Column Name | Type | Length | Description
--- | --- | --- | ---
domain | VARCHAR | 255 | Domain of the external component (Primary Key)
remotePort | NUMBER | n/a | Port of the remote server to connect to
permission | VARCHAR | 10 | Permission that indicates if the remote server is allowed to connect to the server

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
membersOnly | 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
useReservedNick | NUMBER | n/a | Flag indicating whether users can only join the room using their reserved nicknames
canChangeNick | NUMBER | n/a | Flag indicating whether occupants can change their nicknames in the room
canRegister | NUMBER | n/a | Flag indicating whether users are allowed to register with the room

top of page

mucRoomProp (name-value associations for a Groupchat room)
Column Name | Type | Length | Description
--- | --- | --- | ---
roomID | NUMBER | n/a | ID of room (Primary Key)
name | VARCHAR | 100 | Property Name (Primary Key)
propValue | VARCHAR | 4000 | Property Value

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

pubsubNode (nodes of the pubsub service)
Column Name | Type | Length | Description
--- | --- | --- | ---
serviceID | VARCHAR | 100 | ID of service hosting the node (Primary Key)
nodeID | VARCHAR | 100 | ID of the node (Primary Key)
leaf | NUMBER | n/a | Flag indicating whether the node is a leaf or collection node
creationDate | VARCHAR | 15 | Creation Date
modificationDate | VARCHAR | 15 | Last Modified Date
parent | VARCHAR | 100 | ID of the parent node (if any)
deliverPayloads | NUMBER | n/a | Flag indicating whether payloads are included in notifications
maxPayloadSize | NUMBER | n/a | Max size of the payload in bytes
persistItems | NUMBER | n/a | Flag indicating whether the node will persist published items
maxItems | NUMBER | n/a | Max number of items to persist
notifyConfigChanges | NUMBER | n/a | Flag indicating whether to send notifications when the node configuration has changed
notifyDelete | NUMBER | n/a | Flag indicating whether to send notifications when the node is deleted
notifyRetract | NUMBER | n/a | Flag indicating whether to send notifications when published items are deleted
presenceBased | NUMBER | n/a | Flag indicating whether to send notifications to only users only
sendItemSubscribe | NUMBER | n/a | Flag indicating whether to send last published item to new subscribers
publisherModel | VARCHAR | 15 | Publisher model used by the node
subscriptionEnabled | NUMBER | n/a | Flag indicating whether subscriptions are allowed
configSubscription | NUMBER | n/a | Flag indicating whether new subscriptions must be configured to become active
accessModel | VARCHAR | 10 | Access model used by the node
payloadType | VARCHAR | 100 | Type of payload data to be provided at the node
bodyXSLT | VARCHAR | 100 | URL of an XSLT for transforming the payload format into a message body
dataformXSLT | VARCHAR | 100 | URL of an XSLT for transforming the payload format into Data Forms result
creator | VARCHAR | 1024 | JID of the entity that created the node
description | VARCHAR | 255 | Description of the node
language | VARCHAR | 255 | Default language of the node
name | VARCHAR | 50 | Name of the node
replyPolicy | VARCHAR | 15 | Policy that defines whether owners or publisher should receive replies to items
associationPolicy | VARCHAR | 15 | Policy that defines who may associate leaf nodes with a collection
maxLeafNodes | NUMBER | n/a | Max number of leaf nodes that a collection node might have

top of page

pubsubNodeJIDs (JIDs associated with nodes)
Column Name | Type | Length | Description
--- | --- | --- | ---
serviceID | VARCHAR | 100 | ID of service hosting the node (Primary Key)
nodeID | VARCHAR | 100 | ID of the node (Primary Key)
jid | VARCHAR | 1024 | JID of the entity (Primary Key)
associationType | VARCHAR | 20 | Type of association with the node

top of page

pubsubNodeGroups (Roster groups associated with nodes)
Column Name | Type | Length | Description
--- | --- | --- | ---
serviceID | VARCHAR | 100 | ID of service hosting the node
nodeID | VARCHAR | 100 | ID of the node
rosterGroup | VARCHAR | 100 | Roster group of the node owner allowed to subscribe and retrieve items

top of page

pubsubAffiliation (node affiliates)
Column Name | Type | Length | Description
--- | --- | --- | ---
serviceID | VARCHAR | 100 | ID of service hosting the node (Primary Key)
nodeID | VARCHAR | 100 | ID of the node (Primary Key)
jid | VARCHAR | 1024 | JID of the affiliate (Primary Key)
affiliation | VARCHAR | 10 | Type of affiliation

top of page

pubsubItem (items published to nodes)
Column Name | Type | Length | Description
--- | --- | --- | ---
serviceID | VARCHAR | 100 | ID of service hosting the node (Primary Key)
nodeID | VARCHAR | 100 | ID of the node (Primary Key)
id | VARCHAR | 100 | ID of the published item (unique per node) (Primary Key)
jid | VARCHAR | 1024 | JID of the publisher
creationDate | VARCHAR | 15 | Creation Date
payload | TEXT | n/a | XML of the payload included in the published item

top of page

pubsubSubscription (subscriptions to nodes)
Column Name | Type | Length | Description
--- | --- | --- | ---
serviceID | VARCHAR | 100 | ID of service hosting the node (Primary Key)
nodeID | VARCHAR | 100 | ID of the node (Primary Key)
id | VARCHAR | 100 | ID of the subscription (Primary Key)
jid | VARCHAR | 1024 | Address to receive notifications
owner | VARCHAR | 1024 | JID of the affiliate that owns the subscription
state | VARCHAR | 15 | State of the subscription (in the workflow)
deliver | NUMBER | n/a | Flag indicating whether notifications are enabled or not
digest | NUMBER | n/a | Flag indicating whether an entity wants to receive digests of notifications
digest_frequency | NUMBER | n/a | Minimum number of milliseconds between sending any two notification digests
expire | VARCHAR | 15 | Date at which a leased subscription will end or has ended
includeBody | NUMBER | n/a | Flag indicating whether an entity wants to receive a message body in addition to the payload format
showValues | VARCHAR | 30 | Presence states for which an entity wants to receive notifications
subscriptionType | VARCHAR | 10 | Whether subscriber is subscribed to items or nodes (collection nodes only)
subscriptionDepth | NUMBER | n/a | Receive notification from children up to certain depth (collection nodes only)
keyword | VARCHAR | 200 | Keyword that the event needs to match

top of page

pubsubDefaultConf (default configuration of nodes)
Column Name | Type | Length | Description
--- | --- | --- | ---
serviceID | VARCHAR | 100 | ID of service hosting the node (Primary Key)
leaf | NUMBER | n/a | Flag indicating whether configuration belongs to a leaf or collection node (Primary Key)
deliverPayloads | NUMBER | n/a | Flag indicating whether payloads are included in notifications
maxPayloadSize | NUMBER | n/a | Max size of the payload in bytes
persistItems | NUMBER | n/a | Flag indicating whether the node will persist published items
maxItems | NUMBER | n/a | Max number of items to persist
notifyConfigChanges | NUMBER | n/a | Flag indicating whether to send notifications when the node configuration has changed
notifyDelete | NUMBER | n/a | Flag indicating whether to send notifications when the node is deleted
notifyRetract | NUMBER | n/a | Flag indicating whether to send notifications when published items are deleted
presenceBased | NUMBER | n/a | Flag indicating whether to send notifications to only users only
sendItemSubscribe | NUMBER | n/a | Flag indicating whether to send last published item to new subscribers
publisherModel | VARCHAR | 15 | Publisher model used by the node
subscriptionEnabled | NUMBER | n/a | Flag indicating whether subscriptions are allowed
accessModel | VARCHAR | 10 | Access model used by the node
language | VARCHAR | 255 | Default language of the node
replyPolicy | VARCHAR | 15 | Policy that defines whether owners or publisher should receive replies to items
associationPolicy | VARCHAR | 15 | Policy that defines who may associate leaf nodes with a collection
maxLeafNodes | NUMBER | n/a | Max number of leaf nodes that a collection node might have

top of page