Igor Tandetnik wrote:
Jay Sprenkle wrote:
Every time you open a :memory: database you get a separate instance,
identified only by its sqlite3* handle. I'm not sure if ATTACH would
work with :memory:, but even if it does it would just create a new,
empty in-memory database, not refer to the one (of possibly many) you
already have open and populated.
sqlite> create table x(y text);
sqlite> insert into x(y) values('one');
sqlite> select * from x;
one
sqlite> attach ":memory:" as db1;
sqlite> create table db1.x(y text);
sqlite> insert into db1.x(y) values('two');
sqlite> select * from db1.x;
two
sqlite> select * from x;
one
sqlite> attach ":memory:" as db2;
sqlite> create table db2.x(y text);
sqlite> insert into db2.x(y) values('three');
sqlite> select * from db2.x;
three
sqlite> select * from db1.x;
two
sqlite> select * from x;
one
sqlite>
Would that work with pre-existing memory databases? Here, you create
several new memory databases attached to an existing one. The whole
contraption hangs off of a single sqlite* handle. For all intents and
purposes, you have a single in-memory database, just with somewhat
unusual table names.
This technique may be cute, but I don't quite see how it's useful. It
defeats the whole point of having multiple independent memory
databases - for example the ability to write to them concurrently from
different threads.
Igor Tandetnik
Thanks for all who have replied so far.
I know very well how to read/write memory db tables to/from disk db
tables, that is not my problem.
As far as I know, Igor is correct- a memory database is unique to it's
sqlite3 connection and as far as I know, one memory db instance can't be
attached to another.
I use memory dbs on an embedded system with a very slow flash disk to
improve 'real-time' updates. I have a separate thread that does 'lazy'
writes to an 'identical' disk db. The separate thread keeps the disk db
in sync with my mem db over time.
My system is initialized from a central server-I first initialize my mem
db and would like to update my file db in a separate thread from the mem
db, without locking the mem db for more than a couple of seconds. The
most efficient way to do this would seem to be to do something like
'create table mem1.table1 as select * from mem2.table1' . Obviously this
doesn't work, but at least if I could efficiently copy one mem db to a
second, I could attach a file db to the second and do my update in a
separate thread.
My thought was that if I could copy one mem db to another without going
to disk, I could accomplish this quickly.
Perhaps there is another way to accomplish my goal?
Thanks for all replies,
-Randy
