From 22fd269069b8e47bc40fbd8681243bdd4bd61a47 Mon Sep 17 00:00:00 2001
From: Paul Duncan <pabs@pablotron.org>
Date: Sat, 5 Feb 2022 02:37:00 -0500
Subject: mv internal/dbstore dbstore

---
 internal/dbstore/sql/old-cpe-search-all.sql | 69 -----------------------------
 1 file changed, 69 deletions(-)
 delete mode 100644 internal/dbstore/sql/old-cpe-search-all.sql

(limited to 'internal/dbstore/sql/old-cpe-search-all.sql')

diff --git a/internal/dbstore/sql/old-cpe-search-all.sql b/internal/dbstore/sql/old-cpe-search-all.sql
deleted file mode 100644
index 18e286f..0000000
--- a/internal/dbstore/sql/old-cpe-search-all.sql
+++ /dev/null
@@ -1,69 +0,0 @@
-SELECT a.cpe_id,
-       a.cpe23,
-       COALESCE(c.titles, '[]') AS titles,
-       COALESCE(d.refs, '[]') AS refs,
-       b.rank
-
-  FROM cpes a
-  JOIN (
-    -- limit to "best" match (e.g., lowest rank) for given CPE
-    -- NOTE: it's not clear to me whether cross-index bm25()
-    -- comparisons are valid
-    SELECT a.cpe_id,
-           MIN(a.rank) AS rank
-
-      FROM (
-        -- find title matches
-        SELECT b.cpe_id,
-               a.rank AS rank
-
-          FROM cpe_titles_fts a
-          JOIN cpe_titles b
-            ON (b.cpe_title_id = a.rowid)
-
-         WHERE cpe_titles_fts MATCH :q
-
-        UNION ALL
-
-        -- find reference matches
-        SELECT b.cpe_id,
-               a.rank
-
-          FROM cpe_refs_fts a
-          JOIN cpe_refs b
-            ON (b.cpe_ref_id = a.rowid)
-
-         WHERE cpe_refs_fts MATCH :q
-      ) a
-
-     GROUP BY a.cpe_id
-  ) b ON (b.cpe_id = a.cpe_id)
-
-  -- get all titles, grouped by cpe
-  LEFT JOIN (
-    SELECT cpe_id,
-           json_group_array(json_object(
-             'lang', lang,
-             'text', val
-           )) AS titles
-
-      FROM cpe_titles
-
-     GROUP BY cpe_id
-  ) c ON (c.cpe_id = b.cpe_id)
-
-  -- get all refs, grouped by cpe
-  LEFT JOIN (
-    SELECT cpe_id,
-           json_group_array(json_object(
-             'href', href,
-             'text', val
-           )) AS refs
-
-      FROM cpe_refs
-
-     GROUP BY cpe_id
-  ) d ON (d.cpe_id = b.cpe_id)
-
- -- order by rank
- ORDER BY b.rank
-- 
cgit v1.2.3

