analytics/configuration/schemas/ans/hsql/ANS2_CREATE_Hsql.sql

--
--    Licensed Materials - Property of IBM
--    
--    BI and PM: ANS
--    
--    (c) Copyright IBM Corp. 2003, 2010.
--    
--    US Government Users Restricted RigANS - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
--    

--
--  Modifications:
--
--  1) Change NCCOG  to the desired database name.
--  2) Note: CCSID is an estimated value, your
--           requirements may differ.

-------------------------------------------------------------------------------
-- ANS_CREATE_Hsql.sql         *  DO NOT EDIT  *          Data Store Version: 2
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
--$200-- tables create begin
-------------------------------------------------------------------------------
CREATE TABLE ANN_ANNOTATION (id BIGINT NOT NULL, creationTime TIMESTAMP, lastModificationTime TIMESTAMP, locale VARCHAR(255), owner VARCHAR(255), parentId VARCHAR(33), secondaryParentId VARCHAR(33), contextId BIGINT, textId BIGINT NOT NULL, PRIMARY KEY (id));
CREATE TABLE ANN_ANNOTATION_PROPS (id BIGINT NOT NULL, name VARCHAR(255), value VARCHAR(255), annotationId BIGINT NOT NULL, PRIMARY KEY (id));
CREATE TABLE ANN_ANNOTATION_TEXT (id BIGINT NOT NULL, description VARCHAR(3600), name VARCHAR(128), PRIMARY KEY (id));
CREATE TABLE ANN_CONTEXT (id BIGINT NOT NULL, compID VARCHAR(255), PRIMARY KEY (id));
CREATE TABLE ANN_CONTEXT_DIMENSIONMEMBER (contextId BIGINT, dimensionMemberId BIGINT);
CREATE TABLE ANN_DATAITEM (id BIGINT NOT NULL, name VARCHAR(64) NOT NULL, vE SMALLINT NOT NULL, value VARCHAR(255) NOT NULL, PRIMARY KEY (id), CONSTRAINT UNQ_namevaluevE UNIQUE (name, value, vE));
CREATE TABLE ANN_DIMENSION (id BIGINT NOT NULL, compID VARCHAR(255), PRIMARY KEY (id));
CREATE TABLE ANN_DIMENSIONMEMBER (id BIGINT NOT NULL, compID VARCHAR(255) NOT NULL, dimId BIGINT NOT NULL, PRIMARY KEY (id), CONSTRAINT UNQ_dimIdcompID UNIQUE (dimId, compID));
CREATE TABLE ANN_DIMENSIONMEMBER_DATAITEM (dimensionMemberId BIGINT, dataItemId BIGINT);
CREATE TABLE ANN_DIMENSION_METADATAITEM (dimId BIGINT, metadataId BIGINT);
CREATE TABLE ANN_IDTABLE (PK VARCHAR(255) NOT NULL, IDMAX BIGINT, PRIMARY KEY (PK));
CREATE TABLE ANN_METADATAITEM (id BIGINT NOT NULL, name VARCHAR(64) NOT NULL, vE SMALLINT NOT NULL, value VARCHAR(255) NOT NULL, PRIMARY KEY (id), CONSTRAINT UNQ_valuenamevE UNIQUE (value, name, vE));
CREATE TABLE ANN_MODEL (id BIGINT NOT NULL, PRIMARY KEY (id));
CREATE TABLE ANN_MODEL_DIMENSION (modelId BIGINT, dimId BIGINT);
CREATE TABLE ANN_VERSION (id VARCHAR(32) NOT NULL, version INTEGER, PRIMARY KEY (id));


-------------------------------------------------------------------------------
--$201-- indices create begin
-------------------------------------------------------------------------------

CREATE INDEX I_NN_NTTN_CONTEXT ON ANN_ANNOTATION (contextId);
CREATE INDEX I_NN_NTTN_TEXT ON ANN_ANNOTATION (textId);
CREATE INDEX I_NN_NRPS_ANNOTATION ON ANN_ANNOTATION_PROPS (annotationId);
CREATE UNIQUE INDEX I_NN_CTXT_COMPID ON ANN_CONTEXT (compID);
CREATE INDEX I_NN_CMBR_CONTEXTID ON ANN_CONTEXT_DIMENSIONMEMBER (contextId);
CREATE INDEX I_NN_CMBR_ELEMENT ON ANN_CONTEXT_DIMENSIONMEMBER (dimensionMemberId);
CREATE UNIQUE INDEX I_NN_DNSN_COMPID ON ANN_DIMENSION (compID);
CREATE INDEX I_NN_DMBR_COMPID ON ANN_DIMENSIONMEMBER (compID);
CREATE INDEX I_NN_DMBR_DIMENSION ON ANN_DIMENSIONMEMBER (dimId);
CREATE INDEX I_NN_DTTM_DIMENSIONMEMBERID ON ANN_DIMENSIONMEMBER_DATAITEM (dimensionMemberId);
CREATE INDEX I_NN_DTTM_ELEMENT ON ANN_DIMENSIONMEMBER_DATAITEM (dataItemId);
CREATE INDEX I_NN_DTTM_DIMID ON ANN_DIMENSION_METADATAITEM (dimId);
CREATE INDEX I_NN_DTTM_ELEMENT1 ON ANN_DIMENSION_METADATAITEM (metadataId);
CREATE INDEX I_NN_MNSN_ELEMENT ON ANN_MODEL_DIMENSION (dimId);
CREATE INDEX I_NN_MNSN_MODELID ON ANN_MODEL_DIMENSION (modelId);



-------------------------------------------------------------------------------
--$202-- primary keys add begin
-------------------------------------------------------------------------------




-------------------------------------------------------------------------------
--$240-- foreign keys add begin
-------------------------------------------------------------------------------




-------------------------------------------------------------------------------
--$210-- views create begin
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
--$220-- server data inserts begin
-------------------------------------------------------------------------------

INSERT INTO ANN_VERSION(id, version) VALUES ('2', 2);




-------------------------------------------------------------------------------
--	END ANS_CREATE_Hsql.sql        *  DO NOT EDIT  *
-------------------------------------------------------------------------------