(CakePHP2.2.1 + SQLite3) 生のSQLを実行する際の注意点

プログラミング


最終更新:2012-08-24
原因はいまだ判明しませんが、汎用的な対処法を見つけました。


下記のように"query()"を使って生のSQLを実行する場合。



(コントローラ)

$data = $this->User->query('SELECT * FROM "users" LIMIT 10');
"*(アスタリスク)"が問題になってしまうようです。

MySQL
問題なく、データを取得できます。


Array

(

  [0] => Array

    (

      [users] => Array

        (

          [id] => 1

          [name] => 佐藤さん

          [created] => 2008-11-15 00:00:00

          [modified] => 2008-11-15 00:00:00

        )
    )
  [1] => Array

    (

      [users] => Array

        (

          [id] => 2

          [name] => 鈴木さん

          [created] => 2008-11-15 22:22:22

          [modified] => 2008-11-15 19:59:50

        )
    )

  ...
SQLite3
"*"というフィールドを探してしまうようです…orz


Array

(

  [0] => Array

    (

      [0] => Array

        (

          [*] => 1

        )
    )
  [1] => Array

    (

      [0] => Array

        (

          [*] => 2

        )
    )

  ...


ただし、アスタリスクを使わなければ、正常に取得できます。



$data = $this->User->query('SELECT "id", "name" FROM "users" LIMIT 10');


Array

(

  [0] => Array

    (

      [0] => Array

        (

          [id] => 1

          [name] => 佐藤さん

        )
    )
  [1] => Array

    (

      [0] => Array

        (

          [id] => 2

          [name] => 鈴木さん

        )
    )

  ...


テーブル名も付け加えれば、一般的な"find()"で取得するものと変わらなくなります。



$data = $this->User->query('SELECT "User"."id", "User"."name" FROM "users" as "User" LIMIT 10');


Array

(

  [0] => Array

    (

      [User] => Array

        (

          [id] => 1

          [name] => 佐藤さん

        )
    )
  [1] => Array

    (

      [User] => Array

        (

          [id] => 2

          [name] => 鈴木さん

        )
    )

  ...
MySQLでは問題ないのに、なぜSQLiteではアスタリスクが障害となるのか…。
原因を探ってみましたが、あちこちに飛んだ処理を追いかけるのが大変で、諦めましたorz


フィールド名一覧を汎用的に取得する方法

当然、フィールド名はテーブルごとに異なるので、フィールド名をモデル内でハードコーディングする必要があります。
メンテナンスの障害になりますよね…。
アスタリスクは使えなくても、せめてフィールド名一覧を簡単に取得する方法はないものかと探してきました。


(成功)

(モデル)

$path = ConnectionManager::$config->{$this->useDbConfig}['database'];

$db = new SQLite3($path);

$rows = $db->query("PRAGMA table_info(\"{$this->useTable}\")");

$all_fields = array();

$model = "\"{$this->name}\"";

while ($row = $rows->fetchArray(SQLITE3_ASSOC)) {

	$field = "\"{$row['name']}\"";

	$all_fields[] = "$model.$field";

}

$db->close();

$all_fields = join(',', $all_fields);



//下記のような文字列になる。

//"Post"."id","Post"."name","Post"."created","Post"."modified"
…ご覧のとおり、独自にデータベースに接続して、素のPHPと生のSQLで問い合わせるという、何とも見苦しい方法です (-_-;)


下記はCakePHPの内部のメソッドを使うという、ほんの少しスマートな方法なのですが、WindowsのXAMPPでは成功するのにLinuxのXAMPPでは成功せず、どうにも安定しないので不採用としました。



(失敗)

(モデル)

$arr = $this->getDataSource($this->useDbConfig)->describe($this->name);

$all_fields = array();

$model = "\"{$this->name}\"";

foreach($arr as $key => $val) {

	$field = "\"$key\"";

	$all_fields[] = "$model.$field";

}

$all_fields = join(',', $all_fields);


バグ報告

この件について、 cakephp.lighthouseapp.com へバグ報告をしました。
(2012-08-25)
#3147 (SQLite3) "*(asterisk)" in SQL cannot work correctly. - CakePHP - cakephp
http://t.co/v3KDt27b