Re: [sqlite] No way to check for CHECK constraint violations a posteriori
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
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
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
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
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
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
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
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
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
[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')); sqlite> insert into t values (1); sqlite> insert into t values ('two'); Error: CHECK constraint failed: t sqlite> pragma ignore_check_constraints = 1; sqlite> insert into t values ('two'); sqlite> pragma integrity_check; ok sqlite> pragma foreign_key_check; sqlite> pragma ignore_check_constraints = 0; sqlite> pragma quick_check; ok ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users