RESTでMySQLを操作する

php-crud-apiをインストールする


2014年ごろにはMySQL HTTP PluginというものがMySQL Labs版(MySQLの実験版)で公開されていて、
こ ちらに立派なドキュメントまで有るのですが、MySQLの公式版には採用されなかったみたいです。

MySQLをRESTで操作するツールとしてこれらのツールが公開されています。

https://www.npmjs.com/package/mysql-to-rest

https://github.com/mevdschee/php-crud-api

そこで、今回はphp-crud-apiを使ってみることにします。
php-crud-apiの実態はapi.phpで、このスクリプトはWEBサーバー上のアプリとして動き、
RESTで受け取ったリスクエストをSQLに変換して実行し、 SQLの結果をJSONで応答します。


 
このツールはMySQLだけでなく、PostgreとSQL-Serverにも対応していて、DBMSに関係なく同じI/FでDBを操作すること ができるのが特徴です。
 


使用するDBMSはsrc/index.phpの以下の部分で指定します。
デフォルトの設定は以下の様になっています。
$config = new Config([
    // 'driver' => 'mysql',
    // 'address' => 'localhost',
    // 'port' => '3306',
    'username' => 'php-crud-api',
    'password' => 'php-crud-api',
    'database' => 'php-crud-api',
    // 'debug' => false
]);

こ ちらにsqliteを使う場合の設定例とサンプルDBが公開されています。


最初にMySQLをインストールします。
mysqlのインストール手順はこちらを 参考にしました。

mysql_secure_installationでMySQLサーバのセキュリティ関連の設定を行います。
最初、password validation policyをmidiumで設定したのですが、どうにもパスワードが面倒で、
midium→lowに変更しました。
password validation policyの変更手順はこ ちらに詳しく公開されています。

インストールしたmysqlのバージョンは以下の通りです。

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.28, for Linux (x86_64) using  EditLine wrapper

この時点でルートユーザでログインできることを確認しておきます。

$ sudo mysql -u root -p


次にphp-crud-apiをインストールし、テスト用のDB、ユーザを構築します。
php-curd-api/tests/fixturesにはMySQLだけでなく、PostgresとSQL-Server用のスクリプトも準 備されています。
$ cd $HOME

$ git clone https://github.com/mevdschee/php-crud-api

$ cd php-crud-api/

$ sudo mysql -u root -p

テスト用DB(php-crud-api)とテスト用ユーザ(php-crud-api)の作成
mysql> source tests/fixtures/create_mysql.sql;

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| php-crud-api       |
| sys                |
+--------------------+
6 rows in set (0.00 sec)


mysql> select Host, User from mysql.user;
+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| localhost | debian-sys-maint |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | php-crud-api     |
| localhost | root             |
+-----------+------------------+
7 rows in set (0.00 sec)

mysql> exit


php-curd-apiのユーザー(パスワードはphp-crud-api)で再度MySQLにログインし、テスト用のテーブルを構築します。
$ mysql -u php-crud-api -h localhost --password=php-crud-api

mysql> use php-crud-api;
Database changed

mysql> select database();
+--------------+
| database()   |
+--------------+
| php-crud-api |
+--------------+
1 row in set (0.00 sec)


mysql> source tests/fixtures/blog_mysql.sql;

mysql> SHOW TABLES;
+------------------------+
| Tables_in_php-crud-api |
+------------------------+
| barcodes               |
| categories             |
| comments               |
| countries              |
| events                 |
| invisibles             |
| kunsthandvark          |
| nopk                   |
| post_tags              |
| posts                  |
| products               |
| tag_usage              |
| tags                   |
| users                  |
+------------------------+
14 rows in set (0.00 sec)

mysql> select * from posts;
+----+---------+-------------+--------------+
| id | user_id | category_id | content      |
+----+---------+-------------+--------------+
|  1 |       1 |           1 | blog started |
|  2 |       1 |           2 | It works!    |
+----+---------+-------------+--------------+
2 rows in set (0.00 sec)

mysql> exit


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

PDO driver for MySQLをインストール
$ sudo apt-get install php-mysql

$ 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

Built-in WEB Serverを起動
$ php -S 0.0.0.0:8080 -t $HOME/php-crud-api
PHP 7.2.24-0ubuntu0.18.04.1 Development Server started at Mon Dec 30 09:21:32 2019
Listening on http://0.0.0.0:8080
Document root is /home/nop/php-crud-api
Press Ctrl-C to quit.

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

読み込み
GET /api.php/records/テーブル名/{id}

データの追加
POST /api.php/records/テーブル名?{JSON形式のデータ}

データの更新
PUT /api.php/records/テーブル名/{Id}?{JSON形式のデータ}

データの削除
DELETE /api.php/records/テーブル名/{Id}

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

