Re: [sqlite] CHECK constraints and type affinity

FYI, for strict typing I prefer the ENFORCE public domain extension from http://sqlite.mobigroup.ru/src/wiki?name=WClark CREATE TABLE t (i enforce integer); insert into t values('-5'); Error: t.i must be type 'integer' This acts as (N INTEGER CHECK(TYPEOF(N) = 'integer') OR N IS NULL) but error

Re: [sqlite] CHECK constraints and type affinity

Agreed. I would hope that the application (at a higher level) is performing type checks on the input data before attempting any insertion... On Thu, Apr 29, 2010 at 8:28 AM, Igor Tandetnik wrote: > Alexey Pechnikov > wrote: > > Do you want produce

Re: [sqlite] CHECK constraints and type affinity

Alexey Pechnikov wrote: > Do you want produce SQL-injections security holes? When database > engine can convert datatypes on demand we may quoting all values from > web forms and all other external sources. Don't build SQL statements on the fly - use parameterized

Re: [sqlite] CHECK constraints and type affinity

I believe it isn't more complicated than check() constraints and hasn't such performance problem compared to check() constraints, not sure about your definition of "big and dirty schema". OTOH it has better error message... But anyway you have no other choice at this moment and there's no reason

Re: [sqlite] CHECK constraints and type affinity

s-boun...@sqlite.org on behalf of Alexey Pechnikov Sent: Thu 4/29/2010 9:07 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] CHECK constraints and type affinity This is just for fun, isn't it? It'is not more simple then cast(... as ...) and has performance problem and produce big and

Re: [sqlite] CHECK constraints and type affinity

10 8:59 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] CHECK constraints and type affinity Do you want produce SQL-injections security holes? When database engine can convert datatypes on demand we may quoting all values from web forms and all other external sources. Note: variable

Re: [sqlite] CHECK constraints and type affinity

This is just for fun, isn't it? It'is not more simple then cast(... as ...) and has performance problem and produce big and dirty database schema. 2010/4/29 Pavel Ivanov : >> But how we can resolve this situation without >> manual types casting? > > sqlite> create trigger tr

Re: [sqlite] CHECK constraints and type affinity

Do you want produce SQL-injections security holes? When database engine can convert datatypes on demand we may quoting all values from web forms and all other external sources. Note: variable binding is impossible for dynamically created views, triggers, etc. And modern languages can use string

Re: [sqlite] CHECK constraints and type affinity

sion Systems From: sqlite-users-boun...@sqlite.org on behalf of Alexey Pechnikov Sent: Thu 4/29/2010 8:25 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] CHECK constraints and type affinity Yes, I did understand. But how we can resolve th

Re: [sqlite] CHECK constraints and type affinity

