Advertisement
Guest User

Untitled

a guest
Apr 11th, 2018
29
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 3.65 KB | None | 0 0
  1. CREATE TABLE CATEGORY
  2. (
  3.   ID        INTEGER AUTO_INCREMENT PRIMARY KEY,
  4.   PARENT_ID INTEGER
  5.     CONSTRAINT CATEGORY_CATEGORY_ID_FK
  6.     REFERENCES CATEGORY,
  7.   NAME      CLOB
  8. );
  9.  
  10. CREATE TABLE COLLECTION
  11. (
  12.   ID                INTEGER AUTO_INCREMENT PRIMARY KEY,
  13.   NAME              VARCHAR(255),
  14.   CATEGORY_ID       INTEGER DEFAULT NULL
  15.     CONSTRAINT COLLECTION_CATEGORY_ID_FK
  16.     REFERENCES CATEGORY,
  17.   DESCRIPTION       CLOB,
  18.   DATETIME_CREATED  TIMESTAMP WITH TIME ZONE(30, 10) DEFAULT NOW() NOT NULL,
  19.   DATETIME_MODIFIED TIMESTAMP WITH TIME ZONE(30, 10) DEFAULT NOW() NOT NULL,
  20.   DELETED           BOOLEAN DEFAULT FALSE                          NOT NULL,
  21.   ACTIVE            BOOLEAN DEFAULT TRUE                           NOT NULL
  22. );
  23.  
  24. CREATE INDEX COLLECTION_ACTIVE_INDEX
  25.   ON COLLECTION (ACTIVE);
  26.  
  27. CREATE INDEX COLLECTION_DELETED_INDEX
  28.   ON COLLECTION (DELETED);
  29.  
  30. CREATE INDEX COLLECTION_NAME_INDEX
  31.   ON COLLECTION (NAME);
  32.  
  33. CREATE TABLE COLLECTION_PARENT_COLLECTION_ASSN
  34. (
  35.   COLLECTION_ID        INTEGER              NOT NULL
  36.     CONSTRAINT COLLECTION__PARENT_COLLECTION_COLLECTION_ID_FK
  37.     REFERENCES COLLECTION,
  38.   PARENT_COLLECTION_ID INTEGER              NOT NULL
  39.     CONSTRAINT COLLECTION__PARENT_COLLECTION_COLLECTION_ID_FK_2
  40.     REFERENCES COLLECTION,
  41.   EXCLUSIVE            BOOLEAN DEFAULT TRUE NOT NULL,
  42.   CONSTRAINT COLLECTION__PARENT_COLLECTION_COLLECTION_ID_PARENT_COLLECTION_ID_PK
  43.   PRIMARY KEY (COLLECTION_ID, PARENT_COLLECTION_ID)
  44. );
  45.  
  46. CREATE TABLE ITEM
  47. (
  48.   ID                INTEGER AUTO_INCREMENT PRIMARY KEY,
  49.   NAME              VARCHAR(255)                                   NOT NULL,
  50.   CATEGORY_ID       INTEGER      DEFAULT NULL
  51.     CONSTRAINT ITEM_CATEGORY_ID_FK
  52.     REFERENCES CATEGORY,
  53.   VERSION           VARCHAR(255),
  54.   DATA              CLOB,
  55.   DATETIME_CREATED  TIMESTAMP WITH TIME ZONE(30, 10) DEFAULT NOW() NOT NULL,
  56.   DATETIME_MODIFIED TIMESTAMP WITH TIME ZONE(30, 10) DEFAULT NOW() NOT NULL,
  57.   DELETED           BOOLEAN DEFAULT FALSE                          NOT NULL,
  58.   ACTIVE            BOOLEAN DEFAULT TRUE                           NOT NULL
  59. );
  60.  
  61. CREATE TABLE COLLECTION_ITEM_ASSN
  62. (
  63.   COLLECTION_ID INTEGER           NOT NULL
  64.     CONSTRAINT COLLECTION__ITEM_COLLECTION_ID_FK
  65.     REFERENCES COLLECTION,
  66.   ITEM_ID       INTEGER           NOT NULL
  67.     CONSTRAINT COLLECTION__ITEM_ITEM_ID_FK
  68.     REFERENCES ITEM,
  69.   QUANTITY      INTEGER DEFAULT 1 NOT NULL,
  70.   CONSTRAINT COLLECTION__ITEM_COLLECTION_ID_ITEM_ID_PK
  71.   PRIMARY KEY (COLLECTION_ID, ITEM_ID)
  72. );
  73.  
  74. CREATE INDEX ITEM_ACTIVE_INDEX
  75.   ON ITEM (ACTIVE);
  76.  
  77. CREATE INDEX ITEM_DELETED_INDEX
  78.   ON ITEM (DELETED);
  79.  
  80. CREATE INDEX ITEM_NAME_INDEX
  81.   ON ITEM (NAME);
  82.  
  83. CREATE INDEX ITEM_NAME_VERSION_INDEX
  84.   ON ITEM (NAME, VERSION);
  85.  
  86. CREATE INDEX ITEM_VERSION_INDEX
  87.   ON ITEM (VERSION);
  88.  
  89. CREATE TABLE TAG
  90. (
  91.   ID          INTEGER AUTO_INCREMENT PRIMARY KEY,
  92.   NAME        CLOB NOT NULL,
  93.   CATEGORY_ID INTEGER DEFAULT NULL
  94.     CONSTRAINT TAG_CATEGORY_ID_FK
  95.     REFERENCES CATEGORY,
  96.   DATA        CLOB
  97. );
  98.  
  99. CREATE TABLE TAG_COLLECTION_ASSN
  100. (
  101.   TAG_ID      INTEGER NOT NULL
  102.     CONSTRAINT TAG_COLLECTION_ASSN_TAG_ID_FK
  103.     REFERENCES TAG,
  104.   COLLECTION_ID INTEGER      NOT NULL
  105.     CONSTRAINT TAG_COLLECTION_ASSN_COLLECTION_ID_FK
  106.     REFERENCES COLLECTION,
  107.   CONSTRAINT TAG_COLLECTION_ASSN_TAG_ID_COLLECTION_ID_PK
  108.   PRIMARY KEY (TAG_ID, COLLECTION_ID)
  109. );
  110.  
  111. CREATE TABLE TAG_ITEM_ASSN
  112. (
  113.   TAG_ID INTEGER NOT NULL
  114.     CONSTRAINT TAG_ITEM_ASSN_TAG_ID_FK
  115.     REFERENCES TAG,
  116.   ITEM_ID  INTEGER      NOT NULL
  117.     CONSTRAINT TAG_ITEM_ASSN_ITEM_ID_FK
  118.     REFERENCES ITEM,
  119.   CONSTRAINT TAG_ITEM_ASSN_TAG_ID_ITEM_ID_PK
  120.   PRIMARY KEY (TAG_ID, ITEM_ID)
  121. );
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement