Jeans & Development コンピューターのことなどを綴ったメモ (旧：目から鱗 w/SQLite)

SHOW FIELDS FROM
nucleus_itemの場合

CREATE TABLE `nucleus_item` (
  `inumber` int(11) NOT NULL auto_increment,
  `ititle` varchar(160) default NULL,
  `ibody` text NOT NULL,
  `imore` text,
  `iblog` int(11) NOT NULL default '0',
  `iauthor` int(11) NOT NULL default '0',
  `itime` datetime NOT NULL default '0000-00-00 00:00:00',
  `iclosed` tinyint(2) NOT NULL default '0',
  `idraft` tinyint(2) NOT NULL default '0',
  `ikarmapos` int(11) NOT NULL default '0',
  `icat` int(11) default NULL,
  `ikarmaneg` int(11) NOT NULL default '0',
  PRIMARY KEY  (`inumber`),
  UNIQUE KEY `inumber` (`inumber`),
  KEY `itime` (`itime`),
  FULLTEXT KEY `ibody` (`ibody`,`ititle`,`imore`)
) TYPE=MyISAM PACK_KEYS=0;

SQLiteでは、

CREATE TABLE 'nucleus_item' (
 'inumber' INTEGER NOT NULL PRIMARY KEY,
 'ititle' varchar(160) default NULL,
 'ibody' text NOT NULL,
 'imore' text,
 'iblog' int(11) NOT NULL default '0',
 'iauthor' int(11) NOT NULL default '0',
 'itime' datetime NOT NULL default '0000-00-00 00:00:00',
 'iclosed' tinyint(2) NOT NULL default '0',
 'idraft' tinyint(2) NOT NULL default '0',
 'ikarmapos' int(11) NOT NULL default '0',
 'icat' int(11) default NULL,
 'ikarmaneg' int(11) NOT NULL default '0' 
)

CREATE INDEX 'ibody' ON 'nucleus_item' ('ibody','ititle','imore')

CREATE INDEX 'itime' ON 'nucleus_item' ('itime')

CREATE UNIQUE INDEX 'inumber' ON 'nucleus_item' ('inumber')

Field	Type	Null	Key	Default	Extra
(string)	(string)	(string)	(string)	(NULL)/(string)	(string)
inumber	int(11)		PRI	(NULL)	auto_increment
ititle	varchar(160)	YES		(NULL)
ibody	text		MUL	(string)
imore	text	YES		(NULL)
iblog	int(11)			(string) 0
iauthor	int(11)			(string) 0
itime	datetime		MUL	(string) 0000-00-00 00:00:00
iclosed	tinyint(2)			(string) 0
idraft	tinyint(2)			(string) 0
ikarmapos	int(11)			(string) 0
icat	int(11)	YES		(NULL)
ikarmaneg	int(11)			(string) 0

$tname=str_replace("'",'','nucleus_item');


// First, get the sql query when the table created
$res=sqlite_query($SQLITE_DBHANDLE,"SELECT sql FROM sqlite_master WHERE tbl_name = '$tname' ORDER BY type DESC");
$a=sqlite_mysql_fetch_assoc($res);
$tablesql=trim($a['sql']);
if (preg_match('/^[^\(]+\(([\s\S]*?)\)$/',$tablesql,$matches)) $tablesql=$matches[1];
$tablearray=array();
foreach(sqlite_splitByComma($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;
    }
}
//foreach($tablearray as $key=>$value) echo "$key=>$value</br>\n";

// Check if INDEX has been made for the parameter 'MUL' in 'KEY' column
$multi=array();
$res=sqlite_query($SQLITE_DBHANDLE,"SELECT name FROM sqlite_master WHERE type = 'index' and tbl_name = '$tname' ORDER BY type DESC");
while ($a=sqlite_mysql_fetch_assoc($res)) $multi[str_replace("'",'',$a['name'])]='MUL';
//foreach($multi as $key=>$value) echo "$key=>$value</br>\n";

// 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($SQLITE_DBHANDLE,"CREATE TEMPORARY TABLE $tmpname ('Field', 'Type', 'Null', 'Key', 'Default', 'Extra')"); 

// Check the table
foreach($tablearray as $field=>$value) {
    $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);
    else $type='';
    if (strtoupper($type)=='INTEGER') $type='int(11)';
    if (strpos($uvalue,'NOT NULL')===false) $null='YES';
    else $null='';
    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($SQLITE_DBHANDLE,"INSERT INTO '$tmpname' ('Field', 'Type', 'Null', 'Key', $setdefault 'Extra')".
        " VALUES ('$field', '$type', '$null', '$key', $default '$extra')");
}

$res=sqlite_query($SQLITE_DBHANDLE,"SELECT * FROM $tmpname");
while ($a=sqlite_mysql_fetch_assoc($res)) {echo "\n";foreach($a as $key=>$value) echo '('.gettype($value).")$key=>$value</br>\n";}

//return "SELECT * FROM $tmpname";

実行結果

(string)Field=>inumber
(string)Type=>int(11)
(string)Null=>
(string)Key=>PRI
(NULL)Default=>
(string)Extra=>auto_increment

(string)Field=>ititle
(string)Type=>varchar(160)
(string)Null=>YES
(string)Key=>
(NULL)Default=>
(string)Extra=>

(string)Field=>ibody
(string)Type=>text
(string)Null=>
(string)Key=>MUL
(string)Default=>
(string)Extra=>

(string)Field=>imore
(string)Type=>text
(string)Null=>YES
(string)Key=>
(NULL)Default=>
(string)Extra=>

(string)Field=>iblog
(string)Type=>int(11)
(string)Null=>
(string)Key=>
(string)Default=>0
(string)Extra=>

(string)Field=>iauthor
(string)Type=>int(11)
(string)Null=>
(string)Key=>
(string)Default=>0
(string)Extra=>

(string)Field=>itime
(string)Type=>datetime
(string)Null=>
(string)Key=>MUL
(string)Default=>0000-00-00 00:00:00
(string)Extra=>

(string)Field=>iclosed
(string)Type=>tinyint(2)
(string)Null=>
(string)Key=>
(string)Default=>0
(string)Extra=>

(string)Field=>idraft
(string)Type=>tinyint(2)
(string)Null=>
(string)Key=>
(string)Default=>0
(string)Extra=>

(string)Field=>ikarmapos
(string)Type=>int(11)
(string)Null=>
(string)Key=>
(string)Default=>0
(string)Extra=>

(string)Field=>icat
(string)Type=>int(11)
(string)Null=>YES
(string)Key=>
(NULL)Default=>
(string)Extra=>

(string)Field=>ikarmaneg
(string)Type=>int(11)
(string)Null=>
(string)Key=>
(string)Default=>0
(string)Extra=>