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

kosugiatkipsさんに教わった命令で、MySQL の『SHOW KEYS FROM』に相当する部分が見えてきた。キー（インデックス）の作成は、SQLiteでは、

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

のようにすればよい。この書式をテーブルから読み出すには、

$res=sqlite_mysql_query("SELECT sql,name FROM sqlite_master WHERE type = 'index' and tbl_name = 'nucleus_item' ORDER BY type DESC");
while ($a=sqlite_mysql_fetch_assoc($res)) foreach($a as $key=>$value) echo "$key=>$value</br>\n";

で良いらしい。実行結果の例は、次の通り。

sql=>CREATE INDEX 'itime' ON 'nucleus_item' ('itime')
name=>'itime'
sql=>CREATE UNIQUE INDEX 'inumber' ON 'nucleus_item' ('inumber')
name=>'inumber'
sql=>CREATE INDEX 'ibody' ON 'nucleus_item' ('ibody','ititle','imore')
name=>'ibody'
sql=>
name=>(nucleus_item autoindex 1)

$tname='nucleus_item';

//sqlite_mysql_query("CREATE UNIQUE INDEX 'inumber' ON '$tname' ('inumber')");
//sqlite_mysql_query("CREATE INDEX 'itime' ON '$tname' ('itime')");
//sqlite_mysql_query("CREATE INDEX 'ibody' ON '$tname' ('ibody','ititle','imore')");


$res=sqlite_mysql_query("SELECT * FROM sqlite_master WHERE tbl_name = '$tname' ORDER BY type DESC");
$tablestruct=sqlite_mysql_fetch_assoc($res);
//foreach($tablestruct as $key=>$value) echo "$key=>$value</br>\n";


$tablesql=$tablestruct['sql'];
$notnull=array();
foreach(sqlite_splitByComma(substr($tablesql,strpos($tablesql,'(')+1)) as $value) {
    $name=str_replace("'",'',substr($value,0,strpos($value,' ')));
    echo "$name:$value</br>\n";
    if (strpos(strtoupper($value),'NOT NULL')!==false) $notnull[$name]='';
    else $notnull[$name]='YES';
}
echo "";

if (preg_match('/[^a-zA-Z_\']([\S]+)[^a-zA-Z_\']+INTEGER NOT NULL PRIMARY KEY/i',$tablesql,$matches)) {
    $pkey=$matches[1];
    $pkeynull='';
} else if (preg_match('/[^a-zA-Z_\']([\S]+)[^a-zA-Z_\']+INTEGER PRIMARY KEY/i',$tablesql,$matches)) {
    $pkey=$matches[1];
    $pkeynull='YES';
} else $pkey=null;

$pkey=str_replace("'",'',$pkey);
echo "$pkey";

echo "";

if (function_exists('microtime')) $tmpname='t'.str_replace('.','',str_replace(' ','',microtime()));
else $tmpname = 't'.rand(0,999999).time();//'inumber' INTEGER NOT NULL PRIMARY KEY, 
sqlite_mysql_query("CREATE TEMPORARY TABLE $tmpname ('Table', 'Non_unique', 'Key_name', 'Seq_in_index',".
    " 'Column_name', 'Collation', 'Cardinalit', 'Sub_part', 'Packed', 'Null', 'Index_type', 'Comment')"); 

$res=sqlite_mysql_query("SELECT sql,name FROM sqlite_master WHERE type = 'index' and tbl_name = '$tname' ORDER BY type DESC");
while ($a=sqlite_mysql_fetch_assoc($res)) {
    if (!($sql=$a['sql'])) {
        if ($pkey && strpos(strtolower($a['name']),'autoindex')) {
            $cardinalit=sqlite_mysql_num_rows(sqlite_mysql_query("SELECT $pkey FROM '$tname'"));
            sqlite_mysql_query("INSERT INTO $tmpname ('Table', 'Non_unique', 'Key_name', 'Seq_in_index',".
                " 'Column_name', 'Collation', 'Cardinalit', 'Sub_part', 'Packed', 'Null', 'Index_type', 'Comment')".
                " VALUES ('$tname', '0', 'PRIMARY', '1',".
                " '$pkey', 'A', '$cardinalit', null, null, '$pkeynull', 'BTREE', '')"); 

        }
    } else {
        if (($name=str_replace("'",'',$a['name'])) && preg_match('/\(([\s\S]+)\)/',$sql,$matches)) {
            foreach(explode(',',$matches[1]) as $key=>$value) {
                $columnname=str_replace("'",'',$value);
                if (strpos(strtoupper($sql),'CREATE UNIQUE ')===0) $nonunique='0';
                else $nonunique='1';
                $cardinalit=sqlite_mysql_num_rows(sqlite_mysql_query("SELECT $columnname FROM '$tname'"));
                sqlite_mysql_query("INSERT INTO $tmpname ('Table', 'Non_unique', 'Key_name', 'Seq_in_index',".
                    " 'Column_name', 'Collation', 'Cardinalit', 'Sub_part', 'Packed', 'Null', 'Index_type', 'Comment')".
                    " VALUES ('$tname', '$nonunique', '$name', '".(string)($key+1)."',".
                    " '$columnname', 'A', '$cardinalit', null, null, '$notnull[$columnname]', 'BTREE', '')"); 
            }
        }
    }
}
echo "";

$res=sqlite_mysql_query("SELECT * FROM $tmpname");
while ($a=sqlite_mysql_fetch_assoc($res)) {
    foreach($a as $key=>$value) echo "$key=>$value</br>\n";
    echo "</br>\n";
}

実行結果

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

inumber


Table=>nucleus_item
Non_unique=>1
Key_name=>ibody
Seq_in_index=>1
Column_name=>ibody
Collation=>A
Cardinalit=>3
Sub_part=>
Packed=>
Null=>
Index_type=>BTREE
Comment=>

Table=>nucleus_item
Non_unique=>1
Key_name=>ibody
Seq_in_index=>2
Column_name=>ititle
Collation=>A
Cardinalit=>3
Sub_part=>
Packed=>
Null=>YES
Index_type=>BTREE
Comment=>

Table=>nucleus_item
Non_unique=>1
Key_name=>ibody
Seq_in_index=>3
Column_name=>imore
Collation=>A
Cardinalit=>3
Sub_part=>
Packed=>
Null=>YES
Index_type=>BTREE
Comment=>

Table=>nucleus_item
Non_unique=>1
Key_name=>itime
Seq_in_index=>1
Column_name=>itime
Collation=>A
Cardinalit=>3
Sub_part=>
Packed=>
Null=>
Index_type=>BTREE
Comment=>

Table=>nucleus_item
Non_unique=>
Key_name=>inumber
Seq_in_index=>1
Column_name=>inumber
Collation=>A
Cardinalit=>3
Sub_part=>
Packed=>
Null=>
Index_type=>BTREE
Comment=>

Table=>nucleus_item
Non_unique=>
Key_name=>PRIMARY
Seq_in_index=>1
Column_name=>inumber
Collation=>A
Cardinalit=>3
Sub_part=>
Packed=>
Null=>
Index_type=>BTREE
Comment=>

(追記:060404)
テーブル作成の際のPRIMARY KEY(xxx,xxx) にも対処した。あとは、auto_increment の際に、PRIMARY KEY 指定するようにする必要有り。（ver 0.57 で解決）