[Bug]: DB Browser is ignoring the sqlite_sequence

[bwminick]

It appears that DB Browser is ignoring the sqlite_sequence for a table when creating new records.

Here's how to replicate it:

• Create a new database.

• Create a new Employees table with the following fields:

  • id (primary key, autoincrement, unique not null)
  • firstName (text)
  • lastName (text)

• Go to the new sqlite_sequence table.
• Add a new row with the values: Employees (for the name field) and 1000 (for the seq field).
• Write Changes
• Go to the Employees table
• Add a new row (via the "Insert a new record in the current table" toolbar button with values of your choosing.
• Notice that the ID value is 1, instead of 1001.
• Now, add a second row (this time via sql commands) instead.

You'll notice that the executed sql is following the sqlite_sequence table.

Expected Results:
The first record's ID should be 1001. The second record's ID should be 1002. However, the "Insert a new record in the current table" toolbar button is ignoring the sqlite_sequence table.

[chrisjlocke]

Its the 'NN' option which is causing DB4S to choose a number for you.
In the application log it shows

SELECT "_rowid_",* FROM "main"."a" LIMIT 0, 49999;
SELECT MAX(CAST("Field1" AS INTEGER)) FROM "main"."a"
INSERT INTO "main"."a"("Field1","Field2","Field3") VALUES (10001,NULL,NULL);

If you remove the NN option, it'll work as you expect.

This 'kind of' makes sense, but yes, it's a bug as you describe. An empty record has to be created, and usually DB4S just adds a NULL record for all fields. However, with the NN set, this could fail, so to assume uniqueness, the maximum number for the primary key is found, and one added to it, rather than looking to see if there is a sequence table.

Note that once you've entered data anyway, SQLite itself ignores this sequence table and uses the 'find the max and add one to it' method. Set your sequence back down to 300, add a record, and it'll be 1002, with the sequence table updated to 1002 too. So yes, it's a bug but also how SQLite works too.

[MKleusberg]

Thank you for reporting this! It should be fixed in the next nightly build. Can you download that tomorrow and see if it works for you? 😄 The download will be available here.

[MKleusberg]

@bwminick Did the nightly build work for you?

[chrisjlocke]

Closing due to lack of response.