PleasanterのデータベースにYugabyteDBが使えるかトライしてみた！

## Pleasanterとは
技術的な知識がなくても、簡単に業務アプリを作成できるソフトウェアです。オープンソースで提供しており、無料で使用可能です。詳しくは以下のページをご確認ください。

https://pleasanter.org/

ソースコードはこちらからご確認ください。

https://github.com/Implem/Implem.Pleasanter


## YugabyteDBとは
PostgreSQL および Cassandra と互換性を持ち、一貫性、拡張性、高可用性を兼ね備えたクラウドネイティブな分散型 SQL データベースとのことです。

https://www.sraoss.co.jp/tech-blog/yugabytedb/yugabytedb-intro/

## Let's Try!!
以下の流れでPleasanterが起動できるかトライしてみます。

1. YugabyteDBのコンテナを作成する
1. データベースを作成する（CodeDefinerの実行）
1. Pleasanterをデバッグ起動する

### 1. YugabyteDBのコンテナを作成する
下記ページの **2. Dockerでの YugabyteDB クラスタ作成** を参考にYugabyteDBのコンテナを作成します。

https://yugabytedb-japan.github.io/codelabs/getting-started-yugabyted/index.html?index=..%2F..index#1

コンテナが起動していることを確認します。
```cmd
> docker ps
CONTAINER ID   IMAGE                 COMMAND                  CREATED        STATUS         PORTS                                                                                                                                                                                 NAMES
4aced61c0b8c   yugabytedb/yugabyte   "/sbin/tini -- bin/y…"   18 hours ago   Up 2 minutes   6379/tcp, 7100/tcp, 7200/tcp, 9042/tcp, 9100/tcp, 10100/tcp, 11000/tcp, 12000/tcp, 0.0.0.0:5435->5433/tcp, 0.0.0.0:7003->7000/tcp, 0.0.0.0:9103->9000/tcp, 0.0.0.0:15435->15433/tcp   yugabyte-n3
0f5e37fd8662   yugabytedb/yugabyte   "/sbin/tini -- bin/y…"   18 hours ago   Up 2 minutes   6379/tcp, 7100/tcp, 7200/tcp, 9042/tcp, 9100/tcp, 10100/tcp, 11000/tcp, 12000/tcp, 0.0.0.0:5434->5433/tcp, 0.0.0.0:7002->7000/tcp, 0.0.0.0:9102->9000/tcp, 0.0.0.0:15434->15433/tcp   yugabyte-n2
01e73ee87bbc   yugabytedb/yugabyte   "/sbin/tini -- bin/y…"   18 hours ago   Up 2 minutes   6379/tcp, 7100/tcp, 7200/tcp, 9042/tcp, 9100/tcp, 10100/tcp, 11000/tcp, 0.0.0.0:5433->5433/tcp, 0.0.0.0:15433->15433/tcp, 12000/tcp, 0.0.0.0:7001->7000/tcp, 0.0.0.0:9101->9000/tcp   yugabyte-n1
```

ブラウザで http://localhost:15433/ にアクセスして、3つのコンテナで構成されたクラスタを確認します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/481749/0ee3b5cb-c978-40ac-86e2-069a98cce385.png)

下記ページを参考に接続してみます。

https://docs.yugabyte.com/preview/tutorials/quick-start/docker/#connect-to-the-database

```
> docker exec -it yugabyte-n1 bash -c '/home/yugabyte/bin/ysqlsh --echo-queries --host $(hostname)'
ysqlsh (15.12-YB-2.25.2.0-b0)
Type "help" for help.

yugabyte=# 
```

PostgreSQLのpsqlのようにメタコマンドもあるようです。

https://docs.yugabyte.com/preview/api/ysqlsh-meta-commands/

データベースの一覧を確認してみます。
```
yugabyte=# \l
                                                  List of databases
      Name       |  Owner   | Encoding | Collate |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------------+----------+----------+---------+-------------+------------+-----------------+-----------------------
 postgres        | postgres | UTF8     | C       | en_US.UTF-8 |            | libc            |
 system_platform | postgres | UTF8     | C       | en_US.UTF-8 |            | libc            |
 template0       | postgres | UTF8     | C       | en_US.UTF-8 |            | libc            | =c/postgres          +
                 |          |          |         |             |            |                 | postgres=CTc/postgres
 template1       | postgres | UTF8     | C       | en_US.UTF-8 |            | libc            | =c/postgres          +
                 |          |          |         |             |            |                 | postgres=CTc/postgres
 yugabyte        | postgres | UTF8     | C       | en_US.UTF-8 |            | libc            |
(5 rows)

yugabyte=#
```

### 2. データベースを作成する（CodeDefinerの実行）
ソースコードをクローンします。
```cmd
> git clone https://github.com/Implem/Implem.Pleasanter.git
```

