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.

Hide image
Click to see full-sized image

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.

Hide image

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.

Hide image
Click to see full-sized image

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.

Hide image
Click to see full-sized image

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.

Hide image
Click to see full-sized image

5 Advanced account configuration

After you connect, you should see a status window similar to the Pidgin buddy list:

Hide image

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.

Hide image

7 The room list dialog before rooms are retrieved

Click [Get List].

Hide image

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].

Hide image

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);

/* Finally, insert default table values. */

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);

/* Entry for admin user */
INSERT INTO jiveUser (username, plainPassword, name, email, creationDate, 
  modificationDate)
  VALUES ('admin', 'admin', 'Administrator', 'admin@example.com', '0', 
    '0');

Server Response from: ETNASC03