sascha@979: BEGIN; sascha@979: sascha@985: CREATE SEQUENCE USERS_ID_SEQ; sascha@985: sascha@979: CREATE TABLE users ( sascha@985: id INT PRIMARY KEY NOT NULL, sascha@985: gid UUID NOT NULL UNIQUE sascha@979: ); sascha@979: sascha@985: CREATE SEQUENCE COLLECTIONS_ID_SEQ; sascha@985: sascha@979: CREATE TABLE collections ( sascha@985: id INT PRIMARY KEY NOT NULL, sascha@985: gid UUID NOT NULL UNIQUE, sascha@985: user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE, sascha@985: name VARCHAR(256) NOT NULL sascha@979: ); sascha@979: sascha@985: CREATE SEQUENCE ARTIFACTS_ID_SEQ; sascha@985: sascha@979: CREATE TABLE artifacts ( sascha@985: id INT PRIMARY KEY NOT NULL, sascha@985: gid UUID NOT NULL UNIQUE, sascha@985: state VARCHAR(256) NOT NULL sascha@979: ); sascha@979: sascha@988: CREATE SEQUENCE COLLECTION_ITEMS_ID_SEQ; sascha@985: sascha@979: CREATE TABLE collection_items ( sascha@985: id INT PRIMARY KEY NOT NULL, sascha@985: collection_id INT NOT NULL REFERENCES collections(id) ON DELETE CASCADE, sascha@985: artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE sascha@979: ); sascha@979: sascha@985: CREATE SEQUENCE ARTIFACT_DATA_ID_SEQ; sascha@985: sascha@979: CREATE TABLE artifact_data ( sascha@985: id INT PRIMARY KEY NOT NULL, sascha@985: artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE, sascha@990: kind VARCHAR(256) NOT NULL, sascha@985: k VARCHAR(256) NOT NULL, sascha@985: v VARCHAR(256), -- Maybe too short sascha@979: UNIQUE (artifact_id, k) sascha@979: ); sascha@979: sascha@985: CREATE SEQUENCE OUTS_ID_SEQ; sascha@985: sascha@981: CREATE TABLE outs ( sascha@985: id INT PRIMARY KEY NOT NULL, sascha@985: artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE, sascha@985: name VARCHAR(256) NOT NULL, sascha@981: description VARCHAR(256), sascha@981: out_type VARCHAR(256) sascha@981: ); sascha@981: sascha@985: CREATE SEQUENCE FACETS_ID_SEQ; sascha@985: sascha@979: CREATE TABLE facets ( sascha@985: id INT PRIMARY KEY NOT NULL, sascha@985: artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE, sascha@985: out_id INT NOT NULL REFERENCES outs(id) ON DELETE CASCADE, sascha@985: name VARCHAR(256) NOT NULL, sascha@979: num INT NOT NULL, sascha@980: state VARCHAR(256) NOT NULL, sascha@979: description VARCHAR(256), sascha@985: UNIQUE (artifact_id, out_id, num, name) sascha@979: ); sascha@979: sascha@985: -- DROP SEQUENCE USERS_ID_SEQ; sascha@985: -- DROP SEQUENCE COLLECTIONS_ID_SEQ; sascha@985: -- DROP SEQUENCE ARTIFACTS_ID_SEQ; sascha@988: -- DROP SEQUENCE COLLECTION_ITEMS_ID_SEQ; sascha@985: -- DROP SEQUENCE ARTIFACT_DATA_ID_SEQ; sascha@985: -- DROP SEQUENCE OUTS_ID_SEQ; sascha@985: -- DROP SEQUENCE FACETS_ID_SEQ; sascha@980: -- DROP TABLE facets; sascha@981: -- DROP TABLE outs; sascha@980: -- DROP TABLE artifact_data; sascha@980: -- DROP TABLE collection_items; sascha@980: -- DROP TABLE collections; sascha@980: -- DROP TABLE artifacts; sascha@980: -- DROP TABLE users; sascha@980: sascha@979: COMMIT;