dbstore: lots of small fixes

2 files changed, 71 insertions, 123 deletions

diff --git a/dbstore/sql/cpe-match/insert-name.sql b/dbstore/sql/cpe-match/insert-name.sql
index adc99f4..ebdb638 100644
--- a/dbstore/sql/cpe-match/insert-name.sql
+++ b/dbstore/sql/cpe-match/insert-name.sql
@@ -1,5 +1,4 @@
-INSERT INTO cpe_match_cpes(
-  cpe_match_id,
-  cpe_id,
-  cpe22_uri
-) VALUES (?, (SELECT cpe_id FROM cpes WHERE cpe23 = ?), ?);
+INSERT INTO cpe_match_cpes(cpe_match_id, cpe_id, cpe22_uri)
+  SELECT ?, cpe_id, ?
+    FROM cpes
+   WHERE cpe23 = ?;
diff --git a/dbstore/sql/init.sql b/dbstore/sql/init.sql
index 0e05c6a..cc83a02 100644
--- a/dbstore/sql/init.sql
+++ b/dbstore/sql/init.sql
@@ -22,25 +22,6 @@ CREATE TABLE cpes (
                     )
 );
 
--- create all fts table (cross join across all
--- titles and references, populated by triggers)
-CREATE VIRTUAL TABLE cpe_fts_all USING fts5(
-  -- parent CPE id
-  cpe_id UNINDEXED,
-
-  -- title
-  title,
-
-  -- reference URL
-  href,
-
-  -- reference name
-  val,
-
-  -- use porter stemming
-  tokenize = 'porter'
-);
-
 -- create titles table
 CREATE TABLE cpe_titles (
   -- title primary key
@@ -51,66 +32,23 @@ CREATE TABLE cpe_titles (
                     REFERENCES cpes(cpe_id),
 
   -- language code
-  lang              TEXT NOT NULL
-                    CHECK (LENGTH(lang) >= 2),
+  lang              TEXT NOT NULL CHECK (
+                      -- FIXME: is this upper bound correct
+                      LENGTH(lang) BETWEEN 2 AND 16
+                    ),
 
   -- text value
-  val               TEXT NOT NULL
-                    CHECK (LENGTH(val) > 0),
+  val               TEXT NOT NULL CHECK (
+                      -- FIXME: is this upper bound correct
+                      LENGTH(val) BETWEEN 1 AND 1024
+                    ),
 
   -- prevent duplicate titles of the same name
   UNIQUE (cpe_id, lang)
 );
 
--- create titles fts table
-CREATE VIRTUAL TABLE cpe_fts_titles USING fts5(
-  -- parent CPE id
-  cpe_id UNINDEXED,
-
-  -- title
-  title,
-
-  -- use porter stemming
-  tokenize = 'porter'
-);
-
--- create titles all fts insert trigger
-CREATE TRIGGER tr_cpe_titles_insert_fts_all AFTER INSERT ON cpe_titles
-  FOR EACH ROW WHEN (NEW.lang = 'en-US') BEGIN
-    INSERT INTO cpe_fts_all(cpe_id, title, href, val)
-      SELECT NEW.cpe_id,
-             NEW.val,
-             b.href,
-             b.val
-
-        FROM cpes a
-        JOIN cpe_refs b
-          ON (b.cpe_id = a.cpe_id)
-
-       WHERE a.cpe_id = NEW.cpe_id;
-  END;
-
--- create titles delete all fts trigger
-CREATE TRIGGER tr_cpe_titles_delete_fts_all BEFORE DELETE ON cpe_titles
-  FOR EACH ROW WHEN (OLD.lang = 'en-US') BEGIN
-    DELETE FROM cpe_fts_all
-     WHERE cpe_id = OLD.cpe_id
-       AND title = OLD.val;
-  END;
-
--- create titles all fts insert trigger
-CREATE TRIGGER tr_cpe_titles_insert_fts_titles AFTER INSERT ON cpe_titles
-  FOR EACH ROW WHEN (NEW.lang = 'en-US') BEGIN
-    INSERT INTO cpe_fts_titles(cpe_id, title) VALUES (NEW.cpe_id, NEW.val);
-  END;
-
--- create titles delete titles fts trigger
-CREATE TRIGGER tr_cpe_titles_delete_fts_titles BEFORE DELETE ON cpe_titles
-  FOR EACH ROW WHEN (OLD.lang = 'en-US') BEGIN
-    DELETE FROM cpe_fts_titles
-     WHERE cpe_id = OLD.cpe_id
-       AND title = OLD.val;
-  END;
+-- create language index
+CREATE INDEX in_cpe_titles_lang ON cpe_titles(lang);
 
 -- create refs table
 CREATE TABLE cpe_refs (
@@ -123,69 +61,78 @@ CREATE TABLE cpe_refs (
 
   -- url
   href              TEXT NOT NULL CHECK (
-                      LENGTH(href) > 0 AND
-                      (href LIKE 'http://%' OR href LIKE 'https://%')
+                      LENGTH(href) BETWEEN 1 AND 1024 AND (
+                        href LIKE 'http://%' OR
+                        href LIKE 'https://%' OR
+                        href LIKE 'ftp://%'
+                      )
                     ),
 
   -- text description
-  val               TEXT NOT NULL
-                    CHECK (LENGTH(val) > 0)
+  val               TEXT NOT NULL CHECK (
+                      -- FIXME: check upper bound
+                      LENGTH(val) BETWEEN 1 AND 1024
+                    )
 );
 
--- create refs fts table
-CREATE VIRTUAL TABLE cpe_fts_refs USING fts5(
+-- create all fts table (cross join across all
+-- titles and references, populated by triggers)
+CREATE VIRTUAL TABLE cpes_fts USING fts5(
   -- parent CPE id
   cpe_id UNINDEXED,
 
-  -- reference URL
-  href,
+  -- titles
+  titles,
 
-  -- reference name
-  val,
+  -- reference URL (no bueno w/ porter stemming)
+  -- href,
+
+  -- reference names
+  refs,
 
   -- use porter stemming
   tokenize = 'porter'
 );
 
--- create refs insert trigger for all fts
-CREATE TRIGGER tr_cpe_refs_insert_fts_all AFTER INSERT ON cpe_refs
-  FOR EACH ROW BEGIN
-    INSERT INTO cpe_fts_all(cpe_id, title, href, val)
-      SELECT NEW.cpe_id,
-             b.val,
-             NEW.href,
-             NEW.val
-
-        FROM cpes a
-        JOIN cpe_titles b
-          ON (b.cpe_id = a.cpe_id)
-
-       WHERE a.cpe_id = NEW.cpe_id
-         AND b.lang = 'en-US';
-  END;
+-- dummy table to trigger cpes_fts refreshes.
+CREATE TABLE cpes_fts_refresh (
+  created_at        TIMESTAMP WITH TIME ZONE NOT NULL
+                    DEFAULT CURRENT_TIMESTAMP
+);
 
--- create refs delete trigger for all fts
-CREATE TRIGGER tr_cpe_refs_delete_fts_all BEFORE DELETE ON cpe_refs
+-- create before insert trigger on cpes_fts_refresh
+CREATE TRIGGER tr_cpes_fts_refresh_before_insert
+  BEFORE INSERT ON cpes_fts_refresh
   FOR EACH ROW BEGIN
-    DELETE FROM cpe_fts_all
-     WHERE cpe_id = OLD.cpe_id
-       AND href = OLD.lang
-       AND val = OLD.val;
+    DELETE FROM cpes_fts;
   END;
 
--- create refs insert trigger for refs fts
-CREATE TRIGGER tr_cpe_refs_insert_fts_refs AFTER INSERT ON cpe_refs
+-- create insert trigger on cpes_fts_refresh
+CREATE TRIGGER tr_cpes_fts_refresh_after_insert
+  AFTER INSERT ON cpes_fts_refresh
   FOR EACH ROW BEGIN
-    INSERT INTO cpe_fts_refs(cpe_id, href, val) VALUES (NEW.cpe_id, NEW.href, NEW.val);
-  END;
+    INSERT INTO cpes_fts(cpe_id, titles, refs)
+      SELECT a.cpe_id,
+             b.vals,
+             -- c.href,
+             c.vals
 
--- create refs delete trigger for refs fts
-CREATE TRIGGER tr_cpe_refs_delete_fts_refs BEFORE DELETE ON cpe_refs
-  FOR EACH ROW BEGIN
-    DELETE FROM cpe_fts_refs
-     WHERE cpe_id = OLD.cpe_id
-       AND href = OLD.lang
-       AND val = OLD.val;
+        FROM cpes a
+
+        JOIN (
+          SELECT cpe_id,
+                 group_concat(val, ' ') AS vals
+            FROM cpe_titles
+           WHERE lang = 'en-US'
+           GROUP BY cpe_id
+        ) b ON (b.cpe_id = a.cpe_id)
+
+        JOIN (
+          SELECT cpe_id,
+                 group_concat(val, ' ') AS vals
+            FROM cpe_refs
+           GROUP BY cpe_id
+        ) c ON (c.cpe_id = a.cpe_id);
   END;
 
 --
@@ -196,7 +143,7 @@ CREATE TABLE cpe_matches (
   cpe_match_id  INTEGER PRIMARY KEY,
 
   -- modern CPE 2.3 formatted string
-  cpe23             TEXT UNIQUE NOT NULL CHECK (
+  cpe23             TEXT NOT NULL CHECK (
                       LENGTH(cpe23) > 28 AND
                       cpe23 LIKE 'cpe:2.3:%:%:%:%:%:%:%:%:%:%:%'
                     ),
@@ -205,6 +152,8 @@ CREATE TABLE cpe_matches (
   cpe22_uri         TEXT NOT NULL
 );
 
+CREATE INDEX in_cpe_matches_cpe23 ON cpe_matches(cpe23);
+
 -- cpe match vulnerabilities
 CREATE TABLE cpe_match_vulnerables (
   cpe_match_id      INTEGER PRIMARY KEY,
@@ -265,7 +214,7 @@ CREATE TABLE cpe_match_version_maxs (
     REFERENCES cpe_matches(cpe_match_id)
 );
 
--- map of matching
+-- map of matches to cpe IDs
 CREATE TABLE cpe_match_cpes (
   -- CPE match ID
   cpe_match_id      INTEGER NOT NULL
@@ -927,7 +876,7 @@ CREATE VIRTUAL TABLE cisa_vulns_fts USING fts5(
 );
 
 --
--- Dummy table to trigger cve_fts refreshes.
+-- Dummy table to trigger cisa_vulns_fts refreshes.
 --
 CREATE TABLE cisa_vulns_fts_refresh (
   created_at        TIMESTAMP WITH TIME ZONE NOT NULL