# HG changeset patch
# User Matti Hamalainen <ccr@tnsp.org>
# Date 1386435412 -7200
# Node ID 3232f682f0d240efdf938674d6f000cb548f37fd
# Parent  d2a38070e18fe2867c4b0c827598445aa912dc38
Change how table schemas are defined, in preparation for future database
upgrading code.

diff -r d2a38070e18f -r 3232f682f0d2 createdb.php
--- a/createdb.php	Sat Dec 07 17:52:40 2013 +0200
+++ b/createdb.php	Sat Dec 07 18:56:52 2013 +0200
@@ -161,22 +161,112 @@
 );
 
 $sqlTables = array(
-  "dbmeta" => "key VARCHAR(32) PRIMARY KEY, vtype INT, vstr VARCHAR(128), vtext TEXT, vint INT, desc VARCHAR(128)",
+  //
+  // Database metadata
+  //
+  "dbmeta" => array(
+    array("key"          , "VARCHAR(32)", "PRIMARY KEY"),
+    array("vtype"        , "INTEGER"),
+    array("vstr"         , "VARCHAR(128)"),
+    array("vtext"        , "TEXT"),
+    array("vint"         , "INTEGER"),
+    array("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",
-  "entries" => "id INTEGER PRIMARY KEY AUTOINCREMENT, show_id INT DEFAULT 0, name VARCHAR(".SET_LEN_ENTRY_NAME."), author VARCHAR(".SET_LEN_ENTRY_AUTHOR."), compo_id INT DEFAULT NULL, filename VARCHAR(".SET_LEN_ENTRY_FILENAME.") DEFAULT NULL, info VARCHAR(".SET_LEN_ENTRY_INFO.") DEFAULT NULL",
+  //
+  // Site settings
+  //
+  "settings" => array(
+    array("key"          , "VARCHAR(32)", "PRIMARY KEY"),
+    array("vtype"        , "INTEGER"),
+    array("vstr"         , "VARCHAR(128)"),
+    array("vtext"        , "TEXT"),
+    array("vint"         , "INTEGER"),
+    array("desc"         , "VARCHAR(128)"),
+  ),
+
+  "news" => array(
+    array("id"           , "INTEGER", "PRIMARY KEY AUTOINCREMENT"),
+    array("utime"        , "INTEGER"),
+    array("title"        , "VARCHAR(".SET_LEN_NEWS_TITLE.")"),
+    array("text"         , "VARCHAR(".SET_LEN_NEWS_TEXT.")"),
+    array("author"       , "VARCHAR(".SET_LEN_NEWS_AUTHOR.")"),
+    array("persist"      , "INTEGER", "DEFAULT 0"),
+  ),
 
-  "attendees" => "id INTEGER PRIMARY KEY AUTOINCREMENT, regtime INT, name VARCHAR(".SET_LEN_USERNAME."), groups VARCHAR(".SET_LEN_GROUPS."), oneliner VARCHAR(".SET_LEN_ONELINER."), email VARCHAR(".SET_LEN_EMAIL."), key_id INT DEFAULT NULL",
+  "compos" => array(
+    array("id"           , "INTEGER", "PRIMARY KEY AUTOINCREMENT"),
+    array("name"         , "VARCHAR(".SET_LEN_COMPO_NAME.")"),
+    array("description"  , "VARCHAR(".SET_LEN_COMPO_DESC.")"),
+    array("visible"      , "INTEGER", "DEFAULT 0"),
+    array("voting"       , "INTEGER", "DEFAULT 0"),
+    array("showAuthors"  , "INTEGER", "DEFAULT 0"),
+    array("type"         , "INTEGER", "DEFAULT 0"),
+  ),
+  
+  "entries" => array(
+    array("id"           , "INTEGER", "PRIMARY KEY AUTOINCREMENT"),
+    array("show_id"      , "INTEGER", "DEFAULT 0"),
+    array("name"         , "VARCHAR(".SET_LEN_ENTRY_NAME.")"),
+    array("author"       , "VARCHAR(".SET_LEN_ENTRY_AUTHOR.")"),
+    array("compo_id"     , "INTEGER", "DEFAULT 0"),
+    array("filename"     , "VARCHAR(".SET_LEN_ENTRY_FILENAME.")", "DEFAULT NULL"),
+    array("info"         , "VARCHAR(".SET_LEN_ENTRY_INFO.")", "DEFAULT NULL"),
+    array("flags"        , "INTEGER", "DEFAULT 0"),
+  ),
 
-  "votekeys" => "id INTEGER PRIMARY KEY AUTOINCREMENT, key VARCHAR(64), active INT DEFAULT 0",
-  "votes" => "id INTEGER PRIMARY KEY AUTOINCREMENT, entry_id INT DEFAULT NULL, key_id INT DEFAULT NULL, value INT DEFAULT 0",
+  "attendees" => array(
+    array("id"           , "INTEGER", "PRIMARY KEY AUTOINCREMENT"),
+    array("regtime"      , "INTEGER"),
+    array("name"         , "VARCHAR(".SET_LEN_USERNAME.")"),
+    array("groups"       , "VARCHAR(".SET_LEN_GROUPS.")"),
+    array("oneliner"     , "VARCHAR(".SET_LEN_ONELINER.")"),
+    array("email"        , "VARCHAR(".SET_LEN_EMAIL.")"),
+    array("key_id"       , "INTEGER", "DEFAULT NULL"),
+  ),
+
+  "votekeys" => array(
+    array("id"           , "INTEGER", "PRIMARY KEY AUTOINCREMENT"),
+    array("key"          , "VARCHAR(".SET_LEN_VOTEKEY.")"),
+    array("active"       , "INTEGER", "DEFAULT 0"),
+  ),
 
-  "displayVars" => "key VARCHAR(32) PRIMARY KEY, vtype INT, vstr VARCHAR(128), vtext TEXT, vint INT, desc VARCHAR(128)",
-  "displaySlides" => "id INTEGER PRIMARY KEY AUTOINCREMENT, title VARCHAR(".SET_LEN_DISP_SLIDE_TITLE."), text VARCHAR(".SET_LEN_DISP_SLIDE_TEXT.")",
-  "rotationListData" => "id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(".SET_LEN_ROT_LIST_NAME.")",
-  "rotationListSlides" => "id INTEGER PRIMARY KEY AUTOINCREMENT, list_id INT DEFAULT 0, slide_id INT DEFAULT 0, order_num INT DEFAULT 0",
+  "votes" => array(
+    array("id"           , "INTEGER", "PRIMARY KEY AUTOINCREMENT"),
+    array("entry_id"     , "INTEGER", "DEFAULT NULL"),
+    array("key_id"       , "INTEGER", "DEFAULT 0"),
+    array("value"        , "INTEGER", "DEFAULT 0"),
+  ),
+
+  //
+  // Information system tables
+  //
+  "displayVars" => array(
+    array("key"          , "VARCHAR(32)", "PRIMARY KEY"),
+    array("vtype"        , "INTEGER"),
+    array("vstr"         , "VARCHAR(128)"),
+    array("vtext"        , "TEXT"),
+    array("vint"         , "INTEGER"),
+    array("desc"         , "VARCHAR(128)"),
+  ),
+
+  "displaySlides" => array(
+    array("id"           , "INTEGER", "PRIMARY KEY AUTOINCREMENT"),
+    array("title"        , "VARCHAR(".SET_LEN_DISP_SLIDE_TITLE.")"),
+    array("text"         , "VARCHAR(".SET_LEN_DISP_SLIDE_TEXT.")"),
+  ),
+
+  "rotationListData" => array(
+    array("id"           , "INTEGER", "PRIMARY KEY AUTOINCREMENT"),
+    array("name"         , "VARCHAR(".SET_LEN_ROT_LIST_NAME.")"),
+  ),
+  
+  "rotationListSlides" => array(
+    array("id"           , "INTEGER", "PRIMARY KEY AUTOINCREMENT"),
+    array("list_id"      , "INTEGER", "DEFAULT 0"),
+    array("slide_id"     , "INTEGER", "DEFAULT 0"),
+    array("order_num"    , "INTEGER", "DEFAULT 0"),
+  ),
 );
 
 $siteDisplayVars = array(
@@ -274,6 +364,17 @@
 //
 // Helper functions
 //
+function stGetTableSchema($data)
+{
+  $res = array();
+
+  foreach ($data as $col)
+    $res[] = implode(" ", $col);
+
+  return implode(",", $res);
+}
+
+
 function stCreateOneTable($dbh, $name, $schema)
 {
   return (stDBExecSQL($dbh, "CREATE TABLE IF NOT EXISTS ".$name." (".$schema.")") !== FALSE) ? TRUE : FALSE;
@@ -284,10 +385,10 @@
 {
   global $sqlTables;
   echo "Creating tables...\n";
-  foreach ($sqlTables as $name => $schema)
+  foreach ($sqlTables as $name => $schemaData)
   {
     echo " - '".$name."'\n";
-    if (!stCreateOneTable($dbh, $name, $schema) && !upgrade)
+    if (!stCreateOneTable($dbh, $name, stGetTableSchema($schemaData)) && !$upgrade)
       return FALSE;
   }
   return TRUE;
@@ -519,9 +620,11 @@
       if (!stAddSettings($inDB, $outDB, $siteDisplayVars, "displayVars", TRUE))
         exit;
 
-      
+      stAddSettings($inDB, $outDB, $dbMeta, "dbmeta", TRUE);
 
-      stAddSettings($inDB, $outDB, $dbMeta, "dbmeta", TRUE);
+      // Migrate other tables
+      if (!stMigrateTables($inDB, $outDB, array("settings", "displayVars", "dbmeta")))
+        exit;
 
       echo "Setting dbVersion.\n";
       stSetDBMeta($outDB, "dbVersion", $dbVersion);
diff -r d2a38070e18f -r 3232f682f0d2 msite.inc.php
--- a/msite.inc.php	Sat Dec 07 17:52:40 2013 +0200
+++ b/msite.inc.php	Sat Dec 07 18:56:52 2013 +0200
@@ -35,6 +35,7 @@
 define("SET_LEN_DISP_SLIDE_TEXT", 4096);
 define("SET_LEN_ROT_LIST_NAME", 128);
 
+define("SET_LEN_VOTEKEY", 64);
 //
 // Different voting modes
 //