RESTでSqliteを操作する


Sqliteは簡単に使えるDBですがクラサバではないので、ネットワーク超しに使うことができません。
こちらにSQLite、MySQL、 PostgreSQLデータベース用のRESTfulAPIが公開されています。
これを使えば、ネットワーク超しにSqliteを使うことができます。


最初に以下のスクリプトでサンプルのデータベース(example.db)を作成します。
データの更新や削除を行う際にprimary keyが必要になります。
Primary Keyの無いテーブルは更新や削除を行うことができません。
#!/bin/bash
#set -x

if [ ! -x $(which sqlite3) ];then
  echo "sqlite3 not found."
  exit -1
fi

cd $(dirname $0)
dbfile=$1
if [ $# -ne 1 ];then
        dbfile="example.db"
        echo "Create ${dbfile}"
fi


# Create Database
if [ ! -e ${dbfile} ];then
  echo ".open ${dbfile}" | sqlite3
  echo "build database"
else
  echo "database already exists"
fi

option="-noheader -separator ,"
sqlite="sqlite3 ${option} ${dbfile} "

# Create Table
${sqlite} "create table if not exists customers( \
id integer primary key autoincrement, \
name varchar(255), \
gender int);"

# Insert Data
${sqlite} "insert into customers(name,gender) values(\"Luis\", 1);"
${sqlite} "insert into customers(name,gender) values(\"Leonie\", 1);"
${sqlite} "insert into customers(name,gender) values(\"Francois\", 2);"
${sqlite} "insert into customers(name,gender) values(\"Bjorn\", 2);"

${sqlite} "select * from customers;"

以下の手順でArrestDBをインストールし、index.phpにデータベースファイルを設定します。
$ git close https://github.com/alixaxel/ArrestDB

$ cd ArrestDB

$ vi index.php

以下の行にデータベースのフルパスを設定します

$dsn = '';

例えばデータベースが//home/nop/example.db の場合、以下の様になります。

$dsn = 'sqlite:///home/nop/example.db';

本来、このツールはNginxやApacheなどのWEB Serverに組み込んで使うのですが、今回はお手軽にphpのBuilt-in WEB Serverを使うことにします。
phpとPDO driver for Sqlite3をインストールし、phpのBuilt-in WEB Serverを起動します。
PDO driver for Sqlite3のインストールはこ ちらに詳しく紹介されています。
リモートマシンからもアクセスできる様に、アドレスは0.0.0.0として起動します。
$ sudo apt install php

$ php --version
PHP 7.2.24-0ubuntu0.18.04.1 (cli) (built: Oct 28 2019 12:07:07) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.2.0, Copyright (c) 1998-2018 Zend Technologies
    with Zend OPcache v7.2.24-0ubuntu0.18.04.1, Copyright (c) 1999-2018, by Zend Technologies

PDO driver for Sqlite3をインストール
$ sudo apt install php7.2-sqlite3

Built-in WEB Serverを起動
$ php -S 0.0.0.0:8080 -t $HOME/ArrestDB
PHP 7.2.24-0ubuntu0.18.04.7 Development Server started at Thu Oct 29 08:21:25 2020
Listening on http://0.0.0.0:8080
Document root is /home/nop/ArrestDB
Press Ctrl-C to quit.


こちらにAPIの詳しい使い方が公 開されていますが、基本的なAPIは以下の４つです

読み込み
GET

データの追加
POST

データの更新
PUT

データの削除
DELETE

そこで、curlを使ってそれぞれのAPIを確認してみました。

全件読み込み
$ curl http://192.168.10.43:8080/customers/
[
    {
        "id": "1",
        "name": "Luis",
        "gender": "1"
    },
    {
        "id": "2",
        "name": "Leonie",
        "gender": "1"
    },
    {
        "id": "3",
        "name": "Francois",
        "gender": "2"
    },
    {
        "id": "4",
        "name": "Bjorn",
        "gender": "2"
    }
]

Primary Keyを指定してデータを読み込み
$ curl http://192.168.10.43:8080/customers/3
{
    "id": "3",
    "name": "Francois",
    "gender": "2"
}

id=3のデータを読み込み
応答データがリストになります。
$ curl http://192.168.10.43:8080/customers/id/3
[
    {
        "id": "3",
        "name": "Francois",
        "gender": "2"
    }
]

gender=2のデータを読み込み
こちらも応答データがリストになります。
$ curl http://192.168.10.43:8080/customers/gender/2
[
    {
        "id": "3",
        "name": "Francois",
        "gender": "2"
    },
    {
        "id": "4",
        "name": "Bjorn",
        "gender": "2"
    }
]

先頭の１件だけを読み込み
こちらも応答データがリストになります。
$ curl "http://192.168.10.43:8080/customers/?limit=1&by=id&orde=asc"
[
    {
        "id": "1",
        "name": "Luis",
        "gender": "1"
    }
]

末尾の１件だけを読み込み
こちらも応答データがリストになります。
$ curl "http://192.168.10.43:8080/customers/?limit=1&by=id&orde=desc"
[
    {
        "id": "26",
        "name": "Tom",
        "gender": "1"
    }
]

データを追加
idはautoincrementになっているので指定しません。
$ curl -X POST -H "Content-Type: application/json" -d '{"name":"Tom", "gender":"2"}' http://192.168.10.43:8080/customers/
{
    "success": {
        "code": 201,
        "status": "Created"
    }
}

Primary Keyを指定してデータを更新
$ curl http://192.168.10.43:8080/customers/3
{
    "id": "3",
    "name": "Francois",
    "gender": "2"
}


$ curl -X PUT -H "Content-Type: application/json" -d '{"name":"Petty"}' http://192.168.10.43:8080/customers/3
{
    "success": {
        "code": 200,
        "status": "OK"
    }
}


$ curl http://192.168.10.43:8080/customers/3
{
    "id": "3",
    "name": "Petty",
    "gender": "2"
}

Primary Keyを指定してデータを削除
$ curl -X DELETE http://192.168.10.43:8080/customers/3
{
    "success": {
        "code": 200,
        "status": "OK"
    }
}


$ curl http://192.168.10.43:8080/customers/id/3
{
    "error": {
        "code": 204,
        "status": "No Content"
    }
}

以下のリクエストはエラーとなります。
$ curl -X DELETE http://192.168.10.43:8080/customers/id/3
{
    "error": {
        "code": 400,
        "status": "Bad Request"
    }
}

これでRaspberryPiやESP32から、ネットワーク超しにsqliteを使うことができるようになります。

MySQLやPostgreSQLを使う場合、こちらで 紹介しているphp-crud-apiもRESTfulAPIを提供しています。
php-crud-apiの方が検索時のfilter(絞り込み)機能やJOIN機能が豊富です。