aboutsummaryrefslogtreecommitdiff
path: root/internal/dbstore/sql
diff options
context:
space:
mode:
authorPaul Duncan <pabs@pablotron.org>2022-02-05 02:37:00 -0500
committerPaul Duncan <pabs@pablotron.org>2022-02-05 02:37:00 -0500
commit22fd269069b8e47bc40fbd8681243bdd4bd61a47 (patch)
treee16bbe17d985d1d567f83926fd1b7a417948083a /internal/dbstore/sql
parent56cc399430d127cb628b3abe84bc653d2b0ce59b (diff)
downloadcvez-22fd269069b8e47bc40fbd8681243bdd4bd61a47.tar.bz2
cvez-22fd269069b8e47bc40fbd8681243bdd4bd61a47.zip
mv internal/dbstore dbstore
Diffstat (limited to 'internal/dbstore/sql')
-rw-r--r--internal/dbstore/sql/cpe-search-all.sql46
-rw-r--r--internal/dbstore/sql/cpe-search-ref.sql47
-rw-r--r--internal/dbstore/sql/cpe-search-title.sql44
-rw-r--r--internal/dbstore/sql/fill-fts.sql10
-rw-r--r--internal/dbstore/sql/init.sql180
-rw-r--r--internal/dbstore/sql/insert-cpe.sql1
-rw-r--r--internal/dbstore/sql/insert-ref.sql1
-rw-r--r--internal/dbstore/sql/insert-title.sql1
-rw-r--r--internal/dbstore/sql/old-cpe-search-all.sql69
-rw-r--r--internal/dbstore/sql/table-exists.sql6
10 files changed, 0 insertions, 405 deletions
diff --git a/internal/dbstore/sql/cpe-search-all.sql b/internal/dbstore/sql/cpe-search-all.sql
deleted file mode 100644
index 3260902..0000000
--- a/internal/dbstore/sql/cpe-search-all.sql
+++ /dev/null
@@ -1,46 +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 cpe_id,
- MIN(rank) AS rank
- FROM cpe_fts_all a
- WHERE cpe_fts_all MATCH :q
- GROUP BY 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
diff --git a/internal/dbstore/sql/cpe-search-ref.sql b/internal/dbstore/sql/cpe-search-ref.sql
deleted file mode 100644
index a2d3be6..0000000
--- a/internal/dbstore/sql/cpe-search-ref.sql
+++ /dev/null
@@ -1,47 +0,0 @@
-SELECT a.cpe_id,
- a.cpe23,
- COALESCE(c.titles, '[]') AS titles,
- COALESCE(d.refs, '[]') AS refs,
- b.rank
-
- -- find matching cpes
- FROM cpes a
- JOIN (
- -- there can be multiple matching references for a given
- -- CPE, so use MIN() to limit the match to the most
- -- "relevant", according to bm25()
- SELECT cpe_id,
- MIN(rank) AS rank
- FROM cpe_fts_refs
- WHERE cpe_fts_refs MATCH :q
- GROUP BY 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
diff --git a/internal/dbstore/sql/cpe-search-title.sql b/internal/dbstore/sql/cpe-search-title.sql
deleted file mode 100644
index 7421ffc..0000000
--- a/internal/dbstore/sql/cpe-search-title.sql
+++ /dev/null
@@ -1,44 +0,0 @@
-SELECT a.cpe_id,
- a.cpe23,
- COALESCE(c.titles, '[]') AS titles,
- COALESCE(d.refs, '[]') AS refs,
- b.rank
-
- -- find matching cpes
- FROM cpes a
- JOIN (
- SELECT cpe_id,
- MIN(rank) AS rank
- FROM cpe_fts_titles
- WHERE cpe_fts_titles MATCH :q
- GROUP BY 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;
diff --git a/internal/dbstore/sql/fill-fts.sql b/internal/dbstore/sql/fill-fts.sql
deleted file mode 100644
index 5329ca7..0000000
--- a/internal/dbstore/sql/fill-fts.sql
+++ /dev/null
@@ -1,10 +0,0 @@
--- populate fts table
-INSERT INTO cpe_fts(rowid, title)
- SELECT a.cpe_id,
- COALESCE(b.val, '')
-
- FROM cpes a
- LEFT JOIN cpe_titles b
- ON (b.cpe_id = a.cpe_id)
-
- WHERE b.lang = 'en-US';
diff --git a/internal/dbstore/sql/init.sql b/internal/dbstore/sql/init.sql
deleted file mode 100644
index 37eef54..0000000
--- a/internal/dbstore/sql/init.sql
+++ /dev/null
@@ -1,180 +0,0 @@
--- enable foreign keys
-PRAGMA foreign_keys = true;
-
--- create cpes table
-CREATE TABLE cpes (
- -- cpe unique ID
- cpe_id INTEGER PRIMARY KEY,
-
- -- cpe uri
- cpe_uri TEXT UNIQUE NOT NULL,
-
- -- cpe 2.3 formatting string
- cpe23 TEXT UNIQUE NOT NULL
-);
-
--- 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
- cpe_title_id INTEGER PRIMARY KEY,
-
- -- external CPE id
- cpe_id INT NOT NULL
- REFERENCES cpes(cpe_id),
-
- -- language code
- lang TEXT NOT NULL
- CHECK (LENGTH(lang) > 0),
-
- -- text value
- val TEXT NOT NULL
- CHECK (LENGTH(val) > 0),
-
- -- 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 refs table
-CREATE TABLE cpe_refs (
- -- reference unique id
- cpe_ref_id INTEGER PRIMARY KEY,
-
- -- external CPE id
- cpe_id INT NOT NULL
- REFERENCES cpes(cpe_id),
-
- -- url
- href TEXT NOT NULL
- CHECK (LENGTH(href) > 0),
-
- -- text description
- val TEXT NOT NULL
- CHECK (LENGTH(val) > 0)
-);
-
--- create refs fts table
-CREATE VIRTUAL TABLE cpe_fts_refs USING fts5(
- -- parent CPE id
- cpe_id UNINDEXED,
-
- -- reference URL
- href,
-
- -- reference name
- val,
-
- -- 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;
-
--- create refs delete trigger for all fts
-CREATE TRIGGER tr_cpe_refs_delete_fts_all BEFORE DELETE ON cpe_refs
- FOR EACH ROW BEGIN
- DELETE FROM cpe_fts_all
- WHERE cpe_id = OLD.cpe_id
- AND href = OLD.lang
- AND val = OLD.val;
- END;
-
--- create refs insert trigger for refs fts
-CREATE TRIGGER tr_cpe_refs_insert_fts_refs AFTER INSERT ON cpe_refs
- FOR EACH ROW BEGIN
- INSERT INTO cpe_fts_refs(cpe_id, href, val) VALUES (NEW.cpe_id, NEW.href, NEW.val);
- END;
-
--- 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;
- END;
-
-PRAGMA user_version = 314159;
diff --git a/internal/dbstore/sql/insert-cpe.sql b/internal/dbstore/sql/insert-cpe.sql
deleted file mode 100644
index dca1c2b..0000000
--- a/internal/dbstore/sql/insert-cpe.sql
+++ /dev/null
@@ -1 +0,0 @@
-INSERT INTO cpes(cpe_uri, cpe23) VALUES (?, ?);
diff --git a/internal/dbstore/sql/insert-ref.sql b/internal/dbstore/sql/insert-ref.sql
deleted file mode 100644
index a39ab1c..0000000
--- a/internal/dbstore/sql/insert-ref.sql
+++ /dev/null
@@ -1 +0,0 @@
-INSERT INTO cpe_refs(cpe_id, href, val) VALUES (?, ?, ?);
diff --git a/internal/dbstore/sql/insert-title.sql b/internal/dbstore/sql/insert-title.sql
deleted file mode 100644
index 5045d95..0000000
--- a/internal/dbstore/sql/insert-title.sql
+++ /dev/null
@@ -1 +0,0 @@
-INSERT INTO cpe_titles(cpe_id, lang, val) VALUES (?, ?, ?);
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
diff --git a/internal/dbstore/sql/table-exists.sql b/internal/dbstore/sql/table-exists.sql
deleted file mode 100644
index 09d32a4..0000000
--- a/internal/dbstore/sql/table-exists.sql
+++ /dev/null
@@ -1,6 +0,0 @@
-SELECT EXISTS(
- SELECT 1
- FROM sqlite_schema
- WHERE type = 'table'
- AND name = ?
-)