Location: PHPKode > projects > KORA > kora-2.0.0/upgradeDatabase.php
<?php
/*
Copyright (2008) Matrix: Michigan State University

This file is part of KORA.

KORA 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.

KORA 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 this program.  If not, see <http://www.gnu.org/licenses/>.
 */

// Initial Version: Brian Beck, 2009

// Note: This explicitly doesn't do any requireLogin or requireSystemAdmin checks
// in case a database change/codebase change breaks stuff so heavily that users can't
// log in.

require_once('includes/utilities.php');

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" >

    <head>
        <title>KORA</title>
		<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
        <meta http-equiv="Pragma" content="no-cache" />
        <link href="<?php echo baseURI;?>css/all.css" rel="stylesheet" type="text/css" />
        <?php // Add style switcher code here instead of always using default
	if (isset($_SESSION['currentProjectStyleSheet']))
	{
		?><link href="<?php echo baseURI;?>css/<?php echo $_SESSION['currentProjectStyleSheet']?>" rel="stylesheet" type="text/css" /><?php 
	}
	else
	{
        ?><link href="<?php echo baseURI;?>css/default.css" rel="stylesheet" type="text/css" /><?php 
    } ?>
    </head>
    <body>
		<div id="container_main">
		<div id="container">
		<div id="header">
        <div id="login">
        
        <?php if (!isLoggedIn()) { ?>
            
            <a href="<?php echo baseURI;?>index.php"><?php echo gettext('Log In');?></a> |
            <a href="<?php echo baseURI;?>register.php"><?php echo gettext('Register');?></a> |
            <a href="<?php echo baseURI;?>activate.php"><?php echo gettext('Activate Account');?></a>
        <?php } else { ?>
        	<a href="<?php echo baseURI;?>logout.php"><?php echo gettext('Log Out');?></a> |
            <a href="<?php echo baseURI;?>accountSettings.php"><?php echo gettext('Update User Info');?></a>

        <?php } ?>

        </div>
        </div>
		<div id="content_container">
		
<?php include('includes/menu.php'); ?>
<div id="right_container"><div id="right">

        <h2><?php echo gettext('KORA Upgrade Utility');?></h2>
        <?php echo gettext('This script will ensure that your KORA database is up-to-date.  If an upgrade fails, please attempt it a second time before reporting the issue.')?><br /><br />
<?php 

//////////////////////////////////////
// We must check for the existence of the systemInfo table
// and a row in it corresponding to the system version.  It
// creates them if they don't exist.  If they don't exist and
// can't be created, the updater dies since it cannot proceed.
// Other checks don't have to die, but these two are absolutely
// critical.
//////////////////////////////////////

// Check for the existence of the system info table
echo gettext('Checking for existence of System Information database table').'.....';
$sysTableQuery = $db->query("SHOW TABLES LIKE 'systemInfo'");
if ($sysTableQuery->num_rows < 1)
{
    // The table doesn't exist; create it.
    $result = $db->query('CREATE TABLE systemInfo (
  version VARCHAR(64) NOT NULL,
  baseURL VARCHAR(255) NOT NULL UNIQUE) CHARACTER SET utf8 COLLATE utf8_general_ci');
    if ($result)
    {
        echo '<strong>'.gettext('Created').'</strong><br />';
    }
    else
    {
        echo '<strong><font color="#ff0000">'.gettext('Failed').'</font></strong><br /><br />';
        die(gettext('Unable to create systemInfo table').'.  '.gettext('Please check your database configuration.  Unable to proceed with update process.'));
    }
}
else
{
    echo '<strong>'.gettext('Found').'</strong><br />';
}
echo gettext('Checking for existence of system information for this install').'.....';
$sysInfoQuery = $db->query('SELECT version FROM systemInfo WHERE baseURL='.escape(baseURI).' LIMIT 1');
if ($sysInfoQuery->num_rows < 1)
{
    // The row doesn't exist; create it
    $result = $db->query('INSERT INTO systemInfo (baseURL, version) VALUES ('.escape(baseURI).', \'0\')');
    if ($result)
    {
        echo '<strong>'.gettext('Created').'</strong><br />';
        // This should be an associative array containing all the values pulled in the
        // SELECT query above, corresponding to whatever they're created to here.
        $systemInfo = array('version' => '0');
    }
    else
    {
        echo '<strong><font color="#ff0000">'.gettext('Failed').'</font></strong><br /><br />';
        die(gettext('Unable to create systemInfo data').'.  '.gettext('Please check your database configuration.  Unable to proceed with update process.'));
    }
}
else
{
    echo '<strong>'.gettext('Found').'</strong><br />';
    $systemInfo = $sysInfoQuery->fetch_assoc();
}
echo '<br />';

