PostgreSQLのソフト削除カスケード🐘 とyugabytedb🚀

これはソフトの削除についてのこの簡単な例です.

ケリー・サマーズ
ケラビテ

興味深いRDBMS機能は、柔らかい削除をカスケード化するでしょう.
午前2時10分- 2022年4月15日
私はどのデータベースケリーSommersが使用するかを知りません、しかし、PostgreSQLは多くの特徴を持っています、結合して、宣言的な方法でこのデータ処理論理を実行するのを手伝ってください.私は人々がデータベースにコードを置くのが好きでないということを知っています、しかし、これはビジネスロジックでありません.これは純粋なデータロジックです.SQLはこのための大きな利点を持っています:それは宣言言語です.あなたは一度それを宣言し、それをテストし、あなたが行われます.追加のコードや追加のテストの必要はありません.
私は、それがPostgreSQLと同じように動作することを確認するためにyugabytedbにこれを走らせました.もちろん、Supgres SQL処理層を使用してください.

テーブル

以下は親テーブルですparent_deleted timestamp削除の日付に設定します.デフォルト値infinity , が有効なレコードです.このカラムは、プライマリキーの一部ですparent_id . しかし、1つだけ有効です.

CREATE TABLE parent (
 parent_id int, parent_deleted timestamptz default 'infinity',
 primary key (parent_id,parent_deleted)
);

子テーブルは親の主キーを継承し、child_number その主キーとして.外部キーはon update cascade ソフトの削除は、この主キーに更新としてカスケードされます.

CREATE TABLE child (
 parent_id int, parent_deleted timestamptz default 'infinity',
 child_number int,
 primary key (parent_id,parent_deleted, child_number),
 foreign key (parent_id,parent_deleted) 
  references parent(parent_id,parent_deleted)
  on update cascade
);

見解

テーブルは十分でしょう.しかし、SQLの美しさは論理的独立です.ソフト削除の実装を気にせずに、アプリケーションやユーザから自分のテーブルを問い合わせる.
その意見を述べます.アプリケーションがクエリvalid_parent and valid_child 現在のバージョンを参照してください.

create view valid_parent as
 select parent_id from parent where parent_deleted>=now();

create view valid_child as
 select parent_id,child_number from child where parent_deleted>=now();

主キーを再利用するために、テーブルに加わる必要はありません.削除がまれであるとき、これは正しい選択です.そして、人々は結合がスケールしないと思いがちです.

手順

このロジックをデータベースにカプセル化し、このソフト削除のために呼び出される手順を作成します.

create procedure soft_delete_parent(id int) as $SQL$
update parent
set parent_deleted=now()
where parent_id=id;
$SQL$ language sql;

あなたがストアドプロシージャが好きでないならば、私は後で選択肢を示します.しかし、個人的に、私はこの手順カプセル化が意味があるので、私は好きです:アプリケーションは特定の手順を呼びます.

データ

そこに数行挿入しています.ビューを仮想テーブルに挿入し、すべてのDMLが許可されているので、ビューを挿入します.デフォルトinfinity 値が自動的に設定されます

insert into valid_parent
 select n from generate_series(1,3) n;

insert into valid_child
 select parent_id,n from valid_parent,generate_series(1,2) n;

これが私のテストのスクリーンショットです

あなたは簡単にそれを再現することができますfree tier ?

テスト

SQLでデータロジックを実装するとき、データベースはすべてのマルチユーザーの一貫性を考慮しているので、単純な単体テストは通常十分です.

select * from valid_parent;

select * from valid_child;

これは有効な行だけを示します.プロシージャをソフトで親を削除します.

call soft_delete_parent(2);

ビューに問い合わせを行うと、その行は事実上削除されました.

select * from valid_parent;

select * from valid_child;

結果を以下に示します.

ビューの後ろにあるテーブルでは、すべての行を見ることができます.

yugabyte=# select * from parent;

 parent_id |        parent_deleted
-----------+-------------------------------
         1 | infinity
         2 | 2022-04-15 10:21:45.635693+00
         3 | infinity
(3 rows)

yugabyte=# select * from child;

 parent_id |        parent_deleted         | child_number
-----------+-------------------------------+--------------
         1 | infinity                      |            1
         1 | infinity                      |            2
         2 | 2022-04-15 10:21:45.635693+00 |            1
         2 | 2022-04-15 10:21:45.635693+00 |            2
         3 | infinity                      |            1
         3 | infinity                      |            2
(6 rows)

グラントとrevokeを使用すると、ビューにのみアクセスできます.とハード削除する権利を取り消す.

ルール

ユーザーはプロシージャを呼び出す必要はありませんが、単に代わりにDOコードを使用してビューを削除する必要があります.

create or replace rule soft_delete_parent as 
on delete to valid_parent do instead
update parent
set parent_deleted=now()
where parent_id=old.parent_id;

これは簡単です.現在、削除は実際にはソフト削除を行います.

これは、アプリケーションがちょうど標準SQL API(選択、挿入、更新、削除)と相互作用しているように見えます.そして、それは便利なときに、アプリケーションを変更することができます.しかし、より良いコード品質のために、アプリケーション開発者が彼女が何をするかを知っているように、私は手順を好みます(私のプロシージャ名は柔らかい削除について陽です).また、この規則がその出力で100 %透明でないことを見ることができますDELETE 0 .

PostgreSQL互換

この手法は、PostgreSQLとPostgreSQLの互換性のあるデータベースでは簡単です.ここでは、簡単、宣言、および透明にするSQL機能の一覧を示します.
SQL機能
🐘 PostgreSQL
🚀 ユガシテ
🪳 ゴキブリ
🅾 oracle
コンポジットPK
✅
✅
✅
✅
デフォルト
✅
✅
✅ ( 4 )
❌ ( 1 )
更新カスケード
✅
✅
✅
❌ ( 2 )
ストアドプロシージャ
✅
✅
❌
✅
ビューに挿入する
✅
✅
❌
✅
ルール/ビューの代わりに
✅
✅
❌
✅ ( 3 )
グラント/レヴォーク
✅
✅
✅
✅
(1)時間的妥当性は代替である
(2)トリガおよび繰返された制約は、代替であってもよい
( 3 ) Oracleは等価でないnow() 取引開始
( 4 ) 2942766 - 12 - 31に表示されます.23 : 59 : 59 , 999999 + 00
公平であるために、私は私が基礎より知っているデータベースだけに比較しました.SQL Serverの推測です

フランク・パチョ💙💛 🇺🇦

私は基本よりも知っているDBSとだけを比較する.なぜなら、時には、同じ機能がそこにないが、他の方法を同じようにしている、それは公正と言うだけではない' no '.見ていることからは(無限なし)できるdbfiddle.uk/?rdbms=sqlserv…
午後16時22分- 2022年4月18日
これは、yugabytedbのすべてのPostgreSQL機能を持つことが多くのエンタープライズアプリケーションの適切なソリューションになります.あなたがデータベースにビジネスロジックを入れたくない場合でも、格納された手続き、トリガー、ルール、またはモノリシックデータベース上でその値を証明している現在のSQL機能のいずれかを必要とする一日があり、分散SQLで利用可能になりました.