全件読み込み
$ curl "http://localhost:8080/api.php/records/posts/" | python -mjson.tool
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   134  100   134    0     0  44666      0 --:--:-- --:--:-- --:--:-- 44666
{
    "records": [
        {
            "id": 1,
            "user_id": 1,
            "category_id": 1,
            "content": "blog started"
        },
        {
            "id": 2,
            "user_id": 1,
            "category_id": 2,
            "content": "It works!"
        }
    ]
}

Primary Keyを指定してデータを読み込み
$ curl "http://localhost:8080/api.php/records/posts/1" | python -mjson.tool
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100    61  100    61    0     0   4066      0 --:--:-- --:--:-- --:--:--  4066
{
    "category_id": 1,
    "content": "blog started",
    "id": 1,
    "user_id": 1
}

$ curl "http://localhost:8080/api.php/records/posts/2" | python -mjson.tool
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100    58  100    58    0     0   3411      0 --:--:-- --:--:-- --:--:--  3411
{
    "category_id": 2,
    "content": "It works!",
    "id": 2,
    "user_id": 1
}

検索条件(filter)を指定してデータを読み込み
$ curl "http://localhost:8080/api.php/records/posts/?filter=category_id,eq,2" | python -mjson.tool
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100    72  100    72    0     0  12000      0 --:--:-- --:--:-- --:--:-- 12000
{
    "records": [
        {
            "category_id": 2,
            "content": "It works!",
            "id": 2,
            "user_id": 1
        }
    ]
}

複数のテーブルをJOINしてデータを読み込み
$ curl "http://192.168.10.43:8080/api.php/records/posts?join=comments,users&join=tags" | python -mjson.tool
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   594  100   594    0     0  23760      0 --:--:-- --:--:-- --:--:-- 23760
{
    "records": [
        {
            "category_id": 1,
            "comments": [
                {
                    "category_id": 3,
                    "id": 1,
                    "message": "great",
                    "post_id": 1
                },
                {
                    "category_id": 3,
                    "id": 2,
                    "message": "fantastic",
                    "post_id": 1
                }
            ],
            "content": "blog started",
            "id": 1,
            "tags": [
                {
                    "id": 1,
                    "is_important": false,
                    "name": "funny"
                },
                {
                    "id": 2,
                    "is_important": true,
                    "name": "important"
                }
            ],
            "user_id": 1
        },
        {
            "category_id": 2,
            "comments": [
                {
                    "category_id": 3,
                    "id": 3,
                    "message": "thank you",
                    "post_id": 2
                },
                {
                    "category_id": 3,
                    "id": 4,
                    "message": "awesome",
                    "post_id": 2
                }
            ],
            "content": "It works!",
            "id": 2,
            "tags": [
                {
                    "id": 1,
                    "is_important": false,
                    "name": "funny"
                },
                {
                    "id": 2,
                    "is_important": true,
                    "name": "important"
                }
            ],
            "user_id": 1
        }
    ]
}

Primary Keyを指定してデータを更新
$ curl "http://localhost:8080/api.php/records/posts/3" | python -mjson.tool
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100    60  100    60    0     0  10000      0 --:--:-- --:--:-- --:--:-- 10000
{
    "category_id": 3,
    "content": "Hello World",
    "id": 3,
    "user_id": 1
}

$ curl -X PUT -H "Content-Type: application/json" -d '{"content":"Hello Japan"}' http://localhost:8080/api.php/records/posts/3

$ curl "http://localhost:8080/api.php/records/posts/3" | python -mjson.tool
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100    60  100    60    0     0   4285      0 --:--:-- --:--:-- --:--:--  4285
{
    "category_id": 3,
    "content": "Hello Japan",
    "id": 3,
    "user_id": 1
}

Primary Keyを指定してデータを削除
$ curl "http://localhost:8080/api.php/records/posts/3" | python -mjson.tool
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100    60  100    60    0     0   4285      0 --:--:-- --:--:-- --:--:--  4285
{
    "category_id": 3,
    "content": "Hello Japan",
    "id": 3,
    "user_id": 1
}

$ curl -X DELETE http://localhost:8080/api.php/records/posts/3

$ curl "http://localhost:8080/api.php/records/posts/" | python -mjson.tool
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   134  100   134    0     0   7052      0 --:--:-- --:--:-- --:--:--  7052
{
    "records": [
        {
            "category_id": 1,
            "content": "blog started",
            "id": 1,
            "user_id": 1
        },
        {
            "category_id": 2,
            "content": "It works!",
            "id": 2,
            "user_id": 1
        }
    ]
}


Windowsで動くREST Clientがいくつか公開されています。
今回、こちらのClientを使ってみました。
GET/POST/PUT/DELETEを直感的に使うことができます。








続く．．．