--- /dev/null
+<?php
+ /****************************************
+ * SQLite-MySQL wrapper for Nucleus *
+ * ver 0.9.0.2 *
+ * Written by Katsumi License: GPL *
+ ****************************************/
+
+function sqlite_createtable_query($commands,$tablename,$temptable,&$morequeries){
+ if ($temptable) $query="CREATE TEMPORARY TABLE $tablename (";
+ else $query="CREATE TABLE $tablename (";
+ $auto_increment=$first=true;
+ foreach($commands as $key => $value) {
+ if (strpos(strtolower($value),'auto_increment')==strlen($value)-14) $auto_increment=true;
+ $isint=preg_match('/int\(([0-9]*?)\)/i',$value);
+ $isint=$isint | preg_match('/tinyint\(([0-9]*?)\)/i',$value);
+ $value=preg_replace('/int\(([0-9]*?)\)[\s]+unsigned/i','int($1)',$value);
+ $value=preg_replace('/int\([0-9]*?\)[\s]+NOT NULL[\s]+auto_increment$/i',' INTEGER NOT NULL PRIMARY KEY',$value);
+ $value=preg_replace('/int\([0-9]*?\)[\s]+auto_increment$/i',' INTEGER PRIMARY KEY',$value);
+ if ($auto_increment) $value=preg_replace('/^PRIMARY KEY(.*?)$/i','',$value);
+ while (preg_match('/PRIMARY KEY[\s]*\((.*)\([0-9]+\)(.*)\)/i',$value)) // Remove '(100)' from 'PRIMARY KEY (`xxx` (100))'
+ $value=preg_replace('/PRIMARY KEY[\s]*\((.*)\([0-9]+\)(.*)\)/i','PRIMARY KEY ($1 $2)',$value);
+
+ // CREATE KEY queries for SQLite (corresponds to KEY 'xxxx'('xxxx', ...) of MySQL
+ if (preg_match('/^FULLTEXT KEY(.*?)$/i',$value,$matches)) {
+ array_push($morequeries,'CREATE INDEX '.str_replace('('," ON $tablename (",$matches[1]));
+ $value='';
+ } else if (preg_match('/^UNIQUE KEY(.*?)$/i',$value,$matches)) {
+ array_push($morequeries,'CREATE UNIQUE INDEX '.str_replace('('," ON $tablename (",$matches[1]));
+ $value='';
+ } else if (preg_match('/^KEY(.*?)$/i',$value,$matches)) {
+ array_push($morequeries,'CREATE INDEX '.str_replace('('," ON $tablename (",$matches[1]));
+ $value='';
+ }
+
+ // Check if 'DEFAULT' is set when 'NOT NULL'
+ $uvalue=strtoupper($value);
+ if (strpos($uvalue,'NOT NULL')!==false &&
+ strpos($uvalue,'DEFAULT')===false &&
+ strpos($uvalue,'INTEGER NOT NULL PRIMARY KEY')===false) {
+ if ($isint) $value.=" DEFAULT 0";
+ else $value.=" DEFAULT ''";
+ }
+
+ if ($value) {
+ if ($first) $first=false;
+ else $query.=',';
+ $query.=' '.$value;
+ }
+ }
+ $query.=' )';
+ return $query;
+}
+
+function sqlite_renametable($commands,$dbhandle){
+ $carray=array();
+ foreach($commands as $command){
+ $command=_sqlite_divideByChar(array(' ',"\t","\r","\n"),$command);
+ if (count($command)!=3) return sqlite_ReturnWithError(htmlspecialchars("near '$command[0]': syntax error"));
+ if (strtoupper($command[1])!='TO') return sqlite_ReturnWithError(htmlspecialchars("near '$command[1]': syntax error"));
+ $carray[str_replace("'",'',$command[0])]=str_replace("'",'',$command[2]);
+ }
+ foreach($carray as $old=>$new){
+ if (!sqlite_copytable($old,$new,$dbhandle)) return false;
+ if (!sqlite_query($dbhandle,"DROP TABLE $old")) return sqlite_ReturnWithError(htmlspecialchars("fail to remove table, '$old'"));
+ }
+ sqlite_query($dbhandle,'VACUUM');
+ return true;
+}
+
+function sqlite_copytable($table,$newname,$dbhandle,$newtablearray=array()){
+ // Getting information from original table and create new table
+ $res = sqlite_query($dbhandle,"SELECT sql,name,type FROM sqlite_master WHERE tbl_name = '".$table."' ORDER BY type DESC");
+ if(!sqlite_num_rows($res)) return sqlite_ReturnWithError('no such table: '.$table);
+ if (count($newtablearray)) {
+ $query="CREATE TABLE $newname (".implode(',',$newtablearray).')';
+ if (!sqlite_query($dbhandle,$query)) return sqlite_ReturnWithError('Table could not be created.');
+ } else {
+ while($row=sqlite_fetch_array($res,SQLITE_ASSOC)){
+ if (!preg_match('/^([^\(]*)[\s]([^\(\']+)[\s]*\(([\s\S]*)$/',$row['sql'],$m) &&
+ !preg_match('/^([^\(]*)[\s]\'([^\(]+)\'[\s]*\(([\s\S]*)$/',$row['sql'],$m)) return sqlite_ReturnWithError('unknown error');
+ if (!sqlite_query($dbhandle,$m[1]." '$newname' (".$m[3])) return sqlite_ReturnWithError('Table could not be created.');
+ }
+ }
+ // Copy the items
+ sqlite_query($dbhandle,'BEGIN');
+ $res=sqlite_unbuffered_query($dbhandle,"SELECT * FROM $table");
+ while($row=sqlite_fetch_array($res,SQLITE_ASSOC)){
+ $keys=$values=array();
+ foreach($row as $key=>$value) {
+ if (count($newtablearray) && !isset($newtablearray[strtolower($key)])) continue;
+ $keys[]="'$key'";
+ $values[]="'".sqlite_escape_string($value)."'";
+ }
+ if (!sqlite_query($dbhandle,"INSERT INTO '$newname'(".implode(', ',$keys).') VALUES ('.implode(', ',$values).')')) {
+ sqlite_query($dbhandle,'COMMIT');
+ return false;
+ }
+ }
+ sqlite_query($dbhandle,'COMMIT');
+ $orgnum=sqlite_array_query($dbhandle,"SELECT COUNT(*) FROM $table");
+ $newnum=sqlite_array_query($dbhandle,"SELECT COUNT(*) FROM $newname");
+ if ($orgnum[0][0]!=$newnum[0][0]) return sqlite_ReturnWithError('Data transfer failed.');
+ return true;
+}
+function sqlite_altertable($table,$alterdefs,$dbhandle){
+ // Almost completely re-written in February 2008.
+ $table=str_replace("'",'',$table);
+
+ // Getting information from original table
+ $res = sqlite_query($dbhandle,"SELECT sql,name,type FROM sqlite_master WHERE tbl_name = '".$table."' ORDER BY type DESC");
+ if(!sqlite_num_rows($res)) return sqlite_ReturnWithError('no such table: '.$table);
+ $orgindex=array();
+ $row=sqlite_fetch_array($res,SQLITE_ASSOC); //table sql
+ $orgsql=$row['sql'];
+ while($row=sqlite_fetch_array($res,SQLITE_ASSOC)) $orgindex[strtolower($row['name'])]=$row['sql'];
+ if (!preg_match('/^[^\(]+\((.*)\);*$/',$orgsql,$m)) return sqlite_ReturnWithError('unknown error');
+ $orgtablearray=array();
+ foreach(_sqlite_divideByChar(',',$orgtable=$m[1]) as $value){
+ if (!preg_match('/^([^\s\']+)[\s]+([\s\S]*)$/',$value,$m) &&
+ !preg_match('/^\'([^\']+)\'[\s]+([\s\S]*)$/',$value,$m)) return sqlite_ReturnWithError('unknown error');
+ $orgtablearray[strtolower($m[1])]="'$m[1]' $m[2]";
+ }
+ $desttablearray=$orgtablearray;
+ $destindex=$orgindex;
+
+ // Convert table
+ foreach(_sqlite_divideByChar(',',$alterdefs) as $def){
+ $def=_sqlite_divideByChar(array(' ',"\t","\r","\n"),trim($def));
+ if (($c=count($def))<2) return sqlite_ReturnWithError('near "'.htmlspecialchars($def[0]).'": syntax error');
+ // Check if FIRST/AFTER is used.
+ $first=$after=false;
+ if (strtoupper($def[$c-1])=='FIRST') {
+ $first=true;
+ array_pop($def);
+ } elseif (strtoupper($def[$c-2])=='AFTER') {
+ $after=strtolower(str_replace("'",'',array_pop($def)));
+ array_pop($def);
+ }
+ // Ignore CONSTRAINT and COLUMN
+ $method=strtoupper(array_shift($def));
+ switch(strtoupper($def[0])){
+ case 'CONSTRAINT': // delete two
+ array_shift($def);
+ case 'COLUMN': // delete one
+ array_shift($def);
+ default:
+ break;
+ }
+ // The main routine of this function follow.
+ switch($method){
+ case 'MODIFY':
+ case 'ALTER':
+ if (error_reporting() & E_NOTICE) sqlite_ReturnWithError('ALTER/MODIFY is not supported');
+ break;
+ case 'DROP':
+ case 'CHANGE':
+ if (strtoupper($def[0])=='INDEX') {
+ // delete index
+ unset($destindex[strtolower($def[1])]);
+ } else {
+ // delete field
+ unset($desttablearray[strtolower(str_replace("'",'',$def[0]))]);
+ }
+ if ($method!='CHANGE') break;
+ case 'ADD':
+ $field=array_shift($def);
+ switch($submedthod=strtoupper($field)){
+ case 'UNIQUE':
+ case 'PRIMARY':
+ case 'FOREIGN':
+ case 'INDEX':
+ case 'FULLTEXT':
+ // add index
+ if (strtoupper($index=array_shift($def))=='KEY') $index=array_shift($def);
+ $def=implode(' ',$def);
+ $destindex[strtolower(str_replace("'",'',$index))]=
+ ($submedthod=='UNIQUE'?'CREATE UNIQUE INDEX ':'CREATE INDEX ').
+ "$index ON '$table' $def";
+ break;
+ default:
+ // add field
+ $field=str_replace("'",'',$field);
+ if ($first) {
+ $desttablearray=array_merge(
+ array(strtolower($field)=>"'$field' ".implode(' ',$def)),
+ $desttablearray);
+ } elseif($after) {
+ $temp=$desttablearray;
+ $desttablearray=array();
+ $ok=false;
+ foreach($temp as $key=>$value) {
+ $desttablearray[$key]=$value;
+ if ($ok || $key!=$after) continue;
+ $ok=true;
+ $desttablearray[strtolower($field)]="'$field' ".implode(' ',$def);
+ }
+ if (!$ok) {
+ $desttablearray[strtolower($field)]="'$field' ".implode(' ',$def);
+ if (error_reporting() & E_NOTICE) sqlite_ReturnWithError(htmlspecialchars("Field '$after' not found."));
+ }
+ } else {
+ $desttablearray[strtolower($field)]="'$field' ".implode(' ',$def);
+ }
+ }
+ break;
+ default:
+ return sqlite_ReturnWithError('near "'.htmlspecialchars($method).'": syntax error');
+ }
+ }
+
+ // Create temporary table that has the modified field and copy the items into it.
+ if (function_exists('microtime')) $tmptable='t'.str_replace(array(' ','.'),'',microtime());
+ else $tmptable = 't'.rand(0,999999).time();
+ if (!sqlite_copytable($table,$tmptable,$dbhandle,$desttablearray)) return false;
+
+ // New temporary table sccusfully made.
+ // So, delete the original table and copy back the temporary table to one with original name.
+ sqlite_query("DROP TABLE $table",$dbhandle);
+ if (sqlite_copytable($tmptable,$table,$dbhandle)) sqlite_query("DROP TABLE $tmptable",$dbhandle);
+
+ // Add the indexes, finally.
+ foreach($destindex as $index) sqlite_query($index,$dbhandle);
+ sqlite_query($dbhandle,'VACUUM');
+ return true;
+}
+function sqlite_showKeysFrom($tname,$dbhandle) {
+ // This function is for supporing 'SHOW KEYS FROM' and 'SHOW INDEX FROM'.
+ // For making the same result as obtained by MySQL, temporary table is made.
+ if (preg_match('/^([^\s]+)\s+LIKE\s+\'([^\']+)\'$/i',$tname,$m)) list($m,$tname,$like)=$m;
+ $tname=str_replace("'",'',$tname);
+
+ // Create a temporary table for making result
+ if (function_exists('microtime')) $tmpname='t'.str_replace('.','',str_replace(' ','',microtime()));
+ else $tmpname = 't'.rand(0,999999).time();
+ sqlite_query($dbhandle,"CREATE TEMPORARY TABLE $tmpname ('Table', 'Non_unique', 'Key_name', 'Seq_in_index',".
+ " 'Column_name', 'Collation', 'Cardinality', 'Sub_part', 'Packed', 'Null', 'Index_type', 'Comment')");
+
+ // First, get the sql query when the table created
+ $res=sqlite_query($dbhandle,"SELECT sql FROM sqlite_master WHERE tbl_name = '$tname' ORDER BY type DESC");
+ $a=nucleus_mysql_fetch_assoc($res);
+ $tablesql=$a['sql'];
+
+ // Check if each columns are unique
+ $notnull=array();
+ foreach(_sqlite_divideByChar(',',substr($tablesql,strpos($tablesql,'(')+1)) as $value) {
+ $name=str_replace("'",'',substr($value,0,strpos($value,' ')));
+ if (strpos(strtoupper($value),'NOT NULL')!==false) $notnull[$name]='';
+ else $notnull[$name]='YES';
+ }
+
+ // Get the primary key (and check if it is unique???).
+ if (preg_match('/[^a-zA-Z_\']([\S]+)[^a-zA-Z_\']+INTEGER NOT NULL PRIMARY KEY/i',$tablesql,$matches)) {
+ $pkey=str_replace("'",'',$matches[1]);
+ $pkeynull='';
+ } else if (preg_match('/[^a-zA-Z_\']([\S]+)[^a-zA-Z_\']+INTEGER PRIMARY KEY/i',$tablesql,$matches)) {
+ $pkey=str_replace("'",'',$matches[1]);
+ $pkeynull='YES';
+ } else if (preg_match('/PRIMARY KEY[\s]*?\(([^\)]+)\)/i',$tablesql,$matches)) {
+ $pkey=null;// PRIMARY KEY ('xxx'[,'xxx'])
+ foreach(explode(',',$matches[1]) as $key=>$value) {
+ $value=str_replace("'",'',trim($value));
+ $key++;
+ $cardinality=nucleus_mysql_num_rows(sqlite_query($dbhandle,"SELECT '$value' FROM '$tname'"));
+ sqlite_query($dbhandle,"INSERT INTO $tmpname ('Table', 'Non_unique', 'Key_name', 'Seq_in_index',".
+ " 'Column_name', 'Collation', 'Cardinality', 'Sub_part', 'Packed', 'Null', 'Index_type', 'Comment')".
+ " VALUES ('$tname', '0', 'PRIMARY', '$key',".
+ " '$value', 'A', '$cardinality', null, null, '', 'BTREE', '')");
+ }
+ } else $pkey=null;
+
+ // Check the index.
+ $res=sqlite_query($dbhandle,"SELECT sql,name FROM sqlite_master WHERE type = 'index' and tbl_name = '$tname' ORDER BY type DESC");
+ while ($a=nucleus_mysql_fetch_assoc($res)) {
+ if (!($sql=$a['sql'])) {// Primary key
+ if ($pkey && strpos(strtolower($a['name']),'autoindex')) {
+ $cardinality=nucleus_mysql_num_rows(sqlite_query($dbhandle,"SELECT $pkey FROM '$tname'"));
+ sqlite_query($dbhandle,"INSERT INTO $tmpname ('Table', 'Non_unique', 'Key_name', 'Seq_in_index',".
+ " 'Column_name', 'Collation', 'Cardinality', 'Sub_part', 'Packed', 'Null', 'Index_type', 'Comment')".
+ " VALUES ('$tname', '0', 'PRIMARY', '1',".
+ " '$pkey', 'A', '$cardinality', null, null, '$pkeynull', 'BTREE', '')");
+ $pkey=null;
+ }
+ } else {// Non-primary key
+ if (($name=str_replace("'",'',$a['name'])) && preg_match('/\(([\s\S]+)\)/',$sql,$matches)) {
+ if (isset($like) && $name!=$like) continue;
+ foreach(explode(',',$matches[1]) as $key=>$value) {
+ $columnname=str_replace("'",'',$value);
+ if (strpos(strtoupper($sql),'CREATE UNIQUE ')===0) $nonunique='0';
+ else $nonunique='1';
+ $cardinality=nucleus_mysql_num_rows(sqlite_query($dbhandle,"SELECT $columnname FROM '$tname'"));
+ sqlite_query($dbhandle,"INSERT INTO $tmpname ('Table', 'Non_unique', 'Key_name', 'Seq_in_index',".
+ " 'Column_name', 'Collation', 'Cardinality', 'Sub_part', 'Packed', 'Null', 'Index_type', 'Comment')".
+ " VALUES ('$tname', '$nonunique', '$name', '".(string)($key+1)."',".
+ " '$columnname', 'A', '$cardinality', null, null, '$notnull[$columnname]', 'BTREE', '')");
+ }
+ }
+ }
+ }
+ if ($pkey) { // The case that the key (index) is not defined.
+ $cardinality=nucleus_mysql_num_rows(sqlite_query($dbhandle,"SELECT $pkey FROM '$tname'"));
+ sqlite_query($dbhandle,"INSERT INTO $tmpname ('Table', 'Non_unique', 'Key_name', 'Seq_in_index',".
+ " 'Column_name', 'Collation', 'Cardinality', 'Sub_part', 'Packed', 'Null', 'Index_type', 'Comment')".
+ " VALUES ('$tname', '0', 'PRIMARY', '1',".
+ " '$pkey', 'A', '$cardinality', null, null, '$pkeynull', 'BTREE', '')");
+ $pkey=null;
+ }
+
+ // return the final query to show the keys in MySQL style (using temporary table).
+ return "SELECT * FROM $tmpname";
+}
+function sqlite_showFieldsFrom($tname,$dbhandle){
+ // This function is for supporing 'SHOW FIELDS FROM' and 'SHOW COLUMNS FROM'.
+ // For making the same result as obtained by MySQL, temporary table is made.
+ if (preg_match('/^([^\s]+)\s+LIKE\s+\'([^\']+)\'$/i',$tname,$m)) list($m,$tname,$like)=$m;
+ $tname=str_replace("'",'',$tname);
+
+ // First, get the sql query when the table created
+ $res=sqlite_query($dbhandle,"SELECT sql FROM sqlite_master WHERE tbl_name = '$tname' ORDER BY type DESC");
+ $a=nucleus_mysql_fetch_assoc($res);
+ $tablesql=trim($a['sql']);
+ if (preg_match('/^[^\(]+\(([\s\S]*?)\)$/',$tablesql,$matches)) $tablesql=$matches[1];
+ $tablearray=array();
+ foreach(_sqlite_divideByChar(',',$tablesql) as $value) {
+ $value=trim($value);
+ if ($i=strpos($value,' ')) {
+ $name=str_replace("'",'',substr($value,0,$i));
+ $value=trim(substr($value,$i));
+ if (substr($value,-1)==',') $value=substr($value,strlen($value)-1);
+ $tablearray[$name]=$value;
+ }
+ }
+
+ // Check if INDEX has been made for the parameter 'MUL' in 'KEY' column
+ $multi=array();
+ $res=sqlite_query($dbhandle,"SELECT name FROM sqlite_master WHERE type = 'index' and tbl_name = '$tname' ORDER BY type DESC");
+ while ($a=nucleus_mysql_fetch_assoc($res)) $multi[str_replace("'",'',$a['name'])]='MUL';
+
+ // Create a temporary table for making result
+ if (function_exists('microtime')) $tmpname='t'.str_replace('.','',str_replace(' ','',microtime()));
+ else $tmpname = 't'.rand(0,999999).time();
+ sqlite_query($dbhandle,"CREATE TEMPORARY TABLE $tmpname ('Field', 'Type', 'Null', 'Key', 'Default', 'Extra')");
+
+ // Check the table
+ foreach($tablearray as $field=>$value) {
+ if (strtoupper($field)=='PRIMARY') continue;//PRIMARY KEY('xx'[,'xx'])
+ if (isset($like) && $field!=$like) continue;
+ $uvalue=strtoupper($value.' ');
+ $key=(string)$multi[$field];
+ if ($uvalue=='INTEGER NOT NULL PRIMARY KEY ' || $uvalue=='INTEGER PRIMARY KEY ') {
+ $key='PRI';
+ $extra='auto_increment';
+ } else $extra='';
+ if ($i=strpos($uvalue,' ')) {
+ $type=substr($value,0,$i);
+ if (strpos($type,'(') && ($i=strpos($value,')')))
+ $type=substr($value,0,$i+1);
+ } else $type='';
+ if (strtoupper($type)=='INTEGER') $type='int(11)';
+ if (strpos($uvalue,'NOT NULL')===false) $null='YES';
+ else {
+ $null='';
+ $value=preg_replace('/NOT NULL/i','',$value);
+ $uvalue=strtoupper($value);
+ }
+ if ($i=strpos($uvalue,'DEFAULT')) {
+ $default=trim(substr($value,$i+7));
+ if (strtoupper($default)=='NULL') {
+ $default="";
+ $setdefault="";
+ } else {
+ if (substr($default,0,1)=="'") $default=substr($default,1,strlen($default)-2);
+ $default="'".$default."',";
+ $setdefault="'Default',";
+ }
+ } else if ($null!='YES' && $extra!='auto_increment') {
+ if (strpos(strtolower($type),'int')===false) $default="'',";
+ else $default="'0',";
+ $setdefault="'Default',";
+ } else {
+ $default="";
+ $setdefault="";
+ }
+ sqlite_query($dbhandle,"INSERT INTO '$tmpname' ('Field', 'Type', 'Null', 'Key', $setdefault 'Extra')".
+ " VALUES ('$field', '$type', '$null', '$key', $default '$extra')");
+ }
+
+ // return the final query to show the keys in MySQL style (using temporary table).
+ return "SELECT * FROM $tmpname";
+}
+
+?>
\ No newline at end of file
OSDN Git Service
