sqlite and multiple databases

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

sqlite and multiple databases

Rajan, Vivek K
Hello-

 

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?

*        Is there any performance penalty for multiple databases,
especially with different schemas? Has someone done any benchmarking on
this front?

*        Are there any BKM's for how to use multiple databases with
SQLite with different schemas, especially with query/select/join
statements

*        Any document pointers/code-examples for how to use multiple
database with different schemas?  

 

Thanks.

 

Rajan

Reply | Threaded
Open this post in threaded view
|

Re: sqlite and multiple databases

Igor Tandetnik
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