SQLite should be removed

[EatonZ]

I think it's time to consider the removal of SQLite.

If you look through some recent (and old!) open and closed issues within this project, several of them are related to SQLite. Blocking strong name support, waiting for updates, blocking AppCenter package install, and it randomly stops working for some people.

Not to mention, you had to put a lot of effort into dealing with SQLite corruption!

This is more trouble than it's worth and what benefit does it really bring? It seems like it's just meant to store report data when a machine is offline so it can sent later. With my app, nearly all users appear to use it with a stable internet connection. Users that use the app on an offline machine probably do that for a reason, and AppCenter will store 10 MB of data on their disk that probably will never be sent. For users that have an unstable internet connection - that is probably such a small % of users and I would honestly rather reduce my app by 1.3 MB so my next app update will be smaller and thus respect their slower connection.

There are 2 significant benefits to this:

1. The elimination of 5 extra dlls people have to deploy with with their apps. That is 1.3 MB!
2. Less errors and frustration for developers. For me, this has been one of the most annoying packages I have had to set up with my project, purely because of SQLite dependencies.

There may be some people who really want to capture as much data as possible, so maybe keep SQLite for them, but for the rest of us, ifdef all that out for a build that gets published to NuGet without any SQLite support. That would be great.🙂

[nabeelio]

It would be nice to have an interface which could be implemented for database backing, or just being able to null it out if we don't want that feature (as above). I use LiteDB with my app, so it would be nice to be able to have some type of adapter/interface to implement, instead of also having to include sqlite, though I'd honestly do the same as @EatonZ and just disable it.

I also believe sqlite is keeping me from updating to .NET Core, since there seems to be an issue with packaging as a single exe, though that's a guess because the messages around that are pretty terrible. But it's an additional set of dependencies to have to worry about.

[maxkatz6]

Also with removing SQLite and adding interface for database backing, AppCenter will can return support for NetStandard1 and older UWP SDK versions. SQLite backend could be available under AppCenter.SQLite nuget package.

[myokeeh]

I'm starting to like this idea. Perhaps even switch to a simpler format (JSON?)

[rolikoff]

If only someone could prioritise that :). We have so many issues with SQL dependencies here. On every appcenter update, we have to double check that the crashes, events and analytics are coming and make sure that we don't see any stuff like this:

2020-06-11 22:01:27.657 [AppCenter] ERROR: Failed to initialize sqlite3 provider.
System.ArgumentException: The path is not of a legal form.
   at System.IO.Path.LegacyNormalizePath(String path, Boolean fullCheck, Int32 maxPathLength, Boolean expandShortPaths)
   at System.IO.Path.InternalGetDirectoryName(String path)
   at SQLitePCL.NativeLibrary.MakePossibilitiesFor(String basename, Assembly assy, Int32 flags, LibSuffix suffix)
   at SQLitePCL.NativeLibrary.MyLoad(String basename, Assembly assy, Int32 flags, Action`1 log)
   at SQLitePCL.NativeLibrary.Load(String libraryName, Assembly assy, Int32 flags)
   at SQLitePCL.Batteries_V2.MakeDynamic(String name, Int32 flags)
   at SQLitePCL.Batteries_V2.DoDynamic_cdecl(String name, Int32 flags)
   at Microsoft.AppCenter.Storage.StorageAdapter..cctor()
'EncryptMeUI.exe' (CLR v4.0.30319: EncryptMeUI.exe): Loaded 'System.Runtime.CompilerServices.Unsafe'. Module was built without symbols.
'EncryptMeUI.exe' (CLR v4.0.30319: EncryptMeUI.exe): Loaded 'System.Runtime'.
2020-06-11 22:01:27.722 [AppCenter] ERROR: An error occurred while initializing storage
System.NullReferenceException: Object reference not set to an instance of an object.
   at SQLitePCL.raw.sqlite3_open(String filename, sqlite3& db)
   at Microsoft.AppCenter.Storage.StorageAdapter.Initialize(String databasePath)
   at Microsoft.AppCenter.Storage.Storage.InitializeDatabase()
2020-06-11 22:01:27.724 [AppCenter] ERROR: The storage operation failed
Microsoft.AppCenter.Storage.StorageException: The database wasn't initialized.
   at Microsoft.AppCenter.Storage.StorageAdapter.ExecuteSelectionSqlQuery(String query, IList`1 args)
   at Microsoft.AppCenter.Storage.StorageAdapter.Count(String tableName, String columnName, Object value)
   at Microsoft.AppCenter.Storage.Storage.<>c__DisplayClass31_0`1.<AddTaskToQueue>b__0()
2020-06-11 22:01:27.731 [AppCenter] ERROR: The storage operation failed
Microsoft.AppCenter.Storage.StorageException: The database wasn't initialized.
   at Microsoft.AppCenter.Storage.StorageAdapter.ExecuteSelectionSqlQuery(String query, IList`1 args)
   at Microsoft.AppCenter.Storage.StorageAdapter.Count(String tableName, String columnName, Object value)
   at Microsoft.AppCenter.Storage.Storage.<>c__DisplayClass31_0`1.<AddTaskToQueue>b__0()
'myapp.exe' (CLR v4.0.30319: myapp.exe): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Management\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Management.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
2020-06-11 22:01:27.749 [AppCenter] ERROR: The storage operation failed
Microsoft.AppCenter.Storage.StorageException: The database wasn't initialized.
   at Microsoft.AppCenter.Storage.StorageAdapter.ExecuteSelectionSqlQuery(String query, IList`1 args)
   at Microsoft.AppCenter.Storage.StorageAdapter.Count(String tableName, String columnName, Object value)
   at Microsoft.AppCenter.Storage.Storage.<>c__DisplayClass31_0`1.<AddTaskToQueue>b__0()

[Jamminroot]

Hey, thanks for showing your interest to this feature request. I'll address that feedback to PMs team

[nabeelio]

@Jamminroot Thanks! I'm considering dropping appcenter because I'm blocking on moving to .NET core because of sqlite.

[Jamminroot]

@nabeelio I see. But if the problem is just bumping moving to .NET Core - that shouldn't be a problem since version 3.0.0 App Center uses SQLitePCLRaw.bundle_green 2.0.2, which is .NET Core compatible. Sorry if I missed something

[EatonZ]

The current App Center works fine for me on .NET Core 3.1.

[Jamminroot]

@nabeelio waiting to hear from you

[nabeelio]

Sorry, my bad, I should have been more specific; I think there's an issue with sqlite which is preventing the packaging as a single exe feature of Core from working properly. I haven't revisited it in a while, though, so maybe 2.0.2 fixed it. There were a few Github issues about it - not sqlite specifically, but that's where, when I traced it, I think the packaging issues were coming from.

With Fody Costura, I need to exclude all of the AppCenter/sqlite dependencies from the packaging or it doesn't start up. I think that was in another issue here (I can't remember if I opened that one or not). So I think that's part of the issue with the Core single exe, too.

But that's an aside from this request because I'd like to get rid of the sqlite dependency all together for the reasons outlined in the original post.

[Jamminroot]

Ah, I see. Thanks for clarifying this.

[rolikoff]

@nabeelio yep, the packaging/merging appcenter with Fody/Costura both unmanaged (e_sqlite3.dll) and managed sqlite assemblies is problematic. Appcenter just refuses to work and throws errors like "Failed to initialize sqlite3 provider". Wonder how this all will work with PublishSingleFile=true compiler param for .net core 3+, need to verify that.

[nabeelio]

@Jamminroot @rolikoff yup, you helped me solve that issue with Fody. When I last tried PublishSingleFile, it wasn't working, and I checked the .net core project file I have, it is with 2.0.2

[Jamminroot]

@nabeelio glad it worked

[myokeeh]

Has this been considered by the team?

[ALIENQuake]

Same issue: no data if I weave appcenter+dependencies into the main assembly via Fody/Costura. I cannot use Net Core PublishSingleFile.

[rolikoff]

Can someone please look at this issue/request? I think it breaks new cool features of dotnet such as PublishSingleFile. Developers really want to be able to pack their app into one single binary for easier distribution, but in that case, appcenter becomes useless.

[nabeelio]

@ibauersachs posted a workaround here, which I haven't tried yet: https://github.com/dotnet/runtime/issues/3735#issuecomment-709062731. Just wanted to highlight that this SQLite dependency is causing bundling issues on NET Core. I'm going to try this workaround soon, though. The comment above mentioned these two issues:

https://system.data.sqlite.org/index.html/tktview/ba4a3a4ac5e06a406b98f1cfc5ac517bef2a9f57 https://system.data.sqlite.org/index.html/tktview/02ec01b7c355d1e2fee5

@guperrot @Jamminroot are there any updates/will this be taken up?

[olmisa]

Hello @nabeelio , we have no concrete plans for this task and for now we can't say when this feature will be implemented. As described in this roadmap https://github.com/microsoft/appcenter/wiki/Roadmap we reduce work on a new features through mid-2021. Thanks

[EatonZ]

On the 4-year anniversary of this issue, I am guessing this will never be implemented since AppCenter is being retired in a year.

[DmitriyKirakosyan]

As we do not have plans to add support for this feature in the next year, I'm closing the issue.