1 /* Initialisation script for CPS.
\r
3 To be moved to cps-ri in Honolulu. */
\r
6 CREATE TABLE IF NOT EXISTS RELATION_TYPE
\r
8 RELATION_TYPE TEXT NOT NULL,
\r
9 ID SERIAL PRIMARY KEY
\r
12 CREATE TABLE IF NOT EXISTS DATASPACE
\r
14 ID SERIAL PRIMARY KEY,
\r
16 CONSTRAINT "UQ_NAME" UNIQUE (NAME)
\r
19 CREATE TABLE IF NOT EXISTS SCHEMA_NODE
\r
21 SCHEMA_NODE_IDENTIFIER TEXT NOT NULL,
\r
22 ID SERIAL PRIMARY KEY
\r
25 CREATE TABLE IF NOT EXISTS MODULE_SET
\r
27 MODULE_SET_REFERENCE TEXT NOT NULL,
\r
28 ID SERIAL PRIMARY KEY
\r
31 CREATE TABLE IF NOT EXISTS FRAGMENT
\r
33 ID BIGSERIAL PRIMARY KEY,
\r
34 XPATH TEXT NOT NULL,
\r
35 DATASPACE_ID INTEGER NOT NULL REFERENCES DATASPACE(ID),
\r
37 ANCHOR_ID BIGINT REFERENCES FRAGMENT(ID),
\r
38 PARENT_ID BIGINT REFERENCES FRAGMENT(ID),
\r
39 MODULE_SET_ID INTEGER REFERENCES MODULE_SET(ID),
\r
40 SCHEMA_NODE_ID INTEGER REFERENCES SCHEMA_NODE(ID)
\r
43 CREATE TABLE IF NOT EXISTS RELATION
\r
45 FROM_FRAGMENT_ID BIGINT NOT NULL REFERENCES FRAGMENT(ID),
\r
46 TO_FRAGMENT_ID BIGINT NOT NULL REFERENCES FRAGMENT(ID),
\r
47 RELATION_TYPE_ID INTEGER NOT NULL REFERENCES RELATION_TYPE(ID),
\r
48 FROM_REL_XPATH TEXT NOT NULL,
\r
49 TO_REL_XPATH TEXT NOT NULL,
\r
50 CONSTRAINT RELATION_PKEY PRIMARY KEY (TO_FRAGMENT_ID, FROM_FRAGMENT_ID, RELATION_TYPE_ID)
\r
53 CREATE INDEX IF NOT EXISTS "FKI_FRAGMENT_DATASPACE_ID_FK" ON FRAGMENT USING BTREE(DATASPACE_ID) ;
\r
54 CREATE INDEX IF NOT EXISTS "FKI_FRAGMENT_MODULE_SET_ID_FK" ON FRAGMENT USING BTREE(MODULE_SET_ID) ;
\r
55 CREATE INDEX IF NOT EXISTS "FKI_FRAGMENT_PARENT_ID_FK" ON FRAGMENT USING BTREE(PARENT_ID) ;
\r
56 CREATE INDEX IF NOT EXISTS "FKI_FRAGMENT_ANCHOR_ID_FK" ON FRAGMENT USING BTREE(ANCHOR_ID) ;
\r
57 CREATE INDEX IF NOT EXISTS "PERF_SCHEMA_NODE_SCHEMA_NODE_ID" ON SCHEMA_NODE USING BTREE(SCHEMA_NODE_IDENTIFIER) ;
\r
58 CREATE INDEX IF NOT EXISTS "FKI_SCHEMA_NODE_ID_TO_ID" ON FRAGMENT USING BTREE(SCHEMA_NODE_ID) ;
\r
59 CREATE INDEX IF NOT EXISTS "FKI_RELATION_TYPE_ID_FK" ON RELATION USING BTREE(RELATION_TYPE_ID);
\r
60 CREATE INDEX IF NOT EXISTS "FKI_RELATIONS_FROM_ID_FK" ON RELATION USING BTREE(FROM_FRAGMENT_ID);
\r
61 CREATE INDEX IF NOT EXISTS "FKI_RELATIONS_TO_ID_FK" ON RELATION USING BTREE(TO_FRAGMENT_ID);
\r
62 CREATE INDEX IF NOT EXISTS "PERF_MODULE_SET_MODULE_SET_REFERENCE" ON MODULE_SET USING BTREE(MODULE_SET_REFERENCE) ;
\r
63 CREATE UNIQUE INDEX IF NOT EXISTS "UQ_FRAGMENT_XPATH"ON FRAGMENT USING btree(xpath COLLATE pg_catalog."default" text_pattern_ops, dataspace_id);