#!/usr/bin/php
<?php

# DB from and to charsets.
$from  = 'latin1';
$to    = 'utf8';

# Corresponding PHP from and to charsets.
$cfrom = 'ISO-8859-1';
$cto   = 'UTF-8';

// ----------------------------------------------------------------------
// Load the Phorum database layer
// ----------------------------------------------------------------------

define('PHORUM', '5.2');

require_once('./include/db/config.php');

// Backward compatbility: the "mysqli" layer was merged with the "mysql"
// layer, but people might still be using "mysqli" as their configured
// database type.
if ($PHORUM["DBCONFIG"]["type"] == "mysqli" &&
    !file_exists("./include/db/mysqli.php")) {
    $PHORUM["DBCONFIG"]["type"] = "mysql";
}

// Load the database layer.
$PHORUM['DBCONFIG']['type'] = basename($PHORUM['DBCONFIG']['type']);
require_once("./include/db/{$PHORUM['DBCONFIG']['type']}.php");

function phorum_database_error($error) {
    die($error . "\n");
}

// ----------------------------------------------------------------------
// Convert the Phorum tables
// ----------------------------------------------------------------------

$tables = phorum_db_interact(
    DB_RETURN_ROWS,
    'SHOW TABLES',
    0
);

$qp = quotemeta($PHORUM['DBCONFIG']['table_prefix']);

foreach ($tables as $table => $dummy)
{
    if (preg_match("/^{$qp}_/", $table))
    {
        print "CONVERT $table\n";

	phorum_db_interact(DB_RETURN_RES, "ALTER TABLE $table CONVERT TO CHARACTER SET $to");
    }
}

// ----------------------------------------------------------------------
// Do fixes on serialized data
// ----------------------------------------------------------------------

function convert_item(&$item, $key)
{
    global $cfrom;
    global $cto;

    $orig = $item;
    $item = iconv($cfrom, $cto, $item);

    if ($orig != $item) {
        global $fix;
        $fix[] = $key;
    }
}

// Specification of the fields that use serialized data.
$serialized_fields = array
(
  array(
    "moderator_data in {$PHORUM['user_table']}",
    "SELECT user_id, moderator_data FROM {$PHORUM['user_table']} WHERE moderator_data IS NOT NULL AND moderator_data != ''",
    "UPDATE {$PHORUM['user_table']} SET moderator_data='%val%' WHERE user_id='%key%'"
  ),

  array(
    "settings_data in {$PHORUM['user_table']}",
    "SELECT user_id, settings_data FROM {$PHORUM['user_table']} WHERE settings_data IS NOT NULL AND settings_data != ''",
    "UPDATE {$PHORUM['user_table']} SET settings_data='%val%' WHERE user_id='%key%'"
  ),

  array(
    "meta data in {$PHORUM['pm_messages_table']}",
    "SELECT pm_message_id, meta FROM {$PHORUM['pm_messages_table']} WHERE meta IS NOT NULL AND meta != ''",
    "UPDATE {$PHORUM['pm_messages_table']} SET meta='%val%' WHERE pm_message_id='%key%'"
  ),

  array(
    "settings in {$PHORUM['settings_table']}",
    "SELECT name, data FROM {$PHORUM['settings_table']} WHERE type != 'V' AND data IS NOT NULL AND data != ''",
    "UPDATE {$PHORUM['settings_table']} SET data='%val%' WHERE name = '%key%'"
  ),

  array(
    "meta data in {$PHORUM['message_table']}",
    "SELECT message_id, meta FROM {$PHORUM['message_table']} WHERE meta IS NOT NULL AND meta != ''",
    "UPDATE {$PHORUM['message_table']} SET meta='%val%' WHERE message_id='%key%'"
  ),

  array(
    "forum_path data in {$PHORUM['forums_table']}",
    "SELECT forum_id, forum_path FROM {$PHORUM['forums_table']} WHERE forum_path IS NOT NULL",
    "UPDATE {$PHORUM['forums_table']} SET forum_path='%val%' WHERE forum_id='%key%'"
  ),
);

foreach ($serialized_fields as $field)
{
    print "FIX: {$field[0]}\n";

    // Retrieve the data in the old format.

    phorum_db_interact(DB_RETURN_RES, "SET NAMES $from");
    phorum_db_interact(DB_RETURN_RES, "SET CHARACTER SET $from");

    $res = phorum_db_interact(
        DB_RETURN_RES,
        $field[1]
    );

    // Convert serialized data to UTF-8 and put it back in the database.

    phorum_db_interact(DB_RETURN_RES, "SET NAMES $to");
    phorum_db_interact(DB_RETURN_RES, "SET CHARACTER SET $to");

    $fixcount = 0;
    while ($row = phorum_db_fetch_row($res, DB_RETURN_ROW))
    {
        $val = unserialize($row[1]);
        $fix = array();
        array_walk_recursive($val, 'convert_item');

        // Was there something changed in the array?
        // Then update the database.
        if (!empty($fix))
        {
            $key = phorum_db_interact(DB_RETURN_QUOTED, $row[0]);
            $val = phorum_db_interact(DB_RETURN_QUOTED, serialize($val));

            $sql = str_replace(
                array('%key%','%val%'),
                array($key, $val),
                $field[2] 
            );

            phorum_db_interact(DB_RETURN_RES, $sql);

            $fixcount ++;
            print ".";
        }
    }
    if ($fixcount) print "\n";
}


?>