使用FDW与Pleasanter和外部数据进行协作

1 年 ago

逸, 科

2 minutes

FDW是什么意思。

有一个用于访问外部数据的PostgreSQL扩展插件，可以访问各种不同的外部数据。

SNS（Facebook、Twitterなど）

参考: 让我们一起在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

预先准备

不会提及各个服务器的操作系统和关系型数据库管理系统的安装。

服务器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）。

下面的命令可用于安装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 用户，我将访问数据库（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扩展

通过这个“拓展SQL”功能，您可以获取和更新无法使用标准功能进行操作的数据。本次我们将执行通过API创建的拓展SQL语句以获取外部数据。
参考：开发者功能：拓展功能：拓展SQL
开发者功能：拓展功能：拓展SQL：通过API执行拓展SQL语句

请准备以下两个文件，并将其放置在服务器A的”/web/pleasanter/Implem.Pleasanter/App_Data/Parameters/ExtendedSqls”目录中。

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

select * from testtable;

配置完后，将重新启动Pleasanter。

$ sudo systemctl restart pleasanter

获取数据

通过API执行扩展SQL。

请求

url

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"
                }
            ]
        }
    }
}