Re: [sqlite] How to verify referential integrity of SQLite database

[Levi Haskell (BLOOMBERG/ 731 LEXIN)]

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

Re: [sqlite] How to verify referential integrity of SQLite database

[Richard Hipp]

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
___

Re: [sqlite] How to verify referential integrity of SQLite database

[Levi Haskell (BLOOMBERG/ 731 LEXIN)]

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

[Baruch Burstein]

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

[Richard Hipp]

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

[Levi Haskell (BLOOMBERG/ 731 LEXIN)]

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

[Kees Nuyt]

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

[Richard Hipp]

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

[gwenn]

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

[Jean-Christophe Deschamps]

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

[Clemens Ladisch]

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

[Dominique Devienne]

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

[Simon Slavin]

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

[Jos Groot Lipman]

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

[Jay A. Kreibich]

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

[Levi Haskell (BLOOMBERG/ 731 LEXIN)]

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

[Levi Haskell (BLOOMBERG/ 731 LEXIN)]

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

[Richard Hipp]

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

[Levi Haskell (BLOOMBERG/ 731 LEXIN)]

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

[Jos Groot Lipman]

> 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

[Roger Binns]

-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

[Richard Hipp]

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

[Richard Hipp]

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

[Simon Slavin]

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

[sqlite] How to verify referential integrity of SQLite database

[Levi Haskell (BLOOMBERG/ 731 LEXIN)]

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