/*
 * Copyright 2010 Institute for Systems Biology
 *                Seattle, Washington, USA.
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *     http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
-- $Id$

-- =====================================================================
--                         SQL Medline Schema
--
--                           Gaurav Bhalotia
--                            Feb. 16, 2003
--                           Ariel Schwartz
--                              Jun. 10, 2003
--                              Based on:
--                         nlmmedline_021101.dtd
--                      nlmmedlinecitation_021101.dtd
--                         nlmcommon_021101.dtd
--
--               Revised to support version 031101 of the DTDs
--                         nlmmedline_031101.dtd
--                      nlmmedlinecitation_031101.dtd
--                         nlmcommon_031101.dtd
--                        nlmsharedcatcit_031101.dtd
--                             Ariel Schwartz
--                              Mar. 10, 2004
--
--                   The DTD files are located at:
--                     http://www.nlm.nih.gov/databases/dtd/
--
-- NOTE: Deleted citations are not part of the schema.
--         Those should be handled by the code to keep the current
--       copy updated.
--
-- 2010-06-24
-- Updated for MySQL 5.1 and Jan 1, 2010 DTD: nlmmedlinecitationset_100101.dtd
-- David Julian, Institute for Systems Biology
-- =====================================================================

/* MySQL Notes:
 *
 * This script does not create the database; you must do so manually.  You are
 * probably fine with the defaults regarding character sets, collation, and
 * storage engines.  However, the README contains further information to be
 * aware of.
 */

-- =============================================================
-- TABLE: medline_citation
-- =============================================================
DROP TABLE IF EXISTS medline_citation;
CREATE TABLE medline_citation(
    pmid                            INTEGER         NOT NULL,
    date_created                    DATE            NOT NULL,
    date_completed                  DATE,
    date_revised                    DATE,

    -- /MedlineCitation/Article
    pub_model                       VARCHAR(20)     NOT NULL,
    -- /MedlineCitation/Article/Journal
    issn                            CHAR(9),
    issn_type                       VARCHAR(20),
    -- /MedlineCitation/Article/Journal/JournalIssue
    cited_medium                    VARCHAR(10),
    volume                          VARCHAR(100),
    issue                           VARCHAR(100),
    pub_date_year                   VARCHAR(4),
    pub_date_month                  VARCHAR(20),
    pub_date_day                    VARCHAR(2),
    pub_date_season                 VARCHAR(10),
    medline_date                    VARCHAR(100),
    -- /MedlineCitation/Article/Journal
    journal_title                   VARCHAR(2000),
    iso_abbreviation                VARCHAR(100),
    -- /MedlineCitation/Article
    article_title                   VARCHAR(1000)   NOT NULL,
    -- /MedlineCitation/Article/Pagination
    medline_pgn                     VARCHAR(100),
    -- /MedlineCitation/Article/Abstract
    abstract_text                   TEXT,           -- was: CLOB(10000),
    copyright_info                  VARCHAR(1000),
    -- /MedlineCitation/Article
    article_affiliation             VARCHAR(500),
    article_author_list_comp_yn     CHAR(1)         DEFAULT 'Y',
    data_bank_list_comp_yn          CHAR(1)         DEFAULT 'Y',
    grantlist_complete_yn           CHAR(1)         DEFAULT 'Y',
    vernacular_title                VARCHAR(1000),
    date_of_electronic_publication  DATE,
    elec_pub_official_date_yn       CHAR(1)         DEFAULT 'N',
    -- end Article

    -- /MedlineCitation/MedlineJournalInfo
    country                         VARCHAR(50),
    medline_ta                      VARCHAR(500)    NOT NULL,
    nlm_unique_id                   VARCHAR(20),
    issn_linking                    VARCHAR(20),
    -- end MedlineJournalInfo

    xml_file_name                   VARCHAR(500)    NOT NULL,
    number_of_references            INTEGER,
    keyword_list_owner              VARCHAR(30),
    citation_owner                  VARCHAR(30)     DEFAULT 'NLM',
    citation_status                 VARCHAR(50)     NOT NULL,

    PRIMARY KEY (pmid)
);

