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.
| ofGroup (user Group data) |
| Column Name |
Type |
Length |
Description |
| groupName |
VARCHAR |
50 |
Group Name (Primary Key) |
| description |
VARCHAR |
255 |
Group Description |
top of page
| ofGroupProp (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
| ofGroupUser (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
| ofID (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
| ofOffline (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
|
stanza
|
TEXT
|
n/a
|
The message text
|
top of page
| ofPresence (offline presence) |
| Column Name |
Type |
Length |
Change |
| username |
VARCHAR |
64 |
User Name (Primary Key) |
offlinePresence
|
TEXT
|
n/a
|
Presence message set as user logged off
|
offlineDate
|
CHAR
|
15
|
Date message stored
|
top of page
| ofPrivate (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) |
| privateData |
TEXT |
n/a |
Value of the private data |
top of page
| ofUser (User data) |
| Column Name |
Type |
Length |
Description |
| username |
VARCHAR |
32 |
User Name (Primary Key) |
| plainPassword |
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
| ofUserProp (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
| ofUserFlag (special flags set on a User (like disabled)) |
| Column Name |
Type |
Length |
Description |
| username |
VARCHAR |
64 |
User Name (Primary Key) |
| name |
VARCHAR |
100 |
User Property Name (Primary Key) |
| startTime |
CHAR |
15 |
Time when the flag is to start being effective (null for 'now') |
| endTime |
CHAR |
15 |
Time when the flag is to end being effective (null for 'forever') |
top of page
| ofRoster (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
| ofRosterGroups (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
| ofPrivacyList (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
| ofVCard (vCard contact information) |
| Column Name |
Type |
Length |
Description |
| username |
VARCHAR |
32 |
User Name (Primary Key) |
| vcard |
TEXT |
n/a |
Value of the vCard entry |
top of page
| ofVersion (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
| ofProperty (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
| ofExtComponentConf (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
| ofRemoteServerConf (remote servers configuration) |
| Column Name |
Type |
Length |
Description |
| xmppDomain |
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
| ofSecurityAuditLog (logging of security events) |
| Column Name |
Type |
Length |
Description |
| msgID |
NUMBER |
n/a |
ID of audit message (Primary Key) |
| username |
VARCHAR |
64 |
user who performed the action |
| entryStamp |
NUMBER |
n/a |
Timestamp when event occurred |
| summary |
VARCHAR |
255 |
Summary of what occured in event |
| node |
VARCHAR |
255 |
Node where event occurred |
| details |
TEXT |
n/a |
Verbose details of what occurred |
top of page
| ofMucService (A Groupchat service) |
| Column Name |
Type |
Length |
Description |
| serviceID |
NUMBER |
n/a |
ID of service (Indexed) |
| subdomain |
VARCHAR |
255 |
Subdomain of service (Primary Key) |
| description |
VARCHAR |
255 |
Description of service |
| isHidden |
NUMBER |
n/a |
1 if hidden from admin interface lists, 0 of normal |
top of page
| ofMucServiceProp (name-value associations for a Groupchat service) |
| Column Name |
Type |
Length |
Description |
| serviceID |
NUMBER |
n/a |
ID of service (Primary Key) |
| name |
VARCHAR |
100 |
Property Name (Primary Key) |
| propValue |
TEXT |
n/a |
Property Value |
top of page
| ofMucRoom (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 |
| roomPassword |
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
| ofMucRoomProp (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
| ofMucAffiliation (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
| ofMucMember (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
| ofMucConversationLog (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 |
| logTime |
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
| ofPubsubNode (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
| ofPubsubNodeJIDs (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
| ofPubsubNodeGroups (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
| ofPubsubAffiliation (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
| ofPubsubItem (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
| ofPubsubSubscription (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
| ofPubsubDefaultConf (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