 [All]
The Embarcadero chat server
By: John Kaster
Abstract: Special features of the Embarcadero chat server
Embarcadero chat server
The Embarcadero chat server is available for any Embarcadero Developer Network member to use at any time. We are using a Jabber-based open source server called Openfire customized to support Embarcadero account logins. Our default chat client is based on a customized version of another open source project called JWChat, which is a JavaScript client for Jabber.
We chose to standardize on Jabber because it is a very popular instant-messaging protocol with huge industry support. There are literally hundreds of clients available for Jabber.
We chose Openfire because it is one of the most full-featured Jabber servers available, we are using the freely available Open Source version, and we can (and do) customize it with JBuilder.
We chose JWChat because it is browser based, it works well with multiple browsers, it is a freely available Open Source project, and the JavaScript was easy to customize.
Openfire customizations
Openfire is a Jabber server written in Java, with both a commercial version (Enterprise) and the open source version. The open source version appears to be more than sufficient for our needs, with our ability to customize it as needed with JBuilder.
Openfire should be able to scale to our needs without any problems, but the details are a bit light on the page discussing Openfire scalability.
Blackfish SQL support
We have created a Blackfish SQL schema for Openfire, and have been using Blackfish SQL from the start. After a very productive discussion with one of the developers of Openfire, the "time" field in Openfire database is being changed to "logTime" so it doesn't conflict with the reserved word "time" in Blackfish SQL. Openfire 3.4 and above supports Blackfish SQL. InterBase can also be used with OpenFire 3.4 and above.
The Blackfish SQL schema we use for 3.4.x is available at the end of this document.
Embarcadero plug-in for Openfire
We have created a custom Embarcadero plug-in for Openfire to support features Openfire does not have.
Embarcadero account support
Openfire has a very nice plug-in architecture that allowed us to create "shadow copies" of Embarcadero account information. All account logins use Embarcadero membership services. The first time a user logs in to the chat server, an account record is created for them, so their Embarcadero chat server preferences can be preserved.
Chat room participation information
In addition to support for Embarcadero accounts, we extended Openfire to provide summary information of chat room participation that could be easily accessed by JavaScript. The custom JavaScript routines discussed below use this feature of the Embarcadero plug-in.
Moderated room enhancements
Our Openfire plug-in also gracefully handles "unauthorized" entry into rooms that require moderation (such as the sales chat room) when no moderator is present. If a user enters a moderated room when no moderator is present, an automatic reply asks them to come back when a moderator is present and the room will close in 10 seconds. A "/kick" of the user is submitted 10 seconds after this message is displayed, which removes them from the room but allows them to return again later. We implemented this for the plug-in rather than JWChat so all clients connected would comply with the moderation rules.
The "moderation required" setting is a new Openfire server property called mustHaveModeratorRooms, which accepts a comma-delimited list of roomIDs to flag as requiring a moderator. For example:
mustHaveModeratorRooms=sales,coderage1,coderage2
is a valid configuration.
Q&A Interface
We have also introduced a "Question and Answer" (Q&A) interface for web broadcasts like our online conferences and public chats. This Q&A interface uses three (3) chat rooms that pop up on request, and can be placed anywhere on your monitor(s): the public chat room, the VIP room, and the Q&A room.
Public chat room
The public chat room can be used by all people attending the chat, including moderators. Chat participants can /ask questions in moderated chat rooms, and receive notification that their question has been submitted to Q&A room. The Q&A room is only visible to chat moderators. Answers to /asked questions appear in the VIP room.
The main difference between the public chat room for Q&A chats and other public group chats are the [Ask] button, and links to the other rooms used during the event. Clicking on the link for another room will automatically join you to that room, or re-display it if you're "in" the room but it is not visible on your display.
For all participants, the public chat room has a link to open the VIP room. Moderators will also have a link to the Q&A room. Chat administrators can toggle any user as a moderator. If a user is a moderator, a link to open the Q&A room appears in the public chat room.
1 Sample public chat room
VIP Room
Only moderators can post content to the VIP room. Questions answered in the Q&A room appear in the VIP room with some special formatting to group the question and answer together. Moderators can also post messages directly to the VIP room from the Q&A room.
2 Sample VIP room with questions and answers
Q&A room
Only moderators can see the Q&A room. The Q&A room color codes the status of the questions.
As the following screen shot shows, multiple actions are available for processing questions. All buttons except for [Send] and [Ask] require a specific question to be selected in the queue.
3 Q&A room view, only for moderators
Button |
Description |
Assign |
Sets the text field for the "Assigned" column if a specific user should answer the question. |
Delete |
Remove the question from the Q&A room |
Mark as Read |
Changes the status of a message from unread to read (only for your own browser view). Question ID 9 has been marked as "read" in the screenshot above. |
Set Reply |
Assign the text of the reply to the question. The question itself can also be edited in this dialog. Question 6 above has its reply set, so it is ready to be answered. |
Answer |
Send the question and reply to the VIP room. The question must have a reply to be sent. Question ID 7 above has been answered. |
Ask |
Submits a question to the Q&A queue. /ask <question> can also be used, just as it works in the public chat room. |
Send |
Send a message to the VIP room. This message has all the special handling described above in the public chat room section. |
|
|
History preferences override for Group chat rooms
We have introduced new server behavior to override the default "history" that gets displayed for a chat room when the user enters it, to help set the context of the current conversation. For all standard chat rooms, we have configured the server to show the last 25 messages in the chat. We needed to turn off history for the sales room, and that required a customization.
For the sales chat room, the following server properties are used:
room.<roomname>.type = number
room.<roomname>.maxNumber = <# of past chats to show>
In the case of the sales chat room, the specific values for turning off chat history when you join the room are:
room.sales.type=number
room.sales.maxNumber=0
This way, users joining the chat room do not see what conversations have taken place before they arrive.
Sidebar: Using the Sales chat room
This is a "best practice" recommendation for the sales support chat room, should Embarcadero start using it.
All communications on the server will be secured via SSL. However, any conversations with specific existing or prospective customers should be private. Once a user joins the sales chat room, and private conversation can be started with that user by clicking on their user name in the roster list. That way, any private information exchanged with that user is only visible to the Embarcadero representative and that user.
Other users can be explicitly invited into the private chat by either party.
Registered user rooms
Another customization is rooms that are only available to registered users and moderators. Access for a registered user room is based on comma-delimited list of Embarcadero package IDs. If the user account requesting access has registered one of the packages in the ID list, they are granted access to the room. Any unauthorized access from any IM client will be denied.
Registered user rooms appear in the standard room list page. (See Room list JavaScript page below).
Field test rooms
(TODO. This feature is not yet implemented.)
Another customization is rooms that are only available to registered field testers and moderators. Access for a field test room is based on the name of the field test in our Digital Signature system. If the user account requesting access has signed our Field Test NDA and been authorized as a field tester, they are granted access to the room. Any unauthorized access from any IM client will be denied.
Until we roll out version 2.0 of our membership services, access rights will be determined by calling the white listed Digital Signature web services method HasUserSignedTemplateCode(templatecode, [email], [userid]).
Field test rooms do not appear in the standard room list page. (See Room list JavaScript page below).
JWChat browser client
Our default chat client, JWChat, uses JavaScript to communicate with our Jabber server. It is a very effective browser-based instant messaging client that is easy to extend for our special purposes.
JWChat commands
JWChat has special text recognition enhancements for chat messaging presented in the browser. It also supports a subset of IRC commands, and some different commands applicable to browser client chats.
The command syntax is a slash character, followed by the command, and the text to parse for the command.
Command |
Description |
Example |
/ask |
Ask a question. Only available in certain Q&A chats. |
/ask Why is there air? |
/join |
Join a chat room by room name. |
/join delphi |
/me |
Describes something you're doing. |
/me laughs |
/ban |
Moderator only. Ban a user from using our chat server. |
/ban bozo |
/kick |
Moderator only. Kick a user out of a chat room. They can rejoin the chat again. |
/kick bozo |
/kickall |
Moderator only. Kick all non-moderators out of a chat room |
/kickall |
/say |
Posts a message to the chat room. (This is the same as entering text and not specifying any special command.) |
/say Boo! |
/clear |
Clears the current chat display in your browser window. Does not clear chat history for the room. |
/clear |
/nick |
Sets your chat nickname. This option is not available in Embarcadero chats. |
If your Embarcadero user account has a nickname set, it will be used in the chat room. If you don't have a nickname specified, FirstName_LastName is used as your nickname. You can set your nickname in membership services. |
/topic |
Moderator only. Sets the topic of the chat room. |
/topic New chat topic! |
/invite |
Invites a user to join the chat. Available in most chat rooms. |
/invite jkaster |
/msg |
Send a private message to a specific user. |
/msg jaben I'm here! |
/part |
Leave one or more chat rooms |
/part delphi |
/whois |
Display public information on specified user. (This currently has issues on our chat server.) |
/whois jkaster |
|
|
|
JWChat customizations
We have already made significant enhancements and bug fixes to JWChat, and will provide the appropriate improvements back to the project after our beta cycle completes.
CSS changes
We have made the JWChat pages look better, and fit the current Embarcadero look.
JWChat login page
The JWChat login page has been updated to provide some valuable hints to the user about logging in with their Embarcadero account information, their automatic login progress, and enabling pop-ups.
Automatic login
If the user is currently logged in to the Embarcadero web sites (the USER cookie is set), automatic login will occur when:
- the JWChat login page is accessed, or
- entry to a chat room is requested
If the USER cookie is not found, the user is prompted to login with the JWChat login. After a successful login, the browser then opens the chat room.
Room list JavaScript page
The room list is a "dashboard" page that shows participation levels in the current chat rooms. Room status is updated every 15 seconds. The countdown can be pre-empted by clicking on the "refreshing" link. Any room on the list can be joined via the browser by clicking on the room name.
The room list is available at http://chat.Embarcadero.com.
Any room that is currently "open" appears in the room list. Rooms that require moderation will be displayed when a moderator is available, and will disappear when a moderator is not present.
Embarcadero macros
We have a several standard "phrases" that automatically receive hyperlinks in the chat. All product names are automatically converted to the urls for the product page. There are also These are defined in the JavaScript file cgAdditions.js.
Pattern |
Result |
CC:<CC#> |
Points to the CodeCentral web site at the CC item id |
Embarcadero |
Points to http://www.embarcadero.com |
CodeRage |
Points to the current CodeRage page |
QC:<QC#> |
Points to the QualityCentral web site at the QC report ID |
QCWIN:Defect_No=<QC#> |
Point the QC Windows client to the provided report ID |
|
|
We have also extended the JWChat regex matching to support italics with a syntax like /this should be in italics/.
Embarcadero emoticon
To embed the Embarcadero favicon  in the chat, use the text :cg: as part of your message.
Online chat awareness
A critical component to the success of this chat system is letting people know about chat room availability and user participation. We have created some JavaScript routines to make it easy to provide online awareness of our group chat rooms on any web page, not just pages on the Embarcadero domain.
We have JavaScript routines available that can be embedded in any html page on any web site, so anyone can invite people to participate in our group chats.
These routines go from the most general to the most personal and are called showRoomInfo, joinMe, and chatWithMe. The JavaScript calls refresh their status every 15 seconds. The sample HTML pages provided in this document will automatically update their status every 15 seconds simply by calling the appropriate JavaScript method.
JavaScript string substitution macros
The following "macros" are embeddable in the formatting strings used in the online awareness JavaScript routines described below.
Recognition of these identifiers in strings is case-insensitive.
Macro |
Description |
{MODERATORS} |
# of moderators in the room |
{MODERATORSTR} |
Evaluates to "moderator" or "moderators", depending on the # of moderators in the room |
{PEOPLE} |
# of people in the room |
{PEOPLESTR} |
Evaluates to "person" or "people", depending on the # of people in the room |
{ROOM} |
Name of room |
{ROOMDESC} |
Description of room |
{ROOMURL} |
Direct link to the chat room |
{USERS} |
# of people that aren’t moderators in the room (users = people – moderators) |
{USERSTR} |
Evaluates to "user" or "users", depending on the # of users in the room |
|
|
showRoomInfo
showRoomInfo is a general-purpose JavaScript routine for showing the current status of a specific chat room. It will provide a direct link to the chat room if the values passed into it mean the room is "open" for chatting.
showRoomInfo syntax
showRoomInfo( roomID, roomName, [onlyIfModeratorPresent,
[hasPeopleFormat, [noModeratorFormat, [noPeopleFormat]]]]);
showRoomInfo parameters
Parameter |
Description |
roomID |
The id of the chat room. The id of the chat room is its ending url, which can be determined by going to http://chat.embarcadero.com and clicking on the desired chat room. Some of public chat room IDs are: 3rdrail, blackfish_sql, c_builder, change_manager, communitychat, db_optimizer, dbartisan, delphi, delphi_for_php, delphi_prism, edn, er_studio, interbase, japanese, jbuilder, jedi, performance_center, qc, and rapid_sql Some of the moderated chat room ids (moderated rooms are only open if a moderator is present) are: Coderage1, coderage2, coderage3, coderage4, learn, and sales |
roomName |
This is "friendly" name of the chat room, and localized values can be passed in for it. Defaults to the "room name" value configured in Open Fire. |
onlyIfModeratorPresent |
Set to true to require a moderator in the room, false for a general public chat room. If true is passed and no moderator is present, the noModeratorFormat string will be evaluated and returned as the result of this call. This value will be true for rooms like the sales support room. Default = false. |
hasPeopleFormat |
This string is evaluated and returned if there are people in the chat room. It can be overridden for localization or customization purposes. Default = "Chat with the {PEOPLE} {PEOPLESTR} in the {ROOM} room." |
noModeratorFormat |
This string is evaluated and returned if there is no moderator in the chat room and true is passed for onlyIfModeratorPresent. It can be overridden for localization or customization purposes. Default = "{ROOM} chat room closed." |
noPeopleFormat |
This string is evaluated and returned if there are no people in the chat room, and can be overridden for localization or customization purposes. Default = "Start a chat in the {ROOM} chat room." |
|
|
showRoomInfo HTML example
<html>
<head>
<title>Chat Room Stamp Demo</title>
<script language="JavaScript" type="text/javascript"
src="http://chat.Embarcadero.com/chatroominfo.js">
</script>
</head>
<body>
<script language="JavaScript" type="text/javascript">
<!-- showRoomInfo( "delphi" ); // -->
</script>
</body>
</html>
joinMe
joinMe is used to invite someone into a public chat room when a specific user is also participating in that chat room.
joinMe syntax
joinMe( roomID, userID, [roomName, [userFriendlyName,
[showIfNotInRoom, [ifUserInRoomFormat,
[ifUserNotInRoomFormat]]]]] );
joinMe parameters
Parameter |
Description |
roomID |
Specifies the room ID of the room from which to retrieve information. |
userID |
Specifies the user ID (login name) of the user to join in the chat room. |
roomName |
Specifies the friendly name of the room. Default = null |
userFriendlyName |
Specifies the friendly name of the user. Default = null |
showIfNotInRoom |
Specifies whether to show the link if the user isn't in the room. Default = false |
ifUserInRoomFormat |
Specifies the format of the link/text to display if the specified user is in the room. Default = "Join me in the {ROOM} chat room." |
ifUserNotInRoomFormat |
Specifies the format of the link/text to display if the specified user is not in the room. Default = "Not in the {ROOM} chat room right now, come back later." |
|
|
Available substitutions for joinMe:
{ROOM}
{ROOMID}
{USER}
{USERID}
{ROOMURL}
joinMe HTML example
<html>
<head>
<title>Join Me Stamp Demo</title>
<script language="JavaScript" type="text/javascript"
src="http://chat.Embarcadero.com/chatroominfo.js">
</script>
</head>
<body>
<script language="JavaScript" type="text/javascript">
<!-- joinMe( "delphi", "jomitech" ); // -->
</script>
</body>
</html>
chatWithMe
This routine allows you to invite someone to start a private chat with you whenever your online status for our Jabber server is set to "open".
chatWithMe syntax
function chatWithMe( userID, [userFriendlyName,
[userAvailableFormat, [userNotAvailableFormat]]] )
chatWithMe parameters
Parameter |
Description |
userID |
Specifies the user ID of the user inviting a chat |
userFriendlyName |
Specifies the friendly name of the user. Default = null |
userAvailableFormat |
Specifies the format of the link/text to display if the specified user is available and ready to chat. Default = "Chat with me now!" |
userNotAvailableFormat |
Specifies the format of the link/text to display if the specified user is not available. Default = "Not available to chat." |
|
|
Available substitutions:
{USER}
{USERID}
{CHATURL}
chatWithMe HTML example
<html>
<head>
<title>Chat With Me Stamp Demo</title>
<script language="JavaScript" type="text/javascript"
src="http://chat.Embarcadero.com/chatroominfo.js">
</script>
</head>
<body>
<script language="JavaScript" type="text/javascript">
<!-- chatWithMe( "jomitech" ); // -->
</script>
</body>
</html>
GUI Jabber clients
The GUI client we recommend is Pidgin, which supports far more than just Jabber for instant messaging. It is available on a variety of platforms.
To connect to our Jabber server with Pidgin, use the following values for the dialog under the Accounts menu on the Pidgin buddy list window, but change the user information to your Embarcadero Developer Network login rather than mine!
Use the Accounts|Add/Edit menu to edit or create your account.
4 Basic account configuration
Your screen name should be your developer network Login name. You can view your login name with the login form at https://members.embarcadero.com. If the values on the form are blank, just do your login and you can see all your account values.
The password you provide is the same as your Developer Network login.
5 Advanced account configuration
After you connect, you should see a status window similar to the Pidgin buddy list:
6 The Pidgin buddy list window
Then, use Tools|Room List menu to start retrieving the list of group chat rooms hosted on our server.
7 The room list dialog before rooms are retrieved
Click [Get List].
8 Entering the chat server address
Use "rooms.chat.embarcadero.com" as the conference server name if it's not already set, and click [Find Rooms].
9 The retrieved room list
Select the room you wish to join, and click the [Join] button. If you want to add the room to your "roster" of chats, click the [Add Chat] button instead.
That should be enough to get you started with Pidgin. We hope to see you in a chat soon!
Blackfish SQL Schema
For anyone who wants to use Blackfish SQL with Openfire, here is the Blackfish SQL schema:
CREATE TABLE jiveUser (
username VARCHAR(64) NOT NULL,
plainPassword VARCHAR(32),
encryptedPassword VARCHAR(255),
name VARCHAR(100),
email VARCHAR(100),
creationDate CHAR(15) NOT NULL,
modificationDate CHAR(15) NOT NULL,
CONSTRAINT jiveUser_pk PRIMARY KEY (username)
);
CREATE TABLE jiveUserProp (
username VARCHAR(64) NOT NULL,
name VARCHAR(100) NOT NULL,
propValue VARCHAR(2000) NOT NULL,
CONSTRAINT jiveUserProp_pk PRIMARY KEY (username, name)
);
CREATE TABLE jivePrivate (
username VARCHAR(64) NOT NULL,
name VARCHAR(100) NOT NULL,
namespace VARCHAR(200) NOT NULL,
privateData VARCHAR NOT NULL,
CONSTRAINT JivePrivate_pk PRIMARY KEY (username, name, namespace)
);
CREATE TABLE jiveOffline (
username VARCHAR(64) NOT NULL,
messageID INTEGER NOT NULL,
creationDate CHAR(15) NOT NULL,
messageSize INTEGER NOT NULL,
stanza VARCHAR NOT NULL,
CONSTRAINT jiveOffline_pk PRIMARY KEY (username, messageID)
);
CREATE TABLE jivePresence (
username VARCHAR(64) NOT NULL,
offlinePresence VARCHAR,
offlineDate CHAR(15) NOT NULL,
CONSTRAINT jivePresence_pk PRIMARY KEY (username)
);
CREATE TABLE jiveRoster (
rosterID INTEGER NOT NULL,
username VARCHAR(64) NOT NULL,
jid VARCHAR(1024) NOT NULL,
sub INTEGER NOT NULL,
ask INTEGER NOT NULL,
recv INTEGER NOT NULL,
nick VARCHAR(255),
CONSTRAINT jiveRoster_pk PRIMARY KEY (rosterID)
);
CREATE TABLE jiveRosterGroups (
rosterID INTEGER NOT NULL,
rank INTEGER NOT NULL,
groupName VARCHAR(255) NOT NULL,
CONSTRAINT jiveRosterGroups_pk PRIMARY KEY (rosterID, rank)
);
CREATE TABLE jiveVCard (
username VARCHAR(64) NOT NULL,
vcard VARCHAR NOT NULL,
CONSTRAINT JiveVCard_pk PRIMARY KEY (username)
);
CREATE TABLE jiveGroup (
groupName VARCHAR(50) NOT NULL,
description VARCHAR(255),
CONSTRAINT group_pk PRIMARY KEY (groupName)
);
CREATE TABLE jiveGroupProp (
groupName VARCHAR(50) NOT NULL,
name VARCHAR(100) NOT NULL,
propValue VARCHAR(2000) NOT NULL,
CONSTRAINT jiveGroupProp_pk PRIMARY KEY (groupName, name)
);
CREATE TABLE jiveGroupUser (
groupName VARCHAR(50) NOT NULL,
username VARCHAR(100) NOT NULL,
administrator INTEGER NOT NULL,
CONSTRAINT jiveGroupUser_pk PRIMARY KEY (groupName, username,
administrator)
);
CREATE TABLE jiveID (
idType INTEGER NOT NULL,
id INTEGER NOT NULL,
CONSTRAINT jiveID_pk PRIMARY KEY (idType)
);
CREATE TABLE jiveProperty (
name VARCHAR(100) NOT NULL,
propValue VARCHAR NOT NULL,
CONSTRAINT jiveProperty_pk PRIMARY KEY (name)
);
CREATE TABLE jiveVersion (
name VARCHAR(50) NOT NULL,
version INTEGER NOT NULL,
CONSTRAINT jiveVersion_pk PRIMARY KEY (name)
);
CREATE TABLE jiveExtComponentConf (
subdomain VARCHAR(255) NOT NULL,
secret VARCHAR(255),
permission VARCHAR(10) NOT NULL,
CONSTRAINT jiveExtComponentConf_pk PRIMARY KEY (subdomain)
);
CREATE TABLE jiveRemoteServerConf (
xmppDomain VARCHAR(255) NOT NULL,
remotePort INTEGER,
permission VARCHAR(10) NOT NULL,
CONSTRAINT jiveRemoteServerConf_pk PRIMARY KEY (domain)
);
CREATE TABLE jivePrivacyList (
username VARCHAR(64) NOT NULL,
name VARCHAR(100) NOT NULL,
isDefault INT NOT NULL,
list VARCHAR NOT NULL,
CONSTRAINT jivePrivacyList_pk PRIMARY KEY (username, name)
);
CREATE TABLE jiveSASLAuthorized (
username VARCHAR(64) NOT NULL,
principal VARCHAR(2000) NOT NULL,
CONSTRAINT jiveSASLAuthoirzed_pk PRIMARY KEY (username, principal)
);
CREATE TABLE mucRoom (
roomID INT NOT NULL,
creationDate CHAR(15) NOT NULL,
modificationDate CHAR(15) NOT NULL,
name VARCHAR(50) NOT NULL,
naturalName VARCHAR(255) NOT NULL,
description VARCHAR(255),
lockedDate CHAR(15) NOT NULL,
emptyDate CHAR(15) NULL,
canChangeSubject INT NOT NULL,
maxUsers INT NOT NULL,
publicRoom INT NOT NULL,
moderated INT NOT NULL,
membersOnly INT NOT NULL,
canInvite INT NOT NULL,
roomPassword VARCHAR(50) NULL,
canDiscoverJID INT NOT NULL,
logEnabled INT NOT NULL,
subject VARCHAR(100) NULL,
rolesToBroadcast INT NOT NULL,
useReservedNick INT NOT NULL,
canChangeNick INT NOT NULL,
canRegister INT NOT NULL,
CONSTRAINT mucRoom__pk PRIMARY KEY (name)
);
CREATE TABLE mucRoomProp (
roomID INT NOT NULL,
name VARCHAR(100) NOT NULL,
propValue VARCHAR(2000) NOT NULL,
CONSTRAINT mucRoomProp_pk PRIMARY KEY (roomID, name)
);
CREATE TABLE mucAffiliation (
roomID INT NOT NULL,
jid VARCHAR(424) NOT NULL,
affiliation INT NOT NULL,
CONSTRAINT mucAffiliation__pk PRIMARY KEY (roomID,jid)
);
CREATE TABLE mucMember (
roomID INT NOT NULL,
jid VARCHAR(424) NOT NULL,
nickname VARCHAR(255) NULL,
firstName VARCHAR(100) NULL,
lastName VARCHAR(100) NULL,
url VARCHAR(100) NULL,
email VARCHAR(100) NULL,
faqentry VARCHAR(100) NULL,
CONSTRAINT mucMember__pk PRIMARY KEY (roomID,jid)
);
CREATE TABLE mucConversationLog (
ID INT NOT NULL AUTOINCREMENT,
roomID INT NOT NULL,
sender VARCHAR(1024) NOT NULL,
nickname VARCHAR(255) NULL,
logTime CHAR(15) NOT NULL,
subject VARCHAR(255) NULL,
body VARCHAR NULL,
CONSTRAINT mucConversationLog_pk PRIMARY KEY (ID)
);
CREATE TABLE pubsubNode (
serviceID VARCHAR(100) NOT NULL,
nodeID VARCHAR(100) NOT NULL,
leaf INT NOT NULL,
creationDate CHAR(15) NOT NULL,
modificationDate CHAR(15) NOT NULL,
parent VARCHAR(100) NULL,
deliverPayloads INT NOT NULL,
maxPayloadSize INT NULL,
persistItems INT NULL,
maxItems INT NULL,
notifyConfigChanges INT NOT NULL,
notifyDelete INT NOT NULL,
notifyRetract INT NOT NULL,
presenceBased INT NOT NULL,
sendItemSubscribe INT NOT NULL,
publisherModel VARCHAR(15) NOT NULL,
subscriptionEnabled INT NOT NULL,
configSubscription INT NOT NULL,
accessModel VARCHAR(10) NOT NULL,
payloadType VARCHAR(100) NULL,
bodyXSLT VARCHAR(100) NULL,
dataformXSLT VARCHAR(100) NULL,
creator VARCHAR(255) NOT NULL,
description VARCHAR(255) NULL,
language VARCHAR(255) NULL,
name VARCHAR(50) NULL,
replyPolicy VARCHAR(15) NULL,
associationPolicy VARCHAR(15) NULL,
maxLeafNodes INT NULL,
CONSTRAINT pubsubNode_pk PRIMARY KEY (serviceID, nodeID)
);
CREATE TABLE pubsubNodeJIDs (
serviceID VARCHAR(100) NOT NULL,
nodeID VARCHAR(100) NOT NULL,
jid VARCHAR(250) NOT NULL,
associationType VARCHAR(20) NOT NULL,
CONSTRAINT pubsubJID_pk PRIMARY KEY (serviceID, nodeID, jid)
);
CREATE TABLE pubsubNodeGroups (
serviceID VARCHAR(100) NOT NULL,
nodeID VARCHAR(100) NOT NULL,
rosterGroup VARCHAR(100) NOT NULL
);
CREATE TABLE pubsubAffiliation (
serviceID VARCHAR(100) NOT NULL,
nodeID VARCHAR(100) NOT NULL,
jid VARCHAR(250) NOT NULL,
affiliation VARCHAR(10) NOT NULL,
CONSTRAINT pubsubAffil_pk PRIMARY KEY (serviceID, nodeID, jid)
);
CREATE TABLE pubsubItem (
serviceID VARCHAR(100) NOT NULL,
nodeID VARCHAR(100) NOT NULL,
id VARCHAR(100) NOT NULL,
jid VARCHAR(1024) NOT NULL,
creationDate CHAR(15) NOT NULL,
payload VARCHAR NULL,
CONSTRAINT pubsubItem_pk PRIMARY KEY (serviceID, nodeID, id)
);
CREATE TABLE pubsubSubscription (
serviceID VARCHAR(100) NOT NULL,
nodeID VARCHAR(100) NOT NULL,
id VARCHAR(100) NOT NULL,
jid VARCHAR(1024) NOT NULL,
owner VARCHAR(1024) NOT NULL,
state VARCHAR(15) NOT NULL,
deliver INT NOT NULL,
digest INT NOT NULL,
digest_frequency INT NOT NULL,
expire CHAR(15) NULL,
includeBody INT NOT NULL,
showValues VARCHAR(30) NOT NULL,
subscriptionType VARCHAR(10) NOT NULL,
subscriptionDepth INT NOT NULL,
keyword VARCHAR(200) NULL,
CONSTRAINT pubsubSubs_pk PRIMARY KEY (serviceID, nodeID, id)
);
CREATE TABLE pubsubDefaultConf (
serviceID VARCHAR(100) NOT NULL,
leaf INT NOT NULL,
deliverPayloads INT NOT NULL,
maxPayloadSize INT NOT NULL,
persistItems INT NOT NULL,
maxItems INT NOT NULL,
notifyConfigChanges INT NOT NULL,
notifyDelete INT NOT NULL,
notifyRetract INT NOT NULL,
presenceBased INT NOT NULL,
sendItemSubscribe INT NOT NULL,
publisherModel VARCHAR(15) NOT NULL,
subscriptionEnabled INT NOT NULL,
accessModel VARCHAR(10) NOT NULL,
language VARCHAR(255) NULL,
replyPolicy VARCHAR(15) NULL,
associationPolicy VARCHAR(15) NOT NULL,
maxLeafNodes INT NOT NULL,
CONSTRAINT pubsubDefConf_pk
PRIMARY KEY (serviceID, leaf)
);
CREATE INDEX jiveUser_cDate_idx
ON jiveUser (creationDate ASC);
CREATE INDEX jiveRoster_username_idx
ON jiveRoster (username ASC);
CREATE INDEX jiveRosterGroups_rosterid_idx
ON jiveRosterGroups (rosterID ASC);
ALTER TABLE jiveRosterGroups
ADD CONSTRAINT jiveRosterGroups_rosterID_fk
FOREIGN KEY (rosterID) REFERENCES jiveRoster;
CREATE INDEX jivePList_default_idx
ON jivePrivacyList (username, isDefault);
CREATE INDEX mucRoom_roomID_idx on mucRoom(roomID);
CREATE INDEX mucLog_time_idx ON mucConversationLog (logTime);
CREATE INDEX mucRoomID_idx ON mucConversationLog (roomID);
CREATE INDEX mucSubject_idx ON mucConversationLog (subject);
CREATE INDEX pubsubNodeGroups_idx
ON pubsubNodeGroups (serviceID, nodeID);
INSERT INTO jiveID (idType, id) VALUES (18, 1);
INSERT INTO jiveID (idType, id) VALUES (19, 1);
INSERT INTO jiveID (idType, id) VALUES (23, 1);
INSERT INTO jiveVersion (name, version)
VALUES ('openfire', 13);
INSERT INTO jiveUser (username, plainPassword, name, email, creationDate,
modificationDate)
VALUES ('admin', 'admin', 'Administrator', 'admin@example.com', '0',
'0');
|
|
Connect with Us