Rajan, Vivek K wrote:
I have few questions regarding usage-model of multiple databases with
SQLite. I understand SQLite allows for attaching multiple databases
in a single session. My questions are:
* Do the schema for multiple databases need to be same for
attaching with SQLite in single session?
No.
* Is there any performance penalty for multiple databases,
especially with different schemas?
Different schemas are a red herring. There is no gain nor loss in
attaching a database with the same schema, as opposed to different
schemas.
There is of course some overhead when working with multiple databases.
For example, whenever a modifying transaction starts (or rather, when it
has to actually write data to disk for the first time), SQLite has to
create a journal file for each DB involved as well as a master journal
file.
* Are there any BKM's for how to use multiple databases with
SQLite with different schemas, especially with query/select/join
statements
It's not different from using a single database, again regardless of
whether the two databases have the same or different schemas. As far as
SQL syntax is concerned, you basically have a single database containing
all the tables from all the attached sources. If two tables from two
different databases happen to have the same name, you disambiguate them
using dbname.tablename syntax wherever table name is needed. Here dbname
is "main" (without quotes) for the main database (the one you originally
opened and to which all the others are attached), and for the other
databases you assign an arbitrary name in ATTACH statement.
Igor Tandetnik
