转载

openfire数据库模式

介绍

本文概述了数据类型规则和表格的Openfire数据库架构。有些信息，如列索引和外键被省略。

数据类型

数据库支持日期列类型。然而，Openfire的特别编码为VARCHAR值的日期。每一个日期是一个Java long值是0填充到15个字符。长的值是Java的Date对象的内部表示，可以得到如下面的代码：long currentDate = new Date().getTime();布尔值总是为代表的数字值：0为假，1真

数据库表

下面是一个描述的Openfire数据库架构中的表。黄色的行表示一个主键。

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
email	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