META-INF.sql.DB2.createttmodel.sql Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of tentackle-test-pdo Show documentation
Show all versions of tentackle-test-pdo Show documentation
Test depdendency to support writing PDO-based tests.
Also generates the SQL-scripts for the TT tables and
contains some PDO tests.
This artifact must be included in test-scope only!
The newest version!
----------------------
-- Tentackle Tables --
----------------------
CREATE TABLE bundle ( -- resource bundle \
tableserial BIGINT NOT NULL, -- table serial \
bname VARCHAR(128), -- the resource bundle name \
blocale VARCHAR(8) NOT NULL, -- the locale, null if default \
id BIGINT NOT NULL PRIMARY KEY, -- object id \
serial BIGINT NOT NULL DEFAULT 1 -- object serial \
);
COMMENT ON TABLE bundle IS 'resource bundle';
COMMENT ON COLUMN bundle.tableserial IS 'table serial';
COMMENT ON COLUMN bundle.bname IS 'the resource bundle name';
COMMENT ON COLUMN bundle.blocale IS 'the locale, null if default';
COMMENT ON COLUMN bundle.id IS 'object id';
COMMENT ON COLUMN bundle.serial IS 'object serial';
CREATE UNIQUE INDEX bundle_udk ON bundle (bname, blocale);
CREATE TABLE bundlekey ( -- bundle key with translation \
bundle_id BIGINT NOT NULL, -- the bundle id \
bkey VARCHAR(512), -- the resource bundle key \
bvalue VARCHAR(512), -- the localized string \
id BIGINT NOT NULL PRIMARY KEY, -- object id \
serial BIGINT NOT NULL DEFAULT 1 -- object serial \
);
COMMENT ON TABLE bundlekey IS 'bundle key with translation';
COMMENT ON COLUMN bundlekey.bundle_id IS 'the bundle id';
COMMENT ON COLUMN bundlekey.bkey IS 'the resource bundle key';
COMMENT ON COLUMN bundlekey.bvalue IS 'the localized string';
COMMENT ON COLUMN bundlekey.id IS 'object id';
COMMENT ON COLUMN bundlekey.serial IS 'object serial';
CREATE INDEX bundlekey_bundle ON bundlekey (bundle_id);
CREATE TABLE modification ( -- modification tracking table \
tablename VARCHAR(64), -- the tablename \
id BIGINT NOT NULL PRIMARY KEY, -- object id \
serial BIGINT NOT NULL DEFAULT 1 -- object serial \
);
COMMENT ON TABLE modification IS 'modification tracking table';
COMMENT ON COLUMN modification.tablename IS 'the tablename';
COMMENT ON COLUMN modification.id IS 'object id';
COMMENT ON COLUMN modification.serial IS 'object serial';
CREATE UNIQUE INDEX modification_tablename ON modification (tablename);
CREATE TABLE modlog ( -- modification table for async coupling \
objectid BIGINT NOT NULL, -- object id \
classid INTEGER NOT NULL, -- object class id \
classname VARCHAR(192), -- object classname (if classid == 0) \
txid BIGINT NOT NULL, -- transaction id (optional) \
txname VARCHAR(64), -- transaction name (optional) \
modtype CHAR(1) NOT NULL, -- modification type \
modtime TIMESTAMP, -- time of event \
userid BIGINT NOT NULL, -- user id \
message VARCHAR(512), -- optional informational or error message \
processed TIMESTAMP NOT NULL, -- processing time \
id BIGINT NOT NULL PRIMARY KEY, -- object id \
serial BIGINT NOT NULL DEFAULT 1 -- object serial \
);
COMMENT ON TABLE modlog IS 'modification table for async coupling';
COMMENT ON COLUMN modlog.objectid IS 'object id';
COMMENT ON COLUMN modlog.classid IS 'object class id';
COMMENT ON COLUMN modlog.classname IS 'object classname (if classid == 0)';
COMMENT ON COLUMN modlog.txid IS 'transaction id (optional)';
COMMENT ON COLUMN modlog.txname IS 'transaction name (optional)';
COMMENT ON COLUMN modlog.modtype IS 'modification type';
COMMENT ON COLUMN modlog.modtime IS 'time of event';
COMMENT ON COLUMN modlog.userid IS 'user id';
COMMENT ON COLUMN modlog.message IS 'optional informational or error message';
COMMENT ON COLUMN modlog.processed IS 'processing time';
COMMENT ON COLUMN modlog.id IS 'object id';
COMMENT ON COLUMN modlog.serial IS 'object serial';
CREATE INDEX modlog_next ON modlog (processed, id);
CREATE INDEX modlog_txid ON modlog (txid);
CREATE INDEX modlog_object ON modlog (objectid, classid, processed);
CREATE INDEX modlog_user ON modlog (userid, processed);
CREATE TABLE numpool ( -- number pool \
name VARCHAR(30), -- the number pool name \
realm VARCHAR(80) NOT NULL, -- pool realm, optional \
description VARCHAR(512), -- short description \
poolonline SMALLINT NOT NULL, -- true if online, else offline \
lowmark BIGINT NOT NULL, -- minimum number count before request to fill up from uplink, 0 to disable \
reqsize BIGINT NOT NULL, -- number count to request from uplink, 0 to disable \
uplink VARCHAR(512), -- uplink configuration (optional) \
id BIGINT NOT NULL PRIMARY KEY, -- object id \
serial BIGINT NOT NULL DEFAULT 1 -- object serial \
);
COMMENT ON TABLE numpool IS 'number pool';
COMMENT ON COLUMN numpool.name IS 'the number pool name';
COMMENT ON COLUMN numpool.realm IS 'pool realm, optional';
COMMENT ON COLUMN numpool.description IS 'short description';
COMMENT ON COLUMN numpool.poolonline IS 'true if online, else offline';
COMMENT ON COLUMN numpool.lowmark IS 'minimum number count before request to fill up from uplink, 0 to disable';
COMMENT ON COLUMN numpool.reqsize IS 'number count to request from uplink, 0 to disable';
COMMENT ON COLUMN numpool.uplink IS 'uplink configuration (optional)';
COMMENT ON COLUMN numpool.id IS 'object id';
COMMENT ON COLUMN numpool.serial IS 'object serial';
CREATE UNIQUE INDEX numpool_udk ON numpool (name, realm);
CREATE TABLE numrange ( -- number range \
poolid BIGINT NOT NULL, -- ID of the number pool \
rbegin BIGINT NOT NULL, -- begin of range \
rend BIGINT NOT NULL, -- end of range \
id BIGINT NOT NULL PRIMARY KEY, -- object id \
serial BIGINT NOT NULL DEFAULT 1 -- object serial \
);
COMMENT ON TABLE numrange IS 'number range';
COMMENT ON COLUMN numrange.poolid IS 'ID of the number pool';
COMMENT ON COLUMN numrange.rbegin IS 'begin of range';
COMMENT ON COLUMN numrange.rend IS 'end of range';
COMMENT ON COLUMN numrange.id IS 'object id';
COMMENT ON COLUMN numrange.serial IS 'object serial';
CREATE TABLE prefkey ( -- a preferences key/value pair \
tableserial BIGINT NOT NULL, -- table serial \
nodeid BIGINT NOT NULL, -- ID of the preferences-node \
rootnodeid BIGINT NOT NULL, -- ID of the root-node \
pkey VARCHAR(128), -- name of the key \
pvalue VARCHAR(512), -- value of the key \
id BIGINT NOT NULL PRIMARY KEY, -- object id \
serial BIGINT NOT NULL DEFAULT 1 -- object serial \
);
COMMENT ON TABLE prefkey IS 'a preferences key/value pair';
COMMENT ON COLUMN prefkey.tableserial IS 'table serial';
COMMENT ON COLUMN prefkey.nodeid IS 'ID of the preferences-node';
COMMENT ON COLUMN prefkey.rootnodeid IS 'ID of the root-node';
COMMENT ON COLUMN prefkey.pkey IS 'name of the key';
COMMENT ON COLUMN prefkey.pvalue IS 'value of the key';
COMMENT ON COLUMN prefkey.id IS 'object id';
COMMENT ON COLUMN prefkey.serial IS 'object serial';
CREATE UNIQUE INDEX prefkey_path ON prefkey (nodeid, pkey);
CREATE INDEX prefkey_tableserial ON prefkey (tableserial);
CREATE INDEX prefkey_root ON prefkey (rootnodeid);
CREATE TABLE prefnode ( -- a preferences node \
tableserial BIGINT NOT NULL, -- table serial \
username VARCHAR(32) NOT NULL, -- name of user, null if system \
nodename VARCHAR(192), -- name of the node \
parentid BIGINT NOT NULL, -- ID of parent-node, 0 if root-node \
rootnodeid BIGINT NOT NULL, -- ID of the root-node \
id BIGINT NOT NULL PRIMARY KEY, -- object id \
serial BIGINT NOT NULL DEFAULT 1 -- object serial \
);
COMMENT ON TABLE prefnode IS 'a preferences node';
COMMENT ON COLUMN prefnode.tableserial IS 'table serial';
COMMENT ON COLUMN prefnode.username IS 'name of user, null if system';
COMMENT ON COLUMN prefnode.nodename IS 'name of the node';
COMMENT ON COLUMN prefnode.parentid IS 'ID of parent-node, 0 if root-node';
COMMENT ON COLUMN prefnode.rootnodeid IS 'ID of the root-node';
COMMENT ON COLUMN prefnode.id IS 'object id';
COMMENT ON COLUMN prefnode.serial IS 'object serial';
CREATE UNIQUE INDEX prefnode_nodename ON prefnode (nodename, username);
CREATE INDEX prefnode_parentid ON prefnode (parentid);
CREATE INDEX prefnode_tableserial ON prefnode (tableserial);
CREATE INDEX prefnode_root ON prefnode (rootnodeid);
CREATE TABLE secrules ( -- security ACLs \
objectclass VARCHAR(128), -- the protected classname, null if an entity \
objectclassid INTEGER NOT NULL, -- the protected object's class id, 0 if not an entity \
objectid BIGINT NOT NULL, -- the id of the protected object, 0 if all instances or not an entity \
contextclassid INTEGER NOT NULL, -- the class id of the DomainContext's context entity, 0 if all contexts \
contextid BIGINT NOT NULL, -- the id of DomainContext's context object, 0 if all instances \
granteeclassid INTEGER NOT NULL, -- the class id of the entity the permissions are granted to, 0 if all classes \
granteeid BIGINT NOT NULL, -- the id of the entity the permissions are granted to, 0 if all grantees \
secprio INTEGER NOT NULL, -- the priority or evaluation order, 0 is highest or first \
permissions VARCHAR(128), -- the permissions as a comma-separated list \
allowed SMALLINT NOT NULL, -- the false if denied, true if allowed \
message VARCHAR(512), -- the user message \
id BIGINT NOT NULL PRIMARY KEY, -- object id \
serial BIGINT NOT NULL DEFAULT 1 -- object serial \
);
COMMENT ON TABLE secrules IS 'security ACLs';
COMMENT ON COLUMN secrules.objectclass IS 'the protected classname, null if an entity';
COMMENT ON COLUMN secrules.objectclassid IS 'the protected object''s class id, 0 if not an entity';
COMMENT ON COLUMN secrules.objectid IS 'the id of the protected object, 0 if all instances or not an entity';
COMMENT ON COLUMN secrules.contextclassid IS 'the class id of the DomainContext''s context entity, 0 if all contexts';
COMMENT ON COLUMN secrules.contextid IS 'the id of DomainContext''s context object, 0 if all instances';
COMMENT ON COLUMN secrules.granteeclassid IS 'the class id of the entity the permissions are granted to, 0 if all classes';
COMMENT ON COLUMN secrules.granteeid IS 'the id of the entity the permissions are granted to, 0 if all grantees';
COMMENT ON COLUMN secrules.secprio IS 'the priority or evaluation order, 0 is highest or first';
COMMENT ON COLUMN secrules.permissions IS 'the permissions as a comma-separated list';
COMMENT ON COLUMN secrules.allowed IS 'the false if denied, true if allowed';
COMMENT ON COLUMN secrules.message IS 'the user message';
COMMENT ON COLUMN secrules.id IS 'object id';
COMMENT ON COLUMN secrules.serial IS 'object serial';
CREATE INDEX secrules_entity ON secrules (objectid, objectclassid);
CREATE INDEX secrules_class ON secrules (objectclass);
CREATE INDEX secrules_context ON secrules (contextid, contextclassid);
CREATE INDEX secrules_grantee ON secrules (granteeid, granteeclassid);
CREATE TABLE tokenlock ( -- token lock table \
pdoclassid INTEGER NOT NULL, -- class ID of the PDO \
lockedby BIGINT NOT NULL, -- userId of token lock holder \
lockedsince TIMESTAMP, -- time since token lock given to user \
lockexpiry TIMESTAMP, -- time when token lock expires \
id BIGINT NOT NULL, -- object id \
serial BIGINT NOT NULL DEFAULT 1 -- object serial \
);
COMMENT ON TABLE tokenlock IS 'token lock table';
COMMENT ON COLUMN tokenlock.pdoclassid IS 'class ID of the PDO';
COMMENT ON COLUMN tokenlock.lockedby IS 'userId of token lock holder';
COMMENT ON COLUMN tokenlock.lockedsince IS 'time since token lock given to user';
COMMENT ON COLUMN tokenlock.lockexpiry IS 'time when token lock expires';
COMMENT ON COLUMN tokenlock.id IS 'object id';
COMMENT ON COLUMN tokenlock.serial IS 'object serial';
CREATE UNIQUE INDEX tokenlock_pdo ON tokenlock (id, pdoclassid);
-- referential integrity
ALTER TABLE bundlekey ADD CONSTRAINT bkey_bundle_id_fkey FOREIGN KEY (bundle_id) REFERENCES bundle (id) ON DELETE CASCADE;
-- sequences
CREATE SEQUENCE object_sequence_id; -- to generate the ID of PDOs