Bug: [SQLite] PrimaryKey with AUTOINCREMENT not recognized

[Kaizer69]

Bug Description

Hello. With a table created with DBSqlite Browser, after adding a primary key, with or without autoincrement, if the AUTOINCREMENT "specialword" is added on the column, RepoDB recognize the key as a primary key with autoincrement and skip filling the query with ID field.
Instead, with PRIMARY KEY added in the end of table (for example when you have multiple fields for primary key or with default creation of table with DBSqliteBrowser), RepoDb fill the ID with a value (0 for example, because the key is a not null integer, but it will be not valorized), then you get an exception regard duplicate keys.

Exception Message:

Error regard duplicate PrimaryKEY when inserting a new record.

Schema and Model:

CREATE TABLE "Articles" (
"ID" INTEGER NOT NULL UNIQUE,
"ArticleID" TEXT,
"Title" TEXT NOT NULL,
"Description" TEXT,
"Date_Added" INTEGER NOT NULL,
"Date_Fetched" INTEGER,
PRIMARY KEY("ID" AUTOINCREMENT)
)

[Table("Articles")]
public record EArticle {
[Primary]
public int    ID         { get; set; }
public string ArticleID  { get; set; }
public string Title      { get; set; }
public string Description { get; set; }

[PropertyHandler(typeof(DateTimeHandlers))]
public DateTime Date_Added { get; set; }

[PropertyHandler(typeof(DateTimeNullableHandlers))]
public DateTime? Date_Fetched { get; set; }
}

Library Version:

Example: RepoDb v1.12.7 and RepoDb.Sqlite

With that schema it works perfectly

CREATE TABLE "Articles" (
"ID" INTEGER NOT NULL PRIMARY KEY,
"ArticleID" TEXT,
"Title" TEXT NOT NULL,
"Description" TEXT,
"Date_Added" INTEGER NOT NULL,
"Date_Fetched" INTEGER,
)

[mikependon]

That's true, nice catch mate! The code on this line explains that, and now you give me problem. :)

[Kaizer69]

Ah well done!
As a workaround I try to add those attributes
[Primary,Key,Identity] but it wont work.

[mikependon]

The problem with SQLite is the fact that there is no sys tables that maintain these definitions, and that, we need to manually extract the identity column from the actual literal string schema extracted from the database.

Anyway, I should expect that putting an Identity attribute would help you on your use-case, if not, then that is an another bug.

For now, please use the "ColumnName Type AUTOINCREMENT" approach until the issue on the other schema implementation is rectified. Is that okay?

[Kaizer69]

Yes, as a workaround I created the table in another way.
I was surprised that no one had found it before that weird bug, I think that DBSqlite Browser is the most used tool for creating Sqlite databases.

[mikependon]

Thanks! This kind of issue is up for grabs as well, I guess, the changes to this issue are very limited, so it is safe for a PR. Please let me know if you wish to contribute 👍🏼 😉

[Kaizer69]

Sorry, at the moment I'm too busy to contribute.
Anyway thanks a lot for your project 🥇

[mikependon]

No worries, we can always issue the fix for you soon.

[Kaizer69]

Naaaaaa, don't worry. if there is a workaround, there is no rush to fix it :)
The same for the other bug regard PropertyMapperHandler with DateTime and DateTime? Just use the PropertyMapperAttribute for a temporany fix

[mikependon]

The fix has been introduced and it will be available on the next release. Please do let me know if you need a beta release for this.

[Kaizer69]

Well done! For me is not needed a beta. I will wait next stable release.