Mercurial > hg > fapweb

changeset 502:d2a38070e18f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Partially refactor database upgrading .. not finished yet, and the conversion procedure does not convert everything.
author Matti Hamalainen <ccr@tnsp.org>
date Sat, 07 Dec 2013 17:52:40 +0200
parents 5f6e74b660df
children 3232f682f0d2
files createdb.php msitegen.inc.php
diffstat 2 files changed, 128 insertions(+), 109 deletions(-) [+]
line wrap: on
line diff
--- a/createdb.php	Sat Dec 07 17:51:35 2013 +0200
+++ b/createdb.php	Sat Dec 07 17:52:40 2013 +0200
@@ -274,27 +274,53 @@
 //
 // Helper functions
 //
-function stCreateOneTable($name, $schema)
+function stCreateOneTable($dbh, $name, $schema)
 {
-  return (stExecSQL("CREATE TABLE IF NOT EXISTS ".$name." (".$schema.")") !== FALSE) ? TRUE : FALSE;
+  return (stDBExecSQL($dbh, "CREATE TABLE IF NOT EXISTS ".$name." (".$schema.")") !== FALSE) ? TRUE : FALSE;
 }
 
 
-function stCreateTables($upgrade)
+function stCreateTables($dbh, $upgrade)
 {
   global $sqlTables;
   echo "Creating tables...\n";
   foreach ($sqlTables as $name => $schema)
   {
     echo " - '".$name."'\n";
-    if (!stCreateOneTable($name, $schema) && !upgrade)
+    if (!stCreateOneTable($dbh, $name, $schema) && !upgrade)
       return FALSE;
   }
   return TRUE;
 }
 
 
-function stAddSettings($settings, $table, $upgrade)
+$upgradeMappings = array(
+//  "" => array("key" => "", value => ""),
+);
+
+
+function stUpgradeMap($type, $name, $value = FALSE)
+{
+  global $upgradeMappings;
+
+  if (isset($upgradeMappings[$name]))
+  {
+    if (isset($upgradeMappings[$name][$type]))
+    {
+    }
+    else
+      die("Upgrade failed due to missing or invalid upgrade definition: ".$type." ".$name." from version X to Y.\n");
+  }
+  else
+  switch ($type)
+  {
+    case "key": return $name;
+    case "value": return $value;
+  }
+}
+
+
+function stAddSettings($inDB, $outDB, $settings, $table, $upgrade)
 {
   echo ($upgrade ? "Adding settings to" : "Upgrading settings in")." '".$table."' table.";
 
@@ -309,28 +335,28 @@
       default:      die("Invalid type in default settings '".$key."', type=".$data[0]."\n");
     }
 
-    if ($upgrade && ($res = stFetchSQL(stPrepareSQL("SELECT * FROM ".$table." WHERE key=%s", $key))) !== FALSE)
+    $inKey = stUpgradeMap("key", $key);
+
+    if ($upgrade && ($res = stDBFetchSQL($inDB, stDBPrepareSQL($inDB, "SELECT * FROM ".$table." WHERE key=%s", $inKey))) !== FALSE)
     {
       echo ".";
 
       if ($res["vtype"] != $data[0])
-      {
         die("Oops! Data type of '".$key."' does not match in table '".$table.". DB upgrade failed.\n");
-      }
 
-      $sql = stPrepareSQL(
-        "UPDATE ".$table." SET desc=%s WHERE key=%s",
-        $data[2], $key);
+      $sql = stDBPrepareSQL($outDB,
+        "INSERT INTO ".$table." (key,vtype,".$var.",desc) VALUES (%s,%d,".$type.",%s)",
+        $key, $data[0], stUpgradeMap("value", $key, $res[$var]), $data[2]);
     }
     else
     {
       echo "+";
-      $sql = stPrepareSQL(
+      $sql = stDBPrepareSQL($outDB,
         "INSERT INTO ".$table." (key,vtype,".$var.",desc) VALUES (%s,%d,".$type.",%s)",
         $key, $data[0], $data[1], $data[2]);
     }
 
-    if (stExecSQL($sql) === FALSE)
+    if (stDBExecSQL($outDB, $sql) === FALSE)
       return FALSE;
   }
   echo "\n";
@@ -338,22 +364,7 @@
 }
 
 
