Openfire Logo

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:

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