From 16df68a5118801472909f63fc48caf16e548060c Mon Sep 17 00:00:00 2001
From: Adam Mathes <adam@trenchant.org>
Date: Sat, 16 Jun 2018 09:55:36 -0700
Subject: add back in search support, requires sqlite

---
 sqlite.init.sql | 71 +++++++++++++++++++++++++++++++++++----------------------
 1 file changed, 44 insertions(+), 27 deletions(-)

(limited to 'sqlite.init.sql')

diff --git a/sqlite.init.sql b/sqlite.init.sql
index d4d08c8..411d152 100644
--- a/sqlite.init.sql
+++ b/sqlite.init.sql
@@ -1,30 +1,47 @@
-CREATE TABLE IF NOT EXISTS "feed" (
-  "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
-  "url" varchar(100) NOT NULL UNIQUE,
-  "web_url" varchar(255) NOT NULL DEFAULT '',
-  "title" varchar(255) NOT NULL DEFAULT '',
-  "last_updated" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-  "category" varchar(255) NOT NULL DEFAULT 'uncategorized'
+CREATE TABLE feed (
+  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+  url varchar(100) NOT NULL UNIQUE,
+  web_url varchar(255) NOT NULL DEFAULT '',
+  title varchar(255) NOT NULL DEFAULT '',
+  last_updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  category varchar(255) NOT NULL DEFAULT 'uncategorized'
 );
-CREATE INDEX "feed_url" ON "feed" ("url");
-CREATE INDEX "feed_category" ON "feed" ("category");
-CREATE INDEX "feed_id" ON "feed" ("id");
+CREATE INDEX feed_url ON feed (url);
+CREATE INDEX feed_category ON feed (category);
 
-CREATE TABLE IF NOT EXISTS "item" (
-  "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
-  "feed_id" int(11) NOT NULL,
-  "title" text NOT NULL DEFAULT '',
-  "url" varchar(255) NOT NULL UNIQUE,
-  "description" text NOT NULL DEFAULT '',
-  "publish_date" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-  "read_state" tinyint(1) NOT NULL DEFAULT '0',
-  "starred" tinyint(1) NOT NULL DEFAULT '0',
-  "full_content" text NOT NULL DEFAULT '',
-  "header_image" text NOT NULL DEFAULT '',
-  CONSTRAINT "item_ibfk_1" FOREIGN KEY ("feed_id") REFERENCES "feed" ("id") ON DELETE CASCADE
+CREATE TABLE item (
+  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+  feed_id int(11) NOT NULL,
+  title text NOT NULL DEFAULT '',
+  url varchar(255) NOT NULL UNIQUE,
+  description text NOT NULL DEFAULT '',
+  publish_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  read_state tinyint(1) NOT NULL DEFAULT '0',
+  starred tinyint(1) NOT NULL DEFAULT '0',
+  full_content text NOT NULL DEFAULT '',
+  header_image text NOT NULL DEFAULT '',
+  CONSTRAINT item_ibfk_1 FOREIGN KEY (feed_id) REFERENCES feed(id) ON DELETE CASCADE
 );
-CREATE INDEX "item_url" ON "item" ("url");
-CREATE INDEX "item_publish_date" ON "item" ("publish_date");
-CREATE INDEX "item_feed_id" ON "item" ("feed_id");
-CREATE INDEX "item_rev_id" ON "item" ("id");
-CREATE INDEX "item_read_state" ON "item" ("read_state");
+CREATE INDEX item_url ON item (url);
+CREATE INDEX item_publish_date ON item (publish_date);
+CREATE INDEX item_feed_id ON item (feed_id);
+CREATE INDEX item_read_state ON item (read_state);
+
+CREATE VIRTUAL TABLE fts_item using fts4(content="item", title, url, description);
+
+INSERT INTO fts_item(fts_item) VALUES('rebuild');
+
+
+CREATE TRIGGER item_bu BEFORE UPDATE ON item BEGIN
+  DELETE FROM fts_item WHERE docid=old.rowid;
+END;
+CREATE TRIGGER t2_bd BEFORE DELETE ON t2 BEGIN
+  DELETE FROM t3 WHERE docid=old.rowid;
+END;
+
+CREATE TRIGGER t2_au AFTER UPDATE ON t2 BEGIN
+  INSERT INTO t3(docid, b, c) VALUES(new.rowid, new.b, new.c);
+END;
+CREATE TRIGGER t2_ai AFTER INSERT ON t2 BEGIN
+  INSERT INTO t3(docid, b, c) VALUES(new.rowid, new.b, new.c);
+END;
-- 
cgit v1.2.3