openfire 数据库

在学习openfire 之前，先简单的了解一下支撑它运行的数据模式，以后在学习过程中，可能要经常反复的学习数据模式，了解数据处理流程和存储过程必不少的环节的。

标准文档如下，原文连接：http://www.igniterealtime.org/builds/openfire/docs/latest/documentation/database-guide.html

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
• ofGroupProp
• ofGroupUser
• ofID
• ofOffline
• ofPresence
• ofPrivate
• ofUser
• ofUserProp
• ofUserFlag
• ofRoster
• ofRosterGroups
• ofPrivacyList
• ofVCard
• ofVersion
• ofProperty
• ofExtComponentConf
• ofRemoteServerConf
• ofSecurityAuditLog
• ofMucService
• ofMucServiceProp
• ofMucRoom
• ofMucRoomProp
• ofMucAffiliation
• ofMucMember
• ofMucConversationLog
• ofPubsubNode
• ofPubsubNodeJIDs
• ofPubsubNodeGroups
• ofPubsubAffiliation
• ofPubsubItem
• ofPubsubSubscription
• ofPubsubDefaultConf

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