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

## 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情報ポータルサイト](https://lets.postgresql.jp/documents/technical/fdw)

## 環境情報
今回使用する環境は以下の通り。サーバ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 ユーザマニュアル セットアップ](https://pleasanter.org/manual/getting-started-pleasanter-ubuntu)
### サーバB
下記コマンドで参照するデータベース(testdb)を作成します。
```bash
$ psql -U postgres
postgres=# create database "testdb";
```
テーブル(users)を作成します。
```sql
testdb=# create table users (id integer, name varchar(10));
```
参照するデータを追加します。
```sql
testdb=# insert into public.users values (1, 'yamada');
testdb=# insert into public.users values (2, 'nakano');
```

## FDWインストール
サーバAにログインし、データベース(Implem.Pleasanter)に接続します。
```bash
$ su - postgres
$ psql -U postgres "Implem.Pleasanter"
```
下記コマンドでFDW(postgres_fdw)をインストールします。
```sql
Implem.Pleasanter=# create extension postgres_fdw;
```

## 外部テーブル接続設定
外部サーバの定義を行います。
```sql
Implem.Pleasanter=# create server ext_server foreign data wrapper postgres_fdw options (host 'xxx.xxx.xxx.xxx', port '5432', dbname 'testdb');
```
ユーザマッピングの定義を行います。
```sql
Implem.Pleasanter=# create user mapping for "Implem.Pleasanter_User" server ext_server options(user 'postgres', password 'xxxxx');
```
外部サーバの定義を利用するユーザに対しアクセス権限を付与します。完了したら終了(\q)します。

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

"Implem.Pleasanter_User"ユーザで、データベース(Implem.Pleasanter)にアクセスします。
```bash
$ psql -h localhost -U "Implem.Pleasanter_User" "Implem.Pleasanter"
```
外部サーバのテーブル定義を行います。
```sql
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 行)
```

外部テーブルの情報を確認します。
```sql
Implem.Pleasanter=> select * from testtable;
 id |  name
----+--------
  1 | yamada
  2 | nakano
(2 行)
```

## Pleasanter経由で外部データを取得する
### 拡張SQL
Pleasanterには標準機能では行えないデータの取得や更新をこの「拡張SQL」機能で行うことが可能になります。今回はAPIから作成した拡張SQLを実行して外部データを取得します。
参考：[開発者向け機能：拡張機能：拡張SQL](https://pleasanter.org/manual/extended-sql)
　　　[開発者向け機能：拡張機能：拡張SQL：APIから拡張SQLを実行する](https://pleasanter.org/manual/extended-sql-api)

以下の2ファイルを準備し、サーバAの「/web/pleasanter/Implem.Pleasanter/App_Data/Parameters/ExtendedSqls」ディレクトリに配置します。
```SampleExtendedSql.json
{
    "Name": "SampleExtendedSql",
    "Api": true
}
```
```SampleExtendedSql.json.sql
select * from testtable;
```
配置後、Pleasanterを再起動します。
```bash
$ sudo systemctl restart pleasanter
```

### データ取得
API経由で拡張SQLを実行します。
#### リクエスト
- url
```
http://xxx.xxx.xxx.xxx/api/extended/sql
```
- Body
```json
{
    "ApiVersion": 1.1,
    "ApiKey": "xxxxx...",
    "Name": "SampleExtendedSql"
}
```
#### レスポンス

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