op5-upgradescripts/import_schedules.php

   1 <?php
   2
   3 $gui_db_opt['type'] = 'mysql'; # mysql is the only one supported for now.
   4 $gui_db_opt['host'] = 'localhost';
   5 $gui_db_opt['user'] = 'monitor';
   6 $gui_db_opt['passwd'] = 'monitor';
   7 $gui_db_opt['database'] = 'monitor_gui';
   8 $gui_db_opt['persistent'] = true;       # set to false if you're using php-cgi
   9 $gui_db_opt['new_db_version'] = '5.0';
  10 $gui_dbh = false; # database resource
  11 $DEBUG = true;
  12
  13 # connects to and selects database. false on error, true on success
  14 function gui_db_connect() {
  15         global $gui_dbh;
  16         global $gui_db_opt;
  17
  18         if($gui_db_opt['type'] !== 'mysql') {
  19                 die("Only mysql is supported as of yet.<br />\n");
  20         }
  21
  22         if(!empty($gui_db_opt['persistent'])) {
  23                 # use persistent connections
  24                 $gui_dbh = mysql_pconnect($gui_db_opt['host'],
  25                                                                   $gui_db_opt['user'],
  26                                                                   $gui_db_opt['passwd']);
  27         } else {
  28                 $gui_dbh = mysql_connect($gui_db_opt['host'],
  29                                                                   $gui_db_opt['user'],
  30                                                                   $gui_db_opt['passwd']);
  31         }
  32
  33         if($gui_dbh === false) return(false);
  34
  35         return(mysql_select_db($gui_db_opt['database']));
  36 }
  37
  38 # fetch a single row to associative array
  39 function sql_fetch_array($resource) {
  40         return(mysql_fetch_array($resource, MYSQL_ASSOC));
  41 }
  42
  43 function sql_escape_string($string)
  44 {
  45         return mysql_real_escape_string($string);
  46 }
  47
  48 # execute an SQL query with error handling
  49 function sql_exec_query($query) {
  50         global $gui_dbh, $DEBUG;
  51
  52         if(empty($query)) return(false);
  53
  54         if($gui_dbh === false) {
  55                 gui_db_connect();
  56         }
  57
  58         $result = mysql_query($query, $gui_dbh);
  59         if($result === false) {
  60                 echo "SQL query failed with the following error message;\n" .
  61                   mysql_error() . "\n";
  62                 if($DEBUG) echo "Query was:\n".$query."\n";
  63         }
  64
  65         return($result);
  66 }
  67
  68 function fetch_and_import()
  69 {
  70         global $gui_db_opt;
  71
  72         $version = get_db_version();
  73         if ($version === false) {
  74                 echo "Nothing to import\n";
  75                 return true;
  76         }
  77         if ($version >= $gui_db_opt['new_db_version']) {
  78                 # return if already imported and updated
  79                 echo "Schedules seems to be already imported.\n";
  80                 return true;
  81         }
  82
  83         $sql = "SELECT * FROM ".$gui_db_opt['database'].".auto_reports_scheduled";
  84
  85         $res = sql_exec_query($sql);
  86         if ($res === false) {
  87                 return false;
  88         }
  89         while ($row = sql_fetch_array($res)) {
  90                 import_schedule($row);
  91         }
  92
  93         # upgrade db version in old database
  94         upgrade_db_version($gui_db_opt['new_db_version']);
  95
  96         # truncate old table if all seems OK
  97         compare_and_truncate();
  98
  99         echo "Done importing old schedules\n";
 100         return true;
 101 }
 102
 103 function import_schedule($row)
 104 {
 105         # new database and table
 106         $database = "monitor_reports";
 107         $table = "scheduled_reports";
 108         unset($row['id']);
 109         $fields = implode(',', array_keys($row));
 110         $values = "'".implode("', '", array_values($row))."'";
 111         $sql = "INSERT INTO ".$database.".".$table." (".$fields.") VALUES(".$values.")";
 112         sql_exec_query($sql);
 113         #echo $sql."\n";
 114 }
 115
 116 function get_db_version()
 117 {
 118         global $gui_db_opt;
 119         $ok = db_exists($gui_db_opt['database']);
 120         if (!$ok)
 121                 return false;
 122
 123         $sql = "SELECT version FROM ".$gui_db_opt['database'].".auto_reports_db_version";
 124         $res = sql_exec_query($sql);
 125         if ($res === false) {
 126                 return false;
 127         }
 128         $row = sql_fetch_array($res);
 129         return isset($row['version']) ? $row['version'] : false;
 130 }
 131
 132 function db_exists($db=false)
 133 {
 134     if (empty($db))
 135         return false;
 136     global $gui_db_opt;
 137     $db_selected = mysql_select_db($db);
 138     if (!$db_selected) {
 139         return false;
 140     }
 141     return true;
 142 }
 143
 144 function upgrade_db_version($version=false)
 145 {
 146         global $gui_db_opt;
 147         if (empty($version)) {
 148                 $version = $gui_db_opt['new_db_version'];
 149         }
 150         $sql = "UPDATE ".$gui_db_opt['database'].".auto_reports_db_version SET version='".$version."'";
 151         sql_exec_query($sql);
 152 }
 153
 154 # check that we really have imported the old schedules
 155 # and truncate the old table in that case
 156 function compare_and_truncate()
 157 {
 158         global $gui_db_opt;
 159
 160         # new database and table
 161         $database = "monitor_reports";
 162         $table = "scheduled_reports";
 163
 164         # check nr of schedules in old database
 165         $sql = "SELECT COUNT(id) AS cnt FROM ".$gui_db_opt['database'].".auto_reports_scheduled";
 166         $res = sql_exec_query($sql);
 167         $cnt_old = 0;
 168         if ($res !== false) {
 169                 $row = sql_fetch_array($res);
 170                 $cnt_old = $row['cnt'];
 171         }
 172         if ($cnt_old) {
 173                 # only compare (and truncate) if the old table actually had any data
 174                 $sql = "SELECT COUNT(id) AS cnt FROM ".$database.".".$table;
 175                 $res = sql_exec_query($sql);
 176                 if ($res !== false) {
 177                         $row = sql_fetch_array($res);
 178                         $cnt_new = $row['cnt'];
 179                         if ($cnt_new >= $cnt_old) {
 180                                 # at least the same nr of schedules exists
 181                                 # go ahead and empty the old table
 182                                 $sql = "TRUNCATE TABLE ".$gui_db_opt['database'].".auto_reports_scheduled";
 183                                 sql_exec_query($sql);
 184                                 return true;
 185                         }
 186                 }
 187         }
 188         return false;
 189 }
 190
 191 $return = fetch_and_import() === true ? 0 :1 ;
 192 exit($return);
 193 ?>