On Thursday, 1 August, 2019 16:32, Randall Smith <[email protected]> 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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
