Use Full Text Search extensions in RDBMS to improve text search in JPA View

[zambrovski]

Scenario

• camunda-bpm-taskpool version: 3.10.0
• Camunda BPM version: 7.18
• Description of your use case: After a like text search of name, description, process name is implemented, I want to make use of local improvements of a RDBMS to make it faster.

Current Behaviour

LIKE operator of SQL is used (slow).

Wanted Behaviour

Faster implementation accelerated by a RDBMS vendor extension.

Full text search is supported by the following vendors:

• Postgresql
• Azure SQL

Possible Workarounds

• wait

[zambrovski]

Idea: use built-in RDBMS functions, expose them into JPA, and use as part of specification. The function exposure will be vendor specific, the remaining part should be generic.

References:

• https://dev.to/fabiothiroki/setup-spring-and-postgres-for-full-text-search-4n97
• https://azure.microsoft.com/en-us/blog/full-text-search-is-now-available-for-preview-in-azure-sql-database/

[zambrovski]

• I-Test with containerized DB (test-container setup)
• Segregate creation of Specification from Implementation of the custom DB function
• Create Metadata builder contributor for (POSGRESQL, AZURE)
• Create Specification

[zambrovski]

See https://github.com/toolisticon/example-spring-jpa-fts