Database Schema Guide does not match database schema

Openfire Openfire Dev
1

Hi,

http://www.jivesoftware.org/builds/wildfire/docs/latest/documentation/database-g uide.html

contains

CREATE TABLE mucAffiliation (
  roomID              NUMBER,
  jid                 TEXT,
  affiliation         NUMBER,
);

while wildfire_hsqldb.sql contains

CREATE TABLE mucAffiliation (
  roomID              BIGINT        NOT NULL,
  jid                 VARCHAR(1024) NOT NULL,
  affiliation         INTEGER       NOT NULL,
  CONSTRAINT mucAffiliation_pk PRIMARY KEY (roomID, jid)
);

and limits the JID to 1024 characters which seems to be bad as the JID may be 31023’’@’’*/’’ characters long. The Oracle scripts seems to do the same while the DB2/SQLServer scripts limit the JID to only 1000/424 characters.

Only for MySQL a TEXT datatype is used (I have no idea about the MySQL TEXT limitations). Anyhow there the PK is not really a PK as the JID is limited to 70 characters.

A simple VARCHAR(3072) datatype should have been fine for all databases … but maybe it’'s time to do a redesign of some tables to store all JIDs in one table and use just references to them where needed.

LG

Message was edited by it2000:

I did post contains and not contain but currently contain gets displayed. Seems to be another forum bug. Fixed the posting by adding ‘’ ‘’ and reported it here: http://www.jivesoftware.org/community/thread.jspa?threadID=20972

Message was edited by it2000 again:

I wonder whether the JID includes a resource identifier, so 2*1023+’’@’’ characters could be fine.

Message was edited by it2000 once again:

database-guide.html makes one think that table pubsubSubscription contains two columns called “jid” - I think that this is impossible.

Message was edited by it2000 again and again:

Table jiveSASLAuthorized seems to be missing on the web page while it is in the scripts.

So to make it short I have a tool to create scripts for all databases using a template but as one can imagine there is a huge difference between the existing scripts and the generated output, so there’'s still a lot to do.

2

possible problems:

oracle:

key size of an index can not be greater then the block size of the database storage, eg when using a database with a 2kb block size the key size can not be greater then 2kb, leaving (2048 - 4)/2 = 1022 length for the jid when using unicode.

ms sql200x: max key size 900 bytes, leaving 424 unicode chars

mysql 1000 byte key length

postgres: seems to be dependant on the block size

so a JID of length 3071 bytes is not going to fit in an indix on most databases, or you have to resort to reconfigging the storage engine (with possible performance hit).

possible solution:

usage of md5/crc checksum helper index colomn with triggers/constraints to enforce the pk.

like :

CREATE TABLE mucAffiliation (

roomID NUMBER,

jid varchar(3072),

jid_md5 varchar(100),

affiliation NUMBER,

);

CREATE INDEX IDX_MD5 ON mucAffiliation (roomID, jid_md5)

and the trigger

create trigger afterUpdateTrigger on mucAffiliation AFTER INSERT, UPDATE

AS

declare @rowcount int

select @rowcount = count(*)

FROM inserted b, mucAffiliation a

where a.jid_md5 = b.jid_md5

and a.jid = b.jid

and a.roomID= b.roomID

if (@rowcount > 1)

BEGIN

RAISERROR (’‘error’’, 16, 1)

ROLLBACK TRANSACTION

END

the problem is that the trigger is mostly database dependant sql

3

Hi,

I thought of three tables, one for the node, the domain and the resource identifier. So the JID could be stored in a forth table.

LG

4

as per http://www.xmpp.org/specs/rfc3920.html#addressing-overview the node, domain and resource are 1023 bytes in length, so for most databases (ms sql, mysql) it wont work or you end up without a unique index.

I tested the code above yesterday and the db overhead for inserts and deletes is only 33% where the db spends 66% on the pk index insert (mssql2005).

regards

bram