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/
2010/4/28 Dan Bishop danbisho...@gmail.com:
...
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 test
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
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
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
pechni...@mobigroup.ru wrote:
See here:
Link to mail-archive:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg47382.html
2010/4/29 Pavel Ivanov paiva...@gmail.com:
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
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
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
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 experiment, an
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 INTO T2
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 to an
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
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 positive')
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 this situation without
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
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 paiva...@gmail.com:
But how we can resolve this situation without
manual types casting?
sqlite create trigger tr before
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 binding is
impossible
...@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 dirty
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
Alexey Pechnikov pechni...@mobigroup.ru
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 queries to
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 itandet...@mvps.org wrote:
Alexey Pechnikov pechni...@mobigroup.ru
wrote:
Do you want produce
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
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) =
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:
-5 0
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. Inside CHECK
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
sqlite CREATE TABLE T1 (N INTEGER CHECK(N = 0));
the constraint is applied
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 type coercion,
27 matches
Mail list logo
