[v4.0] Data List - SQL data source does not support SQLite (on Umbraco v10+)

[prjseal]

Which Contentment version are you using?

4.1.5

Which Umbraco version are you using? For example: 8.14.1 - don't just write v8

10.2.0

Bug summary

When trying to use the Data List SQL Data Source an error is thrown, complaining about Keyword not supported: 'cache'.

Full Stacktrace:

at Microsoft.Data.Common.DbConnectionOptions.ParseInternal(Dictionary`2 parsetable, String connectionString, Boolean buildChain, Dictionary`2 synonyms, Boolean firstKey)
   at Microsoft.Data.Common.DbConnectionOptions..ctor(String connectionString, Dictionary`2 synonyms)
   at Microsoft.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
   at Microsoft.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key)
   at Microsoft.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
   at Umbraco.Community.Contentment.DataEditors.SqlDataListSource.GetSqlItems[TConnection,TCommand](String query, String connectionString)+MoveNext()
   at System.Collections.Generic.List`1.InsertRange(Int32 index, IEnumerable`1 collection)
   at Umbraco.Community.Contentment.DataEditors.SqlDataListSource.GetItems(Dictionary`2 config)
   at Umbraco.Community.Contentment.DataEditors.DataListConfigurationEditor.ToValueEditor(Object configuration)
   at Umbraco.Community.Contentment.DataEditors.DataListApiController.GetPreview(JObject data)
   at lambda_method1009(Closure , Object , Object[] )
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextExceptionFilterAsync>g__Awaited|26_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)

Steps to reproduce

Use this script to install the right version of Umbraco with the right version of Contentment.

https://psw.codeshare.co.uk/?InstallUmbracoTemplate=true&UmbracoTemplateVersion=10.2.0&Packages=Our.Umbraco.Community.Contentment%7C4.1.5&UserEmail=admin%40example.com&ProjectName=MyProject&CreateSolutionFile=true&SolutionName=MySolution&UseUnattendedInstall=true&DatabaseType=SQLite&UserPassword=1234567890&UserFriendlyName=Administrator&IncludeStarterKit=true&StarterKitPackage=clean

Create a new Data Type which uses the Contentment Data List.

Choose the SQL Data Source.

Leave it to be the default query

Choose buttons or dropdown for the editor, or anything really.

See the error.

Expected result / actual result

I expected the query to be able to execute properly and return results to the property.

Do you have Umbraco ModelsBuilder enabled?

• [X] Yes, it is enabled.

What browsers are you seeing the problem on?

Chrome

[leekelleher]

@prjseal Thanks for raising this. Unfortunately, I haven't got around to implementing the SQLite provider yet 😢 (I left a code comment about it, but I should have put it in the README's Known Issues - doh!)

I'll need to figure out how to handle SQLite with a multi-targeted package. 😬

[prjseal]

Ah that makes sense now. I was able to get around this by implementing my own custom data list source which calls my repository service that uses NPoco.

[enkelmedia]

Hi!

I have not looked into any details around this specific issue but I wanted to share some of the experience of making sure my email package works on both SQL Server and SqlLite.

My initial idea was to create some kind of abstraction over the DB but before I did that I tried to see if would be possible to just "translate" the "SQL Server"-SQL into "SqlLite"-SQL, by making sure that types in my db-queries were correct and so on. In my case, this works great but I guess it all depends on the specific use case. Anyway, I figured I'll share the code here:

https://gist.github.com/enkelmedia/d4a19f797d9ee1ff8bd4cfbd4589b783

The basic idea is that you run your string representation of your SQL query via this method and then get back a query that would work on SqlLite. I do know that this is far from optimized in terms of memory etc. but it does solve the problem and I have and it might work as an inspiration (maybe =D). I'm also sure that this does not cover every possible use case and query, just the basic ones that I had.

I understand if this approach is too specific for my use case but I wanted to share the pragmatic approach that I took for quite a big package. There is always the option for custom data sources and so one which might be a better recommendation in this scenario.

I would be happy to share more details about the code or "formalize" it if this is interesting but wanted to start by showing the basic idea.

All the best! and look forward to seeing you att CodeGarden 2023, Kee thanks again for a great package!

[leekelleher]

Good news, I hopefully have fixed this in PR #356! 🙌

[leekelleher]

Closing this ticket. It'll be part of the next patch release, (date unknown, soon-ish, week or two-ish).