redbeanify version 1.0, 25 April 2012

"; exit(); } else if ($action == "check") { $server = $_POST['server']; $loginId = $_POST['loginid']; $password = $_POST['password']; $databaseName = $_POST['databaseName']; $disableTimeout = isset($_POST['disableTimeout']); try { if ($disableTimeout) { set_time_limit(0); } $database = new Database($server, $loginId, $password, $databaseName); $checkResults = $database->ReturnCheckResults(); $_SESSION['server'] = $server; $_SESSION['loginid'] = $loginId; $_SESSION['password'] = $password; $_SESSION['databaseName'] = $databaseName; $_SESSION['disableTimeout'] = $disableTimeout; $_SESSION['database'] = $database; // Will this work? } catch (Exception $ce) { die ($ce->getMessage()); } print "

Check Results

"; if (count($checkResults) == 0) { print "

No problems found! Maybe you should buy a lottery ticket today. It could be your lucky day.

"; } else { print ""; foreach ($checkResults as $checkResult) { print ""; } print "

Table	Problem	Action
{$checkResult['Table']}	{$checkResult['Problem']}	{$checkResult['Action']}

"; } print ""; exit(); } else if ($action == "convert") { $database = $_SESSION['database']; if (!isset($database)) { die ('SESSION variable does not contain database object'); } try { if ($_SESSION['disableTimeout']) { set_time_limit(0); } $database->RefreshDatabaseConnection($_SESSION['server'], $_SESSION['loginid'], $_SESSION['password'], $_SESSION['databaseName']); $database->redbeanify(); } catch (Exception $ce) { die ($ce->getMessage()); } print "

Completed

"; } else { die ("Unrecognized action {$action}"); } /*********** * Classes * ***********/ class Database { private $mysqliConnection; private $databaseName; private $activated = false; private $tables = array(); private $checkResults = array(); private $sql; function __construct($server, $loginId, $password, $databaseName) { $this->OpenDatabaseConnection($server, $loginId, $password, $databaseName); $this->databaseName = $databaseName; $this->GetTables(); } private function OpenDatabaseConnection($server, $loginId, $password, $databaseName) { $this->mysqliConnection = new mysqli($server, $loginId, $password, $databaseName); if (mysqli_connect_errno()) { throw new Exception("Connect to database failed: " . mysqli_connect_error() . " ({$server}, {$loginId}, {$databaseName})"); } } function RefreshDatabaseConnection($server, $loginId, $password, $databaseName) { $this->OpenDatabaseConnection($server, $loginId, $password, $databaseName); foreach ($this->tables as $table) { $table->SetDatabaseConnection($this->mysqliConnection); } } function ReturnCheckResults() { // Check out each table's' internals foreach ($this->tables as $table) { $tableCheckResults = $table->ReturnExceptionMessages(); foreach ($tableCheckResults as $tableCheckResult) { $this->checkResults[] = $tableCheckResult; } } // Now check to see if any included table references an excluded table foreach ($this->tables as $table) { if ($table->skipThisTable) { continue; } foreach ($table->referencedTables as $referencedTable) { if ($this->tables[$referencedTable["Referenced Table"]]->skipThisTable) { $table->skipThisTable = true; $this->checkResults[] = array ( "Table" => $table->tableName, "Problem" => "Table references table {$referencedTable["Referenced Table"]}, which is excluded", "Action" => "Skip table" ); } } } return $this->checkResults; } private function GetTables() { try { $sql = "SHOW TABLES"; $tableRows = $this->mysqliConnection->query($sql); while ($tableRow = $tableRows->fetch_row()) { $table = new Table($this->mysqliConnection, $this->databaseName, $tableRow[0]); $this->tables[$table->tableName] = $table; } $tableRows->free(); } catch (Exception $ge) { throw new Exception($ge->getMessage() . "

