6.0.0-git
2022-01-22
Last Modified 2015-11-20 by Guest

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

<?php
// if you get postgres-errors:
// ALTER TABLE horde_alarms DROP zdv_encoding_id;
// DROP SEQUENCE zdv_encoding_horde_<tablename>_seq;

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;
    $errors[$level]++;
    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");
mb_internal_encoding("UTF-8");
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) {
    $tablekey="";
    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");
        $tablekey="zdv_encoding_id";
    } else {
        printLog(LOG_NOTICE, "Sec: ".(time()-$starttime).": key vorhanden\n");
        $tablekey=$tabledata["key"];
    }
    if (isset($tabledata["cols"])) {
        $cols=$tabledata["cols"];
        foreach ($cols as $col) {
            $convert_error=0;
            $convert_failed=0;
            $convert_converted=0;
            $convert_already=0;
            $convert_ascii=0;
            $my_stmname="my_update_".$table."_".$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)) {
                $value=$line[$col];
                $id=$line[$tablekey];
                if (!mb_check_encoding($value,"ASCII")) {
                    $changed_value = mb_convert_encoding ( $value , "ISO-8859-15");
                    $unserial_value=@unserialize($changed_value);
                    if ($unserial_value != FALSE) {
                        mb_convert_variables("UTF-8", "ISO-8859-15", $unserial_value);
                        $converted_value=serialize($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");
                            $convert_error++;
                        } else {
                            $convert_converted++;
                        }
                    } else {
                        $unserial_value=@unserialize($value);
                        if ($unserial_value == FALSE) {
                            printLog(LOG_ERR, "FAILED: Unserialize ISO and UTF-8 $table:$col VALUE=$value\n");
                            $convert_failed++;
                        } else {
                            printLog(LOG_INFO, "ALREADY Converted: $table:$col  ID=$id\n");
                            printLog(LOG_DEBUG, "VALUE=$value\n");
                            $convert_already++;
                        }
                    }
                } else {
                    printLog(LOG_DEBUG, "ASCII only $table:$col ID=$id\n");
                    $convert_ascii++;
                }
            }
            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");

?>