<?php
/*******************************************************************************
 *
 * LEIDEN OPEN VARIATION DATABASE (LOVD)
 *
 * Created     : 2006-03-31
 * Modified    : 2011-01-27
 * For LOVD    : 2.0-30
 *
 * Access      : Public
 * Purpose     : Include SQL to create the standard LOVD tables.
 *
 * Copyright   : 2004-2011 Leiden University Medical Center; http://www.LUMC.nl/
 * Programmer  : Ing. Ivo F.A.C. Fokkema <I.F.A.C.Fokkema@LUMC.nl>
 * Last edited : Ing. Ivo F.A.C. Fokkema <I.F.A.C.Fokkema@LUMC.nl>
 *
 *
 * This file is part of LOVD.
 *
 * LOVD is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * LOVD is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with LOVD; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 *
 *************/

$aTableSQL =
         array('TABLE_USERS' =>
   'CREATE TABLE ' . TABLE_USERS . ' (
    userid TINYINT(3) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    institute VARCHAR(75) NOT NULL,
    email VARCHAR(75) NOT NULL,
    username VARCHAR(20) NOT NULL,
    password CHAR(32) NOT NULL,
    password_autogen CHAR(32) NOT NULL,
    password_force_change TINYINT(1) UNSIGNED NOT NULL,
    phpsessid CHAR(32) NOT NULL,
    current_db VARCHAR(12) NOT NULL,
    list_size SMALLINT(4) UNSIGNED NOT NULL,
    level TINYINT(1) UNSIGNED NOT NULL,
    allowed_ip VARCHAR(255) NOT NULL,
    login_attempts TINYINT(1) UNSIGNED NOT NULL,
    last_login DATETIME,
    deleted TINYINT(1) NOT NULL,
    created_by TINYINT(3) UNSIGNED NOT NULL,
    created_date DATETIME NOT NULL,
    edited_by TINYINT(3) UNSIGNED,
    edited_date DATETIME,
    UNIQUE (username))
    TYPE=' . $_INI['database']['engine']

         , 'TABLE_SUBS' =>
   'CREATE TABLE ' . TABLE_SUBS . ' (
    submitterid SMALLINT(5) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(50) NOT NULL,
    lastname VARCHAR(50) NOT NULL,
    institute VARCHAR(75) NOT NULL,
    department VARCHAR(75) NOT NULL,
    address TEXT NOT NULL,
    city VARCHAR(50) NOT NULL,
    country CHAR(2) NOT NULL,
    email VARCHAR(200) NOT NULL,
    telephone VARCHAR(50) NOT NULL,
    reference VARCHAR(50) NOT NULL,
    username VARCHAR(20) NOT NULL,
    password CHAR(32) NOT NULL,
    password_autogen CHAR(32) NOT NULL,
    password_force_change TINYINT(1) UNSIGNED NOT NULL,
    login_attempts TINYINT(1) UNSIGNED NOT NULL,
    last_login DATETIME,
    deleted TINYINT(1) NOT NULL,
    created_by TINYINT(3) UNSIGNED NOT NULL,
    created_date DATETIME NOT NULL,
    edited_by TINYINT(3) UNSIGNED,
    edited_date DATETIME,
    UNIQUE (username))
    TYPE=' . $_INI['database']['engine']

         , 'TABLE_CONFIG' =>
   'CREATE TABLE ' . TABLE_CONFIG . ' (
    system_title VARCHAR(255) NOT NULL,
    location_name VARCHAR(255) NOT NULL,
    location_url VARCHAR(255) NOT NULL,
    email_address VARCHAR(75) NOT NULL,
    send_fwd TINYINT(1) NOT NULL,
    send_signature TINYINT(1) UNSIGNED NOT NULL,
    send_stats TINYINT(1) UNSIGNED NOT NULL,
    send_listings TINYINT(1) UNSIGNED NOT NULL,
    use_cookies TINYINT(1) UNSIGNED NOT NULL,
    use_ssl TINYINT(1) UNSIGNED NOT NULL,
    my_submissions TINYINT(1) UNSIGNED NOT NULL,
    search_count_entries TINYINT(1) UNSIGNED NOT NULL,
    hide_nonpublic TINYINT(1) UNSIGNED NOT NULL,
    api_feed_history TINYINT(1) UNSIGNED NOT NULL,
    lock_curators TINYINT(1) UNSIGNED NOT NULL,
    lock_submitters TINYINT(1) UNSIGNED NOT NULL,
    allow_unlock TINYINT(1) UNSIGNED NOT NULL,
    lock_uninstall TINYINT(1) UNSIGNED NOT NULL)
    TYPE=' . $_INI['database']['engine']

         , 'TABLE_STATUS' =>
   'CREATE TABLE ' . TABLE_STATUS . ' (
    lock_update TINYINT(1) UNSIGNED NOT NULL,
    version VARCHAR(15) NOT NULL,
    signature CHAR(32) NOT NULL,
    update_check DATETIME,
    update_version VARCHAR(15),
    update_level TINYINT(1) UNSIGNED,
    update_description VARCHAR(255),
    update_released DATE,
    date_install DATE NOT NULL,
    date_updated DATE,
    upgrade_progress TINYINT(2) NOT NULL)
    TYPE=' . $_INI['database']['engine']

         , 'TABLE_COLS' =>
   'CREATE TABLE ' . TABLE_COLS . ' (
    colid VARCHAR(100) NOT NULL PRIMARY KEY,
    col_order TINYINT(3) UNSIGNED NOT NULL,
    hgvs TINYINT(1) UNSIGNED NOT NULL,
    standard TINYINT(1) UNSIGNED NOT NULL,
    mandatory TINYINT(1) UNSIGNED NOT NULL,
    head_column VARCHAR(30) NOT NULL,
    description_form TEXT NOT NULL,
    description_legend_short TEXT NOT NULL,
    description_legend_full TEXT NOT NULL,
    mysql_type VARCHAR(50) NOT NULL,
    form_type VARCHAR(100) NOT NULL,
    select_options TEXT NOT NULL,
    preg_pattern VARCHAR(200) NOT NULL,
    public TINYINT(1) UNSIGNED NOT NULL,
    public_form TINYINT(1) UNSIGNED NOT NULL,
    allow_count_all TINYINT(1) UNSIGNED NOT NULL,
    created_by TINYINT(3) UNSIGNED NOT NULL,
    created_date DATETIME NOT NULL,
    edited_by TINYINT(3) UNSIGNED,
    edited_date DATETIME)
    TYPE=' . $_INI['database']['engine']

         , 'TABLE_LINKS' =>
   'CREATE TABLE ' . TABLE_LINKS . ' (
    linkid TINYINT(3) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY,
    linkname VARCHAR(50) NOT NULL,
    pattern_text VARCHAR(20) NOT NULL,
    replace_text TEXT NOT NULL,
    link_description TEXT NOT NULL,
    active TINYINT(1) UNSIGNED NOT NULL,
    created_by TINYINT(3) UNSIGNED NOT NULL,
    created_date DATETIME NOT NULL,
    edited_by TINYINT(3) UNSIGNED,
    edited_date DATETIME,
    UNIQUE (linkname),
    UNIQUE (pattern_text))
    TYPE=' . $_INI['database']['engine']

         , 'TABLE_SOURCES' =>
   'CREATE TABLE ' . TABLE_SOURCES . ' (
    source VARCHAR(15) NOT NULL PRIMARY KEY,
    url VARCHAR(150) NOT NULL)
    TYPE=' . $_INI['database']['engine']

         , 'TABLE_COUNTRIES' =>
   'CREATE TABLE ' . TABLE_COUNTRIES . ' (
    code CHAR(2) NOT NULL PRIMARY KEY,
    country VARCHAR(35) NOT NULL)
    TYPE=' . $_INI['database']['engine']

         , 'TABLE_LOGS' =>
   'CREATE TABLE ' . TABLE_LOGS . ' (
    logname VARCHAR(10) NOT NULL,
    date DATETIME NOT NULL,
    mtime MEDIUMINT(6) UNSIGNED ZEROFILL NOT NULL,
    event VARCHAR(20) NOT NULL,
    log TEXT NOT NULL,
    UNIQUE (logname, date, mtime))
    TYPE=' . $_INI['database']['engine']

         , 'TABLE_MODULES' =>
   'CREATE TABLE ' . TABLE_MODULES . ' (
    moduleid VARCHAR(15) NOT NULL UNIQUE,
    name VARCHAR(35) NOT NULL,
    version VARCHAR(15) NOT NULL,
    description VARCHAR(200) NOT NULL,
    active TINYINT(1) NOT NULL,
    settings TEXT NOT NULL,
    date_install DATE NOT NULL,
    date_updated DATE)
    TYPE=' . $_INI['database']['engine']

         , 'TABLE_DBS' =>
   'CREATE TABLE ' . TABLE_DBS . ' (
    symbol VARCHAR(12) NOT NULL UNIQUE,
    gene VARCHAR(175) NOT NULL,
    chrom_location VARCHAR(20) NOT NULL,
    refseq_genomic VARCHAR(15) NOT NULL,
    refseq_mrna VARCHAR(15) NOT NULL,
    refseq_build VARCHAR(4) NOT NULL,
    c_position_mrna_start SMALLINT NOT NULL,
    c_position_mrna_end MEDIUMINT UNSIGNED NOT NULL,
    c_position_cds_end MEDIUMINT UNSIGNED NOT NULL,
    g_position_mrna_start INT UNSIGNED NOT NULL,
    g_position_mrna_end INT UNSIGNED NOT NULL,
    reference VARCHAR(200) NOT NULL,
    url_homepage VARCHAR(150) NOT NULL,
    url_external TEXT NOT NULL,
    allow_download TINYINT(1) UNSIGNED NOT NULL,
    allow_index_wiki TINYINT(1) UNSIGNED NOT NULL,
    id_hgnc INT(10) UNSIGNED NOT NULL,
    id_entrez INT(10) UNSIGNED NOT NULL,
    id_omim_gene INT(10) UNSIGNED NOT NULL,
    id_omim_disease TEXT NOT NULL,
    id_uniprot VARCHAR(8) NOT NULL,
    show_hgmd TINYINT(1) UNSIGNED NOT NULL,
    show_genecards TINYINT(1) UNSIGNED NOT NULL,
    show_genetests TINYINT(1) UNSIGNED NOT NULL,
    note_index TEXT NOT NULL,
    note_listing TEXT NOT NULL,
    genbank TINYINT(1) NOT NULL,
    genbank_uri VARCHAR(25) NOT NULL,
    refseq VARCHAR(1) NOT NULL,
    refseq_url VARCHAR(255) NOT NULL,
    disclaimer TINYINT(1) UNSIGNED NOT NULL,
    disclaimer_text TEXT NOT NULL,
    header TEXT NOT NULL,
    header_align TINYINT(1) NOT NULL,
    footer TEXT NOT NULL,
    footer_align TINYINT(1) NOT NULL,
    created_by TINYINT(3) UNSIGNED NOT NULL,
    created_date DATETIME NOT NULL,
    edited_by TINYINT(3) UNSIGNED,
    edited_date DATETIME,
    updated_by TINYINT(3) UNSIGNED,
    updated_date DATETIME NOT NULL)
    TYPE=' . $_INI['database']['engine']

         , 'TABLE_PATIENTS' =>
   'CREATE TABLE ' . TABLE_PATIENTS . ' (
    patientid MEDIUMINT(7) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY,
    submitterid SMALLINT(5) UNSIGNED ZEROFILL NOT NULL,
    created_by TINYINT(3) UNSIGNED NOT NULL,
    created_date DATETIME NOT NULL,
    edited_by TINYINT(3) UNSIGNED,
    edited_date DATETIME,
    INDEX (submitterid))
    TYPE=' . $_INI['database']['engine']

         , 'TABLE_PATIENTS_COLS' =>
   'CREATE TABLE ' . TABLE_PATIENTS_COLS . ' (
    colid VARCHAR(100) NOT NULL PRIMARY KEY,
    col_order TINYINT(3) UNSIGNED NOT NULL,
    width SMALLINT(5) UNSIGNED NOT NULL,
    mandatory TINYINT(1) UNSIGNED NOT NULL,
    description_form TEXT NOT NULL,
    description_legend_short TEXT NOT NULL,
    description_legend_full TEXT NOT NULL,
    select_options TEXT NOT NULL,
    public TINYINT(1) UNSIGNED NOT NULL,
    public_form TINYINT(1) UNSIGNED NOT NULL,
    created_by TINYINT(3) UNSIGNED NOT NULL,
    created_date DATETIME NOT NULL,
    edited_by TINYINT(3) UNSIGNED,
    edited_date DATETIME)
    TYPE=' . $_INI['database']['engine']

         , 'TABLE_CURATES' =>
   'CREATE TABLE ' . TABLE_CURATES . ' (
    userid TINYINT(3) UNSIGNED NOT NULL,
    symbol VARCHAR(12) NOT NULL,
    show_order TINYINT(2) UNSIGNED NOT NULL DEFAULT 1,
    UNIQUE(userid, symbol))
    TYPE=' . $_INI['database']['engine']

         , 'TABLE_COLS2LINKS' =>
   'CREATE TABLE ' . TABLE_COLS2LINKS . ' (
    colid VARCHAR(100) NOT NULL,
    linkid TINYINT(3) UNSIGNED NOT NULL,
    UNIQUE(colid, linkid))
    TYPE=' . $_INI['database']['engine']

         , 'TABLE_PAT2VAR' =>
   'CREATE TABLE ' . TABLE_PAT2VAR . ' (
    patientid MEDIUMINT(7) UNSIGNED ZEROFILL NOT NULL,
    allele TINYINT(2) UNSIGNED NOT NULL,
    symbol VARCHAR(12) NOT NULL,
    variantid MEDIUMINT(7) UNSIGNED ZEROFILL NOT NULL,
    pathogenic TINYINT(2) UNSIGNED ZEROFILL NOT NULL,
    status TINYINT(1) UNSIGNED NOT NULL,
    created_by TINYINT(3) UNSIGNED NOT NULL,
    created_date DATETIME NOT NULL,
    edited_by TINYINT(3) UNSIGNED,
    edited_date DATETIME,
    PRIMARY KEY (symbol, variantid, patientid, allele),
    INDEX (patientid))
    TYPE=' . $_INI['database']['engine']

         , 'TABLE_VAR_STATUS' =>
   'CREATE TABLE ' . TABLE_VAR_STATUS . ' (
    status TINYINT(1) UNSIGNED NOT NULL PRIMARY KEY,
    status_text VARCHAR(10) NOT NULL)
    TYPE=' . $_INI['database']['engine']

         , 'TABLE_PATHOGENIC' =>
   'CREATE TABLE ' . TABLE_PATHOGENIC . ' (
    pathogenic TINYINT(2) UNSIGNED ZEROFILL NOT NULL PRIMARY KEY,
    pathogenic_text VARCHAR(5) NOT NULL)
    TYPE=' . $_INI['database']['engine']
          );

