CODEWITHSUNDEEP
sqlite
Jonas
Jonas

Reputation: 74930

How do I find out whether any inserts/updates are pending?

I have a process writing some or no data to a database during a session. Since I need to log every commit and create a backup of the table, I would like to limit the number of commits, and particularly not do any empty commits.

To limit the number of commits, I run BEGIN TRANSACTION at the start of the session, so that I can do batch-wise commits. However, since I cannot know for sure how much data is being written during a session, is there a way to find out whether there have been any INSERTs/UPDATEs (or DELETEs) before I call COMMIT later?

One way to achieve what I want would be to (1) create a temporary back-up at the start of the session, (2) blindly commit, (3) compare the updated db with the back-up, (4a) if the two files differ, rename the backup, log the commit, or (4b) if the two files don't differ, and it's the end of the session, remove the backup.

I'd rather just not commit nothing in the firsts place. Can I somehow inspect the journal file to check whether there are any pending write operations?

edited to better explain that "just commit anyway" is not really the answer I'm looking for

Upvotes: 0

Views: 881

Answers (1)

GreenScape
GreenScape

Reputation: 7717

There is a way... but I doubt you'll like it. There are some pragmas that are a no-op inside of transaction. Having this knowledge you can determine, in a rather hacky way, if there is one:

sqlite> pragma foreign_keys;
0
sqlite> begin;
sqlite> pragma foreign_keys = 1;
sqlite> pragma foreign_keys;
0

As you can see foreign_keys is still 0, when trying to change it inside of transaction, but when we commit:

sqlite> commit;
sqlite> pragma foreign_keys = 1;
sqlite> pragma foreign_keys;
1

The value is gladly changed.

But seriously, follow CL's advice and just commit. It won't do any harm if there is nothing to commit!

Edit:

Now that you've edited your question so much, it becomes clear what you desire.Turns out, there is a way. sqlite3_total_changes() can aid you. You just need to store it's return value befroe the BEGIN and then compare it to the value returned just before COMMIT. Here's a small example:

sqlite3 * db = 0;
sqlite3_open(":memory:", &db);

sqlite3_exec(db, "CREATE TABLE foo(a);", 0, 0, 0);

std::cout << "(1) " << sqlite3_total_changes(db) << std::endl;

sqlite3_exec(db, "INSERT INTO foo(a) VALUES(1);", 0, 0, 0);

std::cout << "(2) " << sqlite3_total_changes(db) << std::endl;

sqlite3_exec(db, "INSERT INTO foo(a) VALUES(2);", 0, 0, 0);

std::cout << "(3) " << sqlite3_total_changes(db) << std::endl;

sqlite3_exec(db, "BEGIN;", 0, 0, 0);
std::cout << "(4) " << sqlite3_total_changes(db) << std::endl;

sqlite3_exec(db, "DELETE FROM foo;", 0, 0, 0);

std::cout << "(5) " << sqlite3_total_changes(db) << std::endl;

sqlite3_exec(db, "COMMIT;", 0, 0, 0);

std::cout << "(6) " << sqlite3_total_changes(db) << std::endl;

The output is:

(1) 0
(2) 1
(3) 2
(4) 2
(5) 4
(6) 4

Upvotes: 1

Related Questions