LoginSignup
5
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@myasuinfukuoka.ex：福岡Elixirコミュ

ElixirでPostgreSQLへ読み書きする

Last updated at Posted at 2020-05-14

1.はじめに

前の記事では、UDPデータの受信と、XMLのパーサまで試してみました。
今回は任意のデータを、PostgreSQLのテーブルにINSERT, SELECTする手順をまとめました。

（ECTO1を使うところまでは学習が追いつかなかったので、ひとまずPostgrex2を使うところまで）

最終的に、下記リンク先のツール「UECS通信実用規約に基づいたロギング用ソフトウェア」のElixir版を作るのを目標にしています。
ユビキタス環境制御システム(UECS)技術
ユビキタス環境制御システム「UECS」
(20/5/25)完成版：UecsListner

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

ここでは、下記の設定でデータベースを用意しました。
データベース uecsraspi
ユーザ uecs
パスワード hogefuga
テーブル Uecs_LRaw

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

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するだけの機能を作ります。

ソースファイル

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

mixファイルに、PostgrexとTimexのライブラリを追記します。
#↓ここから追記の所を書き加えます。

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ライブラリを利用して、データベースへの接続情報を別ファイルに管理しています。

コマンドライン
# pgのソースファイル
elixir/pgapp/lib/pgapp$ touch pguecs.ex
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

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

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

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

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

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

レコードのINSERTの実行

# ライブラリの依存関係の処理
$ 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から確認します。

$ 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

ソースファイル

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

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の実行

コマンドライン
# コンパイル・実行
$ 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

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

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

コマンドライン
# ユーザ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)

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

---データベースに入る
$ 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)

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

---アクセス権限を付加する前
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';

参考資料

  1. https://elixirschool.com/ja/lessons/ecto/basics/ ↩

  2. https://hexdocs.pm/postgrex/ ↩

  3. こちらの記事にUECSの定義を紹介しています。ここでは独自の拡張として「dataaval」という文字列型のフィールドを追加しています。（UECSの本来の定義でのdataはfloat型だけを扱う） ↩

5
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?