Multiple ATTACHments to the same DB?

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Multiple ATTACHments to the same DB?

Randall Smith
Hi, all.

I am developing a setup where multiple independent slave SQLite databases are periodically synching with the same master database.

It would be convenient if I could ATTACH the slave and master DBs to each other during the synch so I could run the synch as a single transaction and so I can do simple and fast operations on both databases within a single query.

My question is:  Can I have 10 or whatever slaves ATTACHed to the same master at the same the time (that is, constantly ATTACHed while he slave is open), in the same spirit as having 10 database connections open to the master all the time?  Or do I need to set up and tear down individual ATTACHments as part of each sync operation?

I would prefer the former, but it's not clear what the issues are.  Can an ATTACH request encounter a "locked database" error, or do these only occur when an actual modification is attempted?  How are ATTACHments different from connections?

Thanks for any words of wisdom here.

Randall.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Multiple ATTACHments to the same DB?

Simon Slavin-3
On 1 Aug 2019, at 11:32pm, Randall Smith <[hidden email]> wrote:

> My question is:  Can I have 10 or whatever slaves ATTACHed to the same master at the same the time (that is, constantly ATTACHed while he slave is open), in the same spirit as having 10 database connections open to the master all the time?  Or do I need to set up and tear down individual ATTACHments as part of each sync operation?

It should not be a problem to have 10 attached databases on the same connection.  SQLite was designed to do this without problems.

In case you didn't know, there's a command of the form

    INSERT INTO tableA SELECT * FROM tableB
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Multiple ATTACHments to the same DB?

Simon Slavin-3
In reply to this post by Randall Smith
On 1 Aug 2019, at 11:32pm, Randall Smith <[hidden email]> wrote:

> Can an ATTACH request encounter a "locked database" error, or do these only occur when an actual modification is attempted?  How are ATTACHments different from connections?

Sorry, hit 'send' too soon.

I don't think ATTACH can get a 'locked' or 'busy' error.  I think you're right that those errors could happen only inside a transaction.

A connection is the way SQLite keeps track of a database, the state that database is in, any transaction it is in the middle of, any statements it is in the middle of, the parts of the database which are cached, and lots of other things like that.

Two different programs, or two different processes in one program, would be expected to use two different connections to talk to a database, or to two different databases.  Otherwise one program or process might interfere with the other, and they would both have to have complicated logic in to figure out what to do about interference.

On the other hand, the ATTACH command is a way for one connection to talk to two or more databases at once.  Your request is a good example of why you might want to do this.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Multiple ATTACHments to the same DB?

Keith Medcalf
In reply to this post by Randall Smith

On Thursday, 1 August, 2019 16:32, Randall Smith <[hidden email]> wrote:

>I am developing a setup where multiple independent slave SQLite
>databases are periodically synching with the same master database.

>It would be convenient if I could ATTACH the slave and master DBs to
>each other during the synch so I could run the synch as a single
>transaction and so I can do simple and fast operations on both
>databases within a single query.

>My question is:  Can I have 10 or whatever slaves ATTACHed to the
>same master at the same the time (that is, constantly ATTACHed while
>he slave is open), in the same spirit as having 10 database
>connections open to the master all the time?

Yes.  There is a compile-time define that controls the maximum number of attached databases, and the default is 10 with an upper limit of 125.  The limit can be LOWERED using the sqlite3_limit() function.  See https://www.sqlite.org/limits.html#max_attached

>Or do I need to set up and tear down individual ATTACHments as part
>of each sync operation?

>I would prefer the former, but it's not clear what the issues are.
>Can an ATTACH request encounter a "locked database" error, or do
>these only occur when an actual modification is attempted?  How are
>ATTACHments different from connections?

https://www.sqlite.org/lang_attach.html

You will get an error if you try to attach more databases than the limit.  Otherwise, ATTACH merely attaches an additional database file to the current connection.  Transactions which affect multiple attached databases are atomic across all referenced attached databases (and behave exactly as you would expect).  The main database (the one you open with one of the sqlite3_open* calls) is nothing more (nor less) than an attached database with the name "main".  If you have tables in multiple attached databases with the same name, then you need to qualify the table with the name of the schema (attachment name) if you care which one of the tables the query is referring to (otherwise it will use the first one found with that name).

A "connection" is the thing on which you issue commands, that is connected to a database.  The "main" file that you open when creating a database connection is given the schema (attachment) name "main".  The temporary database (if you use one) is an attached database called "temp".  You can attach as many "files" as you like up to the limit, and each will have its own schema (attachment) name.  If you do not specify the schema (attachment) name to which a table belongs in an SQL statement, then whatever attached file contains the table is the one that is assumed.  Triggers & Indexes may only refer to things contained within their own database file.

>Thanks for any words of wisdom here.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users