[Bug] The "File -> Export -> Database to SQL File ..." doesn't export the values in the "sqlite_sequence" table

[oscaretu]

What did you do?

I choose "File -> Export -> Database to SQL File ..." doesn't export the values in the "sqlite_sequence" table and in the Tables box appears the table named "sqlite_sequence":

but in the exported file don't appear the values of the sequence counters, as they would appear when you use the .dump command from the sqlite command.

What did you expect to see?

I expected that the values of the sequence counter where exported in the backup. Using .dump in sqlite command the appear:

...
DELETE FROM sqlite_sequence;
INSERT INTO "sqlite_sequence" VALUES('cen_centro',2038);
INSERT INTO "sqlite_sequence" VALUES('tit_titulacion',2052);
INSERT INTO "sqlite_sequence" VALUES('uni_universidad',63);
COMMIT;

The sequence counters are created, because there are related to the PRIMARY KEYs

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "cen_centro" (
	"cen_codCentro"	INTEGER UNIQUE,
         .. (deleted fields to simplify) ...
	"cen_urlpisos"	VARCHAR,
	PRIMARY KEY("cen_codCentro" AUTOINCREMENT)
);

CREATE TABLE IF NOT EXISTS "tit_titulacion" (
	"tit_codTitulacion"	INTEGER DEFAULT Null UNIQUE,
	"tit_nombre"	VARCHAR,
	PRIMARY KEY("tit_codTitulacion" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "uni_universidad" (
	"uni_codUniversidad"	INTEGER DEFAULT Null UNIQUE,
	"uni_nombre"	VARCHAR,
	PRIMARY KEY("uni_codUniversidad" AUTOINCREMENT)
...

Without the correct sequence counter values, a failure will occur when inserting the next row, as a duplicate primary key error will be generated.

What did you see instead?

There is no values for sequences in the exported file.

DB4S Version

3.12.2

What OS are you seeing the problem on?

Windows, Linux

OS version

Windows 10 (native and under WSL2 with Ubuntu 22.04)

Relevant log output

No response

Prevention against duplicate issues

• I have searched for similar issues

[chrisjlocke]

There is no values for sequences in the exported file.

While noted, what impact do you feel this causes?
When you add a record, sqlite notes the missing table, and creates it. It finds the maximum value in the table, and creates that record too.
So for all intents and purposes, the missing table has no consequence.
While yes, it would be nice if it was there, it doesn't technically break anything.

I've tagged this and can leave it open, but as it has little impact on the usability, fixing this would be a low priority.

[oscaretu]

Hello @chrisjlocke

Anyone would expect a backup to represent the exact state of the database, not something that looks like the actual state of the database (but isn't). In the export form you can select the sqlite_sequence table, but then its values are not exported. And that is inconsistent. If the data is not going to be exported, modify the interface so that the user knows that it is not going to be exported.

When that backup is reloaded back into SQLite, values from sequence counters that were previously used could be reused again. Imagine a case where the last records added to the database have been deleted, And imagine that those IDs have been used in services external to the database. And that the external service uses the existence of those IDs in the database for its decision making. And that now an export is made. When reloading the database, previously used ID values would be generated again, which would make the external service believe that an ID is active when in fact it was deleted and a new record is reusing an old value.

In addition, if the exported file is to be used to load the backup into another database engine, it is important to have the values of the sequence counters, to ensure that the correct values are retrieved, without making assumptions about what SQLite does when.

In summary, for security, reliability, repeatability and to avoid unanticipated problems, if the user flags the sqlite_sequence table to be exported, its data should be exported.