\documentclass{article}
\usepackage{ulem}
\usepackage{graphicx}
\usepackage{hyperref}
\pagestyle{headings}
\begin{document}
\part{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,<br />
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<br />
the environment of the University of Tübingen which runs PostgreSQL. You should adjust the script to your own needs.

\begin{itemize}
\item \textbf{THIS SCRIPT COMES WITH NO WARRANTY}


\item **BACKUP YOUR DATABASE **


\end{itemize}
This script expects that the database is converted to utf-8 already, and will fix the serialization of non-ASCII data

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

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()."\textbackslash\{\}n");
mb\_internal\_encoding("UTF-8");
printLog(LOG\_INFO, mb\_internal\_encoding()."\textbackslash\{\}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\textbackslash\{\}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\textbackslash\{\}n");
        \$tablekey="zdv\_encoding\_id";
    \} else \{
        printLog(LOG\_NOTICE, "Sec: ".(time()-\$starttime).": key vorhanden\textbackslash\{\}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\textbackslash\{\}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\textbackslash\{\}n");
                        printLog(LOG\_DEBUG, "FROM=\$value TO=\$converted\_value\textbackslash\{\}n");
                        \$up\_result = pg\_execute(\$dbconn, \$my\_stmname, array(\$converted\_value, \$id));
                        if (!\$up\_result) \{
                            printLog(LOG\_ERR, "ERROR WITH QUERY: \$query\textbackslash\{\}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\textbackslash\{\}n");
                            \$convert\_failed++;
                        \} else \{
                            printLog(LOG\_INFO, "ALREADY Converted: \$table:\$col  ID=\$id\textbackslash\{\}n");
                            printLog(LOG\_DEBUG, "VALUE=\$value\textbackslash\{\}n");
                            \$convert\_already++;
                        \}
                    \}
                \} else \{
                    printLog(LOG\_DEBUG, "ASCII only \$table:\$col ID=\$id\textbackslash\{\}n");
                    \$convert\_ascii++;
                \}
            \}
            printLog(LOG\_NOTICE, "--------\textbackslash\{\}nConverted   : \$convert\_converted\textbackslash\{\}nASCII         : \$convert\_ascii\textbackslash\{\}nAlready con     : \$convert\_already\textbackslash\{\}nFailed : \$convert\_failed\textbackslash\{\}nFehler        : \$convert\_error\textbackslash\{\}n");
            printLog(LOG\_NOTICE, "Sec: ".(time()-\$starttime).": after UPDATE \$table:\$col\textbackslash\{\}n");
        \}
    \} else \{
        printLog(LOG\_WARNING, "No Cols for Table \$table found\textbackslash\{\}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\textbackslash\{\}n");
        \$result = pg\_query(\$query) or die('Abfrage fehlgeschlagen: ' . pg\_last\_error());
        printLog(LOG\_NOTICE, "Sec: ".(time()-\$starttime).": after ALTER TABLE \$table DROP COLUMN\textbackslash\{\}n");
    \}
\}
printLog(LOG\_NOTICE, "Sec: ".(time()-\$starttime).": after Convert\textbackslash\{\}n");
printLog(LOG\_NOTICE, "Errors: ".\$errors[LOG\_ERR]."\textbackslash\{\}nWarning: ".\$errors[LOG\_WARNING]."\textbackslash\{\}n");

?>
</code></pre>
\end{document}