Multiple files for a single SQLite database

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

Multiple files for a single SQLite database

ayagmur
SQLite stores a single database in a single file.

According to the specific project requirements and due to the performance
concerns;

When working with large databases, is it possible to explicitly tell SQLite
not to store the whole DB in a single file and store different tables in
different files instead?
_______________________________________________
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 files for a single SQLite database

Simon Slavin-3
On 30 Oct 2019, at 1:41pm, Aydin Ozgur Yagmur <[hidden email]> wrote:

> SQLite stores a single database in a single file.
>
> According to the specific project requirements and due to the performance concerns;
>
> When working with large databases, is it possible to explicitly tell SQLite not to store the whole DB in a single file and store different tables in different files instead?

SQLite can open with several different files on the same connection using the ATTACH DATABASE command:

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

Open one database in the normal way.  This could be an empty database with no tables in.  This database automatically gets the schema name 'main'.  But you should supply schema names for attached databases:
ATTACH DATABASE 'second.sqlite' AS suppliers
ATTACH DATABASE 'third.sqlite' AS customers
etc..

There's a limit, somewhere around ten, I think.  Though you can increase it by explicitly messing with limits and compilation options.

In your SQL commands you must refer to the schema as part of the table name.  Otherwise SQLite assumes that the table is part of the 'main' database.  So for a table "invoices" you might use

SELECT customer_num, pre_tax_total, post_tax_total
    FROM customers.invoices
    WHERE invoice_date = "20191030"

There are things you cannot split across different database files.  For instance, you cannot declare a FOREIGN KEY that refers to a table in a different schema.  Because SQLite can't be sure you'll have that schema available.
_______________________________________________
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 files for a single SQLite database

David Raymond
"There's a limit, somewhere around ten, I think.  Though you can increase it by explicitly messing with limits and compilation options."

Default is 10, can be raised to 125.
_______________________________________________
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 files for a single SQLite database

J. King-3
In reply to this post by Simon Slavin-3
On October 30, 2019 9:58:20 a.m. EDT, Simon Slavin <[hidden email]> wrote:

>There are things you cannot split across different database files.  For
>instance, you cannot declare a FOREIGN KEY that refers to a table in a
>different schema.  Because SQLite can't be sure you'll have that schema
>available.

It's also worth noting that in WAL mode transactions are not atomic across database files (they are atomic in the default rollback-journal mode, however).

--
J. King
_______________________________________________
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 files for a single SQLite database

ayagmur
In reply to this post by David Raymond
How can it be raised to 125?
I tried to change with calling "sqlite3_limit(db_, SQLITE_LIMIT_ATTACHED,
125)" but there is no effect.
I still get "too many attached databases" exception.


On Wed, Oct 30, 2019 at 5:01 PM David Raymond <[hidden email]>
wrote:

> "There's a limit, somewhere around ten, I think.  Though you can increase
> it by explicitly messing with limits and compilation options."
>
> Default is 10, can be raised to 125.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
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 files for a single SQLite database

Warren Young
On Oct 31, 2019, at 5:40 AM, Aydin Ozgur Yagmur <[hidden email]> wrote:
>
> How can it be raised to 125?

Define SQLITE_MAX_ATTACHED at build time:

    https://www.sqlite.org/limits.html#max_attached

> I tried to change with calling "sqlite3_limit(db_, SQLITE_LIMIT_ATTACHED,
> 125)" but there is no effect.

That’s only useful for *lowering* the maximum from the compile time SQLITE_MAX_ATTACHED value.
_______________________________________________
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 files for a single SQLite database

ayagmur
In reply to this post by ayagmur
Oh, I found the answer.
SQLITE_MAX_ATTACH should be changed.


On Thu, Oct 31, 2019 at 2:40 PM Aydin Ozgur Yagmur <[hidden email]>
wrote:

> How can it be raised to 125?
> I tried to change with calling "sqlite3_limit(db_, SQLITE_LIMIT_ATTACHED,
> 125)" but there is no effect.
> I still get "too many attached databases" exception.
>
>
> On Wed, Oct 30, 2019 at 5:01 PM David Raymond <[hidden email]>
> wrote:
>
>> "There's a limit, somewhere around ten, I think.  Though you can increase
>> it by explicitly messing with limits and compilation options."
>>
>> Default is 10, can be raised to 125.
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
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 files for a single SQLite database

Keith Medcalf
In reply to this post by ayagmur

See https://sqlite.org/limits.html

11. Maximum Number Of Attached Databases

The ATTACH statement is an SQLite extension that allows two or more databases to be associated to the same database connection and to operate as if they were a single database. The number of simultaneously attached databases is limited to SQLITE_MAX_ATTACHED which is set to 10 by default. The maximum number of attached databases cannot be increased above 125.

The maximum number of attached databases can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_ATTACHED,size) interface.

So you have to compile a version of SQLite3 with a larger maximum limit.  At runtime you cannot raise the limit above the default set by the SQLITE_MAX_ATTACHED compile-time parameter, you can only reduce the limit.

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

>-----Original Message-----
>From: sqlite-users <[hidden email]> On
>Behalf Of Aydin Ozgur Yagmur
>Sent: Thursday, 31 October, 2019 05:40
>To: SQLite mailing list <[hidden email]>
>Subject: Re: [sqlite] Multiple files for a single SQLite database
>
>How can it be raised to 125?
>I tried to change with calling "sqlite3_limit(db_, SQLITE_LIMIT_ATTACHED,
>125)" but there is no effect.
>I still get "too many attached databases" exception.
>
>
>On Wed, Oct 30, 2019 at 5:01 PM David Raymond <[hidden email]>
>wrote:
>
>> "There's a limit, somewhere around ten, I think.  Though you can
>increase
>> it by explicitly messing with limits and compilation options."
>>
>> Default is 10, can be raised to 125.
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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