META-INF.sql.MsSQL.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 BIGINT NOT NULL, -- table serial
    bname NVARCHAR(128), -- the resource bundle name
    blocale NVARCHAR(8) NOT NULL, -- the locale, null if default
    id BIGINT NOT NULL PRIMARY KEY, -- object id
    serial BIGINT NOT NULL DEFAULT 1 -- 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 NVARCHAR(MAX), -- the resource bundle key
    bvalue NVARCHAR(MAX), -- the localized string
    id BIGINT NOT NULL PRIMARY KEY, -- object id
    serial BIGINT NOT NULL DEFAULT 1 -- object serial
);
CREATE INDEX bundlekey_bundle ON bundlekey (bundle_id);

CREATE TABLE modification ( -- modification tracking table
    tablename NVARCHAR(64), -- the tablename
    id BIGINT NOT NULL PRIMARY KEY, -- object id
    serial BIGINT NOT NULL DEFAULT 1 -- 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 INT NOT NULL, -- object class id
    classname NVARCHAR(192), -- object classname (if classid == 0)
    txid BIGINT NOT NULL, -- transaction id (optional)
    txname NVARCHAR(64), -- transaction name (optional)
    modtype NCHAR(1) NOT NULL, -- modification type
    modtime DATETIME, -- time of event
    userid BIGINT NOT NULL, -- user id
    message NVARCHAR(MAX), -- optional informational or error message
    processed DATETIME NOT NULL, -- processing time
    id BIGINT NOT NULL PRIMARY KEY, -- object id
    serial BIGINT 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 NVARCHAR(30), -- the number pool name
    realm NVARCHAR(80) NOT NULL, -- pool realm, optional
    description NVARCHAR(MAX), -- short description
    poolonline BIT 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 NVARCHAR(MAX), -- uplink configuration (optional)
    id BIGINT NOT NULL PRIMARY KEY, -- object id
    serial BIGINT NOT NULL DEFAULT 1 -- 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
);

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 NVARCHAR(128), -- name of the key
    pvalue NVARCHAR(MAX), -- value of the key
    id BIGINT NOT NULL PRIMARY KEY, -- object id
    serial BIGINT 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 BIGINT NOT NULL, -- table serial
    username NVARCHAR(32) NOT NULL, -- name of user, null if system
    nodename NVARCHAR(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
);
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 NVARCHAR(128), -- the protected classname, null if an entity
    objectclassid INT 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 INT 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 INT 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 INT NOT NULL, -- the priority or evaluation order, 0 is highest or first
    permissions NVARCHAR(128), -- the permissions as a comma-separated list
    allowed BIT NOT NULL, -- the false if denied, true if allowed
    message NVARCHAR(MAX), -- the user message
    id BIGINT NOT NULL PRIMARY KEY, -- object id
    serial BIGINT 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 BIGINT NOT NULL, -- userId of token lock holder
    lockedsince DATETIME, -- time since token lock given to user
    lockexpiry DATETIME, -- time when token lock expires
    id BIGINT NOT NULL, -- object id
    serial BIGINT 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