Strict mode tables not working, syntax error

[voltagex]

tl;dr can you please update the underlying sqlite library to at least 3.37.0?

CREATE TABLE ... STRICT does not appear to be working in Microsoft.Data.Sqlite

See https://www.sqlite.org/stricttables.html. This feature requires SQLite 3.37.0 (2021-11-27)

Command line:

 sqlite3.exe -cmd "CREATE TABLE MyTable (MyID INTEGER) STRICT;"
SQLite version 3.37.1 2021-12-30 15:30:28
Enter ".help" for usage hints.
sqlite> .schema MyTable
CREATE TABLE MyTable (MyID INTEGER) STRICT;
sqlite> .quit

(via LinqPad, using statements not included )

void Main()
{
	var connection = new SqliteConnection("Data Source=:memory:");
	connection.Open();
	var command = connection.CreateCommand();
	command.CommandText = "CREATE TABLE MyTable (MyID INTEGER) STRICT";
	command.ExecuteNonQuery();
}

Include stack traces

SQLite Error 1: 'near "STRICT": syntax error'.
at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteCommand.GetStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
   at UserQuery.Main(), line 7

Include version information

Microsoft.Data.Sqlite version:

I have tried

Microsoft.Data.Sqlite 6.0.5
SQLitePCLRaw.lib.e_sqlite3 2.0.6
SQLitePCLRaw.provider.e_sqlite3 2.0.6

and
Microsoft.Data.Sqlite 7.0.0-preview.4.22229.2
SQLitePCLRaw.bundle_e_sqlite3 2.0.8-pre20220111224339
SQLitePCLRaw.core 2.0.8-pre20220111224339

SELECT sqlite_version() from the included library shows 3.35.5 for both stable and prerelease

Target framework: (e.g. .NET 5.0): .NET 6 or .NET 7
Operating system: Windows 11

[voltagex]

Side question: how is Microsoft.Data.Sqlite built? I don't see it in the GitHub actions - for the experiments and side projects I'm working on, I'd be happy to build my own with a newer sqlite.

[roji]

@voltagex the CI build is done using Azure Pipelines, so see azure-pipelines.yml in the repo root if you're interested. However, for building locally see these instructions (the CI shouldn't really be relevant for you).

@bricelam can provide an answer for the the SQLLite version.

[voltagex]

@roji Thanks! Looked straight past the azure-pipelines.yml. Oops!

Thanks for the pointer to the local build instructions, too.

[voltagex]

It looks like the process to update e_sqlite3.dll is quite involved and seemingly requires a build of https://github.com/ericsink/SQLitePCL.raw. cc @ericsink

Edit: Maybe only the Microsoft.Data.Sqlite.Core dependency version needs an update to 2.1.0?

<PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="2.1.0" /> in my csproj file and the STRICT modifier is working.

[ajcvickers]

@voltagex As you say, the new version requires a new PLC.raw package. As such, this is an external issue--we will automatically update the EF provider once a new version is available.

[voltagex]

@ajcvickers there's an updated version, https://www.nuget.org/packages/SQLitePCLRaw.core/ is at 2.1.0

[ajcvickers]

@voltagex And that update has now been picked up: 5fc5762

[bricelam]

Note, you can also use the 2.1.0 version of SQLitePCLRaw with older versions of Microsoft.Data.Sqlite.

<PackageReference Include="Microsoft.Data.Sqlite" Version="6.0.5" />
<PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="2.1.0" />

[ErikEJ]

But does 2.1 include the updated SQLite engine and how would one know?

[ericsink]

@ErikEJ I think maybe one answer to that is "look at the SQLitePCLRaw release notes".

Which I forgot to do for 2.1. :-(

My bad. I'll fix that.

[ericsink]

@ErikEJ Done: https://github.com/ericsink/SQLitePCL.raw/releases/tag/v2.1.0