blob: fd751ec360459376062ea83082efaea20abb7a54 [file] [log] [blame]
<?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 ";
mysql_remember_query( $sql, $dbh );
mysql_error_check();
$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 = mysql_remember_query( "DESCRIBE $tablename$tablenamesuffix", $dbh );
if( strlen(mysql_error()) > 0 ) {
if( $context->devmode
|| $context->testmode
|| $databasename == 'myfoundation' ) {
echo "..does not exist, creating <br>\n";
$this->create_db( $this->$createfunction($tablenamesuffix), $dbh );
$result = mysql_remember_query( "DESCRIBE $tablename$tablenamesuffix", $dbh );
$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 mysql_error();
echo "..does not exist, error <br>\n";
$this->createTableFromSchema($table_name,$schemas[count($schemas)],$dbh,$context);
$result = mysql_remember_query( "DESCRIBE $tablename$tablenamesuffix", $dbh );
$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 = mysql_remember_query( "DESCRIBE $tablename$tablenamesuffix", $dbh );
$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";
mysql_remember_query( "DROP TABLE $tablename$tablenamesuffix", $dbh);
mysql_error_check();
$this->create_db( $this->$createfunction($tablenamesuffix), $dbh );
$result = mysql_remember_query( "DESCRIBE $tablename$tablenamesuffix", $dbh );
$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 = mysql_remember_query( "DESCRIBE $tablename$tablenamesuffix", $dbh );
$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'];
mysql_remember_query($modq,$dbh);
}
}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($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($query);
foreach($endqueries as $q){
mysqli_query($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 = mysql_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 ) {
mysql_remember_query( $sql, $dbh );
mysql_error_check();
}
/* returns error string if error, null if schema matches */
private function table_has_schema( $result, $tablename, $schemadescription, $dbh ) {
$schema = $this->splitSchema($schemadescription);
while( $row = mysql_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;
}
}
?>