On Sunday, 13 October, 2019 01:41, Adam Levy <theadaml...@gmail.com> wrote:
>One of the requirements for sqlite3_snapshot_get is that "One or more
>transactions must have been written to the current wal file since it was
>created on disk (by any connection). This means that a snapshot cannot be
>taken on a wal mode database with no wal file immediately after it is
>first opened. At least one transaction must be written to it first."
>I am looking for a simple way to ensure that this is the case.
>One way of course, is to simply start a write transaction with at least
>one write and roll it back. Simply doing BEGIN IMMEDIATE; COMMIT; does not
>appear to qualify although it does cause the WAL file to be created, it
>must not write a transaction to the WAL file. Creating a "test" table and
>adding a row and then rolling this back seems to work,
This does not work for me. I have to actually COMMIT the transaction to have a
transaction in the WAL file. Rolling back a transaction does not put a
transaction in the WAL file. The following works to retrieve a snapshot as it
writes a transaction to the WAL file, even though that transaction does not
change anything:
#include <sqlite3.h>
#include <stdio.h>
void main(void)
{
sqlite3* db = NULL;
sqlite3_stmt* stmt;
sqlite3_snapshot *sn = NULL;
int rc = 0;
int uv = 0;
char buf[128];
if (SQLITE_OK == sqlite3_open("test.db", &db))
printf("Database Opened\n");
// Test retrieving a snapshot structure
sqlite3_exec(db, "BEGIN", NULL, NULL, NULL);
rc = sqlite3_snapshot_get(db, "main", &sn);
printf("snapshot at open rc = %d (%d)\n", rc, (sn == NULL));
sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL);
// create a transaction on the user_version
sqlite3_exec(db, "BEGIN IMMEDIATE", NULL, NULL, NULL);
sqlite3_prepare_v2(db, "pragma user_version", -1, &stmt, NULL);
sqlite3_step(stmt);
uv = sqlite3_column_int(stmt, 1);
sqlite3_finalize(stmt);
snprintf(buf, sizeof(buf), "pragma user_version=%d", uv);
sqlite3_exec(db, buf, NULL, NULL, NULL);
sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
// Test retrieving a snapshot structure
sqlite3_exec(db, "BEGIN", NULL, NULL, NULL);
sn = NULL;
rc = sqlite3_snapshot_get(db, "main", &sn);
printf("snapshot after user_version rc = %d (%d)\n", rc, (sn == NULL));
sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL);
sqlite3_close(db);
}
Note that the "pragma user_version=..." is apparently processed at prepare
time, so you cannot use variable binding you have to dynamically create the
statement.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
