分享

openfire 数据库

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

标准文档如下,原文连接:http://www./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 NameTypeLengthDescription
groupNameVARCHAR50Group Name (Primary Key)
descriptionVARCHAR255Group Description
 top of page


ofGroupProp (name-value associations for a Group)
Column NameTypeLengthDescription
groupNameVARCHAR50Group Name (Primary Key)
nameVARCHAR100Group Property Name (Primary Key)
propValueVARCHAR4000Group Property Value
 top of page


ofGroupUser (associates Users with Groups)
Column NameTypeLengthDescription
groupNameVARCHAR50Group Name (Primary Key)
usernameVARCHAR100User Name (Primary Key)
administratorNUMBERn/aAdminstrator (Boolean) (Primary Key)
 top of page


ofID (used for unique ID sequence generation)
Column NameTypeLengthDescription
idTypeNUMBERn/aID type (e.g., Group, User, Roster) (Primary Key)
idNUMBERn/aNext available block of ID’s (Used for Database-Independent ID Sequence Generator)
 top of page


ofOffline (offline message storage)
Column NameTypeLengthChange
usernameVARCHAR32User Name (Primary Key)
messageIDNUMBERn/aID of stored message (Primary Key)
creationDateVARCHAR15Date message stored
messageSizeNUMBERn/aSize of message in bytes
stanzaTEXTn/aThe message text
 top of page


ofPresence (offline presence)
Column NameTypeLengthChange
usernameVARCHAR64User Name (Primary Key)
offlinePresenceTEXTn/aPresence message set as user logged off
offlineDateCHAR15Date message stored
 top of page


ofPrivate (Private data storage)
Column NameTypeLengthDescription
usernameVARCHAR32User Name (Primary Key)
nameVARCHAR100Name of the private entry (Primary Key)
namespaceVARCHAR200Namespace of the private entry (Primary Key)
privateDataTEXTn/aValue of the private data
 top of page



ofUser (User data)
Column NameTypeLengthDescription
usernameVARCHAR32User Name (Primary Key)
plainPasswordVARCHAR32Plain-text password data
encryptedPasswordVARCHAR255Encrypted password data (default)
nameVARCHAR100Name
emailVARCHAR100Email Address
creationDateVARCHAR15Creation Date
modificationDateVARCHAR15Last Modified Date
 top of page


ofUserProp (name-value associations for a User)
Column NameTypeLengthDescription
usernameVARCHAR32User Name (Primary Key)
nameVARCHAR100User Property Name (Primary Key)
propValueVARCHAR4000User Property Value
 top of page


ofUserFlag (special flags set on a User (like disabled))
Column NameTypeLengthDescription
usernameVARCHAR64User Name (Primary Key)
nameVARCHAR100User Property Name (Primary Key)
startTimeCHAR15Time when the flag is to start being effective (null for 'now')
endTimeCHAR15Time when the flag is to end being effective (null for 'forever')
 top of page


ofRoster (buddy rosters or lists)
Column NameTypeLengthDescription
rosterIDNUMBERn/aID of roster (Primary Key)
usernameVARCHAR32User Name
jidTEXTn/aThe address of the roster entry
subNUMBERn/aThe subscription status of the entry
askNUMBERn/aThe ask status of the entry
recvNUMBERn/aFlag indicating the entry is a roster request that was received
nickVARCHAR255The nickname assigned to this roster entry
 top of page


ofRosterGroups (Groups of buddy entries in a roster)
Column NameTypeLengthDescription
rosterIDNUMBERn/aRoster ID (Primary Key)
rankNUMBERn/aPosition of the entry (Primary Key)
groupNameVARCHAR255The user defined name for this roster group
 top of page


ofPrivacyList (Users privacy lists)
Column NameTypeLengthDescription
usernameVARCHAR32User Name (Primary Key)
nameVARCHAR100Name of the privacy list (Primary Key)
isDefaultNUMBERn/aFlag indicating if this is the default privacy list of the user
listTEXTn/aXML representation of the privacy list
 top of page


ofVCard (vCard contact information)
Column NameTypeLengthDescription
usernameVARCHAR32User Name (Primary Key)
vcardTEXTn/aValue of the vCard entry
 top of page


ofVersion (contains product version information)
Column NameTypeLengthDescription
nameVARCHAR50Name of the item that version information is being tracked for (Primary Key)
versionINTEGERn/aThe version number
 top of page


ofProperty (server properties)
Column NameTypeLengthDescription
nameVARCHAR100Property Name (Primary Key)
propValueTEXTn/aValue of the entry
 top of page


ofExtComponentConf (external components configuration)
Column NameTypeLengthDescription
subdomainVARCHAR255Subdomain of the external component (Primary Key)
secretVARCHAR255Shared secret key of the external component
permissionVARCHAR10Permission that indicates if the component is allowed to connect to the server
 top of page


ofRemoteServerConf (remote servers configuration)
Column NameTypeLengthDescription
xmppDomainVARCHAR255Domain of the external component (Primary Key)
remotePortNUMBERn/aPort of the remote server to connect to
permissionVARCHAR10Permission that indicates if the remote server is allowed to connect to the server
 top of page


