Re: [sqlite] Enabling shared-cache, breaking attach database

2012-07-16 Thread Keith Medcalf 

You can open a :memory: database for the main database, then ATTACH your other 
database files.  This works just fine without shared cache and does achieve 
your objective of always permitting access using qualified names.  Be aware 
though that using unqualified names for a select will use the name found in the 
firstly attached database in which it exists (though I do not know if the 
search order is guaranteed).  

Unqualified creates will create the object in the memory database.  Except for 
associated items, of course, which are created in the attached database in 
which they were found (I believe -- I've always created the database schema 
using a direct connection and only used the multiple attached database files 
for DML, not to create new objects).  I've done this many times by writing an 
"opener" for existing databases all located in a single directory that opens 
:memory: first, then attaches every database found in the directory.  Haven't 
tried with a shared-cache through.

If you want to use a single shared memory cache, you should be able to open a 
shared-memory database as the "main" database then attach the database files in 
shared-cache mode by using URI format names:

Open "file::memory:?cache=shared" for the "main" database, then attach each of 
the other databases using

ATTACH 'file:filename?cache=shared' as dbname;

You have to have compiled the engine with URI names support for this to work 
(is this the default?)

Note that you *must* open the main database using the URI format name to get a 
shared memory database.  Personally, I would use URI name formats for 
explicitly set shared-cache.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Grace Simon Batumbya
> Sent: Monday, 16 July, 2012 11:35
> To: General Discussion of SQLite Database
> Cc: Carlin Desautels
> Subject: Re: [sqlite] Enabling shared-cache, breaking attach database
> 
> On 7/16/2012 12:44, Richard Hipp wrote:
> > On Mon, Jul 16, 2012 at 12:37 PM, Grace Batumbya <
> > grace.batum...@senecacollege.ca> wrote:
> >
> >> Hi there,
> >> With the shared-cache enabled, attaching a database to a second instance
> >> of the database connection throws an error : "database is already
> attached".
> >> Why does this happen? is it by design or is it a bug?
> >>
> > We would not have had an error message for this specific issue where it not
> > by design.  But that design happened so long ago, that I do not recall the
> > reason, right off hand, why the restriction is in place.
> >
> > Why do you want to attach the same database twice?  What are you hoping to
> > accomplish?
> 
> We are using SQLite in a concurrent environment. The second connection
> would be on a new thread. We like to refer to DB objects using
> fully-qualified names; hence the need to attach databases for each
> connection.
> 
> *Grace Batumbya*
> Research Assistant | Seneca CDOT
> Phone: 416-491-5050 x3548
> cdot.senecac.on.ca <http://cdot.senecac.on.ca/>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Enabling shared-cache, breaking attach database

2012-07-16 Thread Grace Simon Batumbya 

On 7/16/2012 12:44, Richard Hipp wrote:

On Mon, Jul 16, 2012 at 12:37 PM, Grace Batumbya <
grace.batum...@senecacollege.ca> wrote:


Hi there,
With the shared-cache enabled, attaching a database to a second instance
of the database connection throws an error : "database is already attached".
Why does this happen? is it by design or is it a bug?


We would not have had an error message for this specific issue where it not
by design.  But that design happened so long ago, that I do not recall the
reason, right off hand, why the restriction is in place.

Why do you want to attach the same database twice?  What are you hoping to
accomplish?


We are using SQLite in a concurrent environment. The second connection 
would be on a new thread. We like to refer to DB objects using 
fully-qualified names; hence the need to attach databases for each 
connection.


*Grace Batumbya*
Research Assistant | Seneca CDOT
Phone: 416-491-5050 x3548
cdot.senecac.on.ca 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Enabling shared-cache, breaking attach database

2012-07-16 Thread Richard Hipp 
On Mon, Jul 16, 2012 at 12:37 PM, Grace Batumbya <
grace.batum...@senecacollege.ca> wrote:

> Hi there,
> With the shared-cache enabled, attaching a database to a second instance
> of the database connection throws an error : "database is already attached".
> Why does this happen? is it by design or is it a bug?
>

We would not have had an error message for this specific issue where it not
by design.  But that design happened so long ago, that I do not recall the
reason, right off hand, why the restriction is in place.

Why do you want to attach the same database twice?  What are you hoping to
accomplish?



>
> Here is how to replicate the error:
>
> #include 
> #include "sqlite3.h"
>
> int main(int argc, char **argv){
>   sqlite3 *dbCon1;
>   sqlite3 *dbCon2;
>   int rc = SQLITE_OK;
>   char *err;
>
>   sqlite3_enable_shared_cache(1); // enable shared cache
>   sqlite3_open("c:\\temp\\Database.sqlite", );
>   if((rc = sqlite3_exec(dbCon1, "attach 'c:\\temp\\Database.sqlite' as
> db;", 0, 0, )) != SQLITE_OK){
> printf("Error attaching: %s\n", err)
> sqlite3_close(dbCon1);
> return rc;
>   }
>
>   sqlite3_open("c:\\temp\\Database.sqlite", );
>   if((rc = sqlite3_exec(dbCon2, "attach 'c:\\temp\\Database.sqlite' as
> db;", 0, 0, )) != SQLITE_OK){
> printf("Error attaching: %s\n", err);
> sqlite3_close(dbCon2);
> return rc;
>   }
>
>   printf("attached on both connections\n");
>   sqlite3_close(dbCon1);
>   sqlite3_close(dbCon2);
> }
>
> Grace Batumbya
> Research Assistant | Seneca CDOT
> Phone: 416-491-5050 x3548
> cdot.senecac.on.ca
>
>
>
> ___
> 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