--- a/createdb.php	Sat Nov 16 06:13:17 2013 +0200
+++ b/createdb.php	Sat Nov 16 06:20:37 2013 +0200
@@ -3,6 +3,7 @@
 require_once "mconfig.inc.php";
 require_once "msite.inc.php";
 
+
 // Check if we are running from commandline or not
 if (php_sapi_name() != "cli" || !empty($_SERVER["REMOTE_ADDR"]))
 {
@@ -10,6 +11,12 @@
   die();
 }
 
+$dbVersion = 3;
+
+$dbMeta = array(
+  "dbVersion" => array(VT_INT, $dbVersion, "Database version"),
+);
+
 
 // The defaults we put in
 $siteDefaults = array(
@@ -153,6 +160,8 @@
 );
 
 $sqlTables = array(
+  "dbmeta" => "key VARCHAR(32) PRIMARY KEY, vtype INT, vstr VARCHAR(128), vtext TEXT, vint INT, desc VARCHAR(128)",
+
   "settings" => "key VARCHAR(32) PRIMARY KEY, vtype INT, vstr VARCHAR(128), vtext TEXT, vint INT, desc VARCHAR(128)",
   "news" => "id INTEGER PRIMARY KEY AUTOINCREMENT, utime INT, title VARCHAR(".SET_LEN_NEWS_TITLE."), text VARCHAR(".SET_LEN_NEWS_TEXT."), author VARCHAR(".SET_LEN_NEWS_AUTHOR."), persist INT DEFAULT 0",
   "compos" => "id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(".SET_LEN_COMPO_NAME."), description VARCHAR(".SET_LEN_COMPO_DESC."), visible INT DEFAULT 0, voting INT DEFAULT 0, showAuthors INT DEFAULT 0",
@@ -305,29 +314,52 @@
 }
 
 
-function stCreateOneTable($name, $schema)
+function stGetDBMeta($name)
 {
-  return (stExecSQL("CREATE TABLE ".$name." (".$schema.")") !== FALSE) ? TRUE : FALSE;
+  global $db;
+
+  if (($item = stFetchSQL("SELECT * FROM dbmeta WHERE key=".$db->quote($name))) === FALSE)
+    return FALSE;
+  
+  return stGetSQLSettingData($item);
 }
 
 
-function stCreateTables()
+function stSetDBMeta($name, $value)
+{
+  global $db;
+
+  if (($item = stFetchSQL("SELECT * FROM dbmeta WHERE key=".$db->quote($name))) === FALSE)
+    return FALSE;
+
+  $sql = "UPDATE dbmeta SET ".stGetSettingSQL($item, $value)." WHERE key=".$db->quote($name);
+  return stExecSQL($sql);
+}
+
+
+function stCreateOneTable($name, $schema)
+{
+  return (stExecSQL("CREATE TABLE IF NOT EXISTS ".$name." (".$schema.")") !== FALSE) ? TRUE : FALSE;
+}
+
+
+function stCreateTables($upgrade)
 {
   global $sqlTables;
   echo "Creating tables...\n";
   foreach ($sqlTables as $name => $schema)
   {
     echo " - '".$name."'\n";
-    if (!stCreateOneTable($name, $schema))
+    if (!stCreateOneTable($name, $schema) && !upgrade)
       return FALSE;
   }
   return TRUE;
 }
 
 
-function stAddSettings($settings, $table)
+function stAddSettings($settings, $table, $upgrade)
 {
-  echo "Adding settings to '".$table."' table.\n";
+  echo ($upgrade ? "Adding settings to" : "Upgrading settings in")." '".$table."' table.";
 
   foreach ($settings as $key => $data)
   {
@@ -340,9 +372,44 @@
       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)
+    {
+      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);
+    }
+    else
+    {
+      echo "+";
+      $sql = stPrepareSQL(
+        "INSERT INTO ".$table." (key,vtype,".$var.",desc) VALUES (%s,%d,".$type.",%s)",
+        $key, $data[0], $data[1], $data[2]);
+    }
+
+    if (stExecSQL($sql) === FALSE)
+      return FALSE;
+  }
+  echo "\n";
+  return TRUE;
+}
+
+
+function stRenameSettings($mapping, $table)
+{
+  echo "Upgrading '".$table."' table.\n";
+
+  foreach ($mapping as $from => $to)
+  {
     $sql = stPrepareSQL(
-      "INSERT INTO ".$table." (key,vtype,".$var.",desc) VALUES (%s,%d,".$type.",%s)",
-      $key, $data[0], $data[1], $data[2]);
+      "UPDATE ".$table." SET key=%s WHERE key=%s",
+      $to, $from);
 
     stExecSQL($sql);
   }
@@ -388,6 +455,8 @@
   "                    or default to the one in mconfig.inc.php\n".
   "\n".
   "  test [dbspec]     Like new, but add initial test data.\n".
+  "\n".
+  "  upgrade [dbspec]  Upgrade current database, if possible.\n".
   "\n";
   exit;
 }
@@ -395,8 +464,9 @@
 
 if (!isset($siteSettings["voteKeyMode"]) || $siteSettings["voteKeyMode"] < 0)
 {
-  echo "FATAL ERROR! VoteKeyMode not set in site settings! This setting ".
-  "MUST be defined and should not be changed after database creation.\n";
+  echo
+    "FATAL ERROR! VoteKeyMode not set in site settings! This setting ".
+    "MUST be defined and should not be changed after database creation.\n";
   exit;
 }
 
@@ -420,10 +490,11 @@
     $addData = TRUE;
 
   case "new":
-    if (stCreateTables())
+    if (stCreateTables(FALSE))
     {
-      stAddSettings($siteDefaults, "settings");
-      stAddSettings($siteDisplayVars, "displayVars");
+      stAddSettings($dbMeta, "dbmeta", FALSE);
+      stAddSettings($siteDefaults, "settings", FALSE);
+      stAddSettings($siteDisplayVars, "displayVars", FALSE);
     }
     
     if ($addData)
@@ -444,6 +515,39 @@
     }
     break;
 
+  case "upgrade":
+    if (($currVersion = stGetDBMeta("dbVersion")) === FALSE)
+      $currVersion = -1;
+
+    if ($currVersion == $dbVersion)
+    {
+      echo "Database is already version ".$dbVersion.", no upgrading needed.\n";
+    }
+    else
+    {
+      echo "Database at version ".$currVersion.", upgrading to ".$dbVersion."\n";
+      // Possibly bail out incompatible upgrades here
+      
+      // Create tables
+      if (!stCreateTables(TRUE))
+        exit;
+
+      // Do renames etc. here
+
+      // Do upgrading of setting descs
+      if (!stAddSettings($siteDefaults, "settings", TRUE))
+        exit;
+
+      if (!stAddSettings($siteDisplayVars, "displayVars", TRUE))
+        exit;
+
+      stAddSettings($dbMeta, "dbmeta", TRUE);
+      
+      stSetDBMeta("dbVersion", $dbVersion);
+      echo "Upgrade complete.\n";
+    }
+    break;
+
   default:
     echo "ERROR! Invalid operation mode '".stCArg(1)."'.\n";
     break;