All Downloads are FREE. Search and download functionalities are using the official Maven repository.
  • Log In
  • Register

    • sql.Ingres.createmodel.sql Maven / Gradle / Ivy

    Go to download

    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!

    There is a newer version: 21.16.1.0
    Show newest version
    
-- created from tentackle/target/wurbel/model at Mon Feb 05 18:23:40 CET 2018 by harald on gandalf
-- backend is Ingres

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 -- 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(1024), -- the resource bundle key
    bvalue VARCHAR(1024), -- the localized string
    id BIGINT NOT NULL PRIMARY KEY, -- object id
    serial BIGINT 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 BIGINT NOT NULL PRIMARY KEY, -- object id
    serial BIGINT 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 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
    moduser VARCHAR(32), -- name of user
    message VARCHAR(1024), -- optional informational or error message
    processed TIMESTAMP NOT NULL, -- processing time
    id BIGINT NOT NULL PRIMARY KEY, -- object id
    serial BIGINT 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.moduser IS 'name of user';
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 (processed, objectid, classid);
CREATE INDEX modlog_user ON modlog (processed, moduser);

CREATE TABLE numpool ( -- number pool
    name VARCHAR(30), -- the number pool name
    realm VARCHAR(80) NOT NULL, -- pool realm, optional
    description VARCHAR(1024), -- short description
    poolonline TINYINT NOT NULL, -- true if online, else offline
    lowmark BIGINT NOT NULL, -- minimum number count before request to fill up from uplink, 0 if disable
    reqsize BIGINT NOT NULL, -- number count to request from uplink, 0 if disable
    uplink VARCHAR(1024), -- uplink configuration (optional)
    id BIGINT NOT NULL PRIMARY KEY, -- object id
    serial BIGINT 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 if disable';
COMMENT ON COLUMN numpool.reqsize IS 'number count to request from uplink, 0 if 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 -- 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(255), -- value of the key
    id BIGINT NOT NULL PRIMARY KEY, -- object id
    serial BIGINT 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 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 rootnode
    rootnodeid BIGINT NOT NULL, -- ID of the root node
    id BIGINT NOT NULL PRIMARY KEY, -- object id
    serial BIGINT 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 rootnode';
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, null 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 TINYINT NOT NULL, -- the false if denied, true if allowed
    message VARCHAR(1024), -- the user message
    id BIGINT NOT NULL PRIMARY KEY, -- object id
    serial BIGINT 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, null 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);

-- referential integrity
ALTER TABLE bundlekey ADD CONSTRAINT bkey_bundle_id_fkey FOREIGN KEY (bundle_id) REFERENCES bundle (id) ON DELETE CASCADE;




    © 2015 - 2025 Weber Informatics LLC | Privacy Policy