" . $sql); } } function redbeanify() { try { $this->ConvertPrimaryKeys(); $this->AddForeignKeys(); } catch (Exception $re) { throw new Exception($re->getMessage()); } } function ConvertPrimaryKeys() { try { foreach ($this->tables as $table) { $table->ConvertPrimaryKey(); } } catch (Exception $ke) { throw $ke; } } function AddForeignKeys() { try { foreach ($this->tables as $table) { $table->AddForeignKey(); } } catch (Exception $ke) { throw $ke; } } } class Table { var $tableName; var $referencedTables = array(); var $skipThisTable = false; private $mysqliConnection; private $databaseName; private $hasPrimaryKey = false; private $primaryKeyColumns = array(); private $hasColumnNamedId = false; private $hasAutoIncrementColumn = false; private $hasMultipleFksToSamePk = false; private $sql; function __construct($mysqliConnection, $databaseName, $tableName) { $this->mysqliConnection = $mysqliConnection; $this->databaseName = $databaseName; $this->tableName = $tableName; $this->GetColumnMetaData(); } function SetDatabaseConnection($mysqliConnection) { $this->mysqliConnection = $mysqliConnection; } private function GetColumnMetaData() { try { $sql = "SHOW COLUMNS FROM " . $this->tableName; $columnRows = $this->mysqliConnection->query($sql); while ($columnRow = $columnRows->fetch_assoc()) { if (strtolower($columnRow["Field"]) == "id") { $this->hasColumnNamedId = true; } if ($columnRow["Key"] == "PRI") { $this->primaryKeyColumns[] = $columnRow["Field"]; $this->hasPrimaryKey = true; } if ($columnRow["Extra"] == "auto_increment") { $this->hasAutoIncrementColumn = true; } } $columnRows->free(); // set $this->referencedTables. $sql = "SELECT CONSTRAINT_NAME,COLUMN_NAME,ORDINAL_POSITION,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME " . "FROM information_schema.KEY_COLUMN_USAGE " . "WHERE CONSTRAINT_SCHEMA = '{$this->databaseName}' AND REFERENCED_TABLE_NAME IS NOT NULL AND TABLE_NAME = '{$this->tableName}' " . "ORDER BY CONSTRAINT_NAME,ORDINAL_POSITION"; // This array has one entry per foreign key, identifying referenced table and all pairs of columns in the FK // array ( "Referenced Table" => $referencedTableRow['REFERENCED_TABLE_NAME'], "Constraint Name" => $referencedTableRow['CONSTRAINT_NAME'], // "Column Pairs" => array ( array ( "Referencing Column" => $referencedTableRow['COLUMN_NAME'], "Referenced Column" => $referencedTableRow['REFERENCED_COLUMN_NAME'] ), ... ) ) $referencedTablesQuery = $this->mysqliConnection->query($sql); $previousConstraintName = "!@#%^"; while ($referencedTableRow = $referencedTablesQuery->fetch_assoc()) { if ($referencedTableRow["CONSTRAINT_NAME"] != $previousConstraintName) { if ($previousConstraintName != "!@#%^") { $nextConstraint["Column Pairs"] = $columnPairs; $this->referencedTables[] = $nextConstraint; // Check new table name: If a referenced table shows up in the list more than once, that's a problem. foreach ($this->referencedTables as $referencedTable) { if ($referencedTable["Referenced Table"] == $referencedTableRow["REFERENCED_TABLE_NAME"]) { $this->hasMultipleFksToSamePk = true; break; } } } $previousConstraintName = $referencedTableRow["CONSTRAINT_NAME"]; $nextConstraint = array ( "Referenced Table" => $referencedTableRow['REFERENCED_TABLE_NAME'], "Constraint Name" => $referencedTableRow['CONSTRAINT_NAME'] ); $columnPairs = array(); } $columnPairs[] = array ( "Referencing Column" => $referencedTableRow['COLUMN_NAME'], "Referenced Column" => $referencedTableRow['REFERENCED_COLUMN_NAME'] ); } // Flush last value to array if ($previousConstraintName != "!@#%^") { $nextConstraint["Column Pairs"] = $columnPairs; $this->referencedTables[] = $nextConstraint; } $referencedTablesQuery->free(); } catch (Exception $ge) { throw new Exception($ge->getMessage() . "