-- =============================================================
-- TABLE: medline_author
/*
<!ELEMENT   Author ( (LastName, ForeName?, Initials?, Suffix?, NameID*)
             | (CollectiveName,NameID*) )>
<!ATTLIST   Author ValidYN (Y | N) "Y">
*/
-- NameID "reserved for future use."
-- =============================================================
DROP TABLE IF EXISTS medline_author;
CREATE TABLE medline_author (
    pmid                            INTEGER         NOT NULL,
    valid_yn                        CHAR(1)         DEFAULT 'Y',
    last_name                       VARCHAR(500),
    fore_name                       VARCHAR(50),
    initials                        VARCHAR(10),
    suffix                          VARCHAR(10),
    collective_name                 VARCHAR(500),

    INDEX idx_author_pmid (pmid),

    CONSTRAINT fk_med_author
        FOREIGN KEY (pmid)
        REFERENCES medline_citation (pmid) ON DELETE CASCADE
);

-- =============================================================
-- TABLE: medline_chemical_list
-- =============================================================
DROP TABLE IF EXISTS medline_chemical_list;
CREATE TABLE medline_chemical_list (
    pmid                            INTEGER         NOT NULL,
    registry_number                 VARCHAR(20)     NOT NULL,
    name_of_substance               VARCHAR(2000)   NOT NULL,

    INDEX idx_chemlist_pmid (pmid),

    CONSTRAINT fk1_med_chem_list
        FOREIGN KEY (pmid)
        REFERENCES medline_citation (pmid) ON DELETE CASCADE
);

-- =============================================================
-- TABLE: medline_gene_symbol_list
-- =============================================================
DROP TABLE IF EXISTS medline_gene_symbol_list;
CREATE TABLE medline_gene_symbol_list (
    pmid                            INTEGER         NOT NULL,
    gene_symbol                     VARCHAR(100)    NOT NULL,

    /* MEDLINE 2010 sample files include one citation which associates with
     * genes aprt and APRT.  The database is case insensitive, of course, and
     * the insertion fails.  It seems this difference-in-case has fallen out
     * of favor, but is preserved here. */
    -- PRIMARY KEY (pmid, gene_symbol)
    INDEX idx_gene_symbol_pmid (pmid),

    CONSTRAINT fk1_med_gs_list
        FOREIGN KEY (pmid)
        REFERENCES medline_citation (pmid) ON DELETE CASCADE
);

-- =============================================================
-- TABLE: medline_keyword_list
-- =============================================================
DROP TABLE IF EXISTS medline_keyword_list;
CREATE TABLE medline_keyword_list (
    pmid                            INTEGER         NOT NULL,
    keyword                         VARCHAR(500)    NOT NULL,
    keyword_major_yn                CHAR(1)         DEFAULT 'N',

    /* MEDLINE 2010 has a citation in file 294 where the same PMID and keyword
     * appear, one as major 'Y' and the other as major 'N'.  Not sure what to
     * make of this, really, but this errs on the side of preserving. */
    -- PRIMARY KEY (pmid, keyword),
    INDEX idx_keyword_pmid (pmid),
    INDEX idx_keyword_keyword (keyword),

    CONSTRAINT fk1_med_kw_list
        FOREIGN KEY (pmid)
        REFERENCES medline_citation (pmid) ON DELETE CASCADE
);

-- =============================================================
-- TABLE: medline_mesh_heading
-- =============================================================
DROP TABLE IF EXISTS medline_mesh_heading;
CREATE TABLE medline_mesh_heading(
    pmid                            INTEGER         NOT NULL,
    descriptor_name                 VARCHAR(500)    NOT NULL,
    descriptor_name_major_yn        CHAR(1)         DEFAULT 'N',

    PRIMARY KEY (pmid, descriptor_name),

    INDEX idx_mesh_heading_descriptor_name (descriptor_name),

    CONSTRAINT fk_med_meshheading
        FOREIGN KEY (pmid)
        REFERENCES medline_citation (pmid) ON DELETE CASCADE
);

-- =============================================================
-- TABLE: medline_mesh_heading_qualifier
-- =============================================================
DROP TABLE IF EXISTS medline_mesh_heading_qualifier;
CREATE TABLE medline_mesh_heading_qualifier(
    pmid                            INTEGER         NOT NULL,
    descriptor_name                 VARCHAR(500)    NOT NULL,
    qualifier_name                  VARCHAR(50)     NOT NULL,
    qualifier_name_major_yn         CHAR(1)         DEFAULT 'N',

    PRIMARY KEY (pmid, descriptor_name, qualifier_name),

    CONSTRAINT fk_med_mesh_qual
        FOREIGN KEY (pmid, descriptor_name)
        REFERENCES medline_mesh_heading (pmid, descriptor_name)
        ON DELETE CASCADE
);