Visual Studioでソリューションファイル（Implem.Pleasanter/Implem.Pleasanter.sln）を開きます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/481749/1d0f8794-9704-4955-be51-f06571786695.png)

以下のパラメータファイルを編集します。
- Implem.Pleasanter\Implem.Pleasanter\App_Data\Parameters\Rds.json
    ```
    {
        "Dbms": "PostgreSQL",
        "Provider": "Local",
        "SaConnectionString": "Server=localhost;Port=5433;Database=yugabyte;UID=yugabyte",
        "OwnerConnectionString": "Server=localhost;Port=5433;Database=#ServiceName#;UID=#ServiceName#_Owner;PWD=SetAdminsPWD",
        "UserConnectionString": "Server=localhost;Port=5433;Database=#ServiceName#;UID=#ServiceName#_User;PWD=SetUsersPWD",
        "SqlCommandTimeOut": 0,
        "MinimumTime": 3,
        "DeadlockRetryCount": 4,
        "DeadlockRetryInterval": 1000,
        "DisableIndexChangeDetection": true,
        "SysLogsSchemaVersion": 1
    }
    ```

CodeDefinerの実行時のオプションを編集します。以下ファイルの profiles > Implem.CodeDefiner > commandLineArgs の値を **_rds** にします。
- Implem.Pleasanter\Implem.CodeDefiner\Properties\launchSettings.json
    ```
    {
      "profiles": {
        "Implem.CodeDefiner": {
          "commandName": "Project",
          "commandLineArgs": "_rds"
        },
        "Implem.CodeDefiner_rds": {
           "commandName": "Project",
           "commandLineArgs": "_rds"
        },
        "WSL": {
          "commandName": "WSL2",
          "distributionName": ""
        },
        "Docker": {
          "commandName": "Docker",
          "commandLineArgs": "_rds"
        }
      }
    }
    ```

ソリューションエクスプローラーで Implem.CodeDefiner プロジェクトを右クリックし、**スタートアッププロジェクトに設定** をクリックします。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/481749/6b846e59-14cf-4b6a-ae26-ac612c1e991d.png)

以下の **Implem.CodeDefiner** をクリックして、データベース(Implem.Pleasanter)を作成します。※F5キーを押下でも可

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/481749/bb3f352f-b05c-4550-8ae1-7856f1dc32d8.png)

コマンドプロンプトで以下のように **SUCESS** が出力されれば、データベース(Implem.Pleasanter)の作成が完了です。

```
<INFO> Starter.Main: Implem.CodeDefiner 1.4.16.3
<INFO> RdsConfigurator.UpdateDatabase: Implem.Pleasanter
<INFO> UsersConfigurator.Execute: Implem.Pleasanter_Owner
<INFO> UsersConfigurator.Execute: Implem.Pleasanter_User
<INFO> SchemaConfigurator.Configure: Implem.Pleasanter
<INFO> Configurator.OutputLicenseInfo:
ServerName: localhost
Database: yugabyte
Deadline: 0001/01/01
Licensee:
Users: 0
<INFO> Configurator.OutputLicenseInfo: This edition is "Community Edition".
Type "y" (yes) if the license is correct, otherwise type "n" (no).
y
<INFO> TablesConfigurator.ConfigureTableSet: AutoNumberings
<INFO> TablesConfigurator.ConfigureTableSet: Binaries
<INFO> TablesConfigurator.ConfigureTableSet: Demos
<INFO> TablesConfigurator.ConfigureTableSet: Depts
<INFO> Tables.CreateTable: Depts
<INFO> Tables.CreateTable: Depts_deleted
<INFO> Tables.CreateTable: Depts_history
<INFO> TablesConfigurator.ConfigureTableSet: Extensions
<INFO> Tables.CreateTable: Extensions
<INFO> Tables.CreateTable: Extensions_deleted
<INFO> Tables.CreateTable: Extensions_history
<INFO> TablesConfigurator.ConfigureTableSet: GroupChildren
<INFO> Tables.CreateTable: GroupChildren
<INFO> Tables.CreateTable: GroupChildren_deleted
<INFO> Tables.CreateTable: GroupChildren_history
<INFO> TablesConfigurator.ConfigureTableSet: GroupMembers
<INFO> Tables.CreateTable: GroupMembers
<INFO> Tables.CreateTable: GroupMembers_deleted
<INFO> Tables.CreateTable: GroupMembers_history
<INFO> TablesConfigurator.ConfigureTableSet: Groups
<INFO> Tables.CreateTable: Groups
<INFO> Tables.CreateTable: Groups_deleted
<INFO> Tables.CreateTable: Groups_history
<INFO> TablesConfigurator.ConfigureTableSet: Items
<INFO> Tables.CreateTable: Items
<INFO> Tables.CreateTable: Items_deleted
<INFO> Tables.CreateTable: Items_history
<INFO> TablesConfigurator.ConfigureTableSet: Links
<INFO> Tables.CreateTable: Links
<INFO> Tables.CreateTable: Links_deleted
<INFO> Tables.CreateTable: Links_history
<INFO> TablesConfigurator.ConfigureTableSet: LoginKeys
<INFO> Tables.CreateTable: LoginKeys
<INFO> Tables.CreateTable: LoginKeys_deleted
<INFO> Tables.CreateTable: LoginKeys_history
<INFO> TablesConfigurator.ConfigureTableSet: MailAddresses
<INFO> Tables.CreateTable: MailAddresses
<INFO> Tables.CreateTable: MailAddresses_deleted
<INFO> Tables.CreateTable: MailAddresses_history
<INFO> TablesConfigurator.ConfigureTableSet: Orders
<INFO> Tables.CreateTable: Orders
<INFO> Tables.CreateTable: Orders_deleted
<INFO> Tables.CreateTable: Orders_history
<INFO> TablesConfigurator.ConfigureTableSet: OutgoingMails
<INFO> Tables.CreateTable: OutgoingMails
<INFO> Tables.CreateTable: OutgoingMails_deleted
<INFO> Tables.CreateTable: OutgoingMails_history
<INFO> TablesConfigurator.ConfigureTableSet: Registrations
<INFO> Tables.CreateTable: Registrations
<INFO> Tables.CreateTable: Registrations_deleted
<INFO> Tables.CreateTable: Registrations_history
<INFO> TablesConfigurator.ConfigureTableSet: ReminderSchedules
<INFO> Tables.CreateTable: ReminderSchedules
<INFO> Tables.CreateTable: ReminderSchedules_deleted
<INFO> Tables.CreateTable: ReminderSchedules_history
<INFO> TablesConfigurator.ConfigureTableSet: Sessions
<INFO> Tables.CreateTable: Sessions
<INFO> Tables.CreateTable: Sessions_deleted
<INFO> Tables.CreateTable: Sessions_history
<INFO> TablesConfigurator.ConfigureTableSet: Sites
<INFO> Tables.CreateTable: Sites
<INFO> Tables.CreateTable: Sites_deleted
<INFO> Tables.CreateTable: Sites_history
<INFO> TablesConfigurator.ConfigureTableSet: Statuses
<INFO> Tables.CreateTable: Statuses
<INFO> Tables.CreateTable: Statuses_deleted
<INFO> Tables.CreateTable: Statuses_history
<INFO> TablesConfigurator.ConfigureTableSet: SysLogs
<INFO> Tables.CreateTable: SysLogs
<INFO> Tables.CreateTable: SysLogs_deleted
<INFO> Tables.CreateTable: SysLogs_history
<INFO> TablesConfigurator.ConfigureTableSet: Tenants
<INFO> Tables.CreateTable: Tenants
<INFO> Tables.CreateTable: Tenants_deleted
<INFO> Tables.CreateTable: Tenants_history
<INFO> TablesConfigurator.ConfigureTableSet: Users
<INFO> Tables.CreateTable: Users
<INFO> Tables.CreateTable: Users_deleted
<INFO> Tables.CreateTable: Users_history
<INFO> TablesConfigurator.ConfigureTableSet: Permissions
<INFO> Tables.CreateTable: Permissions
<INFO> Tables.CreateTable: Permissions_deleted
<INFO> Tables.CreateTable: Permissions_history
<INFO> TablesConfigurator.ConfigureTableSet: Dashboards
<INFO> Tables.CreateTable: Dashboards
<INFO> Tables.CreateTable: Dashboards_deleted
<INFO> Tables.CreateTable: Dashboards_history
<INFO> TablesConfigurator.ConfigureTableSet: Issues
<INFO> Tables.CreateTable: Issues
<INFO> Tables.CreateTable: Issues_deleted
<INFO> Tables.CreateTable: Issues_history
<INFO> TablesConfigurator.ConfigureTableSet: Results
<INFO> Tables.CreateTable: Results
<INFO> Tables.CreateTable: Results_deleted
<INFO> Tables.CreateTable: Results_history
<INFO> TablesConfigurator.ConfigureTableSet: Wikis
<INFO> Tables.CreateTable: Wikis
<INFO> Tables.CreateTable: Wikis_deleted
<INFO> Tables.CreateTable: Wikis_history
<INFO> PrivilegeConfigurator.Execute: Implem.Pleasanter_Owner
<INFO> PrivilegeConfigurator.Execute: Implem.Pleasanter_User
<SUCCESS> Starter.ConfigureDatabase: Database configuration has been completed.
<SUCCESS> Starter.Main: All of the processes have been completed.
```

