Quantcast

3.18 Problem

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

3.18 Problem

David Burgess
Hi . I am new to this mailing list

I have a table with 500,000 rows, it has a unique column of registration numbers
The rules for the format of these numbers has changed over time and the
schema has changed with the rules of the time. PRAGMA integrity_check
now complains about some rows not passing the constraint.
An option on integrity_check to ignore schema CHECK would be avoid having
to code a CHECK that caries with time.
I preferred the pre 3.18 behaviour. More flexible and fulfills a real
world requirement.

--
DB
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 3.18 Problem

Simon Slavin-3

On 2 Apr 2017, at 2:50pm, David Burgess <[hidden email]> wrote:

> I have a table with 500,000 rows, it has a unique column of registration numbers
> The rules for the format of these numbers has changed over time and the
> schema has changed with the rules of the time. PRAGMA integrity_check
> now complains about some rows not passing the constraint.
> An option on integrity_check to ignore schema CHECK would be avoid having
> to code a CHECK that caries with time.

Remove the CHECK constraint from the table.  Do your checking using a TRIGGER.  When your format changes, update the TRIGGER.  This will not invalidate rows which are already in the table.

> I preferred the pre 3.18 behaviour. More flexible and fulfills a real
> world requirement.

But wrong.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 3.18 Problem

David Burgess
Bad trigger performance prevents me using your suggested solution.
Is there a secret to making triggers perform like CHECK?

On Mon, Apr 3, 2017 at 1:35 AM, Simon Slavin <[hidden email]> wrote:

>
> On 2 Apr 2017, at 2:50pm, David Burgess <[hidden email]> wrote:
>
>> I have a table with 500,000 rows, it has a unique column of registration numbers
>> The rules for the format of these numbers has changed over time and the
>> schema has changed with the rules of the time. PRAGMA integrity_check
>> now complains about some rows not passing the constraint.
>> An option on integrity_check to ignore schema CHECK would be avoid having
>> to code a CHECK that caries with time.
>
> Remove the CHECK constraint from the table.  Do your checking using a TRIGGER.  When your format changes, update the TRIGGER.  This will not invalidate rows which are already in the table.
>
>> I preferred the pre 3.18 behaviour. More flexible and fulfills a real
>> world requirement.
>
> But wrong.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



--
David Burgess
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 3.18 Problem

Keith Medcalf

Perhaps you could show the trigger you are complaining about?

> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of David Burgess
> Sent: Sunday, 2 April, 2017 17:41
> To: SQLite mailing list
> Subject: Re: [sqlite] 3.18 Problem
>
> Bad trigger performance prevents me using your suggested solution.
> Is there a secret to making triggers perform like CHECK?
>
> On Mon, Apr 3, 2017 at 1:35 AM, Simon Slavin <[hidden email]> wrote:
> >
> > On 2 Apr 2017, at 2:50pm, David Burgess <[hidden email]> wrote:
> >
> >> I have a table with 500,000 rows, it has a unique column of
> registration numbers
> >> The rules for the format of these numbers has changed over time and the
> >> schema has changed with the rules of the time. PRAGMA integrity_check
> >> now complains about some rows not passing the constraint.
> >> An option on integrity_check to ignore schema CHECK would be avoid
> having
> >> to code a CHECK that caries with time.
> >
> > Remove the CHECK constraint from the table.  Do your checking using a
> TRIGGER.  When your format changes, update the TRIGGER.  This will not
> invalidate rows which are already in the table.
> >
> >> I preferred the pre 3.18 behaviour. More flexible and fulfills a real
> >> world requirement.
> >
> > But wrong.
> >
> > Simon.
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> David Burgess
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 3.18 Problem

Simon Slavin-3
In reply to this post by David Burgess

On 3 Apr 2017, at 12:41am, David Burgess <[hidden email]> wrote:

> Bad trigger performance prevents me using your suggested solution.

I’d like to see your TRIGGER.  But if a well-written TRIGGER can’t reach the performance you need (it’ll never be as fast as a constraint) then your best solution is to do sanity-checking in your programming language.

> Is there a secret to making triggers perform like CHECK?

No.  They do two different jobs.  CHECK is to ensure integrity of the TABLE.  A constraint applies to all the rows in the table all the time.  The thing you want does not involve CHECK since existing entries in the database which violate your rule are not a problem.

TRIGGERs apply when things are added to, modified, or deleted from the TABLE.  But a TRIGGER can be used to check /new/ entries in the database.  It would seem that a TRIGGER might be useful to you.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 3.18 Problem

David Burgess
Yes a trigger is right thing to use. I started with a trigger and
moved to a check purely for performance. The trouble being that
inserts are done in blocks of anywhere up to 10,000. The trigger
overhead on the big inserts is significant (I guess I should re-check
to see if that has changed since 3.7)
The check version handles  10,000 inserts in a timeframe that is not
worth measuring. Instant from a user perspective.
I have vague memories when I first did this a(a long time ago) of
measuring the overhead of a trigger that did nothing. I should
probably do this again.
The trigger version had the same tests as the check. See below.


-----
I have sanitized this (a little)