-- =============================================================
-- TABLE: medline_comments_corrections
-- =============================================================
DROP TABLE IF EXISTS medline_comments_corrections;
CREATE TABLE medline_comments_corrections (
    pmid                            INTEGER         NOT NULL,
    ref_type                        VARCHAR(20)     NOT NULL,
    ref_source                      VARCHAR(1000)   NOT NULL,
    ref_pmid                        INTEGER,
    note                            VARCHAR(2000),

    INDEX idx_comments_pmid (pmid),

    CONSTRAINT fk1_med_cc
        FOREIGN KEY (pmid)
        REFERENCES medline_citation (pmid) ON DELETE CASCADE
);

-- =============================================================
-- TABLE: medline_citation_subsets
-- =============================================================
DROP TABLE IF EXISTS medline_citation_subsets;
CREATE TABLE medline_citation_subsets(
    pmid                            INTEGER         NOT NULL,
    citation_subset                 VARCHAR(500)    NOT NULL,

    PRIMARY KEY (pmid, citation_subset),

    CONSTRAINT fk1_med_cit_sub
        FOREIGN KEY (pmid)
        REFERENCES medline_citation (pmid) ON DELETE CASCADE
);

-- =============================================================
-- TABLE: medline_article_publication_type
-- =============================================================
DROP TABLE IF EXISTS medline_article_publication_type;
CREATE TABLE medline_article_publication_type(
    pmid                            INTEGER         NOT NULL,
    publication_type                VARCHAR(100)    NOT NULL,
    
    INDEX idx_pub_type_pmid (pmid),

    CONSTRAINT fk1_med_art_ptype
        FOREIGN KEY (pmid)
        REFERENCES medline_citation (pmid) ON DELETE CASCADE
);

-- =============================================================
-- TABLE: medline_article_language
-- =============================================================
DROP TABLE IF EXISTS medline_article_language;
CREATE TABLE medline_article_language(
    pmid                            INTEGER         NOT NULL,
    language                        VARCHAR(100)    NOT NULL,
    
    INDEX idx_lang_pmid (pmid),

    CONSTRAINT fk1_med_lang
        FOREIGN KEY (pmid)
        REFERENCES medline_citation (pmid) ON DELETE CASCADE
);

-- =============================================================
-- TABLE: medline_article_elocationid
-- http://www.nlm.nih.gov/bsd/licensee/elements_descriptions.html#elocationid
-- =============================================================
DROP TABLE IF EXISTS medline_article_elocationid;
CREATE TABLE medline_article_elocationid(
    pmid                            INTEGER         NOT NULL,
    valid_yn                        CHAR(1)         DEFAULT 'Y',
    eid_type                        CHAR(3)         NOT NULL,
    elocationid                     VARCHAR(100)    NOT NULL,

    INDEX idx_elocationid_pmid (pmid),

    CONSTRAINT fk1_med_art_elid
        FOREIGN KEY (pmid)
        REFERENCES medline_citation (pmid) ON DELETE CASCADE
);

-- =============================================================
-- TABLE: medline_grant
-- <!ELEMENT   Grant (GrantID?, Acronym?, Agency, Country)>
-- =============================================================
DROP TABLE IF EXISTS medline_grant;
CREATE TABLE medline_grant(
    pmid                            INTEGER         NOT NULL,
    grant_id                        VARCHAR(100),
    acronym                         VARCHAR(100),
    agency                          VARCHAR(500)    NOT NULL,
    country                         VARCHAR(100)    NOT NULL,

    INDEX idx_grant_pmid (pmid),

    CONSTRAINT fk1_medline_grant
        FOREIGN KEY (pmid)
        REFERENCES medline_citation (pmid) ON DELETE CASCADE
);

