Use custom SQL query in FHIR Engine

[jingtang10]

Is your feature request related to a problem? Please describe.

See comment in opensrp/fhircore#2066 (comment):

Write raw queries instead of using the FHIR Engine search API. I had to add a method inside FhirEngine to allow raw queries and return a raw Cursor

Describe the solution you'd like
Provide an API so that developers can write custom SQL queries to search the offline FHIR Engine database.

Describe alternatives you've considered
@ekigamba can you please provide some examples of the custom queries you need to write? And we can see if there're any alternative approach we can take.

Additional context
opensrp/fhircore#2066

Would you like to work on the issue?
Potentially

[ekigamba]

@jingtang10 Here is the snippet of the draft issue which should try and answer some questions and/or provide the context

Describe the solution you'd like

• Expose the database.search(SearchQuery) API through the FhirEngine as FhirEngine.search(SearchQuery) which allows returning results as FHIR Resources. This allows us to run nested queries that are more performant and use pre-cached values from previous queries such as resourceUuid and optimise search performance for our use-cases
• Provide an API that exposes the database to allow running custom queries and returning a Cursor to allow fetching table metadata such as resourceUuid which is is not a valid FHIR Resource property but is the unique identifier for records on the database and used to reference resources. This will allow us to directly use these cached resourceUuid instead of fetching them again for each query. Queries performed using the FHIR Search engine only allow Resource.id which still needs to be translated to resourceUuid to connect with other search param tables. This adds an extra operation whose time complexity quickly grows in UIs with a lot of data points such as client lists

Describe alternatives you've considered
The team has:

• Reduced the amount of data per client/patient list to reduce the queries that need to be performed
• Moved count operations to the FHIR search API

Both solutions improved the performance it still take some seconds even for patient lists with less data points on a moderate device. The device is Blu G60 with 3 GB RAM, 64 GB ROM, Octa-core 1.6 GHz and running on Android 9. Writing our own queries to consolidate some of the operations further imporved the performance by more than 50%

Example queries used to improve performance

SELECT resourceUuid FROM ResourceEntity WHERE resourceType = "Patient" AND resourceId IN (
SELECT SUBSTR(index_value, 9) FROM ReferenceIndexEntity WHERE index_name = "member" 
AND resourceUuid = (SELECT resourceUuid FROM ResourceEntity WHERE resourceId = ?)
)

Query allows us to fetch the resourceUuid that we can use to this to fetch other values faster, for example,

SELECT COUNT(*) FROM TokenIndexEntity a JOIN DateIndexEntity b ON a.resourceUuid = b.resourceUuid  
WHERE a.resourceUuid IN ($memberSelector) AND a.index_name = "active" AND a.index_value = "true" 
AND b.index_name = "birthdate" AND b.index_from >= ?

Query fetches group members whose birthdate if from X years ago eg. 5 years ago directly with

For our specific use case, we'd benefit more if we could cache the specific data points that we frequently access a lot and I believe Arun had expressed an idea that was related to some tasking stuff he was working on

[jingtang10]

Is this still needed @ekigamba? Seems we don't need this any more with the performance work we've done?

[ekigamba]

We don't need this at this point. We can close this