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.
Topics that are covered in this document:
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 |
| 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 |
| 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) |
| 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) |
| 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 |
| 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 |
| 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 |
| 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 |
| VARCHAR | 100 | Email Address | |
| creationDate | VARCHAR | 15 | Creation Date |
| modificationDate | VARCHAR | 15 | Last Modified Date |
| 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 |
| 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') |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| ofProperty (server properties) | |||
|---|---|---|---|
| Column Name | Type | Length | Description |
| name | VARCHAR | 100 | Property Name (Primary Key) |
| propValue | TEXT | n/a | Value of the entry |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |