PostgreSQLのStored Procedureでkintone連携

社内のデータが外出先から見られたら便利なことってありますよね。
（それをやって良いかどうかは別にして）TRYしてみました。
PostgreSQLに登録された情報をkintoneアプリに登録するサンプルです。
もちろん、更新や削除もいけますよ！

今回は[PostgreSQL](https://www.postgresql.jp/)のStored Procedureを使って[kintone](https://kintone.cybozu.com/jp/)を操作します。

※kintoneはクラウドで動作するデータベースサービスです。
　ドラッグ&ドロップでパーツを配置するだけで、データベースと入力フォームが出来上がります。
　REST APIでのデータ操作が可能です。



# 前提
- PostgreSQLのテーブルにserial型（unique）があること
- serial値は更新されないこと

# サンプルコードで動作するデータ構成
kintoneのフィールド名、フィールドタイプ、フィールドコード、PostgreSQLの列名、型は以下

| フィールド名 | フィールドタイプ | フィールドコード | 列名 | 型 |
|----------|----------------|------------------|----------------|----------------|
| PostgreSQLの管理番号 | 数値 | serial_id | serial_id | serial |
| 会社名 | 文字列1行 | company_name | company_name | varchar |
| 担当 | ユーザー選択 | account_executive | account_executive | varchar |
| 訪問予定日 | 日付 | next_visit_day | next_visit_day | date |


# PostgreSQLの環境構築

PostgreSQLのインストール
`$ sudo yum install postgresql-server postgresql-plpython python-requests`

PostgreSQLの起動
`$ sudo postgresql-setup initdb`
`$ sudo systemctl start postgresql`

データベースの作成
`$ su - postgres`
`$ createdb withkintone`

テーブルの作成
`$ psql withkintone`
`withkintone=# create table customer_manage (serial_id serial, company_name varchar(100), account_executive varchar(50), next_visit_day date);`

PL/Pythonの有効化
（本当はkintoneのカスタマイズと同じJavaScriptでやりたかったのですが、今はHTTPリクエストが不可なので）
`withkintone=# create extension plpythonu;`


# kintoneの環境構築
前提にあるとおりフォームを作成

APIトークンを発行（アプリの設定完了を押し忘れないように！）
![token.png](https://qiita-image-store.s3.amazonaws.com/0/102790/e3dabbfc-7e71-8840-4098-015a178f2c95.png)

# サンプルコード
```sql:sample.sql
CREATE OR REPLACE FUNCTION relay_to_kintone() RETURNS TRIGGER AS $$

import urllib
import requests
import json

# 連携するアプリID
app = 1983

# 連携するドメイン
domain = 'xxxx.cybozu.com'

# 連携するアプリのAPIトークン
token = 'xxxx'

# PostgreSQLのserial列名
relay_column = 'serial_id'



class Kintone:
    # initialize
    def __init__(self, app, domain, token, relay_column):
        self.app = app
        self.domain = domain
        self.token = token
        self.relay_column = relay_column

    # 更新、削除のためにkintoneのレコード番号を取得
    def get_record_id(self):
        url = 'https://' + self.domain + '/k/v1/records.json'

        headers = {
            'X-Cybozu-API-Token': self.token,
            'Content-Type': 'application/json'
        }

        data = {
            'app': self.app,
            'query': self.relay_column + ' = "' + str(TD['old'][self.relay_column]) + '"',
            'fields': ['$id'],
            'totalCount': 'true'
        }

        res = requests.get(url, headers=headers, data=json.dumps(data))

        total_count = res.json()['totalCount']
        if total_count == '1':
            return res.json()['records'][0]['$id']['value']
        else:
            message = self.relay_column + ': ' + str(TD['old'][self.relay_column])
            message += 'が' + str(total_count) + '件あります'
            plpy.fatal(message)

    # レコードの登録
    def insert_record(self):
        url = 'https://' + self.domain + '/k/v1/record.json'

        headers = {
            'X-Cybozu-API-Token': self.token,
            'Content-Type': 'application/json'
        }

        # 列名やフィールドタイプなどが異なる場合は要書き換え
        data = {
            'app': app,
            'record': {
                self.relay_column: { 'value': TD['new'][self.relay_column] },
                'company_name': { 'value': TD['new']['company_name'] },
                'account_executive': { 'value': [{'code': TD['new']['account_executive']}]},
                'next_visit_day': { 'value': TD['new']['next_visit_day'] }
            }
        }

        res = requests.post(url, headers=headers, data=json.dumps(data))

        message = self.relay_column + ': ' + str(TD['new'][self.relay_column])
        if res.status_code == 200:
            message += 'を登録しました'
            plpy.info(message)
        else:
            message += 'の登録に失敗しました'
            plpy.error(message)

    # レコードの更新
    def update_record(self, update_id):
        url = 'https://' + self.domain + '/k/v1/record.json'

        headers = {
            'X-Cybozu-API-Token': self.token,
            'Content-Type': 'application/json'
        }


        # 列名やフィールドタイプなどが異なる場合は要書き換え
        data = {
            'app': self.app,
            'id': update_id,
            'record': {
                'company_name': { 'value': TD['new']['company_name'] },
                'account_executive': { 'value': [{'code': TD['new']['account_executive']}]},
                'next_visit_day': { 'value': TD['new']['next_visit_day'] }
            }
        }

        res = requests.put(url, headers=headers, data=json.dumps(data))

        message = self.relay_column + ': ' + str(TD['new'][self.relay_column])
        if res.status_code == 200:
            message += 'を更新しました'
            plpy.info(message)
        else:
            message += 'の更新に失敗しました'
            plpy.error(message)

    # レコードの削除
    def delete_record(self, delete_id):
        url = 'https://' + domain + '/k/v1/records.json'

        headers = {
            'X-Cybozu-API-Token': self.token,
            'Content-Type': 'application/json'
        }

        data = {
            'app': self.app,
            'ids': [delete_id]
        }

        res = requests.delete(url, headers=headers, data=json.dumps(data))

        message = self.relay_column + ': ' + str(TD['old'][self.relay_column])
        if res.status_code == 200:
            message += 'を削除しました'
            plpy.info(message)
        else:
            message += 'の削除に失敗しました'
            plpy.error(message)



kintone = Kintone(app, domain, token, relay_column)

if TD['event'] == 'INSERT':
    kintone.insert_record()
elif TD['event'] == 'UPDATE':
    update_id = kintone.get_record_id()
    kintone.update_record(update_id)
elif TD['event'] == 'DELETE':
    delete_id = kintone.get_record_id()
    kintone.delete_record(delete_id)

return 'OK'

$$ LANGUAGE plpythonu;
```

# サンプルコードの読み込み
`withkintone=# \i sample.sql`

# トリガーの作成
`withkintone=# CREATE TRIGGER kintone_trigger AFTER INSERT OR UPDATE OR DELETE ON customer_manage FOR EACH ROW EXECUTE PROCEDURE relay_to_kintone();`

# 動作確認
## 1件登録
どきどき・・・
`withkintone=# insert into customer_manage (company_name, account_executive, next_visit_day) values ('○○株式会社', 'momota', '2016-01-01');`

上手く行きました。
![insert1.png](https://qiita-image-store.s3.amazonaws.com/0/102790/35caed30-0c0e-ec94-a3b3-0255fcb400f5.png)


## 2件登録
`withkintone=# insert into customer_manage (company_name, account_executive, next_visit_day) values ('××株式会社', 'momota', '2016-02-01'), ('△△株式会社', 'tamai', '2016-03-01');`

いえあ！
![insert2.png](https://qiita-image-store.s3.amazonaws.com/0/102790/18552ca5-7fd5-2f68-7cf7-a322b5b5bb2d.png)


## 2件更新
では調子に乗って2件更新してみます。
`withkintone=# update customer_manage set account_executive = 'hayami' where next_visit_day in ('2016-02-01', '2016-03-01');`

ひゅ～ひゅ～！
![update1.png](https://qiita-image-store.s3.amazonaws.com/0/102790/bc482317-3dda-9db8-f3bf-dbaa1a2b3b6a.png)


## 2件削除
同じ調子で2件削除してみます。
`withkintone=# delete from customer_manage where account_executive = 'hayami';`

どんどんぱふぱふ！！！
![delete1.png](https://qiita-image-store.s3.amazonaws.com/0/102790/950b3f7a-7dc5-4055-03c9-b530e6ba3d69.png)


# まとめ
これで社内の情報をこっそり社外で見られますね！（違
kintoneはグラフ機能もすぐれてますので、kintoneで見える化するのもお勧めです。

# kintone開発者ライセンス
kintone面白そうだな～と思った方は5ユーザー、1年間無償の[開発者ライセンス](https://cybozudev.zendesk.com/hc/ja/)を使ってみてください。