<commit_before>-- Run this script in the database of your choice, note that
-- it drops any old tables present.
-- It's not been tested very well on anything other than Oracle,
-- but should be portable with minor changes.

-- DROP OLD TABLES (IN ORDER)
DROP TABLE MAPPINGS;
DROP TABLE PROVIDERS;
DROP TABLE GENOME_PROK_COMPLETE;
DROP TABLE GENOME_PROK_INPROGRESS;
DROP TABLE GENOME_PROK;
DROP TABLE GENOME_EUK;
DROP TABLE GENOME_ENV;
DROP TABLE GENOME_PROJ_ACC;
DROP TABLE GENOME_PROJ_REFSEQ;
DROP TABLE PROJECTS_LABS;
DROP TABLE GENOME_PROJECTS;
DROP SEQUENCE MAPPINGS_SEQ;
DROP SEQUENCE PROJ_LABS_SEQ;
DROP SEQUENCE GENOME_PROJ_ACC_SEQ;
DROP SEQUENCE GENOME_PROJ_REFSEQ_SEQ;

-- CREATE THE TABLE LAYOUT
-- Should be somewhat portable, but probably contains some oracle-isms

-- sequences will not work in mysql, unfortunately the code relies on them...
CREATE SEQUENCE GENOME_PROJ_ACC_SEQ;
CREATE SEQUENCE GENOME_PROJ_REFSEQ_SEQ;
CREATE SEQUENCE MAPPING_SEQ;
CREATE SEQUENCE PROJ_LABS_SEQ;

CREATE TABLE GENOME_PROJECTS
  (
    ID NUMERIC(10,0),
    PRIMARY KEY (ID)
  );
CREATE TABLE PROJECTS_LABS
  (
    ID       NUMBER(10,0),
    PROJ_ID  NUMBER(10,0),
    LAB_NAME VARCHAR(255),
    PRIMARY KEY (ID),
    UNIQUE (PROJ_ID, LAB_NAME)
  );
CREATE TABLE GENOME_EUK
  (
    PROJ_ID           NUMERIC(10,0),
    ORGANISM_NAME     VARCHAR(255),
    ORGANISM_GROUP    VARCHAR(50),
    ORGANISM_SUBGROUP VARCHAR(50),
    NCBI_TAXON_ID     NUMERIC(10,0),
    GENOME_SIZE       NUMERIC(14,4),
    NR_CHROMOSOMES    NUMERIC(8,0),
    SEQUENCE_STATUS   VARCHAR(20),
    SEQUENCE_METHOD   VARCHAR(20),
    COVERAGE          VARCHAR(50),
    RELEASE_DATE DATE,
    CREATED_AT DATE,
    UPDATED_AT DATE,
    PRIMARY KEY (PROJ_ID)
  );
CREATE TABLE GENOME_ENV
  (
    PROJ_ID           NUMERIC(10,0),
    PARENT_PROJ_ID    NUMERIC(10,0),
    TITLE             VARCHAR(255),
    METAGENOME_SOURCE VARCHAR(50),
    METAGENOME_TYPE   NUMERIC(4,0),
    RELEASE_DATE DATE,
    CREATED_AT DATE,
    UPDATED_AT DATE,
    PRIMARY KEY (PROJ_ID)
  );
CREATE TABLE GENOME_PROK
  (
    PROJ_ID         NUMERIC(10,0),
    REFSEQ          NUMERIC(10,0),
    NCBI_TAXON_ID   NUMERIC(10,0),
    ORGANISM_NAME   VARCHAR(100),
    SUPER_KINGDOM   VARCHAR(50),
    TAXON_GROUP     VARCHAR(50),
    SEQUENCE_STATUS VARCHAR(50),
    GENOME_SIZE     NUMERIC(14,4),
    GC_CONTENT      VARCHAR(20),
    GRAM_STAIN      NUMERIC(1,0),
    SHAPE           VARCHAR(50),
    ARRANGEMENT     VARCHAR(50),
    ENDOSPORES      VARCHAR(50),
    MOTILITY        VARCHAR(50),
    SALINITY        VARCHAR(50),
    OXYGEN_REQ      VARCHAR(50),
    HABITAT         VARCHAR(50),
    TEMP_RANGE      VARCHAR(50),
    OPTIMAL_TEMP    VARCHAR(50),
    PATHOGENIC_IN   VARCHAR(50),
    DISEASE         VARCHAR(512),
    CREATED_AT DATE,
    UPDATED_AT DATE,
    PRIMARY KEY (PROJ_ID)
  );
CREATE TABLE GENOME_PROK_COMPLETE
  (
    PROJ_ID           NUMERIC(10,0),
    CHROMOSOME_NUMBER NUMERIC(5,0),
    PLASMID_NUMBER NUMERIC(5,0),
    DATE_RELEASED DATE,
    DATE_MODIFIED DATE,
    CREATED_AT DATE,
    UPDATED_AT DATE,
    PRIMARY KEY (PROJ_ID)
  );
