2 /***************************************
3 * SQLite-MySQL wrapper for Nucleus *
6 ***************************************/
8 // The licence of this script is GPL
12 // I thank all the people of Nucleus JP forum
13 // who discussed this project. Especially, I
14 // thank kosugiatkips, mekyo, and nakahara21
15 // for ideas of some part of code.
16 // I also thank Jon Jensen for his generous
17 // acceptance for using his PHP code in this
20 // The features that are supported by this script but not
21 // generally by SQLite are as follows:
23 // CREATE TABLE IF NOT EXISTS, auto_increment,
24 // DROP TABLE IF EXISTS, ALTER TABLE,
25 // INSERT INTO ... SET xx=xx, xx=xx,
26 // REPLACE INTO ... SET xx=xx, xx=xx,
27 // SHOW KEYS FROM, SHOW INDEX FROM,
28 // SHOW FIELDS FROM, SHOW COLUMNS FROM,
29 // CREATE TABLE ... KEYS xxx (xxx,xxx)
30 // SHOW TABLES LIKE, TRUNCATE TABLE
35 // -This is the first established version and
36 // exactly the same as ver 0.7.8b.
39 // -Execute "PRAGMA short_column_names=1" first.
40 // -Avoid executing outside php file in some very specfic environment.
41 // -Avoid executing multiple queries using ";" as delimer.
42 // -Add check routine for the installed SQLite
44 // Check SQLite installed
46 if (!function_exists('sqlite_open')) exit('Sorry, SQLite is not installed in the server.');
48 // Initializiation stuff
49 require_once dirname(__FILE__) . '/sqliteconfig.php';
50 $SQLITE_DBHANDLE=sqlite_open($SQLITECONF['DBFILENAME']);
51 require_once dirname(__FILE__) . '/sqlitequeryfunctions.php';
52 $SQLITECONF['VERSION']='0.8.1';
54 function sqlite_createQueryFunction($queryf,$globalf){
55 global $SQLITE_DBHANDLE;
56 if (function_exists($globalf)) sqlite_create_function($SQLITE_DBHANDLE,$queryf,$globalf);
59 //Following thing may work if MySQL is NOT installed in server.
60 if (!function_exists('mysql_query')) {
61 define ("MYSQL_ASSOC", SQLITE_ASSOC);
62 define ("MYSQL_BOTH", SQLITE_BOTH);
63 define ("MYSQL_NUM", SQLITE_NUM);
64 function mysql_connect(){
66 $SQLITECONF['OVERRIDEMODE']=true;
67 $args=func_get_args();
68 return call_user_func_array('nucleus_mysql_connect',$args);
70 foreach (array('mysql_affected_rows','mysql_change_user','mysql_client_encoding','mysql_close',
71 'mysql_create_db','mysql_data_seek','mysql_db_name','mysql_db_query','mysql_drop_db','mysql_errno',
72 'mysql_error','mysql_escape_string','mysql_fetch_array','mysql_fetch_assoc','mysql_fetch_field','mysql_fetch_lengths',
73 'mysql_fetch_object','mysql_fetch_row','mysql_field_flags','mysql_field_len','mysql_field_name','mysql_field_seek',
74 'mysql_field_table','mysql_field_type','mysql_free_result','mysql_get_client_info','mysql_get_host_info',
75 'mysql_get_proto_info','mysql_get_server_info','mysql_info','mysql_insert_id','mysql_list_dbs',
76 'mysql_list_fields','mysql_list_processes','mysql_list_tables','mysql_num_fields','mysql_num_rows','mysql_numrows',
77 'mysql_pconnect','mysql_ping','mysql_query','mysql_real_escape_string','mysql_result','mysql_select_db',
78 'mysql_stat','mysql_tablename','mysql_thread_id','mysql_unbuffered_query')
80 "function $value(){\n".
81 " \$args=func_get_args();\n".
82 " return call_user_func_array('nucleus_$value',\$args);\n".
86 // Empty object for mysql_fetch_object().
87 class SQLITE_OBJECT {}
89 function sqlite_ReturnWithError($text='Not supported',$more=''){
90 // Show warning when error_reporting() is set.
91 if (!(error_reporting() & E_WARNING)) return false;
93 // Seek the file and line that originally called sql function.
95 foreach($a as $key=>$btrace) {
96 if (!($templine=$btrace['line'])) continue;
97 if (!($tempfile=$btrace['file'])) continue;
98 $file=str_replace("\\",'/',$file);
99 if (!$line && !$file && strpos($tempfile,'/sqlite.php')===false && strpos($tempfile,'/sqlitequeryfunctions.php')===false) {
103 echo "\n<!--$tempfile line:$templine-->\n";
105 echo "Warning from SQLite-MySQL wrapper: $text<br />\n";
106 if ($line && $file) echo "in <b>$file</b> on line <b>$line</b><br />\n";
110 function sqlite_DebugMessage($text=''){
112 if (!$SQLITECONF['DEBUGREPORT']) return;
113 if ($text) $SQLITECONF['DEBUGMESSAGE'].="\n".$text."\n";
114 if (headers_sent()) {
115 echo '<!--sqlite_DebugMessage'.$SQLITECONF['DEBUGMESSAGE'].'sqlite_DebugMessage-->';
116 unset($SQLITECONF['DEBUGMESSAGE']);
120 // nucleus_mysql_XXXX() functions follow.
122 function nucleus_mysql_connect($p1=null,$p2=null,$p3=null,$p4=null,$p5=null){
123 // All prameters are ignored.
124 global $SQLITE_DBHANDLE,$SQLITECONF;
125 if (!$SQLITE_DBHANDLE) $SQLITE_DBHANDLE=sqlite_open($SQLITECONF['DBFILENAME']);
126 // Initialization queries.
127 foreach($SQLITECONF['INITIALIZE'] as $value) nucleus_mysql_query($value);
128 return $SQLITE_DBHANDLE;
131 function nucleus_mysql_close($p1=null){
132 global $SQLITE_DBHANDLE;
133 if (!($dbhandle=$p1)) $dbhandle=$SQLITE_DBHANDLE;
135 return sqlite_close ($dbhandle);
138 function nucleus_mysql_select_db($p1,$p2=null){
139 // SQLite does not support multiple databases in a file.
140 // So this function do nothing and always returns true.
141 // Note: mysql_select_db() function returns true/false,
146 function nucleus_mysql_query($p1,$p2=null,$unbuffered=false){//echo htmlspecialchars($p1)."<br />\n";
147 global $SQLITE_DBHANDLE,$SQLITECONF;
148 if (!($dbhandle=$p2)) $dbhandle=$SQLITE_DBHANDLE;
150 if (strpos($query,"\xEF\xBB\xBF")===0) $query=substr($query,3);// UTF-8 stuff
151 if (substr($query,-1)==';') $query=substr($query,0,strlen($query)-1);
153 // Escape style is changed from MySQL type to SQLite type here.
154 // This is important to avoid possible SQL-injection.
155 $strpositions=array();// contains the data show where the strings are (startposition => endposition)
156 if (strpos($query,'`')!==false || strpos($query,'"')!==false || strpos($query,"'")!==false)
157 $strpositions=sqlite_changeQuote($query);
158 //echo "<br />".htmlspecialchars($p1)."<br /><br />\n".htmlspecialchars($query)."<hr />\n";
161 if ($SQLITECONF['DEBUGMODE']) $query=sqlite_mysql_query_debug($query);
165 if ($ret=@sqlite_unbuffered_query($dbhandle,$query)) return $ret;
167 if ($ret=@sqlite_query($dbhandle,$query)) return $ret;
170 // Error occured. Query must be translated.
171 return sqlite_mysql_query_sub($dbhandle,$query,$strpositions,$p1,$unbuffered);
173 function sqlite_mysql_query_sub($dbhandle,$query,$strpositions=array(),$p1=null,$unbuffered=false){//echo htmlspecialchars($p1)."<br />\n";
174 // Query translation is needed, especially when changing the data in database.
175 // So far, this routine is written for 'CREATE TABLE','DROP TABLE', 'INSERT INTO',
176 // 'SHOW TABLES LIKE', 'SHOW KEYS FROM', 'SHOW INDEX FROM'
177 // and several functions used in query.
178 // How about 'UPDATE' ???
179 global $SQLITE_DBHANDLE,$SQLITECONF;
180 $beforetrans=time()+microtime();
181 if (!$p1) $p1=$query;
182 $morequeries=array();
183 $uquery=strtoupper($query);
184 if (strpos($uquery,'CREATE TABLE')===0 || ($temptable=(strpos($uquery,'CREATE TEMPORARY TABLE')===0))) {
185 if (!($i=strpos($query,'('))) return sqlite_ReturnWithError('nucleus_mysql_query: '.$p1);
186 //check if the command is 'CREATE TABLE IF NOT EXISTS'
187 if (strpos(strtoupper($uquery),'CREATE TABLE IF NOT EXISTS')===0) {
188 $tablename=trim(substr($query,26,$i-26));
189 if (substr($tablename,0,1)!="'") $tablename="'$tablename'";
190 $res=sqlite_query($dbhandle,"SELECT tbl_name FROM sqlite_master WHERE tbl_name=$tablename LIMIT 1");
191 if (nucleus_mysql_num_rows($res)) return true;
193 $tablename=trim(substr($query,12,$i-12));
194 if (substr($tablename,0,1)!="'") $tablename="'$tablename'";
197 $query=trim(substr($query,$i+1));
198 for ($i=strlen($query);0<$i;$i--) if ($query[$i]==')') break;
199 $query=substr($query,0,$i);
200 $auto_increment=false;
201 $commands=sqlite_splitByComma($query);
204 foreach($commands as $key => $value) {
205 if (strpos(strtolower($value),'auto_increment')==strlen($value)-14) $auto_increment=true;
206 $isint=preg_match('/int\(([0-9]*?)\)/i',$value);
207 $isint=$isint | preg_match('/tinyint\(([0-9]*?)\)/i',$value);
208 $value=preg_replace('/int\(([0-9]*?)\)[\s]+unsigned/i','int($1)',$value);
209 $value=preg_replace('/int\([0-9]*?\)[\s]+NOT NULL[\s]+auto_increment$/i',' INTEGER NOT NULL PRIMARY KEY',$value);
210 $value=preg_replace('/int\([0-9]*?\)[\s]+auto_increment$/i',' INTEGER PRIMARY KEY',$value);
211 if ($auto_increment) $value=preg_replace('/^PRIMARY KEY(.*?)$/i','',$value);
212 while (preg_match('/PRIMARY KEY[\s]*\((.*)\([0-9]+\)(.*)\)/i',$value)) // Remove '(100)' from 'PRIMARY KEY (`xxx` (100))'
213 $value=preg_replace('/PRIMARY KEY[\s]*\((.*)\([0-9]+\)(.*)\)/i','PRIMARY KEY ($1 $2)',$value);
215 // CREATE KEY queries for SQLite (corresponds to KEY 'xxxx'('xxxx', ...) of MySQL
216 if (preg_match('/^FULLTEXT KEY(.*?)$/i',$value,$matches)) {
217 array_push($morequeries,'CREATE INDEX '.str_replace('('," ON $tablename (",$matches[1]));
219 } else if (preg_match('/^UNIQUE KEY(.*?)$/i',$value,$matches)) {
220 array_push($morequeries,'CREATE UNIQUE INDEX '.str_replace('('," ON $tablename (",$matches[1]));
222 } else if (preg_match('/^KEY(.*?)$/i',$value,$matches)) {
223 array_push($morequeries,'CREATE INDEX '.str_replace('('," ON $tablename (",$matches[1]));
227 // Check if 'DEFAULT' is set when 'NOT NULL'
228 $uvalue=strtoupper($value);
229 if (strpos($uvalue,'NOT NULL')!==false &&
230 strpos($uvalue,'DEFAULT')===false &&
231 strpos($uvalue,'INTEGER NOT NULL PRIMARY KEY')===false) {
232 if ($isint) $value.=" DEFAULT 0";
233 else $value.=" DEFAULT ''";
237 if ($first) $first=false;
243 if ($temptable) $query='CREATE TEMPORARY TABLE '.$tablename.$query;
244 else $query='CREATE TABLE '.$tablename.$query;
245 //echo "<br />".htmlspecialchars($p1)."<br /><br />\n".htmlspecialchars($query)."<hr />\n";
246 } else if (strpos($uquery,'DROP TABLE IF EXISTS')===0) {
247 if (!($i=strpos($query,';'))) $i=strlen($query);
248 $tablename=trim(substr($query,20,$i-20));
249 if (substr($tablename,0,1)!="'") $tablename="'$tablename'";
250 $res=sqlite_query($dbhandle,"SELECT tbl_name FROM sqlite_master WHERE tbl_name=$tablename LIMIT 1");
251 if (!nucleus_mysql_num_rows($res)) return true;
252 $query='DROP TABLE '.$tablename;
253 } else if (strpos($uquery,'ALTER TABLE ')===0) {
254 $query=trim(substr($query,11));
255 if ($i=strpos($query,' ')) {
256 $tablename=trim(substr($query,0,$i));
257 $query=trim(substr($query,$i));
258 $ret =sqlite_altertable($tablename,$query,$dbhandle);
259 if (!$ret) sqlite_ReturnWithError('SQL error',"<br /><i>".nucleus_mysql_error()."</i><br />".htmlspecialchars($p1)."<br /><br />\n".htmlspecialchars("ALTER TABLE $tablename $query")."<hr />\n");
263 $query=='DROP TABLE '.$query;
264 } else if (strpos($uquery,'INSERT INTO ')===0 || strpos($uquery,'REPLACE INTO ')===0 ||
265 strpos($uquery,'INSERT IGNORE INTO ')===0 || strpos($uquery,'REPLACE IGNORE INTO ')===0) {
266 $buff=str_replace(' IGNORE ',' OR IGNORE ',substr($uquery,0,($i=strpos($uquery,' INTO ')+6)));
267 $query=trim(substr($query,$i));
268 if ($i=strpos($query,' ')) {
269 $buff.=trim(substr($query,0,$i+1));
270 $query=trim(substr($query,$i));
272 if ($i=strpos($query,' ')) {
273 if (strpos(strtoupper($query),'SET')===0) {
274 $query=trim(substr($query,3));
275 $commands=sqlite_splitByComma($query);
278 foreach($commands as $key=>$value){
279 //echo "[".htmlspecialchars($value)."]";
284 if ($i=strpos($value,'=')) {
285 $buff.=trim(substr($value,0,$i));
286 $query.=substr($value,$i+1);
293 $commands=sqlite_splitByComma($query);
295 foreach($commands as $key=>$value){
296 if ($beforevalues=='' && preg_match('/^(.*)\)\s+VALUES\s+\(/i',$value,$matches)) {
297 $beforevalues=$buff.' '.$query.$matches[1].')';
299 if (0<$key) $query.=$beforevalues.' VALUES ';// supports multiple insertion
304 $query=$buff.' '.$query;
305 } else if (strpos($uquery,'SHOW TABLES LIKE ')===0) {
306 $query='SELECT name FROM sqlite_master WHERE type=\'table\' AND name LIKE '.substr($query,17);
307 } else if (strpos($uquery,'SHOW TABLES')===0) {
308 $query='SELECT name FROM sqlite_master WHERE type=\'table\'';
309 } else if (strpos($uquery,'SHOW KEYS FROM ')===0) {
310 $query=sqlite_showKeysFrom(trim(substr($query,15)),$dbhandle);
311 } else if (strpos($uquery,'SHOW INDEX FROM ')===0) {
312 $query=sqlite_showKeysFrom(trim(substr($query,16)),$dbhandle);
313 } else if (strpos($uquery,'SHOW FIELDS FROM ')===0) {
314 $query=sqlite_showFieldsFrom(trim(substr($query,17)),$dbhandle);
315 } else if (strpos($uquery,'SHOW COLUMNS FROM ')===0) {
316 $query=sqlite_showFieldsFrom(trim(substr($query,18)),$dbhandle);
317 } else if (strpos($uquery,'TRUNCATE TABLE ')===0) {
318 $query='DELETE FROM '.substr($query,15);
319 } else sqlite_modifyQueryForUserFunc($query,$strpositions);
322 $aftertrans=time()+microtime();
324 $ret=sqlite_unbuffered_query($dbhandle,$query);
326 $ret=sqlite_query($dbhandle,$query);
329 $afterquery=time()+microtime();
330 if ($SQLITECONF['MEASURESPEED']) sqlite_DebugMessage("translated query:$query\n".
331 'translation: '.($aftertrans-$beforetrans).'sec, query: '.($afterquery-$aftertrans).'sec');
332 if (!$ret) sqlite_ReturnWithError('SQL error',"<br /><i>".nucleus_mysql_error()."</i><br />".htmlspecialchars($p1)."<br /><br />\n".htmlspecialchars($query)."<hr />\n");
333 foreach ($morequeries as $value) if ($value) @sqlite_query($dbhandle,$value);
336 function sqlite_changeQuote(&$query){
337 // This function is most important.
338 // When you modify this function, do it very carefully.
339 // Otherwise, you may allow crackers to do SQL-injection.
340 // This function returns array that shows where the strings are.
343 $qlen=strlen($query);
344 for ($i=0;$i<$qlen;$i++) {
346 if (($i1=strpos($query,'"',$i))===false) $i1=$qlen;
347 if (($i2=strpos($query,"'",$i))===false) $i2=$qlen;
348 if (($i3=strpos($query,'`',$i))===false) $i3=$qlen;
349 if ($i1==$qlen && $i2==$qlen && $i3==$qlen) {
350 $ret.=($temp=substr($query,$i));
351 if (strstr($temp,';')) exit('Warning: try to use more than two queries?');
354 if ($i2<($j=$i1)) $j=$i2;
356 $ret.=($temp=substr($query,$i,$j-$i));
357 $c=$query[($i=$j)]; // $c keeps the type of quote.
358 if (strstr($temp,';')) exit('Warning: try to use more than two queries?');
360 // Check between quotes.
361 // $j shows the begging positioin.
362 // $i will show the ending position.
365 if (($i1=strpos($query,$c,$i))===false) $i1=$qlen;
366 if (($i2=strpos($query,"\\",$i))===false) $i2=$qlen;
368 // \something. Skip two characters.
371 } if ($i1<($qlen-1) && $query[$i1+1]==$c) {
372 // "", '' or ``. Skip two characters.
375 } else {// OK. Reached the end position
381 $ret.="'".sqlite_changeslashes(substr($query,$j,$i-$j));
382 if ($i<$qlen) $ret.="'"; //else Syntax error in query.
385 }//echo htmlspecialchars($query).'<br />'.htmlspecialchars($ret).'<br />';
389 function sqlite_splitByComma($query) {
390 // The query is splitted by comma and the data will be put into an array.
391 // The commas in quoted strings are ignored.
396 if ($query[$i]=="'") {
398 while ($i<strlen($query)) {
399 if ($query[$i++]!="'") continue;
400 if ($query[$i]!="'") break;
404 } else if ($query[$i]=='(') $in=true;
405 else if ($query[$i]==')') $in=false;
406 else if ($query[$i]==',' && (!$in)) {
407 $commands[]=trim(substr($query,0,$i));
408 $query=trim(substr($query,$i+1));
411 } // Do NOT add 'else' statement here! '$i++' is important in the following line.
412 if (strlen($query)<=($i++)) break;
414 if ($query) $commands[]=$query;
417 function sqlite_changeslashes(&$text){
418 // By SQLite, "''" is used in the quoted string instead of "\'".
419 // In addition, only "'" seems to be allowed for perfect quotation of string.
420 // This routine is used for the conversion from MySQL type to SQL type.
421 // Do NOT use stripslashes() but use stripcslashes(). Otherwise, "\r\n" is not converted.
422 if ($text==='') return '';
423 return (sqlite_escape_string (stripcslashes((string)$text)));
425 function sqlite_altertable($table,$alterdefs,$dbhandle){
426 // This function originaly came from Jon Jensen's PHP class, SQLiteDB.
427 // There are some modifications by Katsumi.
428 $table=str_replace("'",'',$table);
429 if (!$alterdefs) return false;
430 $result = sqlite_query($dbhandle,"SELECT sql,name,type FROM sqlite_master WHERE tbl_name = '".$table."' ORDER BY type DESC");
431 if(!sqlite_num_rows($result)) return sqlite_ReturnWithError('no such table: '.$table);
432 $row = sqlite_fetch_array($result); //table sql
433 if (function_exists('microtime')) $tmpname='t'.str_replace('.','',str_replace(' ','',microtime()));
434 else $tmpname = 't'.rand(0,999999).time();
435 $origsql = trim(preg_replace("/[\s]+/"," ",str_replace(",",", ",preg_replace("/[\(]/","( ",$row['sql'],1))));
436 $createtemptableSQL = 'CREATE TEMPORARY '.substr(trim(preg_replace("'".$table."'",$tmpname,$origsql,1)),6);
437 $createindexsql = array();
439 $defs = preg_split("/[,]+/",$alterdefs,-1,PREG_SPLIT_NO_EMPTY);
441 $oldcols = preg_split("/[,]+/",substr(trim($createtemptableSQL),strpos(trim($createtemptableSQL),'(')+1),-1,PREG_SPLIT_NO_EMPTY);
443 for($i=0;$i<sizeof($oldcols);$i++){
444 $colparts = preg_split("/[\s]+/",$oldcols[$i],-1,PREG_SPLIT_NO_EMPTY);
445 $oldcols[$i] = $colparts[0];
446 $newcols[$colparts[0]] = $colparts[0];
451 while(list($key,$val) = each($newcols)){
452 if (strtoupper($val)!='PRIMARY' && strtoupper($key)!='PRIMARY' &&
453 strtoupper($val)!='UNIQUE' && strtoupper($key)!='UNIQUE'){
454 $newcolumns .= ($newcolumns?', ':'').$val;
455 $oldcolumns .= ($oldcolumns?', ':'').$key;
458 $copytotempsql = 'INSERT INTO '.$tmpname.'('.$newcolumns.') SELECT '.$oldcolumns.' FROM '.$table;
459 $dropoldsql = 'DROP TABLE '.$table;
460 $createtesttableSQL = $createtemptableSQL;
461 foreach($defs as $def){
462 $defparts = preg_split("/[\s]+/",$def,-1,PREG_SPLIT_NO_EMPTY);
463 $action = strtolower($defparts[0]);
466 // Modification does not mean anything for SQLite, so just return true.
467 // But this command will be supported in future???
470 if(($i=sizeof($defparts)) <= 2) return sqlite_ReturnWithError('near "'.$defparts[0].($defparts[1]?' '.$defparts[1]:'').'": syntax error');
472 // ignore if there is already such table
474 foreach($oldcols as $value) if (str_replace("'",'',$defparts[1])==str_replace("'",'',$value)) $exists=true;
477 // Ignore 'AFTER xxxx' statement.
478 // Maybe this feature will be supprted later.
479 if (4<=$i && strtoupper($defparts[$i-2])=='AFTER')
480 unset($defparts[$i-1],$defparts[$i-2]);
482 $createtesttableSQL = substr($createtesttableSQL,0,strlen($createtesttableSQL)-1).',';
483 for($i=1;$i<sizeof($defparts);$i++) $createtesttableSQL.=' '.$defparts[$i];
484 $createtesttableSQL.=')';
487 if(sizeof($defparts) <= 3) return sqlite_ReturnWithError('near "'.$defparts[0].($defparts[1]?' '.$defparts[1]:'').($defparts[2]?' '.$defparts[2]:'').'": syntax error');
488 if($severpos = strpos($createtesttableSQL,' '.$defparts[1].' ')){
489 if($newcols[$defparts[1]] != $defparts[1]){
490 sqlite_ReturnWithError('unknown column "'.$defparts[1].'" in "'.$table.'"');
493 $newcols[$defparts[1]] = $defparts[2];
494 $nextcommapos = strpos($createtesttableSQL,',',$severpos);
496 for($i=2;$i<sizeof($defparts);$i++) $insertval.=' '.$defparts[$i];
497 if($nextcommapos) $createtesttableSQL = substr($createtesttableSQL,0,$severpos).$insertval.substr($createtesttableSQL,$nextcommapos);
498 else $createtesttableSQL = substr($createtesttableSQL,0,$severpos-(strpos($createtesttableSQL,',')?0:1)).$insertval.')';
499 } else return sqlite_ReturnWithError('unknown column "'.$defparts[1].'" in "'.$table.'"');
502 if(sizeof($defparts) < 2) return sqlite_ReturnWithError('near "'.$defparts[0].($defparts[1]?' '.$defparts[1]:'').'": syntax error');
503 if($severpos = strpos($createtesttableSQL,' '.$defparts[1].' ')){
504 $nextcommapos = strpos($createtesttableSQL,',',$severpos);
505 if($nextcommapos) $createtesttableSQL = substr($createtesttableSQL,0,$severpos).substr($createtesttableSQL,$nextcommapos + 1);
506 else $createtesttableSQL = substr($createtesttableSQL,0,$severpos-(strpos($createtesttableSQL,',')?0:1) - 1).')';
507 unset($newcols[$defparts[1]]);
508 } else return sqlite_ReturnWithError('unknown column "'.$defparts[1].'" in "'.$table.'"');
511 return sqlite_ReturnWithError('near "'.$prevword.'": syntax error');
514 $prevword = $defparts[sizeof($defparts)-1];
517 //this block of code generates a test table simply to verify that the columns specifed are valid in an sql statement
518 //this ensures that no reserved words are used as columns, for example
519 if (!sqlite_query($dbhandle,$createtesttableSQL)) return false;
520 $droptempsql = 'DROP TABLE '.$tmpname;
521 sqlite_query($dbhandle,$droptempsql);
524 $createnewtableSQL = 'CREATE '.substr(trim(preg_replace("'".$tmpname."'",$table,$createtesttableSQL,1)),17);
528 while(list($key,$val) = each($newcols)) {
529 if (strtoupper($val)!='PRIMARY' && strtoupper($key)!='PRIMARY' &&
530 strtoupper($val)!='UNIQUE' && strtoupper($key)!='UNIQUE'){
531 $newcolumns .= ($newcolumns?', ':'').$val;
532 $oldcolumns .= ($oldcolumns?', ':'').$key;
535 $copytonewsql = 'INSERT INTO '.$table.'('.$newcolumns.') SELECT '.$oldcolumns.' FROM '.$tmpname;
537 sqlite_query($dbhandle,$createtemptableSQL); //create temp table
538 sqlite_query($dbhandle,$copytotempsql); //copy to table
539 sqlite_query($dbhandle,$dropoldsql); //drop old table
541 sqlite_query($dbhandle,$createnewtableSQL); //recreate original table
542 sqlite_query($dbhandle,$copytonewsql); //copy back to original table
543 sqlite_query($dbhandle,$droptempsql); //drop temp table
546 function sqlite_showKeysFrom($tname,$dbhandle) {
547 // This function is for supporing 'SHOW KEYS FROM' and 'SHOW INDEX FROM'.
548 // For making the same result as obtained by MySQL, temporary table is made.
549 $tname=str_replace("'",'',$tname);
551 // Create a temporary table for making result
552 if (function_exists('microtime')) $tmpname='t'.str_replace('.','',str_replace(' ','',microtime()));
553 else $tmpname = 't'.rand(0,999999).time();
554 sqlite_query($dbhandle,"CREATE TEMPORARY TABLE $tmpname ('Table', 'Non_unique', 'Key_name', 'Seq_in_index',".
555 " 'Column_name', 'Collation', 'Cardinality', 'Sub_part', 'Packed', 'Null', 'Index_type', 'Comment')");
557 // First, get the sql query when the table created
558 $res=sqlite_query($dbhandle,"SELECT sql FROM sqlite_master WHERE tbl_name = '$tname' ORDER BY type DESC");
559 $a=nucleus_mysql_fetch_assoc($res);
562 // Check if each columns are unique
564 foreach(sqlite_splitByComma(substr($tablesql,strpos($tablesql,'(')+1)) as $value) {
565 $name=str_replace("'",'',substr($value,0,strpos($value,' ')));
566 if (strpos(strtoupper($value),'NOT NULL')!==false) $notnull[$name]='';
567 else $notnull[$name]='YES';
570 // Get the primary key (and check if it is unique???).
571 if (preg_match('/[^a-zA-Z_\']([\S]+)[^a-zA-Z_\']+INTEGER NOT NULL PRIMARY KEY/i',$tablesql,$matches)) {
572 $pkey=str_replace("'",'',$matches[1]);
574 } else if (preg_match('/[^a-zA-Z_\']([\S]+)[^a-zA-Z_\']+INTEGER PRIMARY KEY/i',$tablesql,$matches)) {
575 $pkey=str_replace("'",'',$matches[1]);
577 } else if (preg_match('/PRIMARY KEY[\s]*?\(([^\)]+)\)/i',$tablesql,$matches)) {
578 $pkey=null;// PRIMARY KEY ('xxx'[,'xxx'])
579 foreach(explode(',',$matches[1]) as $key=>$value) {
580 $value=str_replace("'",'',trim($value));
582 $cardinality=nucleus_mysql_num_rows(sqlite_query($dbhandle,"SELECT '$value' FROM '$tname'"));
583 sqlite_query($dbhandle,"INSERT INTO $tmpname ('Table', 'Non_unique', 'Key_name', 'Seq_in_index',".
584 " 'Column_name', 'Collation', 'Cardinality', 'Sub_part', 'Packed', 'Null', 'Index_type', 'Comment')".
585 " VALUES ('$tname', '0', 'PRIMARY', '$key',".
586 " '$value', 'A', '$cardinality', null, null, '', 'BTREE', '')");
591 $res=sqlite_query($dbhandle,"SELECT sql,name FROM sqlite_master WHERE type = 'index' and tbl_name = '$tname' ORDER BY type DESC");
592 while ($a=nucleus_mysql_fetch_assoc($res)) {
593 if (!($sql=$a['sql'])) {// Primary key
594 if ($pkey && strpos(strtolower($a['name']),'autoindex')) {
595 $cardinality=nucleus_mysql_num_rows(sqlite_query($dbhandle,"SELECT $pkey FROM '$tname'"));
596 sqlite_query($dbhandle,"INSERT INTO $tmpname ('Table', 'Non_unique', 'Key_name', 'Seq_in_index',".
597 " 'Column_name', 'Collation', 'Cardinality', 'Sub_part', 'Packed', 'Null', 'Index_type', 'Comment')".
598 " VALUES ('$tname', '0', 'PRIMARY', '1',".
599 " '$pkey', 'A', '$cardinality', null, null, '$pkeynull', 'BTREE', '')");
602 } else {// Non-primary key
603 if (($name=str_replace("'",'',$a['name'])) && preg_match('/\(([\s\S]+)\)/',$sql,$matches)) {
604 foreach(explode(',',$matches[1]) as $key=>$value) {
605 $columnname=str_replace("'",'',$value);
606 if (strpos(strtoupper($sql),'CREATE UNIQUE ')===0) $nonunique='0';
608 $cardinality=nucleus_mysql_num_rows(sqlite_query($dbhandle,"SELECT $columnname FROM '$tname'"));
609 sqlite_query($dbhandle,"INSERT INTO $tmpname ('Table', 'Non_unique', 'Key_name', 'Seq_in_index',".
610 " 'Column_name', 'Collation', 'Cardinality', 'Sub_part', 'Packed', 'Null', 'Index_type', 'Comment')".
611 " VALUES ('$tname', '$nonunique', '$name', '".(string)($key+1)."',".
612 " '$columnname', 'A', '$cardinality', null, null, '$notnull[$columnname]', 'BTREE', '')");
617 if ($pkey) { // The case that the key (index) is not defined.
618 $cardinality=nucleus_mysql_num_rows(sqlite_query($dbhandle,"SELECT $pkey FROM '$tname'"));
619 sqlite_query($dbhandle,"INSERT INTO $tmpname ('Table', 'Non_unique', 'Key_name', 'Seq_in_index',".
620 " 'Column_name', 'Collation', 'Cardinality', 'Sub_part', 'Packed', 'Null', 'Index_type', 'Comment')".
621 " VALUES ('$tname', '0', 'PRIMARY', '1',".
622 " '$pkey', 'A', '$cardinality', null, null, '$pkeynull', 'BTREE', '')");
626 // return the final query to show the keys in MySQL style (using temporary table).
627 return "SELECT * FROM $tmpname";
629 function sqlite_showFieldsFrom($tname,$dbhandle){
630 // This function is for supporing 'SHOW FIELDS FROM' and 'SHOW COLUMNS FROM'.
631 // For making the same result as obtained by MySQL, temporary table is made.
632 $tname=str_replace("'",'',$tname);
634 // First, get the sql query when the table created
635 $res=sqlite_query($dbhandle,"SELECT sql FROM sqlite_master WHERE tbl_name = '$tname' ORDER BY type DESC");
636 $a=nucleus_mysql_fetch_assoc($res);
637 $tablesql=trim($a['sql']);
638 if (preg_match('/^[^\(]+\(([\s\S]*?)\)$/',$tablesql,$matches)) $tablesql=$matches[1];
640 foreach(sqlite_splitByComma($tablesql) as $value) {
642 if ($i=strpos($value,' ')) {
643 $name=str_replace("'",'',substr($value,0,$i));
644 $value=trim(substr($value,$i));
645 if (substr($value,-1)==',') $value=substr($value,strlen($value)-1);
646 $tablearray[$name]=$value;
650 // Check if INDEX has been made for the parameter 'MUL' in 'KEY' column
652 $res=sqlite_query($dbhandle,"SELECT name FROM sqlite_master WHERE type = 'index' and tbl_name = '$tname' ORDER BY type DESC");
653 while ($a=nucleus_mysql_fetch_assoc($res)) $multi[str_replace("'",'',$a['name'])]='MUL';
655 // Create a temporary table for making result
656 if (function_exists('microtime')) $tmpname='t'.str_replace('.','',str_replace(' ','',microtime()));
657 else $tmpname = 't'.rand(0,999999).time();
658 sqlite_query($dbhandle,"CREATE TEMPORARY TABLE $tmpname ('Field', 'Type', 'Null', 'Key', 'Default', 'Extra')");
661 foreach($tablearray as $field=>$value) {
662 if (strtoupper($field)=='PRIMARY') continue;//PRIMARY KEY('xx'[,'xx'])
663 $uvalue=strtoupper($value.' ');
664 $key=(string)$multi[$field];
665 if ($uvalue=='INTEGER NOT NULL PRIMARY KEY ' || $uvalue=='INTEGER PRIMARY KEY ') {
667 $extra='auto_increment';
669 if ($i=strpos($uvalue,' ')) {
670 $type=substr($value,0,$i);
671 if (strpos($type,'(') && ($i=strpos($value,')')))
672 $type=substr($value,0,$i+1);
674 if (strtoupper($type)=='INTEGER') $type='int(11)';
675 if (strpos($uvalue,'NOT NULL')===false) $null='YES';
678 $value=preg_replace('/NOT NULL/i','',$value);
679 $uvalue=strtoupper($value);
681 if ($i=strpos($uvalue,'DEFAULT')) {
682 $default=trim(substr($value,$i+7));
683 if (strtoupper($default)=='NULL') {
687 if (substr($default,0,1)=="'") $default=substr($default,1,strlen($default)-2);
688 $default="'".$default."',";
689 $setdefault="'Default',";
691 } else if ($null!='YES' && $extra!='auto_increment') {
692 if (strpos(strtolower($type),'int')===false) $default="'',";
693 else $default="'0',";
694 $setdefault="'Default',";
699 sqlite_query($dbhandle,"INSERT INTO '$tmpname' ('Field', 'Type', 'Null', 'Key', $setdefault 'Extra')".
700 " VALUES ('$field', '$type', '$null', '$key', $default '$extra')");
703 // return the final query to show the keys in MySQL style (using temporary table).
704 return "SELECT * FROM $tmpname";
706 function sqlite_mysql_query_debug(&$query){
707 // The debug mode is so far used for checking query difference like "SELECT i.itime, ....".
708 // This must be chaged to "SELECT i.itime as itime,..." for SQLite.
709 // (This feature is not needed any more after the version 0.8.1 (see intialization query))
710 $uquery=strtoupper($query);
711 if (strpos($uquery,"SELECT ")!==0) return $query;
712 if (($i=strpos($uquery," FROM "))===false) return $query;
713 $select=sqlite_splitByComma(substr($query,7,$i-7));
714 $query=substr($query,$i);
716 foreach($select as $value){
717 if (preg_match('/^([a-z_]+)\.([a-z_]+)$/i',$value,$matches)) {
718 $value=$value." as ".$matches[2];
719 $t=$matches[0]."=>$value\n";
720 $a=debug_backtrace();
721 foreach($a as $key=>$btrace) {
722 if (!($templine=$btrace['line'])) continue;
723 if (!($tempfile=$btrace['file'])) continue;
724 $tempfile=preg_replace('/[\s\S]*?[\/\\\\]([^\/\\\\]+)$/','$1',$tempfile);
725 $t.="$tempfile line:$templine\n";
727 sqlite_DebugMessage($t);
729 if ($ret) $ret.=', ';
732 return "SELECT $ret $query";
735 function nucleus_mysql_list_tables($p1=null,$p2=null) {
736 global $SQLITE_DBHANDLE,$MYSQL_DATABASE;
737 return sqlite_query($SQLITE_DBHANDLE,"SELECT name as Tables_in_$MYSQL_DATABASE FROM sqlite_master WHERE type='table'");
739 function nucleus_mysql_listtables($p1=null,$p2=null) { return nucleus_mysql_list_tables($p1,$p2);}
741 function nucleus_mysql_affected_rows($p1=null){
742 global $SQLITE_DBHANDLE;
743 if (!($dbhandle=$p1)) $dbhandle=$SQLITE_DBHANDLE;
744 return sqlite_changes($dbhandle);
747 function nucleus_mysql_error($p1=null){
748 global $SQLITE_DBHANDLE;
749 if (!($dbhandle=$p1)) $dbhandle=$SQLITE_DBHANDLE;
750 return sqlite_error_string ( sqlite_last_error ($dbhandle) );
753 function nucleus_mysql_fetch_array($p1,$p2=SQLITE_BOTH){
754 return sqlite_fetch_array ($p1,$p2);
757 function nucleus_mysql_fetch_assoc($p1){
758 return sqlite_fetch_array($p1,SQLITE_ASSOC);
761 function nucleus_mysql_fetch_object($p1,$p2=SQLITE_BOTH){
762 if (is_array($ret=sqlite_fetch_array ($p1,$p2))) {
763 $o=new SQLITE_OBJECT;
764 foreach ($ret as $key=>$value) {
765 if (strstr($key,'.')) {// Remove table name.
766 $key=preg_replace('/^(.+)\."(.+)"$/','"$2"',$key);
767 $key=preg_replace('/^(.+)\.([^.^"]+)$/','$2',$key);
775 function nucleus_mysql_fetch_row($p1){
776 return sqlite_fetch_array($p1,SQLITE_NUM);
779 function nucleus_mysql_field_name($p1,$p2){
780 return sqlite_field_name ($p1,$p2);
783 function nucleus_mysql_free_result($p1){
784 // ???? Cannot find corresponding function of SQLite.
785 // Maybe SQLite is NOT used for the high spec server
786 // that need mysql_free_result() function because of
787 // many SQL-queries in a script.
791 function nucleus_mysql_insert_id($p1=null){
792 global $SQLITE_DBHANDLE;
793 if (!($dbhandle=$p1)) $dbhandle=$SQLITE_DBHANDLE;
794 return sqlite_last_insert_rowid ($dbhandle);
797 function nucleus_mysql_num_fields($p1){
798 return sqlite_num_fields ($p1);
801 function nucleus_mysql_num_rows($p1){
802 return sqlite_num_rows ($p1);
804 function nucleus_mysql_numrows($p1){
805 return sqlite_num_rows ($p1);
808 function nucleus_mysql_result($p1,$p2,$p3=null){
809 if ($p3) return sqlite_ReturnWithError('nucleus_mysql_result');
810 if (!$p2) return sqlite_fetch_single ($p1);
811 $a=sqlite_fetch_array ($p1);
815 function nucleus_mysql_unbuffered_query($p1,$p2=null){
816 return nucleus_mysql_query($p1,$p2,true);
819 function nucleus_mysql_client_encoding($p1=null){
820 return sqlite_libencoding();
823 function nucleus_mysql_data_seek($p1,$p2) {
824 return sqlite_seek($p1,$p2);
827 function nucleus_mysql_errno ($p1=null){
828 global $SQLITE_DBHANDLE;
829 if (!($dbhandle=$p1)) $dbhandle=$SQLITE_DBHANDLE;
830 return sqlite_last_error($dbhandle);
833 function nucleus_mysql_escape_string ($p1){
834 // The "'" will be changed to "''".
835 // This way works for both MySQL and SQLite when single quotes are used for string.
836 // Note that single quote is always used in this wrapper.
837 // If a plugin is made on SQLite-Nucleus and such plugin will be used for MySQL-Nucleus,
838 // nucleus_mysql_escape_string() will be changed to mysql_escape_string() and
839 // this routine won't be used, so this way won't be problem.
840 return sqlite_escape_string($p1);
843 function nucleus_mysql_real_escape_string ($p1,$p2=null){
844 //addslashes used here.
845 return addslashes($p1);
848 function nucleus_mysql_create_db ($p1,$p2=null){
849 // All prameters are ignored.
850 // Returns always true;
854 function nucleus_mysql_pconnect($p1=null,$p2=null,$p3=null,$p4=null,$p5=null){
855 global $SQLITE_DBHANDLE,$SQLITECONF;
856 sqlite_close ($SQLITE_DBHANDLE);
857 $SQLITE_DBHANDLE=sqlite_popen($SQLITECONF['DBFILENAME']);
858 return ($SQLITE['DBHANDLE']=$SQLITE_DBHANDLE);
861 function nucleus_mysql_fetch_field($p1,$p2=null){
862 if ($p2) return sqlite_ReturnWithError('nucleus_mysql_fetch_field');
863 // Only 'name' is supported.
864 $o=new SQLITE_OBJECT;
866 if(is_array($ret=sqlite_fetch_array ($p1,SQLITE_ASSOC )))
867 foreach ($ret as $key=>$value) {
868 if (is_string($key)) array_push($o->name,$key);
874 // This function is called instead of _execute_queries() in backp.php
875 function sqlite_restore_execute_queries(&$query){
876 global $DIR_NUCLEUS,$DIR_LIBS,$DIR_PLUGINS,$CONF;
878 // Skip until the first "#" or "--"
879 if (($i=strpos($query,"\n#"))===false) $i=strlen($query);
880 if (($j=strpos($query,"\n--"))===false) $j=strlen($query);
881 if ($i<$j) $query=substr($query,$i+1);
882 else $query=substr($query,$j+1);
884 // Save the query to temporary file in sqlite directory.
885 if (function_exists('microtime')) {
886 $prefix=preg_replace('/[^0-9]/','',microtime());
889 $prefix=(string)rand(0,999999);
891 $tmpname=tempnam($DIR_NUCLEUS.'sqlite/',"tmp$prefix");
892 if (!($handle=@fopen($tmpname,'w'))) return 'Cannot save temporary DB file.';
893 fwrite($handle,$query);
895 $tmpname=preg_replace('/[\s\S]*?[\/\\\\]([^\/\\\\]+)$/','$1',$tmpname);
897 // Read the option from NP_SQLite
898 if (!class_exists('NucleusPlugin')) { include($DIR_LIBS.'PLUGIN.php');}
899 if (!class_exists('NP_SQLite')) { include($DIR_PLUGINS.'NP_SQLite.php'); }
901 if (!($numatonce=@$p->getOption('numatonce'))) $numatonce=20;
902 if (!($refreshwait=@$p->getOption('refreshwait'))) $refreshwait=1;
905 $url="plugins/sqlite/restore.php?dbfile=$tmpname&numatonce=$numatonce&refreshwait=$refreshwait";
906 header('HTTP/1.0 301 Moved Permanently');
907 header('Location: '.$url);
908 exit('<html><body>Moved Permanently</body></html>');