//////////////////////////////////////
// Now we have the System (and thus version) information.
// We can compare the version number against a number of
// benchmarks to see what checks need to be run.  Always
// use PHP's version_compare command.  As a side rule, all
// version number increases must correspond to syntax
// acceptable to version_compare.
//////////////////////////////////////

// General idea: If a new version requires a database shift, add a section to the
// bottom.  Do NOT delete previously existing tests.  NEVER reset the value of
// allTestsPassed to true.  ONLY update the version number in the database if
// allTestsPassed is true.  If ANY test fails, set allTestsPassed to false.  If
// you don't, the version number could be updated and the test might never be run
// again, leading to a permanent error.

$allTestsPassed = true;     // Set this to false if a test fails; NEVER set back to true
$anyUpdatesDone = false;
$messages = array();      // Used to store any messages to be displayed at the end

if (version_compare($systemInfo['version'], '1.0.0', '<'))
{
    $anyUpdatesDone = true;
    
    echo gettext('Found version number less than 1.0.0.  Running beta->production upgrades.').'<br />';
    
    // Get the list of tables.  This will let us see if several tables exist/have the correct
    // name.
    $tableQuery = $db->query('SHOW TABLES');
    $fixityExists = false;
    $recordPresetExists = false;
    $controlsExists = false;
    while($t = $tableQuery->fetch_row())
    {
        if ($t[0] == 'fixity')
        {
            $fixityExists = true;
        }
        else if ($t[0] == 'recordPreset')
        {
            $recordPresetExists = true;
        }
        else if ($t[0] == 'controls')
        {
            $controlsExists = true;
        }
    }
        
    // Check for existence of fixity table
    if (!$fixityExists)
    {
        echo gettext('Attempting to create fixity table').'.....<strong>';
        $result = $db->query('CREATE TABLE fixity(
                       kid VARCHAR(20) NOT NULL, 
                       cid INTEGER UNSIGNED NOT NULL, 
                       path VARCHAR(512),
                       initialHash VARCHAR('.HASH_HEX_SIZE.') NOT NULL,
                       initialTime DATETIME NOT NULL,
                       computedHash VARCHAR('.HASH_HEX_SIZE.'),
                       computedTime DATETIME NOT NULL,
                       PRIMARY KEY(kid,cid)) CHARACTER SET utf8 COLLATE utf8_general_ci');
        if ($result)
        {
            echo gettext('Successful');
        }
        else
        {
            echo '<font color="#ff0000">'.gettext('Failed').'</font>';
            $allTestsPassed = false;
        }
            
        echo '</strong><br />';             
    }
        
    // Check for existence of recordPreset
    if (!$recordPresetExists)
    {
       echo gettext('Attempting to create recordPreset table').'.....<strong>';
       $result = $db->query('CREATE TABLE recordPreset(
                           recpresetid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
                           schemeid INTEGER UNSIGNED NOT NULL,
                           name VARCHAR(255),
                           kid VARCHAR(30),
                           PRIMARY KEY(recpresetid)) CHARACTER SET utf8 COLLATE utf8_general_ci');
        if ($result)
        {
            echo gettext('Successful');
        }
        else
        {
            echo '<font color="#ff0000">'.gettext('Failed').'</font>';
            $allTestsPassed = false;
        }
           
        echo '</strong><br />';            
    }
        
    // Check for correct name of control table
    if ($controlsExists)
    {
        echo gettext('Attempting to rename controls table to control').'.....<strong>';
        $result = $db->query('RENAME TABLE controls TO control');
        if ($result)
        {
            echo gettext('Successful');
        }
        else
        {
            echo '<font color="#ff0000">'.gettext('Failed').'</font>';
            $allTestsPassed = false;
        }
        
        echo '</strong><br />';
    }
        
    // Check for allowPasswordReset, searchAccount columns in user table
    $allowPasswordResetExists = false;
    $searchAccountExists = false;
    $columnQuery = $db->query('SHOW COLUMNS FROM user');
    while ($c = $columnQuery->fetch_assoc())
    {
        if ($c['Field'] == 'allowPasswordReset')
        {
            $allowPasswordResetExists = true;
        }
        else if ($c['Field'] == 'searchAccount')
        {
            $searchAccountExists = true;
        }
    }
    if (!$allowPasswordResetExists)
    {
        echo gettext('Expanding user table (Part 1/2)').'.....<strong>';
        $result = $db->query('ALTER TABLE user ADD COLUMN allowPasswordReset TINYINT(1) UNSIGNED NOT NULL AFTER searchAccount');
        if ($result)
        {
            echo gettext('Successful');
        }
        else
        {
            echo '<font color="#ff0000">'.gettext('Failed').'</font>';
            $allTestsPassed = false;
        }
        
        echo '</strong><br />';        
    }
    if (!$searchAccountExists)
    {
        echo gettext('Expanding user table (Part 2/2)').'.....<strong>';
        $result = $db->query('ALTER TABLE user ADD COLUMN resetToken VARCHAR(16) AFTER allowPasswordReset');
        if ($result)
        {
            echo gettext('Successful');
        }
        else
        {
            echo '<font color="#ff0000">'.gettext('Failed').'</font>';
            $allTestsPassed = false;
        }
        
        echo '</strong><br />';         
    }
        
    // Check for dublinCoreOutOfDate column in scheme table
    $columnQuery = $db->query('SHOW COLUMNS FROM scheme WHERE Field=\'dublinCoreOutOfDate\'');
    if ($columnQuery->num_rows == 0)
    {
        echo gettext('Expanding scheme table').'.....<strong>';
        $result = $db->query('ALTER TABLE scheme ADD COLUMN dublinCoreOutOfDate TINYINT(1) UNSIGNED NOT NULL AFTER dublinCoreFields');
        if ($result)
        {
            echo gettext('Successful');
        }
        else
        {
            echo '<font color="#ff0000">'.gettext('Failed').'</font>';
            $allTestsPassed = false;
        }
        
        echo '</strong><br />';         
    }
        
    // Check for xmlPacked column in control table
    $columnQuery = $db->query('SHOW COLUMNS FROM control WHERE Field=\'xmlPacked\'');
    if ($columnQuery->num_rows == 0)
    {
        echo gettext('Expanding control table').'.....<strong>';
        $result = $db->query('ALTER TABLE control ADD COLUMN xmlPacked TINYINT(1) NOT NULL AFTER class');
        if ($result)
        {
            $messages[] = gettext('The controls table has been updated; please run "Update Control List" in the System Management console.');
            echo gettext('Successful');
        }
        else
        {
            echo '<font color="#ff0000">'.gettext('Failed').'</font>';
            $allTestsPassed = false;
        }
        
        echo '</strong><br />';         
    }
    
    // Check for presetid column in controlPreset table
    $columnQuery = $db->query('SHOW COLUMNS FROM controlPreset WHERE Field=\'presetid\'');
    if ($columnQuery->num_rows == 0)
    {
        echo gettext('Expanding controlPreset table').'.....<strong>';
        $result = $db->query('ALTER TABLE controlPreset ADD COLUMN presetid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT BEFORE name, DROP PRIMARY KEY, ADD PRIMARY KEY (presetid)');
        if ($result)
        {
            echo gettext('Successful');
        }
        else
        {
            echo '<font color="#ff0000">'.gettext('Failed').'</font>';
            $allTestsPassed = false;
        }
        
        echo '</strong><br />'; 
    }
    
    // Update Stock Presets
    $presetQuery = $db->query('SELECT name FROM controlPreset WHERE name="Floating Point" and class="Text Control" LIMIT 1');
    if ($presetQuery->num_rows > 0)
    {
        // Update the stock presets
        echo gettext('Renaming stock presets').'.....<strong>';
        $result = $db->query('UPDATE controlPreset SET name="Remove Preset" WHERE name IN ("Empty (No RegEx)", "Empty (No Types)") AND class IN ("FileControl", "ListControl", "TextControl")');
        $result = $db->query('UPDATE controlPreset SET name="Decimal Number" WHERE name="Floating Point" AND class="TextControl" LIMIT 1');
           if ($result)
        {
            echo gettext('Successful');
        }
        else
        {
            echo '<font color="#ff0000">'.gettext('Failed').'</font>';
            $allTestsPassed = false;
        }
        
        echo '</strong><br />'; 
    }
    
    // Update the database to reflect that it's at 1.0.0.
    if ($allTestsPassed)
    {
        $result = $db->query("UPDATE systemInfo SET version='1.0.0' WHERE baseURL=".escape(baseURI).' LIMIT 1');
        if (isset($_SESSION['dbVersion']))
        {
            $_SESSION['dbVersion'] = '1.0.0';
        }        
    }
    
    echo '<br /><br />';
}

