Mercurial > hg > fapweb
view createdb.php @ 472:1eabbd70cd25
Add skipping button.
author | Matti Hamalainen <ccr@tnsp.org> |
---|---|
date | Fri, 06 Dec 2013 22:28:23 +0200 |
parents | 249631e194c8 |
children | 86ee2b42a995 |
line wrap: on
line source
#!/usr/bin/php <? 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"])) { header("Status: 404 Not Found"); die(); } $dbVersion = 10; $dbMeta = array( "dbVersion" => array(VT_INT, $dbVersion, "Database version"), ); // The defaults we put in $siteDefaults = array( "maxAttendeesHard" => array(VT_INT, 60, "Maximum attendees (HARD limit, <= 0 means no limit)"), "maxAttendeesSoft" => array(VT_INT, 50, "Maximum attendees (soft limit, <= 0 means no limit)"), "userTimeout" => array(VT_INT, 120, "User pages (voting) timeout in minutes"), "admTimeout" => array(VT_INT, 15, "Administration interface timeout in minutes"), "showAdmin" => array(VT_BOOL, false, "Always show administration interface link on the menu"), "showAttendees" => array(VT_BOOL, true, "Show attendees list"), "allowRegister" => array(VT_BOOL, false, "Enable event registration"), "allowVoting" => array(VT_BOOL, false, "Enable voting (individual compos must be enabled as well)"), "showResults" => array(VT_BOOL, false, "Enable results page"), "showResultsASCII" => array(VT_BOOL, true, "Show results as ASCII instead of HTML table"), "showResAuthors" => array(VT_BOOL, true, "Show entry authors on results page"), "requireEMail" => array(VT_BOOL, false, "Require e-mail address in registrations"), "showNews" => array(VT_BOOL, true, "Enable News link on main menu"), "showNewsOnAbout" => array(VT_BOOL, true, "Show latest news item on About page"), "registerInfoText" => array(VT_TEXT, "<p> Only your <b>handle</b> and the answer to the botcheck are strictly required. If you plan on joining the IRC channel (<a href=\"irc://#fap2013@ircnet\">#fap2013 @ IRCNet</a>) or staying up to date by other means, <b>e-mail</b> is not required either. </p>", "Registration page info text"), "registerPostText" => array(VT_TEXT, " <h1>Registration successful</h1> <p>Now go make a demo about it!</p>", "Successful post-registration note text"), "registerLimitExceeded" => array(VT_TEXT, " <h1>Sorry, registration disabled!</h1> <p> Registration to the event is not available at this time due to number of attendees limit having been reached. <b>:(</b> </p> ", "Registration attendee limit exceeded note text"), "registerNotEnabled" => array(VT_TEXT, " <h1>Sorry, registration disabled!</h1> <p> Registration to the event is not enabled at this time. </p> ", "Registration not enabled note text"), "registerPostNoEmail" => array(VT_TEXT, " <h2>By the way ...</h2> <p>As you did not specify an e-mail contact address, you'll have to get updates and information about the location (if you don't already know it) by some other means (IRC, for example.)</p> ", "No e-mail address registration note"), "eventDescription" => array(VT_TEXT, " <h1>Event program & schedule</h1> <ul> <li><b>Aegis</b> of DSS and FAG will be performing a DJ gig.</li> <li>.. and possible additional live acts. More info to come.</li> </ul> <h2>Friday 30.11.</h2> <ul> <li><b>18:00</b> - <i>Doors open</i>.</li> <li><b>22:00</b> - DJ set by Aegis.</li> </ul> <h2>Saturday 1.12.</h2> <ul> <li><b>14:00</b> - Deadline for remote entries.</li> <li><b>18:00</b> - Deadline for the entries delivered on location.</li> <li><b>20:00</b> - Competitions start.</li> </ul> Competition schedule and voting deadline will depend on number of entries. <h2>Sunday 2.12.</h2> <ul> <li><b>12:00</b> - Party over?</li> </ul> ", "Event general description / timetables etc."), "compoDescription" => array(VT_TEXT, " <h1>General</h1> <p class=\"notice\"> YOU <b>MUST</b> HAVE AT LEAST ONE ENTRY TO COMPETITIONS IF YOU COME TO THE PARTY. </p> <p class=\"note\"> If there are enough entries, then AGA/OCS/ECS demos will be run in separate compos. <br /> Remote entries are welcome! </p> <p> The compo machine will be an <b>A1200 with an 060/50 and lots of RAM</b>. An <b>A500 1.3 512k/512k</b> will also be available if your prod is not AGA compatible. </p> <h1>Compos</h1> ", "Compo general description"), "siteInfoText" => array(VT_TEXT, " <a href=\"about\"> <img src=\"img/fapsm.png\" alt=\"Finnish Amiga Party 2013\" class=\"logo\" /></a> <div id=\"date\"> 5.-8.12.2013<br /> Helsinki, Finland<br /> @ old location<br /> <span class=\"notice\">Entry 15 EUR + prod</span> </div>", "Site header text"), "newsHeader" => array(VT_TEXT, "", "News page header text"), "aboutDescription" => array(VT_TEXT, " <div style=\"text-align: center;\"> <img src=\"img/fap.png\" alt=\"FAP\" /> <p> Pure Amiga demoscene party, all traditional Amiga compos and purely Amiga-oriented program. <br /> <span class=\"notice\"> YOU <b>MUST</b> HAVE AT LEAST ONE ENTRY TO COMPETITIONS IF YOU COME TO THE PARTY. </span> </p> </div>", "About page text"), "voteFinishedText" => array(VT_TEXT, " <h1>Yay, you have voted!</h1> <p>Now go FAP some more! And make a demo about it.</p>", "Message shown after successful voting."), "siteMenuHeader" => array(VT_TEXT, "<div>13.FAP:> <span class=\"mblink\">■</span></div>", "Site menu header text"), "siteMenuFooter" => array(VT_TEXT, "", "Site menu footer text"), "siteExtraHTML" => array(VT_TEXT, "<div id=\"sponsors\">Gentle Eye</div>", "Extra global HTML code (f.e. sponsors box)"), ); $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", "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", "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", "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", "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", ); $siteDisplayVars = array( "showMode" => array(VT_INT, 0, "Currently active display mode"), "rotateDuration" => array(VT_INT, 15, "Slide rotation time per slide (seconds)"), "tempDuration" => array(VT_INT, 5, "Temporary slide display time (minutes)"), "tempSlide" => array(VT_INT, 0, "Temporary slide ID"), "compoID" => array(VT_INT, 0, "Compo ID of current compo"), "compoPrevEntry" => array(VT_INT, 0, "Previously shown compo entry"), "compoCurrEntry" => array(VT_INT, 0, "Current / next compo entry to be shown"), // Not user-manageable "tempSlideSet" => array(VT_BOOL, false, "Temporary slide set"), "activeSlideMode" => array(VT_INT, 0, "Current active slide display mode"), "activeSlide" => array(VT_INT, 0, "Current active slide"), "activeSlideExpire" => array(VT_INT, 0, "Expiration timestamp of current slide"), "rotateList" => array(VT_INT, 0, "Current rotation list ID"), "rotateListIndex" => array(VT_INT, 0, "Current index in rotation list"), "lastUpdate" => array(VT_INT, 0, "Timestamp of last slide update"), ); $siteTestData = array( "news" => array( "utime,title,text,author", time().",%s,%s,%s", array("Today's news", "We. Are. Back.", "orgaz"), array("Good news, everybody!", "...", "The Professor"), ), "votekeys" => array( "key", "%s", array("test1"), array("test2"), array("7jjnqt5z"), array("c7jcfk1G"), ), "attendees" => array( "regtime,name,groups,oneliner,email", "%d,%s,%s,%s,%s", array(time()-0, "man with no alias", "supergroup", "foo-bar", "c@supergroup.com"), array(time()-15, "man with alias", "supergroup", "hi!", "c@supergroup.com"), array(time()-30, "alias with a man", "supergroup", "mega super kewl rulets", "x@microsoft.com"), ), "compos" => array( "name,description,visible,voting", "%s,%s,1,1", array("Graphics", "Anything in standard resolutions."), array("Protracker music", "Standard 4-channel Protracker MOD music."), array("4k intro", "4k intro competition"), ), "entries" => array( "name,author,compo_id,filename,info", "%s,%s,%d,%s,%s", array("Donkey Dong", "electric/extend", 1, "donkey.lbm", "amigaaaah!"), array("Your kondom", "ccr/TNSP", 1, "kondom.lbm", "oh my god, it's full of cocks!"), array("Penis song", "reed/flt", 2, "penis.mod", "laulu rakkaudelle"), array("jenkka", "aegis", 2, "jenkka.mod", ""), array("Fungiform 2", "mfx", 3, "mfx-fungiform2.lzh", "OCS-only"), ), "displaySlides" => 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."), array("Astu to infodesk", "<b>Astu</b> - please come to info desk!"), array("Gentle Eye mainos", "<b>Buy Amiga stuff!</b><br />Gentle Eye Oy is selling Amiga-related stuff near organizer desk!"), ), "rotationListData" => array( "name", "%s", array("Main rotation"), array("Next Up"), ), "rotationListSlides" => array( "list_id,slide_id", "%d,%d", array(1,2), array(1,3), array(2,1), ), ); // // Helper functions // function stCArg($index) { global $argc, $argv; if ($index < $argc) return $argv[$index]; else return FALSE; } function stCArgLC($index) { global $argc, $argv; if ($index < $argc) return strtolower($argv[$index]); else return FALSE; } function stCSQLError($sql) { global $db; die("Error executing SQL query: ".implode("; ", $db->errorInfo())." in statement \"".$sql."\"\n"); exit; } function stConnectDB($dbspec) { 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) return FALSE; return stGetSQLSettingData($item); } 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) && !upgrade) return FALSE; } return TRUE; } function stAddSettings($settings, $table, $upgrade) { echo ($upgrade ? "Adding settings to" : "Upgrading settings in")." '".$table."' table."; foreach ($settings as $key => $data) { switch ($data[0]) { case VT_TEXT: $type = "%s"; $var = "vtext"; break; case VT_STR: $type = "%s"; $var = "vstr"; break; case VT_BOOL: $type = "%b"; $var = "vint"; break; case VT_INT: $type = "%d"; $var = "vint"; break; 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( "UPDATE ".$table." SET key=%s WHERE key=%s", $to, $from); stExecSQL($sql); } } function stAddTestData() { global $siteTestData; echo "Adding test data.\n"; foreach ($siteTestData as $table => $data) { echo " - ".$table."...\n"; if (count($data) >= 3) { for ($n = 2; $n < count($data); $n++) { $arr = array_merge( array("INSERT INTO ".$table." (".$data[0].") VALUES (".$data[1].")"), $data[$n]); $sql = call_user_func_array('stPrepareSQL', $arr); stExecSQL($sql); } } else { echo " Invalid table / data definition.\n"; } } } // // Main program starts // if ($argc < 2) { echo "Usage: ".$argv[0]." <mode> [args]\n". "Where mode is one of following:\n". "\n". " new [dbspec] Create a new database with given PDO spec\n". " 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; } 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"; exit; } // Check if database spec provided, if not use default if (($spec = stCArg(2)) === FALSE) $spec = $siteSettings["sqlDB"]; // Try to connect to database if (!stConnectDB($spec)) die("Could not connect to SQL database '".$spec."'.\n"); echo "Using database spec '".$spec."'.\n"; // Act according to specified command $addData = FALSE; switch (stCArgLC(1)) { case "test": $addData = TRUE; case "new": if (stCreateTables(FALSE)) { stAddSettings($dbMeta, "dbmeta", FALSE); stAddSettings($siteDefaults, "settings", FALSE); stAddSettings($siteDisplayVars, "displayVars", FALSE); } if ($addData) stAddTestData(); 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"; } } 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 if ($currVersion < 6) stExecSQL("DROP TABLE votes"); if ($currVersion < 9) { stExecSQL("DROP TABLE displayVars"); stExecSQL("DROP TABLE displayListSlides"); stExecSQL("DROP TABLE displayListData"); } // 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; } // // Clean up permissions // foreach (array("createdb.php") as $filename) { if (chmod($filename, 0700) === FALSE) { echo "ERROR! Could not set permissions for '$filename'!\n"; } } ?>