Mercurial > hg > fapweb
view managedb.php @ 1096:bbc0a3d0b51e
Major renaming / refactor of site messages. Some that were previously
modifiable from admin interface are now "hardcoded" in the configuration
file. Having these settings made modifiable from there made no sense
and just took space in the UI.
author | Matti Hamalainen <ccr@tnsp.org> |
---|---|
date | Fri, 27 Jan 2017 22:15:06 +0200 |
parents | 48e16e856646 |
children | 0a2117349f46 |
line wrap: on
line source
#!/usr/bin/php <?php // // FAPWeb Database management utility // (C) Copyright 2012-2017 Tecnic Software productions (TNSP) // if (!file_exists(realpath(dirname(__FILE__))."/mconfig.inc.php")) { die( "ERROR: Missing site configuration file. Please copy ". "'mconfig.inc.php.example' to 'mconfig.inc.php' and ". "edit it as needed.\n"); } require_once "mconfig.inc.php"; require_once "msite.inc.php"; require_once "dbdefs.inc.php"; stCheckCLIExec(); $currVersion = -1; $dbMetaData = array( "dbVersion" => array(VT_INT, $dbVersion, "Database version"), ); // Add database meta table into tables to be created $sqlTables["dbmeta"] = array( array("key" , "VARCHAR(32)", "PRIMARY KEY"), array("vtype" , "INT"), array("vstr" , "VARCHAR(128)"), array("vtext" , "TEXT"), array("vint" , "INT"), array("sdesc" , "VARCHAR(128)"), ); // // Helper functions // function stCreateTables($dbh, $upgrade) { global $sqlTables; echo "Creating tables...\n"; foreach ($sqlTables as $name => $schemaData) { echo " - '".$name."'\n"; stDBExecSQL($dbh, "DROP TABLE IF EXISTS ".$name); if (!stDBCreateOneTable($dbh, $name, stDBGetTableSchema($dbh, $schemaData))) { if (!$upgrade) return FALSE; else echo "\nFailed to create table '".$name."'\n"; } } return TRUE; } function stUpgradeMap($type, $name) { global $upgradeMappings, $currVersion, $dbVersion; $converted = FALSE; $tmpName = $name; for ($ver = $currVersion; $ver <= $dbVersion; $ver++) { if (isset($upgradeMappings[$ver]) && isset($upgradeMappings[$ver][$tmpName]) && isset($upgradeMappings[$ver][$tmpName][$type])) { $tmpName = $upgradeMappings[$ver][$tmpName][$type]; $converted = TRUE; } } if ($converted) return $tmpName; else switch ($type) { case "table": return $name; case "key": return $name; default: die("Invalid upgrade type '".$type."'.\n"); } } function stDoAddSettings($inDB, $outDB, $settings, $table, $upgrade, $group = FALSE) { $status = TRUE; foreach ($settings as $key => $data) { $sql = false; // Get setting type 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"); } $sqlFmt = "INSERT INTO ".$table." (key,vtype,".$var.",sdesc". ($group !== FALSE ? ",vgroup" : "").") VALUES (%s,%d,".$type.",%s". ($group !== FALSE ? ",%d" : "").")"; // Check if we are upgrading if ($upgrade) { // Map the key, in case the name has changed $inKey = stUpgradeMap("key", $key); $inTable = stUpgradeMap("table", $table); if (($res = stDBFetchSQL($inDB, stDBPrepareSQL($inDB, "SELECT * FROM ".$inTable." WHERE key=%s", $inKey))) !== FALSE) { // Yup, upgrade the data, if we can if ($res["vtype"] != $data[0]) die("Oops! Data type of '".$key."' does not match in table '".$table.". DB upgrade failed.\n"); $sql = stDBPrepareSQL($outDB, $sqlFmt, $key, $data[0], $res[$var], $data[2], $group); } } if ($sql === false) { // Normal insertion of default data $sql = stDBPrepareSQL($outDB, $sqlFmt, $key, $data[0], $data[1], $data[2], $group); } if (stDBExecSQL($outDB, $sql) === FALSE) { $status = FALSE; break; } } return $status; } function stAddSettingsGroup($inDB, $outDB, $groups, $groupData, $groupTable, $table, $upgrade) { echo ($upgrade ? "Adding settings to" : "Upgrading settings in")." '".$table."' table.\n"; foreach ($groups as $groupID => $settings) { if (!isset($groupData[$groupID])) { echo "Group data not set for settings group ID '".$groupID."'.\n"; return FALSE; } // Add group, if needed echo " - Group '".$groupID."' -> ".$groupData[$groupID][1]."."; $sql = stDBPrepareSQL($outDB, "INSERT INTO ".$groupTable." (name,description) VALUES (%s,%s)", $groupData[$groupID][0], $groupData[$groupID][1]); if (($gid = stDBExecSQLInsert($outDB, $sql)) === false) { echo "\nFailed to add group '".$groupID."'\n"; return FALSE; } // Add settings to the group stDBBeginTransaction($outDB); if (stDoAddSettings($inDB, $outDB, $settings, $table, $upgrade, $gid) === false) { echo "\nFailed to add settings to group '".$groupID."'\n"; return FALSE; } stDBCommitTransaction($outDB); echo "\n"; } return TRUE; } function stAddSettingsNormal($inDB, $outDB, $settings, $table, $upgrade) { stDBBeginTransaction($outDB); echo ($upgrade ? "Adding settings to" : "Upgrading settings in")." '".$table."' table."; $status = stDoAddSettings($inDB, $outDB, $settings, $table, $upgrade); echo "\n"; if ($status !== FALSE) stDBCommitTransaction($outDB); return $status; } function stAddTestData($outDB) { global $siteTestData; echo "Adding test data.\n"; $status = TRUE; stDBBeginTransaction($outDB); foreach ($siteTestData as $table => $data) { echo " - ".$table."...\n"; if (count($data) >= 3) { for ($n = 2; $n < count($data); $n++) { $arr = array_merge( array($outDB, "INSERT INTO ".$table." (".$data[0].") VALUES (".$data[1].")"), $data[$n]); $sql = call_user_func_array('stDBPrepareSQL', $arr); if (stDBExecSQL($outDB, $sql) === false) { $status = false; break; } } } else { echo " Invalid table / data definition.\n"; } } stDBCommitTransaction($outDB); return $status; } function stGetSQLTypeParam($dbh, $def, $value) { switch (substr($def, 0, 3)) { case "INT": return intval($value); case "VAR": case "TEX": return $dbh->quote($value); default: die("Unknown type ".$col[1].".\n"); } } function stMigrateTables($inDB, $outDB, $upgrade, $excluded) { global $sqlTables; echo "Migrating tables...\n"; $status = TRUE; $dbName = $outDB->getAttribute(PDO::ATTR_DRIVER_NAME); stDBBeginTransaction($outDB); foreach ($sqlTables as $table => $schema) { // Do not migrate excluded tables (or excluded tables with old names) $sequences = array(); $inTable = $upgrade ? stUpgradeMap("table", $table) : $table; if ($inTable === FALSE || in_array($inTable, $excluded) || in_array($table, $excluded)) continue; // Process each row of the input table echo " - '".$table."' "; $query = stDBExecSQL($inDB, "SELECT * FROM ".$inTable); if ($query === FALSE) { echo "SKIPPED!"; } else foreach ($query as $row) { // Convert to new schema, as needed $avals = array(); $acols = array(); foreach ($schema as $col) { // If input has schema column, add it to output if (isset($row[$col[0]])) { $avals[] = stGetSQLTypeParam($outDB, $col[1], $row[$col[0]]); $acols[] = $col[0]; } // If the column should have autoincrement, add it to sequences if (in_array("AUTOINCREMENT", $col)) $sequences[] = $col[0]; } $sql = "INSERT INTO ".$table." (".implode(",", $acols).") VALUES (".implode(",", $avals).")"; if (stDBExecSQL($outDB, $sql) === false) { $status = FALSE; break; } echo "."; } // Perform some specialities here switch ($dbName) { case "pgsql": // For PostgreSQL we need to update sequences foreach ($sequences as $seq) { if (($max_id = stDBFetchSQLColumn($outDB, "SELECT MAX(".$seq.") FROM ".$table)) !== false && ($seq_id = stDBFetchSQLColumn($outDB, "SELECT nextval('".$table."_".$seq."_seq')")) !== false && ($seq_id <= $max_id)) { echo "SEQFIX"; stDBExecSQL($outDB, "SELECT setval('".$table."_".$seq."_seq', ".$max_id.")"); } } break; } echo "\n"; } stDBCommitTransaction($outDB); return $status; } function stSetDBPermissions($spec) { 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 of '".$filename."!\n"; } } } // // Main program starts // if ($argc < 2) { echo "ManageDB - Manage FAPWeb SQL database\n". "(C) Copyright 2012-2017 ccr/TNSP\n". "\n". "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 <input_dbspec> <output_dbspec>\n". " Upgrade current database, if possible.\n". " Output to new database (DO NOT USE SAME as current!)\n". "\n". " force_upgrade <input_dbspec> <output_dbspec>\n". " Like 'upgrade' but without version check.\n". " DO NOT USE unless you know what you are doing.\n". "\n". " migrate <input_dbspec> <output_dbspec>\n". " Like upgrade, but no version check. Creates\n". " a copy of the database to the output spec.\n". "\n"; exit; } // Validate tables data $errors = FALSE; foreach ($sqlTables as $table => $data) { if (strtolower($table) != $table) { echo "Invalid table definition '".$table."', table name must be lower case.\n"; $errors = TRUE; } foreach ($data as $def) if (strtolower($def[0]) != $def[0]) { echo "Invalid table definition '".$table."', column key '".$def[0]."' must be lower case.\n"; $errors = TRUE; } } if ($errors) die("Errors in table definitions.\n"); // Act according to specified command $addTestData = FALSE; $upgrade = FALSE; switch (stCArgLC(1)) { case "test": $addTestData = TRUE; case "new": // Try to connect to database if (($inSpec = stCArg(2)) === false) die("No PDO database spec specified.\n"); if (($inDB = stConnectSQLDBSpec($inSpec)) === false) die("Could not connect to SQL database '".$inSpec."'.\n"); echo "Using database spec '".$inSpec."'.\n"; // Create tables, add defaults if (stCreateTables($inDB, FALSE)) { stAddSettingsNormal($inDB, $inDB, $dbMetaData, "dbmeta", FALSE, FALSE); stAddSettingsGroup($inDB, $inDB, $siteDefaultSettings, $siteSettingsGroups, "settings_groups", "settings", FALSE); stAddSettingsNormal($inDB, $inDB, $siteDisplayVars, "display_vars", FALSE, FALSE); } if ($addTestData) stAddTestData($inDB); stSetDBPermissions($inSpec); break; case "upgrade": case "force_upgrade": $upgrade = TRUE; case "migrate": // // Attempt to upgrade database // if ($argc < 4) die("Usage: ".$argv[0]." upgrade <input_dbspec> <output_dbspec>\n"); $inSpec = stCArg(2); $outSpec = stCArg(3); if ($outSpec == $inSpec) die("The input and output databases CAN NOT BE SAME.\nBe VERY CAREFUL to not accidentally specify same db!\n"); echo "Using INPUT database spec '".$inSpec."'.\n"; if (($inDB = stConnectSQLDBSpec($inSpec)) === false) die("Could not connect to SQL database '".$inSpec."'.\n"); // Check the current version first ... if (($currVersion = stGetDBMeta($inDB, "dbVersion")) === FALSE) $currVersion = -1; if ($currVersion == $dbVersion && stCArgLC(1) == "upgrade") { echo "Database is already version ".$dbVersion.", no upgrading needed.\n"; } else { // Okay, we shall create an upgraded version .. if (($outDB = stConnectSQLDBSpec($outSpec)) === false) die("Could not connect to SQL database '".$outSpec."'.\n"); echo "Database at version ".$currVersion.", upgrading to ".$dbVersion."\n"; echo "Using OUTPUT database spec '".$outSpec."'.\n"; // Possibly bail out incompatible upgrades here // Create tables if (!stCreateTables($outDB, TRUE)) exit; // Migrate data from setting tables .. if (!stAddSettingsGroup($inDB, $outDB, $siteDefaultSettings, $siteSettingsGroups, "settings_groups", "settings", TRUE)) exit; if (!stAddSettingsNormal($inDB, $outDB, $siteDisplayVars, "display_vars", TRUE)) exit; stAddSettingsNormal($inDB, $outDB, $dbMetaData, "dbmeta", TRUE); // Migrate other tables if (!stMigrateTables($inDB, $outDB, $upgrade, array("settings", "display_vars", "dbmeta", "settings_groups"))) exit; // Set new database version echo "Setting dbVersion.\n"; stSetDBMeta($outDB, "dbVersion", $dbVersion); echo "Upgrade complete.\n"; stSetDBPermissions($inSpec); stSetDBPermissions($outSpec); } break; default: echo "ERROR! Invalid operation mode '".stCArg(1)."'.\n"; break; } // // Clean up permissions // foreach (array("managedb.php", "faptool.php") as $filename) { if (@chmod($filename, 0700) === FALSE) { echo "ERROR! Could not set permissions for '".$filename."'!\n"; } } ?>