Homec4science
Diffusion SecGene-i2b2 (master) Browse

SecGene/sql/i2b2/oracle/table.sql
2d922360738bmaster

Last Change
Raw File

table.sql
View Options

CREATE TABLE GEN_PARAMETERS (
POLY_DEGREE INT NOT NULL,
NB_MODULI INT NOT NULL,
PUBLIC_KEY BLOB NOT NULL,
EVALUATION_KEY BLOB NOT NULL,
PLAINTEXT_MODULUS INT NOT NULL
);
CREATE TABLE GEN_VARIANTS (
CIPHER_ID NUMBER(19) NOT NULL,
DEGREE INT NOT NULL,
CHROMOSOME VARCHAR(3) NOT NULL,
POSITION NUMBER(19) NOT NULL,
REFERENCE VARCHAR(500),
ALTERNATE VARCHAR(500),
GENE_NAME VARCHAR(50),
RS_NUMBER VARCHAR(50),
PRIMARY KEY ( CIPHER_ID, DEGREE )
);
CREATE INDEX GEN_INDEX_CHR_POS ON GEN_VARIANTS(CHROMOSOME, POSITION);
CREATE TABLE GEN_PATIENT_MAPPING (
PID_I2B2 INT,
PID_GEN INT NOT NULL,
PATIENT_GROUP INT,
PRIMARY KEY ( PID_GEN )
);
CREATE TABLE GEN_ENCRYPTED_GENOTYPES (
CIPHER_ID NUMBER(19) NOT NULL,
PID_GEN INT NOT NULL,
CIPHER_GT1 BLOB NOT NULL,
CIPHER_GT2 BLOB NOT NULL,
CIPHER_GT3 BLOB,
NO_CALLS BLOB NOT NULL,
PRIMARY KEY ( CIPHER_ID, PID_GEN ),
FOREIGN KEY ( PID_GEN ) REFERENCES GEN_PATIENT_MAPPING ( PID_GEN )
);
CREATE TABLE GEN_PARTIAL_KEY_SET (
PARTIAL_KEY_ID VARCHAR(50) NOT NULL,
PARTIAL_KEY BLOB NOT NULL,
PRIMARY KEY ( PARTIAL_KEY_ID )
);
CREATE TABLE GEN_USER_ROLES (
ACCESS_LEVEL INT NOT NULL,
ROLE_NAME VARCHAR(50) NOT NULL,
EPSILON DOUBLE PRECISION NOT NULL,
PRIMARY KEY ( ACCESS_LEVEL )
);
CREATE TABLE GEN_USER_MAPPING (
USER_ID VARCHAR(50) NOT NULL,
ACCESS_LEVEL INT NOT NULL,
PARTIAL_KEY_ID VARCHAR(50) NOT NULL,
PRIMARY KEY ( USER_ID ),
FOREIGN KEY ( ACCESS_LEVEL ) REFERENCES GEN_USER_ROLES ( ACCESS_LEVEL ),
FOREIGN KEY ( PARTIAL_KEY_ID ) REFERENCES GEN_PARTIAL_KEY_SET ( PARTIAL_KEY_ID )
);
CREATE TABLE GEN_FILTER_PATIENT_SET (
ACCESS_LEVEL INT NOT NULL,
PATIENT_GROUP INT,
FOREIGN KEY ( ACCESS_LEVEL ) REFERENCES GEN_USER_ROLES ( ACCESS_LEVEL )
);
CREATE TABLE GEN_FILTER_RANGE (
ACCESS_LEVEL INT NOT NULL,
CHROMOSOME VARCHAR(3) NOT NULL,
START_POSITION NUMBER(19),
END_POSITION NUMBER(19),
FOREIGN KEY ( ACCESS_LEVEL ) REFERENCES GEN_USER_ROLES ( ACCESS_LEVEL )
);
CREATE TABLE GEN_NOISY_RESULT (
CIPHER_ID NUMBER(19) NOT NULL,
DEGREE INT NOT NULL,
ACCESS_LEVEL INT NOT NULL,
PATIENT_SET_ID INT NOT NULL,
QUERY_TYPE VARCHAR(50),
COUNT NUMBER(19),
RESULT NUMBER(19) NOT NULL,
PRIMARY KEY ( CIPHER_ID, DEGREE, ACCESS_LEVEL, PATIENT_SET_ID, QUERY_TYPE ),
FOREIGN KEY ( ACCESS_LEVEL ) REFERENCES GEN_USER_ROLES ( ACCESS_LEVEL ),
FOREIGN KEY ( CIPHER_ID, DEGREE ) REFERENCES GEN_VARIANTS ( CIPHER_ID, DEGREE )
);
c4science · Help