" . $sql); } } function ReturnExceptionMessages() { $exceptionMessages = array(); if ($this->hasColumnNamedId) { $exceptionMessages[] = array ( "Table" => $this->tableName, "Problem" => "Table already has a column named 'id'", "Action" => "Skip table" ); $this->skipThisTable = true; } if ($this->hasAutoIncrementColumn) { $exceptionMessages[] = array ( "Table" => $this->tableName, "Problem" => "Table already has an autoincrement column", "Action" => "Skip table" ); $this->skipThisTable = true; } if ($this->hasMultipleFksToSamePk) { $exceptionMessages[] = array ( "Table" => $this->tableName, "Problem" => "Table has a referencing table with more than one foreign key (need aliasing)", "Action" => "Skip table" ); $this->skipThisTable = true; } return $exceptionMessages; } function ConvertPrimaryKey() { if ($this->skipThisTable) { return; } try { if ($this->hasPrimaryKey) { // Add a UNIQUE KEY that duplicates this PRIMARY KEY $sql = "ALTER TABLE {$this->tableName} ADD UNIQUE KEY ("; foreach ($this->primaryKeyColumns as $primaryKeyColumn) { $sql .= $primaryKeyColumn . ","; } $sql = trim($sql, ",") . ")"; $this->mysqliConnection->query($sql); // Drop the PRIMARY KEY $sql = "ALTER TABLE {$this->tableName} DROP PRIMARY KEY"; $this->mysqliConnection->query($sql); } // Add a new PRIMARY KEY column for the id $sql = "ALTER TABLE {$this->tableName} ADD id INT AUTO_INCREMENT PRIMARY KEY"; $this->mysqliConnection->query($sql); } catch (Exception $pke) { throw new Exception($pke->getMessage() . "

" . $sql); } } function AddForeignKey() { if ($this->skipThisTable) { return; } try { // $this->referencedTables is an array with one entry per foreign key, identifying referenced table and all pairs of columns in the FK // array ( "Referenced Table" => $referencedTableRow['REFERENCED_TABLE_NAME'], "Constraint Name" => $referencedTableRow['CONSTRAINT_NAME'], // "Column Pairs" => array ( array ( "Referencing Column" => $referencedTableRow['COLUMN_NAME'], "Referenced Column" => $referencedTableRow['REFERENCED_COLUMN_NAME'] ), ... ) ) foreach ($this->referencedTables as $referencedTable) { $referencedTableName = $referencedTable['Referenced Table']; // For each FOREIGN KEY, add a new column pointing back to the PRIMARY KEY $sql = "ALTER TABLE {$this->tableName} ADD {$referencedTableName}_id INT NULL"; $this->mysqliConnection->query($sql); // Add a new FOREIGN KEY to link the two tables $sql = "ALTER TABLE {$this->tableName} ADD FOREIGN KEY ({$referencedTableName}_id) REFERENCES {$referencedTableName} (id)"; $this->mysqliConnection->query($sql); // Initialize the new FOREIGN KEY column(s) $sql = "UPDATE {$this->tableName},{$referencedTableName} SET {$referencedTableName}_id = {$referencedTableName}.id WHERE "; foreach ($referencedTable['Column Pairs'] as $columnPair) { $sql .= "{$this->tableName}.{$columnPair['Referencing Column']} = {$referencedTableName}.{$columnPair['Referenced Column']} AND "; } $sql = substr($sql, 0, strlen($sql) - 5); // trim off the last AND $this->mysqliConnection->query($sql); } } catch (Exception $fke) { throw new Exception($fke->getMessage() . "

" . $sql); } } } ?>