dkforest

commit 2bb946ec3ffe131f24f73d9678908ccc9610693d
parent a22fae4e1ea728fb00321ae44f3a24e10e97d67d
Author: n0tr1v <n0tr1v@protonmail.com>
Date:   Sat, 28 Jan 2023 16:30:26 -0800

fix links triggers

Diffstat:
A
cmd/dkf/migrations/125.sql
 | 
26
++++++++++++++++++++++++++

1 file changed, 26 insertions(+), 0 deletions(-)
diff --git a/cmd/dkf/migrations/125.sql b/cmd/dkf/migrations/125.sql
@@ -0,0 +1,26 @@
+-- +migrate Up
+drop trigger links_before_update;
+drop trigger links_before_update1;
+
+-- +migrate StatementBegin
+CREATE TRIGGER links_before_update_soft_delete
+    BEFORE UPDATE ON links WHEN old.deleted_at IS NULL AND new.deleted_at IS NOT NULL BEGIN
+    DELETE FROM fts5_links WHERE id=old.id;
+END;
+
+CREATE TRIGGER links_before_update
+    BEFORE UPDATE ON links WHEN old.deleted_at IS NOT NULL AND new.deleted_at IS NULL BEGIN
+    INSERT INTO fts5_links(rowid, uuid, url, title, description, created_at, visited_at) VALUES
+        (new.id, new.uuid, new.url, new.title, new.description, new.created_at, new.visited_at);
+END;
+
+CREATE TRIGGER links_after_insert
+    AFTER INSERT ON links BEGIN
+    INSERT INTO fts5_links(rowid, uuid, url, title, description, created_at, visited_at)
+        SELECT id, uuid, url, title, description, created_at, visited_at
+        FROM links
+        WHERE links.id = new.id;
+END;
+-- +migrate StatementEnd
+
+-- +migrate Down