if (version_compare($systemInfo['version'], '1.0.2', '<'))
{
    // Run the 1.0.2 List Control Checks
    echo gettext('Trimming List Control Options').'......';
    $projectQuery = $db->query('SELECT DISTINCT pid FROM project');
    $allListOptionsGood = true;
    while ($p = $projectQuery->fetch_assoc())
    {
        $optionsQuery = $db->query('SELECT cid, type, options FROM p'.$p['pid']."Control WHERE type IN ('ListControl', 'MultiListControl')");
        while ($o = $optionsQuery->fetch_assoc())
        {
            $xml = simplexml_load_string($o['options']);
            
            $trimmedOptions = array();
            // Build a list of the options that need to be trimmed
            foreach($xml->option as $option)
            {
                if (trim((string)$option) != (string)$option)
                {
                    $trimmedOptions[] = (string)$option;
                }
            }
            
            if (!empty($trimmedOptions))
            {
                // Update the options list
                $newXML = simplexml_load_string('<options />');
                foreach($xml->option as $option)
                {
                    $newXML->addChild('option', trim((string)$option));                    
                }
                $updateOptions = $db->query('UPDATE p'.$p['pid'].'Control SET options='.escape($newXML->asXML()).' WHERE cid='.$o['cid'].' LIMIT 1');
                if ($updateOptions)
                {
                    $allListOptionsGood = false;
                }
                
                // Update any data records containing the records that need to be trimmed
                if ($o['type'] == 'ListControl')
                {
                    // Escape the Strings
                    $escapedStrings = array();
                    foreach($trimmedOptions as $t)
                    {
                        $escapedStrings[] = escape($t);
                    }
                    
                    $query = 'SELECT id, cid, value FROM p'.$p['pid'].'Data WHERE cid='.$o['cid'].' AND VALUE IN (';
                    $query .= implode(',', $escapedStrings);
                    $query .= ')';
                    $query = $db->query($query);
                    if (!$query)
                    {
                        $allListOptionsGood = false;
                    }
                    else
                    {
                        while ($row = $query->fetch_assoc())
                        {
                            $updateQuery = $db->query('UPDATE p'.$p['pid'].'Data SET value='.escape(trim($row['value'])).' WHERE id='.escape($row['id']).' AND cid='.$row['cid'].' LIMIT 1');
                            if ($updateQuery)
                            {
                                $allListOptionsGood = false;
                            }
                        }
                    }
                }
                else // if $o['type'] == 'MultiListControl'
                {
                    // Escape the Strings
                    $escapedStrings = array();
                    foreach($trimmedOptions as $t)
                    {
                        $escapedStrings[] = escape('%>'.$t.'<%');
                    }

                    // Build the Query
                    $addOr = false;
                    $query = 'SELECT id, cid, value FROM p'.$p['pid'].'Data WHERE cid='.$o['cid'].' AND ';
                    foreach($escapedStrings as $s)
                    {
                        if ($addOr)
                        {
                            $query .= ' OR ';
                        }
                        $query .= " value LIKE $s "; 
                        
                        $addOr = true;
                    }
                    $query = $db->query($query);
                    if (!$query)
                    {
                        $allListOptionsGood = false;
                    }
                    else
                    {
                        while ($row = $query->fetch_assoc())
                        {
                            $xml = simplexml_load_string($row['value']);
                            $newXML = simplexml_load_string('<multilist />');
                            foreach($xml->value as $value)
                            {
                                $newXML->addChild('value', trim((string)$value));
                            }
                            $updateQ = 'UPDATE p'.$p['pid'].'Data SET value='.escape($newXML->asXML()).' WHERE id='.escape($row['id']).' AND cid='.$row['cid'].' LIMIT 1';
                            $updateQuery = $db->query($updateQ);
                            if (!$updateQuery)
                            {
                                $allListOptionsGood = false;
                                echo gettext('Failure');                                
                            }
                        }
                    }
                }
            }
        }
    }
    
    if ($allListOptionsGood)
    {
        echo gettext('Successful');
    }
    else
    {
        echo '<font color="#ff0000">'.gettext('Failed').'</font>';
        $allTestsPassed = false;
    }
    
    // Update the database to reflect that it's at 1.0.2.
    if ($allTestsPassed)
    {
        $result = $db->query("UPDATE systemInfo SET version='1.0.2' WHERE baseURL=".escape(baseURI).' LIMIT 1');
        if (isset($_SESSION['dbVersion']))
        {
            $_SESSION['dbVersion'] = '1.0.2';
        }
    }
    
    echo '<br /><br />';    
}

