[svn.haxx.se] · SVN Dev · SVN Users · SVN Org · TSVN Dev · TSVN Users · Subclipse Dev · Subclipse Users · this month's index

Re: SQLite and callbacks

From: Stefan Sperling <stsp_at_elego.de>
Date: Tue, 30 Nov 2010 13:42:17 +0100

On Mon, Nov 29, 2010 at 07:43:40PM -0600, Hyrum K. Wright wrote:
> 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?

If you run this version of your test, it will run in an endless loop.
The callback inserts new values, the caller will see those and invoke
the callback again.

We need a way to prevent writes done within the callback from being
visible to the caller.

#include <stdio.h>
#include <string.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 = "insert into foo values (?1,?2);";
  sqlite3_stmt *stmt;
  const unsigned char *msg = "new message for you!";

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

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

  sqlite3_bind_int(stmt, 1, 42);
  sqlite3_bind_text(stmt, 2, msg, strlen(msg) + 1, SQLITE_STATIC);
  CHECK_ERR;

  sqlite3_step(stmt);
  CHECK_ERR;

  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;
}
Received on 2010-11-30 13:43:01 CET

This is an archived mail posted to the Subversion Dev mailing list.