From 42446fcdcf9d752201414afc7e5c7bcb2fb2ee6e Mon Sep 17 00:00:00 2001 From: Paul Duncan Date: Thu, 17 Feb 2022 23:19:55 -0500 Subject: dbstore/sql: add cpe and cpe-match subdirs --- dbstore/sql/cpe-match/insert-name.sql | 5 +++ dbstore/sql/cpe-match/insert-version-max.sql | 5 +++ dbstore/sql/cpe-match/insert-version-min.sql | 5 +++ dbstore/sql/cpe-match/insert-vulnerable.sql | 4 +++ dbstore/sql/cpe-match/insert.sql | 1 + dbstore/sql/cpe-search-all.sql | 46 --------------------------- dbstore/sql/cpe-search-ref.sql | 47 ---------------------------- dbstore/sql/cpe-search-title.sql | 44 -------------------------- dbstore/sql/cpe/insert-ref.sql | 1 + dbstore/sql/cpe/insert-title.sql | 1 + dbstore/sql/cpe/insert.sql | 1 + dbstore/sql/cpe/search-all.sql | 46 +++++++++++++++++++++++++++ dbstore/sql/cpe/search-ref.sql | 47 ++++++++++++++++++++++++++++ dbstore/sql/cpe/search-title.sql | 44 ++++++++++++++++++++++++++ dbstore/sql/insert-cpe.sql | 1 - dbstore/sql/insert-ref.sql | 1 - dbstore/sql/insert-title.sql | 1 - 17 files changed, 160 insertions(+), 140 deletions(-) create mode 100644 dbstore/sql/cpe-match/insert-name.sql create mode 100644 dbstore/sql/cpe-match/insert-version-max.sql create mode 100644 dbstore/sql/cpe-match/insert-version-min.sql create mode 100644 dbstore/sql/cpe-match/insert-vulnerable.sql create mode 100644 dbstore/sql/cpe-match/insert.sql delete mode 100644 dbstore/sql/cpe-search-all.sql delete mode 100644 dbstore/sql/cpe-search-ref.sql delete mode 100644 dbstore/sql/cpe-search-title.sql create mode 100644 dbstore/sql/cpe/insert-ref.sql create mode 100644 dbstore/sql/cpe/insert-title.sql create mode 100644 dbstore/sql/cpe/insert.sql create mode 100644 dbstore/sql/cpe/search-all.sql create mode 100644 dbstore/sql/cpe/search-ref.sql create mode 100644 dbstore/sql/cpe/search-title.sql delete mode 100644 dbstore/sql/insert-cpe.sql delete mode 100644 dbstore/sql/insert-ref.sql delete mode 100644 dbstore/sql/insert-title.sql diff --git a/dbstore/sql/cpe-match/insert-name.sql b/dbstore/sql/cpe-match/insert-name.sql new file mode 100644 index 0000000..adc99f4 --- /dev/null +++ b/dbstore/sql/cpe-match/insert-name.sql @@ -0,0 +1,5 @@ +INSERT INTO cpe_match_cpes( + cpe_match_id, + cpe_id, + cpe22_uri +) VALUES (?, (SELECT cpe_id FROM cpes WHERE cpe23 = ?), ?); diff --git a/dbstore/sql/cpe-match/insert-version-max.sql b/dbstore/sql/cpe-match/insert-version-max.sql new file mode 100644 index 0000000..b39944f --- /dev/null +++ b/dbstore/sql/cpe-match/insert-version-max.sql @@ -0,0 +1,5 @@ +INSERT INTO cpe_match_version_maxs( + cpe_match_id, + is_inclusive, + max_version +) VALUES (?, ?, ?); diff --git a/dbstore/sql/cpe-match/insert-version-min.sql b/dbstore/sql/cpe-match/insert-version-min.sql new file mode 100644 index 0000000..a7e8643 --- /dev/null +++ b/dbstore/sql/cpe-match/insert-version-min.sql @@ -0,0 +1,5 @@ +INSERT INTO cpe_match_version_mins( + cpe_match_id, + is_inclusive, + min_version +) VALUES (?, ?, ?); diff --git a/dbstore/sql/cpe-match/insert-vulnerable.sql b/dbstore/sql/cpe-match/insert-vulnerable.sql new file mode 100644 index 0000000..062c95b --- /dev/null +++ b/dbstore/sql/cpe-match/insert-vulnerable.sql @@ -0,0 +1,4 @@ +INSERT INTO cpe_match_vulnerables ( + cpe_match_id, + is_vulnerable +) VALUES (?, ?) diff --git a/dbstore/sql/cpe-match/insert.sql b/dbstore/sql/cpe-match/insert.sql new file mode 100644 index 0000000..bc5d2c0 --- /dev/null +++ b/dbstore/sql/cpe-match/insert.sql @@ -0,0 +1 @@ +INSERT INTO cpe_matches(cpe23, cpe22_uri) VALUES (?, ?); diff --git a/dbstore/sql/cpe-search-all.sql b/dbstore/sql/cpe-search-all.sql deleted file mode 100644 index 3260902..0000000 --- a/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/dbstore/sql/cpe-search-ref.sql b/dbstore/sql/cpe-search-ref.sql deleted file mode 100644 index a2d3be6..0000000 --- a/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/dbstore/sql/cpe-search-title.sql b/dbstore/sql/cpe-search-title.sql deleted file mode 100644 index 7421ffc..0000000 --- a/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/dbstore/sql/cpe/insert-ref.sql b/dbstore/sql/cpe/insert-ref.sql new file mode 100644 index 0000000..a39ab1c --- /dev/null +++ b/dbstore/sql/cpe/insert-ref.sql @@ -0,0 +1 @@ +INSERT INTO cpe_refs(cpe_id, href, val) VALUES (?, ?, ?); diff --git a/dbstore/sql/cpe/insert-title.sql b/dbstore/sql/cpe/insert-title.sql new file mode 100644 index 0000000..5045d95 --- /dev/null +++ b/dbstore/sql/cpe/insert-title.sql @@ -0,0 +1 @@ +INSERT INTO cpe_titles(cpe_id, lang, val) VALUES (?, ?, ?); diff --git a/dbstore/sql/cpe/insert.sql b/dbstore/sql/cpe/insert.sql new file mode 100644 index 0000000..dca1c2b --- /dev/null +++ b/dbstore/sql/cpe/insert.sql @@ -0,0 +1 @@ +INSERT INTO cpes(cpe_uri, cpe23) VALUES (?, ?); diff --git a/dbstore/sql/cpe/search-all.sql b/dbstore/sql/cpe/search-all.sql new file mode 100644 index 0000000..3260902 --- /dev/null +++ b/dbstore/sql/cpe/search-all.sql @@ -0,0 +1,46 @@ +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/dbstore/sql/cpe/search-ref.sql b/dbstore/sql/cpe/search-ref.sql new file mode 100644 index 0000000..a2d3be6 --- /dev/null +++ b/dbstore/sql/cpe/search-ref.sql @@ -0,0 +1,47 @@ +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/dbstore/sql/cpe/search-title.sql b/dbstore/sql/cpe/search-title.sql new file mode 100644 index 0000000..7421ffc --- /dev/null +++ b/dbstore/sql/cpe/search-title.sql @@ -0,0 +1,44 @@ +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/dbstore/sql/insert-cpe.sql b/dbstore/sql/insert-cpe.sql deleted file mode 100644 index dca1c2b..0000000 --- a/dbstore/sql/insert-cpe.sql +++ /dev/null @@ -1 +0,0 @@ -INSERT INTO cpes(cpe_uri, cpe23) VALUES (?, ?); diff --git a/dbstore/sql/insert-ref.sql b/dbstore/sql/insert-ref.sql deleted file mode 100644 index a39ab1c..0000000 --- a/dbstore/sql/insert-ref.sql +++ /dev/null @@ -1 +0,0 @@ -INSERT INTO cpe_refs(cpe_id, href, val) VALUES (?, ?, ?); diff --git a/dbstore/sql/insert-title.sql b/dbstore/sql/insert-title.sql deleted file mode 100644 index 5045d95..0000000 --- a/dbstore/sql/insert-title.sql +++ /dev/null @@ -1 +0,0 @@ -INSERT INTO cpe_titles(cpe_id, lang, val) VALUES (?, ?, ?); -- cgit v1.2.3