SQLite3 (PDO) を使った全文検索 (FTS) 入門

 

次に、PHPプログラムからSQLite等のデータベースを操作する場合の共通ライブラリ PDO について紹介します

SQLiteは、PDOを使わなくても操作することが出来ますが、PDO ライブラリを使って記述しておけば、基本的には、MySQL や SQLite 等のデータベースの種類に関係なく同じ記述でデータベースを扱うことが出来るので、データベース操作の記述が統一され記述しやすくなり、データベースを移 行する場合等でも最小限の変更で済むようになります

※PDOを使っても、各データベースごとにサポートしているSQL文の違いがあるので全てを共通化出来るわけではありません

PDO

今回作成したプログラムで主に使っているPDOのメソッド(関数)について紹介します

よく使うのは、データベース接続とプリペアードステートメントを使ったSQL実行、結果セットの取得、トランザクションです。

データベース接続 __construct SQLite データベースファイルを絶対パスで指定します

データベースファイルがない場合は、空のデータベースファイル作成を行い、存在する場合はデータベースファイルとの接続を確立します

SQL実行
(SQLエスケープなし)
exec

query

1回のみでSQLエスケープ不要な信頼できるSQLを実行します

※SELECT文以外の結果取得が不要な場合は、exec を使用し、SELECT文で結果セットを PDOStatement オブジェクトとして取得する場合は query を使用します

SQL実行
(プリペアードステートメントによるSQLエスケープ)
prepare

bindValue

execute

 

SQL文にプレースホルダ(変数)を使い信頼出来ない値がセットされる可能性がある場合は、SQLインジェクション対策のために prepared, bindValue, execute を使ってSQLエスケープを行い、安全なSQL文にして実行します

※プレースホルダとPHPでの変数値をバインドさせるには bindValue(変数の値をバインド)、bindParam(変数の参照アドレスとしてバインド)があります

SELECT結果セット取得 fetch

fetchAll

fetchColumn

fetchObject

SELECTの実行結果(PDOStatement オブジェクト)からデータを取得します

取得データが単一か複数データかによって使用関数が異なります。また、配列として取得するか、オブジェクトとして取得するかによっても異なります

トランザクション

 

biginTransaction

commit

rollback

リレーショナル・データベースは、多くの場合複数のテーブルからなり、データを追加、更新、削除する場合には、そのテーブル間等でデータの整合性が保たれ ている必要があります。その為にトランザクションというデータ追加、更新、削除に対して関連するデータに対する処理が全て正常に行われた場合に反映させ、何か問題があった場合 は元に戻す機能があります

SQLiteの場合はサーバーレスのファイル型データベースですので、データ追加、更新時 はトランザクションを使ってある程度まとまったデータを処理したほうがディスクI/O操作を減らせるので高速に実行することが出来ます。但し、トランザク ション時間があまり長いとその間は他のクエリーが待たされてロックエラーとなることも考えられますので、一度に処理する最大データ量はバランスよく見極め る必要があります

データベースクローズ デストラクト PDOのインスタンスに NULL をセットするとクローズされます
明示的にクローズしなくともインスタンスが破棄されるときに自動的にクローズされます

参照 http://php.net/manual/ja/book.pdo.php

詳細はリンク先サイトの説明を参照して下さい

 

Ext_PDO

プログラムを作成するにあたり、PDOを直接扱うコードを書くのはちょっと煩わしかったので、少し扱いやすくなるようにラップした Ext_PDO という拡張クラスを書いてみました

WordPress で MySQL を扱う wpdb クラスにちょっと似た感じで使えるように作りました

SQLの実行と結果データの取得をデータ取得無し、変数(カラム)、行(レコード)、複数行取得に分け、各々プリペアードステートメントの有無で分けています。

データ取得のないSQL実行 sql_exec($sql)
sql_prepare_exec($sql, $ar_param)
変数(カラム)データ取得 sql_get_var($sql, $col_num=0)
sql_prepare_var($sql, $ar_param, $col_num=0)
行(レコード)データ取得 sql_get_row($sql, $output=PDO::FETCH_OBJ)
sql_prepare_row($sql, $ar_param, $output= PDO::FETCH_OBJ)
複数の行データ取得 sql_get_results($sql, $output = PDO::FETCH_OBJ)
sql_prepare_results($sql, $ar_param, $output= PDO::FETCH_OBJ)

各メソッドの詳細は、短いコードですので直接見て下さい m(_ _)m

