Location: PHPKode > projects > WebCollab > webcollab-3.10/update.php
<?php
/*
  $Id: update.php 2328 2009-09-27 08:31:56Z andrewsimpson $

  (c) 2004 - 2011 Andrew Simpson <andrew.simpson at paradise.net.nz>

  WebCollab
  ---------------------------------------

  This program 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.

  This program 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, write to the Free Software Foundation, Inc., 675 Mass Ave,
  Cambridge, MA 02139, USA.

   Function:
  ---------

  Updating older databases to the current schema

*/

require_once('path.php' );
require_once(BASE.'path_config.php' );
require_once(BASE_CONFIG.'config.php' );

include_once(BASE.'lang/lang.php' );
include_once(BASE.'includes/common.php' );
include_once(BASE.'includes/screen.php' );

//
// ERROR FUNCTION
//

function secure_error($message ) {

  create_top('Error', 1 );
  new_box('Error', "<div style=\"text-align:center\">".$message."</div>", 'boxdata-small', 'head-small' );
  create_bottom();
  die;

}

//
// LOGIN CHECK
//

//valid login attempt ?
if( (isset($_POST['username']) && isset($_POST['password']) ) ) {

  include_once(BASE.'includes/common.php' );
  include_once(BASE.'database/database.php' );

  //set variables
  $q = '';
  $content = '';
  $flag_attempt = false;
  $username = safe_data($_POST['username']);
  //encrypt password
  $md5pass = md5($_POST['password'] );

  //no ip (possible?)
  if( ! ($ip = $_SERVER['REMOTE_ADDR'] ) ) {
    secure_error('Unable to determine ip address');
  }

  //do query and check database connection
  if(! ($q = db_prepare('SELECT id FROM '.PRE.'users WHERE deleted=\'f\' AND admin=\'t\'
                                AND name=? AND password=?', 0 ) ) ) {
    secure_error('Unable to connect to database.  Please try again later.' );
  }

  if(! (db_execute($q, array($username, $md5pass ), 0 ) ) ) {
    secure_error('Unable to connect to database.  Please try again later.' );
  }

  //no such user-password combination
  if( ! ($user_id = @db_result($q, 0, 0) ) ) {

    //limit login attempts if post-1.60 database is being used
    if(@db_query('SELECT * FROM '.PRE.'login_attempt LIMIT 1', 0 ) ) {

      $flag_attempt = true;

      //count the number of recent login attempts
      if( ! $q = db_prepare('SELECT COUNT(*) FROM '.PRE.'login_attempt
                                WHERE name=?
                                AND last_attempt > (now()-INTERVAL '.$delim.'10 MINUTE'.$delim.') LIMIT 4', 0 ) ) {
        secure_error('Unable to connect to database.  Please try again later.' );
      }

      if(! (db_execute($q, array($username ), 0 ) ) ) {
        secure_error('Unable to connect to database.  Please try again later.' );
      }
      $count_attempts = db_result($q, 0, 0 );

      //protect against password guessing attacks
      if($count_attempts > 3 ) {
        secure_error('Exceeded allowable number of login attempts.<br /><br />Account locked for 10 minutes.' );
      }

      //record this login attempt
      $q = db_prepare('INSERT INTO '.PRE.'login_attempt(name, ip, last_attempt ) VALUES (?, ?, now() )' );
      db_execute($q, array($username, $ip ) );
    }

    sleep(2);
    secure_error('Not a valid username, or password' );
  }

  //user is okay log him/her in

  //remove the old login information for post 1.60 database
  if($flag_attempt ) {
    $q = db_prepare('DELETE FROM '.PRE.'login_attempt WHERE last_attempt < (now()-INTERVAL '.$delim.'20 MINUTE'.$delim.') OR name=?' );
    db_execute($q, array($username ) );
  }

  //update for version 1.32 -> 1.40
  if(! (db_query('SELECT groupaccess FROM '.PRE.'config', 0 ) ) ) {
     db_begin();
     db_query('ALTER TABLE '.PRE.'tasks ADD COLUMN groupaccess VARCHAR(5)' );
     db_query('ALTER TABLE '.PRE.'tasks ALTER COLUMN groupaccess SET DEFAULT \'f\'' );
     db_query('ALTER TABLE '.PRE.'config ADD COLUMN groupaccess VARCHAR(50)' );
     $content .= "<p>Updating from version pre-1.40 database ... success!</p>\n";
     db_commit();
  }

  //update for version 1.51 -> 1.60
  if(! (db_query('SELECT * FROM '.PRE.'login_attempt', 0 ) ) ) {

    db_begin();

    switch (DATABASE_TYPE) {

      case 'mysql_pdo':
        db_query('CREATE TABLE '.PRE.'login_attempt ( name VARCHAR(100) NOT NULL,
                                               ip VARCHAR(100) NOT NULL,
                                               last_attempt DATETIME NOT NULL)
                                               TYPE = innoDB
                                               CHARACTER SET = utf8;' );
        break;

      case 'postgresql_pdo':
        db_query('CREATE TABLE "'.PRE.'login_attempt" ( "name" character varying(100) NOT NULL,
                                               "ip" character varying(100) NOT NULL,
                                               "last_attempt" timestamp with time zone NOT NULL DEFAULT current_timestamp(0))' );
        break;

      default:
        error('Database type not specified in config file.' );
        break;
    }
    db_commit();
  }

  //update for version 1.51 -> 1.60
  if(! (db_query('SELECT private FROM '.PRE.'users', 0 ) ) ) {
     db_begin();
     db_query('ALTER TABLE '.PRE.'users ADD COLUMN private INT' );
     db_query('ALTER TABLE '.PRE.'users ALTER COLUMN private SET DEFAULT 0' );
     db_commit();
  }

  //update for version 1.51 -> 1.60
  if(! (db_query('SELECT private FROM '.PRE.'usergroups', 0 ) ) ) {
     db_begin();
     db_query('ALTER TABLE '.PRE.'usergroups ADD COLUMN private INT' );
     db_query('ALTER TABLE '.PRE.'usergroups ALTER COLUMN private SET DEFAULT 0' );
     db_commit();
  }

  //update for version 1.59 -> 1.60
  if(! (db_query('SELECT completed FROM '.PRE.'tasks', 0 ) ) ) {

     db_begin();

    //set parameters for appropriate for database
    switch (DATABASE_TYPE) {
      case 'mysql_pdo':
        $date_type = 'DATETIME';
        break;

      case 'postgresql_pdo':
        $date_type = 'timestamp with time zone';
        break;

      default:
        error('Database type not specified in config file.' );
        break;
    }

    //add columns to database
    db_query('ALTER TABLE '.PRE.'tasks ADD COLUMN completed INT' );
    db_query('ALTER TABLE '.PRE.'tasks ALTER COLUMN completed SET DEFAULT 0' );
    db_query('ALTER TABLE '.PRE.'tasks ADD COLUMN completion_time '.$date_type );

    $q1 = db_prepare('SELECT status FROM '.PRE.'tasks WHERE projectid=? AND parent<>0' );
    $q2 = db_prepare('UPDATE '.PRE.'tasks SET completed=? WHERE id=?' );
    $q3 = db_prepare('SELECT MAX(finished_time) FROM '.PRE.'tasks WHERE projectid=?');
    $q4 = db_prepare('UPDATE '.PRE.'tasks SET completion_time=? WHERE id=?' );

    //retrieve existing data
    $q = db_query('SELECT id FROM '.PRE.'tasks WHERE parent=0' );

    for($i=0 ; $row = @db_fetch_array($q, $i ) ; $i++) {

      db_execute($q1, array($row['id'] ) );

      $total_tasks = 0;
      $tasks_completed = 0;

      for($j=0 ; $row_complete = @db_fetch_num($q_complete, $j ) ; $j++ ) {
        $total_tasks++;

        if($row_complete[0] == 'done') {
          $tasks_completed++;
        }
      }

      //project with no tasks is complete
      if($total_tasks == 0 ){
        $percent_completed = 0;
      }
      else{
        $percent_completed = round($tasks_completed * 100 / $total_tasks );
      }

      db_execute($q2, array((int)$percent_completed, $row['id'] ) );

      //for completed project set the completion time
      if($percent_completed == 100 ){
        db_execute($q3, array($row['id'] ) );
        db_execute($q4, array($completion_time, $row['id'] ) );
      }
    }
    db_free_result($q1 );
    db_free_result($q2 );
    db_free_result($q3 );
    db_free_result($q4 );

    db_commit();
    $content .= "<p>Updating from version pre-1.60 database ... success!</p>\n";
  }

  //update for version 1.60 -> 1.70
  if(! (db_query('SELECT guest FROM '.PRE.'users', 0 ) ) ) {

    db_begin();

    //set parameters for appropriate for database
    switch (DATABASE_TYPE) {
      case 'mysql_pdo':
        $integer = 'TINYINT';
        break;

      case 'postgresql_pdo':
        $integer = 'SMALLINT';
        break;

      default:
        error('Database type not specified in config file.' );
        break;
    }

    db_query('ALTER TABLE '.PRE.'users ADD COLUMN guest '.$integer );
    db_query('ALTER TABLE '.PRE.'users ALTER COLUMN guest SET DEFAULT 0' );
    db_query('UPDATE '.PRE.'users SET guest=0' );

    //archive
    db_query('ALTER TABLE '.PRE.'tasks ADD COLUMN archive '.$integer );
    db_query('ALTER TABLE '.PRE.'tasks ALTER COLUMN archive SET DEFAULT 0' );
    db_query('UPDATE '.PRE.'tasks SET archive=0' );

    //project sorting
    db_query('ALTER TABLE '.PRE.'config ADD COLUMN project_order VARCHAR(200)' );
    db_query('UPDATE '.PRE.'config SET project_order=\'ORDER BY name\'' );

    db_query('ALTER TABLE '.PRE.'config ADD COLUMN task_order VARCHAR(200)' );
    db_query('UPDATE '.PRE.'config SET task_order=\'ORDER BY name\'' );

    db_commit();
    $content .= "<p>Updating from version pre-1.69 database ... success!</p>\n";
  }

  //update for version 1.69 -> 1.70
  if(! (db_query("SELECT fileid FROM ".PRE."files", 0 ) ) ) {
 
    db_begin();
 
    //set parameters for appropriate for database
    switch (DATABASE_TYPE) {
      case 'mysql_pdo':
        db_query('ALTER TABLE '.PRE.'files CHANGE COLUMN oid fileid INT' );
        break;

      case 'postgresql_pdo':
        db_query('ALTER TABLE '.PRE.'files ADD COLUMN fileid INTEGER' );

        $q = db_query('SELECT id, oid FROM '.PRE.'files' );

        $q1 = db_prepare('UPDATE '.PRE.'files SET fileid=? WHERE id=?' );

        for($i=0 ; $row = @db_fetch_array($q, $i ) ; $i++) {
          db_execute($q1, array($row['oid'], $row['id'] ) );
        }
        break;

      default:
        error("Database type not specified in config file." );
        break;
    }
    db_commit();
    $content .= "<p>Updating from version pre-1.70 database ... success!</p>\n";
  }

  //update version 1.81 -> 2.00
  if(! (db_query("SELECT taskid FROM ".PRE."contacts", 0 ) ) ) {

    db_begin();

    //add project capability to contacts
    db_query('ALTER TABLE '.PRE.'contacts ADD COLUMN taskid INT' );
    db_query('UPDATE '.PRE.'contacts SET taskid=0' );

    //add locale for users
    db_query('ALTER TABLE '.PRE.'users ADD COLUMN locale VARCHAR(10)' );
    db_query('UPDATE '.PRE.'users SET locale=\''.LOCALE.'\'' );

    //add sequence for iCalendar
    db_query('ALTER TABLE '.PRE.'tasks ADD COLUMN sequence INT' );
    db_query('ALTER TABLE '.PRE.'tasks ALTER COLUMN sequence SET DEFAULT 0' );
    db_query('UPDATE '.PRE.'tasks SET sequence=0' );

    //change 'datetime' columns to 'timestamp'
    if((DATABASE_TYPE == 'mysql' ) || (DATABASE_TYPE == 'mysql_innodb' ) || (DATABASE_TYPE == 'mysqli' ) ) {
      db_query('ALTER TABLE '.PRE.'tasks MODIFY COLUMN created TIMESTAMP' );
      db_query('ALTER TABLE '.PRE.'tasks MODIFY COLUMN edited TIMESTAMP' );
      db_query('ALTER TABLE '.PRE.'tasks MODIFY COLUMN finished_time TIMESTAMP' );
      db_query('ALTER TABLE '.PRE.'tasks MODIFY COLUMN deadline TIMESTAMP' );
      db_query('ALTER TABLE '.PRE.'tasks MODIFY COLUMN lastforumpost TIMESTAMP' );
      db_query('ALTER TABLE '.PRE.'tasks MODIFY COLUMN lastfileupload TIMESTAMP' );
      db_query('ALTER TABLE '.PRE.'tasks MODIFY COLUMN completion_time TIMESTAMP' );
      db_query('ALTER TABLE '.PRE.'forum MODIFY COLUMN posted TIMESTAMP' );
      db_query('ALTER TABLE '.PRE.'logins MODIFY COLUMN lastaccess TIMESTAMP' );
      db_query('ALTER TABLE '.PRE.'seen MODIFY COLUMN time TIMESTAMP' );
      db_query('ALTER TABLE '.PRE.'contacts MODIFY COLUMN date TIMESTAMP' );
      db_query('ALTER TABLE '.PRE.'files MODIFY COLUMN uploaded TIMESTAMP' );
      db_query('ALTER TABLE '.PRE.'login_attempt MODIFY COLUMN last_attempt TIMESTAMP' );
    }

    db_commit();
    $content .= "<p>Updating from version pre-2.00 database ... success!</p>\n";
  }

  //update version 2.00 -> 2.20
  if(! (db_query('SELECT * FROM '.PRE.'site_name', 0 ) ) ) {

    db_begin();

    if(! defined('MANAGER_NAME') )      define('MANAGER_NAME', 'WebCollab Project Management' );
    if(! defined('ABBR_MANAGER_NAME') ) define('ABBR_MANAGER_NAME', 'WebCollab' );

    switch (DATABASE_TYPE) {
      case 'mysql_pdo':
        db_query('CREATE TABLE '.PRE.'site_name (manager_name VARCHAR(100),
                                                 abbr_manager_name VARCHAR(100) )
                                                 TYPE = innoDB 
                                                 CHARACTER SET = utf8;' );
        break;

      case 'postgresql_pdo':
        db_query('CREATE TABLE "'.PRE.'site_name" ("manager_name" character varying(100),
                                                   "abbr_manager_name" character varying(100) )' );
        break;

      default:
        error('Database type not specified in config file.' );
        break;
    }

    //update the new table
    $q = db_prepare("INSERT INTO ".PRE."site_name (manager_name, abbr_manager_name) VALUES(?, ? )" );
    db_execute($q, array(MANAGER_NAME, ABBR_MANAGER_NAME ) );

    //update deadline hours
    db_query('UPDATE '.PRE.'tasks SET deadline=(deadline+INTERVAL '.$delim.'2 HOUR'.$delim.')' );

    db_commit();
    $content .= "<p>Updating from version pre-2.20 database ... success!</p>\n";
  }

  //update version 2.20 -> 2.30
  if(! (db_query('SELECT edited FROM '.PRE.'forum', 0 ) ) ) {

    db_begin();

    //set parameters for appropriate for database
    switch (DATABASE_TYPE) {
      case 'mysql_pdo':
        $date_type = 'DATETIME';
        break;

      case 'postgresql_pdo':
        $date_type = 'timestamp with time zone';
        break;

      default:
        error('Database type not specified in config file.' );
        break;
    }

    //add edited to forum table
    db_query('ALTER TABLE '.PRE.'forum ADD COLUMN edited '.$date_type );

    //add sequence for forum
    db_query('ALTER TABLE '.PRE.'forum ADD COLUMN sequence INT' );
    db_query('ALTER TABLE '.PRE.'forum ALTER COLUMN sequence SET DEFAULT 0' );
    db_query('UPDATE '.PRE.'forum SET sequence=0' );

    db_commit();
    $content .= "<p>Updating from version pre-2.30 database ... success!</p>\n";
  }

  //update version 2.40 -> 2.50
  if(! (db_query('SELECT token FROM '.PRE.'logins', 0 ) ) ) {

    db_begin();

    //add new column for token
    db_query('ALTER TABLE '.PRE.'logins ADD COLUMN token VARCHAR(100)' );

    //set parameters for appropriate for database
    switch (DATABASE_TYPE) {
      case 'mysql_pdo':
        //increase mime column to 200 characters
        db_query('ALTER TABLE '.PRE.'files MODIFY COLUMN mime VARCHAR(200)' );
        break;

      case 'postgresql_pdo':
        //increase mime column to 200 characters
        db_query('ALTER TABLE '.PRE.'files ALTER COLUMN mime TYPE VARCHAR(200)' );
        break;

      default:
        error('Database type not specified in config file.' );
        break;
    }

    db_commit();
    $content .= "<p>Updating from version pre-2.50 database ... success!</p>\n";
  }

  //update version 2.50 -> 3.00
  if(! (db_query('SELECT token FROM '.PRE.'tokens', 0 ) ) ) {

    db_begin();

    //set parameters for appropriate for database
    switch (DATABASE_TYPE) {
      case 'postgresql_pdo':
        $q = db_query('SELECT data_type FROM information_schema.columns 
                              WHERE table_name=\'tasks\' AND column_name=\'groupaccess\'');
        if(db_result($q, 0, 0 ) == 'boolean' ) {

          db_query('ALTER TABLE '.PRE.'tasks ALTER COLUMN globalaccess DROP DEFAULT' );
          db_query('ALTER TABLE '.PRE.'tasks ALTER COLUMN globalaccess TYPE VARCHAR(5) 
                              USING (CASE WHEN globalaccess THEN \'t\' ELSE \'f\' END),
                              ALTER COLUMN globalaccess SET DEFAULT \'f\'' );

          db_query('ALTER TABLE '.PRE.'tasks ALTER COLUMN groupaccess DROP DEFAULT' );
          db_query('ALTER TABLE '.PRE.'tasks ALTER COLUMN groupaccess TYPE VARCHAR(5) 
                              USING (CASE WHEN groupaccess THEN \'t\' ELSE \'f\' END),
                              ALTER COLUMN groupaccess SET DEFAULT \'t\'' );

          db_query('ALTER TABLE '.PRE.'users ALTER COLUMN admin DROP DEFAULT' );
          db_query('ALTER TABLE '.PRE.'users ALTER COLUMN admin TYPE VARCHAR(5) 
                              USING (CASE WHEN admin THEN \'t\' ELSE \'f\' END),
                              ALTER COLUMN admin SET DEFAULT \'f\'' );

          db_query('ALTER TABLE '.PRE.'users ALTER COLUMN deleted DROP DEFAULT' );
          db_query('ALTER TABLE '.PRE.'users ALTER  COLUMN deleted TYPE VARCHAR(5) 
                              USING (CASE WHEN deleted THEN \'t\' ELSE \'f\' END),
                              ALTER COLUMN deleted SET DEFAULT \'f\'' );
        }

        db_query('CREATE TABLE "'.PRE.'tokens" ("token" character varying(100) NOT NULL,
                                                "action" character varying(100) NOT NULL,
                                                "userid" integer NOT NULL,
                                              "lastaccess" timestamp with time zone NOT NULL DEFAULT current_timestamp(0))' );
        break;

      case 'mysql_pdo':
        db_query('CREATE TABLE '.PRE.'tokens (token VARCHAR(100) NOT NULL,
                                              action VARCHAR(100) NOT NULL,
                                              userid INT UNSIGNED NOT NULL,
                                              lastaccess DATETIME NOT NULL)
                                              TYPE = innoDB 
                                              CHARACTER SET = utf8;' );
        break;

      default:
        error('Database type not specified in config file.' );
        break;
    }

    db_commit();
    $content .= "<p>Updating from version pre-3.00 database ... success!</p>\n";
  }

  if( ! $content ) {
    $content .= "<p>No database updates were required.</p>\n";
  }

  $content .= "<p>Database update action has been completed.</p>\n";

  //display box calls
  create_top("Info", 1 );
  new_box("Update completed", $content, 'boxdata-small', 'head-small' );
  create_bottom();
  die;
}

//
// MAIN PROGRAM
//

//login box screen code
create_top('Login', 1, 0, 2 );

$content = "<div style=\"text-align:center\">\n".
           "<p>Admin login is required for database update:</p>\n".
           "<form method=\"post\" action=\"update.php\">\n".
           "<table style=\"margin-left:auto; margin-right:auto;\">\n".
           "<tr align=\"left\" ><td>Login: </td><td><input type=\"text\" class=\"size\" id=\"username\" name=\"username\" />".
           "<script type=\"text/javascript\">document.getElementById('username').focus();</script></td></tr>\n".
           "<tr align=\"left\" ><td>Password: </td><td><input type=\"password\" class=\"size\" name=\"password\" value=\"\" /></td></tr>\n".
           "</table>\n".
           "<p style=\"padding-top: 20px\"><input type=\"submit\" value=\"Login\" /></p>\n".
           "</form></div>\n";

//set box options
new_box("Login", $content, 'boxdata-small', 'head-small' );

create_bottom();

?>
Return current item: WebCollab