META-INF.sql.Informix.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
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 INT8 NOT NULL, -- table serial
    bname VARCHAR(128), -- the resource bundle name
    blocale VARCHAR(8) NOT NULL, -- the locale, null if default
    id INT8 NOT NULL PRIMARY KEY, -- object id
    serial INT8 NOT NULL DEFAULT 1 -- object serial
);
CREATE UNIQUE INDEX bundle_udk ON bundle (bname, blocale);

CREATE TABLE bundlekey ( -- bundle key with translation
    bundle_id INT8 NOT NULL, -- the bundle id
    bkey VARCHAR(255), -- the resource bundle key
    bvalue VARCHAR(255), -- the localized string
    id INT8 NOT NULL PRIMARY KEY, -- object id
    serial INT8 NOT NULL DEFAULT 1 -- object serial
);
CREATE INDEX bundlekey_bundle ON bundlekey (bundle_id);

CREATE TABLE modification ( -- modification tracking table
    tablename VARCHAR(64), -- the tablename
    id INT8 NOT NULL PRIMARY KEY, -- object id
    serial INT8 NOT NULL DEFAULT 1 -- object serial
);
CREATE UNIQUE INDEX modification_tablename ON modification (tablename);

CREATE TABLE modlog ( -- modification table for async coupling
    objectid INT8 NOT NULL, -- object id
    classid INT NOT NULL, -- object class id
    classname VARCHAR(192), -- object classname (if classid == 0)
    txid INT8 NOT NULL, -- transaction id (optional)
    txname VARCHAR(64), -- transaction name (optional)
    modtype CHAR(1) NOT NULL, -- modification type
    modtime DATETIME YEAR TO SECOND, -- time of event
    userid INT8 NOT NULL, -- user id
    message VARCHAR(255), -- optional informational or error message
    processed DATETIME YEAR TO SECOND NOT NULL, -- processing time
    id INT8 NOT NULL PRIMARY KEY, -- object id
    serial INT8 NOT NULL DEFAULT 1 -- 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(255), -- short description
    poolonline BOOLEAN NOT NULL, -- true if online, else offline
    lowmark INT8 NOT NULL, -- minimum number count before request to fill up from uplink, 0 to disable
    reqsize INT8 NOT NULL, -- number count to request from uplink, 0 to disable
    uplink VARCHAR(255), -- uplink configuration (optional)
    id INT8 NOT NULL PRIMARY KEY, -- object id
    serial INT8 NOT NULL DEFAULT 1 -- object serial
);
CREATE UNIQUE INDEX numpool_udk ON numpool (name, realm);

CREATE TABLE numrange ( -- number range
    poolid INT8 NOT NULL, -- ID of the number pool
    rbegin INT8 NOT NULL, -- begin of range
    rend INT8 NOT NULL, -- end of range
    id INT8 NOT NULL PRIMARY KEY, -- object id
    serial INT8 NOT NULL DEFAULT 1 -- object serial
);

CREATE TABLE prefkey ( -- a preferences key/value pair
    tableserial INT8 NOT NULL, -- table serial
    nodeid INT8 NOT NULL, -- ID of the preferences-node
    rootnodeid INT8 NOT NULL, -- ID of the root-node
    pkey VARCHAR(128), -- name of the key
    pvalue VARCHAR(255), -- value of the key
    id INT8 NOT NULL PRIMARY KEY, -- object id
    serial INT8 NOT NULL DEFAULT 1 -- 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 INT8 NOT NULL, -- table serial
    username VARCHAR(32) NOT NULL, -- name of user, null if system
    nodename VARCHAR(192), -- name of the node
    parentid INT8 NOT NULL, -- ID of parent-node, 0 if root-node
    rootnodeid INT8 NOT NULL, -- ID of the root-node
    id INT8 NOT NULL PRIMARY KEY, -- object id
    serial INT8 NOT NULL DEFAULT 1 -- 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 INT NOT NULL, -- the protected object's class id, 0 if not an entity
    objectid INT8 NOT NULL, -- the id of the protected object, 0 if all instances or not an entity
    contextclassid INT NOT NULL, -- the class id of the DomainContext's context entity, 0 if all contexts
    contextid INT8 NOT NULL, -- the id of DomainContext's context object, 0 if all instances
    granteeclassid INT NOT NULL, -- the class id of the entity the permissions are granted to, 0 if all classes
    granteeid INT8 NOT NULL, -- the id of the entity the permissions are granted to, 0 if all grantees
    secprio INT NOT NULL, -- the priority or evaluation order, 0 is highest or first
    permissions VARCHAR(128), -- the permissions as a comma-separated list
    allowed BOOLEAN NOT NULL, -- the false if denied, true if allowed
    message VARCHAR(255), -- the user message
    id INT8 NOT NULL PRIMARY KEY, -- object id
    serial INT8 NOT NULL DEFAULT 1 -- 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 INT NOT NULL, -- class ID of the PDO
    lockedby INT8 NOT NULL, -- userId of token lock holder
    lockedsince DATETIME YEAR TO SECOND, -- time since token lock given to user
    lockexpiry DATETIME YEAR TO SECOND, -- time when token lock expires
    id INT8 NOT NULL, -- object id
    serial INT8 NOT NULL DEFAULT 1 -- 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