if (version_compare($systemInfo['version'], '1.1.0', '<'))
{
    // Run the 1.1.0 checks
    
    // Check the date controls for the <display> option.
    if (true)
    {
        // This is block exists solely so that the indentation matches all
        // the other tests; at this point I can't think of a good way to check
        // if this has been done or not (checking a single control or even a sample
        // could fail if the user creates new controls before running this script)
        // so we'll just have to do it each time until all the tests succeed and
        // the DB version is bumped to 1.1.0

        echo gettext('Adding display option to Date and Multi-Date Controls').'......';
        $projectQuery = $db->query('SELECT pid FROM project');
        $allDateOptionsGood = true;
        while ($p = $projectQuery->fetch_assoc())
        {
            $optionsQuery = $db->query('SELECT cid, options FROM p'.$p['pid']."Control WHERE type IN ('DateControl', 'MultiDateControl')");
            while ($o = $optionsQuery->fetch_assoc())
            {
                $dateOptionChanged = false;
                $xml = simplexml_load_string($o['options']);
                if (!isset($xml->displayFormat))
                {
                    $xml->addChild('displayFormat', 'MDY');
                    $dateOptionChanged = true;
                }
                else if (!in_array( (string) $xml->displayFormat, array('MDY', 'DMY', 'YMD')))
                {
                    $xml->displayFormat = 'MDY';
                    $dateOptionChanged = true;
                }
                
                // Update the options if they changed
                if ($dateOptionChanged)
                {
                    $result = $db->query('UPDATE p'.$p['pid'].'Control SET options='.escape($xml->asXML()).' WHERE cid='.$o['cid'].' LIMIT 1');
                    if (!$result)
                    {
                        $allDateOptionsGood = false;
                    }
                }
            }
        }
        
        if ($allDateOptionsGood)
        {

            echo gettext('Successful');
        }
        else
        {
            echo '<font color="#ff0000">'.gettext('Failed').'</font>';
            $allTestsPassed = false;
        }        
    }
    
    // Once again, it's impossible to easily test if all projects have the
    // necessary column so we have to run this test every time until the DB
    // successfully gets to 1.1.0 or higher.
    if (true)
    {
        echo '<br />'.gettext('Adding Advanced Search column to Project tables').'........';
        $allAdvSearchGood = true;
        $pidQuery = $db->query('SELECT pid FROM project');
        while($row = $pidQuery->fetch_assoc())
        {
            // See if the column exists
            $test = $db->query('SHOW COLUMNS FROM p'.$row['pid'].'Control WHERE Field=\'advSearchable\'');
            if ($test->num_rows == 0)
            {
                $result = $db->query('ALTER TABLE p'.$row['pid'].'Control ADD COLUMN advSearchable TINYINT(1) UNSIGNED NOT NULL AFTER searchable');
                if (!$result)
                {
                    $allAdvSearchGood = false;
                }
            }
        }
        
        if ($allAdvSearchGood)
        {
            echo gettext('Successful');
        }
        else
        {
            echo '<font color="#ff0000">'.gettext('Failed').'</font>';
            $allTestsPassed = false;
        }        
    }
    
    $test = $db->query('SHOW COLUMNS FROM scheme WHERE Field=\'allowPreset\'');
    if ($test->num_rows == 0)
    {
        echo '<br />'.gettext('Adding Scheme Preset column to Scheme table').'........';
        $result = $db->query('ALTER TABLE scheme ADD COLUMN allowPreset TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 AFTER description');
        if ($result)
        {
            echo gettext('Successful');
        }
        else
        {
            echo '<font color="#ff0000">'.gettext('Failed').'</font>';
            $allTestsPassed = false;            
        }
    }
    
    // Update the "US States" Preset
    if (true)
    {
        echo '<br />'.gettext('Correcting Washington DC in US States Preset').'.........';
        $result = $db->query('UPDATE controlPreset SET value=\'<options><option>Alabama</option><option>Alaska</option><option>Arizona</option><option>Arkansas</option><option>California</option><option>Colorado</option><option>Connecticut</option><option>Delaware</option><option>District of Columbia</option><option>Florida</option><option>Georgia</option><option>Hawaii</option><option>Idaho</option><option>Illinois</option><option>Indiana</option><option>Iowa</option><option>Kansas</option><option>Kentucky</option><option>Louisiana</option><option>Maine</option><option>Maryland</option><option>Massachusetts</option><option>Michigan</option><option>Minnesota</option><option>Mississippi</option><option>Missouri</option><option>Montana</option><option>Nebraska</option><option>Nevada</option><option>New Hampshire</option><option>New Jersey</option><option>New Mexico</option><option>New York</option><option>North Carolina</option><option>North Dakota</option><option>Ohio</option><option>Oklahoma</option><option>Oregon</option><option>Pennsylvania</option><option>Rhode Island</option><option>South Carolina</option><option>South Dakota</option><option>Tennessee</option><option>Texas</option><option>Utah</option><option>Vermont</option><option>Virginia</option><option>Washington</option><option>West Virginia</option><option>Wisconsin</option><option>Wyoming</option></options>\' WHERE name=\'US States\' AND class=\'ListControl\' AND project=0 LIMIT 1');
        if ($result)
        {
            echo gettext('Successful');
        }
        else
        {
            echo '<font color="#ff0000">'.gettext('Failed').'</font>';
            $allTestsPassed = false;            
        }        
    }
    
    // Update the 'URL or URI' Preset
    $test = $db->query("SELECT presetid FROM controlPreset WHERE name='URL or URI' AND class='TextControl' AND project=0 AND value='/^(http|ftp|https):///' LIMIT 1");
    if ($test->num_rows == 1)
    {
        $test = $test->fetch_assoc();
        echo '<br />'.gettext('Correcting URL/URI Preset').'..........';
        $result = $db->query("UPDATE controlPreset SET value='/^(http|ftp|https):".'\\\\'.'/'.'\\\\'."//' WHERE presetid=".$test['presetid'].' LIMIT 1');
        if ($result)
        {
            echo gettext('Successful');
        }
        else
        {
            echo '<font color="#ff0000">'.gettext('Failed').'</font>';
            $allTestsPassed = false;            
        }        
    }
    
    // Update the database to reflect that it's at 1.1.0.
    if ($allTestsPassed)
    {
        $result = $db->query("UPDATE systemInfo SET version='1.1.0' WHERE baseURL=".escape(baseURI).' LIMIT 1');
        if (isset($_SESSION['dbVersion']))
        {
            $_SESSION['dbVersion'] = '1.1.0';
        }        
    }    
}

