META-INF.sql.MySQL.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 COMMENT 'table serial',
bname VARCHAR(128) COMMENT 'the resource bundle name',
blocale VARCHAR(8) NOT NULL COMMENT 'the locale, null if default',
id BIGINT NOT NULL PRIMARY KEY COMMENT 'object id',
serial BIGINT NOT NULL DEFAULT 1 COMMENT 'object serial'
) COMMENT='resource bundle';
CREATE UNIQUE INDEX bundle_udk ON bundle (bname, blocale);
CREATE TABLE bundlekey ( -- bundle key with translation
bundle_id BIGINT NOT NULL COMMENT 'the bundle id',
bkey VARCHAR(4096) COMMENT 'the resource bundle key',
bvalue VARCHAR(4096) COMMENT 'the localized string',
id BIGINT NOT NULL PRIMARY KEY COMMENT 'object id',
serial BIGINT NOT NULL DEFAULT 1 COMMENT 'object serial'
) COMMENT='bundle key with translation';
CREATE INDEX bundlekey_bundle ON bundlekey (bundle_id);
CREATE TABLE modification ( -- modification tracking table
tablename VARCHAR(64) COMMENT 'the tablename',
id BIGINT NOT NULL PRIMARY KEY COMMENT 'object id',
serial BIGINT NOT NULL DEFAULT 1 COMMENT 'object serial'
) COMMENT='modification tracking table';
CREATE UNIQUE INDEX modification_tablename ON modification (tablename);
CREATE TABLE modlog ( -- modification table for async coupling
objectid BIGINT NOT NULL COMMENT 'object id',
classid INT NOT NULL COMMENT 'object class id',
classname VARCHAR(192) COMMENT 'object classname (if classid == 0)',
txid BIGINT NOT NULL COMMENT 'transaction id (optional)',
txname VARCHAR(64) COMMENT 'transaction name (optional)',
modtype CHAR(1) NOT NULL COMMENT 'modification type',
modtime DATETIME COMMENT 'time of event',
userid BIGINT NOT NULL COMMENT 'user id',
message VARCHAR(4096) COMMENT 'optional informational or error message',
processed DATETIME NOT NULL COMMENT 'processing time',
id BIGINT NOT NULL PRIMARY KEY COMMENT 'object id',
serial BIGINT NOT NULL DEFAULT 1 COMMENT 'object serial'
) COMMENT='modification table for async coupling';
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) COMMENT 'the number pool name',
realm VARCHAR(80) NOT NULL COMMENT 'pool realm, optional',
description VARCHAR(4096) COMMENT 'short description',
poolonline TINYINT NOT NULL COMMENT 'true if online, else offline',
lowmark BIGINT NOT NULL COMMENT 'minimum number count before request to fill up from uplink, 0 to disable',
reqsize BIGINT NOT NULL COMMENT 'number count to request from uplink, 0 to disable',
uplink VARCHAR(4096) COMMENT 'uplink configuration (optional)',
id BIGINT NOT NULL PRIMARY KEY COMMENT 'object id',
serial BIGINT NOT NULL DEFAULT 1 COMMENT 'object serial'
) COMMENT='number pool';
CREATE UNIQUE INDEX numpool_udk ON numpool (name, realm);
CREATE TABLE numrange ( -- number range
poolid BIGINT NOT NULL COMMENT 'ID of the number pool',
rbegin BIGINT NOT NULL COMMENT 'begin of range',
rend BIGINT NOT NULL COMMENT 'end of range',
id BIGINT NOT NULL PRIMARY KEY COMMENT 'object id',
serial BIGINT NOT NULL DEFAULT 1 COMMENT 'object serial'
) COMMENT='number range';
CREATE TABLE prefkey ( -- a preferences key/value pair
tableserial BIGINT NOT NULL COMMENT 'table serial',
nodeid BIGINT NOT NULL COMMENT 'ID of the preferences-node',
rootnodeid BIGINT NOT NULL COMMENT 'ID of the root-node',
pkey VARCHAR(128) COMMENT 'name of the key',
pvalue VARCHAR(4096) COMMENT 'value of the key',
id BIGINT NOT NULL PRIMARY KEY COMMENT 'object id',
serial BIGINT NOT NULL DEFAULT 1 COMMENT 'object serial'
) COMMENT='a preferences key/value pair';
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 COMMENT 'table serial',
username VARCHAR(32) NOT NULL COMMENT 'name of user, null if system',
nodename VARCHAR(192) COMMENT 'name of the node',
parentid BIGINT NOT NULL COMMENT 'ID of parent-node, 0 if root-node',
rootnodeid BIGINT NOT NULL COMMENT 'ID of the root-node',
id BIGINT NOT NULL PRIMARY KEY COMMENT 'object id',
serial BIGINT NOT NULL DEFAULT 1 COMMENT 'object serial'
) COMMENT='a preferences node';
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) COMMENT 'the protected classname, null if an entity',
objectclassid INT NOT NULL COMMENT 'the protected object''s class id, 0 if not an entity',
objectid BIGINT NOT NULL COMMENT 'the id of the protected object, 0 if all instances or not an entity',
contextclassid INT NOT NULL COMMENT 'the class id of the DomainContext''s context entity, 0 if all contexts',
contextid BIGINT NOT NULL COMMENT 'the id of DomainContext''s context object, 0 if all instances',
granteeclassid INT NOT NULL COMMENT 'the class id of the entity the permissions are granted to, 0 if all classes',
granteeid BIGINT NOT NULL COMMENT 'the id of the entity the permissions are granted to, 0 if all grantees',
secprio INT NOT NULL COMMENT 'the priority or evaluation order, 0 is highest or first',
permissions VARCHAR(128) COMMENT 'the permissions as a comma-separated list',
allowed TINYINT NOT NULL COMMENT 'the false if denied, true if allowed',
message VARCHAR(4096) COMMENT 'the user message',
id BIGINT NOT NULL PRIMARY KEY COMMENT 'object id',
serial BIGINT NOT NULL DEFAULT 1 COMMENT 'object serial'
) COMMENT='security ACLs';
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 COMMENT 'class ID of the PDO',
lockedby BIGINT NOT NULL COMMENT 'userId of token lock holder',
lockedsince DATETIME COMMENT 'time since token lock given to user',
lockexpiry DATETIME COMMENT 'time when token lock expires',
id BIGINT NOT NULL COMMENT 'object id',
serial BIGINT NOT NULL DEFAULT 1 COMMENT 'object serial'
) COMMENT='token lock table';
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 not supported by backend MySQL!