by If you have ever needed to verify, create, or update database schema then you will probably like this snippet (ok, it is a little long to call a snippet). This code assumes that you have a class with a shared PDO.
/** @var string Database table for storing user information */ protected $userTable = "users"; /** @var string Database table for storing feed/video information */ protected $feedTable = "feed"; /** @var array|string SQL database table names */ protected $myDBTables; /** @var array|array array of the correct database table schemas keyed by table name */ private $correctSchemas; private $userCorrect = [ ["Field"=>"ID", "Type"=>"int(11)", "Null"=>"NO", "Key"=>"PRI", "Default"=>null, "Extra"=>"auto_increment"], ["Field"=>"username", "Type"=>"mediumtext", "Null"=>"NO", "Key"=>"", "Default"=>null, "Extra"=>""], ["Field"=>"name", "Type"=>"mediumtext", "Null"=>"YES", "Key"=>"", "Default"=>null, "Extra"=>""] ]; private $feedCorrect = [ ["Field"=>"ID", "Type"=>"int(11)", "Null"=>"NO", "Key"=>"PRI", "Default"=>null, "Extra"=>"auto_increment"], ["Field"=>"userID", "Type"=>"int(11)", "Null"=>"NO", "Key"=>"", "Default"=>null, "Extra"=>""], ["Field"=>"URL", "Type"=>"text", "Null"=>"YES", "Key"=>"", "Default"=>null, "Extra"=>""], ["Field"=>"isVideo", "Type"=>"tinyint(1)", "Null"=>"NO", "Key"=>"", "Default"=>"0", "Extra"=>""], ["Field"=>"timeAdded", "Type"=>"timestamp", "Null"=>"NO", "Key"=>"", "Default"=>"CURRENT_TIMESTAMP", "Extra"=>""] ]
public function __construct(...){
...
$this->myDBTables = [$this->userTable, $this->feedTable];
$this->correctSchemas = [$this->userTable => $this->userCorrect, $this->feedTable => $this->feedCorrect];
...
}
/**
* Verifies the currently connected database against the current schema
* @return int Returns 0 if all is well, 1 if the user table or feed table do not exist, and 2 if the tables exist but the schema inside is wrong
* @throws \PDOException
*/
public function verifyDB(){
try{
$tables = $this->getDatabaseTables();
foreach($this->myDBTables as $tableName){
if(!in_array($tableName, $tables, true)){
return 1;
}
}
foreach($this->myDBTables as $tableName){
$currentTableSchema = $this->describeTable($tableName);
if(!$this->verifySchema($this->correctSchemas[$tableName], $currentTableSchema)){
return 2;
}
}
return 0;
}
catch(\PDOException $e){
echo "ERROR: ".$e->getMessage();
throw $e;
}
}
/**
* Checks if two arrays are equal to test that SQL schemas are compliant
* @param $correct
* @param $existing
* @return bool
*/
private function verifySchema($correct, $existing){
sort($correct);
sort($existing);
return $correct == $existing;
}
/**
* Function to get layout of a specific table
* @param $table string Table to get layout of
* @return array
*/
protected function describeTable($table){
$p = parent::$PDO->prepare("DESCRIBE $table");
$p->execute();
return $p->fetchAll(\PDO::FETCH_ASSOC);
}
/**
* Function to return a list of database tables
* @return array
*/
protected function getDatabaseTables(){
$p = parent::$PDO->prepare("SHOW TABLES");
$p->execute();
$rows = $p->fetchAll(\PDO::FETCH_ASSOC);
$tables = [];
foreach($rows as $r){
$tables[] = array_values($r)[0];
}
return $tables;
}
To verify the database, run verifyDB. If the output is 0, then all is well and nothing needs to happen. If the output is 1 then the database, or one of the tables, needs to be created. If the output is 2 then one or more of tables need to be updated to the current schema.
If the output is 1 you will execute makeDB(1).
/**
* Generate the tables in the current database
* @param int $code
* @return void
* @throws \PDOException
*/
public function makeDB($code = 1){
if($code == 1){
$generalSetupSQL = "SET SQL_MODE = \"NO_AUTO_VALUE_ON_ZERO\";
SET time_zone = \"+00:00\";";
$sql = "";
foreach($this->myDBTables as $tableName){
$sql .= "CREATE TABLE `$tableName` (";
foreach($this->correctSchemas[$tableName] as $column){
$sql .= $this->makeColumnSQL($column).",";
}
$sql = substr($sql, 0, strlen($sql) - 1);
$sql .= ") CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;";
}
try{
// Execute all the statements
$p = parent::$PDO->prepare($generalSetupSQL.$sql);
$p->execute();
}
catch(\PDOException $e){
echo "Database creation failed! ".$e->getMessage();
error_log("Database creation failed! ".$e->getMessage());
throw $e;
}
}
else if($code == 2){
$this->updateDBSchema();
}
}
/**
* Generates SQL query to make a column. Returns something in the form of `columnName` columnType NULL/Not
* Default Key Extra
* @param $c array dictionary representing a column's correct schema
* @return string
*/
private function makeColumnSQL($c){
$columnText = "`".$c["Field"]."` ".$c["Type"];
if($c["Null"] == "NO"){
$columnText .= " NOT NULL";
}
else{
$columnText .= " NULL";
}
if($c["Default"] != null){
$columnText .= " DEFAULT ".$c["Default"];
}
if($c["Key"] == "PRI"){
$columnText .= " PRIMARY KEY";
}
if($c["Extra"] != ""){
$columnText .= " ".$c["Extra"];
}
return $columnText;
}
As you see at the end, if the code is 2 (schema needs to be updated) then updateDBSchema is called.
private function updateDBSchema(){
try{
$alterSQL = "";
foreach($this->myDBTables as $tableName){
$currentSchema = $this->describeTable($tableName);
$alterSQL .= $this->makeAlterQuery([$tableName => $currentSchema],
[$tableName => $this->correctSchemas[$tableName]]);
}
$p = parent::$PDO->prepare($alterSQL);
$p->execute();
}
catch(\PDOException $e){
echo "Database update failed! ".$e->getMessage();
error_log("Database update failed! ".$e->getMessage());
throw $e;
}
}
/**
* Generates SQL query to add missing columns to the given tables
* @param $currentTables array dictionary in the form of ["tableName"=>[table_schema]] representing the values
* that are currently existing in the database
* @param $correctTables array dictionary in the form of ["tableName"=>[table_schema]] representing the correct
* values
* @return string
*/
private function makeAlterQuery($currentTables, $correctTables){
$sql = "";
// Loop through the given tables
foreach($correctTables as $tableName=>$table){
// Loop through all the columns in a table
foreach($table as $i=>$correct){
// Check if the current column is in the existing database table
if(!in_array($correct, $currentTables[$tableName], true)){
$sql .= "ALTER TABLE `".$tableName."` ADD ".$this->makeColumnSQL($correct);
if($i == 0){
$sql .= " FIRST";
}
if($i > 0){
$sql .= " AFTER `".$table[$i-1]["Field"]."`";
}
$sql .= ";";
}
}
}
return $sql;
}
You may have noticed that none of this allows for the dropping of columns or tables. It would be possible to add that functionality, but with the code above you will have to keep any columns that you would rather drop.
Leave a Reply