Re: [sqlite] Sharing a database / Replication

[Simon]

> Most you need to know about locking is found in:
> http://www.sqlite.org/lang_transaction.html
> 
> sqlite3_exec() these statements one by one:
> ATTACH 'filename' as db2;
> BEGIN IMMEDIATE; -- or EXCLUSIVE
> (error handling/retry) 
> 
> -- this assumes table1 has the exact 
> -- same definition in both db1 and db2
> INSERT INTO db1.table1 SELECT * FROM db2.table1;
> (error handling)
> 
> COMMIT; -- or ROLLBACK

Ok, good...  Thanks a lot for this quick structure!

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

Re: [sqlite] Sharing a database / Replication

[Kees Nuyt]

On Sat, 20 Dec 2008 07:03:48 -0500, Simon
 wrote in General Discussion of SQLite
Database :

>> Use transactions, ref:
>> http://www.sqlite.org/lang_transaction.html
>> and program proper lock/error handling. The archives of this
>> mailing list contain several good examples.
>
>Yes, ok, I'll have to work that way... 
>I believe there should also be some time spent 
>on a good design for these locks... 
>like to avoid congestion.  

SQLite does the locking for you, you just have to handle the
locked or busy status.

>But it'll be on my own for that one, shouldn't be difficult
>and it's not sqlite specific anyway. 
>(However, i you do have a good url to such design, please tell me!)

Just browse the two or three most recent weeks in the
mailing list archives, then get your hands dirty using the
SQL language- and C interface docs as a reference. 

>> You can ATTACH a second database (actually several databases
>> at the same time) to the same process. Then CREATE your
>> tables and use INSERT INTO  (SELECT FROM ...) syntax to
>> populate tables in one database with the contents of another
>> one.
>> http://www.sqlite.org/lang_insert.html
>> 
>> You even can CREATE tables semi-automatically using the
>> CREATE TABLE  SELECT ... syntax, but this has the
>> disadvantage of not creating any indexes.
>> http://www.sqlite.org/lang_createtable.html
>
>I've read on ATTACH and it does seem to help a lot for my project.  But if I 
>understand right, if I'm copying data from db1 to db2 and the data is actually 
>a 
>blob...  I would have to make my SQL QUERY, then bind the data in db1 to the 
>query and then step through to actually copy it...  right?  The same method 
>could be used regardless of datatype actually to make something simple.

There is not much need to prepare/bind/step in this case,
because you just pump data from one table into another one,
without the data being returned to your application.

Most you need to know about locking is found in:
http://www.sqlite.org/lang_transaction.html

sqlite3_exec() these statements one by one:
ATTACH 'filename' as db2;
BEGIN IMMEDIATE; -- or EXCLUSIVE
(error handling/retry) 

-- this assumes table1 has the exact 
-- same definition in both db1 and db2
INSERT INTO db1.table1 SELECT * FROM db2.table1;
(error handling)

COMMIT; -- or ROLLBACK


>Thanks,
>   Simon
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sharing a database / Replication

[Simon]

> Use transactions, ref:
> http://www.sqlite.org/lang_transaction.html
> and program proper lock/error handling. The archives of this
> mailing list contain several good examples.

Yes, ok, I'll have to work that way...  I believe there should also be some 
time 
spent on a good design for these locks... like to avoid congestion.  But it'll 
be on my own for that one, shouldn't be difficult and it's not sqlite specific 
anyway. (However, i you do have a good url to such design, please tell me!)

> You can ATTACH a second database (actually several databases
> at the same time) to the same process. Then CREATE your
> tables and use INSERT INTO  (SELECT FROM ...) syntax to
> populate tables in one database with the contents of another
> one.
> http://www.sqlite.org/lang_insert.html
> 
> You even can CREATE tables semi-automatically using the
> CREATE TABLE  SELECT ... syntax, but this has the
> disadvantage of not creating any indexes.
> http://www.sqlite.org/lang_createtable.html

I've read on ATTACH and it does seem to help a lot for my project.  But if I 
understand right, if I'm copying data from db1 to db2 and the data is actually 
a 
blob...  I would have to make my SQL QUERY, then bind the data in db1 to the 
query and then step through to actually copy it...  right?  The same method 
could be used regardless of datatype actually to make something simple.

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

Re: [sqlite] Sharing a database / Replication

[Kees Nuyt]

On Tue, 16 Dec 2008 12:26:30 -0500, Simon
 wrote in turne...@gmail.com, General
Discussion of SQLite Database :

>Hi there,
>  I'm developping an application that will use several databases.  One
>on disk that will hold all configuration and cache, and another in ram
>to hold all live data.
>
>  On the same server, it is possible that multiple process will
>read/write to both the disk db and the memory db.  Someone on IRC
>suggested to create a normal file db instead of memory and place it in
>a ramfs such as /dev/shm so other process can also connect to it.

That's right, only the process that created it can access an
in-memory database.

>  I'm wondering what I will need to organise to ensure stability for
>such operations (two or more processes on on database).  I beleive I
>should look into a strong locking policy, but there may be something
>else...  

Use transactions, ref:
http://www.sqlite.org/lang_transaction.html
and program proper lock/error handling. The archives of this
mailing list contain several good examples.

>Also, I will need to copy chunks of the database from one to
>the other in a similar way to a replication. I know that sqlite does
>nothing about replication, interdatabase communication, etc.  I'm just
>wondering if there could be a query made on a database using
>information provided in another database (ie to compare, or
>insert/copy).

You can ATTACH a second database (actually several databases
at the same time) to the same process. Then CREATE your
tables and use INSERT INTO  (SELECT FROM ...) syntax to
populate tables in one database with the contents of another
one.
http://www.sqlite.org/lang_insert.html

You even can CREATE tables semi-automatically using the
CREATE TABLE  SELECT ... syntax, but this has the
disadvantage of not creating any indexes.
http://www.sqlite.org/lang_createtable.html

>Thanks in advance,
>  Simon
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sharing a database / Replication

[Igor Tandetnik]

Simon  wrote:
> I'm just
> wondering if there could be a query made on a database using
> information provided in another database (ie to compare, or
> insert/copy).

http://sqlite.org/lang_attach.html

Igor Tandetnik 



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

[sqlite] Sharing a database / Replication

[Simon]

Hi there,
  I'm developping an application that will use several databases.  One
on disk that will hold all configuration and cache, and another in ram
to hold all live data.

  On the same server, it is possible that multiple process will
read/write to both the disk db and the memory db.  Someone on IRC
suggested to create a normal file db instead of memory and place it in
a ramfs such as /dev/shm so other process can also connect to it.

  I'm wondering what I will need to organise to ensure stability for
such operations (two or more processes on on database).  I beleive I
should look into a strong locking policy, but there may be something
else...  Also, I will need to copy chunks of the database from one to
the other in a similar way to a replication.  I know that sqlite does
nothing about replication, interdatabase communication, etc.  I'm just
wondering if there could be a query made on a database using
information provided in another database (ie to compare, or
insert/copy).

Thanks in advance,
  Simon

-- 
When Earth was the only inhabited planet in the Galaxy, it was a
primitive place, militarily speaking.  The only weapon they had ever
invented worth mentioning was a crude and inefficient nuclear-reaction
bomb for which they had not even developed the logical defense. -
Asimov
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users