FDWでPleasanterと外部データを連携する #PostgreSQL

FDWとは

外部にあるデータにアクセスするためのPostgreSQLの拡張機能で、様々な外部データにアクセスできるようです。

PostgreSQL
PostgreSQL以外のRDB（Oracle、SQL Server、MySQLなど）
NoSQLデータベース（MongoDB、Redis、Cassandraなど）
ファイル（CSVファイル、プレーンテキストなど）
地理空間情報（PostGISなど）
LDAP
ビッグデータ（Apache Hadoop、BigQueryなど）
SNS（Facebook、Twitterなど）

参考：Let's POSTGRES PostgreSQL情報ポータルサイト

環境情報

今回使用する環境は以下の通り。サーバA(Pleasanterがインストールされている環境)からサーバB(外部データ)の情報を取得します。

サーバA (Pleasanterインストール環境)
- OS: Ubuntu 22.04 LTS
- RDBMS: PostgreSQL 14.8
  - User: postgres
  - Schema: public
  - DB
- Pleasanter: 1.3.37.1
サーバB (外部データ)
- OS: Rocky Linux release 8.5
- RDBMS: PostgreSQL 14.6
  - User: postgres
  - Schema: public
  - DB: testdb

事前準備

各サーバのOS、RDBMSのインストールについては割愛します。

サーバA

ユーザマニュアルを参考にPleasanterをインストールします。
参考：Pleasanter ユーザマニュアルセットアップ

サーバB

下記コマンドで参照するデータベース(testdb)を作成します。

$ psql -U postgres
postgres=# create database "testdb";

テーブル(users)を作成します。

testdb=# create table users (id integer, name varchar(10));

参照するデータを追加します。

testdb=# insert into public.users values (1, 'yamada');
testdb=# insert into public.users values (2, 'nakano');

FDWインストール

サーバAにログインし、データベース(Implem.Pleasanter)に接続します。

$ su - postgres
$ psql -U postgres "Implem.Pleasanter"

下記コマンドでFDW(postgres_fdw)をインストールします。

Implem.Pleasanter=# create extension postgres_fdw;

外部テーブル接続設定

外部サーバの定義を行います。

Implem.Pleasanter=# create server ext_server foreign data wrapper postgres_fdw options (host 'xxx.xxx.xxx.xxx', port '5432', dbname 'testdb');

ユーザマッピングの定義を行います。

Implem.Pleasanter=# create user mapping for "Implem.Pleasanter_User" server ext_server options(user 'postgres', password 'xxxxx');

外部サーバの定義を利用するユーザに対しアクセス権限を付与します。完了したら終了(\q)します。

Implem.Pleasanter=# grant all on foreign server ext_server to "Implem.Pleasanter_User";

"Implem.Pleasanter_User"ユーザで、データベース(Implem.Pleasanter)にアクセスします。

$ psql -h localhost -U "Implem.Pleasanter_User" "Implem.Pleasanter"

外部サーバのテーブル定義を行います。

Implem.Pleasanter=> import foreign schema public limit to (testtable) from server ext_server into public;

外部テーブルがあることを確認します。

Implem.Pleasanter=> \d
                                    リレーション一覧
 スキーマ |                名前                |    タイプ    |         所有者
----------+------------------------------------+--------------+-------------------------
(略)
 public   | Wikis                              | テーブル     | Implem.Pleasanter_Owner
 public   | Wikis_deleted                      | テーブル     | Implem.Pleasanter_Owner
 public   | Wikis_history                      | テーブル     | Implem.Pleasanter_Owner
 public   | testtable                          | 外部テーブル | Implem.Pleasanter_User
(92 行)

外部テーブルの情報を確認します。

Implem.Pleasanter=> select * from testtable;
 id |  name
----+--------
  1 | yamada
  2 | nakano
(2 行)

Pleasanter経由で外部データを取得する

拡張SQL

Pleasanterには標準機能では行えないデータの取得や更新をこの「拡張SQL」機能で行うことが可能になります。今回はAPIから作成した拡張SQLを実行して外部データを取得します。
参考：開発者向け機能：拡張機能：拡張SQL
　　　開発者向け機能：拡張機能：拡張SQL：APIから拡張SQLを実行する

以下の2ファイルを準備し、サーバAの「/web/pleasanter/Implem.Pleasanter/App_Data/Parameters/ExtendedSqls」ディレクトリに配置します。

SampleExtendedSql.json

{
    "Name": "SampleExtendedSql",
    "Api": true
}

SampleExtendedSql.json.sql

select * from testtable;

配置後、Pleasanterを再起動します。

$ sudo systemctl restart pleasanter

データ取得

API経由で拡張SQLを実行します。

リクエスト

http://xxx.xxx.xxx.xxx/api/extended/sql

Body

{
    "ApiVersion": 1.1,
    "ApiKey": "xxxxx...",
    "Name": "SampleExtendedSql"
}

レスポンス

{
    "StatusCode": 200,
    "Response": {
        "Data": {
            "Table": [
                {
                    "id": 1,
                    "name": "yamada"
                },
                {
                    "id": 2,
                    "name": "nakano"
                }
            ]
        }
    }
}