Mercurial > hg > fapweb
view managedb.php @ 1083:1f77195b4f95
Some work on faptool.
author | Matti Hamalainen <ccr@tnsp.org> |
---|---|
date | Wed, 25 Jan 2017 21:44:16 +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"; } } ?>