ElixirでPostgreSQLへ読み書きする

#1.はじめに

[前の記事](https://qiita.com/myasu/items/446d0e96141cf4211165)では、UDPデータの受信と、XMLのパーサまで試してみました。
今回は任意のデータを、PostgreSQLのテーブルにINSERT, SELECTする手順をまとめました。

（ECTO[^1]を使うところまでは学習が追いつかなかったので、ひとまずPostgrex[^2]を使うところまで）

[^1]: https://elixirschool.com/ja/lessons/ecto/basics/
[^2]: https://hexdocs.pm/postgrex/

> 最終的に、下記リンク先のツール「UECS通信実用規約に基づいたロギング用ソフトウェア」のElixir版を作るのを目標にしています。
[ユビキタス環境制御システム(UECS)技術](https://www.naro.affrc.go.jp/laboratory/nivfs/contents/kenkyu_joho/uecs/index.html)
[ユビキタス環境制御システム「UECS」](https://uecs.jp/)
(20/5/25)完成版：[UecsListner](https://github.com/trihome/UecsListner)

#2.練習データベースの構造

ここでは、下記の設定でデータベースを用意しました。

|||
|:--|:--|
|データベース|uecsraspi|
|ユーザ|uecs|
|パスワード|hogefuga|
|テーブル|Uecs_LRaw|

UECSのデータ形式を保存できるテーブル構造[^3]にします。

[^3]: [こちらの記事](https://qiita.com/myasu/items/446d0e96141cf4211165#appendixuecs%E3%81%AE%E3%83%87%E3%83%BC%E3%82%BF%E5%BD%A2%E5%BC%8F)にUECSの定義を紹介しています。ここでは独自の拡張として「dataaval」という文字列型のフィールドを追加しています。（UECSの本来の定義でのdataはfloat型だけを扱う）

```sql:
uecsraspi=# \d Uecs_LRaw;
                                     Table "public.uecs_lraw"
    Column    |            Type             | Collation | Nullable |           Default            
--------------+-----------------------------+-----------+----------+------------------------------
 eqlogid      | integer                     |           | not null | generated always as identity
 clientip     | character varying(45)       |           | not null | '0.0.0.0'::character varying
 dataval      | numeric(9,3)                |           |          | 
 datatype     | character varying(20)       |           |          | 
 dataroom     | smallint                    |           | not null | 0
 dataregion   | smallint                    |           | not null | 0
 dataorder    | integer                     |           | not null | 0
 datapriority | smallint                    |           | not null | 30
 dataaval     | character varying(255)      |           |          | 
 triggerdate  | timestamp without time zone |           | not null | 
 proctime     | integer                     |           | not null | 0
Indexes:
    "uecs_lraw_pkey" PRIMARY KEY, btree (eqlogid)
```

#3.レコードのINSERT

まず最初に、INSERTするだけの機能を作ります。

###ソースファイル

```sh:コマンドライン
$ cd ~/gitwork/elixir
#プロジェクトをつくる（スーパーバイザも有効に）
elixir$ mix new pgapp --sup
elixir$ cd pgapp/lib/pgapp/
```

mixファイルに、Postgrexと[Timex](https://hexdocs.pm/timex/)のライブラリを追記します。
`#↓ここから追記`の所を書き加えます。

```elixir:udpapp/mix.exs
defmodule Udpapp.MixProject do
  use Mix.Project

（･･･省略･･･）

  # Run "mix help deps" to learn about dependencies.
  defp deps do
    [
      # {:dep_from_hexpm, "~> 0.3.0"},
      # {:dep_from_git, git: "https://github.com/elixir-lang/my_dep.git", tag: "0.1.0"}
      #↓ここから追記
      {:timex, "~> 3.5"},
      {:postgrex, "~> 0.15"}
    ]
  end
end
```

PostgreSQLへの読み書きをする機能を実装します。
ここでは、Configライブラリを利用して、データベースへの接続情報を別ファイルに管理しています。

```sh:コマンドライン
#pgのソースファイル
elixir/pgapp/lib/pgapp$ touch pguecs.ex
```

```elixir:pgapp/lib/pgapp/pguecs.ex
defmodule Pgapp.Pguecs do
  @moduledoc """
  Documentation for `Pguecs`.
  """

  use GenServer
  require Logger
  use Timex

  def start_link(pidpg) do
    # application.exのworkerから起動したときに、この関数が実行されます
    Logger.info("* #{__MODULE__}: start_link call")

    # Postgrexを起動
    # ここでは、接続情報をconfig.exファイルから読み込むようにした
    {:ok, pidpg} =
      Postgrex.start_link(
        database: Application.fetch_env!(:pgapp, :database),
        username: Application.fetch_env!(:pgapp, :username),
        password: Application.fetch_env!(:pgapp, :password),
        hostname: Application.fetch_env!(:pgapp, :hostname)
      )

    # GenServerを起動します
    GenServer.start_link(__MODULE__, pidpg, name: __MODULE__)
  end

  def init(pidpg) do
    # GenServerが起動したときにこの初期化関数が実行されます
    Logger.debug("* #{__MODULE__}: init")
    {:ok, pidpg}
  end

  def table_insert() do
    # クライアント側API・ヘルパー関数 table_insert/0
    # ダミーデータのマップを用意。:datetimeは実行時の時刻の文字列を都度代入
    dummydata = %{
      data: ['22.12'],
      ip: ['192.168.99.101'],
      order: ['0'],
      priority: ['29'],
      region: ['101'],
      room: ['1'],
      type: ['InAirTemp'],
      datetime: ['#{getdatetime()}']
    }

    # 後述のtable_insert/1を実行
    table_insert(dummydata)
  end

  def table_insert(arg) do
    # クライアント側API・ヘルパー関数 table_insert/1
    # 引数のマップに指定したデータをレコードに追加
    GenServer.call(
      __MODULE__,
      {:table_insert, arg[:ip], arg[:data], arg[:type], arg[:room], arg[:region], arg[:order],
       arg[:priority], arg[:datetime]}
    )

    # 戻り値
    arg
  end

  def handle_call(
    # GenServer API / INSERTの実行
        {:table_insert, ip, data, type, room, region, order, priority, triggerdate},
        _from,
        pidpg
      ) do

    # 問い合わせ
    # SQL分を書きます
    result = Postgrex.query!(pidpg, "
      INSERT INTO #{Application.fetch_env!(:pgapp, :uecslogtable)}
        (ClientIp, DataVal, DataType, DataRoom, DataRegion, DataOrder, DataPriority, TriggerDate )
      VALUES
        ('#{ip}', #{data}, '#{type}', #{room}, #{region}, #{order}, #{priority}, '#{triggerdate}')
    ", [])

    #　結果の表示
    Logger.debug("* #{__MODULE__}: handle_call > #{inspect(result)}")
    {:reply, result, pidpg}
  end

  def getdatetime() do
    #現在時刻を取得し文字列化
    Timex.local()
    |> Timex.format!("{YYYY}/{M}/{D} {h24}:{m}:{s}")
  end

end
```

スーパーバイザを実装します。
このファイルは、プロジェクトを生成したときに自動的に作られるので、`#↓ここを追記`の所を書き加えます。

```elixir:udpapp/lib/udpapp/application.ex
defmodule Pgapp.Application do
  # See https://hexdocs.pm/elixir/Application.html
  # for more information on OTP Applications
  @moduledoc false

  use Application

  def start(_type, _args) do
    children = [
      # Starts a worker by calling: Pgapp.Worker.start_link(arg)
      # {Pgapp.Worker, arg}
      #↓ここを追記
      {Pgapp.Pguecs, 0}
    ]

    # See https://hexdocs.pm/elixir/Supervisor.html
    # for other strategies and supported options
    opts = [strategy: :one_for_one, name: Pgapp.Supervisor]
    Supervisor.start_link(children, opts)
  end
end
```

設定ファイルを実装します。

```sh:コマンドライン
elixir/pgapp$ mkdir config
elixir/pgapp$ cd config
#拡張子はexsです！
elixir/pgapp/config$ touch config.exs
```

```elixir:pgapp/config/config.exs
import Config

# アプリの各種設定
config :pgapp,
  # データベースの接続情報
  database: "uecsraspi",
  username: "uecs",
  password: "hogefuga",
  hostname: "localhost",
  # 書き込み先テーブル
  uecslogtable: "uecs_lraw"
```

###レコードのINSERTの実行

```sh
#ライブラリの依存関係の処理
$ mix deps.get

#コンパイル・実行
$ iex -S mix
Erlang/OTP 22 [erts-10.6.4] [source] [64-bit] [smp:4:4] [ds:4:4:10] [async-threads:1]
23:23:43.930 [info]  * Elixir.Pgapp.Pguecs: start_link call
Interactive Elixir (1.9.1) - press Ctrl+C to exit (type h() ENTER for help)

#ダミーデータを登録・1件目
iex(1)>  Pgapp.Pguecs.table_insert()
%{
  data: ['22.12'],
  datetime: ['2020/5/14 23:16:36'],
  ip: ['192.168.99.101'], 
  order: ['0'],
  priority: ['29'],
  region: ['101'],
  room: ['1'],
  type: ['InAirTemp']
}

#ダミーデータを登録・2件目
iex(2)> Pgapp.Pguecs.table_insert()
%{
  data: ['22.12'],
  datetime: ['2020/5/14 23:16:38'],
  ip: ['192.168.99.101'], 
  order: ['0'],
  priority: ['29'],
  region: ['101'],
  room: ['1'],
  type: ['InAirTemp']
}

iex(3)> [Ctrl-\]
$
```

PostgreSQLのテーブルに入ったかどうかを、直接psqlから確認します。

```sql
$ sudo su - postgres
$ psql uecsraspi

uecsraspi=# select * from uecs_lraw;

 eqlogid |    clientip    | dataval | datatype  | dataroom | dataregion | dataorder | datapriority | dataaval |     triggerdate     | proctime 
---------+----------------+---------+-----------+----------+------------+-----------+--------------+----------+---------------------+----------
       1 | 192.168.99.101 |  22.120 | InAirTemp |        1 |        101 |         0 |           29 |          | 2020-05-14 23:16:36 |        0
       2 | 192.168.99.101 |  22.120 | InAirTemp |        1 |        101 |         0 |           29 |          | 2020-05-14 23:16:38 |        0
(2 rows)
# ↑２件のレコードを確認できました。

$ exit
```



#4.レコードのSELECT

###ソースファイル

以下の２つの関数を追加します。

```elixir:pgapp/lib/pgapp/pguecs.ex
（・・・省略・・・）

  def table_query() do
    # クライアント側API・ヘルパー関数 table_query/0
    GenServer.call(__MODULE__, :table_query)
  end

  def handle_call(:table_query, _from, pidpg) do
    # GenServer API / SELECTの実行

    # 問い合わせ
    # SQL分を書きます。
    result = Postgrex.query!(pidpg, "
      SELECT
        *
      FROM
        #{Application.fetch_env!(:pgapp, :uecslogtable)}
      WHERE
        triggerdate >= to_timestamp(to_char(current_date, 'YYYY-MM-DD'), 'YYYY-MM-DD hh:mm:ss')
      ORDER BY
        triggerdate desc
      LIMIT
        20
      ;
    ", [])
    # 戻り値
    {:reply, result, pidpg}
  end

```

###レコードのSELECTの実行

```sh:コマンドライン
#コンパイル・実行
$ iex -S mix
Erlang/OTP 22 [erts-10.6.4] [source] [64-bit] [smp:4:4] [ds:4:4:10] [async-threads:1]
00:11:59.845 [info]  * Elixir.Pgapp.Pguecs: start_link call
Interactive Elixir (1.9.1) - press Ctrl+C to exit (type h() ENTER for help)

#問い合わせ
iex(1)> Pgapp.Pguecs.table_query()
%Postgrex.Result{
  columns: ["eqlogid", "clientip", "dataval", "datatype", "dataroom",
   "dataregion", "dataorder", "datapriority", "dataaval", "triggerdate",
   "proctime"],
  command: :select,
  connection_id: 6519,
  messages: [],
  num_rows: 2,
  rows: [
    [2, "192.168.99.101", #Decimal<22.120>, "InAirTemp", 1, 101, 0, 29, nil,
     ~N[2020-05-14 23:16:38.000000], 0],
    [1, "192.168.99.101", #Decimal<22.120>, "InAirTemp", 1, 101, 0, 29, nil,
     ~N[2020-05-14 23:16:36.000000], 0]
  ]
}
# ↑２件のレコードを確認できました。

iex(2)> [Ctrl-\]
$
```

ここまで、データベースへのINSERTとSELECT試してみました。

次回はUDPで流れているUECSのデータ収集からデータベースへの保存までを試してみます。
(20/5/25追記)　できました：[UecsListner](https://github.com/trihome/UecsListner)

#Appendix.練習用テーブルを作る

普段PostgreSQLを使ってないので、覚え書きとして手順を残します。

```sh:コマンドライン
#ユーザpostgresにsu
$ sudo su - postgres
#postgresにユーザuecsをつくる
$ createuser uecs
#データベースuecsraspiを作る。オーナーはユーザーuecsとする。
$ createdb uecsraspi -O uecs
#データベースの一覧を表示
$ psql -l
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges   
-----------+----------+----------+---------+---------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 uecsraspi | uecs     | UTF8     | C.UTF-8 | C.UTF-8 | 
(4 rows)
```

新たにテーブルを作ります。

```sql
---データベースに入る
$ psql uecsraspi

---テーブルを作る
uecsraspi=# create table Uecs_LRaw(
  EqLogID int GENERATED ALWAYS AS IDENTITY  not null,
  ClientIp varchar(45) default '0.0.0.0' not null,
  DataVal decimal(9, 3),
  DataType varchar(20),
  DataRoom smallint default 0 not null,
  DataRegion smallint default 0 not null,
  DataOrder int default 0 not null,
  DataPriority smallint default 30 not null,
  DataAVal varchar(255),
  TriggerDate TIMESTAMP not null,
  ProcTime int default 0 not null,
  primary key (EqLogID)
  );
CREATE TABLE

---テーブルが出来たか確認
uecssmsv=# \dt;
           List of relations
 Schema |   Name    | Type  |  Owner   
--------+-----------+-------+----------
 public | uecs_lraw | table | postgres
(1 rows)

---テーブルの構造を確認
uecsraspi=# \d Uecs_LRaw;
                                     Table "public.uecs_lraw"
    Column    |            Type             | Collation | Nullable |           Default            
--------------+-----------------------------+-----------+----------+------------------------------
 eqlogid      | integer                     |           | not null | generated always as identity
 clientip     | character varying(45)       |           | not null | '0.0.0.0'::character varying
 dataval      | numeric(9,3)                |           |          | 
 datatype     | character varying(20)       |           |          | 
 dataroom     | smallint                    |           | not null | 0
 dataregion   | smallint                    |           | not null | 0
 dataorder    | integer                     |           | not null | 0
 datapriority | smallint                    |           | not null | 30
 dataaval     | character varying(255)      |           |          | 
 triggerdate  | timestamp without time zone |           | not null | 
 proctime     | integer                     |           | not null | 0
Indexes:
    "uecs_lraw_pkey" PRIMARY KEY, btree (eqlogid)
```

各テーブルに対するアクセス権限の付加、およびユーザのパスワードの設定

```sql
---アクセス権限を付加する前
uecssmsv=# SELECT * FROM information_schema.role_table_grants WHERE grantee = 'uecs';
 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
(0 rows)

---アクセス権限を付加
uecsraspi=# GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO uecs;

---アクセス権限を付加した後
uecsraspi=# SELECT * FROM information_schema.role_table_grants WHERE grantee = 'uecs';
 grantor  | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
----------+---------+---------------+--------------+------------+----------------+--------------+----------------
 postgres | uecs    | uecsraspi     | public       | uecs_lraw  | INSERT         | NO           | NO
 postgres | uecs    | uecsraspi     | public       | uecs_lraw  | SELECT         | NO           | YES
 postgres | uecs    | uecsraspi     | public       | uecs_lraw  | UPDATE         | NO           | NO
 postgres | uecs    | uecsraspi     | public       | uecs_lraw  | DELETE         | NO           | NO
(4 rows)

---パスワードの設定
uecsraspi=# CREATE ROLE uecs LOGIN PASSWORD 'hogefuga';
---（パスワードの変更）
uecsraspi=# ALTER ROLE uecs LOGIN PASSWORD 'fugahoge';
```


#参考資料