<?php
/**
 * PDO(SQlite)の拡張クラス
 * 
 *
 * @author enomoto celtislab
 */


class Ext_PDO extends PDO {
    
    public function __construct( $dsn, $DB_USER='', $DB_PASS='' ) {
        parent::__construct( $dsn, $DB_USER, $DB_PASS);
        $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    
    //デバッグ時のエラー表示処理
    static function display_error(PDOException $e) {
        //if (ini_get( 'display_errors' )){
        if ( defined('WP_DEBUG') && WP_DEBUG ){
            var_dump($e->getMessage());
        }
    }   
    
    /**
     * データ取得のないSQL文の実行
     * @param string $sql      SQL文
     * @return 
     */
    public function sql_exec($sql) {
        try {
            $res = $this->exec( $sql );                    
            return $res;
        } catch (PDOException $e) {
            if(PDO::inTransaction()){
                throw $e;
            } else {
                self::display_error($e);
                return false;
            }
        }
    }
    
    /**
     * 変数(カラム)データ取得
     * ※bool 値のカラムに対しては使用不可(falseの区別が出来ない)
     * @param string $sql      SQL文
     * @return 
     */
    public function sql_get_var($sql, $col_num=0) {
        try {
            $stmt = $this->query( $sql );
            $var = $stmt->fetchColumn( $col_num );
            return $var;
        } catch (PDOException $e) {
            if(PDO::inTransaction()){
                throw $e;
            } else {
                self::display_error($e);
                return false;
            }
        }
    }

    /**
     * 行(レコード)データを取得
     * @param string $sql      SQL文
     * @param $output
     *              PDO::FETCH_ASSOC - 結果を連想配列として出力。
     *              PDO::FETCH_NUM - 結果をインデックス配列として出力。      
     *              上記以外の指定 - 結果をオブジェクトとして出力。
     * @return 
     */
    public function sql_get_row($sql, $output=PDO::FETCH_OBJ) {
        try {
            $stmt = $this->query( $sql );
            if($output === PDO::FETCH_ASSOC || $output === PDO::FETCH_NUM ){
                $row = $stmt->fetch( $output );
            }
            else {
                $row = $stmt->fetchObject();
            }
            return $row;
        } catch (PDOException $e) {
            if(PDO::inTransaction()){
                throw $e;
            } else {
                self::display_error($e);
                return false;
            }
        }
    }
    
    /**
     * 複数の行データを取得
     * @param string $sql      SQL文
     * @param $output
     *              PDO::FETCH_ASSOC - 結果を連想配列として出力。
     *              PDO::FETCH_NUM - 結果をインデックス配列として出力。      
     *              上記以外の指定 - 結果をオブジェクト配列として出力。
     * @return 
     */
    public function sql_get_results($sql, $output = PDO::FETCH_OBJ) {
        try {
            $stmt = $this->query( $sql );
            if($output === PDO::FETCH_ASSOC || $output === PDO::FETCH_NUM ){
                $results = $stmt->fetchAll( $output );
            }
            else {
                $stmt->setFetchMode(PDO::FETCH_CLASS, 'stdClass');
                $results = $stmt->fetchAll();
            }
            return $results;
        } catch (PDOException $e) {
            if(PDO::inTransaction()){
                throw $e;
            } else {
                self::display_error($e);
                return false;
            }
        }
    }

    /**
     * プリペアドステートメント(セキュリティ対策)
     * @param string $sql      例. "INSERT INTO table (id, title, author, date) VALUES ( ?, ?, ?, ?)"
     * @param array  $ar_param 例. array($id, $title, $author, $now->format('Y-m-d H:i:s')
     * @param $output
     * @return 
     */
    // データ取得のないSQL文の実行
    public function sql_prepare_exec($sql, $ar_param) {
        try {
            $stmt = $this->prepare( $sql );
            $stmt = self::_stmt_bind( $stmt, $ar_param );
            return self::_stmt_execute( $stmt );
        } catch (PDOException $e) {
            if(PDO::inTransaction()){
                throw $e;
            } else {
                self::display_error($e);
                return false;
            }
        }
    }

    // 変数(カラム)データ取得
    public function sql_prepare_var($sql, $ar_param, $col_num=0) {
        try {
            $stmt = $this->prepare( $sql );
            $stmt = self::_stmt_bind( $stmt, $ar_param );
            $var = $stmt->execute();
            if($var !== false)
                $var = $stmt->fetchColumn( $col_num );
            return $var;
        } catch (PDOException $e) {
            if(PDO::inTransaction()){
                throw $e;
            } else {
                self::display_error($e);
                return false;
            }
        }
    }
    
    // 行(レコード)データを取得
    public function sql_prepare_row($sql, $ar_param, $output= PDO::FETCH_OBJ) {
        try {
            $stmt = $this->prepare( $sql );
            $stmt = self::_stmt_bind( $stmt, $ar_param );
            $row = self::_stmt_execute( $stmt, $output, true);
            return $row;
        } catch (PDOException $e) {
            if(PDO::inTransaction()){
                throw $e;
            } else {
                self::display_error($e);
                return false;
            }
        }
    }
    
    // 複数の行データを取得
    public function sql_prepare_results($sql, $ar_param, $output= PDO::FETCH_OBJ) {
        try {
            $stmt = $this->prepare( $sql );
            $stmt = self::_stmt_bind( $stmt, $ar_param );
            return self::_stmt_execute( $stmt, $output);
        } catch (PDOException $e) {
            if(PDO::inTransaction()){
                throw $e;
            } else {
                self::display_error($e);
                return false;
            }
        }
    }

    //SQL prepare バインド
    static function _stmt_bind($stmt, $ar_param) {
        $n = 1;
        foreach ($ar_param as $val){
            if(is_string($val)){
                $stmt->bindValue($n, $val, PDO::PARAM_STR);
            }
            else if(is_null($val)){
                $stmt->bindValue($n, NULL, PDO::PARAM_NULL);
            }
            else if(is_bool($val)){
                $stmt->bindValue($n, $val, PDO::PARAM_BOOL);
            }
            else if(is_int($val)){
                $stmt->bindValue($n, (int)$val, PDO::PARAM_INT);
            }
            else if(is_float($val)){
                //PDOでは実数型指定ができないがSQLiteはREAL型として扱うようキャストしておく
                $stmt->bindValue($n, (float)$val);
            }
            else {
                //ラージオブジェクト型(バイナリデータ)
                $stmt->bindValue($n, $val, PDO::PARAM_LOB);
            }
            $n++;
        }
        return $stmt;
    }
    //SQL prepare 実行
    static function _stmt_execute($stmt, $output = null, $row = false) {
        $res = $stmt->execute();
        if($res === false)
            return false;
        if($output === null || $res === false)
            return $res;
        else {
            if($output === PDO::FETCH_ASSOC || $output === PDO::FETCH_NUM ){
                $results = ($row)? $stmt->fetch( $output ) : $stmt->fetchAll( $output );
            }
            else {
                $stmt->setFetchMode(PDO::FETCH_CLASS, 'stdClass');
                $results = ($row)? $stmt->fetch() : $stmt->fetchAll();
            }
            return $results;
        }
    }

    /**
     * テーブルの存在確認
     * 
     * @return boolean
     */
    public function is_table_exist($table) {
        //sqlite_master から作成済みのテーブル構造を取得
        $res = $this->sql_get_row("SELECT * FROM sqlite_master WHERE type = 'table' AND name = '$table'");
        if($res === false)
            return false;
        return true;
    }
        
}

これだけでもかなりコードが見やすく簡潔に書けるので自分では気に入っています (^^)

興味ある方は、使いやすいように自由に改造して使って下さい

セキュリティ対策

データベースでは、SQLを実行する時に不正なSQLが実行されないように対策 する必要があります。特に Web環境からのアクセスに対しては、外部から入力されたデータを使ってSQL文を生成して実行する場合が多いので、必ず、SQLインジェクション対策を 行い不正なアクセスを防止しなければなりません

PDOでは、プリペアードメソッド prepare でプレースホルダを使い、バインドでデータ型を指定することで最低限のSQLインジェクション対策を行うことが出来ます

WordPress なら、さらに、nonce を併用してより強固な不正アクセス対策を行いましょうhttp://tokkono.cute.coocan.jp/blog/slow/index.php/wordpress/secured-ajax-with-wp-nonce/

以降のPDOのサンプルコードはこの拡張クラス Ext_PDO を使った記述となります

次のページに続きます (^^)


まとめ記事紹介

search star user home refresh tag chevron-left chevron-right exclamation-triangle calendar comment folder thumb-tack navicon angle-double-up angle-double-down angle-up angle-down quote-left googleplus facebook instagram twitter rss