Mercurial > hg > fapweb
changeset 503:3232f682f0d2
Change how table schemas are defined, in preparation for future database
upgrading code.
| author | Matti Hamalainen <ccr@tnsp.org> |
|---|---|
| date | Sat, 07 Dec 2013 18:56:52 +0200 |
| parents | d2a38070e18f |
| children | a491865e0684 |
| files | createdb.php msite.inc.php |
| diffstat | 2 files changed, 120 insertions(+), 16 deletions(-) [+] |
line wrap: on
line diff
--- 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);