<?php
/*******************************************************************************
 *
 * LOVD DATABASE INCLUDE FILE
 *
 * Created     : 2006-05-01
 * Modified    : 2010-05-03
 * For LOVD    : 2.0-26
 *
 * Access      : Curators and up.
 * Purpose     : This file will be called after authenticating any type of LOVD
 *               user. Purpose is to check if the database has been upgraded
 *               accordingly if files of a new LOVD 2.0 release have been
 *               uploaded.
 *               This file does NOT handle the transition of 1.1.0 to 2.0.
 *
 * Copyright   : 2004-2009 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
 *
 *************/

// Don't allow direct access.
if (!defined('ROOT_PATH')) {
    exit;
}

// 2009-07-17; 2.0-20; Added increased execution time to help perform large upgrades.
if ((int) ini_get('max_execution_time') < 60) {
    set_time_limit(60);
}

// How are the versions related?
$sCalcVersionFiles = lovd_calculateVersion($_SETT['system']['version']);
$sCalcVersionDB = lovd_calculateVersion($_STAT['version']);

if ($sCalcVersionFiles != $sCalcVersionDB) {
    // Version of files are not equal to version of database backend.

    // DB version greater than file version... then we have a problem.
    if ($sCalcVersionFiles < $sCalcVersionDB) {
        lovd_displayError('MySQL:Install|UpgradeError', 'Database version ' . $_STAT['version'] . ' found newer than file version ' . $_SETT['system']['version']);
    }

    require ROOT_PATH . 'inc-top.php';
    lovd_printHeader('upgrade', 'Upgrading LOVD...');

    print('      Please wait while LOVD is upgrading the database backend from ' . $_STAT['version'] . ' to ' . $_SETT['system']['version'] . '.<BR><BR>' . "\n" .
          '      <PRE class="S11">' . "\n");

    // Array of changes.
    $aUpdates =
             array(
                    '2.0-beta-01' =>
                             array(
                                    'INSERT INTO ' . TABLE_SOURCES . ' VALUES ("genbank", "http://www.ncbi.nlm.nih.gov/entrez/viewer.fcgi?db=nucleotide&sendto=t&extrafeatpresent=1&list_uids={{ ID }}")',
                                    'UPDATE ' . TABLE_DBS . ' SET refseq = "c" WHERE refseq = 1',
                                    'UPDATE ' . TABLE_DBS . ' SET refseq = "" WHERE refseq = 0',
                                  ),
                    '2.0-beta-02' =>
                             array(
                                    'ALTER TABLE ' . TABLE_USERS . ' DROP COLUMN refresh_list',
                                    'UPDATE ' . TABLE_COLS . ' SET description_legend_full = "Effect of change on RNA.<BR>\r\n<UL style=\"margin-top : 0px;\">\r\n  <LI>= = RNA change identical to DNA change</LI>\r\n  <LI>? = unknown</LI>\r\n  <LI>(=) = no significant effect expected (but no experimental proof)</LI>\r\n  <LI>(0) = change expected to abolish transcription</LI>\r\n  <LI>(ex4ex5del) = probably deletion of exons 4 to 5</LI>\r\n  <LI>(ex4ex5dup) = probably duplication of exons 4 to 5</LI>\r\n <LI>+cry = activation of cryptic splice site (no sequence published)</LI>\r\n  <LI>spl? = effect on splicing very likely (no experimental proof), examples;</LI>\r\n  <UL>\r\n    <LI>splice donor site change (nucleotides +1 to +5 affected)</LI>\r\n    <LI>splice acceptor site change (nucleotides -2 to -1 affected)</LI>\r\n    <LI>new intronic AG splice acceptor di-nucleotide created close to (within 15 nucleotides) of normal splice acceptor site</LI>\r\n  </UL>\r\n  <LI>(spl?) = might affect splicing (no experimental proof), examples;</LI>\r\n  <UL>\r\n    <LI>change affects first or last nucleotide of exon</LI>\r\n    <LI>change creates strong splice donor or splice acceptor site in exon</LI>\r\n  </UL>\r\n</UL>" WHERE colid = "Variant/RNA" AND description_legend_full = "Effect of change on RNA.<BR>\r\n<UL style=\"margin-top : 0px;\">\r\n  <LI>= = RNA change identical to DNA change</LI>\r\n  <LI>? = unknown</LI>\r\n  <LI>(=) = no significant effect expected (but no experimental proof)</LI>\r\n  <LI>(0) = change expected to abolish transcription</LI>\r\n  <LI>(ex4ex5del) = probably deletion of exons 4 to 5</LI>\r\n  <LI>(ex4ex5dup) = probably duplication of exons 4 to 5</LI>\r\n <LI>+cry = activation of cryptic splice site (no sequence published)</LI>\r\n  <LI>spl? = effect on splicing very likely (no experimental proof), examples;</LI>\r\n  <UL>\r\n    <LI>splice donor site change (nucleotides +1 to +5 affected)</LI>\r\n    <LI>splice acceptor site change (nucleotides -2 to -1 affected)</LI>\r\n    <LI>new intronic AG splice acceptor di-nucleotide created close to (within 15 nucleotides) of normal splice acceptor site</LI>\r\n  </UL>\r\n  <LI>(spl?) = might affect splicing (no experimental proof), examples;</LI>\r\n  <UL>\r\n    <LI>change affects first or last nucleotide of exon</LI>\r\n    <LI>change creates strond splice donor or splice acceptor site in exon</LI>\r\n  </UL>\r\n</UL>"',
                                  ),
                    '2.0-beta-03' =>
                             array(
                                    'ALTER TABLE ' . TABLE_DBS . ' ADD COLUMN reference VARCHAR(200) NOT NULL AFTER chrom_location',
                                    'ALTER TABLE ' . TABLE_DBS . ' ADD COLUMN disclaimer TINYINT(1) UNSIGNED NOT NULL AFTER refseq_url',
                                    'ALTER TABLE ' . TABLE_DBS . ' ADD COLUMN disclaimer_text TEXT NOT NULL AFTER disclaimer',
                                    'ALTER TABLE ' . TABLE_DBS . ' ADD COLUMN header TEXT NOT NULL AFTER disclaimer_text',
                                    'ALTER TABLE ' . TABLE_DBS . ' ADD COLUMN footer TEXT NOT NULL AFTER header',
                                    'INSERT INTO ' . TABLE_COLS2LINKS . ' VALUES ("DBS/Reference", 1)',
                                  ),
                    '2.0-beta-04' =>
                             array(
                                    'ALTER TABLE ' . TABLE_MODULES . ' MODIFY COLUMN moduleid VARCHAR(15) NOT NULL UNIQUE',
                                    'ALTER TABLE ' . TABLE_MODULES . ' ADD COLUMN name VARCHAR(25) NOT NULL AFTER moduleid',
                                    'ALTER TABLE ' . TABLE_MODULES . ' ADD COLUMN description VARCHAR(200) NOT NULL AFTER version',
                                    'ALTER TABLE ' . TABLE_MODULES . ' ADD COLUMN settings TEXT NOT NULL AFTER active',
                                    'UPDATE ' . TABLE_COLS2LINKS . ' SET colid = "Gene/Reference" WHERE colid = "DBS/Reference"',
                                    'ALTER TABLE ' . TABLE_LOGS . ' MODIFY COLUMN event VARCHAR(15) NOT NULL',
                                  ),
                    '2.0-beta-05' =>
                             array(
                                    'UPDATE ' . TABLE_COLS . ' SET mysql_type = "VARCHAR(100)" WHERE colid = "Variant/DNA"',
                                    'UPDATE ' . TABLE_COLS . ' SET mysql_type = "VARCHAR(100)" WHERE colid = "Variant/RNA"',
                                    'UPDATE ' . TABLE_COLS . ' SET mysql_type = "VARCHAR(100)" WHERE colid = "Variant/Protein"',
                                  ),
                    '2.0-beta-08' =>
                             array(
                                    'ALTER TABLE ' . TABLE_USERS . ' ADD UNIQUE (username)',
                                    'ALTER TABLE ' . TABLE_SUBS . ' ADD UNIQUE (username)',
                                  ),
                    '2.0-beta-10' =>
                             array(
                                    'UPDATE ' . TABLE_COLS . ' SET description_form = "The ID-field should start with the ID; the gene symbol followed by an underscore (_) and the ID code, usually five digits <SPAN class=\"S11\">(<A href=\"http://www.lovd.nl/1.1.0/about-dbid.php\" target=\"_blank\">more info</A>)</SPAN>", description_legend_short = "<A href=\"http://www.lovd.nl/1.1.0/about-dbid.php\">Database IDentifier</A>; When available, links to OMIM ID\'s are provided.", description_legend_full = "<A href=\"http://www.lovd.nl/1.1.0/about-dbid.php\">Database IDentifier</A>. When available, links to OMIM ID\'s are provided." WHERE colid = "Variant/DBID"',
                                    'ALTER TABLE ' . TABLE_MODULES . ' MODIFY COLUMN name VARCHAR(35) NOT NULL',
                                  ),
                    '2.0-01' =>
                             array(
                                    'ALTER TABLE ' . TABLE_PATIENTS . ' ADD INDEX (submitterid)',
                                  ),
                    '2.0-02' =>
                             array(
                                    'ALTER TABLE ' . TABLE_CONFIG . ' ADD COLUMN hide_nonpublic TINYINT(1) UNSIGNED NOT NULL AFTER my_submissions',
                                    'UPDATE ' . TABLE_CONFIG . ' set hide_nonpublic = 0',
                                  ),
                    '2.0-03' =>
                             array(
                                    'ALTER TABLE ' . TABLE_DBS . ' ADD COLUMN header_align TINYINT(1) NOT NULL AFTER header',
                                    'ALTER TABLE ' . TABLE_DBS . ' ADD COLUMN footer_align TINYINT(1) NOT NULL AFTER footer',
                                    'UPDATE ' . TABLE_DBS . ' set header_align = 0',
                                    'UPDATE ' . TABLE_DBS . ' set footer_align = 0',
                                    'ALTER TABLE ' . TABLE_SUBS . ' MODIFY COLUMN email TEXT NOT NULL',
                                  ),
                    '2.0-04' =>
                             array(
                                    'ALTER TABLE ' . TABLE_CONFIG . ' ADD COLUMN use_ssl TINYINT(1) UNSIGNED NOT NULL AFTER use_cookies',
                                    'UPDATE ' . TABLE_PAT2VAR . ' SET pathogenic = pathogenic - 40 WHERE pathogenic >= 90',
                                    'UPDATE ' . TABLE_PAT2VAR . ' SET pathogenic = pathogenic + 80 WHERE pathogenic >= 10 AND pathogenic < 20',
                                    'UPDATE ' . TABLE_PAT2VAR . ' SET pathogenic = pathogenic + 10 WHERE pathogenic < 10',
                                    'UPDATE ' . TABLE_PAT2VAR . ' SET pathogenic = pathogenic - 4 WHERE MOD(pathogenic, 10) = 9',
                                    'UPDATE ' . TABLE_PAT2VAR . ' SET pathogenic = pathogenic + 8 WHERE MOD(pathogenic, 10) = 1',
                                    'UPDATE ' . TABLE_PAT2VAR . ' SET pathogenic = pathogenic + 1 WHERE MOD(pathogenic, 10) = 0',
                                    'DELETE FROM ' . TABLE_PATHOGENIC,
                                    'ALTER TABLE ' . TABLE_PATHOGENIC . ' MODIFY COLUMN pathogenic_text VARCHAR(5) NOT NULL',
                                  ),
                    '2.0-06' =>
                             array(
                                    'UPDATE ' . TABLE_LINKS . ' SET replace_text = "<A href=\"http://www.ncbi.nlm.nih.gov/pubmed/[1]\" target=\"_blank\">[2]</A>" WHERE linkid = 001 AND (replace_text = "<A href=\"http://www.ncbi.nlm.nih.gov/entrez/query.fcgi?cmd=Retrieve&amp;db=PubMed&amp;dopt=Abstract&amp;list_uids=[1]\" target=\"_blank\">[2]</A>" OR replace_text = "<A href=\"http://www.ncbi.nlm.nih.gov/entrez/query.fcgi?cmd=Retrieve&db=PubMed&dopt=Abstract&list_uids=[1]\" target=\"_blank\">[2]</A>")',
                                    'ALTER TABLE ' . TABLE_COLS . ' MODIFY COLUMN head_column VARCHAR(30) NOT NULL',
                                  ),
                    '2.0-07' =>
                             array(
                                    'INSERT IGNORE INTO ' . TABLE_COLS . ' VALUES ("Variant/Remarks", 0, 0, 0, "Variant remarks", "", "Variant remarks", "Variant remarks", "TEXT", "Remarks|textarea|50|3", "", "", 1, 1, 0, NOW(), NULL, NULL)',
                                  ),
                    '2.0-11' =>
                             array(
                                    'ALTER TABLE ' . TABLE_LOGS . ' MODIFY COLUMN event VARCHAR(20) NOT NULL',
                                    'UPDATE ' . TABLE_LOGS . ' SET event = "SubmitterCreate" WHERE event = "SubCreate"',
                                    'UPDATE ' . TABLE_LOGS . ' SET event = "SubmitterEdit" WHERE event = "SubEdit"',
                                    'UPDATE ' . TABLE_LOGS . ' SET event = "SubmitterDrop" WHERE event = "SubDrop"',
                                    'UPDATE ' . TABLE_LOGS . ' SET event = "SubmitterPurge" WHERE event = "SubPurge"',
                                    'UPDATE ' . TABLE_LOGS . ' SET event = "SubmitterRegister" WHERE event = "SubRegister"',
                                    'UPDATE ' . TABLE_LOGS . ' SET event = "VariantImport" WHERE event = "VarImport"',
                                  ),
                    '2.0-12' =>
                             array(
                                    'INSERT IGNORE INTO ' . TABLE_COLS . ' VALUES ("Variant/DNA_published", 0, 0, 0, "DNA published", "What the variant was reported as (e.g. 521delT); listed only when different from \"DNA change\"", "What the variant was reported as.", "What the variant was reported as (e.g. 521delT); listed only when different from \"DNA change\".", "VARCHAR(100)", "DNA published|text|30", "", "", 1, 1, 0, NOW(), NULL, NULL)',
                                    'ALTER TABLE ' . TABLE_CONFIG . ' ADD COLUMN search_count_entries TINYINT(1) UNSIGNED NOT NULL AFTER my_submissions',
                                    'ALTER TABLE ' . TABLE_COLS . ' ADD COLUMN allow_count_all TINYINT(1) UNSIGNED NOT NULL AFTER public_form',
                                    'UPDATE ' . TABLE_COLS . ' SET allow_count_all = public',
                                  ),
                    '2.0-14' =>
                             array(
                                    'ALTER TABLE ' . TABLE_USERS . ' ADD COLUMN password_force_change TINYINT(1) UNSIGNED NOT NULL AFTER password_autogen',
                                    'ALTER TABLE ' . TABLE_SUBS . ' ADD COLUMN password_force_change TINYINT(1) UNSIGNED NOT NULL AFTER password_autogen',
                                    'ALTER TABLE ' . TABLE_CONFIG . ' ADD COLUMN allow_unlock TINYINT(1) UNSIGNED NOT NULL AFTER lock_submitters',
                                  ),
                    '2.0-16' =>
                             array(
                                    'ALTER TABLE ' . TABLE_COLS . ' MODIFY COLUMN mysql_type VARCHAR(50) NOT NULL',
                                  ),
                    '2.0-17' =>
                             array(
                                    'ALTER TABLE ' . TABLE_DBS . ' ADD COLUMN allow_index_wiki TINYINT(1) UNSIGNED NOT NULL AFTER allow_download',
                                    'ALTER TABLE ' . TABLE_CONFIG . ' ADD COLUMN location_url VARCHAR(255) NOT NULL AFTER location_name',
                                  ),
                    '2.0-19' =>
                             array(
                                    'UPDATE ' . TABLE_SOURCES . ' SET url = "http://www.ncbi.nlm.nih.gov/sites/GeneTests/lab/gene/{{ ID }}" WHERE source = "genetests"',
                                  ),
                    '2.0-20' =>
                             array(
                                    'ALTER TABLE ' . TABLE_COLS . ' ADD COLUMN col_order TINYINT(3) UNSIGNED NOT NULL AFTER colid',
                                    'UPDATE ' . TABLE_COLS . ' SET description_form = "" WHERE colid = "Variant/Exon" AND description_form = "Please pad the exon number with a zero for ordering purposes, i.e. \'02\' in stead of \'2\'"',
                                    'UPDATE ' . TABLE_COLS . ' SET description_form = "The ID-field should start with the ID; the gene symbol followed by an underscore (_) and the ID code, usually five digits." WHERE colid = "Variant/DBID" AND description_form = "The ID-field should start with the ID; the gene symbol followed by an underscore (_) and the ID code, usually five digits <SPAN class=\"S11\">(<A href=\"http://www.lovd.nl/1.1.0/about-dbid.php\" target=\"_blank\">more info</A>)</SPAN>"',
                                    'UPDATE ' . TABLE_COLS . ' SET description_legend_short = "Database IDentifier; When available, links to OMIM ID\'s are provided." WHERE colid = "Variant/DBID" AND description_legend_short = "<A href=\"http://www.lovd.nl/1.1.0/about-dbid.php\">Database IDentifier</A>; When available, links to OMIM ID\'s are provided."',
                                    'UPDATE ' . TABLE_COLS . ' SET description_legend_full = "Database IDentifier; When available, links to OMIM ID\'s are provided." WHERE colid = "Variant/DBID" AND description_legend_full = "<A href=\"http://www.lovd.nl/1.1.0/about-dbid.php\">Database IDentifier</A>. When available, links to OMIM ID\'s are provided."',
                                  ),
                    '2.0-21' =>
                             array(
                                    'ALTER TABLE ' . TABLE_DBS . ' ADD COLUMN id_uniprot VARCHAR(8) NOT NULL AFTER id_omim_disease',
                                    'ALTER TABLE ' . TABLE_DBS . ' ADD COLUMN show_genecards TINYINT(1) UNSIGNED NOT NULL AFTER show_hgmd',
                                    'INSERT INTO ' . TABLE_SOURCES . ' VALUES ("genecards", "http://www.genecards.org/cgi-bin/carddisp.pl?gene={{ ID }}")',
                                    'INSERT INTO ' . TABLE_SOURCES . ' VALUES ("uniprot",   "http://www.uniprot.org/uniprot/{{ ID }}")',
                                    'ALTER TABLE ' . TABLE_CONFIG . ' ADD COLUMN api_feed_history TINYINT(1) UNSIGNED NOT NULL AFTER hide_nonpublic',
                                    'UPDATE ' . TABLE_CONFIG . ' SET api_feed_history = 3',
                                  ),
                    '2.0-23' =>
                             array(
                                    'UPDATE ' . TABLE_DBS . ' SET genbank = 3 WHERE genbank = 2 AND genbank_uri LIKE "UD_%"',
                                    'ALTER TABLE ' . TABLE_DBS . ' ADD COLUMN refseq_genomic VARCHAR(15) NOT NULL AFTER chrom_location',
                                    'ALTER TABLE ' . TABLE_DBS . ' ADD COLUMN refseq_mrna VARCHAR(15) NOT NULL AFTER refseq_genomic',
                                    'ALTER TABLE ' . TABLE_DBS . ' ADD COLUMN refseq_build VARCHAR(4) NOT NULL AFTER refseq_mrna',
                                    'UPDATE ' . TABLE_DBS . ' SET refseq_build = "hg19"',
                                    'ALTER TABLE ' . TABLE_DBS . ' MODIFY COLUMN gene VARCHAR(100) NOT NULL',
                                    'ALTER TABLE ' . TABLE_DBS . ' ADD COLUMN c_position_mrna_start SMALLINT NOT NULL AFTER refseq_build',
                                    'ALTER TABLE ' . TABLE_DBS . ' ADD COLUMN c_position_mrna_end MEDIUMINT UNSIGNED NOT NULL AFTER c_position_mrna_start',
                                    'ALTER TABLE ' . TABLE_DBS . ' ADD COLUMN c_position_cds_end MEDIUMINT UNSIGNED NOT NULL AFTER c_position_mrna_end',
                                    'ALTER TABLE ' . TABLE_DBS . ' ADD COLUMN g_position_mrna_start INT UNSIGNED NOT NULL AFTER c_position_cds_end',
                                    'ALTER TABLE ' . TABLE_DBS . ' ADD COLUMN g_position_mrna_end INT UNSIGNED NOT NULL AFTER g_position_mrna_start',
                                  ),
                    '2.0-25' =>
                             array(
                                    'ALTER TABLE ' . TABLE_DBS . ' ADD COLUMN id_hgnc INT(10) UNSIGNED NOT NULL AFTER allow_index_wiki',
                                  ),
                    '2.0-26' =>
                             array(
                                    'ALTER TABLE ' . TABLE_COLS . ' MODIFY COLUMN description_form TEXT NOT NULL',
                                    'ALTER TABLE ' . TABLE_COLS . ' MODIFY COLUMN description_legend_short TEXT NOT NULL',
                                    'ALTER TABLE ' . TABLE_PATIENTS_COLS . ' MODIFY COLUMN description_form TEXT NOT NULL',
                                    'ALTER TABLE ' . TABLE_PATIENTS_COLS . ' MODIFY COLUMN description_legend_short TEXT NOT NULL',
                                    'INSERT INTO ' . TABLE_SOURCES . ' VALUES ("hgnc", "http://www.genenames.org/data/hgnc_data.php?hgnc_id={{ ID }}")',
                                    'ALTER TABLE ' . TABLE_DBS . ' MODIFY COLUMN gene VARCHAR(175) NOT NULL',
                                  ),
                  );

    // Addition for upgrade to LOVD v.2.0-beta-02.
    if ($sCalcVersionDB < lovd_calculateVersion('2.0-beta-02')) {
        // Gather available genes and update these.
        $q = mysql_query('SELECT symbol FROM ' . TABLE_DBS);
        while (list($sSymbol) = mysql_fetch_row($q)) {
            $aUpdates['2.0-beta-02'][] = 'UPDATE ' . TABLEPREFIX . '_' . $sSymbol . '_columns SET description_legend_full = "Effect of change on RNA.<BR>\r\n<UL style=\"margin-top : 0px;\">\r\n  <LI>= = RNA change identical to DNA change</LI>\r\n  <LI>? = unknown</LI>\r\n  <LI>(=) = no significant effect expected (but no experimental proof)</LI>\r\n  <LI>(0) = change expected to abolish transcription</LI>\r\n  <LI>(ex4ex5del) = probably deletion of exons 4 to 5</LI>\r\n  <LI>(ex4ex5dup) = probably duplication of exons 4 to 5</LI>\r\n <LI>+cry = activation of cryptic splice site (no sequence published)</LI>\r\n  <LI>spl? = effect on splicing very likely (no experimental proof), examples;</LI>\r\n  <UL>\r\n    <LI>splice donor site change (nucleotides +1 to +5 affected)</LI>\r\n    <LI>splice acceptor site change (nucleotides -2 to -1 affected)</LI>\r\n    <LI>new intronic AG splice acceptor di-nucleotide created close to (within 15 nucleotides) of normal splice acceptor site</LI>\r\n  </UL>\r\n  <LI>(spl?) = might affect splicing (no experimental proof), examples;</LI>\r\n  <UL>\r\n    <LI>change affects first or last nucleotide of exon</LI>\r\n    <LI>change creates strong splice donor or splice acceptor site in exon</LI>\r\n  </UL>\r\n</UL>" WHERE colid = "Variant/RNA" AND description_legend_full = "Effect of change on RNA.<BR>\r\n<UL style=\"margin-top : 0px;\">\r\n  <LI>= = RNA change identical to DNA change</LI>\r\n  <LI>? = unknown</LI>\r\n  <LI>(=) = no significant effect expected (but no experimental proof)</LI>\r\n  <LI>(0) = change expected to abolish transcription</LI>\r\n  <LI>(ex4ex5del) = probably deletion of exons 4 to 5</LI>\r\n  <LI>(ex4ex5dup) = probably duplication of exons 4 to 5</LI>\r\n <LI>+cry = activation of cryptic splice site (no sequence published)</LI>\r\n  <LI>spl? = effect on splicing very likely (no experimental proof), examples;</LI>\r\n  <UL>\r\n    <LI>splice donor site change (nucleotides +1 to +5 affected)</LI>\r\n    <LI>splice acceptor site change (nucleotides -2 to -1 affected)</LI>\r\n    <LI>new intronic AG splice acceptor di-nucleotide created close to (within 15 nucleotides) of normal splice acceptor site</LI>\r\n  </UL>\r\n  <LI>(spl?) = might affect splicing (no experimental proof), examples;</LI>\r\n  <UL>\r\n    <LI>change affects first or last nucleotide of exon</LI>\r\n    <LI>change creates strond splice donor or splice acceptor site in exon</LI>\r\n  </UL>\r\n</UL>"';
        }
    }

    // Addition for upgrade to LOVD v.2.0-beta-05.
    if ($sCalcVersionDB < lovd_calculateVersion('2.0-beta-05')) {
        // Gather available genes.
        $q = mysql_query('SELECT symbol FROM ' . TABLE_DBS);
        while (list($sSymbol) = mysql_fetch_row($q)) {
            // Does this gene have these columns enabled? Should be the case (HGVS cols), but it can be circumvented.
            $qCols = mysql_query('SELECT colid FROM ' . TABLEPREFIX . '_' . $sSymbol . '_columns WHERE colid IN ("Variant/DNA", "Variant/RNA", "Variant/Protein")');
            while (list($sColID) = mysql_fetch_row($qCols)) {
                $aUpdates['2.0-beta-05'][] = 'ALTER TABLE ' . TABLEPREFIX . '_' . $sSymbol . '_variants MODIFY COLUMN `' . $sColID . '` VARCHAR(100) NOT NULL';
            }
        }
    }

    // Addition for upgrade to LOVD v.2.0-beta-08.
    if ($sCalcVersionDB < lovd_calculateVersion('2.0-beta-08')) {
        if (!class_exists('CurrDB')) {
            require ROOT_PATH . 'class/currdb.php';
        }
        if (!function_exists('lovd_sort')) {
            require ROOT_PATH . 'inc-lib-form.php';
        }

        // Gather available genes.
        $q = mysql_query('SELECT symbol FROM ' . TABLE_DBS);
        while (list($sSymbol) = mysql_fetch_row($q)) {
            $_CURRDB = new CurrDB(true, $sSymbol);
            $sMutationCol = $_CURRDB->getMutationCol();
            if ($sMutationCol && in_array($sMutationCol, array('Variant/DNA', 'Variant/RNA'))) {
                // Fetch variants and edit!
                $qMuts = mysql_query('SELECT `' . $sMutationCol . '` FROM ' . TABLEPREFIX . '_' . $sSymbol . '_variants WHERE sort = "" GROUP BY `' . $sMutationCol . '`');
                while (list($sMut) = mysql_fetch_row($qMuts)) {
                    $sSort = lovd_sort($sMut);
                    $aUpdates['2.0-beta-08'][] = 'UPDATE ' . TABLEPREFIX . '_' . $sSymbol . '_variants SET sort = "' . $sSort . '" WHERE `' . $sMutationCol . '` = "' . $sMut . '"';
                }
            }
        }
    }

    // Addition for upgrade to LOVD v.2.0-beta-10.
    if ($sCalcVersionDB < lovd_calculateVersion('2.0-beta-10')) {
        // Gather available genes and update these.
        $q = mysql_query('SELECT symbol FROM ' . TABLE_DBS);
        // We're not checking whether or not the genes have this column or not, just run the query.
        while (list($sSymbol) = mysql_fetch_row($q)) {
            $aUpdates['2.0-beta-10'][] = 'UPDATE ' . TABLEPREFIX . '_' . $sSymbol . '_columns SET description_form = "The ID-field should start with the ID; the gene symbol followed by an underscore (_) and the ID code, usually five digits <SPAN class=\"S11\">(<A href=\"http://www.lovd.nl/1.1.0/about-dbid.php\" target=\"_blank\">more info</A>)</SPAN>", description_legend_short = "<A href=\"http://www.lovd.nl/1.1.0/about-dbid.php\">Database IDentifier</A>; When available, links to OMIM ID\'s are provided.", description_legend_full = "<A href=\"http://www.lovd.nl/1.1.0/about-dbid.php\">Database IDentifier</A>. When available, links to OMIM ID\'s are provided." WHERE colid = "Variant/DBID"';
        }
    }

    // Addition for upgrade to LOVD v.2.0-04.
    if ($sCalcVersionDB < lovd_calculateVersion('2.0-04')) {
        // Update pathogenicity codes.
        foreach ($_SETT['var_pathogenic_short'] as $nStatus => $sStatus) {
            $aUpdates['2.0-04'][] = 'INSERT INTO ' . TABLE_PATHOGENIC . ' VALUES (' . $nStatus . ', "' . $sStatus . '")';
        }
    }

    // Addition for upgrade to LOVD v.2.0-19.
    if ($sCalcVersionDB < lovd_calculateVersion('2.0-19')) {
        // Gather available genes and update these.
        if (!class_exists('CurrDB')) {
            require ROOT_PATH . 'class/currdb.php';
        }

        $aGenes = lovd_getGeneList();
        foreach ($aGenes as $sSymbol) {
            $aUpdates['2.0-19'][] = 'ALTER TABLE ' . TABLEPREFIX . '_' . $sSymbol . '_variants MODIFY COLUMN sort VARCHAR(52) NOT NULL';
            $_CURRDB = new CurrDB(true, $sSymbol);
            if ($_CURRDB->colExists('Variant/Exon')) {
                $aUpdates['2.0-19'][] = 'UPDATE ' . TABLEPREFIX . '_' . $sSymbol . '_variants SET sort = CONCAT(LPAD(SUBSTRING(`Variant/Exon`, 1, 4), 4, "0"), "_", sort)';
            } else {
                $aUpdates['2.0-19'][] = 'UPDATE ' . TABLEPREFIX . '_' . $sSymbol . '_variants SET sort = CONCAT("0000_", sort)';
            }
            unset($_CURRDB);
        }
    }

    // Addition for upgrade to LOVD v.2.0-20.
    // No choice but to repeat this piece of code; 2.0-19 allowed you to create genes with a sort column that was too short.
    if ($sCalcVersionDB < lovd_calculateVersion('2.0-20')) {
        // Gather available genes and update these.
        if (!class_exists('CurrDB')) {
            require ROOT_PATH . 'class/currdb.php';
        }
        if (!function_exists('lovd_sort')) {
            require ROOT_PATH . 'inc-lib-form.php';
        }

        $aGenes = lovd_getGeneList();
        foreach ($aGenes as $sSymbol) {
            $aUpdates['2.0-20'][] = 'ALTER TABLE ' . TABLEPREFIX . '_' . $sSymbol . '_variants MODIFY COLUMN sort VARCHAR(52) NOT NULL';
            $aUpdates['2.0-20'][] = 'UPDATE ' . TABLEPREFIX . '_' . $sSymbol . '_columns SET description_form = "" WHERE colid = "Variant/Exon" AND description_form = "Please pad the exon number with a zero for ordering purposes, i.e. \'02\' in stead of \'2\'"';
            $aUpdates['2.0-20'][] = 'UPDATE ' . TABLEPREFIX . '_' . $sSymbol . '_columns SET description_form = "The ID-field should start with the ID; the gene symbol followed by an underscore (_) and the ID code, usually five digits." WHERE colid = "Variant/DBID" AND description_form = "The ID-field should start with the ID; the gene symbol followed by an underscore (_) and the ID code, usually five digits <SPAN class=\"S11\">(<A href=\"http://www.lovd.nl/1.1.0/about-dbid.php\" target=\"_blank\">more info</A>)</SPAN>"';
            $aUpdates['2.0-20'][] = 'UPDATE ' . TABLEPREFIX . '_' . $sSymbol . '_columns SET description_legend_short = "Database IDentifier; When available, links to OMIM ID\'s are provided." WHERE colid = "Variant/DBID" AND description_legend_short = "<A href=\"http://www.lovd.nl/1.1.0/about-dbid.php\">Database IDentifier</A>; When available, links to OMIM ID\'s are provided."';
            $aUpdates['2.0-20'][] = 'UPDATE ' . TABLEPREFIX . '_' . $sSymbol . '_columns SET description_legend_full = "Database IDentifier; When available, links to OMIM ID\'s are provided." WHERE colid = "Variant/DBID" AND description_legend_full = "<A href=\"http://www.lovd.nl/1.1.0/about-dbid.php\">Database IDentifier</A>. When available, links to OMIM ID\'s are provided."';
            $_CURRDB = new CurrDB(true, $sSymbol);
            if ($_CURRDB->colExists('Variant/Exon')) {
                $sMutationCol = $_CURRDB->getMutationCol();
                if ($sMutationCol && in_array($sMutationCol, array('Variant/DNA', 'Variant/RNA'))) {
                    // Fetch variants and edit!
                    // This is somewhat more efficient, only update the variants where the 2.0-19 sort problem needs to be fixed.
                    $qMuts = mysql_query('SELECT DISTINCT `Variant/Exon`, `' . $sMutationCol . '` FROM ' . TABLEPREFIX . '_' . $sSymbol . '_variants WHERE LEFT(sort, 4) REGEXP "[^0-9]"');
                    while (list($sExon, $sMut) = mysql_fetch_row($qMuts)) {
                        $sSort = lovd_sort($sMut, $sExon);
                        $aUpdates['2.0-20'][] = 'UPDATE ' . TABLEPREFIX . '_' . $sSymbol . '_variants SET sort = "' . $sSort . '" WHERE `' . $sMutationCol . '` = "' . $sMut . '" AND `Variant/Exon` = "' . $sExon . '"';
                    }
                }
            }
            unset($_CURRDB);
        }

        // Also, provide Variant columns with default order.
        require ROOT_PATH . 'install/inc-sql-columns.php';
        $aColsOrder = array_keys($aColSQL);

        $qCols = mysql_query('SELECT colid FROM ' . TABLE_COLS . ' WHERE (hgvs = 1 OR standard = 1) AND colid LIKE "Variant/%"');
        while ($z = mysql_fetch_assoc($qCols)) {
            $sColOrder = array_search($z['colid'], $aColsOrder);
            $aUpdates['2.0-20'][] = 'UPDATE ' . TABLE_COLS . ' SET col_order = "' . $sColOrder . '" WHERE colid = "' . $z['colid'] . '"';
        }
    }

    // Addition for upgrade to LOVD v.2.0-23.
    if ($sCalcVersionDB < lovd_calculateVersion('2.0-23')) {
        // Gather available genes and update these.
        $aGenes = lovd_getGeneList();
        foreach ($aGenes as $sSymbol) {
            $aUpdates['2.0-23'][] = 'ALTER TABLE ' . TABLEPREFIX . '_' . $sSymbol . '_variants ADD COLUMN c_position_start MEDIUMINT AFTER variantid';
            $aUpdates['2.0-23'][] = 'ALTER TABLE ' . TABLEPREFIX . '_' . $sSymbol . '_variants ADD COLUMN c_position_start_intron MEDIUMINT AFTER c_position_start';
            $aUpdates['2.0-23'][] = 'ALTER TABLE ' . TABLEPREFIX . '_' . $sSymbol . '_variants ADD COLUMN c_position_end MEDIUMINT AFTER c_position_start_intron';
            $aUpdates['2.0-23'][] = 'ALTER TABLE ' . TABLEPREFIX . '_' . $sSymbol . '_variants ADD COLUMN c_position_end_intron MEDIUMINT AFTER c_position_end';
            $aUpdates['2.0-23'][] = 'ALTER TABLE ' . TABLEPREFIX . '_' . $sSymbol . '_variants ADD COLUMN g_position_start INT UNSIGNED AFTER c_position_end_intron';
            $aUpdates['2.0-23'][] = 'ALTER TABLE ' . TABLEPREFIX . '_' . $sSymbol . '_variants ADD COLUMN g_position_end INT UNSIGNED AFTER g_position_start';
            $aUpdates['2.0-23'][] = 'ALTER TABLE ' . TABLEPREFIX . '_' . $sSymbol . '_variants ADD COLUMN type VARCHAR(10) AFTER g_position_end';
            $aUpdates['2.0-23'][] = 'ALTER TABLE ' . TABLEPREFIX . '_' . $sSymbol . '_variants ADD INDEX (c_position_start, c_position_end)';
            $aUpdates['2.0-23'][] = 'ALTER TABLE ' . TABLEPREFIX . '_' . $sSymbol . '_variants ADD INDEX (c_position_start, c_position_start_intron, c_position_end, c_position_end_intron)';
            $aUpdates['2.0-23'][] = 'ALTER TABLE ' . TABLEPREFIX . '_' . $sSymbol . '_variants ADD INDEX (g_position_start, g_position_end)';
        }
    }

    // Addition for upgrade to LOVD v.2.0-26.
    if ($sCalcVersionDB < lovd_calculateVersion('2.0-26')) {
        // Gather available genes and update these.
        $aGenes = lovd_getGeneList();
        foreach ($aGenes as $sSymbol) {
            $aUpdates['2.0-23'][] = 'ALTER TABLE ' . TABLEPREFIX . '_' . $sSymbol . '_columns MODIFY COLUMN description_form TEXT NOT NULL';
            $aUpdates['2.0-23'][] = 'ALTER TABLE ' . TABLEPREFIX . '_' . $sSymbol . '_columns MODIFY COLUMN description_legend_short TEXT NOT NULL';
        }
    }










    // 2009-02-24; 2.0-16; Upgrade modules if necessary.
    // This will run $module->getUpgradeSQL() and $module->upgrade(), if available.
    $aSQLModules = $_MODULES->getUpgradeSQL();
    foreach ($aSQLModules as $sSQL) {
        $aUpdates[$_SETT['system']['version']][] = $sSQL;
    }

    // To make sure we upgrade the database correctly, we add the current version to the list...
    if (!isset($aUpdates[$_SETT['system']['version']])) {
        $aUpdates[$_SETT['system']['version']] = array();
    }

    print('Trying update lock... ');
    flush();

    // Try to update the upgrade lock.
    $sQ = 'UPDATE ' . TABLE_STATUS . ' SET lock_update = 1 WHERE lock_update = 0';
    $nMax = 30;
    for ($i = 0; $i < $nMax; $i ++) {
        @mysql_query($sQ);
        $bLocked = mysql_affected_rows();
        if ($bLocked) {
            break;
        }

        // No lock means that someone else is updating the system.
        if (!$i) {
            print('already locked!' . "\n" .
                  '  Update lock is in place, so someone else is already upgrading the database.' . "\n" .
                  '  Waiting for other user to finish...');
        } else {
            print('<GARBAGE>.</GARBAGE>');
        }
        flush();
        sleep(1);
    }

    if (!$bLocked) {
        // Other user is taking ages! Or somethings wrong...
        print("\n" .
              '  User still not finished. This may indicate something went wrong during upgrade.' . "\n");
        if (isset($_GET['force_lock'])) {
            print("\n" .
                  'Forcing upgrade as requested...' . "\n");
            $bLocked = true;
        }

    } else {
        print('OK' . "\n\n" .
              'Upgrading database backend...' . "\n");
    }
    flush();

    if ($bLocked) {
        // There we go...

        $nQueries = 0;
        $nFailed  = 0;
        foreach ($aUpdates as $sVersion => $aQ) {
            if (lovd_calculateVersion($sVersion) <= $sCalcVersionDB || lovd_calculateVersion($sVersion) > $sCalcVersionFiles) {
                continue;
            }
            print('  To ' . $sVersion . '...');

            $aQ[] = 'UPDATE ' . TABLE_STATUS . ' SET version = "' . $sVersion . '", date_updated = NOW()';

            // Loop needed queries...
            foreach ($aQ as $nQ => $sQ) {
                if (!$sQ) {
                    continue;
                }

                $nQ ++;
                if (!$nFailed) {
                    $q = mysql_query($sQ);
                    if (!$q) {
                        $nFailed ++;
                        $sError = mysql_error();
                        lovd_dbFout('UpgradeError', $sQ, $sError, false);
                        print('ERROR' . "\n\n" .
                              'Error while executing query ' . $nQ . ':' . "\n" .
                              $sError . "\n\n" .
                              'This implies these MySQL queries need to be executed manually:' . "\n" .
                              '<SPAN style="background : #C0C0C0">' . str_pad($nQ, strlen(count($aQ)), ' ', STR_PAD_LEFT) . '</SPAN> ' . $sQ . ';' . "\n");

                    } else {
                        $nQueries ++;
                        print('<GARBAGE>.</GARBAGE>');
                        flush();
                        usleep(1000);
                    }

                } else {
                    // Something went wrong, so we need to print out the remaining queries...
                    print('<SPAN style="background : #C0C0C0">' . str_pad($nQ, strlen(count($aQ)), ' ', STR_PAD_LEFT) . '</SPAN> ' . $sQ . "\n");
                }
            }

            if ($nFailed) {
                print("\n" . 'After executing these queries, please try again.' . "\n");
                break;
            } else {
                print('OK' . "\n");
            }
        }

        if (!$nFailed) {
            // Upgrade complete, all OK!
            lovd_writeLog('MySQL:Install', 'Upgrade', $_AUTH['username'] . ' (' . mysql_real_escape_string($_AUTH['name']) . ') successfully upgraded LOVD from ' . $_STAT['version'] . ' to ' . $_SETT['system']['version'] . ', executing ' . $nQueries . ' quer' . ($nQueries == 1? 'y' : 'ies'));
            print("\n" . 'Successfully upgraded to ' . $_SETT['system']['version'] . '!' . "\n" .
                  'Executed ' . $nQueries . ' database quer' . ($nQueries == 1? 'y' : 'ies') . '.' . "\n");
        }

        // Remove update lock.
        $sQ = 'UPDATE ' . TABLE_STATUS . ' SET lock_update = 0';
        $q = mysql_query($sQ);
        if (!$q) {
            print('Notice: Unable to remove upgrade lock!' . "\n");
            lovd_dbFout('UpdateLock', $sQ, mysql_error(), false);
        }
    }
    print('      </PRE>' . "\n");

    // Now that this is over, let the user proceed to whereever they were going!
    $sGet = (!$bLocked? 'force_lock' : '');

    // Don't double quote the data!
    lovd_magicUnquoteAll();

    // 2009-04-23; 2.0-18; Always include all GET variables!
    foreach ($_GET as $key => $val) {
        // 2009-09-24; 2.0-22; Variable can be an array!
        if (is_array($val)) {
            foreach ($val as $value) {
                $sGet .= ($sGet? '&amp;' : '') . $key . '[]=' . rawurlencode($value);
            }
        } elseif ($val || $key == 'sent') {
            $sGet .= ($sGet? '&amp;' : '') . $key . '=' . rawurlencode($val);
        }
    }

    // 2009-04-23; 2.0-18; Always send using post!
    print('      <FORM action="' . $_SERVER['PHP_SELF'] . ($sGet? '?' . $sGet : '') . '" method="post">' . "\n");
    foreach ($_POST as $key => $val) {
        // 2009-04-23; 2.0-18; Added htmlspecialchars to prevent XSS and allow values to include quotes.
        // 2009-09-24; 2.0-22; Variable can be an array!
        if (is_array($val)) {
            foreach ($val as $value) {
                print('        <INPUT type="hidden" name="' . $key . '[]" value="' . htmlspecialchars($value) . '">' . "\n");
            }
        } else {
            print('        <INPUT type="hidden" name="' . $key . '" value="' . htmlspecialchars($val) . '">' . "\n");
        }
    }

    print('        <INPUT type="submit" value="' . (!$bLocked? 'Force upgrade' : 'Proceed') . ' &gt;&gt;">' . "\n" .
          '      </FORM>' . "\n");

    include ROOT_PATH . 'inc-bot.php';
    exit;
}
?>
