Hi! I'm using the SQLite session extension to create changesets, invert them and apply them to undo previous changes in the database. (Essentially what I need to do is persistent savepoints.)
This works well so far, but I recently wondered about the interaction with UNIQUE constraints. In particular, let's say that I record a changeset of the following modification: I remove some rows from a table, and *then* I insert new rows that have the same values in UNIQUE columns as the previously removed ones. That obviously works fine because I delete first and *then* insert. However, my understanding of how the session extension works (according to its docs) is that when I invert and apply the changeset, it will *first* insert the deleted rows, and *then* delete the inserted ones. (Because it inverts each operation but not the order of them.) I created a simplified example program that performs exactly this on an in-memory database. The code (plus the shell script to build it with the flags I used) is below and can also be found on Github: https://gist.github.com/domob1812/a3b78739772a1ff0c001be6cbc600f17 (Perhaps that is easier to view than in the email.) When I run that on my system (with SQLite 3.16.2 from Debian Stretch), I get errors printed from ErrorLogger like this: E0826 10:08:16.840441 21876 test.cpp:16] SQLite error (code 2067): abort at 16 in [INSERT INTO main."mytable" VALUES(?, ?)]: UNIQUE constraint failed: mytable.value E0826 10:08:16.840520 21876 test.cpp:16] SQLite error (code 2067): abort at 16 in [INSERT INTO main."mytable" VALUES(?, ?)]: UNIQUE constraint failed: mytable.value This matches what I expect. However, it seems that applying the changeset still works fine, and I get the correct "old" state restored. Is this just "by chance", or can I safely ignore these errors in this context and rely on the ability to apply inverted changesets even if they (intermittently) violate UNIQUE constraints? Thanks a lot for any insights! Yours, Daniel ================================= Build script: #!/bin/sh -e PKGS="sqlite3 libglog" CFLAGS="`pkg-config --cflags ${PKGS}` -std=c++14 -Wall -Werror -pedantic" CFLAGS="${CFLAGS} -std=c++14 -Wall -Werror -pedantic" CFLAGS="${CFLAGS} -DSQLITE_ENABLE_SESSION -DSQLITE_ENABLE_PREUPDATE_HOOK" LIBS=`pkg-config --libs ${PKGS}` g++ ${CFLAGS} ${LIBS} test.cpp -o test ================================= test.cpp: /* Test code for UNIQUE keys and inverting SQLite changesets. */ #include <sqlite3.h> #include <glog/logging.h> #include <cstdlib> #include <string> namespace { void ErrorLogger (void* arg, const int errCode, const char* msg) { LOG (ERROR) << "SQLite error (code " << errCode << "): " << msg; } void Execute (sqlite3* db, const std::string& sql) { VLOG (1) << "Executing SQL:\n" << sql; char* err; const int rc = sqlite3_exec (db, sql.c_str (), nullptr, nullptr, &err); if (rc != SQLITE_OK) LOG (FATAL) << "SQL error: " << err; sqlite3_free (err); } void Print (sqlite3* db) { const std::string sql = R"( SELECT `id`, `value` FROM `mytable` ORDER BY `id` ASC )"; sqlite3_stmt* stmt; CHECK_EQ (sqlite3_prepare_v2 (db, sql.c_str (), sql.size (), &stmt, nullptr), SQLITE_OK); while (true) { const int rc = sqlite3_step (stmt); if (rc == SQLITE_DONE) break; CHECK_EQ (rc, SQLITE_ROW); LOG (INFO) << " Row: (" << sqlite3_column_int (stmt, 0) << ", " << sqlite3_column_int (stmt, 1) << ")"; } CHECK_EQ (sqlite3_finalize (stmt), SQLITE_OK); } int AbortOnConflict (void* ctx, const int conflict, sqlite3_changeset_iter* it) { LOG (ERROR) << "Changeset application has conflict of type " << conflict; return SQLITE_CHANGESET_ABORT; } } // anonymous namespace int main () { LOG (INFO) << "Using SQLite version " << SQLITE_VERSION << " (library version: " << sqlite3_libversion () << ")"; CHECK_EQ (sqlite3_config (SQLITE_CONFIG_LOG, &ErrorLogger, nullptr), SQLITE_OK); sqlite3* db; CHECK_EQ (sqlite3_open (":memory:", &db), SQLITE_OK); LOG (INFO) << "Opened in-memory database"; Execute (db, R"( CREATE TABLE `mytable` (`id` INTEGER PRIMARY KEY, `value` INTEGER, UNIQUE (`value`)); INSERT INTO `mytable` (`id`, `value`) VALUES (1, 42), (2, 100); )"); LOG (INFO) << "Initial state:"; Print (db); /* Now we modify the table and record the result in a changeset. The modification is valid with respect to the UNIQUE constraint, but only because we delete the existing entries first and insert afterwards. */ sqlite3_session* session; CHECK_EQ (sqlite3session_create (db, "main", &session), SQLITE_OK); CHECK_EQ (sqlite3session_attach (session, nullptr), SQLITE_OK); Execute (db, R"( DELETE FROM `mytable`; INSERT INTO `mytable` (`id`, `value`) VALUES (3, 42), (4, 100); )"); LOG (INFO) << "Modified state:"; Print (db); /* Extract the changeset, invert it and apply the inverted changeset to undo the previous changes. This fails with the UNIQUE constraint violation because the order of delete/insert is wrong. */ int changeSize; void* changeBytes; CHECK_EQ (sqlite3session_changeset (session, &changeSize, &changeBytes), SQLITE_OK); sqlite3session_delete (session); int invertedSize; void* invertedBytes; CHECK_EQ (sqlite3changeset_invert (changeSize, changeBytes, &invertedSize, &invertedBytes), SQLITE_OK); sqlite3_free (changeBytes); LOG (INFO) << "Extracted inverted changeset"; LOG (INFO) << "Applying inverted changeset..."; CHECK_EQ (sqlite3changeset_apply (db, invertedSize, invertedBytes, nullptr, &AbortOnConflict, nullptr), SQLITE_OK); sqlite3_free (invertedBytes); LOG (INFO) << "Restored state:"; Print (db); CHECK_EQ (sqlite3_close (db), SQLITE_OK); return EXIT_SUCCESS; } ================================= -- https://www.domob.eu/ OpenPGP: 1142 850E 6DFF 65BA 63D6 88A8 B249 2AC4 A733 0737 Namecoin: id/domob -> https://nameid.org/?name=domob -- 3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz To go: Arc-Cav-Hea-Kni-Mon-Tou
signature.asc
Description: OpenPGP digital signature
_______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
