On Sat, Apr 27, 2013 at 12:54 AM, Scott Hess <sh...@google.com> wrote:
> Someone over here was trying to use a shared-cache in-memory database with
> two connections, one a read-write connection for purposes of populating
> things, the other a read-only connection for purposes of letting a
> (trusted) user make queries. They were surprised to find out that they
> could run write queries against the read-only handle (the insert query in
> my example). Also, if you flip the order of the opens so that the
> read-only open happens first, then the create statement fails
> with SQLITE_READONLY.
>
Readonly-ness is tracked at the pager level since it can vary from one
ATTACHed database to the next, and the pager is shared between two
connections with shared cache because the cache is part of the pager, so
the readonly setting is only honored for the first connection to open. The
second connection gets whatever the first connection had.
>
> [Aside: I suggested that it might be more appropriate to use an authorizer,
> rather than try to open an in-memory database read-only.]
>
> A couple minutes in the code makes me think that the sense of
> "cache=shared" is implemented at the btree.c layer, so it is somewhat
> unlikely that this is reasonable to actually support. That said, it is
> unexpected. It seems like the library should throw something like
> SQLITE_MISUSE when you request a shared-cache open incompatible with
> previous opens. WDYT?
>
> The same basic problem also appears to happen for on-disk databases, which
> makes me wonder if we're just doing something wrong.
>
> Thanks,
> scott
>
> ---
> /* gcc -g -o sqlmem sqlmem.c sqlite3.c */
>
> #include "sqlite3.h"
> #include <stdio.h>
>
> int main(int argc, char** argv) {
> sqlite3* rw;
> sqlite3* ro;
> int rc;
> char* err;
>
> rc = sqlite3_open_v2("file::memory:?cache=shared", &rw,
> SQLITE_OPEN_READWRITE | SQLITE_OPEN_URI, NULL);
> if (rc!=SQLITE_OK) {
> fprintf(stderr, "rw rc==%d\n", rc);
> return 1;
> }
>
> rc = sqlite3_open_v2("file::memory:?cache=shared", &ro,
> SQLITE_OPEN_READONLY | SQLITE_OPEN_URI, NULL);
> if (rc!=SQLITE_OK) {
> fprintf(stderr, "ro rc==%d\n", rc);
> return 1;
> }
>
> rc = sqlite3_exec(rw, "CREATE TABLE t (a TEXT)", NULL, NULL, &err);
> if (rc!=SQLITE_OK) {
> fprintf(stderr, "rw create error: %d/%s\n", rc, err);
> return 1;
> }
>
> rc = sqlite3_exec(ro, "SELECT 1", NULL, NULL, &err);
> if (rc!=SQLITE_OK) {
> fprintf(stderr, "ro select error: %d/%s\n", rc, err);
> return 1;
> }
>
> rc = sqlite3_exec(ro, "INSERT INTO t (a) VALUES ('a')", NULL, NULL,
> &err);
> if (rc!=SQLITE_OK) {
> fprintf(stderr, "ro insert error: %d/%s\n", rc, err);
> return 1;
> } else {
> fprintf(stderr, "ro insert succeeeded?\n");
> return 1;
> }
> }
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
