Re: [sqlite] Attached database

[Tiberio, Sylvain]

Ok, as the ATTACH is not committed into the database, I understand why we
cannot have cross-database constraints.

Thanks!

Sylvain

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
Sent: Wednesday, January 20, 2010 6:14 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Attached database

> Is it possible? If I attach database B to database A and database A to 
> database B. As these statements are committed into A and B, they stay 
> attached even if I close and reopen. So If another process opens A (or 
> B), it has B (or A) attached to A (or B). I don't have to repeat the 
> ATTACH statement each time I open the database right?

No, that's incorrect. ATTACH commands are per-connection and never get
committed to database. You have to attach databases any time you want to use
them. You can attach them in any order or do not attach them at all -
doesn't make any difference for SQLite, it's just for your convenience and
it can make difference in how you write your queries.


Pavel

On Wed, Jan 20, 2010 at 12:07 PM, Tiberio, Sylvain
<sylvain.tibe...@eads.com> wrote:
> Hi,
>
>> And how precisely do you expect SQLite to pull that off?
> Ignore the DETACH command and set and error... No? As when you insert 
> a row that doesn't respect a constraints.
>
>> How it is going to stop you from, say, opening a separate connection 
>> to one of these databases (and never attaching the other), possibly 
>> from a different process?
> Is it possible? If I attach database B to database A and database A to 
> database B. As these statements are committed into A and B, they stay 
> attached even if I close and reopen. So If another process opens A (or 
> B), it has B (or A) attached to A (or B). I don't have to repeat the 
> ATTACH statement each time I open the database right?
>
>
> I agree that if data are split in several files, it is a way to 
> introduce constraints violation (for instance if I backup/restore one 
> file and not the others...).
>
> My first need was to separate my database into two separate files. In 
> the 1st file I store my main data, In the second I store relation 
> between data (and I accept to lost it). I would like to be able to 
> backup/restore only the 1st file. It works fine with ATTACH but I 
> don't have constraints on reference and view!
>
> Sylvain
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: Wednesday, January 20, 2010 5:17 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Attached database
>
> Tiberio, Sylvain <sylvain.tibe...@eads.com> wrote:
>> About "cross-database foreign key constraints": I agree with you that 
>> attached database can be dettached or changed. So as it is not 
>> possible to create the foreign key constraint with no attached 
>> database (because parent table doesn't exist), I can imagine that 
>> SQLite doesn't allow to detach database when foreign key constraints 
>> exist on it.
>
> And how precisely do you expect SQLite to pull that off? How it is 
> going to stop you from, say, opening a separate connection to one of 
> these databases (and never attaching the other), possibly from a different
process?
>
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attached database

[Pavel Ivanov]

> Is it possible? If I attach database B to database A and database A to
> database B. As these statements are committed into A and B, they stay
> attached even if I close and reopen. So If another process opens A (or B),
> it has B (or A) attached to A (or B). I don't have to repeat the ATTACH
> statement each time I open the database right?

No, that's incorrect. ATTACH commands are per-connection and never get
committed to database. You have to attach databases any time you want
to use them. You can attach them in any order or do not attach them at
all - doesn't make any difference for SQLite, it's just for your
convenience and it can make difference in how you write your queries.


Pavel

On Wed, Jan 20, 2010 at 12:07 PM, Tiberio, Sylvain
<sylvain.tibe...@eads.com> wrote:
> Hi,
>
>> And how precisely do you expect SQLite to pull that off?
> Ignore the DETACH command and set and error... No? As when you insert a row
> that doesn't respect a constraints.
>
>> How it is going to stop you from, say, opening a separate
>> connection to one of these databases (and never attaching
>> the other), possibly from a different process?
> Is it possible? If I attach database B to database A and database A to
> database B. As these statements are committed into A and B, they stay
> attached even if I close and reopen. So If another process opens A (or B),
> it has B (or A) attached to A (or B). I don't have to repeat the ATTACH
> statement each time I open the database right?
>
>
> I agree that if data are split in several files, it is a way to introduce
> constraints violation (for instance if I backup/restore one file and not the
> others...).
>
> My first need was to separate my database into two separate files. In the
> 1st file I store my main data, In the second I store relation between data
> (and I accept to lost it). I would like to be able to backup/restore only
> the 1st file. It works fine with ATTACH but I don't have constraints on
> reference and view!
>
> Sylvain
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: Wednesday, January 20, 2010 5:17 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Attached database
>
> Tiberio, Sylvain <sylvain.tibe...@eads.com> wrote:
>> About "cross-database foreign key constraints": I agree with you that
>> attached database can be dettached or changed. So as it is not
>> possible to create the foreign key constraint with no attached
>> database (because parent table doesn't exist), I can imagine that
>> SQLite doesn't allow to detach database when foreign key constraints
>> exist on it.
>
> And how precisely do you expect SQLite to pull that off? How it is going to
> stop you from, say, opening a separate connection to one of these databases
> (and never attaching the other), possibly from a different process?
>
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attached database

[Tiberio, Sylvain]

Hi,

> And how precisely do you expect SQLite to pull that off?
Ignore the DETACH command and set and error... No? As when you insert a row
that doesn't respect a constraints.

> How it is going to stop you from, say, opening a separate
> connection to one of these databases (and never attaching
> the other), possibly from a different process?
Is it possible? If I attach database B to database A and database A to
database B. As these statements are committed into A and B, they stay
attached even if I close and reopen. So If another process opens A (or B),
it has B (or A) attached to A (or B). I don't have to repeat the ATTACH
statement each time I open the database right?


I agree that if data are split in several files, it is a way to introduce
constraints violation (for instance if I backup/restore one file and not the
others...).

My first need was to separate my database into two separate files. In the
1st file I store my main data, In the second I store relation between data
(and I accept to lost it). I would like to be able to backup/restore only
the 1st file. It works fine with ATTACH but I don't have constraints on
reference and view!

Sylvain

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: Wednesday, January 20, 2010 5:17 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Attached database

Tiberio, Sylvain <sylvain.tibe...@eads.com> wrote:
> About "cross-database foreign key constraints": I agree with you that 
> attached database can be dettached or changed. So as it is not 
> possible to create the foreign key constraint with no attached 
> database (because parent table doesn't exist), I can imagine that 
> SQLite doesn't allow to detach database when foreign key constraints 
> exist on it.

And how precisely do you expect SQLite to pull that off? How it is going to
stop you from, say, opening a separate connection to one of these databases
(and never attaching the other), possibly from a different process?

Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attached database

[Tiberio, Sylvain]

Ok, thanks!

Sylvain

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
Sent: Wednesday, January 20, 2010 5:16 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Attached database

> Ok, so cross-database view is not supported. Right?

Correct. I remember there was some message on this list about these matters.
And IIRC it even offered some simple patch to SQLite sources to allow
cross-database view support. You might try to search the archives.

> So as it is not possible to
> create the foreign key constraint with no attached database (because 
> parent table doesn't exist), I can imagine that SQLite doesn't allow 
> to detach database when foreign key constraints exist on it.

OK. Now you've done with this connection, close it, maybe even close your
application, restart it, connect to database once more but do not attach
that database foreign key refers to. What should SQLite do here? Or another
scenario: you open connection straight to the other database and drop all
data from your referred table. How should SQLite enforce foreign key here?

Unless database is in one piece and in full control of SQLite there always
will be scenario when foreign key cannot be enforced properly.


Pavel

On Wed, Jan 20, 2010 at 11:07 AM, Tiberio, Sylvain
<sylvain.tibe...@eads.com> wrote:
> Ok, so cross-database view is not supported. Right?
>
> About "cross-database foreign key constraints": I agree with you that 
> attached database can be dettached or changed. So as it is not 
> possible to create the foreign key constraint with no attached 
> database (because parent table doesn't exist), I can imagine that 
> SQLite doesn't allow to detach database when foreign key constraints exist
on it.
>
> Sylvain
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
> Sent: Wednesday, January 20, 2010 4:40 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Attached database
>
> I wouldn't say anything about views - from my POV there's no much harm 
> in supporting cross-database views other than possible user confusion 
> (personally I don't know why it was decided to not support this). But 
> concerning foreign keys: how do you think SQLite is supposed to 
> enforce foreign key when other database is not attached or when you 
> attach another database with the same alias that have table with the 
> same name but with completely different data?
>
>
> Pavel
>
> On Wed, Jan 20, 2010 at 10:33 AM, Tiberio, Sylvain 
> <sylvain.tibe...@eads.com> wrote:
>> Hi,
>> I remark some effects if I use a main database with other attached 
>> database (ATTACH command):
>> - I cannot create view in main database that select columns from main 
>> database and attached database.
>> - There is no foreign key constraints control is the foreign key is 
>> on an attached database table.
>> Is there any think to do to be able to do that? Do I forget something?
>>
>> Regards,
>>
>> Sylvain
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attached database

[Igor Tandetnik]

Tiberio, Sylvain  wrote:
> About "cross-database foreign key constraints": I agree with you that
> attached database can be dettached or changed. So as it is not
> possible to 
> create the foreign key constraint with no attached database (because
> parent 
> table doesn't exist), I can imagine that SQLite doesn't allow to
> detach 
> database when foreign key constraints exist on it.

And how precisely do you expect SQLite to pull that off? How it is going to 
stop you from, say, opening a separate connection to one of these databases 
(and never attaching the other), possibly from a different process?

Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attached database

[Pavel Ivanov]

> Ok, so cross-database view is not supported. Right?

Correct. I remember there was some message on this list about these
matters. And IIRC it even offered some simple patch to SQLite sources
to allow cross-database view support. You might try to search the
archives.

> So as it is not possible to
> create the foreign key constraint with no attached database (because parent
> table doesn't exist), I can imagine that SQLite doesn't allow to detach
> database when foreign key constraints exist on it.

OK. Now you've done with this connection, close it, maybe even close
your application, restart it, connect to database once more but do not
attach that database foreign key refers to. What should SQLite do
here? Or another scenario: you open connection straight to the other
database and drop all data from your referred table. How should SQLite
enforce foreign key here?

Unless database is in one piece and in full control of SQLite there
always will be scenario when foreign key cannot be enforced properly.


Pavel

On Wed, Jan 20, 2010 at 11:07 AM, Tiberio, Sylvain
<sylvain.tibe...@eads.com> wrote:
> Ok, so cross-database view is not supported. Right?
>
> About "cross-database foreign key constraints": I agree with you that
> attached database can be dettached or changed. So as it is not possible to
> create the foreign key constraint with no attached database (because parent
> table doesn't exist), I can imagine that SQLite doesn't allow to detach
> database when foreign key constraints exist on it.
>
> Sylvain
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
> Sent: Wednesday, January 20, 2010 4:40 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Attached database
>
> I wouldn't say anything about views - from my POV there's no much harm in
> supporting cross-database views other than possible user confusion
> (personally I don't know why it was decided to not support this). But
> concerning foreign keys: how do you think SQLite is supposed to enforce
> foreign key when other database is not attached or when you attach another
> database with the same alias that have table with the same name but with
> completely different data?
>
>
> Pavel
>
> On Wed, Jan 20, 2010 at 10:33 AM, Tiberio, Sylvain
> <sylvain.tibe...@eads.com> wrote:
>> Hi,
>> I remark some effects if I use a main database with other attached
>> database (ATTACH command):
>> - I cannot create view in main database that select columns from main
>> database and attached database.
>> - There is no foreign key constraints control is the foreign key is on
>> an attached database table.
>> Is there any think to do to be able to do that? Do I forget something?
>>
>> Regards,
>>
>> Sylvain
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attached database

[Tiberio, Sylvain]

Ok, so cross-database view is not supported. Right?

About "cross-database foreign key constraints": I agree with you that
attached database can be dettached or changed. So as it is not possible to
create the foreign key constraint with no attached database (because parent
table doesn't exist), I can imagine that SQLite doesn't allow to detach
database when foreign key constraints exist on it.

Sylvain

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
Sent: Wednesday, January 20, 2010 4:40 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Attached database

I wouldn't say anything about views - from my POV there's no much harm in
supporting cross-database views other than possible user confusion
(personally I don't know why it was decided to not support this). But
concerning foreign keys: how do you think SQLite is supposed to enforce
foreign key when other database is not attached or when you attach another
database with the same alias that have table with the same name but with
completely different data?


Pavel

On Wed, Jan 20, 2010 at 10:33 AM, Tiberio, Sylvain
<sylvain.tibe...@eads.com> wrote:
> Hi,
> I remark some effects if I use a main database with other attached 
> database (ATTACH command):
> - I cannot create view in main database that select columns from main 
> database and attached database.
> - There is no foreign key constraints control is the foreign key is on 
> an attached database table.
> Is there any think to do to be able to do that? Do I forget something?
>
> Regards,
>
> Sylvain
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attached database

[Pavel Ivanov]

I wouldn't say anything about views - from my POV there's no much harm
in supporting cross-database views other than possible user confusion
(personally I don't know why it was decided to not support this). But
concerning foreign keys: how do you think SQLite is supposed to
enforce foreign key when other database is not attached or when you
attach another database with the same alias that have table with the
same name but with completely different data?


Pavel

On Wed, Jan 20, 2010 at 10:33 AM, Tiberio, Sylvain
 wrote:
> Hi,
> I remark some effects if I use a main database with other attached database
> (ATTACH command):
> - I cannot create view in main database that select columns from main
> database and attached database.
> - There is no foreign key constraints control is the foreign key is on an
> attached database table.
> Is there any think to do to be able to do that? Do I forget something?
>
> Regards,
>
> Sylvain
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Attached database

[Tiberio, Sylvain]

Hi,
I remark some effects if I use a main database with other attached database
(ATTACH command):
- I cannot create view in main database that select columns from main
database and attached database.
- There is no foreign key constraints control is the foreign key is on an
attached database table.
Is there any think to do to be able to do that? Do I forget something?

Regards,

Sylvain


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attached database

[Griggs, Donald]

 
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Filipe Madureira

Is there a way to get a list of attached databases?
Either by SQL or by function call?
==

Greetings, Filipe,

PRAGMA database_list;
  http://sqlite.org/pragma.html#schema
Or, if you're using the commandline utility:
  .databases

You may want to read the entire section on PRAGMAs as there are many
useful ones.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Attached database

[Filipe Madureira]

Hi,

Is there a way to get a list of attached databases?
Either by SQL or by function call?

I want to attach several databases, at several points in time.
It can happen that I attempt to attach the same database several times, 
but I want to prevent different attaches to the same database if it is 
already attached.

I can create an internal structure of my own to manage this, but if it 
is already available in SQLite it is easier.


Thanks

Cumprimentos / Best Regards

Filipe Madureira
-
SYSDEV, LDA - Mobile Solutions
(www.sysdevsolutions.com)
Tel: +351 234188027
Fax: +351 234188400
- 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attached database atomic-ness

[Dan]

On Feb 21, 2009, at 5:57 AM, Clark Christensen wrote:

>
> Hello,
>
> In the docs for ATTACH, I see this information:
>
> Transactions involving multiple attached databases are atomic,
> assuming that the main database is not ":memory:".  If the main
> database is ":memory:" then
> transactions continue to be atomic within each individual
> database file. But if the host computer crashes in the middle
> of a COMMIT where two or more database files are updated,
> some of those files might get the changes where others
> might not.
>
> I'm looking for clarification on the last sentence:
>
> "But if the host computer crashes in the middle
> of a COMMIT where two or more database files are updated,
> some of those files might get the changes where others
> might not."
>
> If both the main database, and the attached database(s) are disk- 
> based, is there more risk of data loss during a crash in the middle  
> of a COMMIT than with a single database file?


Possibly slightly more. But committing a multi-file transaction when the
main database is disk-based is officially considered a safe thing to do
(whereas a multi-file transaction where the main database is in-memory  
is
not).

Dan.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Attached database atomic-ness

[Clark Christensen]

Hello,

In the docs for ATTACH, I see this information:

Transactions involving multiple attached databases are atomic,
assuming that the main database is not ":memory:".  If the main
database is ":memory:" then 
transactions continue to be atomic within each individual
database file. But if the host computer crashes in the middle
of a COMMIT where two or more database files are updated,
some of those files might get the changes where others
might not.

I'm looking for clarification on the last sentence:

"But if the host computer crashes in the middle
of a COMMIT where two or more database files are updated,
some of those files might get the changes where others
might not."

If both the main database, and the attached database(s) are disk-based, is 
there more risk of data loss during a crash in the middle of a COMMIT than with 
a single database file?

Thanks!

 -Clark
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users