vaguely

和歌山に戻りました。ふらふらと色々なものに手を出す毎日。

Entity Framework Core < - > PostgreSQL 間の MAX/MIN 値のやりとり (数値データ編) 3

PostgreSQL Entity Framework Core

はじめに

さらっと終わるはずが、もう 3 回目。。。

まぁここは自分にすら役に立つかわからないことを興味の赴くままに調べていく場所。

今回もぼちぼち行きますよ。

trigger

テーブルに INSERT / UPDATE / DELETE が実行された時に特定の処理を実行する、 trigger を作ることができます。

処理自体は PL/pgSQL という言語を使います。

ここでは NumericValueText という text 型のカラムを追加し、 INSERT / UPDATE が実行された時に null か空白でなければ numeric に変換して NumericValue に入れる、という処理を作ってみたいと思います。

ALTER TABLE "NumberSample" ADD COLUMN "NumericValueText" text

手順としては、 function を作り、それを trigger として登録する、ということのようです。

CREATE OR REPLACE FUNCTION im_the_trigger() RETURNS TRIGGER AS $set_numericvalue$
    BEGIN
        IF ((TG_OP = 'INSERT' OR TG_OP = 'UPDATE') AND NEW."NumericValueText" is not null) THEN
            NEW."NumericValue" := CAST(NEW."NumericValueText" AS numeric);
        END IF;
        RETURN NEW;
    END;
$set_numericvalue$ LANGUAGE plpgsql;

大文字にしているからなのか、 IF ～ THEN のせいなのか、 VB っぽさを感じます。

注意点としては、最終行の 「$set_numericvalue$ LANGUAGE plpgsql;」。

これが無いと CREATE TRIGGER ～ の部分でエラーが発生します。

また trigger の名前で、 「$im_the_trigger$」のように設定しようとしたところ、「$」の部分でエラーとなりました。

調べてもよくわからなかったのですが、命名規則などがあるのでしょうか。

ともかく、 function が出来上がったらそれを元に trigger を作ります。

CREATE TRIGGER set_numericvalue BEFORE INSERT OR UPDATE ON "NumberSample"
FOR EACH ROW EXECUTE PROCEDURE im_the_trigger()

ここで trigger と対象となるテーブルの紐づけを行います。

ちなみに trigger を削除する場合は 「DROP TRIGGER set_numericvalue ON "NumberSample"」 のように紐づけたテーブル名も指定する必要があります。

これで INSERT 、 UPDATE 実行時に NumericValueText の値がキャストされて NumericValue に入ります。

どう使うか

今回のケースでは、 C# で頑張って書いていた BigInteger -> string -> 小数点を戻す -> SQL で numeric に変換 といった手順を省略するのに使えそうです。

ただ、あまり不用意に使いすぎると例えば別のアプリから NumericValue の値を直接更新した場合など、想定外の動きにつながることもあり、注意が必要そうです。

trigger については今回ここまでですが、後述の view に対して作ることもできる、ということもあったり、別途もう少し何ができるのかなど遊んでみたいと思います。

rule

DB に rule を設定しておくことで、 INSERT 、 UPDATE 、 DELETE 時に本来の操作の代替となる操作を実行することができる…そうです。
( ON SELECT で条件付きで SELECT に適用することも可能)

試しに INSERT で、 NumericValueText が null でなければキャストした値を NumericValue にセットするようなルールを作ってみます。

CREATE OR REPLACE RULE set_numericvalue_on_insert AS ON INSERT TO "NumberSample" 
WHERE NEW."NumericValueText" IS NOT null
DO UPDATE "NumberSample" SET "NumericValue" = CAST(NEW."NumericValueText" AS numeric)

最初 DO UPDATE ～ を DO INSERT としていたのですが、無限ループになってしまうためエラーが発生しました。

UPDATE についても同じように rule を作ることができます。

ON SELECT

SELECT * FROM "NumberSample" が実行された時に、 NumericValue を含まないような rule が作れるか？と思ってみたのですが、 DO INSTEAD SELECT ～ で指定できる SELECT では、全カラムを含んでいる必要があり、難しいようです。

これが必要な場合、次の view を使うのが良さそうです。

view

SELECT 文において、これまで WHERE によるフィルタリングや CAST による変換などを行った上でデータを取得していました。

これを毎回指定しなくても、物理的なテーブルを作成した時と同じように扱うことができるもの(と思っている) が view です。

例えば下記のように view を作ると…

CREATE VIEW "NumberSampleView" ("Id", "NumericValueText")
AS SELECT "Id", "NumericValueText" FROM "NumberSample"
WHERE "Id" % 2 = 0

下記を実行したときに、「 SELECT "Id", "NumericValueText" FROM "NumberSample" WHERE "Id" % 2 = 0 」と同じ結果が得られます。

SELECT * FROM "NumberSampleView"

なお CREATE VIEW の「("Id", "NumericValueText")」で NumberSampleView におけるカラム名を指定しているため(省略可)、特にテーブルを結合している場合など、必要に応じて指定するのが良さそうです。

また上記では使っていませんが、 INNER JOIN などによる結合、 CAST 、 COUNT などによる元のテーブルにはないカラムの追加を行うこともできます。