-- =============================================================
-- TABLE: medline_data_bank
-- =============================================================
DROP TABLE IF EXISTS medline_data_bank;
CREATE TABLE medline_data_bank(
    pmid                            INTEGER         NOT NULL,
    data_bank_name                  VARCHAR(100)    NOT NULL,
    accession_number                VARCHAR(100)    NOT NULL,

    INDEX idx_data_bank_pmid (pmid),

    CONSTRAINT fk1_med_d_bank
        FOREIGN KEY (pmid)
        REFERENCES medline_citation (pmid) ON DELETE CASCADE
);

-- =============================================================
-- TABLE: medline_personal_name_subject
-- =============================================================
DROP TABLE IF EXISTS medline_personal_name_subject;
CREATE TABLE medline_personal_name_subject (
    pmid                            INTEGER         NOT NULL,
    last_name                       VARCHAR(500)    NOT NULL,
    fore_name                       VARCHAR(50),
    initials                        VARCHAR(10),
    suffix                          VARCHAR(10),

    INDEX idx_pers_name_pmid (pmid),

    CONSTRAINT fk_med_p_name
        FOREIGN KEY (pmid)
        REFERENCES medline_citation (pmid) ON DELETE CASCADE
);

-- =============================================================
-- TABLE: medline_citation_other_id
-- =============================================================
DROP TABLE IF EXISTS medline_citation_other_id;
CREATE TABLE medline_citation_other_id(
    pmid                            INTEGER         NOT NULL,
    source                          VARCHAR(100)    NOT NULL,
    other_id                        VARCHAR(1000)   NOT NULL,

    INDEX idx_other_id_pmid (pmid),

    CONSTRAINT fk1_med_o_id
        FOREIGN KEY (pmid)
        REFERENCES medline_citation (pmid) ON DELETE CASCADE
);

-- =============================================================
-- TABLE: medline_citation_other_abstract
-- =============================================================
DROP TABLE IF EXISTS medline_citation_other_abstract;
CREATE TABLE medline_citation_other_abstract(
    pmid                            INTEGER         NOT NULL,
    type                            VARCHAR(100)    NOT NULL,
    copyright_info                  VARCHAR(500),
    abstract_text                   TEXT            NOT NULL,

    INDEX idx_citation_other_abs_pmid (pmid),

    CONSTRAINT fk1_med_o_abs
        FOREIGN KEY (pmid)
        REFERENCES medline_citation (pmid) ON DELETE CASCADE
);

-- =============================================================
-- TABLE: medline_space_flight_mission
-- =============================================================
DROP TABLE IF EXISTS medline_space_flight_mission;
CREATE TABLE medline_space_flight_mission(
    pmid                            INTEGER         NOT NULL,
    space_flight_mission            VARCHAR(100)    NOT NULL,

    INDEX idx_flight_pmid (pmid),

    CONSTRAINT fk1_med_space
        FOREIGN KEY (pmid)
        REFERENCES medline_citation (pmid) ON DELETE CASCADE
);

-- =============================================================
-- TABLE: medline_investigator
/*
<!ELEMENT   Investigator (LastName,ForeName?, Initials?,Suffix?,NameID*,Affiliation?)>
<!ATTLIST   Investigator ValidYN (Y | N) "Y">
*/
-- NameID "reserved for future use."
-- =============================================================
DROP TABLE IF EXISTS medline_investigator;
CREATE TABLE medline_investigator (
    pmid                            INTEGER         NOT NULL,
    valid_yn                        CHAR(1)         DEFAULT 'Y',
    last_name                       VARCHAR(500)    NOT NULL,
    fore_name                       VARCHAR(50),
    initials                        VARCHAR(10),
    suffix                          VARCHAR(10),
    affiliation                     VARCHAR(500),

    INDEX idx_invest_pmid (pmid),

    CONSTRAINT fk_med_invest
        FOREIGN KEY (pmid)
        REFERENCES medline_citation (pmid) ON DELETE CASCADE
);

-- =============================================================
-- TABLE: medline_general_note
-- =============================================================
DROP TABLE IF EXISTS medline_general_note;
CREATE TABLE medline_general_note(
    pmid                            INTEGER         NOT NULL,
    owner                           VARCHAR(100),
    general_note                    VARCHAR(1000),

    INDEX idx_gen_note_pmid (pmid),

    CONSTRAINT fk1_med_g_note
        FOREIGN KEY (pmid)
        REFERENCES medline_citation (pmid) ON DELETE CASCADE
);
