Ticket #8349: sqlall.sql

CREATE TABLE "SITES" (
    "SITEID" NUMBER(38, 0) NOT NULL PRIMARY KEY,
    "OFFICIALNAME" NVARCHAR2(200) NULL,
    "SITENAME" NVARCHAR2(200) NULL,
    "FRIENDLYNAME" NVARCHAR2(100) NULL,
    "DOMAIN" NVARCHAR2(50) NULL,
    "HOMEURI" NVARCHAR2(255) NULL,
    "EMAIL" NVARCHAR2(100) NULL,
    "CONTACTTEL" NVARCHAR2(255) NULL,
    "EMERGENCYTEL" NVARCHAR2(255) NULL,
    "CSIRTEMAIL" NVARCHAR2(255) NULL,
    "CSIRTTEL" NVARCHAR2(255) NULL,
    "HOURS" NVARCHAR2(30) NULL,
    "TIMEZONEID" NUMBER(38, 0) NULL,
    "COUNTRY" NVARCHAR2(50) NULL,
    "REGION" NVARCHAR2(10) NULL,
    "TIER" NUMBER(38, 0) NULL,
    "GIISURL" NVARCHAR2(250) NULL,
    "PRIMARYSITE" NVARCHAR2(1) NULL,
    "INMONITORING" NVARCHAR2(1) NULL,
    "SITETAG" NVARCHAR2(50) NULL,
    "SITECVSTAG" NVARCHAR2(50) NULL,
    "COMMENTS" NVARCHAR2(255) NULL,
    "ISCERTIFIED" NVARCHAR2(1) NULL,
    "STATUS" NVARCHAR2(20) NULL,
    "TYPE" NVARCHAR2(20) NULL,
    "ORGID" NUMBER(38, 0) NULL,
    "LATITUDE" NUMBER(126, -127) NULL,
    "LONGITUDE" NUMBER(126, -127) NULL,
    "FWIP" NVARCHAR2(255) NULL,
    "ISDELETED" NVARCHAR2(1) NULL
)
;
CREATE TABLE "VO" (
    "VOID" NUMBER(38, 0) NOT NULL PRIMARY KEY,
    "VONAME" NVARCHAR2(100) NULL
)
;
CREATE TABLE "SERVICE" (
    "SERVICEID" NUMBER(38, 0) NOT NULL PRIMARY KEY,
    "WEIGHT" NUMBER(38, 0) NOT NULL,
    "SERVICESCOPE" NVARCHAR2(1) NULL,
    "SERVICEABBR" NVARCHAR2(8) NULL,
    "SERVICENAME" NVARCHAR2(100) NULL,
    "ISDELETED" NVARCHAR2(1) NULL
)
;
CREATE TABLE "TESTDEF" (
    "TESTID" NUMBER(38, 0) NOT NULL PRIMARY KEY,
    "SERVICEID" NUMBER(38, 0) NOT NULL,
    "CREATEDTIMESTAMP" NUMBER(38, 0) NOT NULL,
    "TESTNAME" NVARCHAR2(100) NULL,
    "TESTTITLE" NVARCHAR2(255) NULL,
    "TESTABBR" NVARCHAR2(50) NULL,
    "DATATYPE" NVARCHAR2(64) NULL,
    "DATAUNIT" NVARCHAR2(30) NULL,
    "DATATHRESHOLD" NVARCHAR2(64) NULL,
    "TESTHELP" NCLOB NULL,
    "ISDELETED" NVARCHAR2(1) NULL
)
;
CREATE TABLE "VOSITE" (
    "VOSITEID" NUMBER(0, -127) NOT NULL PRIMARY KEY,
    "SITEID" NUMBER(38, 0) NULL REFERENCES "SITES" ("SITEID") DEFERRABLE INITIALLY DEFERRED,
    "VOSITENAME" NVARCHAR2(255) NULL,
    "TIER" NUMBER(0, -127) NULL
)
;
CREATE TABLE "VOSITEMAP" (
    "ID" NUMBER(11) NOT NULL PRIMARY KEY,
    "SITEID" NUMBER(38, 0) NULL REFERENCES "SITES" ("SITEID") DEFERRABLE INITIALLY DEFERRED,
    "VOSITEID" NUMBER(0, -127) NULL REFERENCES "VOSITE" ("VOSITEID") DEFERRABLE INITIALLY DEFERRED
)
;
CREATE SEQUENCE VOSITEMAP_SQ;

            CREATE OR REPLACE TRIGGER VOSITEMAP_TR
            BEFORE INSERT ON "VOSITEMAP"
            FOR EACH ROW
            WHEN (new."ID" IS NULL)
                BEGIN
                    SELECT VOSITEMAP_SQ.nextval
                    INTO :new."ID" FROM dual;
                END;
                /