> But how we can resolve this situation without > manual types casting? SQLite version 3.6.23.1 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t (n integer); sqlite> create trigger tr before insert on t begin select raise(fail, 'Value must be

Re: [sqlite] CHECK constraints and type affinity

Yes, I did understand. But how we can resolve this situation without manual types casting? Now datatyping working correct only with manual casting but it's hard to use: sqlite> CREATE TABLE T2 (N INTEGER CHECK(TYPEOF(N) = 'integer')); sqlite> INSERT INTO T2 VALUES('5'); Error: constraint failed

Re: [sqlite] CHECK constraints and type affinity

Paul Rigor (uci) wrote: > SQLite seems to be consistent if you actually provide the proper checks you > want to achieve. What's the deal again with prepending the check with "+"? The deal with "+" is my attempt to disprove the OP's suggestion that "the constraint is applied AFTER converting N

Re: [sqlite] CHECK constraints and type affinity

Hey all, SQLite seems to be consistent if you actually provide the proper checks you want to achieve. What's the deal again with prepending the check with "+"? sqlite> CREATE TABLE T2 (N INTEGER CHECK(N >= 0) CHECK(TYPEOF(N)=='integer')); sqlite> INSERT INTO T2 VALUES(-'q'); sqlite> INSERT

Re: [sqlite] CHECK constraints and type affinity

Alexey Pechnikov wrote: > It's bad solution: > > sqlite> CREATE TABLE T1 (N INTEGER CHECK(+N >= 0)); > sqlite> INSERT INTO T1 VALUES('-5'); > sqlite> INSERT INTO T1 VALUES('q'); > sqlite> select N, typeof(N) from T1; > -5|integer > q|text I wasn't proposing this as a solution. It was an

Re: [sqlite] CHECK constraints and type affinity

Similar conversions problems we can see in triggers and constraints too. See the first message in this topic. In TCL layer I did add the "typing when possible" logic but how to do it here? sqlite> INSERT INTO T2 VALUES('18'); In this expression value '18' may has more than single datatype in

Re: [sqlite] CHECK constraints and type affinity

Sorry, I cannot tell you anything about TCL - never used one. But I'm sure that it's some additional layer above SQLite. And if you find some inconsistencies there then it's inconsistencies in TCL, not in SQLite itself. Pavel On Thu, Apr 29, 2010 at 7:30 AM, Alexey Pechnikov

Re: [sqlite] CHECK constraints and type affinity

Link to mail-archive: http://www.mail-archive.com/sqlite-users@sqlite.org/msg47382.html 2010/4/29 Pavel Ivanov : > Sorry, I can't see your link from here and I don't understand what > you're talking about. > If you're trying to show me some email from this list either quote it

Re: [sqlite] CHECK constraints and type affinity

Sorry, I can't see your link from here and I don't understand what you're talking about. If you're trying to show me some email from this list either quote it or give a link from mail-archive.com. Pavel On Thu, Apr 29, 2010 at 7:12 AM, Alexey Pechnikov wrote: > See

Re: [sqlite] CHECK constraints and type affinity

See here: http://readlist.com/lists/sqlite.org/sqlite-users/6/34050.html "works differently" from shell and from file - are you really think, it's true? May be you know different SQL standart which is dependence of the _method_ of the starting SQL scripts?! 2010/4/29 Pavel Ivanov

Re: [sqlite] CHECK constraints and type affinity

> But the problem is internal logical inconsistency in datatyping. There's no internal inconsistency in SQLite. Everything is consistent and works using strict rules which are well explained in documentation and numerously discussed on this list. Just get used to the fact that SQLite works

Re: [sqlite] CHECK constraints and type affinity

2010/4/28 Dan Bishop : ... > Why the inconsistency? There are a lot of same problems. See http://www.mail-archive.com/sqlite-users@sqlite.org/msg47832.html sqlite> select 1='1'; 0 sqlite> create table test(a text); sqlite> insert into test values (1); sqlite> select * from

Re: [sqlite] CHECK constraints and type affinity

It's bad solution: sqlite> CREATE TABLE T1 (N INTEGER CHECK(+N >= 0)); sqlite> INSERT INTO T1 VALUES('-5'); sqlite> INSERT INTO T1 VALUES('q'); sqlite> select N, typeof(N) from T1; -5|integer q|text -- Best regards, Alexey Pechnikov. http://pechnikov.tel/

Re: [sqlite] CHECK constraints and type affinity

Black, Michael (IS) wrote: > Hmmm...when I get rid of the "+'" > CREATE TABLE T1 (N INTEGER CHECK(N >= 0)); > the constraint works > > Seems to me that "+N" is the same as "abs(N)". I'm not even sure of what the > intent of "+N" would be??? A unary plus in SQLite is a no-op, but it suppresses

Re: [sqlite] CHECK constraints and type affinity

Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of Igor Tandetnik Sent: Wed 4/28/2010 7:00 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] CHECK constraints and type affinity Dan Bishop wrote: > If I write >

Re: [sqlite] CHECK constraints and type affinity

Dan Bishop wrote: > If I write > > sqlite> CREATE TABLE T1 (N INTEGER CHECK(N >= 0)); > > the constraint is applied AFTER converting N to an integer. > > sqlite> INSERT INTO T1 VALUES('42'); > sqlite> INSERT INTO T1 VALUES('-5'); > SQL error: constraint failed A curious thing seems to happen.

Re: [sqlite] CHECK constraints and type affinity

Dan Bishop wrote: > sqlite> CREATE TABLE T1 (N INTEGER CHECK(N >= 0)); > > the constraint is applied AFTER converting N to an integer. > > sqlite> INSERT INTO T1 VALUES('42'); > sqlite> INSERT INTO T1 VALUES('-5'); > SQL error: constraint failed How do you know? Both expressions below are true:

[sqlite] CHECK constraints and type affinity

If I write sqlite> CREATE TABLE T1 (N INTEGER CHECK(N >= 0)); the constraint is applied AFTER converting N to an integer. sqlite> INSERT INTO T1 VALUES('42'); sqlite> INSERT INTO T1 VALUES('-5'); SQL error: constraint failed But if I write sqlite> CREATE TABLE T2 (N INTEGER CHECK(TYPEOF(N) =