Last Modified 2015-11-20 by Guest

How to convert Serialized Data to UTF-8

The following script was used at the University of Tübingen, Germany, to convert the serialized data from ISO-8859-15 to UTF-8,
in the process of migrating from horde 3 to horde 5. As it was not intended to be used at other sites, it is tailored to
the environment of the University of Tübingen.


# Copyright: 2015  Michael Menge michael.menge(at)uni-tuebingen(dot)de
# http://www.gnu.org/licenses/gpl

define('LOG_LEVEL', LOG_INFO);

$errors = array(LOG_ERR=>0, LOG_WARNING=>0, LOG_NOTICE=>0, LOG_INFO=>0, LOG_DEBUG=>0);

function printLog ($level, $string) {
    global $errors;
    if ($level <= LOG_LEVEL) {
        echo $string;

$ar=array("horde_alarms"=>array("cols" => array("alarm_internal","alarm_methods","alarm_params")),
"turba_objects"=>array("key" => "object_id", "cols" => array("object_members")),
"turba_shares"=>array("key" => "share_id", "cols" => array("attribute_params")),
"kronolith_events"=>array("key" => "event_id", "cols" => array("event_attendees")),
"ingo_rules"=>array("key" => "rule_id", "cols" => array("rule_conditions")),
"horde_histories"=>array("key"=>"history_id", "cols" => array("history_extra")),
"horde_prefs"=>array("cols" => array("pref_value")));

printLog(LOG_INFO, mb_internal_encoding()."\n");
printLog(LOG_INFO, mb_internal_encoding()."\n");
$starttime = time();
$horde_db="host=HOSTNAME dbname=horde user=horde password=XXXXX";

$dbconn = pg_connect($horde_db)
          or die('Verbindungsaufbau fehlgeschlagen: ' . pg_last_error());

foreach ($ar as $table=>$tabledata) {
    if (!isset($tabledata["key"])) {
        $query="CREATE SEQUENCE zdv_encoding_".$table."_seq; ";
        $query.="ALTER TABLE ".$table." ADD COLUMN zdv_encoding_id INTEGER NOT NULL DEFAULT nextval('zdv_encoding_".$table."_seq'); ";
        $query.="CREATE UNIQUE INDEX zdv_encoding_id_idx ON ".$table." (zdv_encoding_id);";
        printLog(LOG_NOTICE, "Sec: ".(time()-$starttime).": before ALTER TABLE $table ADD index COLUMN\n");
        $result = pg_query($query) or die('Abfrage fehlgeschlagen: ' . pg_last_error());
        printLog(LOG_NOTICE, "Sec: ".(time()-$starttime).": after ALTER TABLE $table ADD index COLUMN\n");
    } else {
        printLog(LOG_NOTICE, "Sec: ".(time()-$starttime).": key vorhanden\n");
    if (isset($tabledata["cols"])) {
        foreach ($cols as $col) {
            pg_prepare($my_stmname, "UPDATE ".$table." SET ".$col."=$1 WHERE ".$tablekey."=$2");
            $query = "SELECT ".$tablekey.", ".$col." FROM ".$table." where ".$col." like 'a:%:{%';";
            $result = pg_query($query) or die('Abfrage fehlgeschlagen: ' . pg_last_error());
            printLog(LOG_NOTICE, "Sec: ".(time()-$starttime).": before UPDATE $table:$col\n");
            while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
                if (!mb_check_encoding($value,"ASCII")) {
                    $changed_value = mb_convert_encoding ( $value , "ISO-8859-15");
                    if ($unserial_value != FALSE) {
                        mb_convert_variables("UTF-8", "ISO-8859-15", $unserial_value);
                        printLog(LOG_INFO, "Convert $table:$col ID=$id\n");
                        printLog(LOG_DEBUG, "FROM=$value TO=$converted_value\n");
                        $up_result = pg_execute($dbconn, $my_stmname, array($converted_value, $id));
                        if (!$up_result) {
                            printLog(LOG_ERR, "ERROR WITH QUERY: $query\n");
                        } else {
                    } else {
                        if ($unserial_value == FALSE) {
                            printLog(LOG_ERR, "FAILED: Unserialize ISO and UTF-8 $table:$col VALUE=$value\n");
                        } else {
                            printLog(LOG_INFO, "ALREADY Converted: $table:$col  ID=$id\n");
                            printLog(LOG_DEBUG, "VALUE=$value\n");
                } else {
                    printLog(LOG_DEBUG, "ASCII only $table:$col ID=$id\n");
            printLog(LOG_NOTICE, "--------\nConverted   : $convert_converted\nASCII         : $convert_ascii\nAlready con     : $convert_already\nFailed : $convert_failed\nFehler        : $convert_error\n");
            printLog(LOG_NOTICE, "Sec: ".(time()-$starttime).": after UPDATE $table:$col\n");
    } else {
        printLog(LOG_WARNING, "No Cols for Table $table found\n");
    if (!isset($tabledata["key"])) {
        $query="DROP INDEX zdv_encoding_id_idx; ";
        $query.="ALTER TABLE ".$table." DROP COLUMN zdv_encoding_id; ";
        $query.="DROP SEQUENCE zdv_encoding_".$table."_seq; ";
        printLog(LOG_NOTICE, "Sec: ".(time()-$starttime).": before ALTER TABLE $table DROP COLUMN\n");
        $result = pg_query($query) or die('Abfrage fehlgeschlagen: ' . pg_last_error());
        printLog(LOG_NOTICE, "Sec: ".(time()-$starttime).": after ALTER TABLE $table DROP COLUMN\n");
printLog(LOG_NOTICE, "Sec: ".(time()-$starttime).": after Convert\n");
printLog(LOG_NOTICE, "Errors: ".$errors[LOG_ERR]."\nWarning: ".$errors[LOG_WARNING]."\n");