Re: [sqlite] No way to check for CHECK constraint violations a posteriori

[Bob Friesenhahn]

On Wed, 22 Feb 2017, Richard Hipp wrote:


CHECK constraint failures are suppose to be exceedingly rare.
Elaborate error messages that pinpoint the problem are possible, but
they increase the library complexity and footprint unnecessarily.  In
the rare event that you encounter a CHECK constraint failure, you can
go back and figure out which constraint and which row is at fault
using ordinary queries.


In my world, constraint failures are common rather than exceedingly 
rare.  We made the decision that the database should validate all of 
its inputs as much as possible (including by using extension 
functions) and do not depend much on intermediate wrappers to do 
validation.  It is pretty normal that the program or person violating 
the constraint is not very aware of the rules.


Due to the extreme weakness of sqlite when it comes to reporting 
constraint failures, we use an approach where table rows are updated 
one by one so that we can know which table row update failed.  This 
causes other issues since whole-row or inter-table consistency checks 
may temporarily fail.


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] No way to check for CHECK constraint violations a posteriori

[Dominique Devienne]

On Wed, Feb 22, 2017 at 6:09 PM Dominique Devienne 
wrote:

> On Wed, Feb 22, 2017 at 5:50 PM, Richard Hipp  wrote:
>
> On 2/22/17, Dominique Devienne  wrote:
>
>
> >
>
>
> > Could we please have the CHECK constraint name,
>
>
> > in addition to the table name, in case of a failure?
>
>
> >
>
>
> > Pushing my luck a little bit: any chance to be able to pinpoint the
>
>
> > offending rows, similar to how foreign_key_check does it?
>
>
>
>
>
> CHECK constraint failures are suppose to be exceedingly rare.
> [...] you can go back and figure out which constraint [...]
>
>
> [...] Could we please at least have the constraint name?
> [...] And that would make it consistent with the normal message
> from an insert failure. Consistency is good right? --DD
>

I see this has been merged to trunk (w/o the constraint name).
Thank you again. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] No way to check for CHECK constraint violations a posteriori

[Dominique Devienne]

On Wed, Feb 22, 2017 at 5:50 PM, Richard Hipp  wrote:

> On 2/22/17, Dominique Devienne  wrote:
> >
> > Could we please have the CHECK constraint name,
> > in addition to the table name, in case of a failure?
> >
> > Pushing my luck a little bit: any chance to be able to pinpoint the
> > offending rows, similar to how foreign_key_check does it?
>
> CHECK constraint failures are suppose to be exceedingly rare.
> Elaborate error messages that pinpoint the problem are possible, but
> they increase the library complexity and footprint unnecessarily.  In
> the rare event that you encounter a CHECK constraint failure, you can
> go back and figure out which constraint and which row is at fault
> using ordinary queries.


OK on the rows-at-fault. No problem.

But I have some tables with many check constraints, one per column at least
basically.
Could we please at least have the constraint name? Assuming it's readily
available
at the time you generate the message, that would narrow down the "search
field"
to a single one of those CHECK constraint. And that would make it
consistent with
the normal message from an insert failure. Consistency is good right? --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] No way to check for CHECK constraint violations a posteriori

[Richard Hipp]

On 2/22/17, Dominique Devienne  wrote:
>
> Could we please have the CHECK constraint name,
> in addition to the table name, in case of a failure?
>
> Pushing my luck a little bit: any chance to be able to pinpoint the
> offending rows, similar to how foreign_key_check does it?

CHECK constraint failures are suppose to be exceedingly rare.
Elaborate error messages that pinpoint the problem are possible, but
they increase the library complexity and footprint unnecessarily.  In
the rare event that you encounter a CHECK constraint failure, you can
go back and figure out which constraint and which row is at fault
using ordinary queries.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] No way to check for CHECK constraint violations a posteriori

[Dominique Devienne]

On Wed, Feb 22, 2017 at 4:47 PM, Richard Hipp  wrote:

