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

Nucleus SQLite化計画における次の課題。

１．日付表示の問題（→解消）
２．SQL 関数への対応
３．IF, IFNULL, NULLIF, CASE などの条件構文への対応

最優先で実装する機能：
concat (実装終了)
ifnull (実装終了)
IF (実装終了)
INTERVAL(実装終了)
SHOW FIELDS FROM (実装終了)
SHOW KEYS FROM (実装終了)
ALTER TABLE (実装終了)


ALTER TABLE ... MODIFY
ただし、MySQL バージョン 3.22.16a 以降では、MODIFY を使用することで、カラムの名前を変更することなく、カラムの型変更を実行できる。
ということは、型の指定が無視される SQLite では、この場合何もしなくて良いことになる？他の ALTER TABLEの機能は、Jon Jensenさんのコードを使うことでできるようになっている模様。

SHOW FIELDS FROM とSHOW KEYS FROM は、mysql_fetch_array()で次のような内容を含む配列を返す。ちなみに、SHOW FIELDS は SHOW COLUMNS のシノニムで、SHOW KEYS は SHOW INDEX のシノニム。

SHOW FIELDS FROM nucleus_item
(string) :0=>inumber
(string) :Field=>inumber
(string) :1=>int(11)
(string) :Type=>int(11)
(string) :2=>
(string) :Null=>
(string) :3=>PRI
(string) :Key=>PRI
(NULL) :4=>
(NULL) :Default=>
(string) :5=>auto_increment
(string) :Extra=>auto_increment

(string) :0=>ititle
(string) :Field=>ititle
(string) :1=>varchar(160)
(string) :Type=>varchar(160)
(string) :2=>YES
(string) :Null=>YES
(string) :3=>
(string) :Key=>
(NULL) :4=>
(NULL) :Default=>
(string) :5=>
(string) :Extra=>

(string) :0=>ibody
(string) :Field=>ibody
(string) :1=>text
(string) :Type=>text
(string) :2=>
(string) :Null=>
(string) :3=>MUL
(string) :Key=>MUL
(string) :4=>
(string) :Default=>
(string) :5=>
(string) :Extra=>

(string) :0=>imore
(string) :Field=>imore
(string) :1=>text
(string) :Type=>text
(string) :2=>YES
(string) :Null=>YES
(string) :3=>
(string) :Key=>
(NULL) :4=>
(NULL) :Default=>
(string) :5=>
(string) :Extra=>

(string) :0=>iblog
(string) :Field=>iblog
(string) :1=>int(11)
(string) :Type=>int(11)
(string) :2=>
(string) :Null=>
(string) :3=>
(string) :Key=>
(string) :4=>0
(string) :Default=>0
(string) :5=>
(string) :Extra=>

(string) :0=>iauthor
(string) :Field=>iauthor
(string) :1=>int(11)
(string) :Type=>int(11)
(string) :2=>
(string) :Null=>
(string) :3=>
(string) :Key=>
(string) :4=>0
(string) :Default=>0
(string) :5=>
(string) :Extra=>

(string) :0=>itime
(string) :Field=>itime
(string) :1=>datetime
(string) :Type=>datetime
(string) :2=>
(string) :Null=>
(string) :3=>MUL
(string) :Key=>MUL
(string) :4=>0000-00-00 00:00:00
(string) :Default=>0000-00-00 00:00:00
(string) :5=>
(string) :Extra=>

(string) :0=>iclosed
(string) :Field=>iclosed
(string) :1=>tinyint(2)
(string) :Type=>tinyint(2)
(string) :2=>
(string) :Null=>
(string) :3=>
(string) :Key=>
(string) :4=>0
(string) :Default=>0
(string) :5=>
(string) :Extra=>

(string) :0=>idraft
(string) :Field=>idraft
(string) :1=>tinyint(2)
(string) :Type=>tinyint(2)
(string) :2=>
(string) :Null=>
(string) :3=>
(string) :Key=>
(string) :4=>0
(string) :Default=>0
(string) :5=>
(string) :Extra=>

(string) :0=>ikarmapos
(string) :Field=>ikarmapos
(string) :1=>int(11)
(string) :Type=>int(11)
(string) :2=>
(string) :Null=>
(string) :3=>
(string) :Key=>
(string) :4=>0
(string) :Default=>0
(string) :5=>
(string) :Extra=>

(string) :0=>icat
(string) :Field=>icat
(string) :1=>int(11)
(string) :Type=>int(11)
(string) :2=>YES
(string) :Null=>YES
(string) :3=>
(string) :Key=>
(NULL) :4=>
(NULL) :Default=>
(string) :5=>
(string) :Extra=>

(string) :0=>ikarmaneg
(string) :Field=>ikarmaneg
(string) :1=>int(11)
(string) :Type=>int(11)
(string) :2=>
(string) :Null=>
(string) :3=>
(string) :Key=>
(string) :4=>0
(string) :Default=>0
(string) :5=>
(string) :Extra=>


SHOW KEYS FROM nucleus_item
(string) :0=>nucleus_item
(string) :Table=>nucleus_item
(string) :1=>0
(string) :Non_unique=>0
(string) :2=>PRIMARY
(string) :Key_name=>PRIMARY
(string) :3=>1
(string) :Seq_in_index=>1
(string) :4=>inumber
(string) :Column_name=>inumber
(string) :5=>A
(string) :Collation=>A
(string) :6=>14
(string) :Cardinality=>14
(NULL) :7=>
(NULL) :Sub_part=>
(NULL) :8=>
(NULL) :Packed=>
(string) :9=>
(string) :Null=>
(string) :10=>BTREE
(string) :Index_type=>BTREE
(string) :11=>
(string) :Comment=>

(string) :0=>nucleus_item
(string) :Table=>nucleus_item
(string) :1=>0
(string) :Non_unique=>0
(string) :2=>inumber
(string) :Key_name=>inumber
(string) :3=>1
(string) :Seq_in_index=>1
(string) :4=>inumber
(string) :Column_name=>inumber
(string) :5=>A
(string) :Collation=>A
(string) :6=>14
(string) :Cardinality=>14
(NULL) :7=>
(NULL) :Sub_part=>
(NULL) :8=>
(NULL) :Packed=>
(string) :9=>
(string) :Null=>
(string) :10=>BTREE
(string) :Index_type=>BTREE
(string) :11=>
(string) :Comment=>

(string) :0=>nucleus_item
(string) :Table=>nucleus_item
(string) :1=>1
(string) :Non_unique=>1
(string) :2=>itime
(string) :Key_name=>itime
(string) :3=>1
(string) :Seq_in_index=>1
(string) :4=>itime
(string) :Column_name=>itime
(string) :5=>A
(string) :Collation=>A
(string) :6=>14
(string) :Cardinality=>14
(NULL) :7=>
(NULL) :Sub_part=>
(NULL) :8=>
(NULL) :Packed=>
(string) :9=>
(string) :Null=>
(string) :10=>BTREE
(string) :Index_type=>BTREE
(string) :11=>
(string) :Comment=>

(string) :0=>nucleus_item
(string) :Table=>nucleus_item
(string) :1=>1
(string) :Non_unique=>1
(string) :2=>ibody
(string) :Key_name=>ibody
(string) :3=>1
(string) :Seq_in_index=>1
(string) :4=>ibody
(string) :Column_name=>ibody
(string) :5=>A
(string) :Collation=>A
(string) :6=>14
(string) :Cardinality=>14
(NULL) :7=>
(NULL) :Sub_part=>
(NULL) :8=>
(NULL) :Packed=>
(string) :9=>
(string) :Null=>
(string) :10=>FULLTEXT
(string) :Index_type=>FULLTEXT
(string) :11=>
(string) :Comment=>

(string) :0=>nucleus_item
(string) :Table=>nucleus_item
(string) :1=>1
(string) :Non_unique=>1
(string) :2=>ibody
(string) :Key_name=>ibody
(string) :3=>2
(string) :Seq_in_index=>2
(string) :4=>ititle
(string) :Column_name=>ititle
(string) :5=>A
(string) :Collation=>A
(string) :6=>14
(string) :Cardinality=>14
(NULL) :7=>
(NULL) :Sub_part=>
(NULL) :8=>
(NULL) :Packed=>
(string) :9=>YES
(string) :Null=>YES
(string) :10=>FULLTEXT
(string) :Index_type=>FULLTEXT
(string) :11=>
(string) :Comment=>

(string) :0=>nucleus_item
(string) :Table=>nucleus_item
(string) :1=>1
(string) :Non_unique=>1
(string) :2=>ibody
(string) :Key_name=>ibody
(string) :3=>3
(string) :Seq_in_index=>3
(string) :4=>imore
(string) :Column_name=>imore
(string) :5=>A
(string) :Collation=>A
(string) :6=>14
(string) :Cardinality=>14
(NULL) :7=>
(NULL) :Sub_part=>
(NULL) :8=>
(NULL) :Packed=>
(string) :9=>YES
(string) :Null=>YES
(string) :10=>FULLTEXT
(string) :Index_type=>FULLTEXT
(string) :11=>
(string) :Comment=>

このテーブルの構造は以下の通り

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;

SHOW INDEX は、ODBC での SQLStatistics 呼び出しとよく似た形式でインデックス情報を返します。以下のカラムが返されます。

Table        テーブル名。 
Non_unique   インデックスに重複が許されない場合は 0、許される場合には 1。 
Key_name     インデックス名。 
Seq_in_index インデックスのカラムシーケンス番号。1 から始まる。 
Column_name  カラム名。 
Collation    インデックスでのカラムのソート方法。 MySQL ではこれは ‘A’（昇順）または NULL（ソートしない）になる。 
Cardinality  インデックス内のユニークな値の数。 これは、isamchk -a の実行によって更新される。 
Sub_part     カラムが部分的にインデックスになっている場合は、インデックスになっている文字数。 キー全体がインデックスになっている場合は NULL。 
Null         カラムに NULL を含めることができれば 'YES'。 
Index_type   使用されるインデックス方式。 
Comment      さまざまなコメント。現在のところ、MySQL 4.0.2 より前のバージョンでは、インデックスが FULLTEXT か否かを表示。 

これに対応できそうなSQLiteの機能は、mekyoさんに教わったこれだ。実行結果の例は、次の通り

SELECT * FROM sqlite_master WHERE tbl_name = 'nucleus_item' ORDER BY type DESC
(string) :0=>table
(string) :type=>table
(string) :1=>nucleus_item
(string) :name=>nucleus_item
(string) :2=>nucleus_item
(string) :tbl_name=>nucleus_item
(string) :3=>23
(string) :rootpage=>23
(string) :4=>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 '', 'iauthor' int(11) 
NOT NULL default '', 'itime' datetime NOT NULL 
default '0000-00-00 00:00:00', 'iclosed' tinyint(2) 
NOT NULL default '', 'idraft' tinyint(2) NOT NULL 
default '', 'ikarmapos' int(11) NOT NULL default '', 'icat' 
int(11) default NULL, 'ikarmaneg' int(11) NOT NULL 
default '', UNIQUE ('inumber') )
(string) :sql=>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 '', 'iauthor' int(11) NOT NULL default '', 'itime' 
datetime NOT NULL default '0000-00-00 00:00:00', 
'iclosed' tinyint(2) NOT NULL default '', 'idraft' tinyint(2) 
NOT NULL default '', 'ikarmapos' int(11) NOT NULL 
default '', 'icat' int(11) default NULL, 'ikarmaneg' int(11) 
NOT NULL default '', UNIQUE ('inumber') )

(string) :0=>index
(string) :type=>index
(string) :1=>(nucleus_item autoindex 1)
(string) :name=>(nucleus_item autoindex 1)
(string) :2=>nucleus_item
(string) :tbl_name=>nucleus_item
(string) :3=>22
(string) :rootpage=>22
(NULL) :4=>
(NULL) :sql=>

この変換は、mysql_query()のラッピングだけではうまく行かない、のか？！(→テンポラリテーブルの作成でうまく行った)