Repodb: Bug: Inserting only specific columns does not work on SQLite

[snalesso]

This problem is present on SQLite, all the tries work on MS SQL Server

Here and here are shown samples of how to execute .Insert/.InsertAsync specifying data for some columns only, using the fields parameter.

I tried to use this feature providing all the columns info but the Id (in order to let the db generate it) but it does not work: every time I specify the fields parameter I get the following exception:

"The non-identity primary field must be present during insert operation."

If I provide the Id the .Insert/.InsertAsync doesn't fail but the Id is not generated by the DB, it keeps the provided value, also accepting 0 as a valid Id.

The only version that correctly lets the DB generate the Id is the hard-coded INSERT INTO <table> (<columns>) VALUES (<values>), which also returns the correct generated Id.

This is my sample:

• RepoDb (1.12.0-beta3)
• RepoDb.SqLite (1.1.0-beta1)

Just copy this code in the Program.cs of a .NET Core 3.1 Console App

Source Code

using System;
using System.Data.SQLite;
using System.Threading.Tasks;
using RepoDb;

namespace RPT.UI.WinConsole2
{
    static class Program
    {
        static async Task Main(string[] args)
        {
            SqLiteBootstrap.Initialize();

            var conn = new SQLiteConnection(@"Data Source=:memory:;Version=3;");
            await conn.OpenAsync();
            await conn.ExecuteNonQueryAsync(CreateDatabaseScripts.EnsurePersonTable);

            var person = new Person { Name = "Cal", Surname = "Crutchlow" };
            var addedFields = Field.Parse<Person>(x => new
            {
                x.Name,
                x.Surname
            });
            var insertPersonSql = $"insert into [{nameof(Person)}] " +
                $"(" +
                $"  [{nameof(Person.Name)}]" +
                $", [{nameof(Person.Surname)}]" +
                $") values (" +
                $"  '{person.Name}'" +
                $", '{person.Surname}'" +
                $")";

            person.Name += "_2";
            uint id;
            ushort i = 1;

            var affectedRows = await conn.ExecuteNonQueryAsync(insertPersonSql).TryCatch(i++);
            // works, inserts correct data, db generates Id

            var dynamicPerson_no_Id = new { Name = "Cal", Surname = "Crutchlow" };
            id = await conn.InsertAsync<dynamic, uint>(nameof(Person), entity: dynamicPerson_no_Id).TryCatch(i++);
            // {"The non-identity primary field must be present during insert operation."}

            var dynamicPerson_with_Id = new { Id = 13, Name = "Jack", Surname = "Brown" };
            id = await conn.InsertAsync<dynamic, uint>(nameof(Person), entity: dynamicPerson_with_Id).TryCatch(i++);
            // works, inserts the Id into the dynamic, returns correct Id, but it is the provided Id, it is not generated by the DB

            dynamicPerson_with_Id = new { Id = 17, Name = "Bruce", Surname = "Wayne" };
            id = await conn.InsertAsync<dynamic, uint>(nameof(Person), entity: dynamicPerson_with_Id, fields: addedFields).TryCatch(i++);
            // {"The non-identity primary field must be present during insert operation."}

            person.Id = 0;
            id = await conn.InsertAsync<Person, uint>(nameof(Person), entity: person).TryCatch(i++);
            // inserts Id = 0, returns correct inserted Id

            person.Id = 37;
            id = await conn.InsertAsync<Person, uint>(entity: person).TryCatch(i++);
            // inserts id = 37, returns correct inserted Id

            id = await conn.InsertAsync<dynamic, uint>(nameof(Person), entity: dynamicPerson_no_Id, fields: addedFields).TryCatch(i++);
            // {"The non-identity primary field must be present during insert operation."}

            id = await conn.InsertAsync<uint>(nameof(Person), entity: dynamicPerson_no_Id, fields: addedFields).TryCatch(i++);
            // {"The non-identity primary field must be present during insert operation."}

            id = await conn.InsertAsync<Person, uint>(nameof(Person), entity: person, fields: addedFields).TryCatch(i++);
            // {"The non-identity primary field must be present during insert operation."}

            id = await conn.InsertAsync<Person, uint>(person, fields: addedFields).TryCatch(i++);
            // {"The non-identity primary field must be present during insert operation."}

            Console.ReadLine();
        }
    }

    public class Person
    {
        public uint Id { get; set; }
        public string Name { get; set; }
        public string Surname { get; set; }
    }

    public static class CreateDatabaseScripts
    {
        public static string EnsurePersonTable = $"create table if not exists [{nameof(Person)}] (" +
            string.Join(",",
                $"[{nameof(Person.Id)}] integer primary key",
                $"[{nameof(Person.Name)}] text not null",
                $"[{nameof(Person.Surname)}] text not null") +
            $");";
    }

    public static class TaskMixins
    {
        public static async Task TryCatch(this Task task, ushort i)
        {
            Console.WriteLine($"#{i++}");

            try
            {
                await task;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                Console.WriteLine("-----------------------");
            }
        }

        public static async Task<T> TryCatch<T>(this Task<T> task, ushort i)
        {
            Console.WriteLine($"#{i++}");

            try
            {
                return await task;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                Console.WriteLine("-----------------------");
            }

            return default;
        }
    }
}

