在学习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 (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