[sqlite] How to verify referential integrity of SQLite database
Hello, We have an application that relies heavily on SQLite foreign key feature to ensure referential integrity of our database. Naturally we always enable foreign key enforcement immediately after connecting. However periodically, when we roll out new software (we have well over 300K installations around the world) we run schema upgrade scripts. To minimize the work an upgrade script needs to do and to limit possibly unwanted side effects those scripts are executed with foreign key enforcement turned off. In a couple of cases due to minor bugs in an upgrade script and the fact foreign keys are not enforced during their operation the database was left in an inconsistent state with regards to the referential integrity. While we can fix the scripts (and we did) we cannot guarantee that those bugs won't happen in the future and thus are looking for an automatic way to verify referential integrity of an existing SQLite database that might have been modified while foreign key enforcement was not enabled. First I tried to roll out my own tool to verify referential integrity based on the information about foreign keys provided by the SQLite through the PRAGMA commands. However I found that it is impossible to unambiguously determine the order of columns in an implicitly specified compound parent key in all cases (short of trying to parse the SQL statement that created the parent table). However looking at SQLite source code it seems to be quite straightforward to add referential integrity verification to perhaps the PRAGMA check_integrity command. My suggestion would be to have check_integrity command verify referential integrity as well only if it's executed while the foreign key enforcement is enabled on the connection. Any thoughts on that? Thanks, - Levi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
On 12 Dec 2012, at 7:29pm, Levi Haskell (BLOOMBERG/ 731 LEXIN) wrote: > In a couple of cases due to minor bugs in an upgrade script and the fact > foreign keys are not enforced during their operation the database was left in > an inconsistent state with regards to the referential integrity. While we can > fix the scripts (and we did) we cannot guarantee that those bugs won't happen > in the future and thus are looking for an automatic way to verify referential > integrity of an existing SQLite database that might have been modified while > foreign key enforcement was not enabled. For checking a database to see whether your FOREIGN KEYs have already been violated, it's possible to check an entire relation with a single SELECT ... JOIN ... ON command. Just check to see if there are any rows in the related table that have an id of NULL. I think that's how I did it. So you can check an entire database which has six FOREIGN KEY relations just by using six SELECTs and seeing if any return more than zero rows. Since you must already have appropriate indices for the keys to work the SELECTs should run quickly. > However I found that it is impossible to unambiguously determine the order of > columns in an implicitly specified compound parent key in all cases (short of > trying to parse the SQL statement that created the parent table). I see what you mean. You have to have the original programmer write your checking statements, you can't generated them automatically. I'm not fond of writing my own parsing code. Maybe SQLite4 will provide better PRAGMAs for this stuff. Other than that, it might help to know there's a compilation directive which sets FOREIGN KEY enforcement on by default: http://www.sqlite.org/compile.html and search for 'SQLITE_DEFAULT_FOREIGN_KEYS'. With this set there's no need to be so sure your own code uses the PRAGMA every time you open a connection. So if your compilation environment will let you set a C directive you can use this ... though only on your own app. It won't help if someone opens your database using the shell tool or something. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
On Wed, Dec 12, 2012 at 2:29 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) < lhask...@bloomberg.net> wrote: > My suggestion would be to have check_integrity command verify referential > integrity as well only if it's executed while the foreign key enforcement > is enabled on the connection. > This seems like a reasonable request. And while we are at it, we will likely also verify UNIQUE and CHECK constraints too. PRAGMA quick_check is still available for users who do not want to take the extra overhead of verifying UNIQUE, CHECK, and foreign key constraints. Does anybody know of any reason why we should not do this? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
On Wed, Dec 12, 2012 at 3:36 PM, Richard Hipp wrote: > On Wed, Dec 12, 2012 at 2:29 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) < > lhask...@bloomberg.net> wrote: > >> My suggestion would be to have check_integrity command verify referential >> integrity as well only if it's executed while the foreign key enforcement >> is enabled on the connection. >> > > This seems like a reasonable request. And while we are at it, we will > likely also verify UNIQUE and CHECK constraints too. > By the way, verifying foreign key constraints is trickier than it seems at first glance, because you have to make sure to get it right for cases involving NULLs and differing collating sequences and affinities between parent and child tables. > > PRAGMA quick_check is still available for users who do not want to take > the extra overhead of verifying UNIQUE, CHECK, and foreign key constraints. > > Does anybody know of any reason why we should not do this? > > > -- > D. Richard Hipp > d...@sqlite.org > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/12/12 12:36, Richard Hipp wrote: > Does anybody know of any reason why we should not do this? I would love if this was combined with an optional per page checksum that detects corruption early. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAlDI7+UACgkQmOOfHg372QR8fACbBFIKWxbuWTz51Tt1k8Hwnykq lkQAnA7gD2x5AZOAZ6w7KOJPo49i37Fc =RRCX -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
> This seems like a reasonable request. And while we are at > it, we will likely also verify UNIQUE and CHECK constraints too. > > PRAGMA quick_check is still available for users who do not > want to take the extra overhead of verifying UNIQUE, CHECK, > and foreign key constraints. > > Does anybody know of any reason why we should not do this? If I understand correctly PRAGMA integrity_check checks for database corruption. A database is corrupt if it is not in a state as documented and intended by the author of SQLite. When you enable foreign keys *after* you insert records, no guarantee is given by SQLite that the foreign key constraints are fulfilled. This may not be what most users would want or expect but it certainly not a corrupt database by SQLite standards. I think the distinction between these two cases should remain clear and the current integrity_check should not mix them. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
On Wed, Dec 12, 2012 at 3:40 PM, Richard Hipp wrote: >On Wed, Dec 12, 2012 at 3:36 PM, Richard Hipp wrote: >> On Wed, Dec 12, 2012 at 2:29 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) < >> lhask...@bloomberg.net> wrote: >> >>> My suggestion would be to have check_integrity command verify referential >>> integrity as well only if it's executed while the foreign key enforcement >>> is enabled on the connection. >>> >> >> This seems like a reasonable request. And while we are at it, we will >> likely also verify UNIQUE and CHECK constraints too. Thank you! >> > >By the way, verifying foreign key constraints is trickier than it seems at >first glance, because you have to make sure to get it right for cases >involving NULLs and differing collating sequences and affinities between >parent and child tables. Intresting, doesn't sqlite3FkCheck() already take these into account? Just curious. - Levi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
On Wed, Dec 12, 2012 at 4:18 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) < lhask...@bloomberg.net> wrote: > On Wed, Dec 12, 2012 at 3:40 PM, Richard Hipp wrote: > >On Wed, Dec 12, 2012 at 3:36 PM, Richard Hipp wrote: > >> On Wed, Dec 12, 2012 at 2:29 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) < > >> lhask...@bloomberg.net> wrote: > >> > >>> My suggestion would be to have check_integrity command verify > referential > >>> integrity as well only if it's executed while the foreign key > enforcement > >>> is enabled on the connection. > >>> > >> > >> This seems like a reasonable request. And while we are at it, we will > >> likely also verify UNIQUE and CHECK constraints too. > > Thank you! > > >> > > > >By the way, verifying foreign key constraints is trickier than it seems at > >first glance, because you have to make sure to get it right for cases > >involving NULLs and differing collating sequences and affinities between > >parent and child tables. > > Intresting, doesn't sqlite3FkCheck() already take these into account? > Yes, it does. But not every home-brew solution to this problem does. That's why we really ought to handle this inside of PRAGMA integrity_check, rather than tossing the problem over the wall to applications developers, as is done now. The whole point of SQLite is to free up app developers to focus on their application logic by simplifying the storage and persistence problem. Checking foreign key constraints really ought to be part of what SQLite does automatically. > > Just curious. > - Levi > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
Jos, I see your point but another way to look at it is that PRAGMA foreign_keys=0/1 changes the definition of what constitutes a legal/consistent state of the database, thus it makes sense that behavior of check_integrity PRAGMA would change accordingly. Thanks, - Levi - Original Message - From: sqlite-users@sqlite.org To: sqlite-users@sqlite.org At: Dec 12 2012 16:01:25 > This seems like a reasonable request. And while we are at > it, we will likely also verify UNIQUE and CHECK constraints too. > > PRAGMA quick_check is still available for users who do not > want to take the extra overhead of verifying UNIQUE, CHECK, > and foreign key constraints. > > Does anybody know of any reason why we should not do this? If I understand correctly PRAGMA integrity_check checks for database corruption. A database is corrupt if it is not in a state as documented and intended by the author of SQLite. When you enable foreign keys *after* you insert records, no guarantee is given by SQLite that the foreign key constraints are fulfilled. This may not be what most users would want or expect but it certainly not a corrupt database by SQLite standards. I think the distinction between these two cases should remain clear and the current integrity_check should not mix them. ___ 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] How to verify referential integrity of SQLite database
Agreed. I originally thought your complexity argument was against implementing it inside SQLite. - Levi - Original Message - From: d...@sqlite.org To: LEVI HASKELL (BLOOMBERG/ 731 LEXIN), sqlite-users@sqlite.org At: Dec 12 2012 16:28:43 On Wed, Dec 12, 2012 at 4:18 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) wrote: On Wed, Dec 12, 2012 at 3:40 PM, Richard Hipp wrote: >On Wed, Dec 12, 2012 at 3:36 PM, Richard Hipp wrote: >> On Wed, Dec 12, 2012 at 2:29 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) < >> lhask...@bloomberg.net> wrote: >> >>> My suggestion would be to have check_integrity command verify referential >>> integrity as well only if it's executed while the foreign key enforcement >>> is enabled on the connection. >>> >> >> This seems like a reasonable request. And while we are at it, we will >> likely also verify UNIQUE and CHECK constraints too. Thank you! >> > >By the way, verifying foreign key constraints is trickier than it seems at >first glance, because you have to make sure to get it right for cases >involving NULLs and differing collating sequences and affinities between >parent and child tables. Intresting, doesn't sqlite3FkCheck() already take these into account? Yes, it does. But not every home-brew solution to this problem does. That's why we really ought to handle this inside of PRAGMA integrity_check, rather than tossing the problem over the wall to applications developers, as is done now. The whole point of SQLite is to free up app developers to focus on their application logic by simplifying the storage and persistence problem. Checking foreign key constraints really ought to be part of what SQLite does automatically. Just curious. - Levi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
On Wed, Dec 12, 2012 at 04:28:21PM -0500, Richard Hipp scratched on the wall: > On Wed, Dec 12, 2012 at 4:18 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) < > lhask...@bloomberg.net> wrote: > > Intresting, doesn't sqlite3FkCheck() already take these into account? > > > > Yes, it does. But not every home-brew solution to this problem does. > That's why we really ought to handle this inside of PRAGMA integrity_check, > rather than tossing the problem over the wall to applications developers, > as is done now. The whole point of SQLite is to free up app developers to > focus on their application logic by simplifying the storage and persistence > problem. Checking foreign key constraints really ought to be part of what > SQLite does automatically. I like the idea of SQLite handling this, but I'm not sure PRAGMA integrity_check is the right place. Unlike a corrupt database, this is the type of thing that an application can recover from. In order to do that, however, the application needs pretty specific information on the violations that were found. That means whatever is doing this check is likely to return a table with a full report of what was found, including table names, rowids, etc. While the current PRAGMA integrity_check does return errors, they're mostly text error messages that are designed for human consumption. Adding programmatic information in additional columns strikes me as a pretty significant change to the return value of a pretty important PRAGMA. I can also see situations when someone might want to run one set or the other set of checks. Breaking it out, so that these checks are done by a different PRAGMA (integrity_check_v2 ?) seems like a wise idea. Existing applications won't benefit from a new PRAGMA, but existing apps don't know how to react to any errors that might be found. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
A way to verify the constraint integrety would be great. Some thoughts: - Introduce a boolean 'all_foreign_key_constraits_are_known_valid' (default false) that is stored in the database header - A new PRAGMA foreign_key_check checks the foreign key constraints - If it finds invalid constraints and the new boolean was set true it reports a corrupted database - If it finds no invalid constraints it sets the new boolean to true - When there is any update to the database while PRAGMA foreign_keys is off the boolean is set to false - When the database is first opened check if the last SQLite library to write the database was a version that is aware of the new boolean. If not, set the boolean false. This way a well behaved application (always immediate set PRAGMA foreign_keys true after opening the database) will always have the new boolean set to true and any invalid constraint can be reported as true corruption. On the other hand, a 'light' version of PRAGMA foreign_key_check would first evaluate the new boolean and skip all checks if it is already true. Jos ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
On 12 Dec 2012, at 9:30pm, "Levi Haskell (BLOOMBERG/ 731 LEXIN)" wrote: > I see your point but another way to look at it is that PRAGMA > foreign_keys=0/1 changes the definition of what constitutes a > legal/consistent state of the database, thus it makes sense that behavior of > check_integrity PRAGMA would change accordingly. So your argument is that with 'PRAGMA foreign_keys' off, the integrity check wouldn't worry about foreign keys. I can get behind that. On the other hand, the argument that PRAGMA integrity_check should check constraints (including but not limited to UNIQUE) is far stronger. SQLite stops you from messing these up: if you try to define a constraint when data violating it already exists, SQLite issues an error message. So any database containing violations is by definition corrupt. There are a few ways you can violate FOREIGN KEY requirements. Not only can you turn the PRAGMA off, but SQLite allows you to declare a FOREIGN KEY relationship when no appropriate index exists. I would prefer it to either issue an error result or to generate its own key when this is done, but instead it waits until something that uses the key to notice that no index exists, and then issues a puzzling error message. Given the above, I would accept that checking FOREIGN KEYS shouldn't be in PRAGMA integrity_check by default. Possibly all this can be fixed in SQLite4, which shouldn't allow a FOREIGN KEY to be declared unless the data and indexes in the database are everything it needs. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
On Wed, Dec 12, 2012 at 10:01 PM, Jos Groot Lipman wrote: > When you enable foreign keys *after* you insert records, no guarantee is > given by SQLite that the foreign key constraints are fulfilled. This may not > be what most users would want or expect but it certainly not a corrupt > database by SQLite standards. I guess one thing I don't understand in this thread is why PRAGMA foreign_keys = ON is *not* persistent with the DB file itself, just like the page size for example. Apologies if this has been discussed before, I'm newly subscribed*, but it seems to me that if the DB creator/owner intends to enforce relational integrity, that should be part of the data, otherwise anyone can come and mess up the data with no regard for the constraints, as demonstrated below. The second session *should* fail to insert (1,1) into the child table IMHO. Relying on all clients to issue the PRAGMA foreign_keys = ON (explicitly and via compile-time switch to default it to ON) is just too brittle and ignores humans natural tendency to make mistakes. If someone explicitly disables the constraints integrity, I guess that's OK, but like others in this thread, I think that trying to re-enable it later should fail if the current DB state violates the FK constraints. What am I missing? Thanks, --DD * is the reason for the non-persistent FK enforcement the fixed SQLite3 DB file format, thus the talk about SQLite4? C:\Users\DDevienne>sqlite3 testfk.db SQLite version 3.7.11 2012-03-20 11:35:50 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> sqlite> PRAGMA foreign_keys = ON; sqlite> create table parent (id int primary key); sqlite> create table child (id int primary key, parent int references parent(id)); sqlite> insert into child values (0, 0); Error: foreign key constraint failed sqlite> insert into parent values (0); sqlite> insert into child values (0, 0); sqlite> select * from parent; 0 sqlite> select * from child; 0|0 sqlite> .q C:\Users\DDevienne>sqlite3 testfk.db SQLite version 3.7.11 2012-03-20 11:35:50 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> sqlite> insert into child values (1, 1); sqlite> select * from parent; 0 sqlite> select * from child; 0|0 1|1 sqlite> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
Jay A. Kreibich wrote: > I can also see situations when someone might want to run one > set or the other set of checks. Breaking it out, so that these > checks are done by a different PRAGMA (integrity_check_v2 ?) seems > like a wise idea. Indeed; with a separate PRAGMA fk_integrity_check, it would be possible to run the check even when foreign keys are not currently enabled. This would be a useful thing to do just before enabling foreign keys. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
Jay A. Kreibich wrote: > I can also see situations when someone might want to run one > set or the other set of checks. Breaking it out, so that these > checks are done by a different PRAGMA (integrity_check_v2 ?) seems > like a wise idea. Indeed; with a separate PRAGMA fk_integrity_check, it would be possible to run the check even when foreign keys are not currently enabled. This would be a useful thing to do just before enabling foreign keys. Isn't something else than a pragma more appropiate? SELECT consistency_check() FROM mytable; would return rows from a specific table where any constraint, unicity or FK is violated: rowid | constraint_name | diag_code SELECT consistency_check_all(); would return rows from every table in turn where any constraint, unicity or FK is violated: table_name | rowid | constraint_name | diag_code ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
Hello, You can give the following tool a try if you want: https://github.com/gwenn/checkfkey But I'm not sure that it correctly handles composite. Regards. On Thu, Dec 13, 2012 at 4:22 PM, Jean-Christophe Deschamps wrote: > >> Jay A. Kreibich wrote: >> > I can also see situations when someone might want to run one >> > set or the other set of checks. Breaking it out, so that these >> > checks are done by a different PRAGMA (integrity_check_v2 ?) seems >> > like a wise idea. >> >> Indeed; with a separate PRAGMA fk_integrity_check, it would be possible >> to run the check even when foreign keys are not currently enabled. >> This would be a useful thing to do just before enabling foreign keys. > > > Isn't something else than a pragma more appropiate? > > SELECT consistency_check() FROM mytable; > > would return rows from a specific table where any constraint, unicity or FK > is violated: > rowid | constraint_name | diag_code > > SELECT consistency_check_all(); > > would return rows from every table in turn where any constraint, unicity or > FK is violated: > table_name | rowid | constraint_name | diag_code > > > > ___ > 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] How to verify referential integrity of SQLite database
On Wed, Dec 12, 2012 at 2:29 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) < lhask...@bloomberg.net> wrote: > My suggestion would be to have check_integrity command verify referential > integrity as well only if it's executed while the foreign key enforcement > is enabled on the connection. > The latest SQLite from trunk (not the 3.7.15.1 patch release, but the code that is destined to become 3.7.16) has a new pragma: PRAGMA foreign_key_check; PRAGMA foreign_key_check(TABLE); The second from checks all of the REFERENCES clauses in TABLE. The first form checks the keys on all tables in the database. The result of the pragma is a table, with one row per mismatched key. The row contains the name of the child table, the rowid of the child table, the name of the parent table, and the "foreign key index" which is an integer that describes the foreign key in PRAGMA foreign_key_list(CHILD). If the foreign_key_check pragma returns an empty set, that means that all of the keys are correct. PRAGMA foreign_key_check works regardless of whether or not foreign keys are currently enabled or disabled. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
On Wed, 19 Dec 2012 21:10:28 -0500, Richard Hipp wrote: > The latest SQLite from trunk (not the 3.7.15.1 patch release, > but the code that is destined to become 3.7.16) has a new pragma: > >PRAGMA foreign_key_check; >PRAGMA foreign_key_check(TABLE); > > The second from checks all of the REFERENCES clauses in TABLE. > The first form checks the keys on all tables in the database. > [] > > PRAGMA foreign_key_check works regardless of whether or not > foreign keys are currently enabled or disabled. Perfect, thanks! -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
Tested, works perfectly. Thanks! Two questions/observation: 1. Should there be a way to determine the parent key w/o looking at or parsing the schema DDL commands? For example: SQLite version 3.7.16 2012-12-20 01:15:20 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table p(a, b, primary key(a, b), unique(b, a)); sqlite> create table c(x, y, foreign key(x, y) references p); sqlite> insert into p values (1, 2); sqlite> insert into c values (1, 2), (2, 1); sqlite> pragma foreign_key_check(c); c|2|p|0 Now I know that the second record is in violation but I don't know what key/index the foreign key actually refers to (and no other combination of existing pragmas will tell me). 2. While I do like your API far better than what I originally proposed, I found that returning no result in case of success may lead to confusion since unknown pragmas behave the same way. So if I run "pragma foreign_key_check;" and get empty result it can mean any of the following: 1. There are no foreign key violations - good! 2. My version of SQLite does not support this pragma yet 3. (In case of using the shell) I made a typo in the pragma name While I don't have a better suggestion now, I just wanted to point it out to you. Again thanks a lot! - Levi - Original Message - From: d...@sqlite.org To: LEVI HASKELL (BLOOMBERG/ 731 LEXIN), sqlite-users@sqlite.org At: Dec 19 2012 21:10:52 On Wed, Dec 12, 2012 at 2:29 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) wrote: My suggestion would be to have check_integrity command verify referential integrity as well only if it's executed while the foreign key enforcement is enabled on the connection. The latest SQLite from trunk (not the 3.7.15.1 patch release, but the code that is destined to become 3.7.16) has a new pragma: PRAGMA foreign_key_check; PRAGMA foreign_key_check(TABLE); The second from checks all of the REFERENCES clauses in TABLE. The first form checks the keys on all tables in the database. The result of the pragma is a table, with one row per mismatched key. The row contains the name of the child table, the rowid of the child table, the name of the parent table, and the "foreign key index" which is an integer that describes the foreign key in PRAGMA foreign_key_list(CHILD). If the foreign_key_check pragma returns an empty set, that means that all of the keys are correct. PRAGMA foreign_key_check works regardless of whether or not foreign keys are currently enabled or disabled. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
On Thu, Dec 20, 2012 at 3:05 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) < lhask...@bloomberg.net> wrote: > Tested, works perfectly. Thanks! > > Two questions/observation: > > 1. Should there be a way to determine the parent key w/o looking at or > parsing the schema DDL commands? > > For example: > SQLite version 3.7.16 2012-12-20 01:15:20 > > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> create table p(a, b, primary key(a, b), unique(b, a)); > sqlite> create table c(x, y, foreign key(x, y) references p); > sqlite> insert into p values (1, 2); > sqlite> insert into c values (1, 2), (2, 1); > sqlite> pragma foreign_key_check(c); > c|2|p|0 > The fourth column is the foreign_key_id. If you look at the output of PRAGMA foreign_key_list(c), you'll find all the information about parent table and the columns that map between parent and child, for that id. > > Now I know that the second record is in violation but I don't know what > key/index the foreign key actually refers to (and no other combination of > existing pragmas will tell me). > > 2. While I do like your API far better than what I originally proposed, I > found that returning no result in case of success may lead to confusion > since unknown pragmas behave the same way. So if I run "pragma > foreign_key_check;" and get empty result it can mean any of the following: > > 1. There are no foreign key violations - good! > 2. My version of SQLite does not support this pragma yet > 3. (In case of using the shell) I made a typo in the pragma name > The pragma throws an error if you enter the name of a table that does not exist. That handles case 3. To verify 2, that the version of SQLite you are using support foreign_key_check, simply use the name of a table that does not exist and verify that you get an error back: PRAGMA foreign_key_check('no-such-table'); --- expect an error > > While I don't have a better suggestion now, I just wanted to point it out > to you. > > Again thanks a lot! > - Levi > > - Original Message - > From: d...@sqlite.org > To: LEVI HASKELL (BLOOMBERG/ 731 LEXIN), sqlite-users@sqlite.org > At: Dec 19 2012 21:10:52 > > > > On Wed, Dec 12, 2012 at 2:29 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) < > lhask...@bloomberg.net> wrote: > >> My suggestion would be to have check_integrity command verify referential >> integrity as well only if it's executed while the foreign key enforcement >> is enabled on the connection. >> > > The latest SQLite from trunk (not the 3.7.15.1 patch release, but the code > that is destined to become 3.7.16) has a new pragma: > > PRAGMA foreign_key_check; > PRAGMA foreign_key_check(TABLE); > > The second from checks all of the REFERENCES clauses in TABLE. The first > form checks the keys on all tables in the database. > > The result of the pragma is a table, with one row per mismatched key. The > row contains the name of the child table, the rowid of the child table, the > name of the parent table, and the "foreign key index" which is an integer > that describes the foreign key in PRAGMA foreign_key_list(CHILD). If the > foreign_key_check pragma returns an empty set, that means that all of the > keys are correct. > > PRAGMA foreign_key_check works regardless of whether or not foreign keys > are currently enabled or disabled. > > > -- > D. Richard Hipp > d...@sqlite.org > > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
On Fri, Dec 21, 2012 at 6:04 AM, Richard Hipp wrote: > On Thu, Dec 20, 2012 at 3:05 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) < > lhask...@bloomberg.net> wrote: > > > Tested, works perfectly. Thanks! > > > > Two questions/observation: > > > > 1. Should there be a way to determine the parent key w/o looking at or > > parsing the schema DDL commands? > > > > For example: > > SQLite version 3.7.16 2012-12-20 01:15:20 > > > > Enter ".help" for instructions > > Enter SQL statements terminated with a ";" > > sqlite> create table p(a, b, primary key(a, b), unique(b, a)); > > sqlite> create table c(x, y, foreign key(x, y) references p); > > sqlite> insert into p values (1, 2); > > sqlite> insert into c values (1, 2), (2, 1); > > sqlite> pragma foreign_key_check(c); > > c|2|p|0 > > > > The fourth column is the foreign_key_id. If you look at the output of > PRAGMA foreign_key_list(c), you'll find all the information about parent > table and the columns that map between parent and child, for that id. > > > > > > Now I know that the second record is in violation but I don't know what > > key/index the foreign key actually refers to (and no other combination of > > existing pragmas will tell me). > > > > 2. While I do like your API far better than what I originally proposed, I > > found that returning no result in case of success may lead to confusion > > since unknown pragmas behave the same way. So if I run "pragma > > foreign_key_check;" and get empty result it can mean any of the > following: > > > > 1. There are no foreign key violations - good! > > 2. My version of SQLite does not support this pragma yet > > 3. (In case of using the shell) I made a typo in the pragma name > > > > The pragma throws an error if you enter the name of a table that does not > exist. That handles case 3. To verify 2, that the version of SQLite you > are using support foreign_key_check, simply use the name of a table that > does not exist and verify that you get an error back: > > PRAGMA foreign_key_check('no-such-table'); --- expect an error > Or check sqlite_version() >= 3.7.16 ? > > > > > > > > While I don't have a better suggestion now, I just wanted to point it out > > to you. > > > > Again thanks a lot! > > - Levi > > > > - Original Message - > > From: d...@sqlite.org > > To: LEVI HASKELL (BLOOMBERG/ 731 LEXIN), sqlite-users@sqlite.org > > At: Dec 19 2012 21:10:52 > > > > > > > > On Wed, Dec 12, 2012 at 2:29 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) < > > lhask...@bloomberg.net> wrote: > > > >> My suggestion would be to have check_integrity command verify > referential > >> integrity as well only if it's executed while the foreign key > enforcement > >> is enabled on the connection. > >> > > > > The latest SQLite from trunk (not the 3.7.15.1 patch release, but the > code > > that is destined to become 3.7.16) has a new pragma: > > > > PRAGMA foreign_key_check; > > PRAGMA foreign_key_check(TABLE); > > > > The second from checks all of the REFERENCES clauses in TABLE. The first > > form checks the keys on all tables in the database. > > > > The result of the pragma is a table, with one row per mismatched key. > The > > row contains the name of the child table, the rowid of the child table, > the > > name of the parent table, and the "foreign key index" which is an integer > > that describes the foreign key in PRAGMA foreign_key_list(CHILD). If the > > foreign_key_check pragma returns an empty set, that means that all of the > > keys are correct. > > > > PRAGMA foreign_key_check works regardless of whether or not foreign keys > > are currently enabled or disabled. > > > > > > -- > > D. Richard Hipp > > d...@sqlite.org > > > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
Hi Richard, Sorry, I missed your message before. Look, in my example: SQLite version 3.7.16 2012-12-20 01:15:20Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table p(a, b, primary key(a, b), unique(b, a)); sqlite> create table c(x, y, foreign key(x, y) references p); sqlite> insert into p values (1, 2); sqlite> insert into c values (1, 2), (2, 1); sqlite> pragma foreign_key_check(c); c|2|p|0 sqlite> pragma foreign_key_list(c); 0|0|p|x||NO ACTION|NO ACTION|NONE 0|1|p|y||NO ACTION|NO ACTION|NONE sqlite> pragma table_info(p); 0|a||0||1 1|b||0||1 sqlite> pragma index_list(p); 0|sqlite_autoindex_p_2|1 1|sqlite_autoindex_p_1|1 No pragma will give me enough info to tell whether fkey implies that c.x = p.a and c.y = p.b *or* c.x = p.b and c.y = p.a. Am I missing something? Thanks, - Levi - Original Message - From: d...@sqlite.org To: LEVI HASKELL (BLOOMBERG/ 731 LEXIN) Cc: sqlite-users@sqlite.org At: Dec 20 2012 23:05:20 On Thu, Dec 20, 2012 at 3:05 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) wrote: Tested, works perfectly. Thanks! Two questions/observation: 1. Should there be a way to determine the parent key w/o looking at or parsing the schema DDL commands? For example: SQLite version 3.7.16 2012-12-20 01:15:20 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table p(a, b, primary key(a, b), unique(b, a));sqlite> create table c(x, y, foreign key(x, y) references p); sqlite> insert into p values (1, 2); sqlite> insert into c values (1, 2), (2, 1); sqlite> pragma foreign_key_check(c); c|2|p|0 The fourth column is the foreign_key_id. If you look at the output of PRAGMA foreign_key_list(c), you'll find all the information about parent table and the columns that map between parent and child, for that id. Now I know that the second record is in violation but I don't know what key/index the foreign key actually refers to (and no other combination of existing pragmas will tell me). 2. While I do like your API far better than what I originally proposed, I found that returning no result in case of success may lead to confusion since unknown pragmas behave the same way. So if I run "pragma foreign_key_check;" and get empty result it can mean any of the following: 1. There are no foreign key violations - good! 2. My version of SQLite does not support this pragma yet 3. (In case of using the shell) I made a typo in the pragma name The pragma throws an error if you enter the name of a table that does not exist. That handles case 3. To verify 2, that the version of SQLite you are using support foreign_key_check, simply use the name of a table that does not exist and verify that you get an error back: PRAGMA foreign_key_check('no-such-table'); --- expect an error While I don't have a better suggestion now, I just wanted to point it out to you. Again thanks a lot! - Levi - Original Message - From: d...@sqlite.org To: LEVI HASKELL (BLOOMBERG/ 731 LEXIN), sqlite-users@sqlite.org At: Dec 19 2012 21:10:52 On Wed, Dec 12, 2012 at 2:29 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) wrote: My suggestion would be to have check_integrity command verify referential integrity as well only if it's executed while the foreign key enforcement is enabled on the connection. The latest SQLite from trunk (not the 3.7.15.1 patch release, but the code that is destined to become 3.7.16) has a new pragma: PRAGMA foreign_key_check; PRAGMA foreign_key_check(TABLE); The second from checks all of the REFERENCES clauses in TABLE. The first form checks the keys on all tables in the database. The result of the pragma is a table, with one row per mismatched key. The row contains the name of the child table, the rowid of the child table, the name of the parent table, and the "foreign key index" which is an integer that describes the foreign key in PRAGMA foreign_key_list(CHILD). If the foreign_key_check pragma returns an empty set, that means that all of the keys are correct. PRAGMA foreign_key_check works regardless of whether or not foreign keys are currently enabled or disabled. -- D. Richard Hipp d...@sqlite.org -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
On Thu, Dec 27, 2012 at 11:43 AM, Levi Haskell (BLOOMBERG/ 731 LEXIN) < lhask...@bloomberg.net> wrote: > Hi Richard, > > Sorry, I missed your message before. > > Look, in my example: > > SQLite version 3.7.16 2012-12-20 01:15:20 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> create table p(a, b, primary key(a, b), unique(b, a)); > sqlite> create table c(x, y, foreign key(x, y) references p); > sqlite> insert into p values (1, 2); > sqlite> insert into c values (1, 2), (2, 1); > sqlite> pragma foreign_key_check(c); > c|2|p|0 > sqlite> pragma foreign_key_list(c); > 0|0|p|x||NO ACTION|NO ACTION|NONE > 0|1|p|y||NO ACTION|NO ACTION|NONE > sqlite> pragma table_info(p); > 0|a||0||1 > 1|b||0||1 > sqlite> pragma index_list(p); > 0|sqlite_autoindex_p_2|1 > 1|sqlite_autoindex_p_1|1 > > No pragma will give me enough info to tell whether fkey implies that c.x = > p.a and c.y = p.b *or* c.x = p.b and c.y = p.a. > > Am I missing something? > In the latest code on the SQLite trunk, the table_info pragma has been enhanced so that the "pk" column (the right-most column) gives the order of the columns in the primary key. > > Thanks, > - Levi > > - Original Message - > From: d...@sqlite.org > To: LEVI HASKELL (BLOOMBERG/ 731 LEXIN) > Cc: sqlite-users@sqlite.org > At: Dec 20 2012 23:05:20 > > > > On Thu, Dec 20, 2012 at 3:05 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) < > lhask...@bloomberg.net> wrote: > >> Tested, works perfectly. Thanks! >> >> Two questions/observation: >> >> 1. Should there be a way to determine the parent key w/o looking at or >> parsing the schema DDL commands? >> >> For example: >> SQLite version 3.7.16 2012-12-20 01:15:20 >> Enter ".help" for instructions >> Enter SQL statements terminated with a ";" >> >> sqlite> create table p(a, b, primary key(a, b), unique(b, a)); >> sqlite> create table c(x, y, foreign key(x, y) references p); >> sqlite> insert into p values (1, 2); >> sqlite> insert into c values (1, 2), (2, 1); >> sqlite> pragma foreign_key_check(c); >> c|2|p|0 >> > The fourth column is the foreign_key_id. If you look at the output of > PRAGMA foreign_key_list(c), you'll find all the information about parent > table and the columns that map between parent and child, for that id. > > >> >> Now I know that the second record is in violation but I don't know what >> key/index the foreign key actually refers to (and no other combination of >> existing pragmas will tell me). >> >> 2. While I do like your API far better than what I originally proposed, I >> found that returning no result in case of success may lead to confusion >> since unknown pragmas behave the same way. So if I run "pragma >> foreign_key_check;" and get empty result it can mean any of the following: >> >> 1. There are no foreign key violations - good! >> 2. My version of SQLite does not support this pragma yet >> 3. (In case of using the shell) I made a typo in the pragma name >> > The pragma throws an error if you enter the name of a table that does not > exist. That handles case 3. To verify 2, that the version of SQLite you > are using support foreign_key_check, simply use the name of a table that > does not exist and verify that you get an error back: >PRAGMA foreign_key_check('no-such-table'); --- expect an error > > > > > >> >> While I don't have a better suggestion now, I just wanted to point it out >> to you. >> >> Again thanks a lot! >> - Levi >> >> - Original Message - >> From: d...@sqlite.org >> To: LEVI HASKELL (BLOOMBERG/ 731 LEXIN), sqlite-users@sqlite.org >> At: Dec 19 2012 21:10:52 >> >> >> >> On Wed, Dec 12, 2012 at 2:29 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) < >> lhask...@bloomberg.net> wrote: >> >>> My suggestion would be to have check_integrity command verify >>> referential integrity as well only if it's executed while the foreign key >>> enforcement is enabled on the connection. >>> >> >> The latest SQLite from trunk (not the 3.7.15.1 patch release, but the >> code that is destined to become 3.7.16) has a new pragma: >> PRAGMA foreign_key_check; >> PRAGMA foreign_key_check(TABLE); >> The second from checks all of the REFERENCES clauses in TABLE. The >> first form checks the keys on all tables in the database. >> >> >> The result of the pragma is a table, with one row per mismatched key. >> The row contains the name of the child table, the rowid of the child table, >> the name of the parent table, and the "foreign key index" which is an >> integer that describes the foreign key in PRAGMA foreign_key_list(CHILD). >> If the foreign_key_check pragma returns an empty set, that means that all >> of the keys are correct. >> >> PRAGMA foreign_key_check works regardless of whether or not foreign keys >> are currently enabled or disabled. >> >> >> -- >> D. Richard Hipp >> >> d...@sqlite.org >> >> > > -- > D. Richard Hipp > > d...@sqlite.org > > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users
Re: [sqlite] How to verify referential integrity of SQLite database
Perfect! Exactly what I needed Thanks a lot, - Levi - Original Message - From: d...@sqlite.org To: LEVI HASKELL (BLOOMBERG/ 731 LEXIN) Cc: sqlite-users@sqlite.org At: Jan 1 2013 09:03:00 On Thu, Dec 27, 2012 at 11:43 AM, Levi Haskell (BLOOMBERG/ 731 LEXIN) wrote: Hi Richard, Sorry, I missed your message before. Look, in my example: SQLite version 3.7.16 2012-12-20 01:15:20 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table p(a, b, primary key(a, b), unique(b, a)); sqlite> create table c(x, y, foreign key(x, y) references p); sqlite> insert into p values (1, 2); sqlite> insert into c values (1, 2), (2, 1); sqlite> pragma foreign_key_check(c); c|2|p|0 sqlite> pragma foreign_key_list(c); 0|0|p|x||NO ACTION|NO ACTION|NONE 0|1|p|y||NO ACTION|NO ACTION|NONE sqlite> pragma table_info(p); 0|a||0||1 1|b||0||1 sqlite> pragma index_list(p); 0|sqlite_autoindex_p_2|1 1|sqlite_autoindex_p_1|1 No pragma will give me enough info to tell whether fkey implies that c.x = p.a and c.y = p.b *or* c.x = p.b and c.y = p.a. Am I missing something? In the latest code on the SQLite trunk, the table_info pragma has been enhanced so that the "pk" column (the right-most column) gives the order of the columns in the primary key. Thanks, - Levi - Original Message - From: d...@sqlite.org To: LEVI HASKELL (BLOOMBERG/ 731 LEXIN) Cc: sqlite-users@sqlite.org At: Dec 20 2012 23:05:20 On Thu, Dec 20, 2012 at 3:05 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) wrote: Tested, works perfectly. Thanks! Two questions/observation: 1. Should there be a way to determine the parent key w/o looking at or parsing the schema DDL commands? For example: SQLite version 3.7.16 2012-12-20 01:15:20 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table p(a, b, primary key(a, b), unique(b, a));sqlite> create table c(x, y, foreign key(x, y) references p); sqlite> insert into p values (1, 2); sqlite> insert into c values (1, 2), (2, 1); sqlite> pragma foreign_key_check(c); c|2|p|0 The fourth column is the foreign_key_id. If you look at the output of PRAGMA foreign_key_list(c), you'll find all the information about parent table and the columns that map between parent and child, for that id. Now I know that the second record is in violation but I don't know what key/index the foreign key actually refers to (and no other combination of existing pragmas will tell me). 2. While I do like your API far better than what I originally proposed, I found that returning no result in case of success may lead to confusion since unknown pragmas behave the same way. So if I run "pragma foreign_key_check;" and get empty result it can mean any of the following: 1. There are no foreign key violations - good! 2. My version of SQLite does not support this pragma yet 3. (In case of using the shell) I made a typo in the pragma name The pragma throws an error if you enter the name of a table that does not exist. That handles case 3. To verify 2, that the version of SQLite you are using support foreign_key_check, simply use the name of a table that does not exist and verify that you get an error back: PRAGMA foreign_key_check('no-such-table'); --- expect an error While I don't have a better suggestion now, I just wanted to point it out to you. Again thanks a lot! - Levi - Original Message - From: d...@sqlite.org To: LEVI HASKELL (BLOOMBERG/ 731 LEXIN), sqlite-users@sqlite.org At: Dec 19 2012 21:10:52 On Wed, Dec 12, 2012 at 2:29 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) wrote: My suggestion would be to have check_integrity command verify referential integrity as well only if it's executed while the foreign key enforcement is enabled on the connection. The latest SQLite from trunk (not the 3.7.15.1 patch release, but the code that is destined to become 3.7.16) has a new pragma: PRAGMA foreign_key_check; PRAGMA foreign_key_check(TABLE); The second from checks all of the REFERENCES clauses in TABLE. The first form checks the keys on all tables in the database. The result of the pragma is a table, with one row per mismatched key. The row contains the name of the child table, the rowid of the child table, the name of the parent table, and the "foreign key index" which is an integer that describes the foreign key in PRAGMA foreign_key_list(CHILD). If the foreign_key_check pragma returns an empty set, that means that all of the keys are correct. PRAGMA foreign_key_check works regardless of whether or not foreign keys are currently enabled or disabled. -- D. Richard Hipp d...@sqlite.org -- D. Richard Hipp d...@sqlite.org -- D. Richard Hipp d...@sqlite.org ___ sqlite