CREATE TABLE GENOME_PROK_INPROGRESS
  (
    PROJ_ID           NUMERIC(10,0),
    REFSEQ_ACCESSION  VARCHAR(20),
    GENBANK_ACCESSION VARCHAR(20),
    CONTIG_NR         NUMERIC(10,0),
    CDS_NR            NUMERIC(10,0),
    DATE_RELEASED     DATE,
    CENTER_NAME           VARCHAR(255),
    CENTER_URL            VARCHAR(1023),
    SEQUENCE_AVAILABILITY VARCHAR(20),
    CREATED_AT DATE,
    UPDATED_AT DATE,
    PRIMARY KEY (PROJ_ID)
  );
CREATE TABLE GENOME_PROJ_ACC
  (
    ID               NUMERIC(10,0),
    PROJ_ID          NUMERIC(10,0),
    ACCESSION_NUMBER VARCHAR(255),
    PRIMARY KEY (ID),
    UNIQUE (PROJ_ID, ACCESSION_NUMBER),
    FOREIGN KEY (PROJ_ID) REFERENCES GENOME_PROJECTS(ID)
  );
CREATE UNIQUE INDEX ON GENOME_PROJ_ACC
  (
    PROJ_ID, ACCESSION_NUMBER
  );
CREATE TABLE GENOME_PROJ_REFSEQ
  (
    ID         NUMERIC(10,0),
    PROJ_ID    NUMERIC(10,0),
    REFSEQ_ACC VARCHAR(255),
    PRIMARY KEY (ID),
    UNIQUE (PROJ_ID, REFSEQ_ACC),
    FOREIGN KEY (PROJ_ID) REFERENCES GENOME_PROJECTS(ID)
  );
CREATE UNIQUE INDEX ON GENOME_PROJ_REFSEQ
  (
    PROJ_ID, REFSEQ_ACC
  );
CREATE TABLE PROVIDERS
  (
    ID   NUMERIC(8,0),
    NAME VARCHAR(128),
    ABBR VARCHAR(32),
    URL  VARCHAR(512),
    PRIMARY KEY (ID)
  );
CREATE INDEX ABBR_INDEX ON PROVIDERS
  (
    ABBR
  );
CREATE TABLE MAPPINGS
  (
    ID           NUMERIC(8,0),
    URL          VARCHAR(1024),
    SUBJECT_TYPE VARCHAR(128),
    LINK_NAME    VARCHAR(128),
    CATEGORY     VARCHAR(128),
    PROVIDER_ID  NUMERIC(8,0),
    PROJECT_ID   NUMERIC(10,0),
    TARGET_ID    VARCHAR(20),
    CREATED_AT DATE,
    UPDATED_AT DATE,
    PRIMARY KEY (ID),
    FOREIGN KEY (PROVIDER_ID) REFERENCES PROVIDERS (ID),
    CONSTRAINT MAPPINGS_GENOME_PROJECTS_FK1 FOREIGN KEY (PROJECT_ID) REFERENCES GENOME_PROJECTS (ID)
  );
CREATE INDEX PROVIDER_TARGET_IDX ON MAPPINGS
  (
    PROVIDER_ID,
    TARGET_ID
  );<commit_msg>Tested and updated the oracle database creation script.<commit_after>-- Run this script to setup tables, note that
-- it drops any old tables present.
-- It's not been tested very well on anything other than Oracle,
-- You can safely drop trigger definitions and created_at/updated_at
-- columns for most tables. Currently the only way to make this
-- work for other databases is to write a new database loader though
-- and check the mappings from the grs-web project. Ideally, these
-- tables would be generated from those mappings, instead of manually
-- defined here.

-- DROP OLD TABLES (IN ORDER)
DROP TABLE MAPPINGS;
DROP TABLE PROVIDERS;
DROP TABLE GENOME_PROK_COMPLETE;
DROP TABLE GENOME_PROK_INPROGRESS;
DROP TABLE GENOME_PROK;
DROP TABLE GENOME_EUK;
DROP TABLE GENOME_ENV;
DROP TABLE GENOME_PROJ_ACC;
DROP TABLE GENOME_PROJ_REFSEQ;
DROP TABLE PROJECTS_LABS;
DROP TABLE GENOME_PROJECTS;
DROP SEQUENCE MAPPING_SEQ;
DROP SEQUENCE PROJ_LABS_SEQ;
DROP SEQUENCE GENOME_PROJ_ACC_SEQ;
DROP SEQUENCE GENOME_PROJ_REFSEQ_SEQ;

