| <?php | |
| /******************************************************************************* | |
| * Copyright (c) 2006-2007 Eclipse Foundation | |
| * All rights reserved. This program and the accompanying materials | |
| * are made available under the terms of the Eclipse Public License v1.0 | |
| * which accompanies this distribution, and is available at | |
| * http://www.eclipse.org/legal/epl-v10.html | |
| * | |
| * Contributors: | |
| * Bjorn Freeman-Benson - initial API and implementation | |
| * Ward Cunningham - initial API and implementation | |
| * Karl Mathias - initial API and implementation | |
| *******************************************************************************/ | |
| function ourTrim(&$item1, $key){$item1 = trim($item1);} | |
| abstract class AbstractSchemaChecker { | |
| abstract public function check_and_modify( $context ); | |
| public function check_and_modify_table( $databasename, $tablename, $schemas, $context ) { | |
| $dbh = $context->database( $databasename ); | |
| $tablenamesuffix = $context->initmode; | |
| echo "working on table '$tablename$tablenamesuffix' \n"; | |
| $createfunction = "create_$tablename"; | |
| if( $context->testmode && $context->initmode === false ) { | |
| echo "..creating in memory <br>\n"; | |
| $sql = "DROP TABLE IF EXISTS $tablename "; | |
| remember_query($dbh, $sql); | |
| mysqli_error_check($dbh); | |
| $sql = $this->$createfunction(''); | |
| $translations = array( | |
| '/param(\d+) TEXT/' => 'param\1 varchar(1024)', | |
| '/\s[Tt][Ee][Xx][Tt]/' => ' varchar(1024)', | |
| '/blob/' => 'varbinary(8192)', | |
| '/ENGINE=([A-Za-z]+)/' => '', | |
| '/mediumtext/' => 'varchar(256)', | |
| '/tinytext/' => 'varchar(256)', | |
| '/FULLTEXT/' => '', | |
| ); | |
| $a1 = array(); | |
| $a2 = array(); | |
| foreach( $translations as $k => $v ) { | |
| $a1[] = $k; | |
| $a2[] = $v; | |
| } | |
| $sql = preg_replace( $a1, $a2, $sql ); | |
| $sql = $sql . " ENGINE=MEMORY SELECT * FROM $tablename" . '_prototype'; | |
| $this->create_db( $sql, $dbh ); | |
| return true; | |
| } else { | |
| $table_name = $tablename.$tablenamesuffix; | |
| $result = remember_query($dbh, "DESCRIBE $tablename$tablenamesuffix"); | |
| if( strlen(mysqli_error($dbh)) > 0 ) { | |
| if( $context->devmode | |
| || $context->testmode | |
| || $databasename == 'myfoundation' ) { | |
| echo "..does not exist, creating <br>\n"; | |
| $this->create_db( $this->$createfunction($tablenamesuffix), $dbh ); | |
| $result = remember_query($dbh, "DESCRIBE $tablename$tablenamesuffix"); | |
| $str = $this->table_has_schema($result, $tablename . $tablenamesuffix, $schemas[count($schemas)], $dbh); | |
| if( $str === false ) | |
| return true; | |
| else { | |
| echo $str; | |
| echo "..schema does not match after creation of table $tablename$tablenamesuffix, error <br>\n"; | |
| return false; | |
| } | |
| } else { | |
| // print mysqli_error($dbh); | |
| echo "..does not exist, error <br>\n"; | |
| $this->createTableFromSchema($table_name,$schemas[count($schemas)],$dbh,$context); | |
| $result = remember_query($dbh, "DESCRIBE $tablename$tablenamesuffix"); | |
| $str = $this->table_has_schema($result, $tablename . $tablenamesuffix, $schemas[count($schemas)], $dbh); | |
| if( $str === false ) | |
| return true; | |
| else { | |
| echo $str; | |
| echo "..schema does not match after creation, error <br>\n"; | |
| return false; | |
| } | |
| } | |
| } | |
| $lastmatch = 0; | |
| $laststr = ''; | |
| for( $i = 1; $i <= count($schemas); $i++ ) { | |
| if( $i > 1 ) | |
| $result = remember_query($dbh, "DESCRIBE $tablename$tablenamesuffix"); | |
| $str = $this->table_has_schema($result, $tablename . $tablenamesuffix, $schemas[$i], $dbh); | |
| if( $str === false ) | |
| $lastmatch = $i; | |
| else | |
| $laststr = $str; | |
| } | |
| if( $lastmatch == 0 ) { | |
| if( $context->devmode | |
| || $context->testmode ) { | |
| echo "..no matching schema, deleting and recreating <br>\n"; | |
| remember_query($dbh, "DROP TABLE $tablename$tablenamesuffix"); | |
| mysqli_error_check($dbh); | |
| $this->create_db( $this->$createfunction($tablenamesuffix), $dbh ); | |
| $result = remember_query($dbh, "DESCRIBE $tablename$tablenamesuffix"); | |
| $str = $this->table_has_schema($result, $tablename . $tablenamesuffix, $schemas[count($schemas)], $dbh); | |
| if( $str === false ) | |
| return true; | |
| else { | |
| echo $str; | |
| echo "..schema does not match after creation, error <br>\n"; | |
| return false; | |
| } | |
| } else { | |
| echo $laststr; | |
| echo "..no matching schema, error <br>\n"; | |
| return false; | |
| } | |
| } | |
| if( $lastmatch == count($schemas) ) { | |
| echo "..correct <br>\n"; | |
| return true; | |
| } | |
| if( $context->devmode | |
| || $context->testmode | |
| || $databasename == 'myfoundation' ) { | |
| echo "..old schema, updating <br>\n"; | |
| for( $i = $lastmatch; $i < count($schemas); $i++ ) { | |
| $modifyfunction = 'modify_' . $tablename . '_' . $i . '_' . ($i+1); | |
| $this->$modifyfunction( $tablenamesuffix, $dbh ); | |
| } | |
| $result = remember_query($dbh, "DESCRIBE $tablename$tablenamesuffix"); | |
| $str = $this->table_has_schema($result, $tablename . $tablenamesuffix, $schemas[count($schemas)], $dbh); | |
| if( $str === false ) | |
| return true; | |
| else { | |
| echo $str; | |
| echo "..schema does not match after modification, error <br>\n"; | |
| return false; | |
| } | |
| } else { | |
| $this->updateTableFromSchema($table_name,$schemas[count($schemas)],$dbh,$context); | |
| echo "..old schema, error <br>\n"; | |
| return false; | |
| } | |
| } | |
| } | |
| private function createTableFromSchema($table_name,$schema,$dbh,$context){ | |
| $fields = $this->splitSchema($schema); | |
| $primary_keys = array(); | |
| foreach($fields as $field){ | |
| $query_guts .= $this->fieldToQuery($field,$primary_keys).","; | |
| } | |
| $query = "create table $table_name ( ".trim($query_guts,",\n"); | |
| if(!empty($primary_keys['PRI'])){ | |
| $query .= ", primary key(".implode($primary_keys['PRI'],",").")"; | |
| } | |
| if(!empty($primary_keys['MUL'])){ | |
| foreach($primary_keys['MUL'] as $mul){ | |
| $query .= ", index($mul)"; | |
| } | |
| } | |
| $query .= ")"; | |
| mysqli_query($dbh, $query); | |
| } | |
| //NEED TO HANDLE EDGES CASES | |
| // - WHEN ALL FIELDS ARE DROPPED FROM TABLE.. TREAT LIKE DROP TABLE CREATE TABLE WITH NEW SCHEMA. | |
| private function updateTableFromSchema($table_name,$schema,$dbh,$context){ | |
| $old_fields = $this->getTableDescription($table_name,$dbh); | |
| $new_fields = $this->splitSchema($schema); | |
| //run prerefactor quries | |
| print "\n";//gogo | |
| //find all old fields that have changed | |
| //find all fields to drop | |
| foreach($old_fields as $k => $v){ | |
| if($diff = array_diff($new_fields[$k], $v)){ | |
| print "MODIFY - $k\n"; | |
| if($diff['Default'] or $diff['Field']){ | |
| $modq = "alter table $table_name modify $k ".$new_fields[$k]['Type']." ".$new_fields[$k]['Default']. " ".$diff['Extra']; | |
| remember_query($dbh, $modq); | |
| } | |
| }else{ | |
| //drop new field | |
| print "DROP - $k\n"; | |
| $query = "alter table $table_name drop $k "; | |
| if($v['Key'] == "PRI"){ | |
| $query .= " DROP PRIMARY KEY "; | |
| } | |
| if($v['Key'] == "MUL"){ | |
| $query .= " DROP INDEX "; | |
| } | |
| mysqli_query($dbh, $query); | |
| } | |
| } | |
| //find all to add | |
| $endqueries[] = array(); | |
| foreach($new_fields as $k => $v){ | |
| if(!isset($old_fields[$k])){ | |
| print "add!!!! $k\n"; | |
| $keys = array(); | |
| $query = "alter table $table_name add "; | |
| $query.= $this->fieldToquery($v,&$keys); | |
| if(is_array($keys['PRI']) and in_array($k,$keys['PRI'])){ | |
| $query .= " PRIMARY KEY "; | |
| } | |
| if(is_array($keys['MUL']) and in_array($k,$keys['MUL'])){ | |
| // $query .= " INDEX "; | |
| $endqueries[] = "alter table $table_name add index ($k) "; | |
| } | |
| print $query."\n"; | |
| mysqli_query($dbh, $query); | |
| foreach($endqueries as $q){ | |
| mysqli_query($dbh, $q); | |
| } | |
| } | |
| } | |
| //check schema | |
| } | |
| private function fieldToquery($fields,&$keys){ | |
| //gogo | |
| // print_r($fields); | |
| list($type,$size) = explode("(" , trim($fields['Type'],") ") ); | |
| foreach($fields as $k => $v){ | |
| if($k == 'Null'){ | |
| if($v == 'NO'){ | |
| $v = "not null"; | |
| }else{ | |
| $v = "null"; | |
| } | |
| } | |
| if($k == 'Default'){ | |
| if($v != "") | |
| $v = "DEFAULT $v"; | |
| else | |
| $v = ""; | |
| } | |
| if($k == 'Key'){ | |
| switch($v){ | |
| case 'PRI': | |
| $keys['PRI'][] = $fields['Field']; | |
| break; | |
| case 'MUL': | |
| $keys['MUL'][] = $fields['Field']; | |
| break; | |
| default: | |
| break; | |
| } | |
| $v = ""; //clear the value they keys will be appened to end of query | |
| } | |
| $line .= " $v "; | |
| } | |
| return $line; | |
| } | |
| private function getTableDescription($table_name,$dbh){ | |
| $query = "DESCRIBE $table_name"; | |
| print $query; | |
| $result = mysqli_query($dbh, $query); | |
| $ret = array(); | |
| while( $row = mysqli_fetch_assoc($result) ) { | |
| $ret[$row['Field']] = $row; | |
| } | |
| return $ret; | |
| } | |
| private function splitSchema($schema){ //todo need to break out the same functionality from table_has_schema | |
| $lines = explode("\n",trim($schema)); | |
| $breaker = array_shift($lines); | |
| $fieldKeys = explode("|",trim(array_shift($lines),"|")); | |
| array_walk($fieldKeys, 'ourTrim'); | |
| array_shift($lines); //shift off the next filler line +----+--- | |
| while($line = array_shift($lines)){ | |
| if($line == $breaker){ | |
| break; | |
| } | |
| $fieldValues = explode("|",trim(trim($line),"|")); | |
| $aray = array_combine($fieldKeys,$fieldValues); | |
| $aray['seen'] = false; | |
| array_walk($aray, 'ourTrim'); | |
| $ret[$aray['Field']] = $aray; | |
| } | |
| // print_r($ret); | |
| return $ret; | |
| } | |
| private function create_db( $sql, $dbh ) { | |
| remember_query($dbh, $sql); | |
| mysqli_error_check($dbh); | |
| } | |
| /* returns error string if error, null if schema matches */ | |
| private function table_has_schema( $result, $tablename, $schemadescription, $dbh ) { | |
| $schema = $this->splitSchema($schemadescription); | |
| while( $row = mysqli_fetch_assoc($result) ) { | |
| if( !array_key_exists($row['Field'], $schema) ) { | |
| $rtrn .= "..column " . $row['Field'] . " not in schema <br>\n"; | |
| continue; | |
| } | |
| $schema[$row['Field']]['seen'] = true; | |
| $rec = $schema[$row['Field']]; | |
| if( $rec['Type'] != $row['Type'] ) { | |
| $rtrn .= "..column " . $row['Field'] . " with different type (" . $rec['Type'] . " vs " . $row['Type'] . ") <br>\n"; | |
| continue; | |
| } | |
| $a = $rec['Null']; | |
| $b = $row['Null']; | |
| if( $a == '' ) $a = 'NO'; | |
| if( $b == '' ) $b = 'NO'; | |
| /* some versions of MySQL seem to ignore NOT NULL when applied to timestamps */ | |
| if( $row['Type'] == 'timestamp' ) $b = 'NO'; | |
| /* */ | |
| if( $a != $b ) { | |
| $rtrn .= "..column " . $row['Field']. " with different NULL (" . $rec['Null'] . " vs " . $row['Null'] . ") <br>\n"; | |
| continue; | |
| } | |
| if( $rec['Key'] != $row['Key']) { | |
| if( $rec['Key']) { | |
| $rtrn .= "..column " . $row['Field']. " with different keys (" . $rec['Key'] . ") vs (" . $row['Key']. ") <br>\n"; | |
| continue; | |
| // note that we allow the table to have more keys than the schema, but not fewer | |
| } | |
| } | |
| if( $rec['Extra'] != $row['Extra']) { | |
| $rtrn .= "..column " . $row['Field']. " with different extra infomration (" . $rec['Extra'] . " vs " . $row['Extra'] . ") <br>\n"; | |
| continue; | |
| } | |
| // not able to check 'default' at this time | |
| } | |
| foreach( $schema as $rec ) { | |
| if( !$rec['seen']) { | |
| $rtrn .= "..column " . $rec['Field'] . " not in table <br>\n"; | |
| continue; | |
| } | |
| } | |
| if( $rtrn == '' ) return false; | |
| return $rtrn; | |
| } | |
| } | |
| ?> |