diff options
| author | Paul Duncan <pabs@pablotron.org> | 2022-02-17 23:19:55 -0500 |
|---|---|---|
| committer | Paul Duncan <pabs@pablotron.org> | 2022-02-17 23:19:55 -0500 |
| commit | 42446fcdcf9d752201414afc7e5c7bcb2fb2ee6e (patch) | |
| tree | 2c633a39b51c8323f859653dc89dd53c6be78861 /dbstore/sql/cpe-search-ref.sql | |
| parent | 103dbf934637d80e9624702d039ed2f7a153548d (diff) | |
| download | cvez-42446fcdcf9d752201414afc7e5c7bcb2fb2ee6e.tar.bz2 cvez-42446fcdcf9d752201414afc7e5c7bcb2fb2ee6e.zip | |
dbstore/sql: add cpe and cpe-match subdirs
Diffstat (limited to 'dbstore/sql/cpe-search-ref.sql')
| -rw-r--r-- | dbstore/sql/cpe-search-ref.sql | 47 |
1 files changed, 0 insertions, 47 deletions
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 |