-- CREATE THE TABLE LAYOUT
-- Should be somewhat portable, but probably contains some oracle-isms

-- sequences will not work in mysql, unfortunately the code relies on them...
CREATE SEQUENCE GENOME_PROJ_ACC_SEQ;
CREATE SEQUENCE GENOME_PROJ_REFSEQ_SEQ;
CREATE SEQUENCE MAPPING_SEQ;
CREATE SEQUENCE PROJ_LABS_SEQ;

CREATE TABLE GENOME_PROJECTS
  (
    ID NUMERIC(10,0),
    PRIMARY KEY (ID)
  );
CREATE TABLE PROJECTS_LABS
  (
    ID       NUMBER(10,0),
    PROJ_ID  NUMBER(10,0),
    LAB_NAME VARCHAR(255),
    PRIMARY KEY (ID),
    UNIQUE (PROJ_ID, LAB_NAME)
  );
CREATE TABLE GENOME_EUK
  (
    PROJ_ID           NUMERIC(10,0),
    ORGANISM_NAME     VARCHAR(255),
    ORGANISM_GROUP    VARCHAR(50),
    ORGANISM_SUBGROUP VARCHAR(50),
    NCBI_TAXON_ID     NUMERIC(10,0),
    GENOME_SIZE       NUMERIC(14,4),
    NR_CHROMOSOMES    NUMERIC(8,0),
    SEQUENCE_STATUS   VARCHAR(20),
    SEQUENCE_METHOD   VARCHAR(20),
    COVERAGE          VARCHAR(50),
    RELEASE_DATE DATE,
    CREATED_AT DATE,
    UPDATED_AT DATE,
    PRIMARY KEY (PROJ_ID)
  );
CREATE OR REPLACE
  TRIGGER EUK_CHANGES
  BEFORE INSERT OR UPDATE ON GENOME_EUK
  FOR EACH ROW
  BEGIN
    IF INSERTING THEN
      :NEW.created_at := sysdate;
    ELSE
      :NEW.created_at := :OLD.created_at;
    END IF;
    :NEW.updated_at := sysdate;
END;
/

CREATE TABLE GENOME_ENV
  (
    PROJ_ID           NUMERIC(10,0),
    PARENT_PROJ_ID    NUMERIC(10,0),
    TITLE             VARCHAR(255),
    METAGENOME_SOURCE VARCHAR(50),
    METAGENOME_TYPE   NUMERIC(4,0),
    RELEASE_DATE DATE,
    CREATED_AT DATE,
    UPDATED_AT DATE,
    PRIMARY KEY (PROJ_ID)
  );
CREATE OR REPLACE
  TRIGGER ENV_CHANGES
  BEFORE INSERT OR UPDATE ON GENOME_ENV
  FOR EACH ROW
  BEGIN
    IF INSERTING THEN
      :NEW.created_at := sysdate;
    ELSE
      :NEW.created_at := :OLD.created_at;
    END IF;
    :NEW.updated_at := sysdate;
END;
/

CREATE TABLE GENOME_PROK
  (
    PROJ_ID         NUMERIC(10,0),
    REFSEQ          NUMERIC(10,0),
    NCBI_TAXON_ID   NUMERIC(10,0),
    ORGANISM_NAME   VARCHAR(100),
    SUPER_KINGDOM   VARCHAR(50),
    TAXON_GROUP     VARCHAR(50),
    SEQUENCE_STATUS VARCHAR(50),
    GENOME_SIZE     NUMERIC(14,4),
    GC_CONTENT      VARCHAR(20),
    GRAM_STAIN      NUMERIC(1,0),
    SHAPE           VARCHAR(50),
    ARRANGEMENT     VARCHAR(50),
    ENDOSPORES      VARCHAR(50),
    MOTILITY        VARCHAR(50),
    SALINITY        VARCHAR(50),
    OXYGEN_REQ      VARCHAR(50),
    HABITAT         VARCHAR(50),
    TEMP_RANGE      VARCHAR(50),
    OPTIMAL_TEMP    VARCHAR(50),
    PATHOGENIC_IN   VARCHAR(50),
    DISEASE         VARCHAR(512),
    CREATED_AT DATE,
    UPDATED_AT DATE,
    PRIMARY KEY (PROJ_ID)
  );
CREATE OR REPLACE
  TRIGGER PROK_CHANGES
  BEFORE INSERT OR UPDATE ON GENOME_PROK
  FOR EACH ROW
  BEGIN
    IF INSERTING THEN
      :NEW.created_at := sysdate;
    ELSE
      :NEW.created_at := :OLD.created_at;
    END IF;
    :NEW.updated_at := sysdate;
END;
/

