Update to SQLitePCL.raw 1.1.12

[bricelam]

A new version of SQLitePCL.raw is available which includes SQLite version 3.26.0.

Some highlights:

• ALTER TABLE..RENAME COLUMN
• Better ALTER TABLE..RENAME TO (cascades into triggers and views)
• Several query optimizations and performance improvements
• UPSERT
• Window functions

[bricelam]

Looks like we forgot to update the submodule for 2.1 so this didn't actually ship in 2.1.8. Moving it to the 2.2.2 milestone. The 2.1 branch has this change, so it should ship whenever we patch 2.1 again.

[Tristan-gu]

Hi @bricelam ,
Is SQLitePCL.raw has been updated in EFCore 2.2.2 and 3.0 preview ?

Because I'm using EFCore 2.2.3 with Microsoft.EntityFrameworkCore.Sqlite 2.2.3, and when I create my database, and query it with 'select sqlite_version()', in SQLite Studio, I have "3.20.1" version.

So, I can't use RENAME COLUMN.
Do you know if it's normal ?

[bricelam]

Have you installed the corresponding version (to 2.2.2) of the .NET Core SDK? ASP.NET Core projects can do weird things when versions aren’t aligned.

[Tristan-gu]

Thank you for your answer bricelam , but I'm not using ASP.NET core project, but Xamarin project.
And my Xamarin project is linked to a .NetStandard library with theses NuGet packages. (this is ths library which creates the SQLite database)

Maybe I'm doing something wrong, but this is my query to show the SQLite version

PS: I tried also to migrate all my NuGet Packages to preview version, the result is the same, the SQLite version is always "3.20.1"

[bricelam]

It that your app executing the query or another tool? Each tool brings its own version of SQLite.

[Tristan-gu]

I'm using SQLite Studio to make this query.

If you're right, I'm really sorry, but I don't understand why a query 'select sqlite_version();' executed on SQLiteStudio can returns me a internal SQLite version.

So, do you know how to know the SQLite database version ?
Because If I write here, it's because the 'Rename column' EFCore migration doesn't work for me, even if EFCore 2.2.3 is supposed to have a compatible SQLite version with that.

[bricelam]

In your app, check the value of new SqliteConnection().ServerVersion.

[bricelam]

Are you using DbContext.Database.Migrate() to execute the column rename? Or are you copying the script into SQLite Studio (which appears to be using an older version of SQLite that doesn't support column renames)?

[bricelam]

I don't understand why ... SQLiteStudio can returns me a internal SQLite version.

SQLite is an in-process database. Each process starts it's own version of the SQLite server to read and write to database files.

[Tristan-gu]

Are you using DbContext.Database.Migrate() to execute the column rename? Or are you copying the script into SQLite Studio (which appears to be using an older version of SQLite that doesn't support column renames)?

I'm using DbContext.Database.Migrate() in my UWP Xamarin app, and open the SQLite database with SQLiteStudio.
I have the same error like here (#13341), this is why I'm here to ask

[bricelam]

Can you submit a new issue so we can investigate further?

[Tristan-gu]

In your app, check the value of new SqliteConnection().ServerVersion.

When I execute this on UWP projet, I have 3.20.1 version too.
Ok I will submit a new issue.

Thank you

[Tristan-gu]

Sorry, it was my fault, I removed and cleaned all my nuget dependencies, and I think I had a mix between EFCore dependencies and other nuget which use also SQLite.

Thank you

[plamenkoyovchev]

Guys, I am using Microsoft.EntityFrameworkCore.Sqlite 2.2.3 and I am getting "SQLite Error 1: 'near "COLUMN": syntax error'." when Migrate() method is executed from my App.cs in Xamarin.Forms.

{Microsoft.Data.Sqlite.SqliteException (0x80004005):
 SQLite Error 1: 'near "COLUMN": syntax error'.\n  at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC (System.Int32 rc, SQLitePCL.sqlite3 db) [0x00067] in <3b661b9701514fe1a13c7889d821c94a>:0
  \n  at Microsoft.Data.Sqlite.SqliteCommand+<PrepareAndEnumerateStatements>d__62.MoveNext () [0x0008a] in <3b661b9701514fe1a13c7889d821c94a>:0 \n 
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader (System.Data.CommandBehavior behavior) [0x00282] in <3b661b9701514fe1a13c7889d821c94a>:0 \n 
    at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader () [0x00000] in <3b661b9701514fe1a13c7889d821c94a>:0 \n 
     at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery () [0x00042] in <3b661b9701514fe1a13c7889d821c94a>:0 \n
       at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute (Microsoft.EntityFrameworkCore.Storage.IRelationalConnection connection

My Migration should rename column name:

 protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.RenameColumn(
                name: "PostalCode",
                table: "Addresses",
                newName: "PostalCodeNumber");
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.RenameColumn(
                name: "PostalCodeNumber",
                table: "Addresses",
                newName: "PostalCode");
        }

Executing new SqliteConnection().ServerVersion returns 3.24.0 ? Why it is not 3.26.0 ?

[bricelam]

We got to the bottom of why this hasn't made into 2.1. PR #15369 should put it in version 2.1.11.

[Tristan-gu]

Hi,

I finally found why I had 3.20.1 SQLite version.
This is was because I tried to get a SQLite Encryption.

So, When I added SQLitePCLRaw.bundle_sqlcipher As you say @bricelam here.

And, only when I have these dependencies in my project, the SQLite versioncreated is 3.20.1.

If I remove SQLitePCLRaw.bundle_sqlcipher, the SQLite version is 3.26.0.

I don't understand why because it is specified here that the SQLite version should be 3.26.0

Do you have an idea of what's happening ?

[bricelam]

@Tristan-gu If you're sure the version in SQLitePCLRaw.bundle_sqlcipher is wrong, you can file an issue on ericsink/SQLitePCL.raw

[bricelam]

@Tristan-gu Looks like this is a known issue: ericsink/SQLitePCL.raw#244

[Tristan-gu]

Thank you very much for your help, I will wait for a new SQLitePCLRaw.bundle_sqlcipher version.

[bricelam]

This finally shipped today in version 2.1.11.

(Note, it first shipped in version 2.2.2 four months ago.)

[thomaslangenkamp]

What does this mean for the EF Core community waiting on SQLite version 3.25.0?

How does one obtain SQLite version 3.25.0, what nuget packages added/updated?

The newest SQLite nuget package is 3.13.0, and I have Microsoft.EntityFrameworkCore.Sqlite 2.1.11
I tried "dotnet ef database update" (with a column rename in migrations) with SQLite 3.13.0 and Microsoft.EntityFrameworkCore.Sqlite 2.1.11 and the same command with only Microsoft.EntityFrameworkCore.Sqlite 2.1.11, but neither worked. I couldn't seem to get it to work with Microsoft.EntityFrameworkCore.Sqlite.Core either, were there more dependencies I missed?

Trying to apply the patch for the issue: "SQLite does not support this migration operation ('RenameColumnOperation'). For more information, see http://go.microsoft.com/fwlink/?LinkId=723262."

I also followed the instructions here, and downloaded and added the sqlite3.dll: #13341 (comment)

But, got the error:
An error occurred while accessing the IWebHost on class 'Program'. Continuing without the application service provider. Error: Unable to load DLL 'sqlite3' or one of its dependencies: The specified module could not be found. (Exception from HRESULT: 0x8007007E)
Unable to create an object of type 'MarketingSuppliesDbContext'. Add an implementation of 'IDesignTimeDbContextFactory' to the project, or see https://go.microsoft.com/fwlink/?linkid=851728 for additional patterns supported at design time.