ysqlshでデータベース(Implem.Pleasanter)が作成されていることを確認します。
```
yugabyte=# \l
                                                           List of databases
       Name        |          Owner          | Encoding | Collate |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-------------------+-------------------------+----------+---------+-------------+------------+-----------------+-----------------------
 Implem.Pleasanter | Implem.Pleasanter_Owner | UTF8     | C       | en_US.UTF-8 |            | libc            |
 postgres          | postgres                | UTF8     | C       | en_US.UTF-8 |            | libc            |
 system_platform   | postgres                | UTF8     | C       | en_US.UTF-8 |            | libc            |
 template0         | postgres                | UTF8     | C       | en_US.UTF-8 |            | libc            | =c/postgres          +
                   |                         |          |         |             |            |                 | postgres=CTc/postgres
 template1         | postgres                | UTF8     | C       | en_US.UTF-8 |            | libc            | =c/postgres          +
                   |                         |          |         |             |            |                 | postgres=CTc/postgres
 yugabyte          | postgres                | UTF8     | C       | en_US.UTF-8 |            | libc            |
(6 rows)

yugabyte=#
```

各テーブルも作成されていますね。
```
yugabyte=# \c "Implem.Pleasanter"
You are now connected to database "Implem.Pleasanter" as user "yugabyte".
Implem.Pleasanter=# set search_path to "Implem.Pleasanter", "$user", public;
set search_path to "Implem.Pleasanter", "$user", public;
SET
Implem.Pleasanter=# \dt
                                List of relations
      Schema       |           Name            | Type  |          Owner
-------------------+---------------------------+-------+-------------------------
 Implem.Pleasanter | AutoNumberings            | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | AutoNumberings_deleted    | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | AutoNumberings_history    | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Binaries                  | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Binaries_deleted          | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Binaries_history          | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Dashboards                | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Dashboards_deleted        | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Dashboards_history        | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Demos                     | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Demos_deleted             | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Demos_history             | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Depts                     | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Depts_deleted             | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Depts_history             | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Extensions                | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Extensions_deleted        | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Extensions_history        | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | GroupChildren             | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | GroupChildren_deleted     | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | GroupChildren_history     | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | GroupMembers              | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | GroupMembers_deleted      | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | GroupMembers_history      | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Groups                    | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Groups_deleted            | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Groups_history            | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Issues                    | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Issues_deleted            | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Issues_history            | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Items                     | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Items_deleted             | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Items_history             | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Links                     | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Links_deleted             | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Links_history             | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | LoginKeys                 | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | LoginKeys_deleted         | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | LoginKeys_history         | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | MailAddresses             | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | MailAddresses_deleted     | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | MailAddresses_history     | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Orders                    | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Orders_deleted            | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Orders_history            | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | OutgoingMails             | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | OutgoingMails_deleted     | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | OutgoingMails_history     | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Permissions               | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Permissions_deleted       | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Permissions_history       | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Registrations             | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Registrations_deleted     | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Registrations_history     | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | ReminderSchedules         | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | ReminderSchedules_deleted | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | ReminderSchedules_history | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Results                   | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Results_deleted           | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Results_history           | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Sessions                  | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Sessions_deleted          | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Sessions_history          | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Sites                     | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Sites_deleted             | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Sites_history             | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Statuses                  | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Statuses_deleted          | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Statuses_history          | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | SysLogs                   | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | SysLogs_deleted           | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | SysLogs_history           | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Tenants                   | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Tenants_deleted           | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Tenants_history           | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Users                     | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Users_deleted             | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Users_history             | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Wikis                     | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Wikis_deleted             | table | Implem.Pleasanter_Owner
 Implem.Pleasanter | Wikis_history             | table | Implem.Pleasanter_Owner
(81 rows)

Implem.Pleasanter=# 
```

ブラウザでも確認が出来るようです。画面左側の **Databases** から確認しました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/481749/cfc9dc38-99d3-4493-91db-ab352b5acb67.png)


### 3. Pleasanterをデバッグ起動する
ソリューションエクスプローラーで Implem.Pleasanter プロジェクトを右クリックし、**スタートアッププロジェクトに設定** をクリックします。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/481749/fa6c44c4-6701-4190-b057-e12025d0e732.png)

以下の **IIS Express** をクリックして、Pleasanterをデバッグ起動します。※F5キーを押下でも可

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/481749/ca5bd581-cfb2-4103-a5f5-f58a8626bc30.png)

ブラウザが立ち上がり、プリザンターのログイン画面が表示されることを確認しました！

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/481749/5b64f7f1-1171-4934-925d-3cf63e6b424c.png)

## さいごに
サイトやレコード作成等の基本的な操作も行うことができたので、YugabyteDBを用いた構成でもPleasanterを使用できるのではないでしょうか。（きちんと検証しないとですね。。。）
高い可用性、耐障害性を求められる場合ではYugabyteDBを活用するのも良いかもしれませんね。もう少しYugabyteDBについて知るためにも、色々といじってみたいと思います！