ただし、 ORDER BY は使えないとのこと。

あと、テーブルを作成したときと同じように、とは言うものの、実際のところは元の SELECT 文が実行されるので、元のテーブル( NumberSample )の最新のデータが結果として返る、ということと、 view を作っても実行速度が速くなるわけではないということは覚えておくべきかもしれません。

Entity Framework Core での扱い

この view ですが、 Entity Framework Core (以下 EF Core )ではどのように扱うのでしょうか。

。。。と思ったら、通常の物理テーブルと同様に扱うことができるようです。

NumberSampleView.cs

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace EfCoreDataTypeSample.Models
{
    [Table("NumberSampleView")]
    public class NumberSampleView
    {
        [Key]
        public int Id { get; set; }
        public string NumericValueText { get; set; }
    }
}

EfCoreDataTypeSampleContext.cs

    ～省略～
    public DbSet< NumberSampleView> NumberSampleViews { get; set; }
    }
}

HomeController.cs

public async Task< List< NumberSampleView>> Index()
{
    return await _context.NumberSampleViews.ToListAsync();
}

view の更新

さて、このようにテーブルと同じように扱える view ですが、 INSERT や UPDATE はできるのでしょうか。

答えは条件付きでできます、ということです。

PostgreSQL 9.3 から、複数テーブルを結合していないこと・関数を使っていないことなどの条件を満たす view であれば、特に trigger などを作らなくてもそのまま INSERT や UPDATE ができるようになったそうです。

そのため、このような処理を実行すると、 NumberSample にレコードが追加されます。

NumberSampleView newView = new NumberSampleView
{
        NumericValueText = "1234567"
};
_context.NumberSampleViews.Add(newView);
await _context.SaveChangesAsync();

※ NumericValueText 以外は serial などデフォルトで値が入るカラム以外は null になります。

view の作成条件によって更新できたりできなかったりというのは、規模が大きくなってくるとややこしくなりそうなので、どれも更新可能にしておくか、明示的に更新できる・できないがわかるように工夫する必要がありそうです。
( CREATE VIEW の時に information_schema を参照すると更新可能かわかる、とのことですが、特にアプリからだと見落としそうなので)

また、 view には再帰を使って結果を得る Recursive View やクエリの結果を実際のテーブルにキャッシュする Materialized View など種類があるので、これらも今後試してみたいところ。

money

だいぶ寄り道した感がありますが、一応今回ラストのデータ型、 money です。

名前の通りお金を扱うデータ型である、と。

取り得る範囲は -92233720368547758.08 ～ +92233720368547758.07 です。

UPDATE "NumberSample" SET "MoneyValue" = -92233720368547758.08 WHERE "Id" = 35;
UPDATE "NumberSample" SET "MoneyValue" = 92233720368547758.07 WHERE "Id" = 36;

0.08 や 0.07 など、中途半端な値に見えるのですが、これを超えてみるとどうなるのでしょうか。

小数点第4位以下を足してみる

-92233720368547758.081 のように、小数点第4位以下を足した場合。

小数点第4位以下 は切り捨てられ、特にエラーは発生しませんでした。

小数点第３位以上を足してみる

下記のエラーが発生しました。

ERROR: bigint out of range

bigint???

以前試した bigint の範囲 を見てみましょう。

-9,223,372,036,854,775,808 ～ 9,223,372,036,854,775,807

比較しやすいように、カンマ、ドットを省いて見てみます。

  • bigint: -9223372036854775808 ～ 9223372036854775807
  • money: -9223372036854775808 ～ 9223372036854775807

どうやら、 money 型は内部的には bigint で値を持っているようです。

なるほど～。

C# での扱い

小数点以下の値を持ち、正確に扱える、ということで decimal が良さそうです。

28 ～ 29 桁に収まりますし。

ただし、 decimal の方が扱うことのできる値の範囲が広いため、 C# 側から渡すときは注意が必要です。

NumberSample.cs

～省略～
namespace EfCoreDataTypeSample.Models
{
    [Table("NumberSample")]
    public class NumberSample
    {
        ～省略～
        private decimal _moneyValue;
        public decimal MoneyValue
        {
            get => _moneyValue;
            set
            {
                var moneyText = value.ToString(CultureInfo.CurrentCulture).Replace(".", "");
                if (long.TryParse(moneyText, out var longValue) == false)
                {
                    throw new ArgumentOutOfRangeException();
                }
                _moneyValue = value;
            }
        }   
    }
}

雑極まりない感じですが、まぁチェックを入れてあげると良いよねということで。

PostgreSQL の money 型としては、 DB のロケールに合わせて値の扱いも変わる、というのも気になるところですが、少なくとも C# に渡して扱う分には特に変わらないよね、ということでこんな扱いとなっております。

おわりに

特に numeric 型の扱いなど、やっぱりちゃんと調べておかないとなかなかツラい。。。

view など今回軽く触っただけのものも、別途しっかり調べて使えるようにしたいところ。

また、数値型以外の型も色々気になるので、これらも追って調べてみたいと思います。

が、次回は諸事情によりフロントエンドに移る予定。

多分。

参照

trigger

rule

view

Money