ofSecurityAuditLog (logging of security events)
Column NameTypeLengthDescription
msgIDNUMBERn/aID of audit message (Primary Key)
usernameVARCHAR64user who performed the action
entryStampNUMBERn/aTimestamp when event occurred
summaryVARCHAR255Summary of what occured in event
nodeVARCHAR255Node where event occurred
detailsTEXTn/aVerbose details of what occurred
 top of page


ofMucService (A Groupchat service)
Column NameTypeLengthDescription
serviceIDNUMBERn/aID of service (Indexed)
subdomainVARCHAR255Subdomain of service (Primary Key)
descriptionVARCHAR255Description of service
isHiddenNUMBERn/a1 if hidden from admin interface lists, 0 of normal
 top of page


ofMucServiceProp (name-value associations for a Groupchat service)
Column NameTypeLengthDescription
serviceIDNUMBERn/aID of service (Primary Key)
nameVARCHAR100Property Name (Primary Key)
propValueTEXTn/aProperty Value
 top of page


ofMucRoom (Groupchat room data)
Column NameTypeLengthDescription
roomIDNUMBERn/aID of room (Primary Key)
creationDateVARCHAR15Creation Date
modificationDateVARCHAR15Last Modified Date
nameVARCHAR50Name of the room used as the public ID
naturalNameVARCHAR255Natural name of the room
descriptionVARCHAR255Room Description
canChangeSubjectNUMBERn/aFlag indicating whether participants can change the subject
maxUsersNUMBERn/aMax number of room occupants
canChangeSubjectNUMBERn/aFlag indicating whether participants can change the subject or not
publicRoomNUMBERn/aFlag indicating whether the room will be listed in the directory or not
moderatedNUMBERn/aFlag indicating whether the room is moderated or not
membersOnlyNUMBERn/aFlag indicating whether the room is members-only or not
canInviteNUMBERn/aFlag indicating whether occupants can invite other users
roomPasswordVARCHAR50Password Data for joining the room
canDiscoverJIDNUMBERn/aFlag indicating whether real JID of occupants is public or not
logEnabledNUMBERn/aFlag indicating whether room conversations are logged or not
subjectVARCHAR100Last known subject of the room
rolesToBroadcastNUMBERn/aBinary representation of the roles to broadcast
useReservedNickNUMBERn/aFlag indicating whether users can only join the room using their reserved nicknames
canChangeNickNUMBERn/aFlag indicating whether occupants can change their nicknames in the room
canRegisterNUMBERn/aFlag indicating whether users are allowed to register with the room
 top of page


ofMucRoomProp (name-value associations for a Groupchat room)
Column NameTypeLengthDescription
roomIDNUMBERn/aID of room (Primary Key)
nameVARCHAR100Property Name (Primary Key)
propValueVARCHAR4000Property Value
 top of page


ofMucAffiliation (affiliation of room users)
Column NameTypeLengthDescription
roomIDNUMBERn/aID of room (Primary Key)
jidTEXTn/aUser JID (Primary Key)
affiliationNUMBERn/aNumber representing the affiliation level
 top of page


ofMucMember (rooms members information)
Column NameTypeLengthDescription
roomIDNUMBERn/aID of room (Primary Key)
jidTEXTn/aUser JID (Primary Key)
nicknameVARCHAR255Reserved nickname of the member
 top of page


ofMucConversationLog (rooms conversations log)
Column NameTypeLengthDescription
roomIDNUMBERn/aID of room
senderTEXTn/aJID of the user that sent the message to the room
nicknameVARCHAR255Nickname used by the user when sending the message
logTimeVARCHAR15Date when the message was sent to the room
subjectVARCHAR50New subject changed with the message
bodyTEXTn/aBody of the message
 top of page


ofPubsubNode (nodes of the pubsub service)
Column NameTypeLengthDescription
serviceIDVARCHAR100ID of service hosting the node (Primary Key)
nodeIDVARCHAR100ID of the node (Primary Key)
leafNUMBERn/aFlag indicating whether the node is a leaf or collection node
creationDateVARCHAR15Creation Date
modificationDateVARCHAR15Last Modified Date
parentVARCHAR100ID of the parent node (if any)
deliverPayloadsNUMBERn/aFlag indicating whether payloads are included in notifications
maxPayloadSizeNUMBERn/aMax size of the payload in bytes
persistItemsNUMBERn/aFlag indicating whether the node will persist published items
maxItemsNUMBERn/aMax number of items to persist
notifyConfigChangesNUMBERn/aFlag indicating whether to send notifications when the node configuration has changed
notifyDeleteNUMBERn/aFlag indicating whether to send notifications when the node is deleted
notifyRetractNUMBERn/aFlag indicating whether to send notifications when published items are deleted
presenceBasedNUMBERn/aFlag indicating whether to send notifications to only users only
sendItemSubscribeNUMBERn/aFlag indicating whether to send last published item to new subscribers
publisherModelVARCHAR15Publisher model used by the node
subscriptionEnabledNUMBERn/aFlag indicating whether subscriptions are allowed
configSubscriptionNUMBERn/aFlag indicating whether new subscriptions must be configured to become active
accessModelVARCHAR10Access model used by the node
payloadTypeVARCHAR100Type of payload data to be provided at the node
bodyXSLTVARCHAR100URL of an XSLT for transforming the payload format into a message body
dataformXSLTVARCHAR100URL of an XSLT for transforming the payload format into Data Forms result
creatorVARCHAR1024JID of the entity that created the node
descriptionVARCHAR255Description of the node
languageVARCHAR255Default language of the node
nameVARCHAR50Name of the node
replyPolicyVARCHAR15Policy that defines whether owners or publisher should receive replies to items
associationPolicyVARCHAR15Policy that defines who may associate leaf nodes with a collection
maxLeafNodesNUMBERn/aMax number of leaf nodes that a collection node might have
 top of page


