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

## はじめに

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

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

## new

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

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

## table

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

```php
$db->table('fruit');
```

## table_create

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

```php
$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(式)|値が式の条件を満たさない場合は、エラーになる|

```php:check(式)の例
//priceが「100以上1000以下」でないとエラーにする
'price'=>'integer check(price >= 100 and price <= 1000)'

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


## table_keys

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

```php
$db->table_keys();
```


## insert 

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

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

## update

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

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

## delete

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

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



## select

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


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

//戻り値は値
print $result;
```

```php:1行取得
$result = $db->select(id番号); //id番号は数字

//戻り値は連想配列
print $result['name'];
print $result['price'];
```

```php:複数行取得
$result = $db->select(開始位置, 取得件数); //開始位置は数字で0から始まる。取得件数は数字

//戻り値は2重配列
foreach($result as $fruit){
    print $fruit['name'];
    print $fruit['price'];
}
```
複数行取得では、idの大きい順に選択されます。逆順にしたい場合は第三引数に``true``を渡してください

## search

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

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

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

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


## query

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

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

foreach($result as $fruit){
    print $fruit['name'];
    print $fruit['price'];
}
```
準備文を使うこともできます。第二引数に対応する配列を渡して下さい

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

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

## count

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

```php
$db->count();
```

## transaction

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

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

```php
$db->transaction(function($db){
    //ここにトランザクションが必要な処理を記述する
    //失敗したら例外を投げてください
});
```
コールバック関数にはDBオブジェクトが渡されます。
コールバック関数に追加の引数を渡したい場合は、transaction()の第二引数以降に渡してください。

参考情報：<a href="https://www.php.net/manual/ja/language.types.callable.php" target="_blank">PHP callable</a>
## データをオブジェクトで取得する

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

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

```php
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つだけです。
ご自由にお使いください

```php
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;
        }
    }
}
```

## 参考リンク

- <a href="https://www.dbonline.jp/sqlite/" target="_blank">SQLite入門</a>
- <a href="https://qiita.com/mpyw/items/b00b72c5c95aac573b71" target="_blank">PHPでデータベースに接続するときのまとめ</a>