On 26/8/62 15:12, Daniel Kraft wrote:
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?
They can be ignored I think.
When sqlite3changeset_apply() hits a UNIQUE constraint, it puts the
change into a "retry buffer". Then, once it has attempted all changes in
the changeset, it goes back and retries those in the retry buffer. It
keeps retrying like this until no further progress can be made. So in a
case like yours - where there does exist an order in which the changes
can be successfully applied without hitting constraints - it eventually
succeeds in applying the entire changeset. But, as the sessions module
works through the normal SQL interface, each time it hits an
intermittent constraint, an error message is emitted on the log.
Dan.
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;
}
=================================
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