-function stRenameSettings($mapping, $table)
-{
-  echo "Upgrading '".$table."' table.\n";
-
-  foreach ($mapping as $from => $to)
-  {
-    $sql = stPrepareSQL(
-      "UPDATE ".$table." SET key=%s WHERE key=%s",
-      $to, $from);
-
-    stExecSQL($sql);
-  }
-}
-
-
-function stAddTestData()
+function stAddTestData($dbh)
 {
   global $siteTestData;
   echo "Adding test data.\n";
@@ -366,11 +377,11 @@
       for ($n = 2; $n < count($data); $n++)
       {
         $arr = array_merge(
-          array("INSERT INTO ".$table." (".$data[0].") VALUES (".$data[1].")"),
+          array($dbh, "INSERT INTO ".$table." (".$data[0].") VALUES (".$data[1].")"),
           $data[$n]);
 
-        $sql = call_user_func_array('stPrepareSQL', $arr);
-        stExecSQL($sql);
+        $sql = call_user_func_array('stDBPrepareSQL', $arr);
+        stDBExecSQL($dbh, $sql);
       }
     }
     else
@@ -381,6 +392,38 @@
 }
 
 
+function stMigrateTables($inDB, $outDB, $excluded)
+{
+  global $sqlTables;
+  echo "Migrating tables...\n";
+  foreach ($sqlTables as $name => $schema)
+  if (!in_array($name, $excluded))
+  {
+    echo " - '".$name."'\n";
+  }
+  return TRUE;
+}
+
+
+function stSetDBPermissions($spec)
+{
+  if (substr($spec, 0, 7) == "sqlite:")
+  {
+    $filename = substr($spec, 7);
+    echo
+      "NOTICE! It seems you have SQLite database in use, changing permission ".
+      "of the target file '".$filename."' to 0600, for security. You may have to ".
+      "loosen up that for the things to actually work, but be careful. Having your ".
+      "database world-readable in the web is NOT good.\n";
+
+    if (chmod($filename, 0600) === FALSE)
+    {
+      echo "ERROR! Could not set permissions of '".$filename."!\n";
+    }
+  }
+}
+
+
 //
 // Main program starts
 //
@@ -398,18 +441,20 @@
   "\n".
   "  test              Like new, but add initial test data.\n".
   "\n".
-  "  upgrade [dbspec]  Upgrade current database, if possible.\n".
+  "  upgrade <output dbspec>\n".
+  "                    Upgrade current database, if possible.\n".
+  "                    Output to new database (DO NOT USE SAME as current!)\n".
   "\n";
   exit;
 }
 
 
 // Try to connect to database
-$spec = $siteSettings["sqlDB"];
-if (!stCLIConnectSQLDB($spec))
-  die("Could not connect to SQL database '".$spec."'.\n");
+$inSpec = stGetSetting("sqlDB");
+if (($inDB = stConnectSQLDBSpec($inSpec)) === false)
+  die("Could not connect to SQL database '".$inSpec."'.\n");
 
-echo "Using database spec '".$spec."'.\n";
+echo "Using INPUT database spec '".$inSpec."'.\n";
 
 
 // Act according to specified command
@@ -420,33 +465,25 @@
     $addTestData = TRUE;
 
   case "new":
-    if (stCreateTables(FALSE))
+    if (stCreateTables($inDB, FALSE))
     {
-      stAddSettings($dbMeta, "dbmeta", FALSE);
-      stAddSettings($siteDefaults, "settings", FALSE);
-      stAddSettings($siteDisplayVars, "displayVars", FALSE);
+      stAddSettings($inDB, $inDB, $dbMeta, "dbmeta", FALSE);
+      stAddSettings($inDB, $inDB, $siteDefaults, "settings", FALSE);
+      stAddSettings($inDB, $inDB, $siteDisplayVars, "displayVars", FALSE);
     }
     
-      stAddTestData();
     if ($addTestData)
+      stAddTestData($inDB);
 
-    if (substr($spec, 0, 7) == "sqlite:")
-    {
-      $filename = substr($spec, 7);
-      echo "NOTICE! It seems you have SQLite database in use, changing permission ".
-      "of the target file '".$filename."' to 0600, for security. You may have to ".
-      "loosen up that for the things to actually work, but be careful. Having your ".
-      "database world-readable in the web is NOT good.\n";
-
-      if (chmod($filename, 0600) === FALSE)
-      {
-        echo "ERROR! Could not set permissions!\n";
-      }
-    }
+    stSetDBPermissions($inSpec);
     break;
 
   case "upgrade":
-    if (($currVersion = stGetDBMeta("dbVersion")) === FALSE)
+    //
+    // Attempt to upgrade database
+    //
+    // Check the current version first ...
+    if (($currVersion = stGetDBMeta($inDB, "dbVersion")) === FALSE)
       $currVersion = -1;
 
     if ($currVersion == $dbVersion)