if (version_compare($systemInfo['version'], '2.0.0-beta', '<'))
{
    // Update Image Controls
    if (true)
    {
        echo '<br />'.gettext('Updating Allowed Image Formats').'..........';
        // get all projects
        $allUpdated = true;
        $projectQuery = $db->query('SELECT pid FROM project');
        $result = true;
        while ($p = $projectQuery->fetch_assoc())
        {
            $controlQuery = $db->query('SELECT cid,options FROM p'.$p['pid'].'Control WHERE type=\'ImageControl\'');
            while ($c = $controlQuery->fetch_assoc())
            {
                $xml = simplexml_load_string($c['options']);
                $hasxpng = false;
                $haspjpeg = false;
                foreach($xml->allowedMIME->mime as $m)
                {
                    if ( (string)$m == 'image/pjpeg' ) $haspjpeg = true;
                    if ( (string)$m == 'image/x-png' ) $hasxpng = true;
                }
                if (!$hasxpng)
                { 
                    $xml->allowedMIME->addChild('mime', 'image/x-png');
                }
                if (!$haspjpeg)
                {
                    $xml->allowedMIME->addChild('mime', 'image/pjpeg');
                }
                if ( (!$hasxpng) || (!$haspjpeg) )
                {
                    // fix it in the database
                    $result = $db->query('UPDATE p'.$p['pid'].'Control SET options='.escape($xml->asXML()).' WHERE cid='.$c['cid'].' LIMIT 1');
                    if (!$result)
                    {
                        $allUpdated = false;
                    }
                }
            }
        }
        if ($allUpdated)
        {
            echo gettext('Successful');
        }
        else
        {
            echo '<font color="#ff0000">'.gettext('Failed').'</font>';
            $allTestsPassed = false;            
        }        
    }

    // Update Reverse Associations
    $badReverseAssocPresent = false;
    // Look for old-style Reverse Associator Fields
    $projectQuery = $db->query('SELECT pid FROM project');
    while ($p = $projectQuery->fetch_assoc())
    {
        // check for a reverse associator without a <assoc> block
        $assocQuery = $db->query('SELECT id FROM p'.$p['pid'].'Data WHERE cid=0 AND VALUE NOT LIKE \'%<assoc>%\' LIMIT 1');
        if ($assocQuery->num_rows > 0)
        {
            // if found, delete all reverse associators in this table and tell the system to rebuild the associators
            $db->query('DELETE FROM p'.$p['pid'].'Data WHERE cid=0');
            $badReverseAssocPresent = true;
        }
    }
    if ($badReverseAssocPresent)
    {
        echo '<br />'.gettext('Updating Reverse Associations').'..........';
        // get all projects
        $allUpdated = true;
        $projectQuery = $db->query('SELECT pid FROM project');
        $result = true;
        while ($p = $projectQuery->fetch_assoc())
        {
            $dataQuery = $db->query('SELECT id, cid, value FROM p'.$p['pid'].'Data WHERE cid IN (SELECT cid FROM p'.$p['pid'].'Control WHERE type=\'AssociatorControl\')');
            while ($d = $dataQuery->fetch_assoc())
            {
                // Rebuild the Reverse Associations
                $xml = simplexml_load_string($d['value']);
                if (isset($xml->kid))
                {
                    foreach($xml->kid as $kid)
                    {
                        addReverseAssociation( (string)$kid, $d['id'], $d['cid'] );
                    }
                }
            }
        }
        if ($allUpdated)
        {
            echo gettext('Successful');
        }
        else
        {
            echo '<font color="#ff0000">'.gettext('Failed').'</font>';
            $allTestsPassed = false;            
        } 
    }
    
    //Add timestamp control to all schemes
	//First get a list of all schemes in all projects
	$allHaveTimestamps = true;
	$anyTimestampsAdded = false;
	$sQuery = "SELECT schemeid, pid FROM scheme";
	$schemes = $db->query($sQuery);
	if(is_object($schemes) && $schemes->num_rows > 0)
	$pids = array();
	while($scheme = $schemes->fetch_assoc()){
		if(array_search($scheme['pid'], $pids) !==FALSE){
			$pids[$scheme['pid']] = array();
			$pids[$scheme['pid']][$scheme['schemeid']] = false;
		}
		else{
			$pids[$scheme['pid']][$scheme['schemeid']] = false;
		} 
	}
	//Get the date to add to all records for newly created timestamp controls
	$now = date("r");
	
	//On a fresh install, or before any projects are added, $pids will be empty
	if(!empty($pids)){
	
	//Go through projects/schemes discovered, add timestamp control if not present
	foreach($pids as $pid=>$schms){
		$_SESSION['currentProject'] = $pid;
		foreach($schms as $sid=>$hasTimestamp){
			//Set to make addControl.php function
			$_SESSION['currentScheme'] = $sid;
			
			$cQuery = "SELECT name,type FROM p".$pid."Control WHERE schemeid='$sid'";
			$result = $db->query($cQuery);
			//If a project doesn't have a control table, this kora install is damage
			if(!is_object($result)){
				die(gettext('Control table is missing from project').' '.$pid.' '.gettext('or').' '.$db->error);
			}
			//If there is at least 1 control for this scheme, loop through controls checking if timestamp control already exists
			if($result->num_rows > 0){
				while ($control = $result->fetch_assoc()){			
					if($control['name'] == 'systimestamp'){
						if($control['type'] == 'TextControl')
							$hasTimestamp = true;
						else{ //If a control named 'systimestamp' is found that is not a text control, timestamp control can't be added
							$hasTimestamp = true;
							$messages[] = 'Schemeid'.' '.$sid.' '.'has a control named systimestamp of type'.' '.$control['type'].' - '."timestamp can't be added".'.<br />';
							$allHaveTimestamps = false;
							$allTestsPassed = false;
						}
					}				
				}
			}
			//If timestamp control was not found, add it
			if(!$hasTimestamp){
				$anyTimestampsAdded = true;
				$tempReq = $_REQUEST;
				$_REQUEST['pid'] = $pid;
				$_REQUEST['sid'] = $sid;
       			$_REQUEST['name'] = 'systimestamp';
       			$_REQUEST['type'] = 'TextControl';
       			$_REQUEST['description'] = ''; //Could add a real description here
       			$_REQUEST['submit'] = true;
       			$_REQUEST['collectionid'] = 0;
       			$_REQUEST['publicentry'] = "on"; //Not used
       			require('addControl.php');
       			$_REQUEST = $tempReq;
       			
       			//Now that timestamp control is added, add the current time as the timestamp for all existing records inthis scheme
       			//First get the cid for the newly created control
       			$cidQuery = "SELECT cid FROM p".$pid."Control WHERE name='systimestamp' AND schemeid='$sid' LIMIT 1";
       			$cid = $db->query($cidQuery);
 				if(!is_object($cid) || $cid->num_rows != 1){
 					//Since we just created this control, if it doesn't exist in the control table, that's bad
 					$allHaveTimestamps = false;
 					$messages[] = gettext('Timestamp control creation failed for scheme').": $sid, $db->error.";
 				}
 				else{
       				$cid = $cid->fetch_assoc();
       				$cid = $cid['cid'];
       				//Get all of the kids for scheme so that timestamp data can be added
       				$dQuery = "SELECT DISTINCT id FROM p".$pid."Data WHERE schemeid='$sid' ORDER BY id ASC";
       				$kids = $db->query($dQuery);
       				if(!is_object($kids) || $kids->num_rows < 1){
       					if($db->error){
       						$messages[] = $db->error;
       						$allHaveTimestamps = false;
       					}
       					//else no records for this scheme, so no timestamps to add      				
       				}
       				else{
       					//Add the timestamp value to the data table
       					while($record = $kids->fetch_assoc()){
       						$db->query("INSERT INTO p".$pid."Data (id, cid, schemeid, value) VALUES ('$record[id]','$cid','$sid','$now')");
       						if($db->error){
       							$messages[] = $db->error;
       							$allHaveTimestamps = false;
       						}
       					}
       				}  			
				}
			}
			unset($_SESSION['currentScheme']);
		}
		unset($_SESSION['currentProject']);
	}
	if($anyTimestampsAdded){
		$anyUpdatesDone = true;
		echo "<br />".gettext('Adding timestamps').' .........';
		if ($allHaveTimestamps){
			echo "<strong>".gettext('Successful')."</strong><br />";
		}
		else{
			$allTestsPassed = false;
			echo '<font color="#ff0000">'.gettext('Failed').'</font>';
		}
	}}
	
	//Add a timestamp column to the dublin core table
	//Note that this is not a regular dublin core field, and will not be added to
	//a user-selectable list of dublin core fields
	$query="SHOW COLUMNS FROM dublinCore LIKE 'timestamp'";
	$result = $db->query($query);
	if($result->num_rows < 1){
		$anyUpdatesDone = true;
		echo "<br />Adding timestamp column to Dublin Core table ....... ";
		$addquery = "ALTER TABLE dublinCore ADD COLUMN timestamp VARCHAR(1000)";
		$db->query($addquery);
		if($db->error != ''){
			$allTestsPassed = false;
			echo '<font color="#ff0000">Failed</font><br />'.$db->error.'<br />';
		}
		else{
			echo "<strong>Successful</strong><br />";
		}
	}
	

    // Update user table
    if(true)
    {
    	echo '<br />'.gettext('Updating user Table').'..........';
    	// check for language column in user
    	$allUpdated = true;
    	$userQuery = $db->query('SELECT * FROM user');
    	if(!array_key_exists('language', $userQuery->fetch_assoc()))
    	{	
    		// add the language column in user
    		$alterUser = $db->query("ALTER TABLE user ADD language varchar(6) default 'en_US' AFTER organization");
    		if(!$alterUser) $allUpdated = false;
    	}
    	if ($allUpdated)
    	{
    		echo gettext('Successful');
    	}
    	else
   	 	{
    		echo '<font color="#ff0000">'.gettext('Failed').'</font>';
    		$allTestsPassed = false;
    	}
    }
    
    
    
    // DO NOT UNCOMMENT THIS BLOCK UNTIL ALL CHANGES FOR VERSION 1.2.0 ARE FINISHED!
    
    // Update the database to reflect that it's at 2.0.0-beta
    if ($allTestsPassed)
    {
        $result = $db->query("UPDATE systemInfo SET version='2.0.0-beta' WHERE baseURL=".escape(baseURI).' LIMIT 1');
        if (isset($_SESSION['dbVersion']))
        {
            $_SESSION['dbVersion'] = '2.0.0-beta';
        }        
    } 
}
if(version_compare($systemInfo['version'], '2.0.0', '<'))
{
	//No database changes for 2.0.0 over the beta - just change version
    if ($allTestsPassed)
    {
        $result = $db->query("UPDATE systemInfo SET version='2.0.0' WHERE baseURL=".escape(baseURI).' LIMIT 1');
        if (isset($_SESSION['dbVersion']))
        {
            $_SESSION['dbVersion'] = '2.0.0';
        }        
    }
    //Mark anyUpdatesDone as true - a change was made, though minor
    $anyUpdatesDone = true;
}

if (!$anyUpdatesDone)
{
    echo '<h3>'.gettext('No updates found; your database is up-to-date.').'</h3>';
}

if (!empty($messages))
{
    echo '<ul>';
    foreach($messages as $m)
    {
        echo "<li><strong>".gettext($m)."</strong></li>";
    }
    echo '</ul>';
}

?>
</div>
</div> <!-- right -->
</div> <!-- right_container -->
<div id="bottom"></div>
<div id="footer"><?php echo gettext('Created by');?> <a href="http://www.matrix.msu.edu">MATRIX</a>
<br /><?php echo gettext('Version');?>: <?php echo KORA_VERSION?>  
</div>
</div>
</div>
</body></html>	
Return current item: KORA