[mikependon]

@snalesso - would you be able to share your table schema (the CREATE TABLE statement)? We are happy to give you feedback very soon about this issue.

EDIT: Thank you for writing the great User Story here. It is easy to understand and follow it.

[snalesso]

SQLite3 DB schema is included in the source code I provided. To speed up here it is:

public static class CreateDatabaseScripts
{
    public static string EnsurePersonTable = $"create table if not exists [{nameof(Person)}] (" +
        string.Join(",",
            $"[{nameof(Person.Id)}] integer primary key",
            $"[{nameof(Person.Name)}] text not null",
            $"[{nameof(Person.Surname)}] text not null") +
        $");";
}

which translates into:

create table if not exists [Person]
(
    [Id] integer primary key,
    [Name] text not null,
    [Surname] text not null
)

[mikependon]

It seems the ID column is not an identity column? Can you create a table like this.

create table if not exists [Person]
(
    [Id] integer primary key autoincrement,
    [Name] text not null,
    [Surname] text not null
)

Auto-increment makes your table generate a new identity value everytime you insert a new row to it.

[mikependon]

The non-identity primary field must be present during insert operation.

This is a customized exception in the Statement Builder. It tries to ensure that you should provide a value to the non-identity primary key.

[snalesso]

Auto-increment makes your table generate a new identity value everytime you insert a new row to it.

Actually it is.
SQLite3 will always treat INT PRIMARY KEY as identity column, auto-generating the Id.
When you specify AUTOINCREMENT you tell SQLite3 that new Ids must not reuse old, lower Ids values, so there's always an incremental progression.
For example, if there are rows with Ids: 1, 2, 3, 4, 5, you remove the 3 and add a new item, 3 is available to be used as Id but the new item will be 6. 3 will never be used again.
This adds CPU work because the DB checks the highest value at each Id generation, also when the max INT value is reached no more Ids can be generated even though there are values not in use.

For reference: official AUTOINCREMENT documentation

[mikependon]

@snalesso - Cool, never thought the item 3 in the link you provided. Thank you for leading us there. Then this as a bug!

The IDbHelper object of RepoDb.SqLite is using the AUTOINCREMENT keyword as an identification for the identity, which I think we can simply be replaced or modified by adding an additional 'OR' condition for the PRIMARY KEY / INTEGER. See this code line.

Anyway, I will fix this on the next upcoming release. But if you are adventurous enough, you can advancely test it by simply injecting your own DbHelper.

Follow these steps:

1. Copy the entire class from here and place it on your project solution. Maybe name it CustomSqLiteDbHelper.
2. Replace the AUTOINCREMENT with PRIMARY KEY. Note: Ignore the data type for now, just for your own use-case.
3. Then, simply inject your customized database helper class like below. Ensure to call it after the RepoDb.SqLiteBootstrap.Initialize().

DbHelperMapper.Add(typeof(SQLiteConnection), new CustomSqLiteDbHelper(), true);

[snalesso]

Did a fast test but it solved only partially: now it recognizes that the identity/primary was provided but it inserts the data into the DB only if:
Id is provided AND if fields are passed they contain Id

also, Id = 0 is accepted

[mikependon]

It seems not properly parsed. I will issue you a new beta release for this. When do you need the fix?

[snalesso]

I have no hurry, I'm more focused on the app structure for the moment (which is still far from release) and I can use MSSQLS for now since the DB schema is still simple.
But for sure I will use SQLite as final engine.

[mikependon]

I am yet to verify whether this last_insert_rowid() call is the correct call. It seems to be not working as expected for INTEGER PRIMARY KEY.

[snalesso]

It is

[mikependon]

LOL. I am into other issue now, but thanks @snalesso for the research. I will do create Integration Tests later. I maybe can give you the beta by Saturday (or even before that).

[mikependon]

Spent my time on this and it seems it is not working as expected. And/or the last_insert_rowid() may not be the correct function. I could not think of anything else for now, it seems to be an invalid behavior from SQLite. Or else, maybe you can help identify by simply cloning the project and running the Integration Tests.

On this line, I would expect that the last_insert_rowid() would return 3, but it is returning 11. Below is the overview of that Integration Test.

• Create 10 SdsCompleteTable models (with Id 1-10).
• InsertAll the 10 models.
• Check the count equality of created rows (10).
• Delete the 3rd row (with Id = 3).
• Check the count equality after deletion (9).
• Create 1 SdsCompleteTable model (with Id 1).
• Insert the newly created model (with all fields except the "Id").
• Assert the result of the insert, expecting 3, but the result is 11.

Here is the model, and here is the table creation script.

[mikependon]

The database fields returned is also correct based on the new logic implied INTEGER PRIMARY KEY and/or AUTOINCREMENT.

var dbFields = DbFieldCache.Get(connection, ClassMappedNameCache.Get<SdsCompleteTable>(), null);

It even says true.

[mikependon]

The RepoDb.SqLite v1.1.0-beta2 is now available with the recent fixes in relation to INTEGER PRIMARY KEY.

[mikependon]

Closing this ticket now. Please feel free to revisit if you still have problems. Thanks