| <?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; |
| } |
| } |
| |
| |
| ?> |
