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-title.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-title.sql')
-rw-r--r-- | dbstore/sql/cpe/search-title.sql | 44 |
1 files changed, 44 insertions, 0 deletions
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; |