Test code for SQLite session extension and UNIQUE constraints

build.sh

#!/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;
}