dkforest

commit 1d8a668322403cd797543f978c498b4cdc340e78
parent 445e1f5fa96103879075ef4762437e3ad035a019
Author: n0tr1v <n0tr1v@protonmail.com>
Date:   Thu, 26 Jan 2023 15:43:31 -0800

fix sqlite triggers for links fts5

Diffstat:
A
cmd/dkf/migrations/124.sql
 | 
20
++++++++++++++++++++

1 file changed, 20 insertions(+), 0 deletions(-)
diff --git a/cmd/dkf/migrations/124.sql b/cmd/dkf/migrations/124.sql
@@ -0,0 +1,20 @@
+-- +migrate Up
+drop trigger links_before_update;
+drop trigger links_before_delete;
+drop trigger links_after_insert;
+drop trigger links_after_update;
+
+-- +migrate StatementBegin
+CREATE TRIGGER links_before_update
+    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_update1
+    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;
+-- +migrate StatementEnd
+
+-- +migrate Down