v10 SQLite support + distributed locking abstractions #11922

p-m-j · 2022-01-28T17:38:23Z

No description provided.

src/Umbraco.Infrastructure/Persistence/NPocoDatabaseTypeExtensions.cs

p-m-j · 2022-02-12T00:03:05Z

src/Umbraco.Infrastructure/Persistence/Repositories/Implement/UserRepository.cs

                .From<UserLoginDto>()
                .Where<UserLoginDto>(x => x.SessionId == SqlTemplate.Arg<Guid>("sessionId"))
-                .ForUpdate());
+                .ForUpdate()
+                .SelectTop(1)); // Stick at end, SQL server syntax provider will insert at start of query after "select ", but sqlite will append limit to end.


Npoco (and our extension) expression builders are pretty linear, the final query is written in order.

The SelectTop extension will add "TOP {n}" at the first select found in the current query.
But for RDBMS with Limit it's hard for us to know when/where to append to query as later statements may still be chained into the builder.

This kindof sucks but if you always put SelectTop last for a query (or sub query) it always works.

p-m-j · 2022-02-12T00:03:30Z

src/Umbraco.Infrastructure/Persistence/ScalarMapper.cs

+
+namespace Umbraco.Cms.Infrastructure.Persistence;
+
+public abstract class ScalarMapper<T> : IScalarMapper


NPoco + MS.Data.Sqlite workaround sad times.

ExecuteScalar<T> in npoco only works for types supported by Convert.ChangeType (e.g. not byte[] or string -> Guid which we need) so we have to workaround for now.

Not a problem with System.Data.Sqlite whose upstream object ExecuteScalar returns Guid by magic, MS team wanted less magic so we get string or byte[].

I don't think there is a way around this, however perhaps the setup should be more consistent with the rest of the provider bundle (e.g. as part of DbProviderFactoryCreator)

p-m-j · 2022-02-12T00:07:50Z

src/Umbraco.Persistence.Sqlite/Services/SqliteBulkSqlInsertProvider.cs

+
+        foreach (var record in records)
+        {
+            database.Insert(record);


If we convert to query text we can instantiate params a single time and rebind (future performance enhancement, not top priority)

p-m-j · 2022-02-12T00:12:22Z

umbraco.sln

-		{1B28FC3E-3D5B-4A46-8961-5483835548D7}.Debug|Any CPU.Build.0 = Debug|Any CPU
-		{1B28FC3E-3D5B-4A46-8961-5483835548D7}.Release|Any CPU.ActiveCfg = Release|Any CPU
-		{1B28FC3E-3D5B-4A46-8961-5483835548D7}.Release|Any CPU.Build.0 = Release|Any CPU
+		{2A5027D9-F71D-4957-929E-F7A56AA1B95A}.SkipTests|Any CPU.ActiveCfg = Debug|Any CPU


stops codeql kicking off about passwords in test projects etc

p-m-j · 2022-02-12T00:14:46Z

src/Umbraco.Persistence.Sqlite/Services/SqliteDatabaseCreator.cs

+        connection.Open();
+
+        using SqliteCommand command = connection.CreateCommand();
+        command.CommandText = "PRAGMA journal_mode = wal;";


not settable on connection string for MS.Data.Sqlite

p-m-j · 2022-02-14T08:57:58Z

src/Umbraco.Persistence.Sqlite/Constants.cs

@@ -8,5 +8,5 @@ public static class Constants
    /// <summary>
    /// SQLite provider name.
    /// </summary>
-    public const string ProviderName = "System.Data.SQLite";
+    public const string ProviderName = "Microsoft.Data.SQLite";


We switched to support (MacOS) osx-arm (native interop library for sys.data missing osx-arm binaries)

src/Umbraco.Infrastructure/Persistence/NPocoSqlExtensions.cs

src/Umbraco.Persistence.SqlServer/Dtos/ColumnInSchemaDto.cs

p-m-j · 2022-02-14T13:44:17Z

src/Umbraco.Persistence.Sqlite/Services/SqliteSyntaxProvider.cs

+        _globalSettings = globalSettings;
+        _log = log;
+
+        _scalarMappers = new Dictionary<Type, IScalarMapper>


should this move to DbProviderFactoryCreator?

TODO: InMemoryDistributedLockingMechanism. Note: Nuked SqlServerDistributedLockingMechanismTests, will still sleep at night. Is covered by Umbraco.Cms.Tests.Integration.Umbraco.Infrastructure.Persistence.LockTests

InMemoryDistributedMechanism was pretty rubbish and now we have a decent implementation for SQLite as we no longer block readers see 8d1f42b. Also drop the CollectionBuilder setup, instead do the same as we do for syntax providers etc, it's more automagical so we never require an explicit selection although we are allowing for it. However keeping the optional IUmbracoBuilder constructor param for CollectionBuilders as it's extremely useful.

Doesn't seem worth it to extract db name from connection string.

There's no point in running the query just to make a single test pass.

p-m-j · 2022-03-11T16:23:27Z

🎉 !!!

p-m-j force-pushed the v10/feature/sqlite-support branch 5 times, most recently from 2216813 to 1e52264 Compare Jan 31, 2022

p-m-j force-pushed the v10/feature/sqlite-support branch 4 times, most recently from 94f94ee to 2748670 Compare Feb 11, 2022

p-m-j reviewed Feb 12, 2022

View changes

src/Umbraco.Infrastructure/Persistence/NPocoDatabaseTypeExtensions.cs Outdated Show resolved Hide resolved

p-m-j reviewed Feb 12, 2022

View changes

p-m-j force-pushed the v10/feature/sqlite-support branch from 7cad38c to ff825fd Compare Feb 13, 2022

p-m-j reviewed Feb 14, 2022

View changes

src/Umbraco.Infrastructure/Persistence/NPocoSqlExtensions.cs Outdated Show resolved Hide resolved

p-m-j reviewed Feb 14, 2022

View changes

src/Umbraco.Persistence.SqlServer/Dtos/ColumnInSchemaDto.cs Outdated Show resolved Hide resolved

p-m-j reviewed Feb 14, 2022

View changes

p-m-j force-pushed the v10/feature/sqlite-support branch 3 times, most recently from 487bc3a to 7d7eb57 Compare Feb 15, 2022

bergmania added release/10.0.0 type/feature category/notable labels Feb 21, 2022

p-m-j force-pushed the v10/feature/sqlite-support branch from 9ea8b57 to 86e4cd2 Compare Feb 25, 2022

p-m-j marked this pull request as ready for review Mar 1, 2022

p-m-j force-pushed the v10/feature/sqlite-support branch from eb51053 to 263c454 Compare Mar 2, 2022

bergmania added the category/breaking label Mar 2, 2022

p-m-j added 22 commits Mar 11, 2022

Integration tests use SqlServerDistributedLockingMechanism when possible

afd26fc

Handle up-gradable locks SqlServerDistributedLockingMechanism.

caa0b45

TODO: InMemoryDistributedLockingMechanism. Note: Nuked SqlServerDistributedLockingMechanismTests, will still sleep at night. Is covered by Umbraco.Cms.Tests.Integration.Umbraco.Infrastructure.Persistence.LockTests

Make tests pass for InMemoryDistributedLockingMechanism, pretty hacky.

3582a05

Tweak constraints on WithCollectionBuilder so i can drop bad constructor

317a78d

Added SqliteDistributedLockingMechanism

e4d18ef

Fix quick install "" database name.

2c70c46

Hide Database Configuration section when a connection string is pre-set.

fc51ca9

Doesn't seem worth it to extract db name from connection string.

Ensure wal test 2+

b8ceae5

Fix logging inconsistencies.

206a628

Ensure in transaction when obtaining locks + no-op the SQLite read lock.

cb42326

There's no point in running the query just to make a single test pass.

Fix installer database display names

fcb03a7

Allow SQLite shared cache without losing deferred transactions

89dadef

Opt into shared cache for new SQLite databases + fix filename

f976ffd

Fix misc inconsistency in .gitignore

89ed5df

Prefer our interceptor interface

b7805ff

Restore DEBUG_DATABASES code OnConnectionOpened in case it's used.

637df0c

Back to private cache.

57b22d0

Added retry strategy for SQLite + refactor out SQL server specific stuff

4ae93d5

Fix SQL server tests.

22b15ef

Misc - Orphaned comment, incorrect casing.

e6d7a06

InMemory SQLite test database & turn shared cache back on everywhere.

a74c984

p-m-j force-pushed the v10/feature/sqlite-support branch from 15e077f to a74c984 Compare Mar 11, 2022

p-m-j changed the title ~~V10 SQLite support~~ v10 SQLite support + Distributed Locking abstractions Mar 11, 2022

p-m-j changed the title ~~v10 SQLite support + Distributed Locking abstractions~~ v10 SQLite support + d]istributed locking abstractions Mar 11, 2022