@@ -455,35 +492,43 @@
     }
     else
     {
-      echo "Database at version ".$currVersion.", upgrading to ".$dbVersion."\n";
-      // Possibly bail out incompatible upgrades here
-      if ($currVersion < 6)
-        stExecSQL("DROP TABLE votes");
+      // Okay, we shall create an upgraded version ..
+      if ($argc < 3)
+        die("Usage: ".$argv[0]." upgrade <output dbspec>\n");
 
-      if ($currVersion < 9)
-      {
-        stExecSQL("DROP TABLE displayVars");
-        stExecSQL("DROP TABLE displayListSlides");
-        stExecSQL("DROP TABLE displayListData");
-      }
+      $outSpec = stCArg(2);
+      if ($outSpec == $inSpec)
+        die("The input and output databases CAN NOT BE SAME.\n");
+      
+      if (($outDB = stConnectSQLDBSpec($outSpec)) === false)
+        die("Could not connect to SQL database '".$outSpec."'.\n");
+
+      echo "Database at version ".$currVersion.", upgrading to ".$dbVersion."\n";
+      echo "Using OUTPUT database spec '".$outSpec."'.\n";
+
+      // Possibly bail out incompatible upgrades here
       
       // Create tables
-      if (!stCreateTables(TRUE))
+      if (!stCreateTables($outDB, TRUE))
+        exit;
+
+      // Migrate data from setting tables ..
+      if (!stAddSettings($inDB, $outDB, $siteDefaults, "settings", TRUE))
+        exit;
+
+      if (!stAddSettings($inDB, $outDB, $siteDisplayVars, "displayVars", TRUE))
         exit;
 
-      // Do renames etc. here
+      
 
-      // Do upgrading of setting descs
-      if (!stAddSettings($siteDefaults, "settings", TRUE))
-        exit;
+      stAddSettings($inDB, $outDB, $dbMeta, "dbmeta", TRUE);
 
-      if (!stAddSettings($siteDisplayVars, "displayVars", TRUE))
-        exit;
+      echo "Setting dbVersion.\n";
+      stSetDBMeta($outDB, "dbVersion", $dbVersion);
+      echo "Upgrade complete.\n";
 
-      stAddSettings($dbMeta, "dbmeta", TRUE);
-      
-      stSetDBMeta("dbVersion", $dbVersion);
-      echo "Upgrade complete.\n";
+      stSetDBPermissions($inSpec);
+      stSetDBPermissions($outSpec);
     }
     break;
 
--- a/msitegen.inc.php	Sat Dec 07 17:51:35 2013 +0200
+++ b/msitegen.inc.php	Sat Dec 07 17:52:40 2013 +0200
@@ -736,48 +736,22 @@
 }
 
 
-function stCSQLError($sql)
-{
-  global $db;
-  die("Error executing SQL query: ".implode("; ", $db->errorInfo())." in statement \"".$sql."\"\n");
-  exit;
-}
-
-
-function stCLIConnectSQLDB($dbspec)
+function stGetDBMeta($dbh, $name)
 {
-  global $db;
-  try {
-    $db = new PDO($dbspec);
-  }
-  catch (PDOException $e) {
-    error_log("Could not connect to SQL database '".$dbspec."': ".$e->getMessage().".");
-    return FALSE;
-  }
-  return TRUE;
-}
-
-
-function stGetDBMeta($name)
-{
-  global $db;
-
-  if (($item = stFetchSQL("SELECT * FROM dbmeta WHERE key=".$db->quote($name))) === FALSE)
+  if (($item = stDBFetchSQL($dbh, "SELECT * FROM dbmeta WHERE key=".$dbh->quote($name))) === FALSE)
     return FALSE;
   
   return stGetSQLSettingData($item);
 }
 
 
-function stSetDBMeta($name, $value)
+function stSetDBMeta($dbh, $name, $value)
 {
-  global $db;
-
-  if (($item = stFetchSQL("SELECT * FROM dbmeta WHERE key=".$db->quote($name))) === FALSE)
+  if (($item = stDBFetchSQL($dbh, "SELECT * FROM dbmeta WHERE key=".$dbh->quote($name))) === FALSE)
     return FALSE;
 
-  $sql = "UPDATE dbmeta SET ".stGetSettingSQL($item, $value)." WHERE key=".$db->quote($name);
-  return stExecSQL($sql);
+  $sql = "UPDATE dbmeta SET ".stGetSettingSQL($item, $value)." WHERE key=".$dbh->quote($name);
+  return stDBExecSQL($dbh, $sql);
 }