Re: [sqlite] Attached database
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 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 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
> 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 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 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
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 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
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 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 > 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
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
> 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 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 > 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
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 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
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
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
-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
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
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
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