Re: [sqlite] Strict affinity again

[Florian Weimer]

* D. Richard Hipp:

> SQLite is strongly typed,

Curiously, it's even more strongly typed than SQL:

sqlite> SELECT 1 = '1';
0

fw=> SELECT 1 = '1';
 ?column?
--
 t
(1 row)

(In SQL, quoted values are not of string type, but their type is
inferred from context.)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strict affinity again

[Kees Nuyt]

On Wed, 25 Mar 2009 13:06:24 -0400, "Wilson, Ron P"
 wrote:

>Cool!  I didn't think of doing that.  
>I presume this would incur a performance hit
>on insert/update to check the constraint 

Not much. The column data is _dynamically_ typed, 
so SQLite will determine the type of each 
value offered anyway.

>and sqlite3_prepare* would return SQLITE_CONSTRAINT 
>if the check failed.  Right?

Wrong. sqlite3_prepare* doesn't know the data you are going
to offer with sqlite3_bind*. The same 'prepared' statement
can be used with valid and invalid data.

CONSTRAINT violations will be discovered during VM execution
of your INSERT / UPDATE statements.
See how it works with something like:

EXPLAIN INSERT ... ;


>RW
>
>Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453
>
>
>
>
>
>If you want to place a restriction on a column such that it will only
>
>hold an integer (for example) you can use a CHECK constraint.
>
>
>
>  CREATE TABLE example1(x INTEGER CHECK( typeof(x)='integer' ));
>
>
>
>D. Richard Hipp
>
>d...@hwaci.com
>
>
>
>
>
>
>
>___
>
>sqlite-users mailing list
>
>sqlite-users@sqlite.org
>
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strict affinity again

[Thomas Briggs]

   I'd be willing to bet that amongst experienced SQLite users, you're
in the minority.

   More importantly, I don't think Dr. Hipp agrees with you, so the
discussion is very likely moot. :)

   -T

On Wed, Mar 25, 2009 at 9:02 AM,   wrote:
> Hi everyone,
>
> I'm new to sqlite and this mailing list and hope to get some help
> here. I've used SQLite for some projects now and I must say, that it
> is the fastes database I ever used. Great work!
>
> But there is one thing, that I really dislike, because I get errors
> sometimes with this and that is the type guessing or the untyped way
> sqlite returns the data. So I searched the web and found
> http://www.sqlite.org/datatype3.html where strict affinity is
> described. I thought great, but how do I enable this option.
>
> After some search I found the thread from Feb. 2008. It sounds like
> that isn't a big code change and Samuel Neff wrote exactly that what I
> think about this :
>
>> But the important point is that no matter how much discussion we have, we
>> will never all agree that untyped is better than typed or that typed is
>> better than typed.  That's why an option so individual developers can choose
>> is good.  We don't have to agree, with an option we can agree to disagree.
>>
>> Sam
>
> So now my question: Why is this not implemented? I'd really like this
> option!!
>
> Jan
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strict affinity again

[D. Richard Hipp]

On Mar 25, 2009, at 9:02 AM, sqlite.20.tomca...@spamgourmet.com wrote:

> Hi everyone,
>
> I'm new to sqlite and this mailing list and hope to get some help
> here. I've used SQLite for some projects now and I must say, that it
> is the fastes database I ever used. Great work!
>
> But there is one thing, that I really dislike, because I get errors
> sometimes with this and that is the type guessing or the untyped way
> sqlite returns the data. So I searched the web and found
> http://www.sqlite.org/datatype3.html where strict affinity is
> described. I thought great, but how do I enable this option.
>
> After some search I found the thread from Feb. 2008. It sounds like
> that isn't a big code change and Samuel Neff wrote exactly that what I
> think about this :
>
>> But the important point is that no matter how much discussion we  
>> have, we
>> will never all agree that untyped is better than typed or that  
>> typed is
>> better than typed.  That's why an option so individual developers  
>> can choose
>> is good.  We don't have to agree, with an option we can agree to  
>> disagree.
>>
>> Sam
>
> So now my question: Why is this not implemented? I'd really like this
> option!!


Let me just say (again) that SQLite is not "untyped".  It is  
dynamically typed.  Big difference.  SQLite is strongly typed, it just  
does not place arbitrary constraints on what types of data that can be  
stored in a particular column.  SQLite keeps the type information with  
the data itself, not on the data's container.

If you want to place a restriction on a column such that it will only  
hold an integer (for example) you can use a CHECK constraint.

  CREATE TABLE example1(x INTEGER CHECK( typeof(x)='integer' ));

D. Richard Hipp
d...@hwaci.com



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