p-m-j changed the title ~~v10 SQLite support + d]istributed locking abstractions~~ v10 SQLite support + distributed locking abstractions Mar 11, 2022

bergmania merged commit 3961c4c into v10/dev Mar 11, 2022
17 checks passed

bergmania deleted the v10/feature/sqlite-support branch Mar 11, 2022

leekelleher mentioned this pull request Apr 21, 2022

[v3.0] Data List - SQL CE data source (on Umbraco v9.0) leekelleher/umbraco-contentment#172

Closed

1 task

umbraco / Umbraco-CMS Public

v10 SQLite support + distributed locking abstractions #11922

v10 SQLite support + distributed locking abstractions #11922

p-m-j commented Jan 28, 2022

p-m-j Feb 12, 2022 •

edited

p-m-j Feb 12, 2022 •

edited

p-m-j Feb 12, 2022 •

edited

p-m-j Feb 12, 2022

p-m-j Feb 12, 2022

p-m-j Feb 14, 2022 •

edited

p-m-j Feb 14, 2022 •

edited

p-m-j commented Mar 11, 2022


		namespace Umbraco.Cms.Infrastructure.Persistence;

		public abstract class ScalarMapper<T> : IScalarMapper

umbraco / Umbraco-CMS Public

v10 SQLite support + distributed locking abstractions #11922

v10 SQLite support + distributed locking abstractions #11922

Conversation

p-m-j commented Jan 28, 2022

p-m-j Feb 12, 2022 • edited

Choose a reason for hiding this comment

p-m-j Feb 12, 2022 • edited

Choose a reason for hiding this comment

p-m-j Feb 12, 2022 • edited

Choose a reason for hiding this comment

p-m-j Feb 12, 2022

Choose a reason for hiding this comment

p-m-j Feb 12, 2022

Choose a reason for hiding this comment

p-m-j Feb 14, 2022 • edited

Choose a reason for hiding this comment

p-m-j Feb 14, 2022 • edited

Choose a reason for hiding this comment

p-m-j commented Mar 11, 2022

p-m-j Feb 12, 2022 •

edited

p-m-j Feb 12, 2022 •

edited

p-m-j Feb 12, 2022 •

edited

p-m-j Feb 14, 2022 •

edited

p-m-j Feb 14, 2022 •

edited