CREATE TABLE GENOME_PROK_COMPLETE
  (
    PROJ_ID           NUMERIC(10,0),
    CHROMOSOME_NUMBER NUMERIC(5,0),
    PLASMID_NUMBER NUMERIC(5,0),
    DATE_RELEASED DATE,
    DATE_MODIFIED DATE,
    CREATED_AT DATE,
    UPDATED_AT DATE,
    PRIMARY KEY (PROJ_ID)
  );
CREATE OR REPLACE
  TRIGGER PROK_COMPLETE_CHANGES
  BEFORE INSERT OR UPDATE ON GENOME_PROK_COMPLETE
  FOR EACH ROW
  BEGIN
    IF INSERTING THEN
      :NEW.created_at := sysdate;
    ELSE
      :NEW.created_at := :OLD.created_at;
    END IF;
    :NEW.updated_at := sysdate;
END;
/

CREATE TABLE GENOME_PROK_INPROGRESS
  (
    PROJ_ID           NUMERIC(10,0),
    REFSEQ_ACCESSION  VARCHAR(20),
    GENBANK_ACCESSION VARCHAR(20),
    CONTIG_NR         NUMERIC(10,0),
    CDS_NR            NUMERIC(10,0),
    DATE_RELEASED     DATE,
    CENTER_NAME           VARCHAR(255),
    CENTER_URL            VARCHAR(1023),
    SEQUENCE_AVAILABILITY VARCHAR(20),
    CREATED_AT DATE,
    UPDATED_AT DATE,
    PRIMARY KEY (PROJ_ID)
  );
CREATE OR REPLACE
  TRIGGER PROK_INPROGRESS_CHANGES
  BEFORE INSERT OR UPDATE ON GENOME_PROK_INPROGRESS
  FOR EACH ROW
  BEGIN
    IF INSERTING THEN
      :NEW.created_at := sysdate;
    ELSE
      :NEW.created_at := :OLD.created_at;
    END IF;
    :NEW.updated_at := sysdate;
END;
/

CREATE TABLE GENOME_PROJ_ACC
  (
    ID               NUMERIC(10,0),
    PROJ_ID          NUMERIC(10,0),
    ACCESSION_NUMBER VARCHAR(255),
    PRIMARY KEY (ID),
    UNIQUE (PROJ_ID, ACCESSION_NUMBER),
    FOREIGN KEY (PROJ_ID) REFERENCES GENOME_PROJECTS(ID)
  );
CREATE TABLE GENOME_PROJ_REFSEQ
  (
    ID         NUMERIC(10,0),
    PROJ_ID    NUMERIC(10,0),
    REFSEQ_ACC VARCHAR(255),
    PRIMARY KEY (ID),
    UNIQUE (PROJ_ID, REFSEQ_ACC),
    FOREIGN KEY (PROJ_ID) REFERENCES GENOME_PROJECTS(ID)
  );
CREATE TABLE PROVIDERS
  (
    ID   NUMERIC(8,0),
    NAME VARCHAR(128),
    ABBR VARCHAR(32),
    URL  VARCHAR(512),
    PRIMARY KEY (ID)
  );
CREATE INDEX ABBR_INDEX ON PROVIDERS
  (
    ABBR
  );
CREATE TABLE MAPPINGS
  (
    ID           NUMERIC(8,0),
    URL          VARCHAR(1024),
    SUBJECT_TYPE VARCHAR(128),
    LINK_NAME    VARCHAR(128),
    CATEGORY     VARCHAR(128),
    PROVIDER_ID  NUMERIC(8,0),
    PROJECT_ID   NUMERIC(10,0),
    TARGET_ID    VARCHAR(20),
    CREATED_AT DATE,
    UPDATED_AT DATE,
    PRIMARY KEY (ID),
    FOREIGN KEY (PROVIDER_ID) REFERENCES PROVIDERS (ID),
    CONSTRAINT MAPPINGS_GENOME_PROJECTS_FK1 FOREIGN KEY (PROJECT_ID) REFERENCES GENOME_PROJECTS (ID)
  );
CREATE OR REPLACE
  TRIGGER MAPPING_CHANGES
  BEFORE INSERT OR UPDATE ON MAPPINGS
  FOR EACH ROW
  BEGIN
    IF INSERTING THEN
      :NEW.created_at := sysdate;
    ELSE
      :NEW.created_at := :OLD.created_at;
    END IF;
    :NEW.updated_at := sysdate;
END;
/

CREATE INDEX PROVIDER_TARGET_IDX ON MAPPINGS
  (
    PROVIDER_ID,
    TARGET_ID
  );
{'fraction_non_alphanumeric': 0.07969848395019903, 'fraction_numerical': 0.02998221394088253, 'mean_word_length': 2.8450016281341584, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3069572', 'n_tokens_mistral': 5040, 'n_tokens_neox': 4468, 'n_words': 1046}