schema-001.sql

/* ---------------------------------------------------------------------- */
/* Domains                                                                */
/* ---------------------------------------------------------------------- */

CREATE DOMAIN TECH_ID AS BIGINT;

CREATE DOMAIN CODE AS VARCHAR(40);

CREATE DOMAIN NAME AS VARCHAR(80);

CREATE DOMAIN DESCRIPTION AS VARCHAR(200);

CREATE DOMAIN FILE_PATH as VARCHAR(1000);

CREATE DOMAIN COLOR_COMPONENT AS VARCHAR(40) CHECK (VALUE IN ('RED', 'GREEN', 'BLUE'));

/* ---------------------------------------------------------------------- */
/* Tables                                                                 */
/* ---------------------------------------------------------------------- */

CREATE TABLE EXPERIMENTS (
  ID BIGSERIAL NOT NULL,
  PERM_ID CODE NOT NULL,

  PRIMARY KEY (ID),
  UNIQUE (PERM_ID)
);

CREATE TABLE CONTAINERS (
  ID BIGSERIAL NOT NULL,
  PERM_ID CODE NOT NULL,

  SPOTS_WIDTH INTEGER,
  SPOTS_HEIGHT INTEGER,
  
  EXPE_ID TECH_ID NOT NULL,

  PRIMARY KEY (ID),
  UNIQUE (PERM_ID),
  CONSTRAINT FK_SAMPLE_1 FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE SPOTS (
  ID BIGSERIAL NOT NULL,
  PERM_ID CODE NOT NULL,
	
	-- position in the container, one-based
  X INTEGER, 
  Y INTEGER, 
  CONT_ID TECH_ID NOT NULL,
  
  PRIMARY KEY (ID),
  UNIQUE (PERM_ID),
  CONSTRAINT FK_SPOT_1 FOREIGN KEY (CONT_ID) REFERENCES CONTAINERS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);


CREATE TABLE DATA_SETS (
  ID BIGSERIAL NOT NULL,
  PERM_ID CODE NOT NULL,  
  
	FIELDS_WIDTH INTEGER,
	FIELDS_HEIGHT INTEGER,	
  
  CONT_ID TECH_ID,

  PRIMARY KEY (ID),
  UNIQUE (PERM_ID),
  CONSTRAINT FK_DATA_SET_1 FOREIGN KEY (CONT_ID) REFERENCES CONTAINERS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE CHANNELS (
    ID BIGSERIAL  NOT NULL,
    
    NAME NAME NOT NULL,
    DESCRIPTION DESCRIPTION,
    WAVELENGTH INTEGER,

    DS_ID TECH_ID	NOT NULL,
    
    PRIMARY KEY (ID),
    CONSTRAINT FK_CHANNELS_1 FOREIGN KEY (DS_ID) REFERENCES DATA_SETS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE CHANNEL_STACKS (
    ID BIGSERIAL  NOT NULL,
		
		-- x and y are kind of a two dimensional sequence number, some use case may only use x and leave y alone
		X INTEGER,
		Y INTEGER,
		-- we use the fixed dimension meters here
		Z_in_M REAL,
		-- we use the fixed dimension seconds here
		T_in_SEC REAL,

    DS_ID TECH_ID	NOT NULL,
		SPOT_ID TECH_ID	NOT NULL,

    PRIMARY KEY (ID),
    CONSTRAINT FK_CHANNEL_STACKS_1 FOREIGN KEY (SPOT_ID) REFERENCES SPOTS (ID) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FK_CHANNEL_STACKS_2 FOREIGN KEY (DS_ID) REFERENCES DATA_SETS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IMAGES (
    ID BIGSERIAL  NOT NULL,
   
    PATH	FILE_PATH NOT NULL,
    PAGE	INTEGER,
    COLOR	COLOR_COMPONENT,
    
    CHANNEL_STACK_ID  TECH_ID NOT NULL,
    CHANNEL_ID  TECH_ID NOT NULL,

    PRIMARY KEY (ID),
    CONSTRAINT FK_IMAGES_1 FOREIGN KEY (CHANNEL_STACK_ID) REFERENCES CHANNEL_STACKS (ID) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FK_IMAGES_2 FOREIGN KEY (CHANNEL_ID) REFERENCES CHANNELS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);

/* ---------------------------------------------------------------------- */
/* FEATURE VECTORS                                                        */
/* ---------------------------------------------------------------------- */ 

CREATE TABLE FEATURE_DEFS (
    ID BIGSERIAL  NOT NULL,
    
    NAME NAME NOT NULL,
    DESCRIPTION DESCRIPTION,
    
    DS_ID  TECH_ID NOT NULL,
    
    PRIMARY KEY (ID),
    CONSTRAINT FK_FEATURE_DEFS_1 FOREIGN KEY (DS_ID) REFERENCES DATA_SETS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE FEATURE_VALUES (
    ID BIGSERIAL  NOT NULL,
		
		Z	REAL,
		T	REAL,
		VALUES BYTEA,
		
		FD_ID  TECH_ID NOT NULL,
		DS_ID  TECH_ID NOT NULL,
		
		PRIMARY KEY (ID),
		CONSTRAINT FK_FEATURE_VALUES_1 FOREIGN KEY (FD_ID) REFERENCES FEATURE_DEFS (ID) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FK_FEATURE_VALUES_2 FOREIGN KEY (DS_ID) REFERENCES DATA_SETS (ID) ON DELETE CASCADE ON UPDATE CASCADE
		
);