Mercurial > hg > fapweb
changeset 542:8c1a53532be0
Rename various database tables and column names for better SQL compatibility.
| author | Matti Hamalainen <ccr@tnsp.org> |
|---|---|
| date | Sun, 15 Dec 2013 23:48:14 +0200 |
| parents | e5a088dd3a4b |
| children | 1f75523293ad |
| files | admajax.php admin.php managedb.php msite.inc.php showajax.php |
| diffstat | 5 files changed, 60 insertions(+), 51 deletions(-) [+] |
line wrap: on
line diff
--- a/admajax.php Sun Dec 15 23:46:41 2013 +0200 +++ b/admajax.php Sun Dec 15 23:48:14 2013 +0200 @@ -30,7 +30,7 @@ stChkRequestItem("voting", $fake, array(CHK_TYPE, VT_BOOL, "Invalid data.") ) && - stChkRequestItem("showAuthors", $fake, + stChkRequestItem("show_authors", $fake, array(CHK_TYPE, VT_BOOL, "Invalid data.") ); } @@ -85,7 +85,7 @@ " <h2>#".$id." - ".chentities($item["name"])."</h2>\n". " ".stGetFormTextInput(40, SET_LEN_COMPO_NAME, "name", $id, $prefix, $item["name"])."\n". " ".stGetFormCheckBoxInput("visible", $id, $prefix, $item["visible"], "Visible")."\n". - " ".stGetFormCheckBoxInput("showAuthors", $id, $prefix, $item["showAuthors"], "Show authors")."\n". + " ".stGetFormCheckBoxInput("show_authors", $id, $prefix, $item["show_authors"], "Show authors")."\n". " ".stGetFormCheckBoxInput("voting", $id, $prefix, $item["voting"], "Enable voting")."<br />\n". " ".stGetFormTextArea(5, 60, "description", $id, $prefix, $item["description"])."\n<br />\n". " ".stGetFormButtonInput("update", $id, $prefix, "Update", "updateCompo(".$id.")")."\n"; @@ -267,9 +267,9 @@ function stGetInfoRotationLists($indent, $outer) { $sql = - "SELECT rotationListData.*, ". - "(SELECT COUNT(*) FROM rotationListSlides WHERE list_id=rotationListData.id) AS nslides ". - "FROM rotationListData ". + "SELECT rot_list_data.*, ". + "(SELECT COUNT(*) FROM rot_list_slides WHERE list_id=rot_list_data.id) AS nslides ". + "FROM rot_list_data ". "ORDER BY id DESC"; $str = stGetOptionListStart("ctrlRotationLists", $indent, $outer); @@ -295,7 +295,7 @@ { $str = stGetOptionListStart("ctrlDisplaySlides", $indent, $outer); - $sql = "SELECT * FROM displaySlides ORDER BY id DESC"; + $sql = "SELECT * FROM display_slides ORDER BY id DESC"; if (($res = stExecSQL($sql)) !== false) { foreach ($res as $item) @@ -309,7 +309,7 @@ function stGetInfoRotationListEditFull($indent, $outer, $list_id) { - $sql = stPrepareSQL("SELECT * FROM rotationListData WHERE id=%d", $list_id); + $sql = stPrepareSQL("SELECT * FROM rot_list_data WHERE id=%d", $list_id); if (($data = stFetchSQL($sql)) === false) return ""; @@ -321,7 +321,7 @@ "<div>Available slides:</div>\n". stGetOptionListStart("ctrlEDDisplaySlides", $indent." ", TRUE); - $sql = "SELECT * FROM displaySlides"; + $sql = "SELECT * FROM display_slides"; if (($res = stExecSQL($sql)) !== false) { foreach ($res as $item) @@ -348,10 +348,10 @@ function stGetInfoRotationListEditData($indent, $outer, $list_id) { $sql = stPrepareSQL( - "SELECT displaySlides.*,rotationListSlides.order_num FROM displaySlides ". - "LEFT JOIN rotationListSlides ON displaySlides.id=rotationListSlides.slide_id ". - "WHERE rotationListSlides.list_id=%d ". - "ORDER BY rotationListSlides.order_num DESC", + "SELECT display_slides.*,rot_list_slides.order_num FROM display_slides ". + "LEFT JOIN rot_list_slides ON display_slides.id=rot_list_slides.slide_id ". + "WHERE rot_list_slides.list_id=%d ". + "ORDER BY rot_list_slides.order_num DESC", $list_id); $str = stGetOptionListStart("ctrlEDRotationList", $indent, $outer); @@ -372,7 +372,7 @@ function stGetInfoActiveRotationList($indent, $outer) { - $sql = stPrepareSQL("SELECT * FROM rotationListData WHERE id=%d", + $sql = stPrepareSQL("SELECT * FROM rot_list_data WHERE id=%d", stGetDisplayVar("rotateList")); $str = $indent.($outer ? "<div id=\"ctrlActiveRotationList\">" : ""). @@ -396,7 +396,7 @@ $indent." <form method=\"post\" action=\"\" onsubmit=\"return updateDisplaySlide(".$slide_id.")\">\n". $indent." <div class=\"ctrlTitle\">Edit display slide</div>\n"; - $sql = stPrepareSQL("SELECT * FROM displaySlides WHERE id=%d", $slide_id); + $sql = stPrepareSQL("SELECT * FROM display_slides WHERE id=%d", $slide_id); if (($slide = stFetchSQL($sql)) !== false) { $str .= @@ -660,7 +660,7 @@ if (stChkRequestItem("id", $slide_id, array(CHK_TYPE, VT_INT, "Invalid data.")) && stChkRequestItem("duration", $slide_dur, array(CHK_RANGE, VT_INT, array(1, 60), "Invalid duration range, should be 1-60 min."))) { - $sql = stPrepareSQL("SELECT * FROM displaySlides WHERE id=%d", $slide_id); + $sql = stPrepareSQL("SELECT * FROM display_slides WHERE id=%d", $slide_id); if (($slide = stFetchSQL($sql)) !== false) { stSetDisplayVar("tempDuration", $slide_dur); @@ -685,10 +685,10 @@ case "copyDisplaySlide": if (stChkRequestItem("id", $slide_id, array(CHK_TYPE, VT_INT, "Invalid data."))) { - $sql = stPrepareSQL("SELECT * FROM displaySlides WHERE id=%d", $slide_id); + $sql = stPrepareSQL("SELECT * FROM display_slides WHERE id=%d", $slide_id); if (($slide = stFetchSQL($sql)) !== false) { - $sql = stPrepareSQL("INSERT INTO displaySlides (title,text) VALUES (%s,%s)", + $sql = stPrepareSQL("INSERT INTO display_slides (title,text) VALUES (%s,%s)", $slide["title"]." (copy)", $slide["text"]); if (stExecSQLCond($sql, "Display slide copied.") !== false) @@ -703,7 +703,7 @@ break; case "newDisplaySlide": - $sql = stPrepareSQL("INSERT INTO displaySlides (title) VALUES (%s)", "New slide"); + $sql = stPrepareSQL("INSERT INTO display_slides (title) VALUES (%s)", "New slide"); if (stExecSQLCond($sql, "Display slide created.") !== false) { $slide_id = $db->lastInsertId(); @@ -721,7 +721,7 @@ array(CHK_LTEQ, VT_STR, SET_LEN_DISP_SLIDE_TEXT, "Slide content too long.") )) { - $sql = stPrepareSQLUpdate("displaySlides", + $sql = stPrepareSQLUpdate("display_slides", "WHERE id=".$slide_id, array( "title" => "S", @@ -736,21 +736,21 @@ case "deleteDisplaySlide": if (stChkRequestItem("id", $slide_id, array(CHK_TYPE, VT_INT, "Invalid data."))) { - $sql = stPrepareSQL("DELETE FROM displaySlides WHERE id=%d", $slide_id); + $sql = stPrepareSQL("DELETE FROM display_slides WHERE id=%d", $slide_id); stExecSQLCond($sql, "Slide deleted."); - $sql = stPrepareSQL("DELETE FROM rotationListSlides WHERE slide_id=%d", $slide_id); + $sql = stPrepareSQL("DELETE FROM rot_list_slides WHERE slide_id=%d", $slide_id); stExecSQLCond($sql, "Slide list refs deleted."); stDisplayUpdated(); } break; case "newRotationList": - $sql = stPrepareSQL("INSERT INTO rotationListData (name) VALUES (%s)", "New list #"); + $sql = stPrepareSQL("INSERT INTO rot_list_data (name) VALUES (%s)", "New list #"); if (stExecSQLCond($sql, "Rotation list created.") !== false) { $list_id = $db->lastInsertId(); - $sql = stPrepareSQL("UPDATE rotationListData SET name=%s WHERE id=%d", + $sql = stPrepareSQL("UPDATE rot_list_data SET name=%s WHERE id=%d", "New list #".$list_id, $list_id); if (stExecSQLCond($sql, "OK!") !== false) @@ -764,7 +764,7 @@ array(CHK_GTEQ, VT_STR, 3, "Rotation list name too short."), array(CHK_LTEQ, VT_STR, SET_LEN_ROT_LIST_NAME, "Rotation list name too long."))) { - $sql = stPrepareSQLUpdate("rotationListData", + $sql = stPrepareSQLUpdate("rot_list_data", "WHERE id=".$list_id, array( "name" => "S", @@ -779,10 +779,10 @@ case "deleteRotationList": if (stChkRequestItem("id", $list_id, array(CHK_TYPE, VT_INT, "Invalid data."))) { - $sql = stPrepareSQL("DELETE FROM rotationListData WHERE id=%d", $list_id); + $sql = stPrepareSQL("DELETE FROM rot_list_data WHERE id=%d", $list_id); stExecSQLCond($sql, "List data deleted."); - $sql = stPrepareSQL("DELETE FROM rotationListSlides WHERE list_id=%d", $list_id); + $sql = stPrepareSQL("DELETE FROM rot_list_slides WHERE list_id=%d", $list_id); stExecSQLCond($sql, "List slide refs deleted."); stDisplayUpdated(); } @@ -801,9 +801,9 @@ if (stChkRequestItem("list_id", $list_id, array(CHK_TYPE, VT_INT, "Invalid data.")) && stChkRequestItem("slide_id", $slide_id, array(CHK_TYPE, VT_INT, "Invalid data."))) { - $nslides = stFetchSQLColumn(stPrepareSQL("SELECT COUNT(*) FROM rotationListSlides WHERE list_id=%d", $list_id)); + $nslides = stFetchSQLColumn(stPrepareSQL("SELECT COUNT(*) FROM rot_list_slides WHERE list_id=%d", $list_id)); $sql = stPrepareSQL( - "INSERT INTO rotationListSlides (list_id,slide_id,order_num) VALUES (%d,%d,%d)", + "INSERT INTO rot_list_slides (list_id,slide_id,order_num) VALUES (%d,%d,%d)", $list_id, $slide_id, $nslides+1); if (stExecSQLCond($sql, "Slide added to list.")) @@ -819,7 +819,7 @@ stChkRequestItem("slide_id", $slide_id, array(CHK_TYPE, VT_INT, "Invalid data.")) && stChkRequestItem("order_num", $order_num, array(CHK_TYPE, VT_INT, "Invalid data."))) { - $sql = stPrepareSQL("DELETE FROM rotationListSlides WHERE list_id=%d AND slide_id=%d AND order_num=%d", + $sql = stPrepareSQL("DELETE FROM rot_list_slides WHERE list_id=%d AND slide_id=%d AND order_num=%d", $list_id, $slide_id, $order_num); if (stExecSQLCond($sql, "List slide refs deleted.")) @@ -1362,7 +1362,7 @@ if ($type == "compo" && stValidateRequestCompoData(FALSE)) { $sql = stPrepareSQL( - "INSERT INTO compos (name,description,visible,voting,showAuthors) VALUES (%S,%Q,0,0,0)", + "INSERT INTO compos (name,description,visible,voting,show_authors) VALUES (%S,%Q,0,0,0)", "name", "description"); stExecSQLCond($sql, "OK, compo added."); @@ -1446,7 +1446,7 @@ "description" => "Q", "visible" => "B", "voting" => "B", - "showAuthors" => "B", + "show_authors" => "B", )); stExecSQLCond($sql, "OK, compo updated.");
--- a/admin.php Sun Dec 15 23:46:41 2013 +0200 +++ b/admin.php Sun Dec 15 23:48:14 2013 +0200 @@ -362,7 +362,7 @@ function updateCompo(id) { - var args = jsMakePostArgs({"name":1, "description":1, "visible":3, "voting":3, "showAuthors":3}, "co", id); + var args = jsMakePostArgs({"name":1, "description":1, "visible":3, "voting":3, "show_authors":3}, "co", id); var msuccess = function(txt) {
--- a/managedb.php Sun Dec 15 23:46:41 2013 +0200 +++ b/managedb.php Sun Dec 15 23:48:14 2013 +0200 @@ -5,7 +5,8 @@ stCheckCLIExec(); -$dbVersion = 11; +$dbVersion = 12; +$currVersion = -1; $dbMeta = array( "dbVersion" => array(VT_INT, $dbVersion, "Database version"), @@ -199,7 +200,7 @@ array("description" , "VARCHAR(".SET_LEN_COMPO_DESC.")"), array("visible" , "INT", "DEFAULT 0"), array("voting" , "INT", "DEFAULT 0"), - array("showAuthors" , "INT", "DEFAULT 0"), + array("show_authors" , "INT", "DEFAULT 0"), array("type" , "INT", "DEFAULT 0"), array("path" , "VARCHAR(".SET_LEN_COMPO_PATH.")"), ), @@ -243,7 +244,7 @@ // Party information system tables - "displayVars" => array( + "display_vars" => array( array("key" , "VARCHAR(32)", "PRIMARY KEY"), array("vtype" , "INT"), array("vstr" , "VARCHAR(128)"), @@ -252,18 +253,18 @@ array("sdesc" , "VARCHAR(128)"), ), - "displaySlides" => array( + "display_slides" => 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( + "rot_list_data" => array( array("id" , "INTEGER", "PRIMARY KEY", "AUTOINCREMENT"), array("name" , "VARCHAR(".SET_LEN_ROT_LIST_NAME.")"), ), - "rotationListSlides" => array( + "rot_list_slides" => array( array("id" , "INTEGER", "PRIMARY KEY", "AUTOINCREMENT"), array("list_id" , "INT", "DEFAULT 0"), array("slide_id" , "INT", "DEFAULT 0"), @@ -346,7 +347,7 @@ array("Fungiform 2", "mfx", 3, "mfx-fungiform2.lzh", "OCS-only"), ), - "displaySlides" => array( + "display_slides" => array( "title,text", "%s,%s", array("Next Up 4k", "<h1>Next up: 4k intro</h1><h2>4k intro compo is about to begin</h2>... in about 10 minutes."), @@ -354,14 +355,14 @@ array("Gentle Eye mainos", "<b>Buy Amiga stuff!</b><br />Gentle Eye Oy is selling Amiga-related stuff near organizer desk!"), ), - "rotationListData" => array( + "rot_list_data" => array( "name", "%s", array("Main rotation"), array("Next Up"), ), - "rotationListSlides" => array( + "rot_list_slides" => array( "list_id,slide_id", "%d,%d", array(1,2), @@ -413,7 +414,7 @@ default: die("Don't know how to handle PDO driver '".$driver."' yet.\n"); } - + $res[] = implode(" ", $tmp); } @@ -423,6 +424,7 @@ function stCreateOneTable($dbh, $name, $schema) { + echo "CREATE TABLE ".$name ." (".$schema.")\n"; return (stDBExecSQL($dbh, "CREATE TABLE ".$name." (".$schema.")") !== FALSE) ? TRUE : FALSE; } @@ -444,6 +446,13 @@ $upgradeMappings = array( // "" => array("key" => "", value => ""), + 12 => array( + "show_authors" => array("key" => "showAuthors"), + "display_vars" => array("table" => "displayVars"), + "display_slides" => array("table" => "displaySlides"), + "rot_list_data" => array("table" => "rotationListData"), + "rot_list_slides" => array("table" => "rotationListSlides"), + ), ); @@ -729,7 +738,7 @@ { stAddSettings($inDB, $inDB, $dbMeta, "dbmeta", FALSE); stAddSettings($inDB, $inDB, $siteDefaults, "settings", FALSE); - stAddSettings($inDB, $inDB, $siteDisplayVars, "displayVars", FALSE); + stAddSettings($inDB, $inDB, $siteDisplayVars, "display_vars", FALSE); } if ($addTestData) @@ -784,13 +793,13 @@ if (!stAddSettings($inDB, $outDB, $siteDefaults, "settings", TRUE)) exit; - if (!stAddSettings($inDB, $outDB, $siteDisplayVars, "displayVars", TRUE)) + if (!stAddSettings($inDB, $outDB, $siteDisplayVars, "display_vars", TRUE)) exit; stAddSettings($inDB, $outDB, $dbMeta, "dbmeta", TRUE); // Migrate other tables - if (!stMigrateTables($inDB, $outDB, array("settings", "displayVars", "dbmeta"))) + if (!stMigrateTables($inDB, $outDB, array("settings", "display_vars", "dbmeta"))) exit; echo "Setting dbVersion.\n";
--- a/msite.inc.php Sun Dec 15 23:46:41 2013 +0200 +++ b/msite.inc.php Sun Dec 15 23:48:14 2013 +0200 @@ -106,7 +106,7 @@ $displayVars = array(); $displayVarsChanged = array(); - if (($res = stExecSQL("SELECT * FROM displayVars")) !== FALSE) + if (($res = stExecSQL("SELECT * FROM display_vars")) !== FALSE) { foreach ($res as $row) $displayVars[$row["key"]] = stGetSQLSettingData($row); @@ -118,12 +118,12 @@ { global $db, $displayVars, $displayVarsChanged; - foreach (stExecSQL("SELECT * FROM displayVars") as $item) + foreach (stExecSQL("SELECT * FROM display_vars") as $item) if (isset($displayVarsChanged[$item["key"]])) { $val = $displayVars[$item["key"]]; stExecSQL( - "UPDATE displayVars SET ".stGetSettingSQL($item, $val). + "UPDATE display_vars SET ".stGetSettingSQL($item, $val). " WHERE key=".$db->quote($item["key"])); } }
--- a/showajax.php Sun Dec 15 23:46:41 2013 +0200 +++ b/showajax.php Sun Dec 15 23:48:14 2013 +0200 @@ -21,7 +21,7 @@ " <div class=\"entryIndex\">#".$entry["show_id"]."</div>\n". " <div class=\"entryName\">".chentities($entry["name"])."</div>\n"; - if ($compo["showAuthors"]) + if ($compo["show_authors"]) { if ($title === false) echo " <div class=\"entryBy\">by</div>\n"; @@ -100,7 +100,7 @@ stGetDisplayVar("tempSlide") > 0 && stGetDisplayVar("tempSlideSet")) { - $sql = stPrepareSQL("SELECT * FROM displaySlides WHERE id=%d", + $sql = stPrepareSQL("SELECT * FROM display_slides WHERE id=%d", stGetDisplayVar("tempSlide")); if (($res = stFetchSQL($sql)) !== false) @@ -135,7 +135,7 @@ // Get list of slides from active rotation list $list = stGetDisplayVar("rotateList"); $sql = stPrepareSQL( - "SELECT * FROM rotationListSlides WHERE list_id=%d ORDER BY order_num,id", + "SELECT * FROM rot_list_slides WHERE list_id=%d ORDER BY order_num,id", $list); if (($slideList = stExecSQL($sql)) !== false)