SCHEMA.md

Pronto-specific tables

All tables are to be created in the Oracle database, with the INTERPRO user.

Users

CREATE TABLE PRONTO_USER
(
	USERNAME VARCHAR2(50) NOT NULL
		CONSTRAINT PK_USER_PRONTO
			PRIMARY KEY,
	NAME VARCHAR2(200) NOT NULL
		CONSTRAINT UQ_USER_PRONTO_NAME$NAME
			UNIQUE,
	DB_USER VARCHAR2(50) NOT NULL
		CONSTRAINT UQ_USER_PRONTO_DB_NAME$DB_NAME
			UNIQUE,
	CREATED_ON DATE DEFAULT SYSDATE NOT NULL,
	LAST_ACTIVITY DATE DEFAULT NULL,
	IS_ACTIVE CHAR DEFAULT 'Y' NOT NULL
		CONSTRAINT CK_USER_PRONTO_ACTIVE$ACTIVE
			CHECK (IS_ACTIVE IN ('Y', 'N'))
);

Tasks

CREATE TABLE PRONTO_TASK
(
    ID VARCHAR2(32) NOT NULL
        CONSTRAINT PK_PRONTO_TASK PRIMARY KEY,
	NAME VARCHAR2(200) NOT NULL ,
	USERNAME VARCHAR2(50) NOT NULL,
	STARTED DATE NOT NULL ,
	FINISHED DATE DEFAULT NULL ,
	STATUS CHAR DEFAULT NULL
		CONSTRAINT CK_PRONTO_TASK$STATUS
			CHECK ("STATUS"='Y' OR "STATUS"='N'),
    RESULT BLOB DEFAULT NULL
);

CREATE INDEX I_PRONTO_TASK$NAME 
    ON PRONTO_TASK (NAME);

GRANT INSERT, SELECT, UPDATE, DELETE 
    ON PRONTO_TASK 
    TO INTERPRO_CURATOR, INTERPRO_PRODUCTION;

Sanity checks

CREATE TABLE SANITY_CHECK
(
    CHECK_TYPE VARCHAR2(30) NOT NULL,
    TERM VARCHAR2(100),
    TIMESTAMP DATE DEFAULT SYSDATE NOT NULL,
    USERNAME VARCHAR2(50) DEFAULT USER NOT NULL,
    CONSTRAINT UQ_SANITY_CHECK
        UNIQUE (CHECK_TYPE, TERM)
);

CREATE TABLE SANITY_EXCEPTION
(
    ID NUMBER NOT NULL
        CONSTRAINT PK_SANITY_EXCEPTION
            PRIMARY KEY,
    CHECK_TYPE VARCHAR2(30) NOT NULL,
    TERM VARCHAR2(100),
    ANN_ID VARCHAR2(7)
        CONSTRAINT FK_SANITY_EXCEPTION$ANN_ID
            REFERENCES COMMON_ANNOTATION
                ON DELETE CASCADE,
    ENTRY_AC VARCHAR2(9)
        CONSTRAINT FK_SANITY_EXCEPTION$ENTRY_AC
            REFERENCES ENTRY
                ON DELETE CASCADE,
    ENTRY_AC2 VARCHAR2(9)
        CONSTRAINT FK_SANITY_EXCEPTION$ENTRY_AC2
            REFERENCES ENTRY
                ON DELETE CASCADE,
    TIMESTAMP DATE DEFAULT SYSDATE NOT NULL,
    USERNAME VARCHAR2(50) DEFAULT USER NOT NULL,
    CONSTRAINT UQ_SANITY_EXCEPTION
        UNIQUE (CHECK_TYPE, TERM, ANN_ID, ENTRY_AC, ENTRY_AC2)
);

CREATE TABLE SANITY_RUN
(
    ID VARCHAR2(32) NOT NULL
        CONSTRAINT PK_SANITY_RUN
            PRIMARY KEY,
    NUM_ERRORS NUMBER(5) NOT NULL,
    TIMESTAMP DATE DEFAULT SYSDATE NOT NULL,
    USERNAME VARCHAR2(50) DEFAULT USER NOT NULL
);

CREATE TABLE SANITY_ERROR
(
    RUN_ID VARCHAR2(32) NOT NULL
        CONSTRAINT FK_SANITY_ERROR
            REFERENCES SANITY_RUN
                ON DELETE CASCADE,
    ID NUMBER NOT NULL,
    ANN_ID VARCHAR2(7),
    ENTRY_AC VARCHAR2(9),
    ERRORS CLOB NOT NULL,
    TIMESTAMP DATE DEFAULT NULL,
    USERNAME VARCHAR2(50) DEFAULT NULL,
    CONSTRAINT PK_SANITY_ERROR
        PRIMARY KEY (RUN_ID, ID),
    CONSTRAINT CK_SANITY_ERROR
        CHECK (ANN_ID IS NOT NULL OR ENTRY_AC IS NOT NULL)
);