Committed data is lost silently.

[rkoe]

Details

If you enter some data manually into SQLiteStudio and click the commit-button, the data sometimes is silently lost by SQLiteStudio: SQLiteStudio still shows the data, but the data was not committed to the database and disappears after a "refresh".

Maybe this only happens when adding new rows, and maybe only with automatically generated unique ids, but I have not checked this in detail.

Workarounds:

• Constantly manually check if the "committed" data is really in the database (=reload after every single commit), and constantly re-enter the lost data.
• Use a different graphical SQLite-frontend instead of SQLiteStudio.

Steps to reproduce

• Use a database with a unique, auto-incrementing primary key id.
• Add rows and enter data (but don't enter anything into the id-field).
• Commit
• Reload, and check if data has been lost.

SQLiteStudio version

3.2.1

[pawelsalawa]

This is a first time someone reports such problem.
Unfortunately it never happens to me, so I would need a recipie for how to reproduce a case.

[rkoe]

After running SQLiteStudio with --debug options, I now have a logfile, and can completely reproduce the issue:

It's unfortunately again caused by SQLiteStudio trying to be smarter than the database... :( I think the SQL-part of SQLiteStudio would need a complete redesign to really solve all these bugs.

SQLiteStudio uses an invalid ROWID when committing data:

• Use a table with an integer id (primary key, autoincrement, unique, not null) and some other fields.
• Probably use a high value for the auto-incrementing id.
• Add a row, leaving the "id"-field empty; commit.
• SQLiteStudio now shows this row with a negative (wrong) id.
• Edit the row; commit.
• SQLiteStudio now commits the data with the wrong negative id, and so the data is lost.

Example database-dump:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, text VARCHAR);
DELETE FROM sqlite_sequence;
INSERT INTO sqlite_sequence VALUES('test',20191202069);
COMMIT;

SQLiteStudio debug output:

"SQL test-lost-data> BEGIN;" (flags: "" )
"SQL test-lost-data> INSERT INTO test (text) VALUES (:arg2)" (flags: "" )
    SQL arg> 0 = QVariant(QString, "foo")
"SQL test-lost-data> COMMIT;" (flags: "" )

"SQL test-lost-data> BEGIN;" (flags: "" )
"SQL test-lost-data> UPDATE main.test SET text = :value_0 WHERE ROWID = :rowIdArg0;" (flags: "" )
    SQL arg> ":value_0" = QVariant(QString, "bar")
    SQL arg> ":rowIdArg0" = QVariant(int, -1283634412)
"SQL test-lost-data> COMMIT;" (flags: "" )

The -1283634412 is wrong here.

[pawelsalawa]

Thank you for the reproduction steps. Actually it won't require "complete redesign". It looks like relatively simple bug. I managed to reproduce it, so it will surely be fixed. Thanks!

[rkoe]

Thanks.

This bug alone does not require a SQL-layer redesign. But since it's at least the 5th bug, which results from the same SQLiteStudio-SQL-layer flaw, and since "trying to be smarter than the database" and "guessing values" is never a good idea, I guess that there are quite some more bugs because of this flaw; and the cleanest way to solve them would be to redesign the SQLiteStudio-SQL-layer by doing no "SQL-optimizing" stuff, and simply directly committing the data to the database and directly asking the database for the data.

[pawelsalawa]

I know it all seams very simple and easy when looking from outside.

What about editing results of complex query with joins and subqueries? Would you rather refuse it?
This is just one basic example, whereas tons of features, that SQLiteStudio provides is based on this SQL preprocessing.

Bugs are natural outcome of software development. If particular project can effort dedicated testers and time for developers - they get fixed sooner and software becomes better overall. I simply don't have as much time as I used to. I honestly do hope I will have more time again in some near future, because I did not let this project go yet. I like working on it.

Lastly but not leastly, this particular bug reported above is not related to SQL pre-processing at all. Editing data for simple table (not query results) does not use pre-processing.