SQLite 3.7.15.2, Solaris and Windows (and presumably others). Journal mode is
delete, but WAL appears to behave in the same manner.
One of my coworkers ran into an infinite loop when accessing SQLite.
Essentially
an outer select loop was reading records, and an inner select loop was
inserting
records based on the records found into the same table. The loop never ended
as
the records inserted showed up in the select. This is not necessarily an
error,
of course. But if you take the script and the program below which reproduces
the problem, add "order by c3" to the select statement, it doesn't happen
(presumably because we're reading the materialized temporary table, not the
base
table at that point). This sort of inconsistency can be an unpleasant surprise.
If you look at SQL 92, it has a concept of INSENSITIVE cursors, which causes
the
cursor to logically (at least) work on a copy of the data, so that changes to
the underlying table are not visible. It would be nice if SQLite provided a
method to make a insensitive select ("order by rowid" appears to get silently
removed by the optimizer as you'd expect) less expensive than performing an
otherwise unnecessary order by.
Peter
create table base1 (c1 integer,c2 text,c3 integer);
insert into base1 (c1,c2,c3) values (1,'andrew',10);
insert into base1 (c1,c2,c3) values (1,'jeffrey',15);
insert into base1 (c1,c2,c3) values (1,'david',20);
insert into base1 (c1,c2,c3) values (1,'Jonathan',25);
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "sqlite3.h"
int main (int argc,
char *argv[])
{
sqlite3 *hdbc;
int s3err,rid,c3;
sqlite3_stmt *hupdate,*hselect;
char sql[256];
/* Open our test database (assumed to be named test.db). */
s3err = sqlite3_open_v2 ("test.db",
&hdbc,
SQLITE_OPEN_READWRITE,
NULL);
if (SQLITE_OK != s3err) {
fprintf (stderr,"Unable to open test.db, err = %d\n",s3err);
return EXIT_FAILURE;
}
/* Start a select on base1. */
s3err = sqlite3_prepare_v2 (hdbc,
"select rowid,c3 "
"from base1 "
"where c3 < 15 and c1 = 1",
-1,
&hselect,
NULL);
if (SQLITE_OK != s3err) {
fprintf (stderr,"Unable to prepare select on base1, err = %d\n",s3err);
return EXIT_FAILURE;
}
while ((s3err = sqlite3_step (hselect)) == SQLITE_ROW) {
rid = sqlite3_column_int (hselect,0);
c3 = sqlite3_column_int (hselect,1);
sprintf (sql,"insert into base1 (c1,c2,c3) values (1,'inactive',%d)",c3);
/* Insert into base1. */
s3err = sqlite3_prepare_v2 (hdbc,
sql,
-1,
&hupdate,
NULL);
if (SQLITE_OK != s3err) {
fprintf (stderr,"Unable to prepare insert on base1, err = %d\n",s3err);
return EXIT_FAILURE;
}
switch (sqlite3_step (hupdate)) {
case SQLITE_DONE:
case SQLITE_ROW:
printf ("%d:",rid);
fflush (stdout);
sqlite3_finalize (hupdate);
break;
default:
fprintf (stderr,"Error on sqlite3_step for insert, err = %d\n",s3err);
return EXIT_FAILURE;
}
}
sqlite3_finalize (hselect);
if (SQLITE_DONE != s3err) {
fprintf (stderr,"Error on sqlite3_step for select, err = %d\n",s3err);
return EXIT_FAILURE;
}
(void) sqlite3_close (hdbc);
return EXIT_SUCCESS;
}
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