CHECKS
 check( instr('ABCDEFGH',substr(col1,10,1)) ),
 check( col2 GLOB '123 45[67]000[0-9][0-9][0-9][0-9][0-9][0-9]' ),
 check( CASE substr(col2,7,1) WHEN '6' THEN
instr('XY',substr(col1,10,1)) WHEN '7' THEN
instr('VW',substr(col1,10,1)) END )

TRIGGER
DROP TRIGGER IF EXISTS tab;
CREATE TRIGGER validate_tab BEFORE INSERT ON tab FOR EACH ROW
BEGIN
  CASE WHEN instr('ABCDEFGH',substr(NEW.col1,10,1)) = 0 THEN RAISE (
ABORT, 'Invalid col1' )
       WHEN NEW.col2 NOT GLOB '123
45[67]000[0-9][0-9][0-9][0-9][0-9][0-9]' THEN RAISE ( ABORT, 'Invalid
col2' )
       WHEN CASE "ditto for third check" END = 0 THEN RAISE ( ABORT,
'Invalid col1 or col2' ) END;
END;

On Mon, Apr 3, 2017 at 10:23 AM, Simon Slavin <[hidden email]> wrote:

>
> On 3 Apr 2017, at 12:41am, David Burgess <[hidden email]> wrote:
>
>> Bad trigger performance prevents me using your suggested solution.
>
> I’d like to see your TRIGGER.  But if a well-written TRIGGER can’t reach the performance you need (it’ll never be as fast as a constraint) then your best solution is to do sanity-checking in your programming language.
>
>> Is there a secret to making triggers perform like CHECK?
>
> No.  They do two different jobs.  CHECK is to ensure integrity of the TABLE.  A constraint applies to all the rows in the table all the time.  The thing you want does not involve CHECK since existing entries in the database which violate your rule are not a problem.
>
> TRIGGERs apply when things are added to, modified, or deleted from the TABLE.  But a TRIGGER can be used to check /new/ entries in the database.  It would seem that a TRIGGER might be useful to you.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



--
David Burgess
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 3.18 Problem

Simon Slavin-3

> On 3 Apr 2017, at 2:11am, David Burgess <[hidden email]> wrote:
>
> (I guess I should re-check
> to see if that has changed since 3.7)

Yep.  Many speedups in recent versions of SQLite.

> TRIGGER

I don’t see anything obvious wrong with your trigger, but I don’t use GLOB often.  Maybe someone else can recommend an improvement.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 3.18 Problem

Richard Hipp-3
On 4/2/17, Simon Slavin <[hidden email]> wrote:
>
>> On 3 Apr 2017, at 2:11am, David Burgess <[hidden email]> wrote:
>>
>> (I guess I should re-check
>> to see if that has changed since 3.7)
>
> Yep.  Many speedups in recent versions of SQLite.
>

But not in triggers :-)
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 3.18 Problem

David Burgess
My specific problem is solved with -

sqlite>  PRAGMA ignore_check_constraints = 1;
sqlite>  PRAGMA integrity_check;
sqlite>  PRAGMA ignore_check_constraints = 0;

An option on integrity_check() to do the same would be nice.

On Mon, Apr 3, 2017 at 11:40 AM, Richard Hipp <[hidden email]> wrote:

> On 4/2/17, Simon Slavin <[hidden email]> wrote:
>>
>>> On 3 Apr 2017, at 2:11am, David Burgess <[hidden email]> wrote:
>>>
>>> (I guess I should re-check
>>> to see if that has changed since 3.7)
>>
>> Yep.  Many speedups in recent versions of SQLite.
>>
>
> But not in triggers :-)
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



--
David Burgess
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 3.18 Problem

Dominique Devienne
In reply to this post by David Burgess
On Sun, Apr 2, 2017 at 3:50 PM, David Burgess <[hidden email]> wrote:

> I preferred the pre 3.18 behaviour. More flexible and fulfills a real
> world requirement.


The pre-3.18 behavior made it impossible to have the CHECK constraints
checked.
So I don't view that as "more flexible". If you bothered adding a CHECK
constraint,
for integrity, and given that SQLite allows to disable the CHECK constraint
on a
per-connection basis, don't you think it's important to be able to validate
the data
is still "correct" as per the *declarative* "rules" in effect?

This also mirrors what is available for foreign keys, so more logical, FWIW.

I disagree with Simon recommendation of using a TRIGGER instead.
CHECK constraints are about integrity. TRIGGERs are not.

If you have integrity rules that evolve with time, you either
1) update the old now-non-conforming data to follow the new rules; or
2) update the rules to allow both the old and the new format/rules for old
and new data.

Sure #2 may force you to recreate the table, since SQLite a little ALTER
TABLE/COLUMN
support, but rules typically change much less often that data does. New
integrity rules *is*
schema evolution, despite column names and types not changing.

CHECK constraints for new a filter for new insertions, they are declarative
*integrity rules*. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 3.18 Problem

James K. Lowden
In reply to this post by David Burgess
On Sun, 2 Apr 2017 23:50:01 +1000
David Burgess <[hidden email]> wrote:

> The rules for the format of these numbers has changed over time and
> the schema has changed with the rules of the time. PRAGMA
> integrity_check now complains about some rows not passing the
> constraint.

No one suggested the obvious solution: modify the CHECK constraint to
describe the allowed values in the column.  With a little cleverness,
perhaps the constraint could prevent use of the old form for new rows,
too.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...