<?php

require_once dirname(__FILE__).'/accesscheck.php';

if (!$GLOBALS['commandline']) {
    @ob_end_flush();
} else {
    @ob_end_clean();
    print ClineSignature();
  ## when on cl, doit immediately
  $_GET['doit'] = 'yes';
    ob_start();
}

function output($message)
{
    if ($GLOBALS['commandline']) {
        @ob_end_clean();
        print strip_tags($message)."\n";
        ob_start();
    } else {
        print $message;
    # output some stuff to make sure it's not buffered in the browser, hmm, would be nice to find a better way for this
    for ($i = 0;$i < 10000; ++$i) {
        print '  '."\n";
    }
        flush();
        @ob_end_flush();
    }
    flush();
}

$dbversion = getConfig('version');
if (!$dbversion) {
    $dbversion = 'Older than 1.4.1';
}
output('<p class="information">'.$GLOBALS['I18N']->get('Your database version').': '.$dbversion.'</p>');

if ($GLOBALS['database_module'] == 'mysql.inc') {
    print Warn(s('Please edit your config file and change "mysql.inc" to "mysqli.inc" to avoid future PHP incompatibility').
  resourceLink('http://resources.phplist.com/system/mysql-mysqli-update')
  );
}

if ($dbversion == VERSION) {
    output($GLOBALS['I18N']->get('Your database is already the correct version, there is no need to upgrade'));

    print '<p>'.PageLinkAjax('upgrade&update=tlds', s('update Top Level Domains'), '', 'button').'</p>';

    print subscribeToAnnouncementsForm();
} elseif (isset($_GET['doit']) && $_GET['doit'] == 'yes') {
    $success = 1;
  # once we are off, this should not be interrupted
  ignore_user_abort(1);
  # rename tables if we are using the prefix
  include dirname(__FILE__).'/structure.php';
    while (list($table, $value) = each($DBstruct)) {
        set_time_limit(500);
        if (isset($table_prefix)) {
            if (Sql_Table_exists($table) && !Sql_Table_Exists($tables[$table])) {
                Sql_Verbose_Query("alter table $table rename $tables[$table]", 1);
            }
        }
    }
    @ob_end_flush();
    @ob_start();

    output('<p class="information">'.$GLOBALS['I18N']->get('Please wait, upgrading your database, do not interrupt').'</p>');

    flush();

    if (preg_match('/(.*?)-/', $dbversion, $regs)) {
        $dbversion = $regs[1];
    }
    switch ($dbversion) {
    case '1.4.1':
      # nothing changed,
    case '1.4.2':
      # nothing changed,
    case 'dev':
    case '1.4.3':
      foreach (array('admin', 'adminattribute', 'admin_attribute', 'task', 'admin_task') as $table) {
          if (!Sql_Table_Exists($table)) {
              Sql_Create_Table($tables[$table], $DBstruct[$table]);
              if ($table == 'admin') {
                  # create a default admin
            Sql_Query(sprintf('insert into %s values(0,"%s","%s","%s",now(),now(),"%s","%s",now(),%d,0)',
              $tables['admin'], 'admin', 'admin', '', $adminname, 'phplist', 1));
              } elseif ($table == 'task') {
                  while (list($type, $pages) = each($system_pages)) {
                      foreach ($pages as $page) {
                          Sql_Query(sprintf('insert into %s (page,type) values("%s","%s")',
                  $tables['task'], $page, $type));
                      }
                  }
              }
          }
      }
      Sql_Query("alter table {$tables['list']} add column owner integer");
      Sql_Query("alter table {$tables['message']} change column status status enum('submitted','inprocess','sent','cancelled','prepared')");
      Sql_Query("alter table {$tables['template']} change column template template longblob");
      # previous versions did not cleanup properly, fix that here
      $req = Sql_Query("select userid from {$tables['user_attribute']} left join {$tables['user']} on {$tables['user_attribute']}.userid = {$tables['user']}.id where {$tables['user']}.id IS NULL");
      while ($row = Sql_Fetch_Row($req)) {
          Sql_query('delete from '.$tables['user_attribute'].' where userid = '.$row[0]);
      }
      $req = Sql_Query("select user from {$tables['user_message_bounce']} left join {$tables['user']} on {$tables['user_message_bounce']}.user = {$tables['user']}.id where {$tables['user']}.id IS NULL");
      while ($row = Sql_Fetch_Row($req)) {
          Sql_query('delete from '.$tables['user_message_bounce'].' where user = '.$row[0]);
      }
      $req = Sql_Query("select userid from {$tables['usermessage']} left join {$tables['user']} on {$tables['usermessage']}.userid = {$tables['user']}.id where {$tables['user']}.id IS NULL");
      while ($row = Sql_Fetch_Row($req)) {
          Sql_query('delete from '.$tables['usermessage'].' where userid = '.$row[0]);
      }

      $success = 1;
    case '1.5.0':
      # nothing changed
    case '1.5.1':
      # nothing changed
    case '1.6.0':
    case '1.6.1': # not released
      # nothing changed
    case '1.6.2':
      # something we should have done ages ago. make checkboxes save "on" value in user_attribute
      $req = Sql_Query("select * from {$tables['attribute']} where type = \"checkbox\"");
      while ($row = Sql_Fetch_Array($req)) {
          $req2 = Sql_Query("select * from $table_prefix"."listattr_$row[tablename]");
          while ($row2 = Sql_Fetch_array($req2)) {
              if ($row2['name'] == 'Checked') {
                  Sql_Query(sprintf('update %s set value = "on" where attributeid = %d and value = %d',
              $tables['user_attribute'], $row['id'], $row2['id']));
              }
          }
          Sql_Query(sprintf('update %s set value = "" where attributeid = %d and value != "on"',
           $tables['user_attribute'], $row['id']));
          Sql_Query("drop table $table_prefix".'listattr_'.$row['tablename']);
      }
      Sql_Query("insert into {$tables['task']} (page,type) values(\"export\",\"user\")");
    case '1.6.3':
    case '1.6.4':
      Sql_Query("alter table {$tables['user']} add column bouncecount integer default 0");
      Sql_Query("alter table {$tables['message']} add column bouncecount integer default 0");
      # we actually never used these tables, so we can just as well drop and recreate them
      Sql_Query("drop table if exists {$tables['bounce']}");
      Sql_Query("drop table if exists {$tables['user_message_bounce']}");
      Sql_Query(sprintf('create table %s (
        id integer not null primary key auto_increment,
        date datetime,
        header text,
        data blob,
        status varchar(255),
        comment text)', $tables['bounce']));
     Sql_Query(sprintf('create table %s (
        id integer not null primary key auto_increment,
        user integer not null,
        message integer not null,
        bounce integer not null,
        time timestamp,
        index (user,message,bounce))',
        $tables['user_message_bounce']));
      Sql_Query("insert into {$tables['task']} (page,type) values(\"bounce\",\"system\")");
      Sql_Query("insert into {$tables['task']} (page,type) values(\"bounces\",\"system\")");
      Sql_Query("insert into {$tables['task']} (page,type) values(\"processbounces\",\"system\")");
    case '1.7.0':
    case '1.7.1':
    case '1.8.0':
      Sql_Query("insert into {$tables['task']} (page,type) values(\"spage\",\"system\")");
      Sql_Query("insert into {$tables['task']} (page,type) values(\"spageedit\",\"system\")");
      Sql_Query("alter table {$tables['user']} add column subscribepage integer default 0");
      Sql_Create_Table($tables['subscribepage'], $DBstruct['subscribepage']);
      Sql_Create_Table($tables['subscribepage_data'], $DBstruct['subscribepage_data']);
    case '1.9.0':
    case '1.9.1':
    case '1.9.2':
      # no changes
    case '1.9.3':
      # add some indexes to speed things up
      Sql_Query("alter table {$tables['bounce']} add index dateindex (date)");
      Sql_Create_Table($tables['eventlog'], $DBstruct['eventlog']);
      Sql_Query("alter table {$tables['sendprocess']} add column page varchar(100)");
      Sql_Query("alter table {$tables['message']} add column sendstart datetime");
      # some cleaning up of data:
      $req = Sql_Query("select {$tables['usermessage']}.userid
        from {$tables['usermessage']} left join {$tables['user']} on {$tables['usermessage']}.userid = {$tables['user']}.id
        where {$tables['user']}.id IS NULL group by {$tables['usermessage']}.userid");
      while ($row = Sql_Fetch_Row($req)) {
          Sql_Query("delete from {$tables['usermessage']} where userid = $row[0]");
      }
       $req = Sql_Query("select {$tables['user_attribute']}.userid
        from {$tables['user_attribute']} left join {$tables['user']} on {$tables['user_attribute']}.userid = {$tables['user']}.id
        where {$tables['user']}.id IS NULL group by {$tables['user_attribute']}.userid");
      while ($row = Sql_Fetch_Row($req)) {
          Sql_Query("delete from {$tables['user_attribute']} where userid = $row[0]");
      }
       $req = Sql_Query("select {$tables['listuser']}.userid
        from {$tables['listuser']} left join {$tables['user']} on {$tables['listuser']}.userid = {$tables['user']}.id
        where {$tables['user']}.id IS NULL group by {$tables['listuser']}.userid");
      while ($row = Sql_Fetch_Row($req)) {
          Sql_Query("delete from {$tables['listuser']} where userid = $row[0]");
      }
       $req = Sql_Query("select {$tables['usermessage']}.userid
        from {$tables['usermessage']} left join {$tables['user']} on {$tables['usermessage']}.userid = {$tables['user']}.id
        where {$tables['user']}.id IS NULL group by {$tables['usermessage']}.userid");
      while ($row = Sql_Fetch_Row($req)) {
          Sql_Query("delete from {$tables['usermessage']} where userid = $row[0]");
      }
       $req = Sql_Query("select {$tables['user_message_bounce']}.user
        from {$tables['user_message_bounce']} left join {$tables['user']} on {$tables['user_message_bounce']}.user = {$tables['user']}.id
        where {$tables['user']}.id IS NULL group by {$tables['user_message_bounce']}.user");
      while ($row = Sql_Fetch_Row($req)) {
          Sql_Query("delete from {$tables['user_message_bounce']} where user = $row[0]");
      }
    case '2.1.0':
    case '2.1.1':
      # oops deleted tables columns that should not have been deleted:
      if (!Sql_Table_Column_Exists($tables['message'], 'tofield')) {
          Sql_Query("alter table {$tables['message']} add column tofield varchar(255)");
      }
      if (!Sql_Table_Column_Exists($tables['message'], 'replyto')) {
          Sql_Query("alter table {$tables['message']} add column replyto varchar(255)");
      }
    case '2.1.2':
    case '2.1.3':
    case '2.1.4':
      Sql_Query("alter table {$tables['message']} change column asboth astextandhtml integer default 0");
      Sql_Query("alter table {$tables['message']} add column aspdf integer default 0");
      Sql_Query("alter table {$tables['message']} add column astextandpdf integer default 0");
       Sql_Query("alter table {$tables['message']} add column rsstemplate varchar(100)");
       Sql_Query("alter table {$tables['list']} add column rssfeed varchar(255)");
      Sql_Query("alter table {$tables['user']} add column rssfrequency varchar(100)");
      Sql_Create_Table($tables['message_attachment'], $DBstruct['message_attachment']);
      Sql_Create_Table($tables['attachment'], $DBstruct['attachment']);
      Sql_Create_Table($tables['rssitem'], $DBstruct['rssitem']);
      Sql_Create_Table($tables['rssitem_data'], $DBstruct['rssitem_data']);
      Sql_Create_Table($tables['user_rss'], $DBstruct['user_rss']);
      Sql_Create_Table($tables['rssitem_user'], $DBstruct['rssitem_user']);
    case '2.2.0':
    case '2.2.1':
      Sql_Query("alter table {$tables['user']} add column password varchar(255)");
      Sql_Query("alter table {$tables['user']} add column passwordchanged datetime");
      Sql_Query("alter table {$tables['user']} add column disabled tinyint default 0");
      Sql_Query("alter table {$tables['user']} add column extradata text");
      Sql_Query("alter table {$tables['message']} add column owner integer");
    case '2.3.0':
    case '2.3.1':
    case '2.3.2':case '2.3.3':
      Sql_Create_Table($tables['listrss'], $DBstruct['listrss']);
    case '2.3.4':case '2.4.0':
      Sql_Query("insert into {$tables['task']} (page,type) values(\"import3\",\"user\")");
      Sql_Query("insert into {$tables['task']} (page,type) values(\"import4\",\"user\")");
    case '2.5.0':case '2.5.1':
    case '2.5.2':
      Sql_Query("alter table {$tables['subscribepage']} add column owner integer");
      Sql_Query("alter ignore table {$tables['task']} add unique (page)");
    case '2.5.3':case '2.5.4':
      Sql_Query("alter table {$tables['user']} add column foreignkey varchar(100)");
      Sql_Query("alter table {$tables['user']} add index fkey (foreignkey)");
    case '2.5.5': case '2.5.6': case '2.5.7': case '2.5.8':
      # some very odd value managed to sneak in
      $cbgroups = Sql_Query("select id from {$tables['attribute']} where type = \"checkboxgroup\"");
      while ($row = Sql_Fetch_Row($cbgroups)) {
          Sql_Query("update {$tables['user_attribute']} set value = \"\" where attributeid = $row[0] and value=\"Empty\"");
      }
    case '2.6.0':case '2.6.1':case '2.6.2':case '2.6.3':case '2.6.4':case '2.6.5':
      Sql_Verbose_Query("alter table {$tables['message']} add column embargo datetime");
      Sql_Verbose_Query("alter table {$tables['message']} add column repeat integer default 0");
      Sql_Verbose_Query("alter table {$tables['message']} add column repeatuntil datetime");
      # make sure that current queued messages are sent
      Sql_Verbose_Query("update {$tables['message']} set embargo = now() where status = \"submitted\"");
      Sql_Query("alter table {$tables['message']} change column status status enum('submitted','inprocess','sent','cancelled','prepared','draft')");
    case '2.6.6':case '2.7.0': case '2.7.1': case '2.7.2':
      Sql_Create_Table($tables['user_history'], $DBstruct['user_history']);
    case '2.8.0':
      Sql_Query("alter table {$tables['message']} add column textmessage text");
    case '2.8.1': case '2.8.2': case '2.8.3':
    case '2.8.4': case '2.8.5': case '2.8.6':
      Sql_Query("alter table {$tables['user']} add index index_uniqid (uniqid)");
    case 'whatever versions we will get later':
      #Sql_Query("alter table table that altered");
      break;
    default:
      # an unknown version, so we do a generic upgrade, if the version is older than 1.4.1
      if ($dbversion > '1.4.1') {
          break;
      }
      Error('Sorry, your version is too old to safely upgrade');
      $success = 0;
      break;
  }

  # at 2.8.x we started to split into stable (2.8) and unstable (2.9)
  # so upgrading is now mixed between major.minor versions
  list($major, $minor, $sub) = explode('.', $dbversion);
    switch ($major) {
    case '2':
      if ($minor < 9 || ($minor == 9 && $sub == 0)) {
          Sql_Create_Table($tables['user_blacklist'], $DBstruct['user_blacklist']);
          Sql_Create_Table($tables['user_blacklist_data'], $DBstruct['user_blacklist_data']);
          Sql_Query("alter table {$tables['user']} add column blacklisted tinyint default 0");
          Sql_Query("alter table {$tables['message']} change column repeat repeatinterval integer default 0");
          set_time_limit(6000);
        # this one can take a long time
        Sql_Query("alter table {$tables['usermessage']} change column entered entered datetime, add column status varchar(255)");
          Sql_Query("update {$tables['usermessage']} set status =\"sent\"");
      }
      if ($minor < 9 || ($minor == 9 && $sub < 2)) {
          Sql_Create_Table($tables['messagedata'], $DBstruct['messagedata']);
      }
      if ($minor < 9 || ($minor == 9 && $sub < 2)) {
          Sql_Query("alter table {$tables['user_attribute']} add index userindex (userid)");
          Sql_Query("alter table {$tables['user_attribute']} add index attindex (attributeid)");
      }
      if ($minor < 9 || ($minor == 9 && $sub < 3)) {
          # this can take quite a while if there are a lot of users and or messages
        set_time_limit(60000);
          Sql_Query("alter table {$tables['usermessage']} add index userindex (userid)");
          Sql_Query("alter table {$tables['usermessage']} add index messageindex (messageid)");
          Sql_Query("alter table {$tables['usermessage']} add index enteredindex (entered)");
          Sql_Create_Table($tables['urlcache'], $DBstruct['urlcache']);
      }
      if ($minor < 9 || ($minor == 9 && $sub <= 4)) {
          Sql_Create_Table($tables['linktrack'], $DBstruct['linktrack']);
          Sql_Create_Table($tables['linktrack_userclick'], $DBstruct['linktrack_userclick']);
          SaveConfig('xormask', md5(uniqid(rand(), true)), 0);
      }
      if ($minor < 9 || ($minor == 9 && $sub < 5)) {
          Sql_Create_Table($tables['user_message_forward'], $DBstruct['user_message_forward']);
          Sql_Query("alter table {$tables['user_attribute']} add index userattid (attributeid,userid)");
          Sql_Query("alter table {$tables['user_attribute']} add index attuserid (userid,attributeid)");
          Sql_Query("alter table {$tables['message']} change column status status varchar(255)");
          Sql_Create_Table($tables['userstats'], $DBstruct['userstats']);
          Sql_Create_Table($tables['bounceregex'], $DBstruct['bounceregex']);
          Sql_Create_Table($tables['bounceregex_bounce'], $DBstruct['bounceregex_bounce']);
      }
      if ($minor < 11 || ($minor == 11 && $sub < 2)) {
          Sql_Create_Table($tables['linktrack_forward'], $DBstruct['linktrack_forward']);
          Sql_Create_Table($tables['linktrack_ml'], $DBstruct['linktrack_ml']);
          Sql_Create_Table($tables['linktrack_uml_click'], $DBstruct['linktrack_uml_click']);
      }
      if ($minor < 11 || ($minor == 11 && $sub < 3)) {
          Sql_Query(sprintf('alter table %s add column optedin tinyint default 0', $tables['user']));
      }
      if ($minor < 11 || ($minor == 11 && $sub < 5)) {
          Sql_Query(sprintf('alter table %s add column category varchar(255) default ""', $tables['list']));
          Sql_Query(sprintf('alter table %s add column requeueinterval integer default 0', $tables['message']));
          Sql_Query(sprintf('alter table %s add column requeueuntil datetime', $tables['message']));
      }
      if ($minor < 11 || ($minor == 11 && $sub < 7)) {
          Sql_Create_Table($tables['admin_password_request'], $DBstruct['admin_password_request'], 1);
          Sql_Create_Table($tables['admintoken'], $DBstruct['admintoken'], 1);
          Sql_Create_Table($tables['i18n'], $DBstruct['i18n'], 1);
          unset($_SESSION['hasI18Ntable']);
          $req = Sql_Query(sprintf('select loginname,password from %s where length(password) < %d', $GLOBALS['tables']['admin'], $GLOBALS['hash_length']));
          while ($row = Sql_Fetch_Assoc($req)) {
              $encryptedPassDB =  hash(ENCRYPTION_ALGO, $row['password']);
              $query = sprintf('update %s set password = "%s" where loginname = "%s"', $GLOBALS['tables']['admin'], $encryptedPassDB, $row['loginname']);
              Sql_Query($query);
          }
      }
      break;
  }

  ## add index on bounces, but ignore the error
  Sql_Query("create index statusindex on {$tables['user_attribute']} (status(10))", 1);
    Sql_Query("create index message_lookup using btree on {$tables['user_message_bounce']} (message)", 1);

  ## add index to i18n to avoid duplicate translations
  ## alter ignore doesn't seem to work on InnoDB: http://bugs.mysql.com/bug.php?id=40344
  # convert to MyIsam first @@Mysql Specific code !
  Sql_Query('alter table '.$tables['i18n'].' engine MyIsam', 1);
    Sql_Query('alter ignore table '.$tables['i18n'].' add unique lanorigunq (lan(10),original(200))', 1);

  ## mantis issue 9001, make sure that the "repeat" column in the messages table is renamed to repeatinterval
  # to avoid a name clash with Mysql 5.
  # problem is that this statement will fail if the DB is already running Mysql 5
  if (Sql_Table_Column_Exists($GLOBALS['tables']['message'], 'repeat')) {
      Sql_Query(sprintf('alter ignore table %s change column repeat repeatinterval integer default 0', $GLOBALS['tables']['message']));
  }

  # check whether it worked and otherwise throw an error to say it needs to be done manually
  if (Sql_Table_Column_Exists($GLOBALS['tables']['message'], 'repeat')) {
      print 'Error, unable to rename column "repeat" in the table '.$GLOBALS['tables']['message'].' to be "repeatinterval"<br/>
      Please do this manually, refer to http://mantis.phplist.com/view.php?id=9001 for more information';
  }

  # fix the new powered by image for the templates
  Sql_Query(sprintf('update %s set data = "%s",width=70,height=30 where filename = "powerphplist.png"',
    $tables['templateimage'], $newpoweredimage));

  # update the system pages
  include_once dirname(__FILE__).'/defaultconfig.php';

  ## remember whether we've done this, to avoid doing it every time
  ## even thought that's not such a big deal
  $isUTF8 = getConfig('UTF8converted');

    if (empty($isUTF8)) {
        $maxsize = 0;
        $req = Sql_Query('select (data_length+index_length) tablesize 
      from information_schema.tables
      where table_schema="'.$GLOBALS['database_name'].'"');

        while ($row = Sql_Fetch_Assoc($req)) {
            if ($row['tablesize'] > $maxsize) {
                $maxsize = $row['tablesize'];
            }
        }
        $maxsize = (int) $maxsize;
        $avail = disk_free_space('/'); ## we have no idea where MySql stores the data, so this is only a crude check and warning.
    $maxsize = (int) ($maxsize * 1.2); ## add another 20%

    ## convert to UTF8
    $dbname = $GLOBALS['database_name'];
        if ($maxsize < $avail && !empty($dbname)) {
            ## the conversion complains about a key length
      Sql_Query(sprintf('alter table '.$GLOBALS['tables']['user_blacklist_data'].' change column email email varchar(150) not null unique'));

            $req = Sql_Query('select * from information_schema.columns where table_schema = "'.$dbname.'" and CHARACTER_SET_NAME != "utf8"');

            $dbcolumns = array();
            $dbtables = array();
            while ($row = Sql_Fetch_Assoc($req)) {
                ## make sure to only change our own tables, in case we share with other applications
        if (in_array($row['TABLE_NAME'], array_values($GLOBALS['tables']))) {
            $dbcolumns[] = $row;
            $dbtables[$row['TABLE_NAME']] = $row['TABLE_NAME'];
        }
            }

            Sql_Query('use '.$dbname);

            output($GLOBALS['I18N']->get('Upgrading the database to use UTF-8, please wait').'<br/>');
            foreach ($dbtables as $dbtable) {
                set_time_limit(3600);
                output($GLOBALS['I18N']->get('Upgrading table ').' '.$dbtable.'<br/>');
                Sql_Query(sprintf('alter table %s default charset utf8', $dbtable), 1);
            }

            foreach ($dbcolumns as $dbcolumn) {
                set_time_limit(600);
                output($GLOBALS['I18N']->get('Upgrading column ').' '.$dbcolumn['COLUMN_NAME'].'<br/>');
                Sql_Query(sprintf('alter table %s change column %s %s %s default character set utf8',
          $dbcolumn['TABLE_NAME'], $dbcolumn['COLUMN_NAME'], $dbcolumn['COLUMN_NAME'], $dbcolumn['COLUMN_TYPE']), 1);
            }
            output($GLOBALS['I18N']->get('upgrade to UTF-8, done').'<br/>');
            saveConfig('UTF8converted', date('Y-m-d H:i'), 0);
        } else {
            print '<div class="error">'.s('Database requires converting to UTF-8.').'<br/>';
            print s('However, there is too little diskspace for this conversion').'<br/>';
            print s('Please do a manual conversion.').' '.PageLinkButton('converttoutf8', s('Run manual conversion to UTF8'));
            print '</div>';
        }
    }

  ## 2.11.7 and up
  Sql_Query(sprintf('alter table %s add column privileges text', $tables['admin']), 1);
    Sql_Query('alter table '.$tables['list'].' add column category varchar(255) default ""', 1);
    Sql_Query('alter table '.$tables['user_attribute'].' change column value value text');
    Sql_Query('alter table '.$tables['message'].' change column textmessage textmessage longtext');
    Sql_Query('alter table '.$tables['message'].' change column message message longtext');
    Sql_Query('alter table '.$tables['messagedata'].' change column data data longtext');
    Sql_Query('alter table '.$tables['bounce'].' add index statusidx (status(20))', 1);

  ## fetch the list of TLDs, if possible
  if (defined('TLD_AUTH_LIST')) {
      refreshTlds(true);
  }

  ## changed terminology
  Sql_Query(sprintf('update %s set status = "invalid email address" where status = "invalid email"', $tables['usermessage']));

  ## for some reason there are some config entries marked non-editable, that should be
  include_once dirname(__FILE__).'/defaultconfig.php';
    foreach ($default_config as $configItem => $configDetails) {
        if (empty($configDetails['hidden'])) {
            Sql_Query(sprintf('update %s set editable = 1 where item = "%s"', $tables['config'], $configItem));
        } else {
            Sql_Query(sprintf('update %s set editable = 0 where item = "%s"', $tables['config'], $configItem));
        }
    }

  ## replace old header and footer with the new one
  ## but only if there are untouched from the default, which seems fairly common
  $oldPH = @file_get_contents(dirname(__FILE__).'/ui/old_public_header.inc');
    $oldPH2 = preg_replace("/\n/", "\r\n", $oldPH); ## version with \r\n instead of \n

  $oldPF = @file_get_contents(dirname(__FILE__).'/ui/old_public_footer.inc');
    $oldPF2 = preg_replace("/\n/", "\r\n", $oldPF); ## version with \r\n instead of \n
  Sql_Query(sprintf('update %s set value = "%s" where item = "pageheader" and (value = "%s" or value = "%s")', $tables['config'], sql_escape($defaultheader), addslashes($oldPH), addslashes($oldPH2)));
    Sql_Query(sprintf('update %s set value = "%s" where item = "pagefooter" and (value = "%s" or value = "%s")', $tables['config'], sql_escape($defaultfooter), addslashes($oldPF), addslashes($oldPF2)));

  ## and the same for subscribe pages
  Sql_Query(sprintf('update %s set data = "%s" where name = "header" and (data = "%s" or data = "%s")', $tables['subscribepage_data'], sql_escape($defaultheader), addslashes($oldPH), addslashes($oldPH2)));
    Sql_Query(sprintf('update %s set data = "%s" where name = "footer" and (data = "%s" or data = "%s")', $tables['subscribepage_data'], sql_escape($defaultfooter), addslashes($oldPF), addslashes($oldPF2)));

    if (is_file(dirname(__FILE__).'/ui/'.$GLOBALS['ui'].'/old_public_header.inc')) {
        $oldPH = file_get_contents(dirname(__FILE__).'/ui/'.$GLOBALS['ui'].'/old_public_header.inc');
        $oldPH2 = preg_replace("/\n/", "\r\n", $oldPH); ## version with \r\n instead of \n
    $oldPF = file_get_contents(dirname(__FILE__).'/ui/'.$GLOBALS['ui'].'/old_public_footer.inc');
        $oldPF2 = preg_replace("/\n/", "\r\n", $oldPF); ## version with \r\n instead of \n
    $currentPH = getConfig('pageheader');
        $currentPF = getConfig('pagefooter');

        if (($currentPH == $oldPH2 || $currentPH."\r\n" == $oldPH2) && !empty($defaultheader)) {
            SaveConfig('pageheader', $defaultheader, 1);
            Sql_Query(sprintf('update %s set data = "%s" where name = "header" and data = "%s"', $tables['subscribepage_data'], sql_escape($defaultheader), addslashes($currentPH)));
      ## only try to change footer when header has changed
      if ($currentPF == $oldPF2 && !empty($defaultfooter)) {
          SaveConfig('pagefooter', $defaultfooter, 1);
          Sql_Query(sprintf('update %s set data = "%s" where name = "footer" and data = "%s"', $tables['subscribepage_data'], sql_escape($defaultfooter), addslashes($currentPF)));
      }
        }
    }

  ## #17328 - remove list categories with quotes
  Sql_Query(sprintf("update %s set category = replace(category,\"\\\\'\",\" \")", $tables['list']));

  ## longblobs are better at mixing character encoding. We don't know the encoding of anything we may want to store in cache
  ## before converting, it's quickest to clear the cache
  clearPageCache();
    Sql_Query(sprintf('alter table %s change column content content longblob', $tables['urlcache']));

  # mark the database to be our current version
  if ($success) {
      SaveConfig('version', VERSION, 0);
    # mark now to be the last time we checked for an update
    SaveConfig('updatelastcheck', date('Y-m-d H:i:s', time()), 0, true);
    ## also clear any possible value for "updateavailable"
    Sql_Query(sprintf('delete from %s where item = "updateavailable"', $tables['config']));

      Info(s('Success'), 1);

      upgradePlugins(array_keys($GLOBALS['plugins']));

      print subscribeToAnnouncementsForm();

##  check for old click track data
    $num = Sql_Fetch_Row_Query(sprintf('select count(*) from %s', $GLOBALS['tables']['linktrack']));
      if ($num[0] > 0) {
          print '<p class="information">'.$GLOBALS['I18N']->get('The clicktracking system has changed').'</p>';
          printf($GLOBALS['I18N']->get('You have %s entries in the old statistics table'), $num[0]).' ';
          print ' '.PageLinkButton('convertstats', $GLOBALS['I18N']->get('Convert Old data to new'));
      }

      if ($GLOBALS['commandline']) {
          output($GLOBALS['I18N']->get('Upgrade successful'));
      }
  } else {
      Error('An error occurred while upgrading your database');
      if ($GLOBALS['commandline']) {
          output($GLOBALS['I18N']->get('Upgrade failed'));
      }
  }
} else {
    print '<p>'.s('Your database requires upgrading, please make sure to create a backup of your database first.').'</p>';
    print '<p>'.s('If you have a large database, make sure you have sufficient diskspace available for upgrade.').'</p>';
    print '<p>'.s('When you are ready click %s Depending on the size of your database, this may take quite a while. Please make sure not to interrupt the process, once it started.', PageLinkButton('upgrade&doit=yes', s('Upgrade'))).'</p>';
}
