LoginSignup
0

More than 3 years have passed since last update.

SQLite+PDOを使いやすくするクラス

Last updated at Posted at 2019-08-09

はじめに

PHPのPDOを使ってSQLiteデータベースにアクセスするクラスを作った。

割り切った仕様のため、主キーの名前をidに限定しています。

new

DBオブジェクトの作成時に、「DBファイルのパス」と「テーブル名」を渡します。
この$dbを使ってデータベースを操作していきます。

$db = new DB('./example.db', 'fruit'); //パスとテーブル名を渡す
  • ファイルが存在しない時は、自動的に作成されます

table

現在のテーブルを変更します。戻り値はDBオブジェクトです。

$db->table('fruit');

table_create

テーブルを作成します。
テーブルの情報は['列名'=>'型 制約']という連想配列で渡します。

$db->table_create([
    'id'    => 'integer primary key autoincrement',
    'name'  => 'text(10) not null',
    'price' => 'integer default 100',
];

SQLiteで設定できる型

説明
text 文字列
integer 整数
real 小数点
numeric 数字
none 何も変換しない

SQLiteで設定できる制約

制約 説明
primary key 主キーにする
autoincrement 自動採番する
not null 値がない場合は、エラーになる
default 値 値がない場合は、指定した値になる
unique 値が重複した場合は、エラーになる
check(式) 値が式の条件を満たさない場合は、エラーになる
check(式)の例
//priceが「100以上1000以下」でないとエラーにする
'price'=>'integer check(price >= 100 and price <= 1000)'

//式で使える主要な記号はPHPと同じです
< > <= >= == != and or not

table_keys

現在のテーブルの列名一覧を配列で返します。

$db->table_keys();

insert

レコードを1件追加します。
戻り値は、追加されたid番号です。

$db->insert(['name'=>'バナナ', 'price'=>100]); //['列名'=>値]の連想配列で渡す

update

レコードを1件更新します。

$db->update(id番号, ['price'=>120]); //id番号は数字。更新内容は連想配列で

delete

レコードを1件削除します。

$db->delete(id番号); //id番号は数字

select

データを取得します。引数により3パターンあります。

1セル取得
$result = $db->select(id番号, '列名'); //id番号は数字。列名は文字列

//戻り値は値
print $result;
1行取得
$result = $db->select(id番号); //id番号は数字

//戻り値は連想配列
print $result['name'];
print $result['price'];
複数行取得
$result = $db->select(開始位置, 取得件数); //開始位置は数字で0から始まる。取得件数は数字

//戻り値は2重配列
foreach($result as $fruit){
    print $fruit['name'];
    print $fruit['price'];
}

複数行取得では、idの大きい順に選択されます。逆順にしたい場合は第三引数にtrueを渡してください

search

文字列検索をします。戻り値は2重配列です。

$result = $db->search('検索ワード', '対象列名', 開始位置, 取得件数);

//検索ワードは空白で区切るとOR検索
//対象列が複数ある場合は配列に入れる
//開始位置は数字。0から。idの大きい順に検索されます
//取得件数は数字

foreach($result as $fruit){
    print $fruit['name'];
    print $fruit['price'];
}

query

任意のSQL文を実行します。
戻り値はPDOStatementオブジェクトです。

$result = $db->query('select * from fruit');

foreach($result as $fruit){
    print $fruit['name'];
    print $fruit['price'];
}

準備文を使うこともできます。第二引数に対応する配列を渡して下さい

//プレースホルダに「?」を使う
$db->query('select * from fruit where name = ?', ['みかん']);

//プレースホルダに「:」を使う
$db->query('select * from fruit where name = :name', ['name'=>'みかん']); //キー名の:は省略可

count

現在のテーブルの全レコード数を数字で返します。

$db->count();

transaction

トランザクションはコールバック関数を渡す形で行います。
失敗したら関数内で例外を投げてください。ロールバックされます。

戻り値は「コールバック関数の戻り値」をそのまま返します。

$db->transaction(function($db){
    //ここにトランザクションが必要な処理を記述する
    //失敗したら例外を投げてください
});

コールバック関数にはDBオブジェクトが渡されます。
コールバック関数に追加の引数を渡したい場合は、transaction()の第二引数以降に渡してください。

参考情報:PHP callable

データをオブジェクトで取得する

当クラスでの取得データは連想配列となりますが、指定クラスのオブジェクトにすることもできます。

テーブル名を指定する所を、クラス名にすると実現できます。
テーブル名と同名のクラスが存在し、クラス名をフルパスで渡すのが条件です。

class fruit{
   //あらかじめfruitクラスを用意しておく
}

$db = new DB('example.db', '\\fruit'); //クラス名をフルパスで渡す

foreach($db->select(0,10) as $fruit){
    print $fruit->name;
    print $fruit->price;
}

テーブルに関するデータと操作を1つのクラスにまとめることができます。

セキュリティ情報

  • テーブル名と列名はノーチェックなので、気を付けてご利用ください
  • DBファイルはネットから見えない所に配置しておきましょう

ソースコード

ソースコードはクラスが1つだけです。
ご自由にお使いください

class DB{
    public  $pdo;
    private $table;
    private $table_class;


    function __construct(string $file, string $table){
        $this->pdo = new \PDO("sqlite:$file", null, null, [
            \PDO::ATTR_ERRMODE=> \PDO::ERRMODE_EXCEPTION,
            \PDO::ATTR_DEFAULT_FETCH_MODE=> \PDO::FETCH_ASSOC,
        ]);
        $this->table($table);
    }


    function table($table){
        if(strpos($table, '\\') === 0){
            $this->table = basename($table);
            $this->table_class = $table;
        }
        else{
            $this->table = $table;
            $this->table_class = "";
        }
        return $this;
    }


    function table_create(array $data){
        foreach($data as $k => $v){
            $sql_create[] = sprintf('"%s" %s', $k, $v);
        }
        $sql = sprintf('create table if not exists "%s" (%s)', $this->table, implode($sql_create, ','));
        $this->query($sql);
    }


    function table_keys(){
        $sql = sprintf('pragma table_info ("%s")', $this->table);
        return array_column($this->query($sql)->fetchAll(), 'name');
    }


    function insert(array $data){
        foreach(array_keys($data) as $v){
            $sql_keys[]   = sprintf('"%s"', $v);
            $sql_holder[] = '?';
        }

        $sql = sprintf('insert into "%s" (%s) values (%s)', $this->table, implode($sql_keys, ','), implode($sql_holder, ','));
        $this->query($sql, array_values($data));

        return $this->pdo->lastInsertId();
    }


    function update(int $id, array $data){
        foreach($data as $k => $v){
            $sql_set[] = sprintf('"%s" = ?', $k);
        }
        $sql = sprintf('update "%s" set %s where id = %s', $this->table, implode($sql_set, ','), $id);
        $this->query($sql, array_values($data));
    }


    function delete(int $id){
        $sql = sprintf('delete from "%s" where id = %s', $this->table, $id);
        $this->query($sql);
    }


    function select(int $start, $length = 0, bool $reverse = false){
        if(is_string($length)){
            $sql = sprintf('select "%s" from "%s" where id = %s', $length, $this->table, $start);
            return $this->query($sql)->fetchColumn();
        }
        else if($length <= 1){
            $sql = sprintf('select * from "%s" where id = %s', $this->table, $start);
            return $this->query($sql)->fetch();
        }
        else{
            $order = ($reverse) ? 'asc' : 'desc';
            $sql = sprintf('select * from "%s" order by id %s limit %s offset %s', $this->table, $order, $length, $start);
            return $this->query($sql)->fetchAll();
        }
    }


    function search(string $word, $key, int $start, int $length){
        $words = preg_split('/[[:space:] ]+/u', $word);
        $words = array_filter($words, 'strlen');

        foreach((array)$key as $v){
            $keys[] = sprintf('"%s"', $v);
        }

        foreach($words as $v){
            $bind[]     = sprintf('%%%s%%', addcslashes($v, '\\_%'));
            $sql_like[] = sprintf('((%s) like ?)', implode($keys, '||'));
        }

        $sql = sprintf('select * from "%s" where %s order by id desc limit %s offset %s', $this->table, implode($sql_like, ' or '), $length, $start);
        return $this->query($sql, $bind)->fetchAll();
    }


    function query(string $sql, array $bind = []){
        if($bind){
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($bind);
        }
        else{
            $stmt = $this->pdo->query($sql);
        }

        if($this->table_class){
            $stmt->setFetchMode(\PDO::FETCH_CLASS, $this->table_class);
        }
        return $stmt;
    }


    function count(){
        $sql = sprintf('select count (*) from "%s"', $this->table);
        return $this->query($sql)->fetchColumn();
    }


    function transaction(callable $func, ...$args){
        try{
            $this->pdo->beginTransaction();
            $result = $func($this, ...$args);
            $this->pdo->commit();
            return $result;
        }
        catch(Exception $e){
            $this->pdo->rollBack();
            throw $e;
        }
    }
}

参考リンク

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0