Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE CATEGORY
- (
- ID INTEGER AUTO_INCREMENT PRIMARY KEY,
- PARENT_ID INTEGER
- CONSTRAINT CATEGORY_CATEGORY_ID_FK
- REFERENCES CATEGORY,
- NAME CLOB
- );
- CREATE TABLE COLLECTION
- (
- ID INTEGER AUTO_INCREMENT PRIMARY KEY,
- NAME VARCHAR(255),
- CATEGORY_ID INTEGER DEFAULT NULL
- CONSTRAINT COLLECTION_CATEGORY_ID_FK
- REFERENCES CATEGORY,
- DESCRIPTION CLOB,
- DATETIME_CREATED TIMESTAMP WITH TIME ZONE(30, 10) DEFAULT NOW() NOT NULL,
- DATETIME_MODIFIED TIMESTAMP WITH TIME ZONE(30, 10) DEFAULT NOW() NOT NULL,
- DELETED BOOLEAN DEFAULT FALSE NOT NULL,
- ACTIVE BOOLEAN DEFAULT TRUE NOT NULL
- );
- CREATE INDEX COLLECTION_ACTIVE_INDEX
- ON COLLECTION (ACTIVE);
- CREATE INDEX COLLECTION_DELETED_INDEX
- ON COLLECTION (DELETED);
- CREATE INDEX COLLECTION_NAME_INDEX
- ON COLLECTION (NAME);
- CREATE TABLE COLLECTION_PARENT_COLLECTION_ASSN
- (
- COLLECTION_ID INTEGER NOT NULL
- CONSTRAINT COLLECTION__PARENT_COLLECTION_COLLECTION_ID_FK
- REFERENCES COLLECTION,
- PARENT_COLLECTION_ID INTEGER NOT NULL
- CONSTRAINT COLLECTION__PARENT_COLLECTION_COLLECTION_ID_FK_2
- REFERENCES COLLECTION,
- EXCLUSIVE BOOLEAN DEFAULT TRUE NOT NULL,
- CONSTRAINT COLLECTION__PARENT_COLLECTION_COLLECTION_ID_PARENT_COLLECTION_ID_PK
- PRIMARY KEY (COLLECTION_ID, PARENT_COLLECTION_ID)
- );
- CREATE TABLE ITEM
- (
- ID INTEGER AUTO_INCREMENT PRIMARY KEY,
- NAME VARCHAR(255) NOT NULL,
- CATEGORY_ID INTEGER DEFAULT NULL
- CONSTRAINT ITEM_CATEGORY_ID_FK
- REFERENCES CATEGORY,
- VERSION VARCHAR(255),
- DATA CLOB,
- DATETIME_CREATED TIMESTAMP WITH TIME ZONE(30, 10) DEFAULT NOW() NOT NULL,
- DATETIME_MODIFIED TIMESTAMP WITH TIME ZONE(30, 10) DEFAULT NOW() NOT NULL,
- DELETED BOOLEAN DEFAULT FALSE NOT NULL,
- ACTIVE BOOLEAN DEFAULT TRUE NOT NULL
- );
- CREATE TABLE COLLECTION_ITEM_ASSN
- (
- COLLECTION_ID INTEGER NOT NULL
- CONSTRAINT COLLECTION__ITEM_COLLECTION_ID_FK
- REFERENCES COLLECTION,
- ITEM_ID INTEGER NOT NULL
- CONSTRAINT COLLECTION__ITEM_ITEM_ID_FK
- REFERENCES ITEM,
- QUANTITY INTEGER DEFAULT 1 NOT NULL,
- CONSTRAINT COLLECTION__ITEM_COLLECTION_ID_ITEM_ID_PK
- PRIMARY KEY (COLLECTION_ID, ITEM_ID)
- );
- CREATE INDEX ITEM_ACTIVE_INDEX
- ON ITEM (ACTIVE);
- CREATE INDEX ITEM_DELETED_INDEX
- ON ITEM (DELETED);
- CREATE INDEX ITEM_NAME_INDEX
- ON ITEM (NAME);
- CREATE INDEX ITEM_NAME_VERSION_INDEX
- ON ITEM (NAME, VERSION);
- CREATE INDEX ITEM_VERSION_INDEX
- ON ITEM (VERSION);
- CREATE TABLE TAG
- (
- ID INTEGER AUTO_INCREMENT PRIMARY KEY,
- NAME CLOB NOT NULL,
- CATEGORY_ID INTEGER DEFAULT NULL
- CONSTRAINT TAG_CATEGORY_ID_FK
- REFERENCES CATEGORY,
- DATA CLOB
- );
- CREATE TABLE TAG_COLLECTION_ASSN
- (
- TAG_ID INTEGER NOT NULL
- CONSTRAINT TAG_COLLECTION_ASSN_TAG_ID_FK
- REFERENCES TAG,
- COLLECTION_ID INTEGER NOT NULL
- CONSTRAINT TAG_COLLECTION_ASSN_COLLECTION_ID_FK
- REFERENCES COLLECTION,
- CONSTRAINT TAG_COLLECTION_ASSN_TAG_ID_COLLECTION_ID_PK
- PRIMARY KEY (TAG_ID, COLLECTION_ID)
- );
- CREATE TABLE TAG_ITEM_ASSN
- (
- TAG_ID INTEGER NOT NULL
- CONSTRAINT TAG_ITEM_ASSN_TAG_ID_FK
- REFERENCES TAG,
- ITEM_ID INTEGER NOT NULL
- CONSTRAINT TAG_ITEM_ASSN_ITEM_ID_FK
- REFERENCES ITEM,
- CONSTRAINT TAG_ITEM_ASSN_TAG_ID_ITEM_ID_PK
- PRIMARY KEY (TAG_ID, ITEM_ID)
- );
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement