CPA-LABテクニカル

（追記があります。）CakePHP1.2をSQLite3で動かしてみた。＆ダウンロード
PHP5のPDOは、すごく便利。
プリペアードステートメントで、インジェクションから守ってくれるわ、バインドでコードが楽ちんだわ、様々な利点がある。
しかし、PDO ＆　SQLite　で唯一の弱点がある。
それは、

SELECT文において、フィールド名につけられたテーブル名は、重複しない限り、結果セットに返さない

というものである。
(SQLiteの場合であり、他のデータベースは知らない）
つまり

SELECT "tableA"."fieldA" FROM "TableA"

としても、結果セットには、fieldAの記述しかなく、tableAを返さないのだ。
cakephpのデータ管理方法は

[データ順][テーブル名（モデルのクラス名）][フィールド名]

となっているため、結果セットからテーブル名を取り出している。

別に、tableAから結果を引っ張ってきているのだから、必要ないのでは？
この場合はそうなのだが、複雑なjoinをしていると、その管理は面倒である。

だから、、、cakephpでは、結果セットから、テーブル名を取得しているのであろう。

これが、SQLite２のsqlite_・・・関数ではできるんだ。テーブル名とフィールド名をドットで連結して返してくれる。


実はPDOには、オプションで、テーブル名を返す、というものも用意されている。


が

PHP5のPDOドライバは、SQLiteに関して、そのオプションを設定する、コマンド　setAttribute　をサポートしていないのだ！（セットしようとすると怒られる）。

本当は

PDO::ATTR_FETCH_TABLE_NAMES (integer) 
結果セット中の各カラム名にテーブル名を追加します。 テーブル名とカラム名は、小数点 (.) で区切られます。 

さえ使えれば移植は楽なのに、これを設定できないのがつらい。


PDO::FETCH_NAMED、PDO::FETCH_ASSOCは、fetchの方で設定できるが、これだとテーブル名は返さない。。。。


漸進的な改善策としては、ASを使うことだろう。

SELECT "tableA"."fieldA" AS "tableA.fieldA"　FROM・・・・

こうすれば、結果セットにテーブル名も返すことになる（”の位置に注意）


しかしそれは、cakephpの中枢部にメスを入れないといけない話なので、私は断念した。
特に複数のテーブルと連結したときや、関数を使った場合分けが面倒のよう。


でも、これもその場しのぎにすぎない。
本当は、cakephpがきちんとPDOに対応して欲しい。そうすれば、今みたいにデータベースごとに巨大なファイルを書かなくても、ほんのちょっとの修正で、様々なデータベースに対応できる。


何より、変数バインドやプリペアなどをPDOで面倒みてくれるのだから、断然早いはず。


PDOへの対応そのものは、dboの全面書き直しになるので、大変なのはよくわかるけど、早くPDO対応になって欲しいなあ。別にPDOとcakephpの相性が悪いのでなくて、cakephpがPHP4にも対応しているからこそ、問題がややこしくなっているような気がする。


（覚書の追記）下も試したけどダメだった。おそらくSQLiteがきちんとテーブル名を返しても、PDOドライバが無視するのだろう。

PRAGMA full_column_names = ON; (1)
PRAGMA full_column_names = OFF; (0)

SQLite のコールバック関数で報告されるカラム名は、"テーブル名.カラム名" の形式が使われない限り、単にカラムの名前そのものです。しかし full_column_names が ON にされると、単純なクエリーにおいてもカラム名は 常に "テーブル名.カラム名" 形式で報告されます。
SQLite が認識できる SQL

cakephpを触っていて、cakeさんがSQLite3でうまく動かないものだから、ダウングレードしてSQLite2でちょっといじってみた。以下はその感想。

バージョン3の方が、、、、

1. 他で書込中でも、素直にselectを受け付けてくれる。
2. 断然早い

CakePHPよ。早くSQLite3に対応してたもうなかれ。

（追記）CakePHP1.2をSQLite3で動かしてみた。＆ダウンロード

SQLiteのデータは、ファイル１つ。しかし、テキストエディタで読むことはできません。何かしらのツールを使う必要があります（当たり前）。
CakePHP1.2で、SQLite2を動かすには、CakePHP1.2をSQLite3で動かしてみた。＆ダウンロードをご覧下さい。


SQLiteの管理ツールを備忘のため残しておきます。

SQLiteManager

http://www.sqlitemanager.org/
フランス語？　ダウンロードページ：http://www.sqlitemanager.org/index.php?option=com_content&task=view&id=10&Itemid=10&lang=fr
一番よくできている管理ツールですが、一部バグがあります。必ず、以下も参照のこと。
SQLiteManager を Windows で使う
/halt/Snapshot - SQLiteManager1.1.3でUTF-8のlangファイルを作る

• PHPで構成されているので、PHPさえ動けば、どのサーバーでも動く。
• 機能はphpMyAdminにとても似ています。
• SQLite version2 & 3

TkSQLite

http://reddog.s35.xrea.com/wiki/TkSQLite.html
日本人の方のようです。winローカルで、自在に操れます。機能ごとの安定性は、SQLiteManagerよりも上だけど、大量のデータを読み込ませるとちょっと遅いです。私はテーブルの構成・変更は、TkSQLite で行って、データ処理は、SQLiteManagerを使うことが多いです。

• SQLite version2 & 3
• わかりやすく、安定した操作感

ちまたで多く使われているデータベースでは、MySQLやPostgreSQL（ポスグレ）なんでしょうけど、私は、SQLiteが好きだ。そして、私のように専用サーバを持たず、レンタルサーバのみの環境の方には、MySQL、PostgreSQLよりもSQLiteのほうが、絶対に幸せになれると思う。

1. データがファイルひとつなので、バックアップが超簡単！
   SQLiteは、サーバー型でないため、データはたったひとつのファイルにまとめられている。バックアップするには、それをFTPでダウンロードするか、そのサーバー上でコピーするだけだ。MySQLでも、ツールを使えばバックアップはできるけど、ファイルのコピーだけで完結する簡潔さには、かなううまい。なお、ファイル名もなんでもよく拡張子もあってもなくてもいい。でも、バージョン２は.sqliteバージョン３は.sqlite3としている人が多いのかな。USBにデータファイルをコピーして、外出先のローカルサーバーで、すぐにSQLiteを使うなんて、当たり前にできる。MySQLでやろうとしたら気が遠くなる。-----DBのテーブル構成を変えたい？そう、なら、サーバーからダウンロードして、デスクトップのツールで変更して、ついでに入らなくなったデータ消去して、新たな更新データもいれておいて、アップロードすれば終わり。ローカルとリモートの同期が超簡単なのもDBがファイルだから。
   
2. 多くのレンタルサーバMySQLよりも高速に作動する
   レンタルサーバのMySQLの遅さに参っている人は多い。もっともこれはMySQLが悪いのでなく、レンタルサーバー業者が多くのアカウントをひとつのMySQLサーバーに押し込んでいるだけ。データベースサーバはサーバに大きな負担をかけるので、せっかくの早いと言われるMySQLが劇遅になってしまう例も見かけられる。それに対し、SQLiteはサーバー上の自分のスペースに置くだけなので、借りているWEBサーバさえまともならば、快適に使える。もちろん、WEBサーバも、共用であれば、MySQLのような副作用もあり得るわけであるが、SQLiteがサーバーに優しい動作であるので、多くの利点がある。なお、同じ環境では、MySQLと同じか、少し早いとのこと。WEBで検索すると書込が遅いという記事があるが、それはテスト方法を間違っているだけ（トランザクションを適切に使っていない）。
   
3. SQL機能的に問題ない
   基本的なSQL文はすべて使える(SQL92準拠)。ここが(SQLite が認識できる SQL )詳しい。ビュー、トリガーもOK。check制約などが使えないが、それがないのがSQLiteなのだ。プロ中のプロが大規模サイトで使うには非力であっても、通常*1の使い方で問題となる面はでてこないであろう。
   
4. 設定が容易。
   パスワード設定がいらないので、MySQLを使ったことのある人ならば、「えっ、もう繋がるの？」とびっくりするだろう。
   
5. 納入が容易。
   私は関係ないが商売として、簡易的なデータベースシステムを使ってWEBシステムを構築しているのであれば、納入の容易さは、大きな利点となるだろう。なんせ、HTMLと同じコピペで納入できるのだから。ライセンスも心配ない。
   
6. PHP5では、SQLite3が標準で使える。PHP4でもSQLite2が標準で使える。
   何もしなくても、いきなりプログラムを書けば自動的にデータベースファイルが作成される。標準バンドルはとてもありがたい。
   
7. データ型の概念が希薄であり、長さも柔軟、PHPと、とても相性がよい。
   多くのデータベースサーバは、テキストの長さを決めなくてはいけなかったりする。（もちろんそうでない型もあるが、基本としてそうなっている。）SQLiteではchr(20)なんて必要ない。
   
8. 無料であり、なんとコアソースコード著作権を主張していない。ライセンスの心配は一切いらない。
   神様のような存在である。
   
9. 容量が少なくて済む。コアは225Kbしかない。
10. rubyでもSQLiteが標準になった（そうだ）
    rubyは使ったことないので、よく知らないけど。
    （追記：ruby単体でなく、ruby on railsのデフォルトDBとして採用されたそうです。）
    

このように、多くの利点があるSQLite、使わない手はない。


私は特に　１　の利点は、ものすごく大きいと思う。外部サーバーから、FTPでダウンロードしてデータベースの中身をちょちょっと修正して、すぐにアップロードできる。なんて、MySQLでは絶対にできないでしょう。


一方で、こんなデメリットは知っておくべきだろう。

1. パスワード設定がない
   ファイル形式であり、ユーザー管理の概念がないため、SQLite自身には、セキュリティ機能はない。自分で、ファイルへのアクセス権限をしっかりとコントロールする必要がある。サーバーのドキュメントルート以下に置く場合は、htaccessの設定が必須だろう。ドキュメントルートより上に置けば、外部の第三者からのセキュリティに問題があることはほとんどないだろう。
   
2. 書込がダブると書込エラーになる
   サーバー型でないので、複数の書込を順次処理することができない。最初の人が書込をしている間に、次の人が書込をしようとするとエラーになるので、そのケアーをする必要があるシステムもあるかもしれない。個人やSOHOのCMS（コンテンツマネジメントシステム）やブログ程度であれば、問題にあることはないだろう。読み出しはダブってもOK。頻繁に不特定多数がデータベースに書き込むような処理は苦手だろう。
   
3. バージョン２と３でデータベースの互換性がない。
   ２と３は別物と考えたほうがいい。PHPからのアクセスに限って言えば、SQLite2は、通常のsqlite_～関数を使ってアクセスするが、SQLite3は、PHP5.1以上で、かつ、PDOというデータベースドライバを通じてしか操作できないので、２->３のアップグレードは注意を要する。（そんなに難しくはないけど）。なお、PHP4,5でPDOを使わずにSQLite3を使う方法もあるようだが、PHP本体のリビルドが必要なようなことと、windouws環境が揃っていないことから、現時点(2008/1)ではお薦めできない。もちろんPDOを使えばアクセスできるので、皆さんPDOを使いましょう。
   
4. 管理ツールでは、phpMyadminに劣るものしかない。
   別記事で私の使っているツールをあげておきます。phpMyadminには及ばないが、しかし十分である。
   
5. 日本語情報は、MySQLに比べて少ない。
   皆さんで盛り上げていきましょう。ちなみに、SQLiteをやってみようは、よくまとまっておおられます。
   

（追記）CakePHP1.2をSQLite3で動かしてみた。＆ダウンロード

（追記）もっと効率的な修正方法があります。CakePHP1.2bとSQLite不具合のもっと簡単な直し方


以前にSQliteでのnew時（insert）の不具合を修正したのだけれど、今度は、editとdeleteでも不具合があることがわかった。しばらくMySQLのお勉強をしていたので、sqliteはほっておいたんだけど、やっぱりsqliteが恋しいので戻ってきた。


こちらでも、同じ認識の方がいるらしい。
CakePHPではまったこと１５（sqliteを使っているときのupdate）
この方、いろいろtips書いておられて、頭が下がります。


なお、これらの不具合は、次期バージョンでは修正されるらしいので、つかのまのtips。これを書いている今でも、本家がアップデートしたら、この記事の意味はありません。また　scaffoldもどきでの動作は確認していますが、自分でsave文は書いていないのでそこは未テストです。


そして、このeditとdeleteは、cakephpが作るSQL文の最終形をハックする形にしたので、複雑なテーブル構成の場合は、何らかの不具合がでる可能性が大いにあります。なお、テーブル名にピリオドがあるとそれだけでNGです。

環境

cake_1.2.0.6311-beta
SQLite2　　←3ではありません。

まずは、どこがおかしいのか列挙してみよう。以下は、MySQLでは通っても、SQLiteではNGのものである。
まずは、DELETEのSQL文から

1. DELETE TabelName FROM TableName
   というあり得ない形になっていた。真ん中のテーブル名が不要
   
2. WHERE FieldName IN('1')
   みたいな形になっていた。sqliteでは、deleteでIN句は使用不能。WHEWRE FieldName='1'であることが必要。
   
3. "TableName"."FieldName" as "TabaleAlias"."FieldName"
   になっていた。deleteでasは使えないので、as以降を消去。deleteで複数テーブルを一気に消去することはないだろうから、たぶん大丈夫。
   

で、EDIT(UPDATE文)についても、上記１以外はすべてあてはまる。
本来であれば、SQL文の生成過程を修正するのが王道であり、おそらく本家はそのように対処するのでしょうが、それはとてつもなく大変なので、こんな感じの「応急措置」です。


では、いってみましょうか。


まずは、

• \cake\libs\model\datasources\dbo\dbo_sqlite.phpを
• \app\models\datasources\dbo\dbo_sqlite.phpにこぴーして、以下の修正＆追加。

（フォルダがない場合は作りましょう。）

function _execute($sql) {
    return sqlite_query($this->connection, $sql);
}

を探しだし、以下のように修正する。

function _execute($sql) {
    return sqlite_query($this->connection, $this->spok_escape($sql));
}

そう、SQLの実行直前でエスケープするという、脆弱性を生みかねない危険な方法。


そして、このファイルの一番下の「}」直前に、以下を付け加える。
なお、わかる人からみたら、「無謀な修正」なので、自己責任で。私の少ないテストしか経ていないので、他で動作しない可能性もたくさんあります。

function spok_escape($sql){
    #UPDATE修正
    if( substr($sql,0,6)==='UPDATE' ) {
        #テーブルasの除去
        $sql=preg_replace("/\sAS\s\"[^\"]+\"/",' ',$sql);
        #テーブル名の除去
        $sql=preg_replace("/\s\"[^\"]+\"\./",' ',$sql);
        #INを=に
        $sql=preg_replace("/\sIN\s*\('/"," ='",$sql);
        #INの後ろカッコ除去
        $sql=preg_replace("/'\)/","' ",$sql);
    }

    #DELLTE修正
    if( substr($sql,0,6)==='DELETE' ) {
        #deleteとfromの間にテーブルが入っていたのを除去
        $sql=preg_replace("/[^\.]*FROM/",'DELETE FROM',$sql);
        #inの処理
        $sql=preg_replace("/\sIN *\('/"," ='",$sql);
        $sql=preg_replace("/'\)/","' ",$sql);
        #テーブルasの除去
        $sql=preg_replace("/\sAS\s\"[^\"]+\"/",' ',$sql);
        #テーブル名の除去
        $sql=preg_replace("/\s\"[^\"]+\"\./",'  ',$sql);
    }

    return $sql;

    }

正規表現なので、//の間を一字一句間違えても動作しません。なお、バックスラッシュは半角の￥マークです。このブログでは、システムの関係で、／になっております。コピペで大丈夫と思うけど。
本当は preg_replaceをつなげなくて（みっともない）、配列に一気に渡したほうがいいのだと思うけど、試行錯誤でこうなったので、このままにしておきます。
IN句のカッコをとるところなんかはいいかげんで、正直怖いです。何か別にカッコを使っていれば、それを取ってしまうので、そこで新たな不具合を生むおそれアリ。（このスクリプト全般がそうなんだけど）


(追記)
入力データの中に　') や　IN(' 　や　"."　などがあると消去されます。入れるデータによってはうまくいかないです。（このようなやり方では仕方ない）


繰り返しですが時期バージョンでは修正されるそうなので、そうなったらすぐに本家に従いましょう。
SQLiteファンとしては早く対処して欲しい。しかし無料だから強くもいえんしね。皆さんもドネーションぐらいはしましょう。paypalで20秒でできちゃったよ。


なお、この同じファイルで、SQliteでのnew時（insert）の不具合を修正をやっておかないとinsertできません。

うーむ。ベータ版とはいえ。。。楽じゃないよcakephp。