sqlite/trunk/nucleus/sqlite/sqlitealtertable.php

   1 <?php
   2     /****************************************
   3     * SQLite-MySQL wrapper for Nucleus      *
   4     *                           ver 0.9.0.2 *
   5     * Written by Katsumi   License: GPL     *
   6     ****************************************/
   7
   8 function sqlite_createtable_query($commands,$tablename,$temptable,&$morequeries){
   9         if ($temptable) $query="CREATE TEMPORARY TABLE $tablename (";
  10         else $query="CREATE TABLE $tablename (";
  11         $auto_increment=$first=true;
  12         foreach($commands as $key => $value) {
  13                 if (strpos(strtolower($value),'auto_increment')==strlen($value)-14) $auto_increment=true;
  14                 $isint=preg_match('/int\(([0-9]*?)\)/i',$value);
  15                 $isint=$isint | preg_match('/tinyint\(([0-9]*?)\)/i',$value);
  16                 $value=preg_replace('/int\(([0-9]*?)\)[\s]+unsigned/i','int($1)',$value);
  17                 $value=preg_replace('/int\([0-9]*?\)[\s]+NOT NULL[\s]+auto_increment$/i',' INTEGER NOT NULL PRIMARY KEY',$value);
  18                 $value=preg_replace('/int\([0-9]*?\)[\s]+auto_increment$/i',' INTEGER PRIMARY KEY',$value);
  19                 if ($auto_increment) $value=preg_replace('/^PRIMARY KEY(.*?)$/i','',$value);
  20                 while (preg_match('/PRIMARY KEY[\s]*\((.*)\([0-9]+\)(.*)\)/i',$value)) // Remove '(100)' from 'PRIMARY KEY (`xxx` (100))'
  21                         $value=preg_replace('/PRIMARY KEY[\s]*\((.*)\([0-9]+\)(.*)\)/i','PRIMARY KEY ($1 $2)',$value);
  22
  23                 // CREATE KEY queries for SQLite (corresponds to KEY 'xxxx'('xxxx', ...) of MySQL
  24                 if (preg_match('/^FULLTEXT KEY(.*?)$/i',$value,$matches)) {
  25                         array_push($morequeries,'CREATE INDEX '.str_replace('('," ON $tablename (",$matches[1]));
  26                         $value='';
  27                 } else if (preg_match('/^UNIQUE KEY(.*?)$/i',$value,$matches)) {
  28                         array_push($morequeries,'CREATE UNIQUE INDEX '.str_replace('('," ON $tablename (",$matches[1]));
  29                         $value='';
  30                 } else if (preg_match('/^KEY(.*?)$/i',$value,$matches)) {
  31                         array_push($morequeries,'CREATE INDEX '.str_replace('('," ON $tablename (",$matches[1]));
  32                         $value='';
  33                 }
  34
  35                 // Check if 'DEFAULT' is set when 'NOT NULL'
  36                 $uvalue=strtoupper($value);
  37                 if (strpos($uvalue,'NOT NULL')!==false &&
  38                                 strpos($uvalue,'DEFAULT')===false &&
  39                                 strpos($uvalue,'INTEGER NOT NULL PRIMARY KEY')===false) {
  40                         if ($isint) $value.=" DEFAULT 0";
  41                         else $value.=" DEFAULT ''";
  42                 }
  43
  44                 if ($value) {
  45                         if ($first) $first=false;
  46                         else $query.=',';
  47                          $query.=' '.$value;
  48                 }
  49         }
  50         $query.=' )';
  51         return $query;
  52 }
  53
  54 function sqlite_renametable($commands,$dbhandle){
  55         $carray=array();
  56         foreach($commands as $command){
  57                 $command=_sqlite_divideByChar(array(' ',"\t","\r","\n"),$command);
  58                 if (count($command)!=3) return sqlite_ReturnWithError(htmlspecialchars("near '$command[0]': syntax error"));
  59                 if (strtoupper($command[1])!='TO') return sqlite_ReturnWithError(htmlspecialchars("near '$command[1]': syntax error"));
  60                 $carray[str_replace("'",'',$command[0])]=str_replace("'",'',$command[2]);
  61         }
  62         foreach($carray as $old=>$new){
  63                 if (!sqlite_copytable($old,$new,$dbhandle)) return false;
  64                 if (!sqlite_query($dbhandle,"DROP TABLE $old")) return sqlite_ReturnWithError(htmlspecialchars("fail to remove table, '$old'"));
  65         }
  66         sqlite_query($dbhandle,'VACUUM');
  67         return true;
  68 }
  69
  70 function sqlite_copytable($table,$newname,$dbhandle,$newtablearray=array()){
  71         // Getting information from original table and create new table
  72         $res = sqlite_query($dbhandle,"SELECT sql,name,type FROM sqlite_master WHERE tbl_name = '".$table."' ORDER BY type DESC");
  73         if(!sqlite_num_rows($res)) return sqlite_ReturnWithError('no such table: '.$table);
  74         if (count($newtablearray)) {
  75                 $query="CREATE TABLE $newname (".implode(',',$newtablearray).')';
  76                 if (!sqlite_query($dbhandle,$query)) return sqlite_ReturnWithError('Table could not be created.');
  77         } else {
  78                 while($row=sqlite_fetch_array($res,SQLITE_ASSOC)){
  79                         if (!preg_match('/^([^\(]*)[\s]([^\(\']+)[\s]*\(([\s\S]*)$/',$row['sql'],$m) &&
  80                                 !preg_match('/^([^\(]*)[\s]\'([^\(]+)\'[\s]*\(([\s\S]*)$/',$row['sql'],$m)) return sqlite_ReturnWithError('unknown error');
  81                         if (!sqlite_query($dbhandle,$m[1]." '$newname' (".$m[3])) return sqlite_ReturnWithError('Table could not be created.');
  82                 }
  83         }
  84         // Copy the items
  85         sqlite_query($dbhandle,'BEGIN');
  86         $res=sqlite_unbuffered_query($dbhandle,"SELECT * FROM $table");
  87         while($row=sqlite_fetch_array($res,SQLITE_ASSOC)){
  88                 $keys=$values=array();
  89                 foreach($row as $key=>$value) {
  90                         if (count($newtablearray) && !isset($newtablearray[strtolower($key)])) continue;
  91                         $keys[]="'$key'";
  92                         $values[]="'".sqlite_escape_string($value)."'";
  93                 }
  94                 if (!sqlite_query($dbhandle,"INSERT INTO '$newname'(".implode(', ',$keys).') VALUES ('.implode(', ',$values).')')) {
  95                         sqlite_query($dbhandle,'COMMIT');
  96                         return false;
  97                 }
  98         }
  99         sqlite_query($dbhandle,'COMMIT');
 100         $orgnum=sqlite_array_query($dbhandle,"SELECT COUNT(*) FROM $table");
 101         $newnum=sqlite_array_query($dbhandle,"SELECT COUNT(*) FROM $newname");
 102         if ($orgnum[0][0]!=$newnum[0][0]) return sqlite_ReturnWithError('Data transfer failed.');
 103         return true;
 104 }
 105 function sqlite_altertable($table,$alterdefs,$dbhandle){
 106         // Almost completely re-written in February 2008.
 107         $table=str_replace("'",'',$table);
 108
 109         // Getting information from original table
 110         $res = sqlite_query($dbhandle,"SELECT sql,name,type FROM sqlite_master WHERE tbl_name = '".$table."' ORDER BY type DESC");
 111         if(!sqlite_num_rows($res)) return sqlite_ReturnWithError('no such table: '.$table);
 112         $orgindex=array();
 113         $row=sqlite_fetch_array($res,SQLITE_ASSOC); //table sql
 114         $orgsql=$row['sql'];
 115         while($row=sqlite_fetch_array($res,SQLITE_ASSOC)) $orgindex[strtolower($row['name'])]=$row['sql'];
 116         if (!preg_match('/^[^\(]+\((.*)\);*$/',$orgsql,$m)) return sqlite_ReturnWithError('unknown error');
 117         $orgtablearray=array();
 118         foreach(_sqlite_divideByChar(',',$orgtable=$m[1]) as $value){
 119                 if (!preg_match('/^([^\s\']+)[\s]+([\s\S]*)$/',$value,$m) &&
 120                         !preg_match('/^\'([^\']+)\'[\s]+([\s\S]*)$/',$value,$m)) return sqlite_ReturnWithError('unknown error');
 121                 $orgtablearray[strtolower($m[1])]="'$m[1]' $m[2]";
 122         }
 123         $desttablearray=$orgtablearray;
 124         $destindex=$orgindex;
 125
 126         // Convert table
 127         foreach(_sqlite_divideByChar(',',$alterdefs) as $def){
 128                 $def=_sqlite_divideByChar(array(' ',"\t","\r","\n"),trim($def));
 129                 if (($c=count($def))<2) return sqlite_ReturnWithError('near "'.htmlspecialchars($def[0]).'": syntax error');
 130                 // Check if FIRST/AFTER is used.
 131                 $first=$after=false;
 132                 if (strtoupper($def[$c-1])=='FIRST') {
 133                         $first=true;
 134                         array_pop($def);
 135                 } elseif (strtoupper($def[$c-2])=='AFTER') {
 136                         $after=strtolower(str_replace("'",'',array_pop($def)));
 137                         array_pop($def);
 138                 }
 139                 // Ignore CONSTRAINT and COLUMN
 140                 $method=strtoupper(array_shift($def));
 141                 switch(strtoupper($def[0])){
 142                 case 'CONSTRAINT': // delete two
 143                         array_shift($def);
 144                 case 'COLUMN': // delete one
 145                         array_shift($def);
 146                 default:
 147                         break;
 148                 }
 149                 // The main routine of this function follow.
 150                 switch($method){
 151                 case 'MODIFY':
 152                 case 'ALTER':
 153                         if (error_reporting() & E_NOTICE) sqlite_ReturnWithError('ALTER/MODIFY is not supported');
 154                         break;
 155                 case 'DROP':
 156                 case 'CHANGE':
 157                         if (strtoupper($def[0])=='INDEX') {
 158                                 // delete index
 159                                 unset($destindex[strtolower($def[1])]);
 160                         } else {
 161                                 // delete field
 162                                 unset($desttablearray[strtolower(str_replace("'",'',$def[0]))]);
 163                         }
 164                         if ($method!='CHANGE') break;
 165                 case 'ADD':
 166                         $field=array_shift($def);
 167                         switch($submedthod=strtoupper($field)){
 168                         case 'UNIQUE':
 169                         case 'PRIMARY':
 170                         case 'FOREIGN':
 171                         case 'INDEX':
 172                         case 'FULLTEXT':
 173                                 // add index
 174                                 if (strtoupper($index=array_shift($def))=='KEY') $index=array_shift($def);
 175                                 $def=implode(' ',$def);
 176                                 $destindex[strtolower(str_replace("'",'',$index))]=
 177                                         ($submedthod=='UNIQUE'?'CREATE UNIQUE INDEX ':'CREATE INDEX ').
 178                                         "$index ON '$table' $def";
 179                                 break;
 180                         default:
 181                                 // add field
 182                                 $field=str_replace("'",'',$field);
 183                                 if ($first) {
 184                                         $desttablearray=array_merge(
 185                                                 array(strtolower($field)=>"'$field' ".implode(' ',$def)),
 186                                                 $desttablearray);
 187                                 } elseif($after) {
 188                                         $temp=$desttablearray;
 189                                         $desttablearray=array();
 190                                         $ok=false;
 191                                         foreach($temp as $key=>$value) {
 192                                                 $desttablearray[$key]=$value;
 193                                                 if ($ok || $key!=$after) continue;
 194                                                 $ok=true;
 195                                                 $desttablearray[strtolower($field)]="'$field' ".implode(' ',$def);
 196                                         }
 197                                         if (!$ok) {
 198                                                 $desttablearray[strtolower($field)]="'$field' ".implode(' ',$def);
 199                                                 if (error_reporting() & E_NOTICE) sqlite_ReturnWithError(htmlspecialchars("Field '$after' not found."));
 200                                         }
 201                                 } else {
 202                                         $desttablearray[strtolower($field)]="'$field' ".implode(' ',$def);
 203                                 }
 204                         }
 205                         break;
 206                 default:
 207                         return sqlite_ReturnWithError('near "'.htmlspecialchars($method).'": syntax error');
 208                 }
 209         }
 210
 211         // Create temporary table that has the modified field and copy the items into it.
 212         if (function_exists('microtime')) $tmptable='t'.str_replace(array(' ','.'),'',microtime());
 213         else $tmptable = 't'.rand(0,999999).time();
 214         if (!sqlite_copytable($table,$tmptable,$dbhandle,$desttablearray)) return false;
 215
 216         // New temporary table sccusfully made.
 217         // So, delete the original table and copy back the temporary table to one with original name.
 218         sqlite_query("DROP TABLE $table",$dbhandle);
 219         if (sqlite_copytable($tmptable,$table,$dbhandle)) sqlite_query("DROP TABLE $tmptable",$dbhandle);
 220
 221         // Add the indexes, finally.
 222         foreach($destindex as $index) sqlite_query($index,$dbhandle);
 223         sqlite_query($dbhandle,'VACUUM');
 224         return true;
 225 }
 226 function sqlite_showKeysFrom($tname,$dbhandle) {
 227         // This function is for supporing 'SHOW KEYS FROM' and 'SHOW INDEX FROM'.
 228         // For making the same result as obtained by MySQL, temporary table is made.
 229         if (preg_match('/^([^\s]+)\s+LIKE\s+\'([^\']+)\'$/i',$tname,$m)) list($m,$tname,$like)=$m;
 230         $tname=str_replace("'",'',$tname);
 231
 232         // Create a temporary table for making result
 233         if (function_exists('microtime')) $tmpname='t'.str_replace('.','',str_replace(' ','',microtime()));
 234         else $tmpname = 't'.rand(0,999999).time();
 235         sqlite_query($dbhandle,"CREATE TEMPORARY TABLE $tmpname ('Table', 'Non_unique', 'Key_name', 'Seq_in_index',".
 236                 " 'Column_name', 'Collation', 'Cardinality', 'Sub_part', 'Packed', 'Null', 'Index_type', 'Comment')");
 237
 238         // First, get the sql query when the table created
 239         $res=sqlite_query($dbhandle,"SELECT sql FROM sqlite_master WHERE tbl_name = '$tname' ORDER BY type DESC");
 240         $a=nucleus_mysql_fetch_assoc($res);
 241         $tablesql=$a['sql'];
 242
 243         // Check if each columns are unique
 244         $notnull=array();
 245         foreach(_sqlite_divideByChar(',',substr($tablesql,strpos($tablesql,'(')+1)) as $value) {
 246                 $name=str_replace("'",'',substr($value,0,strpos($value,' ')));
 247                 if (strpos(strtoupper($value),'NOT NULL')!==false) $notnull[$name]='';
 248                 else $notnull[$name]='YES';
 249         }
 250
 251         // Get the primary key (and check if it is unique???).
 252         if (preg_match('/[^a-zA-Z_\']([\S]+)[^a-zA-Z_\']+INTEGER NOT NULL PRIMARY KEY/i',$tablesql,$matches)) {
 253                 $pkey=str_replace("'",'',$matches[1]);
 254                 $pkeynull='';
 255         } else if (preg_match('/[^a-zA-Z_\']([\S]+)[^a-zA-Z_\']+INTEGER PRIMARY KEY/i',$tablesql,$matches)) {
 256                 $pkey=str_replace("'",'',$matches[1]);
 257                 $pkeynull='YES';
 258         } else if (preg_match('/PRIMARY KEY[\s]*?\(([^\)]+)\)/i',$tablesql,$matches)) {
 259                 $pkey=null;// PRIMARY KEY ('xxx'[,'xxx'])
 260                 foreach(explode(',',$matches[1]) as $key=>$value) {
 261                         $value=str_replace("'",'',trim($value));
 262                         $key++;
 263                         $cardinality=nucleus_mysql_num_rows(sqlite_query($dbhandle,"SELECT '$value' FROM '$tname'"));
 264                         sqlite_query($dbhandle,"INSERT INTO $tmpname ('Table', 'Non_unique', 'Key_name', 'Seq_in_index',".
 265                                 " 'Column_name', 'Collation', 'Cardinality', 'Sub_part', 'Packed', 'Null', 'Index_type', 'Comment')".
 266                                 " VALUES ('$tname', '0', 'PRIMARY', '$key',".
 267                                 " '$value', 'A', '$cardinality', null, null, '', 'BTREE', '')");
 268                 }
 269         } else $pkey=null;
 270
 271         // Check the index.
 272         $res=sqlite_query($dbhandle,"SELECT sql,name FROM sqlite_master WHERE type = 'index' and tbl_name = '$tname' ORDER BY type DESC");
 273         while ($a=nucleus_mysql_fetch_assoc($res)) {
 274                 if (!($sql=$a['sql'])) {// Primary key
 275                         if ($pkey && strpos(strtolower($a['name']),'autoindex')) {
 276                                 $cardinality=nucleus_mysql_num_rows(sqlite_query($dbhandle,"SELECT $pkey FROM '$tname'"));
 277                                 sqlite_query($dbhandle,"INSERT INTO $tmpname ('Table', 'Non_unique', 'Key_name', 'Seq_in_index',".
 278                                         " 'Column_name', 'Collation', 'Cardinality', 'Sub_part', 'Packed', 'Null', 'Index_type', 'Comment')".
 279                                         " VALUES ('$tname', '0', 'PRIMARY', '1',".
 280                                         " '$pkey', 'A', '$cardinality', null, null, '$pkeynull', 'BTREE', '')");
 281                                 $pkey=null;
 282                         }
 283                 } else {// Non-primary key
 284                         if (($name=str_replace("'",'',$a['name'])) && preg_match('/\(([\s\S]+)\)/',$sql,$matches)) {
 285                         if (isset($like) && $name!=$like) continue;
 286                                 foreach(explode(',',$matches[1]) as $key=>$value) {
 287                                         $columnname=str_replace("'",'',$value);
 288                                         if (strpos(strtoupper($sql),'CREATE UNIQUE ')===0) $nonunique='0';
 289                                         else $nonunique='1';
 290                                         $cardinality=nucleus_mysql_num_rows(sqlite_query($dbhandle,"SELECT $columnname FROM '$tname'"));
 291                                         sqlite_query($dbhandle,"INSERT INTO $tmpname ('Table', 'Non_unique', 'Key_name', 'Seq_in_index',".
 292                                                 " 'Column_name', 'Collation', 'Cardinality', 'Sub_part', 'Packed', 'Null', 'Index_type', 'Comment')".
 293                                                 " VALUES ('$tname', '$nonunique', '$name', '".(string)($key+1)."',".
 294                                                 " '$columnname', 'A', '$cardinality', null, null, '$notnull[$columnname]', 'BTREE', '')");
 295                                 }
 296                         }
 297                 }
 298         }
 299         if ($pkey) { // The case that the key (index) is not defined.
 300                 $cardinality=nucleus_mysql_num_rows(sqlite_query($dbhandle,"SELECT $pkey FROM '$tname'"));
 301                 sqlite_query($dbhandle,"INSERT INTO $tmpname ('Table', 'Non_unique', 'Key_name', 'Seq_in_index',".
 302                         " 'Column_name', 'Collation', 'Cardinality', 'Sub_part', 'Packed', 'Null', 'Index_type', 'Comment')".
 303                         " VALUES ('$tname', '0', 'PRIMARY', '1',".
 304                         " '$pkey', 'A', '$cardinality', null, null, '$pkeynull', 'BTREE', '')");
 305                 $pkey=null;
 306         }
 307
 308         // return the final query to show the keys in MySQL style (using temporary table).
 309         return "SELECT * FROM $tmpname";
 310 }
 311 function sqlite_showFieldsFrom($tname,$dbhandle){
 312         // This function is for supporing 'SHOW FIELDS FROM' and 'SHOW COLUMNS FROM'.
 313         // For making the same result as obtained by MySQL, temporary table is made.
 314         if (preg_match('/^([^\s]+)\s+LIKE\s+\'([^\']+)\'$/i',$tname,$m)) list($m,$tname,$like)=$m;
 315         $tname=str_replace("'",'',$tname);
 316
 317         // First, get the sql query when the table created
 318         $res=sqlite_query($dbhandle,"SELECT sql FROM sqlite_master WHERE tbl_name = '$tname' ORDER BY type DESC");
 319         $a=nucleus_mysql_fetch_assoc($res);
 320         $tablesql=trim($a['sql']);
 321         if (preg_match('/^[^\(]+\(([\s\S]*?)\)$/',$tablesql,$matches)) $tablesql=$matches[1];
 322         $tablearray=array();
 323         foreach(_sqlite_divideByChar(',',$tablesql) as $value) {
 324                 $value=trim($value);
 325                 if ($i=strpos($value,' ')) {
 326                         $name=str_replace("'",'',substr($value,0,$i));
 327                         $value=trim(substr($value,$i));
 328                         if (substr($value,-1)==',') $value=substr($value,strlen($value)-1);
 329                         $tablearray[$name]=$value;
 330                 }
 331         }
 332
 333         // Check if INDEX has been made for the parameter 'MUL' in 'KEY' column
 334         $multi=array();
 335         $res=sqlite_query($dbhandle,"SELECT name FROM sqlite_master WHERE type = 'index' and tbl_name = '$tname' ORDER BY type DESC");
 336         while ($a=nucleus_mysql_fetch_assoc($res)) $multi[str_replace("'",'',$a['name'])]='MUL';
 337
 338         // Create a temporary table for making result
 339         if (function_exists('microtime')) $tmpname='t'.str_replace('.','',str_replace(' ','',microtime()));
 340         else $tmpname = 't'.rand(0,999999).time();
 341         sqlite_query($dbhandle,"CREATE TEMPORARY TABLE $tmpname ('Field', 'Type', 'Null', 'Key', 'Default', 'Extra')");
 342
 343         // Check the table
 344         foreach($tablearray as $field=>$value) {
 345                 if (strtoupper($field)=='PRIMARY') continue;//PRIMARY KEY('xx'[,'xx'])
 346                 if (isset($like) && $field!=$like) continue;
 347                 $uvalue=strtoupper($value.' ');
 348                 $key=(string)$multi[$field];
 349                 if ($uvalue=='INTEGER NOT NULL PRIMARY KEY ' || $uvalue=='INTEGER PRIMARY KEY ') {
 350                         $key='PRI';
 351                         $extra='auto_increment';
 352                 } else $extra='';
 353                 if ($i=strpos($uvalue,' ')) {
 354                         $type=substr($value,0,$i);
 355                         if (strpos($type,'(') && ($i=strpos($value,')')))
 356                                 $type=substr($value,0,$i+1);
 357                 } else $type='';
 358                 if (strtoupper($type)=='INTEGER') $type='int(11)';
 359                 if (strpos($uvalue,'NOT NULL')===false) $null='YES';
 360                 else {
 361                         $null='';
 362                         $value=preg_replace('/NOT NULL/i','',$value);
 363                         $uvalue=strtoupper($value);
 364                 }
 365                 if ($i=strpos($uvalue,'DEFAULT')) {
 366                         $default=trim(substr($value,$i+7));
 367                         if (strtoupper($default)=='NULL') {
 368                                 $default="";
 369                                 $setdefault="";
 370                         } else {
 371                                 if (substr($default,0,1)=="'") $default=substr($default,1,strlen($default)-2);
 372                                 $default="'".$default."',";
 373                                 $setdefault="'Default',";
 374                         }
 375                 } else if ($null!='YES' && $extra!='auto_increment') {
 376                         if (strpos(strtolower($type),'int')===false) $default="'',";
 377                         else $default="'0',";
 378                         $setdefault="'Default',";
 379                 } else {
 380                         $default="";
 381                         $setdefault="";
 382                 }
 383                 sqlite_query($dbhandle,"INSERT INTO '$tmpname' ('Field', 'Type', 'Null', 'Key', $setdefault 'Extra')".
 384                         " VALUES ('$field', '$type', '$null', '$key', $default '$extra')");
 385         }
 386
 387         // return the final query to show the keys in MySQL style (using temporary table).
 388         return "SELECT * FROM $tmpname";
 389 }
 390
 391 ?>