> On 2/22/17, Dominique Devienne  wrote:
> > Neither {{pragma integrity_check}} nor {{pragma foreign_check_check}}
> > checks CHECK constraints.
>
> That is now fixed on a branch.  I am still testing the changes prior
> to merging onto trunk.


Thanks Richard!

Could we please have the CHECK constraint name,
in addition to the table name, in case of a failure?
Similar to how we get that name on insert in case of a failure.
(Personally I'd prefer to have both the CHECK name and the TABLE name,
instead of one or the other, depending on whether the constraint is named
or not).

zErr = sqlite3MPrintf(db, "CHECK constraint failed in %s",
   pTab->zName);


Pushing my luck a little bit: any chance to be able to pinpoint the
offending rows, similar to how foreign_key_check does it?

I guess one can manually grab the CHECK constraint from sqlite_master,
and do a select on the table to find those rows,
that just not very convenient :).

Thanks, --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] No way to check for CHECK constraint violations a posteriori

[Richard Hipp]

On 2/22/17, Dominique Devienne  wrote:
>
> PS: Also note that {{pragma integrity_check}} (or quick_check) and {{pragma
> foreign_check_check}} differ in behavior, one return ok, the other nothing.
> Ideally they'd be consistent.
>

Probably they should have been.  But that is water under the bridge
now.  If we change it, it will likely break a bunch of legacy
programs.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] No way to check for CHECK constraint violations a posteriori

[Simon Slavin]

On 22 Feb 2017, at 3:47pm, Richard Hipp  wrote:

> On 2/22/17, Dominique Devienne  wrote:
>> Neither {{pragma integrity_check}} nor {{pragma foreign_check_check}}
>> checks CHECK constraints.
> 
> That is now fixed on a branch.  I am still testing the changes prior
> to merging onto trunk.

Just wanted to point out the PS on DD's original post and ask whether you’d 
noticed it.

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

Re: [sqlite] No way to check for CHECK constraint violations a posteriori

[Richard Hipp]

On 2/22/17, Dominique Devienne  wrote:
> Neither {{pragma integrity_check}} nor {{pragma foreign_check_check}}
> checks CHECK constraints.

That is now fixed on a branch.  I am still testing the changes prior
to merging onto trunk.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] No way to check for CHECK constraint violations a posteriori

[Hick Gunter]

A CHECK constraint is just a special trigger that allows you to raise an error 
and only runs on INSERT and UPDATE.

It has nothing to do with internal database structure (pragma integrity_check) 
or with foreign keys (pragma foreign_key_check; you have to enable foreign key 
checking first anyway).

If you are disabling CHECK constraints for a specific reason, what causes that 
reason to go away?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dominique Devienne
Gesendet: Mittwoch, 22. Februar 2017 14:04
An: General Discussion of SQLite Database 
Betreff: [sqlite] No way to check for CHECK constraint violations a posteriori

Neither {{pragma integrity_check}} nor {{pragma foreign_check_check}} checks 
CHECK constraints.

Given that there's {{pragma ignore_check_constraints = 1}} which allows to 
bypass CHECK constraints, that seems like a surprising oversight.

I even tried {{pragma ignore_check_constraints = 0}} in case it was re-validate 
CHECK constraint on that mode change, but apparently not.

Could we please have a new {{pragma check_constraints_check}} pragma, or 
alternatively have {{pragma integrity_check}} actually check CHECK constraint?

Thanks, --DD

PS: Also note that {{pragma integrity_check}} (or quick_check) and {{pragma 
foreign_check_check}} differ in behavior, one return ok, the other nothing.
Ideally they'd be consistent.

C:\Users\ddevienne>sqlite3
SQLite version 3.10.2 2016-01-20 15:27:19 Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t (v CHECK (typeof(v) = 'integer')); insert into t
sqlite> values (1); insert into t values ('two');
Error: CHECK constraint failed: t
sqlite> pragma ignore_check_constraints = 1; insert into t values
sqlite> ('two'); pragma integrity_check;
ok
sqlite> pragma foreign_key_check;
sqlite> pragma ignore_check_constraints = 0; pragma quick_check;
ok
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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