CREATE TABLE "TOPOLOGYGROUP" (
    "GROUPID" NUMBER(38, 0) NOT NULL PRIMARY KEY,
    "GROUPNAME" NVARCHAR2(256) NULL,
    "VOID" NUMBER(38, 0) NOT NULL,
    "ISVISIBLE" NUMBER(2, 0) NOT NULL
)
;
CREATE TABLE "TOPOLOGYENTRY" (
    "ID" NUMBER(11) NOT NULL PRIMARY KEY,
    "GROUPID" NUMBER(38, 0) NOT NULL REFERENCES "TOPOLOGYGROUP" ("GROUPID") DEFERRABLE INITIALLY DEFERRED,
    "SITEID" NUMBER(38, 0) NOT NULL
)
;
CREATE SEQUENCE TOPOLOGYENTRY_SQ;

            CREATE OR REPLACE TRIGGER TOPOLOGYENTRY_TR
            BEFORE INSERT ON "TOPOLOGYENTRY"
            FOR EACH ROW
            WHEN (new."ID" IS NULL)
                BEGIN
                    SELECT TOPOLOGYENTRY_SQ.nextval
                    INTO :new."ID" FROM dual;
                END;
                /
CREATE TABLE "AVL_ATLAS" (
    "AVAILABILITYID" NUMBER(11) CHECK ("AVAILABILITYID" >= 0) NOT NULL PRIMARY KEY,
    "AVAILABILITYNAME" NVARCHAR2(255) NULL,
    "BDIIFLAG" NVARCHAR2(1) NULL,
    "VOID" NUMBER(38, 0) NOT NULL REFERENCES "VO" ("VOID") DEFERRABLE INITIALLY DEFERRED
)
;
CREATE TABLE "AVAILABILITYINSTANCE" (
    "AVAILABILITYINSTANCEID" NUMBER(0, -127) NOT NULL PRIMARY KEY,
    "AVAILABILITYID" NUMBER(11) NOT NULL REFERENCES "AVL_ATLAS" ("AVAILABILITYID") DEFERRABLE INITIALLY DEFERRED,
    "VOSITEID" NUMBER(0, -127) NOT NULL REFERENCES "VOSITE" ("VOSITEID") DEFERRABLE INITIALLY DEFERRED
)
;
CREATE TABLE "AVAILABILITYTESTCRITICALITY" (
    "ID" NUMBER(11) NOT NULL PRIMARY KEY,
    "AVAILABILITY_ID" NUMBER(11) NOT NULL REFERENCES "AVL_ATLAS" ("AVAILABILITYID") DEFERRABLE INITIALLY DEFERRED,
    "TESTDEF_ID" NUMBER(38, 0) NOT NULL REFERENCES "TESTDEF" ("TESTID") DEFERRABLE INITIALLY DEFERRED,
    UNIQUE ("AVAILABILITY_ID", "TESTDEF_ID")
)
;
CREATE SEQUENCE AVAILABILITYTESTCRITICALITY_SQ;

            CREATE OR REPLACE TRIGGER AVAILABILITYTESTCRITICALITY_TR
            BEFORE INSERT ON "AVAILABILITYTESTCRITICALITY"
            FOR EACH ROW
            WHEN (new."ID" IS NULL)
                BEGIN
                    SELECT AVAILABILITYTESTCRITICALITY_SQ.nextval
                    INTO :new."ID" FROM dual;
                END;
                /
CREATE INDEX "VOSITE_SITEID" ON "VOSITE" ("SITEID");
CREATE INDEX "VOSITEMAP_SITEID" ON "VOSITEMAP" ("SITEID");
CREATE INDEX "VOSITEMAP_VOSITEID" ON "VOSITEMAP" ("VOSITEID");
CREATE INDEX "TOPOLOGYENTRY_GROUPID" ON "TOPOLOGYENTRY" ("GROUPID");
CREATE INDEX "AVL_ATLAS_VOID" ON "AVL_ATLAS" ("VOID");
CREATE INDEX "AVAILABILITYINSTANCE_AVAILC314" ON "AVAILABILITYINSTANCE" ("AVAILABILITYID");
CREATE INDEX "AVAILABILITYINSTANCE_VOSITEID" ON "AVAILABILITYINSTANCE" ("VOSITEID");
COMMIT;