[commits] [Wiki] created: ConvertSerializedDataToUTF8

[Wiki Guest]

guest [134.2.22.132]  Fri, 20 Nov 2015 16:30:21 +0000

Created page: http://wiki.horde.org/ConvertSerializedDataToUTF8

<code>
#!/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");

?>
</code>