CasesManagement add PostgreSQL support

[panoukos41]

After testing the following have been found and need to be fixed/researched so that the cases management system can run using PostgreSQL engine.

1. The implementation of the json columns uses MsSql fucntions by default. This needs to be configurable.
2. The AddWorkflow extension method allows for a delegate to configure the Elsa efcore dbcontext giving the ability to use other engines for Elsa as well.

Other checks that need to be made

1. Make sure the efcore context doesn't use other MsSql specific features.

[cleftheris]

For the core INDICE extensions it is doable to introduce support for PostgreSQL. This actually is related to src/Indice.EntityFrameworkCore/Functions/ModelBuilderExtensions.cs

We can discuss to see what we need to do to support it and there are already tests in-place for these in test/Indice.Services.Tests/FilterClauseQueryableExtensionTests.cs so we can replicate the same behavior for PostgreSQL

@panoukos41

cc @dkarkanas

[cleftheris]

A good candidate for JSON_VALUE equivalent in PostgreSQL could be jsonb_path_query_first https://www.postgresql.org/docs/current/functions-json.html

[Dimkormpos]

After some investigation, jsonb_path_query_first seems to be the way to go. Although it returns a jsonb, so everything using that needs a cast to string. There is a second issue regarding the postgre specific datetime conversion that needs some attention. The arguments need to be in this order Convert(data, timestampz, text) but I was not able to make the Convert work in Postgre. I only got the expected results when using Cast (or ::timestampz). An other note is that when searching exact text, the json value of the jsonb_path_query_first result will be casted along with "" so this needs to be taken into consideration as the example bellow. Finally in Postgre all LIKE operations are case sensitive and an option is to use ILIKE.

So an expected working query would look like this:

SELECT d."Id", d."Data", d."Extras", d."Metadata", d."Name"
FROM "Dummies" AS d
WHERE jsonb_path_query_first(d."Data", '$.displayName')::text ILIKE '%κων%' 
AND (jsonb_path_query_first(d."Data", '$.period.to')::text)::timestamptz > '2024-01-17T00:00:00.0000000' 
AND jsonb_path_query_first(d."Metadata", '$.NAME')::text = '"Thanos"'