ofPubsubNodeJIDs (JIDs associated with nodes)
Column NameTypeLengthDescription
serviceIDVARCHAR100ID of service hosting the node (Primary Key)
nodeIDVARCHAR100ID of the node (Primary Key)
jidVARCHAR1024JID of the entity (Primary Key)
associationTypeVARCHAR20Type of association with the node
 top of page


ofPubsubNodeGroups (Roster groups associated with nodes)
Column NameTypeLengthDescription
serviceIDVARCHAR100ID of service hosting the node
nodeIDVARCHAR100ID of the node
rosterGroupVARCHAR100Roster group of the node owner allowed to subscribe and retrieve items
 top of page


ofPubsubAffiliation (node affiliates)
Column NameTypeLengthDescription
serviceIDVARCHAR100ID of service hosting the node (Primary Key)
nodeIDVARCHAR100ID of the node (Primary Key)
jidVARCHAR1024JID of the affiliate (Primary Key)
affiliationVARCHAR10Type of affiliation
 top of page


ofPubsubItem (items published to nodes)
Column NameTypeLengthDescription
serviceIDVARCHAR100ID of service hosting the node (Primary Key)
nodeIDVARCHAR100ID of the node (Primary Key)
idVARCHAR100ID of the published item (unique per node) (Primary Key)
jidVARCHAR1024JID of the publisher
creationDateVARCHAR15Creation Date
payloadTEXTn/aXML of the payload included in the published item
 top of page


ofPubsubSubscription (subscriptions to nodes)
Column NameTypeLengthDescription
serviceIDVARCHAR100ID of service hosting the node (Primary Key)
nodeIDVARCHAR100ID of the node (Primary Key)
idVARCHAR100ID of the subscription (Primary Key)
jidVARCHAR1024Address to receive notifications
ownerVARCHAR1024JID of the affiliate that owns the subscription
stateVARCHAR15State of the subscription (in the workflow)
deliverNUMBERn/aFlag indicating whether notifications are enabled or not
digestNUMBERn/aFlag indicating whether an entity wants to receive digests of notifications
digest_frequencyNUMBERn/aMinimum number of milliseconds between sending any two notification digests
expireVARCHAR15Date at which a leased subscription will end or has ended
includeBodyNUMBERn/aFlag indicating whether an entity wants to receive a message body in addition to the payload format
showValuesVARCHAR30Presence states for which an entity wants to receive notifications
subscriptionTypeVARCHAR10Whether subscriber is subscribed to items or nodes (collection nodes only)
subscriptionDepthNUMBERn/aReceive notification from children up to certain depth (collection nodes only)
keywordVARCHAR200Keyword that the event needs to match
 top of page


ofPubsubDefaultConf (default configuration of nodes)
Column NameTypeLengthDescription
serviceIDVARCHAR100ID of service hosting the node (Primary Key)
leafNUMBERn/aFlag indicating whether configuration belongs to a leaf or collection node (Primary Key)
deliverPayloadsNUMBERn/aFlag indicating whether payloads are included in notifications
maxPayloadSizeNUMBERn/aMax size of the payload in bytes
persistItemsNUMBERn/aFlag indicating whether the node will persist published items
maxItemsNUMBERn/aMax number of items to persist
notifyConfigChangesNUMBERn/aFlag indicating whether to send notifications when the node configuration has changed
notifyDeleteNUMBERn/aFlag indicating whether to send notifications when the node is deleted
notifyRetractNUMBERn/aFlag indicating whether to send notifications when published items are deleted
presenceBasedNUMBERn/aFlag indicating whether to send notifications to only users only
sendItemSubscribeNUMBERn/aFlag indicating whether to send last published item to new subscribers
publisherModelVARCHAR15Publisher model used by the node
subscriptionEnabledNUMBERn/aFlag indicating whether subscriptions are allowed
accessModelVARCHAR10Access model used by the node
languageVARCHAR255Default language of the node
replyPolicyVARCHAR15Policy that defines whether owners or publisher should receive replies to items
associationPolicyVARCHAR15Policy that defines who may associate leaf nodes with a collection
maxLeafNodesNUMBERn/aMax number of leaf nodes that a collection node might have

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多