SQLite and callbacks

[Hyrum K. Wright]

We use callbacks extensively throughout our code as a means of
providing streamy feedback to callers.  It's a pretty good paradigm,
and one that has served us well.  We don't put many restrictions on
what the callbacks can do in terms of fetching more information or
calling other functions.

Enter wc-ng.

Stefan's patch to make a recursive proplist much more performant
highlights the great benefit that our sqlite-backed storage can have.
However, he reverted it due to concerns about the potential for
database contention.  The theory was that the callback might try and
call additional wc functions to get more information, and such nested
statements weren't healthy for sqlite.  We talked about it for a bit
in IRC this morning, and the picture raised by this issue was quite
dire.

In an attempt to find out what the consequences of these nested
queries are, I wrote a test program to attempt to demonstrate the
failure, only now I can't seem to do so.  Attached is the test
program, but when I run it, I'm able to successfully execute multiple
prepared statements on the same set of rows simultaneously, which was
the concern we had about our callback mechanism in sqlite.

So is this a valid problem?  If so, could somebody use the attached
test program to illustrate it for those of us who may not fully
understand the situation?

Thanks,
-Hyrum

#include <stdio.h>

#include <sqlite3.h>

#define CHECK_ERR  \
  if (sqlite3_errcode(db) \
        && (sqlite3_errcode(db) != SQLITE_ROW) \
        && (sqlite3_errcode(db) != SQLITE_DONE))  \
    fprintf(stderr, "%d: %d: %s\n", __LINE__, sqlite3_errcode(db), sqlite3_errmsg(db));

#define TEST_DATA \
  "create table foo (num int, message text); "  \
  "" \
  "insert into foo values (1, 'A is for Allegator'); " \
  "insert into foo values (2, 'B is for Bayou'); " \
  "insert into foo values (3, 'C is for Cyprus Trees'); " \
  "insert into foo values (4, 'D is for Dew'); " \
  "insert into foo values (5, 'E is for Everything like'); " \
  "insert into foo values (6, 'F Ferns or'); " \
  "insert into foo values (7, 'G Grass that''s'); " \
  "insert into foo values (8, 'H Home to you'); " \
  ""

void callback(sqlite3 *db, int num)
{
  const char *query = "select message from foo where num = ?;";
  sqlite3_stmt *stmt;
  const unsigned char *msg;

  printf("Got number: %d, now getting message\n", num);

  sqlite3_prepare_v2(db, query, -1, &stmt, NULL);
  CHECK_ERR;

  sqlite3_bind_int(stmt, 1, num);
  CHECK_ERR;

  sqlite3_step(stmt);
  CHECK_ERR;

  msg = sqlite3_column_text(stmt, 0);
  CHECK_ERR;

  printf("Message: %s\n", msg);

  sqlite3_finalize(stmt);
  CHECK_ERR;
}

void get_numbers(sqlite3 *db,
                 void (*callback)(sqlite3 *, int))
{
  const char *query = "select num from foo;";
  sqlite3_stmt *stmt;
  int code;

  sqlite3_prepare_v2(db, query, -1, &stmt, NULL);
  CHECK_ERR;

  code = sqlite3_step(stmt);
  CHECK_ERR;
  while (code == SQLITE_ROW)
    {
      int number = sqlite3_column_int(stmt, 0);
      callback(db, number);

      code = sqlite3_step(stmt);
      CHECK_ERR;
    }

  sqlite3_finalize(stmt);
  CHECK_ERR;
}


int
main(int argc, char *argv[])
{
  sqlite3 *db;

  remove("test.db");

  sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
                  NULL);
  CHECK_ERR;

  sqlite3_extended_result_codes(db, 1);
  CHECK_ERR;

  sqlite3_exec(db, TEST_DATA, NULL, NULL, NULL);
  CHECK_ERR;

  get_numbers(db, callback);

  sqlite3_close(db);

  return 0;
}