Wildfire with Apache Derby database (Java DB)

Alexander · July 17, 2006, 8:36am

Hello,

I use Wildfire with Apache Derby database (http://db.apache.org/derby/), also known as Sun Java DB (http://developers.sun.com/prodtech/javadb/). Here is my wildfire_derby.sql script, maybe it will be helpful. Thank you.

==

CREATE TABLE jiveUser (

username VARCHAR(64) NOT NULL,

password VARCHAR(32),

encryptedPassword VARCHAR(255),

name VARCHAR(100),

email VARCHAR(100),

creationDate VARCHAR(15) NOT NULL,

modificationDate VARCHAR(15) NOT NULL,

CONSTRAINT jiveUser_pk PRIMARY KEY (username)

);

CREATE INDEX jiveUser_cDate_idx ON jiveUser (creationDate);

CREATE TABLE jiveUserProp (

username VARCHAR(64) NOT NULL,

name VARCHAR(100) NOT NULL,

propValue VARCHAR(4000) 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,

value VARCHAR(2000) NOT NULL,

CONSTRAINT jivePrivate_pk PRIMARY KEY (username, name, namespace)

);

CREATE TABLE jiveOffline (

username VARCHAR(64) NOT NULL,

messageID BIGINT NOT NULL,

creationDate VARCHAR(15) NOT NULL,

messageSize INTEGER NOT NULL,

message VARCHAR(2000) NOT NULL,

CONSTRAINT jiveOffline_pk PRIMARY KEY (username, messageID)

);

CREATE TABLE jiveRoster (

rosterID BIGINT 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 INDEX jiveRoster_username_idx ON jiveRoster (username);

CREATE TABLE jiveRosterGroups (

rosterID BIGINT NOT NULL,

rank INTEGER NOT NULL,

groupName VARCHAR(255) NOT NULL,

CONSTRAINT jiveRosterGroups_pk PRIMARY KEY (rosterID, rank)

);

CREATE INDEX jiveRosterGroup_rosterid_idx ON jiveRosterGroups (rosterID);

CREATE TABLE jiveVCard (

username VARCHAR(64) NOT NULL,

value VARCHAR(2000) NOT NULL,

CONSTRAINT jiveVCard_pk PRIMARY KEY (username)

);

CREATE TABLE jiveGroup (

groupName VARCHAR(50) NOT NULL,

description VARCHAR(255),

CONSTRAINT jiveGroup_pk PRIMARY KEY (groupName)

);

CREATE TABLE jiveGroupProp (

groupName VARCHAR(50) NOT NULL,

name VARCHAR(100) NOT NULL,

propValue VARCHAR(4000) 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 BIGINT NOT NULL,

CONSTRAINT jiveID_pk PRIMARY KEY (idType)

);

CREATE TABLE jiveProperty (

name VARCHAR(100) NOT NULL,

propValue VARCHAR(4000) 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 (

domain 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 INTEGER NOT NULL,

list VARCHAR(2000) NOT NULL,

CONSTRAINT jivePrivacyList_pk PRIMARY KEY (username, name)

);

CREATE INDEX jivePList_default_idx ON jivePrivacyList (username, isDefault);

CREATE TABLE jiveSASLAuthorized (

username VARCHAR(64) NOT NULL,

principal VARCHAR(4000) NOT NULL,

CONSTRAINT jiveSASLAuthorized_pk PRIMARY KEY (username, principal)

);

CREATE TABLE mucRoom (

roomID BIGINT 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),

canChangeSubject INTEGER NOT NULL,

maxUsers INTEGER NOT NULL,

publicRoom INTEGER NOT NULL,

moderated INTEGER NOT NULL,

membersOnly INTEGER NOT NULL,

canInvite INTEGER NOT NULL,

password VARCHAR(50),

canDiscoverJID INTEGER NOT NULL,

logEnabled INTEGER NOT NULL,

subject VARCHAR(100),

rolesToBroadcast INTEGER NOT NULL,

useReservedNick INTEGER NOT NULL,

canChangeNick INTEGER NOT NULL,

canRegister INTEGER NOT NULL,

CONSTRAINT mucRoom_pk PRIMARY KEY (name)

);

CREATE INDEX mucRoom_roomid_idx ON mucRoom(roomID);

CREATE TABLE mucRoomProp (

roomID BIGINT NOT NULL,

name VARCHAR(100) NOT NULL,

propValue VARCHAR(4000) NOT NULL,

CONSTRAINT mucRoomProp_pk PRIMARY KEY (roomID, name)

);

CREATE TABLE mucAffiliation (

roomID BIGINT NOT NULL,

jid VARCHAR(1024) NOT NULL,

affiliation INTEGER NOT NULL,

CONSTRAINT mucAffiliation_pk PRIMARY KEY (roomID, jid)

);

CREATE TABLE mucMember (

roomID BIGINT NOT NULL,

jid VARCHAR(1024) NOT NULL,

nickname VARCHAR(255),

firstName VARCHAR(100),

lastName VARCHAR(100),

url VARCHAR(100),

email VARCHAR(100),

faqentry VARCHAR(100),

CONSTRAINT mucMember_pk PRIMARY KEY (roomID, jid)

);

CREATE TABLE mucConversationLog (

roomID BIGINT NOT NULL,

sender VARCHAR(1024) NOT NULL,

nickname VARCHAR(255),

time CHAR(15) NOT NULL,

subject VARCHAR(255),

body CLOB

);

CREATE INDEX mucLog_time_idx ON mucConversationLog (time);

CREATE TABLE pubsubNode (

serviceID VARCHAR(100) NOT NULL,

nodeID VARCHAR(100) NOT NULL,

leaf INTEGER NOT NULL,

creationDate CHAR(15) NOT NULL,

modificationDate CHAR(15) NOT NULL,

parent VARCHAR(100),

deliverPayloads INTEGER NOT NULL,

maxPayloadSize INTEGER,

persistItems INTEGER,

maxItems INTEGER,

notifyConfigChanges INTEGER NOT NULL,

notifyDelete INTEGER NOT NULL,

notifyRetract INTEGER NOT NULL,

presenceBased INTEGER NOT NULL,

sendItemSubscribe INTEGER NOT NULL,

publisherModel VARCHAR(15) NOT NULL,

subscriptionEnabled INTEGER NOT NULL,

configSubscription INTEGER NOT NULL,

accessModel VARCHAR(10) NOT NULL,

payloadType VARCHAR(100),

bodyXSLT VARCHAR(100),

dataformXSLT VARCHAR(100),

creator VARCHAR(1024) NOT NULL,

description VARCHAR(255),

language VARCHAR(255),

name VARCHAR(50),

replyPolicy VARCHAR(15),

associationPolicy VARCHAR(15),

maxLeafNodes INTEGER,

CONSTRAINT pubsubNode_pk PRIMARY KEY (serviceID, nodeID)

);

CREATE TABLE pubsubNodeJIDs (

serviceID VARCHAR(100) NOT NULL,

nodeID VARCHAR(100) NOT NULL,

jid VARCHAR(1024) 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 INDEX pubsubNodeGroups_idx ON pubsubNodeGroups (serviceID, nodeID);

CREATE TABLE pubsubAffiliation (

serviceID VARCHAR(100) NOT NULL,

nodeID VARCHAR(100) NOT NULL,

jid VARCHAR(1024) 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(4000),

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 INTEGER NOT NULL,

digest INTEGER NOT NULL,

digest_frequency INTEGER NOT NULL,

expire CHAR(15),

includeBody INTEGER NOT NULL,

showValues VARCHAR(30) NOT NULL,

subscriptionType VARCHAR(10) NOT NULL,

subscriptionDepth INTEGER NOT NULL,

keyword VARCHAR(200),

CONSTRAINT pubsubSubs_pk PRIMARY KEY (serviceID, nodeID, id)

);

CREATE TABLE pubsubDefaultConf (

serviceID VARCHAR(100) NOT NULL,

leaf INTEGER NOT NULL,

deliverPayloads INTEGER NOT NULL,

maxPayloadSize INTEGER NOT NULL,

persistItems INTEGER NOT NULL,

maxItems INTEGER NOT NULL,

notifyConfigChanges INTEGER NOT NULL,

notifyDelete INTEGER NOT NULL,

notifyRetract INTEGER NOT NULL,

presenceBased INTEGER NOT NULL,

sendItemSubscribe INTEGER NOT NULL,

publisherModel VARCHAR(15) NOT NULL,

subscriptionEnabled INTEGER NOT NULL,

accessModel VARCHAR(10) NOT NULL,

language VARCHAR(255),

replyPolicy VARCHAR(15),

associationPolicy VARCHAR(15) NOT NULL,

maxLeafNodes INTEGER NOT NULL,

CONSTRAINT pubsubDefConf_pk PRIMARY KEY (serviceID, leaf)

);

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 (’‘wildfire’’, 10);

INSERT INTO jiveUser (username, password, name, email, creationDate, modificationDate)

VALUES (''admin'', ''admin'', ''Administrator'', ''admin@example.com'', ''0'', ''0'');

matt · July 17, 2006, 5:25pm

Alex,

Thanks for submitting the database schema! Would you be willing to help maintain the schema over time? If so, we could consider getting it officially into the project.

Regards,

Matt

Alexander · July 18, 2006, 8:33am

Matt, thank you for your answer!

I don’'t know Derby dialect in details, but I will be glad to know it and to help project. What should I do to maintain the schema?

BTW, Derby looks as good alternative for embeded DB (on level with HSQLDB).

Respectfully,

Alex

matt · July 18, 2006, 3:53pm

Alex,

We’‘re pretty happy with HSQLB as the default database. The main reason is that it’‘s very small, so doesn’‘t bloat the Wildfire download size. It’'s also been working great for quite a few years.

Still, there will be a growing number of people that use Derby which is why it could be useful to support it. To maintain the schema, you’'d simply help create the upgrade scripts each time a new database version is released (every couple of months). Does this sound like something you could take on?

Regards,

Matt

LG1 · July 18, 2006, 6:13pm

Hi Matt,

I always thought that you’‘d update a master SQL script and would use a script to convert it to the needed database versions. I’'d kill myself if I had to change five or more scripts manually.

LG

matt · July 18, 2006, 6:26pm

LG,

Ahh, I wish. Unfortunately, we don’'t have access to such a tool. The upgrade scripts get particularly complicated.

-Matt

LG1 · July 18, 2006, 8:11pm

Hi Matt,

‘‘sed’’, ‘‘m4’’ or ‘‘awk’’ could be your friends and do the job. It will require some minutes to tune them to do the job right but the time is invested very good.

Somehow you did create http://www.jivesoftware.org/builds/wildfire/docs/latest/documentation/database-g uide.html and using this or a plaintext file as source should make it possible to create the DDL scripts.

I know that you like java a lot, so using java and xml could also be fine but it could take a little bit longer to code.

LG

Alexander · July 19, 2006, 7:26am

Hi Matt,

thank you for explanation about HSQLDB - yes, you are right of course.

Respectfully,

Alex