full text search works with indexing and fuzzy matching

[github-actions[bot]]

Hasura Semantic Diff

Hasura config files have changed. This comment shows which fields have changed ignoring formatting.

Click to expand!

``` lunatrace + one map entry added: functions: "!include lunatrace/functions/functions.yaml" 0 ± value change - !include public_manifest_dependency_node_child_edges_recursive.yaml + !include public_search_vulnerabilities.yaml (root level) + one map entry added: function: name: search_vulnerabilities schema: public lunatrace-custom.permissions - three list entries removed: - role: user definition: schema: | scalar JSON scalar UUID type AuthenticatedRepoCloneUrlOutput { url: String } type Mutation { presignManifestUpload(project_id: UUID!): PresignedUrlResponse } type PresignedUrlResponse { bucket: String! headers: JSON! key: String! url: String! } type Query { authenticatedRepoCloneUrl(repoGithubId: Int!): AuthenticatedRepoCloneUrlOutput fakeQueryToHackHasuraBeingABuggyMess: String sbomUrl(buildId: UUID!): String } type SbomUploadUrlOutput { error: Boolean! uploadUrl: UploadUrl } type UploadUrl { headers: JSON! url: String! } - role: service definition: schema: | scalar JSON scalar UUID type AuthenticatedRepoCloneUrlOutput { url: String } type Mutation { presignManifestUpload(project_id: UUID!): PresignedUrlResponse } type PresignedUrlResponse { bucket: String! headers: JSON! key: String! url: String! } type Query { authenticatedRepoCloneUrl(repoGithubId: Int!): AuthenticatedRepoCloneUrlOutput fakeQueryToHackHasuraBeingABuggyMess: String presignSbomUpload(orgId: UUID!, buildId: UUID!): SbomUploadUrlOutput sbomUrl(buildId: UUID!): String } type SbomUploadUrlOutput { error: Boolean! uploadUrl: UploadUrl } type UploadUrl { headers: JSON! url: String! } input SbomUploadUrlInput { orgId: UUID! projectId: UUID! } - role: cli definition: schema: | scalar JSON scalar UUID type Query { presignSbomUpload(orgId: UUID!, buildId: UUID!): SbomUploadUrlOutput } type SbomUploadUrlOutput { error: Boolean! uploadUrl: UploadUrl } type UploadUrl { headers: JSON! url: String! } + three list entries added: - role: user definition: schema: | type AuthenticatedRepoCloneUrlOutput { url: String } scalar JSON type Mutation { presignManifestUpload(project_id: UUID!): PresignedUrlResponse } type PresignedUrlResponse { bucket: String! headers: JSON! key: String! url: String! } type Query { authenticatedRepoCloneUrl(repoGithubId: Int!): AuthenticatedRepoCloneUrlOutput fakeQueryToHackHasuraBeingABuggyMess: String sbomUrl(buildId: UUID!): String } type SbomUploadUrlOutput { error: Boolean! uploadUrl: UploadUrl } scalar UUID type UploadUrl { headers: JSON! url: String! } - role: service definition: schema: | type AuthenticatedRepoCloneUrlOutput { url: String } scalar JSON type Mutation { presignManifestUpload(project_id: UUID!): PresignedUrlResponse } type PresignedUrlResponse { bucket: String! headers: JSON! key: String! url: String! } type Query { authenticatedRepoCloneUrl(repoGithubId: Int!): AuthenticatedRepoCloneUrlOutput fakeQueryToHackHasuraBeingABuggyMess: String presignSbomUpload(orgId: UUID!, buildId: UUID!): SbomUploadUrlOutput sbomUrl(buildId: UUID!): String } input SbomUploadUrlInput { orgId: UUID! projectId: UUID! } type SbomUploadUrlOutput { error: Boolean! uploadUrl: UploadUrl } scalar UUID type UploadUrl { headers: JSON! url: String! } - role: cli definition: schema: | scalar JSON type Query { presignSbomUpload(orgId: UUID!, buildId: UUID!): SbomUploadUrlOutput } type SbomUploadUrlOutput { error: Boolean! uploadUrl: UploadUrl } scalar UUID type UploadUrl { headers: JSON! url: String! } diff --git a/lunatrace/bsl/hasura/migrations/lunatrace/1676395122236_full-text-search/down.sql b/lunatrace/bsl/hasura/migrations/lunatrace/1676395122236_full-text-search/down.sql new file mode 100644 index 00000000..418eed7f --- /dev/null +++ b/lunatrace/bsl/hasura/migrations/lunatrace/1676395122236_full-text-search/down.sql @@ -0,0 +1 @@ +DROP INDEX vulnerability.vulnerability_fts_idx; diff --git a/lunatrace/bsl/hasura/migrations/lunatrace/1676395122236_full-text-search/up.sql b/lunatrace/bsl/hasura/migrations/lunatrace/1676395122236_full-text-search/up.sql new file mode 100644 index 00000000..dfabd663 --- /dev/null +++ b/lunatrace/bsl/hasura/migrations/lunatrace/1676395122236_full-text-search/up.sql @@ -0,0 +1,52 @@ +CREATE EXTENSION IF NOT EXISTS pg_trgm; + +-- -- function used for combining multiple text fields together into the full text search index +-- This function is immutable, and that means it can be used for the index, where as naked concat_ws cannot +CREATE OR REPLACE FUNCTION f_concat_ws(VARIADIC text[]) + RETURNS text + LANGUAGE sql + IMMUTABLE PARALLEL SAFE AS +'SELECT array_to_string($1, '' '')'; + + + + + +-- SELECT * +-- FROM vulnerability.vulnerability +-- WHERE 'GHSA-f3fp-gc8g-vw66' <% f_concat_ws(source_id, cve_id, summary, details) +-- ORDER BY SIMILARITY(f_concat_ws(source_id, cve_id, summary, details), 'GHSA-f3fp-gc8g-vw66' ) +-- DESC; + +-- If the search field is blank, we need to skip the text search and just return all + +CREATE OR REPLACE FUNCTION search_vulnerabilities(search text) + returns setof vulnerability.vulnerability AS $$ +BEGIN + IF search = '' THEN + RETURN QUERY SELECT * + FROM vulnerability.vulnerability; + ELSE + RETURN QUERY SELECT * + FROM vulnerability.vulnerability + WHERE search <% f_concat_ws(source_id, cve_id, summary, details) + ORDER BY SIMILARITY(f_concat_ws(source_id, cve_id, summary, details), search) + DESC; + END IF; +END +-- +-- SELECT * +-- FROM vulnerability.vulnerability +-- WHERE search <% f_concat_ws(source_id, cve_id, summary, details) +-- ORDER BY SIMILARITY(f_concat_ws(source_id, cve_id, summary, details), search) +-- DESC; + +$$ LANGUAGE plpgsql STABLE; + + +CREATE INDEX vulnerability_fts_idx ON vulnerability.vulnerability USING GIN ( + -- use this exact same f_concat_ws expression in queries in order to use this index. Lightening fast if you use it right + f_concat_ws(source_id, cve_id, summary, details) + gin_trgm_ops + ); + ```