update_db.php

Go to the documentation of this file.

<?php
/*
 * update_db#.php
 * 
 * Copyright 2013 Johannes <jojo@jojo-42>
 * 
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
 * MA 02110-1301, USA.
 * 
 * 
 */

require_once("config.php");
require_once("inc/db.class.php");
require_once("inc/auth.class.php");
require_once("inc/poll.class.php");
require_once("inc/tools.php");
$db= new db( DB_USER, DB_PASS, DB_DATABASE, DB_HOST );

try {
    $dbh = new PDO("mysql:dbname=".DB_DATABASE.";host=".DB_HOST, DB_USER, DB_PASS,
                        array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
} catch (PDOException $e) {
    die( 'Connection failed: ' . $e->getMessage());
}

$au = make_auth_object();


echo "<pre>";
        
/*
$sqh = $dbh->query("SHOW TABLES FROM `umfragen`");
$rows = $sqh->fetchAll(PDO::FETCH_ASSOC);
$tables = array();
foreach($rows as $r){
        $tables[] = $r["Tables_in_umfragen"];
}
//var_dump($tables);
foreach($tables as $table){

        
        if( is_numeric($table) ){
                echo "<h1>{$table}</h1>";
                $sql = "ALTER TABLE `umfragen`.`$table` ADD `parent` int( 11 ) NOT NULL DEFAULT '-1'";
                echo "<p>".$sql."</p>";
                $dbh->exec($sql);
                        
                $sql = "SELECT ID,name FROM `umfragen`.`$table` WHERE `typ`='radioButtonList' OR `typ`='checkBoxList'";
                $sqh = $dbh->query($sql);
                $rows = $sqh->fetchAll( PDO::FETCH_ASSOC );
                foreach( $rows as $row){
                        $sql = "UPDATE `umfragen`.`$table` SET `parent`= {$row["ID"]} WHERE `is_child` = '1' AND `name`={$row["name"]}";
                        echo "<p>".$sql."</p>";
                        $dbh->exec($sql);
                }
        }
        
        
        if( substr($table,-1*strlen("_results") ) == "_results"){
                echo $table.PHP_EOL;
                echo "/\/\/\/\/\/".PHP_EOL;
                $sql = "ALTER TABLE `$table` ADD `name` varchar( 256 ) NOT NULL ";
                echo $sql.PHP_EOL;
                $dbh->exec($sql);

                $sql = "ALTER TABLE `$table` ADD `group` varchar( 256 ) NOT NULL ";
                echo $sql.PHP_EOL;
                $dbh->exec($sql);
                                

        }
        
}

$sqh = $dbh->query("SELECT * from `umfragen`.`umfragen`");      
$poll_info_arrays = $sqh->fetchAll(PDO::FETCH_ASSOC);
$polls = array();

foreach($poll_info_arrays as $poll_array){
        $p = new poll();
        $p->load_from_id($db,$poll_array["ID"]);
        $polls[] = $p;
}

//var_dump($polls);

foreach( $polls as $poll ){
        foreach( $poll->widget_list as $widget ){
                if( $widget instanceof container ){
                        foreach( $widget->option_list as $option ){
                                $option->serialize_config();
                                $db->update_widget_field($option , "config" , $option->config);                         
                        }
                }
        $widget->unserialize_config();  
        $widget->serialize_config();
        $db->update_widget_field($widget , "config" , $widget->config);                         
                
        }
}
 

$dbh->exec("ALTER TABLE `umfragen` ADD `active_since` DATETIME NOT NULL AFTER `timeout`");
*/
/*
$dbh->exec("ALTER TABLE `umfragen` ADD `hashID` VARCHAR( 32 ) NOT NULL AFTER `ID`");
$dbh->exec("ALTER TABLE `umfragen` ADD `noDisplay` TINYINT( 1 ) NOT NULL DEFAULT '0' AFTER `status`");

$polls_arr = $db->get_all_polls_array();

foreach( $polls_arr as $p ){
        $pp = new poll();
        $pp->load_from_id($db,$p["ID"]);

        echo "Processing {$pp->name} ...".PHP_EOL;
        $hash = $pp->make_id_hash(); 
        echo "  hash is $hash".PHP_EOL;
        $sql = "UPDATE `umfragen` SET `hashID` = '$hash' WHERE `ID`={$pp->ID}";
        echo "  SQL IS: $sql".PHP_EOL;
        $count = $dbh->exec($sql);
        echo "  $count row(s) modified".PHP_EOL.PHP_EOL;
} 

*/

/*$sql "CREATE TABLE IF NOT EXISTS `user_config` (
  `user` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `polls_visited` longtext COLLATE utf8_unicode_ci NOT NULL
);";
echo $sql;
$dbh->exec($sql);
*/



$sqh = $dbh->query("SHOW TABLES FROM `umfragen`");
$rows = $sqh->fetchAll(PDO::FETCH_ASSOC);
$tables = array();
foreach($rows as $r){
        $tables[] = $r["Tables_in_umfragen"];
}
//var_dump($tables);
foreach($tables as $table){

        
        /*if( is_numeric($table) ){
                echo "<h1>{$table}</h1>";
                $sql = "ALTER TABLE `umfragen`.`$table` ADD `parent` int( 11 ) NOT NULL DEFAULT '-1'";
                echo "<p>".$sql."</p>";
                $dbh->exec($sql);
                        
                $sql = "SELECT ID,name FROM `umfragen`.`$table` WHERE `typ`='radioButtonList' OR `typ`='checkBoxList'";
                $sqh = $dbh->query($sql);
                $rows = $sqh->fetchAll( PDO::FETCH_ASSOC );
                foreach( $rows as $row){
                        $sql = "UPDATE `umfragen`.`$table` SET `parent`= {$row["ID"]} WHERE `is_child` = '1' AND `name`={$row["name"]}";
                        echo "<p>".$sql."</p>";
                        $dbh->exec($sql);
                }
        }*/
        
        
        if( substr($table,-1*strlen("_results") ) == "_results"){
                echo "<h1>".$table."</h1>".PHP_EOL;
                
                $sql = "ALTER TABLE `$table` ADD `Nachname` varchar( 256 ) NOT NULL AFTER `name`";
                echo $sql.PHP_EOL;
                $dbh->exec($sql);

                $sql = "ALTER TABLE `$table` ADD `Vorname` varchar( 256 ) NOT NULL AFTER `name`";
                echo $sql.PHP_EOL;
                $dbh->exec($sql);

                $sqh = $dbh->query("SELECT * FROM `{$table}`");
                $rows = $sqh->fetchAll(PDO::FETCH_ASSOC);
                foreach( $rows as $row ){
                        if( ($row["user"] !== "") ){
                                $inf = $au->info($row["user"]);
                                if(  (isset($inf["surName"])) AND (isset($inf["givenName"])) AND  ($inf["surName"] != "") AND ($inf["givenName"] != "") ){
                                        echo $row["user"]." -> ".$inf["fullName"].PHP_EOL;
                                        $sqh = $dbh->prepare("UPDATE `$table` SET `Vorname` = :givenName , `Nachname` = :surName WHERE `user` = '{$row["user"]}'");
                                        $sqh->execute(   array(":givenName"=>$inf["givenName"], ":surName"=>$inf["surName"])   );
                                }
                        }
                }
                
        }
        
}




echo "</pre>";


?>