-----------------------------------\012-- Version 003\012-----------------------------------\012\012-- Create domain types section -------------------------------------------------\012\012CREATE DOMAIN TECH_ID AS BIGINT;\012\012CREATE DOMAIN CODE AS VARCHAR(40);\012\012CREATE DOMAIN FILE_NAME as VARCHAR(255);\012\012CREATE DOMAIN FILE_PATH as VARCHAR(1000);\012\012CREATE DOMAIN SHORT_LABEL as VARCHAR(20); \012\012CREATE DOMAIN LONG_LABEL as VARCHAR(100); \012\012CREATE DOMAIN CHAR as VARCHAR(1);\012\012CREATE DOMAIN ms_quantification_software_kind AS character varying(40)\012\011CONSTRAINT ms_quantification_software_kind_check CHECK (((VALUE)::text = \012\011\011ANY ((ARRAY['msSoft'::character varying, 'Xcalibur'::character varying, \012\011\011\011\011\011\011\011\011'Analyst'::character varying, 'MassHunter'::character varying])::text[])));\012\012\012-- Create tables section -------------------------------------------------\012\012-- Table EXPERIMENTS\012\012CREATE TABLE EXPERIMENTS (\012  ID BIGSERIAL NOT NULL,\012  PERM_ID CODE NOT NULL,\012  NAME LONG_LABEL NOT NULL,\012  PRIMARY KEY (ID),\012  UNIQUE (PERM_ID)\012);\012\012-- Table SAMPLES\012\012CREATE TABLE SAMPLES (\012  ID BIGSERIAL NOT NULL,\012  PERM_ID CODE NOT NULL,\012  NAME LONG_LABEL NOT NULL,\012  EXPE_ID TECH_ID NOT NULL,\012  PRIMARY KEY (ID),\012  UNIQUE (PERM_ID),\012  CONSTRAINT FK_SAMPLE_1 FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS (ID) ON DELETE CASCADE ON UPDATE CASCADE\012);\012\012-- Table DATA_SETS\012\012CREATE TABLE DATA_SETS (\012  ID BIGSERIAL NOT NULL,\012  PERM_ID CODE NOT NULL,  \012  EXPE_ID TECH_ID NOT NULL,\012  SAMP_ID TECH_ID,\012  PRIMARY KEY (ID),\012  UNIQUE (PERM_ID),\012  CONSTRAINT FK_DATA_SET_1 FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS (ID) ON DELETE CASCADE ON UPDATE CASCADE,\012  CONSTRAINT FK_DATA_SET_2 FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES (ID) ON DELETE CASCADE ON UPDATE CASCADE\012);\012\012-- Table EIC_MS_RUNS\012\012CREATE TABLE EIC_MS_RUNS (\012  ID BIGSERIAL NOT NULL,\012  EXPE_ID TECH_ID NOT NULL,\012  SAMP_ID TECH_ID,\012  DS_ID TECH_ID NOT NULL,\012  RAW_DATA_FILE_NAME FILE_NAME NOT NULL,\012  RAW_DATA_FILE_PATH FILE_PATH DEFAULT NULL,\012  ACQUISITION_DATE TIMESTAMP DEFAULT NULL,\012  INSTRUMENT_TYPE SHORT_LABEL DEFAULT NULL,\012  INSTRUMENT_MANUFACTURER LONG_LABEL DEFAULT NULL,\012  INSTRUMENT_MODEL LONG_LABEL DEFAULT NULL,\012  METHOD_IONISATION SHORT_LABEL DEFAULT NULL,\012  METHOD_SEPARATION LONG_LABEL DEFAULT NULL,\012  OPERATOR SHORT_LABEL DEFAULT NULL,\012  MS_RUN_ID BIGINT DEFAULT NULL,\012  SET_ID TECH_ID DEFAULT NULL,\012  START_TIME REAL NOT NULL,\012  END_TIME REAL NOT NULL,\012  PRIMARY KEY (ID),\012  CONSTRAINT FK_EIC_MS_RUN_1 FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS (ID) ON DELETE CASCADE ON UPDATE CASCADE,\012  CONSTRAINT FK_EIC_MS_RUN_2 FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES (ID) ON DELETE CASCADE ON UPDATE CASCADE,\012  CONSTRAINT FK_EIC_MS_RUN_3 FOREIGN KEY (DS_ID) REFERENCES DATA_SETS (ID) ON DELETE CASCADE ON UPDATE CASCADE\012);\012\012CREATE INDEX EIC_MS_RUNS_FK_DS_ID_IDX ON EIC_MS_RUNS(DS_ID);\012CREATE INDEX EIC_MS_RUNS_FK_SAMP_ID_IDX ON EIC_MS_RUNS(SAMP_ID);\012CREATE INDEX EIC_MS_RUNS_FK_EXPE_ID_IDX ON EIC_MS_RUNS(EXPE_ID);\012\012-- Table EIC_CHROMATOGRAMS\012\012CREATE TABLE EIC_CHROMATOGRAMS (\012  ID BIGSERIAL NOT NULL,\012  EIC_MS_RUN_ID TECH_ID NOT NULL,\012  Q1_MZ REAL NOT NULL,\012  Q3_LOW_MZ REAL NOT NULL,\012  Q3_HIGH_MZ REAL NOT NULL,\012  LABEL LONG_LABEL DEFAULT NULL,\012  POLARITY CHAR DEFAULT NULL,\012  RUN_TIMES TEXT NOT NULL,\012  INTENSITIES TEXT NOT NULL,\012  PRIMARY KEY (ID),\012  CONSTRAINT FK_EIC_CHROMATOGRAM_1 FOREIGN KEY (EIC_MS_RUN_ID) REFERENCES EIC_MS_RUNS (ID) ON DELETE CASCADE ON UPDATE CASCADE\012);\012\012CREATE INDEX EIC_CHROMATOGRAM_FK_IDX ON EIC_CHROMATOGRAMS(EIC_MS_RUN_ID);\012\012-- Table FIA_MS_RUNS\012\012CREATE TABLE FIA_MS_RUNS (\012  ID BIGSERIAL NOT NULL,\012  EXPE_ID TECH_ID NOT NULL,\012  SAMP_ID TECH_ID,\012  DS_ID TECH_ID NOT NULL,\012  RAW_DATA_FILE_NAME FILE_NAME NOT NULL,\012  RAW_DATA_FILE_PATH FILE_PATH DEFAULT NULL,\012  ACQUISITION_DATE TIMESTAMP DEFAULT NULL,\012  INSTRUMENT_TYPE SHORT_LABEL DEFAULT NULL,\012  INSTRUMENT_MANUFACTURER LONG_LABEL DEFAULT NULL,\012  INSTRUMENT_MODEL LONG_LABEL DEFAULT NULL,\012  METHOD_IONISATION SHORT_LABEL DEFAULT NULL,\012  METHOD_SEPARATION LONG_LABEL DEFAULT NULL,\012  POLARITY VARCHAR(1) DEFAULT NULL,\012  LOW_MZ REAL NOT NULL,\012  HIGH_MZ REAL NOT NULL,\012  INTERNAL_STANDARD REAL NOT NULL,\012  -- Is this a good name?\012  OD REAL NOT NULL,\012  OPERATOR SHORT_LABEL DEFAULT NULL,\012  PRIMARY KEY (ID),\012  CONSTRAINT FK_FIA_MS_RUN_1 FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS (ID) ON DELETE CASCADE ON UPDATE CASCADE,\012  CONSTRAINT FK_EIA_MS_RUN_2 FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES (ID) ON DELETE CASCADE ON UPDATE CASCADE,\012  CONSTRAINT FK_EIA_MS_RUN_3 FOREIGN KEY (DS_ID) REFERENCES DATA_SETS (ID) ON DELETE CASCADE ON UPDATE CASCADE\012);\012\012CREATE INDEX FIA_MS_RUNS_FK_DS_ID_IDX ON FIA_MS_RUNS(DS_ID);\012CREATE INDEX FIA_MS_RUNS_FK_SAMP_ID_IDX ON FIA_MS_RUNS(SAMP_ID);\012CREATE INDEX FIA_MS_RUNS_FK_EXPE_ID_IDX ON FIA_MS_RUNS(EXPE_ID);\012\012-- Table FIA_PROFILES\012\012CREATE TABLE FIA_PROFILES (\012  ID BIGSERIAL NOT NULL,\012  FIA_MS_RUN_ID TECH_ID NOT NULL,\012  LOW_MZ REAL NOT NULL,\012  HIGH_MZ REAL NOT NULL,\012  MZ TEXT NOT NULL,\012  INTENSITIES TEXT NOT NULL,\012  PRIMARY KEY (ID),\012  CONSTRAINT FK_FIA_PROFILE_1 FOREIGN KEY (FIA_MS_RUN_ID) REFERENCES FIA_MS_RUNS (ID) ON DELETE CASCADE ON UPDATE CASCADE\012);\012\012CREATE INDEX FIA_PROFILE_I_ID on FIA_PROFILES(FIA_MS_RUN_ID);\012CREATE INDEX FIA_PROFILE_I_ID_MZ on FIA_PROFILES(FIA_MS_RUN_ID, LOW_MZ, HIGH_MZ);\012\012-- Table FIA_CENTROIDS\012\012CREATE TABLE FIA_CENTROIDS (\012  ID BIGSERIAL NOT NULL,\012  FIA_MS_RUN_ID TECH_ID NOT NULL,\012  MZ REAL NOT NULL,\012  INTENSITY REAL NOT NULL,\012  CORRELATION REAL NOT NULL,\012  PRIMARY KEY (ID),\012  CONSTRAINT FK_FIA_CENTROID_1 FOREIGN KEY (FIA_MS_RUN_ID) REFERENCES FIA_MS_RUNS (ID) ON DELETE CASCADE ON UPDATE CASCADE\012);\012\012CREATE INDEX FIA_CENTROID_I_ID ON FIA_CENTROIDS(FIA_MS_RUN_ID);\012CREATE INDEX FIA_CENTROID_I_ID_MZ ON FIA_CENTROIDS(FIA_MS_RUN_ID, MZ);\012 \012-- Table MS_QUANTIFICATIONS\012\012CREATE TABLE MS_QUANTIFICATIONS (\012  ID BIGSERIAL NOT NULL,\012  DS_ID TECH_ID NOT NULL,\012  EXPE_ID TECH_ID NOT NULL,\012  SOURCE MS_QUANTIFICATION_SOFTWARE_KIND NOT NULL,\012  VALID BOOLEAN NOT NULL,\012  COMMENT TEXT,\012  REGISTRATOR SHORT_LABEL DEFAULT NULL,\012  REGISTRATION_DATE TIMESTAMP DEFAULT NULL,\012  PRIMARY KEY (ID),\012  CONSTRAINT MS_QUANTIFICATION_1 FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS (ID) ON DELETE CASCADE ON UPDATE CASCADE,\012  CONSTRAINT MS_QUANTIFICATION_3 FOREIGN KEY (DS_ID) REFERENCES DATA_SETS (ID) ON DELETE CASCADE ON UPDATE CASCADE\012);\012\012CREATE INDEX MS_QUANTIFICATION_FK_DS_ID_IDX ON MS_QUANTIFICATIONS(DS_ID);\012CREATE INDEX MS_QUANTIFICATION_FK_EXPE_ID_IDX ON MS_QUANTIFICATIONS(EXPE_ID);\012\012-- Table MS_QUANT_CONCENTRATIONS\012\012CREATE TABLE MS_QUANT_CONCENTRATIONS (\012  ID BIGSERIAL NOT NULL,\012  MS_QUANTIFICATION_ID TECH_ID NOT NULL,\012  PARENT_DS_PERM_ID CODE NOT NULL,\012  AMOUNT REAL NOT NULL,\012  UNIT SHORT_LABEL NOT NULL,\012  VALID BOOLEAN NOT NULL,\012  COMMENT TEXT,\012  RETENTION_TIME REAL NOT NULL,\012  Q1 REAL NOT NULL,\012  Q3 REAL NOT NULL,\012  INTERNAL_STANDARD VARCHAR(30) NOT NULL,\012  PRIMARY KEY (ID),\012  CONSTRAINT FK_QUANT_CONCENTRATIONS_1 FOREIGN KEY (MS_QUANTIFICATION_ID) REFERENCES MS_QUANTIFICATIONS (ID) ON DELETE CASCADE ON UPDATE CASCADE\012);\012\012-- Table MS_QUANT_COMPOUND\012\012CREATE TABLE MS_QUANT_COMPOUNDS (\012\011ID BIGSERIAL NOT NULL,\012  MS_QUANT_CONCENTRATION_ID TECH_ID NOT NULL,\012  COMPOUND_ID TECH_ID NOT NULL,\012  CONSTRAINT FK_QUANT_COMPOUND_1 FOREIGN KEY (MS_QUANT_CONCENTRATION_ID) REFERENCES MS_QUANT_CONCENTRATIONS (ID) ON DELETE CASCADE ON UPDATE CASCADE\012);\012\012--------------- mzXML file content\012CREATE TABLE MZ_MS_RUNS (\012  ID BIGSERIAL NOT NULL,\012  EXPE_ID TECH_ID NOT NULL,\012  SAMP_ID TECH_ID,\012  DS_ID TECH_ID NOT NULL,\012\012  INSTRUMENT_TYPE SHORT_LABEL DEFAULT NULL,\012  INSTRUMENT_MANUFACTURER LONG_LABEL DEFAULT NULL,\012  INSTRUMENT_MODEL LONG_LABEL DEFAULT NULL,\012  METHOD_IONISATION SHORT_LABEL DEFAULT NULL,\012\012  PRIMARY KEY (ID), \012  CONSTRAINT FK_MZ_MS_RUN_1 FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS (ID) ON DELETE CASCADE ON UPDATE CASCADE,\012  CONSTRAINT FK_MZ_MS_RUN_2 FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES (ID) ON DELETE CASCADE ON UPDATE CASCADE,\012  CONSTRAINT FK_MZ_MS_RUN_3 FOREIGN KEY (DS_ID) REFERENCES DATA_SETS (ID) ON DELETE CASCADE ON UPDATE CASCADE\012);\012\012CREATE INDEX MZ_MS_RUNS_FK_DS_ID_IDX ON MZ_MS_RUNS(DS_ID);\012CREATE INDEX MZ_MS_RUNS_FK_SAMP_ID_IDX ON MZ_MS_RUNS(SAMP_ID);\012CREATE INDEX MZ_MS_RUNS_FK_EXPE_ID_IDX ON MZ_MS_RUNS(EXPE_ID);\012\012\012CREATE TABLE MZ_SCANS (\012  ID BIGSERIAL NOT NULL,\012\011MZ_MS_RUN_ID TECH_ID NOT NULL,\012\012\011NUMBER INTEGER NOT NULL CHECK (NUMBER > 0),\012\011LEVEL INTEGER NOT NULL CHECK (LEVEL > 0),\012\011PEAKS_COUNT INTEGER NOT NULL CHECK (PEAKS_COUNT >= 0),\012\011POLARITY CHAR (1) CHECK (POLARITY = '+' OR POLARITY = '-'),\012\011SCAN_TYPE VARCHAR(10),\012\011COLLISION_ENERGY REAL, \012\011LOW_MZ REAL,\012\011HIGH_MZ REAL,\012\011RETENTION_TIME REAL,\012\012\011PRECURSOR1_MZ REAL,\012\011PRECURSOR1_INTENSITY REAL,\012\011PRECURSOR1_CHARGE INTEGER CHECK (PRECURSOR1_CHARGE > 0),\012\012\011PRECURSOR2_MZ REAL,\012\011PRECURSOR2_INTENSITY REAL,\012\011PRECURSOR2_CHARGE INTEGER CHECK (PRECURSOR2_CHARGE > 0),\012\011\012\011CHECK (\012\011\011\011(PRECURSOR1_MZ IS NULL AND PRECURSOR1_INTENSITY IS NULL AND PRECURSOR1_CHARGE IS NULL) OR \012\011\011\011(PRECURSOR1_MZ IS NOT NULL AND PRECURSOR1_INTENSITY IS NOT NULL) \012\011),\012\011CHECK (\012\011\011\011(PRECURSOR2_MZ IS NULL AND PRECURSOR2_INTENSITY IS NULL AND PRECURSOR2_CHARGE IS NULL) OR \012\011\011\011(PRECURSOR2_MZ IS NOT NULL AND PRECURSOR2_INTENSITY IS NOT NULL) \012\011),\012\011PRIMARY KEY (ID),\012\011CONSTRAINT FK_MZ_MS_RUN FOREIGN KEY (MZ_MS_RUN_ID) REFERENCES MZ_MS_RUNS (ID) ON DELETE CASCADE ON UPDATE CASCADE\012);\012\012CREATE TABLE MZ_PEAKS (\012 MZ_SCAN_ID TECH_ID NOT NULL,\012 MZ REAL NOT NULL,\012 INTENSITY REAL NOT NULL,\012 CONSTRAINT FK_MZ_SCAN FOREIGN KEY (MZ_SCAN_ID) REFERENCES MZ_SCANS (ID) ON DELETE CASCADE ON UPDATE CASCADE\012);\012\012--------------- END mzXML\012\012-- Table EVENTS\012\012CREATE TABLE EVENTS (\012  LAST_SEEN_DELETION_EVENT_ID TECH_ID NOT NULL\012);\012\012GRANT SELECT ON TABLE EXPERIMENTS TO GROUP metabol_readonly;\012GRANT SELECT ON TABLE SAMPLES TO GROUP metabol_readonly;\012GRANT SELECT ON TABLE DATA_SETS TO GROUP metabol_readonly;\012GRANT SELECT ON TABLE EIC_MS_RUNS TO GROUP metabol_readonly;\012GRANT SELECT ON TABLE EIC_CHROMATOGRAMS TO GROUP metabol_readonly;\012GRANT SELECT ON TABLE FIA_MS_RUNS TO GROUP metabol_readonly;\012GRANT SELECT ON TABLE FIA_PROFILES TO GROUP metabol_readonly;\012GRANT SELECT ON TABLE FIA_CENTROIDS TO GROUP metabol_readonly;\012GRANT SELECT ON TABLE MS_QUANTIFICATIONS TO GROUP metabol_readonly;\012GRANT SELECT ON TABLE MS_QUANT_CONCENTRATIONS TO GROUP metabol_readonly;\012GRANT SELECT ON TABLE MS_QUANT_COMPOUNDS TO GROUP metabol_readonly;\012GRANT SELECT ON TABLE EVENTS TO GROUP metabol_readonly;\012GRANT SELECT ON SEQUENCE EXPERIMENTS_ID_SEQ TO GROUP metabol_readonly;\012GRANT SELECT ON SEQUENCE SAMPLES_ID_SEQ TO GROUP metabol_readonly;\012GRANT SELECT ON SEQUENCE DATA_SETS_ID_SEQ TO GROUP metabol_readonly;\012GRANT SELECT ON SEQUENCE EIC_MS_RUNS_ID_SEQ TO GROUP metabol_readonly;\012GRANT SELECT ON SEQUENCE EIC_CHROMATOGRAMS_ID_SEQ TO GROUP metabol_readonly;\012GRANT SELECT ON SEQUENCE FIA_MS_RUNS_ID_SEQ TO GROUP metabol_readonly;\012GRANT SELECT ON SEQUENCE FIA_PROFILES_ID_SEQ TO GROUP metabol_readonly;\012GRANT SELECT ON SEQUENCE FIA_CENTROIDS_ID_SEQ TO GROUP metabol_readonly;\012GRANT SELECT ON SEQUENCE MS_QUANTIFICATIONS_ID_SEQ TO GROUP metabol_readonly;\012GRANT SELECT ON SEQUENCE MS_QUANT_CONCENTRATIONS_ID_SEQ TO GROUP metabol_readonly;\012GRANT SELECT ON TABLE MZ_MS_RUNS TO GROUP metabol_readonly;\012GRANT SELECT ON TABLE MZ_SCANS TO GROUP metabol_readonly;\012GRANT SELECT ON TABLE MZ_PEAKS TO GROUP metabol_readonly;\012GRANT SELECT ON SEQUENCE MZ_MS_RUNS_ID_SEQ TO GROUP metabol_readonly;\012GRANT SELECT ON SEQUENCE MZ_SCANS_ID_SEQ TO GROUP metabol_readonly;\012\012GRANT ALL PRIVILEGES ON TABLE EXPERIMENTS TO GROUP metabol_readwrite;\012GRANT ALL PRIVILEGES ON TABLE SAMPLES TO GROUP metabol_readwrite;\012GRANT ALL PRIVILEGES ON TABLE DATA_SETS TO GROUP metabol_readwrite;\012GRANT ALL PRIVILEGES ON TABLE EIC_MS_RUNS TO GROUP metabol_readwrite;\012GRANT ALL PRIVILEGES ON TABLE EIC_CHROMATOGRAMS TO GROUP metabol_readwrite;\012GRANT ALL PRIVILEGES ON TABLE FIA_MS_RUNS TO GROUP metabol_readwrite;\012GRANT ALL PRIVILEGES ON TABLE FIA_PROFILES TO GROUP metabol_readwrite;\012GRANT ALL PRIVILEGES ON TABLE FIA_CENTROIDS TO GROUP metabol_readwrite;\012GRANT ALL PRIVILEGES ON TABLE MS_QUANTIFICATIONS TO GROUP metabol_readwrite;\012GRANT ALL PRIVILEGES ON TABLE MS_QUANT_CONCENTRATIONS TO GROUP metabol_readwrite;\012GRANT ALL PRIVILEGES ON TABLE MS_QUANT_COMPOUNDS TO GROUP metabol_readwrite;\012GRANT ALL PRIVILEGES ON TABLE EVENTS TO GROUP metabol_readwrite;\012GRANT ALL PRIVILEGES ON SEQUENCE EXPERIMENTS_ID_SEQ TO GROUP metabol_readwrite;\012GRANT ALL PRIVILEGES ON SEQUENCE SAMPLES_ID_SEQ TO GROUP metabol_readwrite;\012GRANT ALL PRIVILEGES ON SEQUENCE DATA_SETS_ID_SEQ TO GROUP metabol_readwrite;\012GRANT ALL PRIVILEGES ON SEQUENCE EIC_MS_RUNS_ID_SEQ TO GROUP metabol_readwrite;\012GRANT ALL PRIVILEGES ON SEQUENCE EIC_CHROMATOGRAMS_ID_SEQ TO GROUP metabol_readwrite;\012GRANT ALL PRIVILEGES ON SEQUENCE FIA_MS_RUNS_ID_SEQ TO GROUP metabol_readwrite;\012GRANT ALL PRIVILEGES ON SEQUENCE FIA_PROFILES_ID_SEQ TO GROUP metabol_readwrite;\012GRANT ALL PRIVILEGES ON SEQUENCE FIA_CENTROIDS_ID_SEQ TO GROUP metabol_readwrite;\012GRANT ALL PRIVILEGES ON SEQUENCE MS_QUANTIFICATIONS_ID_SEQ TO GROUP metabol_readwrite;\012GRANT ALL PRIVILEGES ON SEQUENCE MS_QUANT_CONCENTRATIONS_ID_SEQ TO GROUP metabol_readwrite;\012GRANT ALL PRIVILEGES ON TABLE MZ_MS_RUNS TO GROUP metabol_readwrite;\012GRANT ALL PRIVILEGES ON TABLE MZ_SCANS TO GROUP metabol_readwrite;\012GRANT ALL PRIVILEGES ON TABLE MZ_PEAKS TO GROUP metabol_readwrite;\012GRANT ALL PRIVILEGES ON SEQUENCE MZ_MS_RUNS_ID_SEQ TO GROUP metabol_readwrite;\012GRANT ALL PRIVILEGES ON SEQUENCE MZ_SCANS_ID_SEQ TO GROUP metabol_readwrite;\012