META-INF.sql.Oracle.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 NUMBER(19) NOT NULL, -- table serial
bname VARCHAR(128), -- the resource bundle name
blocale VARCHAR(8) NOT NULL, -- the locale, null if default
id NUMBER(19) NOT NULL PRIMARY KEY, -- object id
serial NUMBER(19) DEFAULT 1 NOT NULL -- 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 NUMBER(19) NOT NULL, -- the bundle id
bkey VARCHAR(4000), -- the resource bundle key
bvalue VARCHAR(4000), -- the localized string
id NUMBER(19) NOT NULL PRIMARY KEY, -- object id
serial NUMBER(19) DEFAULT 1 NOT NULL -- 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 NUMBER(19) NOT NULL PRIMARY KEY, -- object id
serial NUMBER(19) DEFAULT 1 NOT NULL -- 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 NUMBER(19) NOT NULL, -- object id
classid NUMBER(10) NOT NULL, -- object class id
classname VARCHAR(192), -- object classname (if classid == 0)
txid NUMBER(19) NOT NULL, -- transaction id (optional)
txname VARCHAR(64), -- transaction name (optional)
modtype CHAR(1) NOT NULL, -- modification type
modtime TIMESTAMP, -- time of event
userid NUMBER(19) NOT NULL, -- user id
message VARCHAR(4000), -- optional informational or error message
processed TIMESTAMP NOT NULL, -- processing time
id NUMBER(19) NOT NULL PRIMARY KEY, -- object id
serial NUMBER(19) DEFAULT 1 NOT NULL -- 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(4000), -- short description
poolonline NUMBER(1) NOT NULL, -- true if online, else offline
lowmark NUMBER(19) NOT NULL, -- minimum number count before request to fill up from uplink, 0 to disable
reqsize NUMBER(19) NOT NULL, -- number count to request from uplink, 0 to disable
uplink VARCHAR(4000), -- uplink configuration (optional)
id NUMBER(19) NOT NULL PRIMARY KEY, -- object id
serial NUMBER(19) DEFAULT 1 NOT NULL -- 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 NUMBER(19) NOT NULL, -- ID of the number pool
rbegin NUMBER(19) NOT NULL, -- begin of range
rend NUMBER(19) NOT NULL, -- end of range
id NUMBER(19) NOT NULL PRIMARY KEY, -- object id
serial NUMBER(19) DEFAULT 1 NOT NULL -- 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 NUMBER(19) NOT NULL, -- table serial
nodeid NUMBER(19) NOT NULL, -- ID of the preferences-node
rootnodeid NUMBER(19) NOT NULL, -- ID of the root-node
pkey VARCHAR(128), -- name of the key
pvalue VARCHAR(4000), -- value of the key
id NUMBER(19) NOT NULL PRIMARY KEY, -- object id
serial NUMBER(19) DEFAULT 1 NOT NULL -- 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 NUMBER(19) NOT NULL, -- table serial
username VARCHAR(32) NOT NULL, -- name of user, null if system
nodename VARCHAR(192), -- name of the node
parentid NUMBER(19) NOT NULL, -- ID of parent-node, 0 if root-node
rootnodeid NUMBER(19) NOT NULL, -- ID of the root-node
id NUMBER(19) NOT NULL PRIMARY KEY, -- object id
serial NUMBER(19) DEFAULT 1 NOT NULL -- 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 NUMBER(10) NOT NULL, -- the protected object's class id, 0 if not an entity
objectid NUMBER(19) NOT NULL, -- the id of the protected object, 0 if all instances or not an entity
contextclassid NUMBER(10) NOT NULL, -- the class id of the DomainContext's context entity, 0 if all contexts
contextid NUMBER(19) NOT NULL, -- the id of DomainContext's context object, 0 if all instances
granteeclassid NUMBER(10) NOT NULL, -- the class id of the entity the permissions are granted to, 0 if all classes
granteeid NUMBER(19) NOT NULL, -- the id of the entity the permissions are granted to, 0 if all grantees
secprio NUMBER(10) NOT NULL, -- the priority or evaluation order, 0 is highest or first
permissions VARCHAR(128), -- the permissions as a comma-separated list
allowed NUMBER(1) NOT NULL, -- the false if denied, true if allowed
message VARCHAR(4000), -- the user message
id NUMBER(19) NOT NULL PRIMARY KEY, -- object id
serial NUMBER(19) DEFAULT 1 NOT NULL -- 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 NUMBER(10) NOT NULL, -- class ID of the PDO
lockedby NUMBER(19) NOT NULL, -- userId of token lock holder
lockedsince TIMESTAMP, -- time since token lock given to user
lockexpiry TIMESTAMP, -- time when token lock expires
id NUMBER(19) NOT NULL, -- object id
serial NUMBER(19) DEFAULT 1 NOT NULL -- 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