Re: [sqlite] feature proposal - strong but dynamic typing

[Dan Bishop]

Darren Duncan wrote:
> Roger Binns wrote:
>   
>>> In fact this support might even be easier as it may only require 
>>> enhancements to 
>>> the SQL parser, which would generate VM opcodes like for a CHECK 
>>> constraint, 
>>> unless further work is done to optimize for the presented cases, or to 
>>> enhance 
>>> semantics.
>>>   
>> It has been stated in several places that the increasing items in the parser
>> will lead to various fields needing to go to the next size up which would
>> increase memory consumption.  There is also the whole backwards
>> compatibility angle - what would happen if the database was loaded into an
>> older version of SQLite which then ignored this whole UNIVERSAL thing
>> allowing "wrongly" typed data to be inserted?
>> 
>
> An added type name like UNIVERSAL would be completely backwards compatible 
> because, as far as I recall, if SQLite currently sees a type name it doesn't 
> recognize, then the column has no affinity and will accept any value, so same 
> behavior.  And so then, for older systems using that keyword would be 
> effectively a documentation convention.
>   
This is not correct. See http://www.sqlite.org/datatype3.html, section 2.1:

"""

The type affinity of a column is determined by the declared type of the 
column, according to the following rules:

   1.

  If the datatype contains the string "INT" then it is assigned
  INTEGER affinity.

   2.

  If the datatype of the column contains any of the strings "CHAR",
  "CLOB", or "TEXT" then that column has TEXT affinity. Notice that
  the type VARCHAR contains the string "CHAR" and is thus assigned
  TEXT affinity.

   3.

  If the datatype for a column contains the string "BLOB" or if no
  datatype is specified then the column has affinity NONE.

   4.

  If the datatype for a column contains any of the strings "REAL",
  "FLOA", or "DOUB" then the column has REAL affinity

   5.

  Otherwise, the affinity is NUMERIC.

If a table is created using a "CREATE TABLE  AS SELECT..." 
statement, then all columns have no datatype specified and they are 
given no affinity.

"""

#5 means that your UNIVERSAL type is not backwards-compatible, and #3 
means that it is not necessary.

Your proposal would break any databases that store strings or blobs in 
columns with unrecognized types. In particular, it would break all the 
tables I have that store timestamps as text in columns declared as 
TIMESTAMP. We could deal with this by adding a new affinity rule:

"""
If the datatype for a column contains either of the strings "DATE" or 
"TIME", then the column has DATETIME affinity.

A column with DATETIME affinity behaves in the same way as a column with 
NUMERIC affinity, except that in strict affinity mode TEXT and BLOB 
values are allowed only if they are valid time strings (as determined by 
the datetime() or julianday() function).
"""

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

Re: [sqlite] feature proposal - strong but dynamic typing

[Nicolas Williams]

On Fri, Oct 30, 2009 at 03:59:11PM -0500, Jay A. Kreibich wrote:
> On Fri, Oct 30, 2009 at 03:19:59PM -0500, Nicolas Williams scratched on the 
> wall:
> > I should add that a pragma that cause CHECK constraints to be
> > automatically created for enforcing strong typing in subsequent CREATE
> > TABLE statements 
> 
>   That's tricky.  Values have TYPES.  Columns have AFFINITIES.  There
>   is not a clear or obvious one-to-one mapping between them in all
>   cases.  The CREATE TABLE column datatypes can be used to figure out
>   the column affinity, but that doesn't always make it clear what
>   value type should go in the CHECK expression.

Indeed.

>   Also, CHECK expressions need to go into the CREATE TABLE definition.
>   They can't be added after-the-fact, like key triggers.  There are
>   ways around this, of course, but they're kind of messy.

That's actually a _feature_.  The pragma should affect only subsequent
table creation.

Note: I am not asking for this.  I'm only asking that whatever is done,
if anything, be backwards compatible, and preferably result in DB files
that are compatible with older versions of SQLite3.

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

Re: [sqlite] feature proposal - strong but dynamic typing

[Jay A. Kreibich]

On Fri, Oct 30, 2009 at 03:19:59PM -0500, Nicolas Williams scratched on the 
wall:
> I should add that a pragma that cause CHECK constraints to be
> automatically created for enforcing strong typing in subsequent CREATE
> TABLE statements 

  That's tricky.  Values have TYPES.  Columns have AFFINITIES.  There
  is not a clear or obvious one-to-one mapping between them in all
  cases.  The CREATE TABLE column datatypes can be used to figure out
  the column affinity, but that doesn't always make it clear what
  value type should go in the CHECK expression.



  Also, CHECK expressions need to go into the CREATE TABLE definition.
  They can't be added after-the-fact, like key triggers.  There are
  ways around this, of course, but they're kind of messy.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature proposal - strong but dynamic typing

[Darren Duncan]

I think that something several people had missed was that I specifically 
proposed the strong typing behavior to be activated by a new pragma, and unless 
people activate that pragma they would get the old behavior, so total backwards 
compatibility.  I see several people then proposed using the same pragma.

In any event, I am quite satisfied to drop the matter and no longer pursue this 
feature.

Let people write explicit CHECK constraints when they want strong typing, which 
also has the advantage of carrying that behavior backwards to older SQLite 
installations.

And also, CHECK constraints would be necessary to use anyway for the general 
case of constraints where the constraint isn't just "all values are of this 
generic type".

So, request withdrawn.

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

Re: [sqlite] feature proposal - strong but dynamic typing

[Nicolas Williams]

I should add that a pragma that cause CHECK constraints to be
automatically created for enforcing strong typing in subsequent CREATE
TABLE statements is rather like having FOREIGN KEY clauses automatically
generate triggers.  There's precedent, in other words, and it is a
simple way to implement strong typing.

Also, when you view the schema you'll see the CHECK clauses, and will
know not only that typing is enforced at INSERT/UPDATE time, but also
what type SQLite3 actually inferred from the declared type.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature proposal - strong but dynamic typing

[Nicolas Williams]

On Fri, Oct 30, 2009 at 01:30:31PM -0400, John Crenshaw wrote:

+1

I don't think this proposal can or will be accepted.

One reasonable idea, perhaps, would to have a pragma that causes
subsequent CREATE TABLE statements to get automatically generated CHECK
expressions that enforce typing.  Any CHECK expressions in the given
CREATE TABLE statements would have to be wrapped, but that seems simple
enough.

That way you get forwards- and backwards-compatibility for DB files,
schemas, and SQL statements, while while still having the options of
strong and dynamic typing and the ability to mix the two.

And you'd not need any ugly keywords like "STRONG" or "UNIVERSAL"
(which, because SQLite3 accepts any type names, would have compatibility
issues anyways).

You'd still pay for type checking at run-time, even when using strong
type checking.  I'm OK with that.

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

Re: [sqlite] feature proposal - strong but dynamic typing

[John Crenshaw]

> I believe I understand Darren's point (whether or not I care for them
> is another story).

Yes, you've understood Darren for the most part, but clearly don't understand 
the objections.

> On Fri, Oct 30, 2009 at 2:22 AM, Roger Binns  wrote:
> > -BEGIN PGP SIGNED MESSAGE-
> > Hash: SHA1
> >
> > Darren Duncan wrote:
> >> But on a newer SQLite that implements the stronger typing support I 
> >> proposed,
> >> when that feature is active then columns with declared types like 
> >> INTEGER/etc
> >> would enforce that only values of that type are stored there,
> >
> > I might have misunderstood you.  Do you really mean that a new SQLite
> > version should enforce the types with 'UNIVERSAL' meaning any?  Do you
> > really expect everyone to have to upgrade their database schemas for this?
>
> No, (I think what) Darren is saying is that a column with type
> UNIVERSAL will behave as if that column had no CHECKs at all. It would
> not enforce any type, and behave, more or less, like any SQLite column
> except for INTEGER PRIMARY KEY currently behaves. That is, UNIVERSAL
> would allow storing anything in it.

You said no, then answered yes. This proposal would require many thousands of 
existing schemas to be updated. Anyone who doesn't want the strong typing would 
have to update their schema to use the "UNIVERSAL" keyword. This isn't going to 
be acceptable to ANYBODY except the "strong typing" clan. Additionally, this 
would be quite the shock to users not participating in this thread, who may, 
without prior warning, see new random errors when they update. A likely sore 
spot is the TIMESTAMP which, due to the current lack of documentation and 
supporting APIs, may likely be used to store data in ANY of the 4 types right 
now.

> >
> >> shorthand for an appropriate CHECK constraint,
> >
> > Now I am even more confused.  There is this alleged group of people out
> > there who need type enforcing but are somehow unable to put in CHECK
> > constraints (which also let you addition stuff like range checks and other
> > restrictions on values), so the rest of us would have to start littering our
> > schemas with 'UNIVERSAL' to cater for them?
>
> Any column not declared as UNIVERSAL, so, INTEGER, REAL, BLOB, TEXT,
> perhaps even a new type called DATETIME, would behave as if CHECK
> CONSTRAINT were defined on them, allowing only the declared type of
> data to be stored in them.

Fortunately, I don't think this is exactly what is being proposed. The proposal 
(as I read it) only does the strong type checking on column types it 
recognizes, and others are left to the current model. If strong typing were 
done on any column not declared as UNIVERSAL, this would wreck even more 
schemas, since there are certainly going to be countless schemas using data 
types other than those that would be implemented.

SQLite currently takes *ANYTHING* as the typename. This means, for example, 
someone might choose to give each column a type based on the C/C++ type/class 
that they will use to manipulate it. Not good SQL, but functional SQLite and 
plenty clean. I personally use TIMESTAMP for what you called DATETIME and there 
are who knows how many different variations on that, used by other SQL engines.

> I see no problem with the existing tools, but, on the other hand, I
> really see no problem with Darren's suggestion as well other than it
> might make SQLite less Lite and more Heavy.
>
> But, I certainly see no backward compatibility issues with Darren's
> suggestion. His suggestion allows those who care for strong typing,
> but are too lazy to do it themselves, will actually have it done for
> them, and those who don't care for strong typing can use UNIVERSAL.

This IS a backwards compatibility issue. People get the new version of the 
library, but it behaves substantially differently than the previous version. 
The behavioral difference may break their app, and worse, it will not break at 
compile time, but only at runtime. This is a backwards compatibility sort of 
the worst type.

Worse yet, this could affect users more transparently than you think. Take, for 
example, the web site written in PHP that uses SQLite. One day PHP is updated 
to use the new strongly typed SQLite, then Apache is updated to use the latest 
PHP, finally, one by one, web hosting providers throughout the world update to 
the latest Apache. And a whole rash of websites suddenly have problems. These 
sites changed NOTHING, but suddenly their sites don't work?

I see SERIOUS problems with this proposal, especially in terms of backwards 
compatibility.

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

Re: [sqlite] feature proposal - strong but dynamic typing

[Simon Slavin]

On 30 Oct 2009, at 2:14pm, P Kishor wrote:

> Actually, there can be one bad effect of Darren's suggestion, now that
> I think of it, and that would be for those who don't care for strong
> typing. They will end up getting strong typing for all non-UNIVERSAL
> columns whether they like it or not, whether they expect it or not,
> unless there is a pragma as well to just disable strong typing
> completely. See, it is getting less Lite and more Heavy. At this
> point, those who are dissatisfied with SQLite should just move to
> PostGres of MySQL.

Yes.  The proper way to implement strong typing, for those who want  
it, is to introduce a keyword for strong typing, not change the  
meaning of existing keywords.  So for instance you could define

numberOrdered   INTEGER
numberSent  STRONG INTEGER

or something like that.  Without the word 'STRONG' column types would  
continue to mean what they've always meant.

But as others have said here, my opinion is that the enforcement of  
typing is not the job of a database engine, it's the job of the  
application, which can check types at the same time as it checks 'not- 
negative', and 'must-contain-only-printable-characters'.  It's a waste  
putting strong types into SQLite when late (or weak) typing is so much  
more powerful.

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

Re: [sqlite] feature proposal - strong but dynamic typing

[Scott Hess]

On Fri, Oct 30, 2009 at 7:14 AM, P Kishor  wrote:
> Actually, there can be one bad effect of Darren's suggestion, now that
> I think of it, and that would be for those who don't care for strong
> typing. They will end up getting strong typing for all non-UNIVERSAL
> columns whether they like it or not, whether they expect it or not,
> unless there is a pragma as well to just disable strong typing
> completely.

If someone were to implement this, the appropriate place is not in the
schema definition, the appropriate place is as a PRAGMA, perhaps
PRAGMA strong_typing.  When defined, schema would be transformed at
table creation time to add the appropriate checks.  With this
implementation you could also more easily compile the support out of
SQLite, because it's not a change to syntax, it's a change to how
tables are created, so when compiled out you get exactly the SQLite
you had before the code was added.  Another alternate might be "CREATE
PEDANTIC TABLE xxx", because then the only footprint is that
additional keyword, though I think PRAGMA is a lot more clear for
optional features.

That said, I agree with others that the feature is mis-guided.  If you
think that a column should be an integer, then when you store things
to that column use sqlite3_bin_int().  As an application developer,
you should NEVER let users define the type of data stored in your
database in the first place, you should ALWAYS explicitly validate
their input.  You could use this kind of type-checking to keep people
developing against your schema from doing bad things, but given the
nature of SQLite it's hard to see how useful that would be in practice
(versus a client-server system where many people may be sharing the
same production database instance).

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

Re: [sqlite] feature proposal - strong but dynamic typing

[Doug Currie]

On Oct 30, 2009, at 10:14 AM, P Kishor wrote:

> Actually, there can be one bad effect of Darren's suggestion, now that
> I think of it, and that would be for those who don't care for strong
> typing. They will end up getting strong typing for all non-UNIVERSAL
> columns whether they like it or not, whether they expect it or not,
> unless there is a pragma as well to just disable strong typing
> completely.

On Oct 29, 2009, at 5:33 PM, Darren Duncan wrote:

> Support for what I indicated could conceivably just be added like  
> how support
> for foreign keys was just added, and it could be turned on/off with  
> a pragma
> likewise to aid backwards compatibility, for people who wrote the  
> column types
> in their SQL but expected enforcement to be lax.

e

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

Re: [sqlite] feature proposal - strong but dynamic typing

[P Kishor]

I believe I understand Darren's point (whether or not I care for them
is another story).

On Fri, Oct 30, 2009 at 2:22 AM, Roger Binns  wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Darren Duncan wrote:
>> But on a newer SQLite that implements the stronger typing support I proposed,
>> when that feature is active then columns with declared types like INTEGER/etc
>> would enforce that only values of that type are stored there,
>
> I might have misunderstood you.  Do you really mean that a new SQLite
> version should enforce the types with 'UNIVERSAL' meaning any?  Do you
> really expect everyone to have to upgrade their database schemas for this?

No, (I think what) Darren is saying is that a column with type
UNIVERSAL will behave as if that column had no CHECKs at all. It would
not enforce any type, and behave, more or less, like any SQLite column
except for INTEGER PRIMARY KEY currently behaves. That is, UNIVERSAL
would allow storing anything in it.



>
>> shorthand for an appropriate CHECK constraint,
>
> Now I am even more confused.  There is this alleged group of people out
> there who need type enforcing but are somehow unable to put in CHECK
> constraints (which also let you addition stuff like range checks and other
> restrictions on values), so the rest of us would have to start littering our
> schemas with 'UNIVERSAL' to cater for them?

Any column not declared as UNIVERSAL, so, INTEGER, REAL, BLOB, TEXT,
perhaps even a new type called DATETIME, would behave as if CHECK
CONSTRAINT were defined on them, allowing only the declared type of
data to be stored in them.


>
> I have yet to see a clear demonstration of two things:
>
> Why developers who want particular type/value constraints are unable to just
> go ahead and use constraints?

There is really no understandable reason for this other that perhaps
psychology and a bad kind of laziness.

>
> Why developers who want 'strong types' don't realise that modulo type
> affinity you get out what you put in so don't put in "wrong" types!
>
> In short what problem actually needs to be solved and what is wrong with the
> existing tools for those who have the problem?

I see no problem with the existing tools, but, on the other hand, I
really see no problem with Darren's suggestion as well other than it
might make SQLite less Lite and more Heavy.

But, I certainly see no backward compatibility issues with Darren's
suggestion. His suggestion allows those who care for strong typing,
but are too lazy to do it themselves, will actually have it done for
them, and those who don't care for strong typing can use UNIVERSAL.

Actually, there can be one bad effect of Darren's suggestion, now that
I think of it, and that would be for those who don't care for strong
typing. They will end up getting strong typing for all non-UNIVERSAL
columns whether they like it or not, whether they expect it or not,
unless there is a pragma as well to just disable strong typing
completely. See, it is getting less Lite and more Heavy. At this
point, those who are dissatisfied with SQLite should just move to
PostGres of MySQL.


>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iEYEARECAAYFAkrqlEEACgkQmOOfHg372QTCPACgkdvchMq2NzAU7n4cSKXABUNF
> YGMAn3buLfY4gfVoEeyeTYGA2UC1I4dL
> =3FL+
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature proposal - strong but dynamic typing

[John Crenshaw]

Been watching this discussion go back and forth, and I'd like to weigh
in.

I'm generally a HUGE fan of strong typing, but this doesn't do it for
me. To me, strongly typed means a compiler catches my type mismatches
before the app goes out the door. In this case though, no matter what
you do, a mistake with your use of a type can never show up at compile
time. You'll only get the error at runtime.

Frankly, once the mistake is made, and the code deployed, I expect the
program to behave the best it possibly can. Making the application blow
up, rather than storing the data and moving on, is not a great plan in
my mind. The user doesn't *care* that a REAL was provided, but an
INTEGER was expected. The user cares that the app exploded on them and
didn't save the edit they just made.

If strong data typing *at the SQL level* is that important to you, this
is a job for constraints:
CHECK(typeof(product_id) = 'integer')

If you hate the idea of manually adding these constraints, you could
even write something to automatically add them for you. If you hate the
performance hit, you could add them only in your debug build.

As far as making the core universally reject data that doesn't match its
belief about the format that data should be in, I think this is a very
bad idea. It would break plenty of old code to provide a feature that is
already possible for which the benefit is questionable anyway. Since
SQLite can't catch my type errors at application compile time anyway, I
think I like the current behavior better.

Just my 2 cents.

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

Re: [sqlite] feature proposal - strong but dynamic typing

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Darren Duncan wrote:
> But on a newer SQLite that implements the stronger typing support I proposed, 
> when that feature is active then columns with declared types like INTEGER/etc 
> would enforce that only values of that type are stored there, 

I might have misunderstood you.  Do you really mean that a new SQLite
version should enforce the types with 'UNIVERSAL' meaning any?  Do you
really expect everyone to have to upgrade their database schemas for this?

> shorthand for an appropriate CHECK constraint, 

Now I am even more confused.  There is this alleged group of people out
there who need type enforcing but are somehow unable to put in CHECK
constraints (which also let you addition stuff like range checks and other
restrictions on values), so the rest of us would have to start littering our
schemas with 'UNIVERSAL' to cater for them?

I have yet to see a clear demonstration of two things:

Why developers who want particular type/value constraints are unable to just
go ahead and use constraints?

Why developers who want 'strong types' don't realise that modulo type
affinity you get out what you put in so don't put in "wrong" types!

In short what problem actually needs to be solved and what is wrong with the
existing tools for those who have the problem?

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkrqlEEACgkQmOOfHg372QTCPACgkdvchMq2NzAU7n4cSKXABUNF
YGMAn3buLfY4gfVoEeyeTYGA2UC1I4dL
=3FL+
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature proposal - strong but dynamic typing

[Darren Duncan]

Roger Binns wrote:
>> In fact this support might even be easier as it may only require 
>> enhancements to 
>> the SQL parser, which would generate VM opcodes like for a CHECK constraint, 
>> unless further work is done to optimize for the presented cases, or to 
>> enhance 
>> semantics.
> 
> It has been stated in several places that the increasing items in the parser
> will lead to various fields needing to go to the next size up which would
> increase memory consumption.  There is also the whole backwards
> compatibility angle - what would happen if the database was loaded into an
> older version of SQLite which then ignored this whole UNIVERSAL thing
> allowing "wrongly" typed data to be inserted?

An added type name like UNIVERSAL would be completely backwards compatible 
because, as far as I recall, if SQLite currently sees a type name it doesn't 
recognize, then the column has no affinity and will accept any value, so same 
behavior.  And so then, for older systems using that keyword would be 
effectively a documentation convention.

But on a newer SQLite that implements the stronger typing support I proposed, 
when that feature is active then columns with declared types like INTEGER/etc 
would enforce that only values of that type are stored there, like it is a 
shorthand for an appropriate CHECK constraint, and so marking a column 
UNIVERSAL 
in this situation is explicitly stating that, in the system where type 
constraints are enforced, that column may take any value.

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

Re: [sqlite] feature proposal - strong but dynamic typing

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Darren Duncan wrote:
> I should point out, for people reading this thread, that it is very possible 
> to 
> have both the amount of type flexibility that SQLite provides and have strict 
> typing, at the same time.

What I fail to understand with all these "complaints" about SQLite's typing
is what the problem actually is.  The documentation is very clear:

  http://sqlite.org/datatype3.html

But that aside if you don't want an integer stored in a column then don't
store an integer in that column!  You can use check constraints to ensure
data typing (as well as ranges and similar validation), and you can use
(case plus) cast if you want to munge the data from one type to another.

So what exactly is wrong with SQLite's behaviour of you get out what you put
in (modulo the affinity stuff)?

> In fact this support might even be easier as it may only require enhancements 
> to 
> the SQL parser, which would generate VM opcodes like for a CHECK constraint, 
> unless further work is done to optimize for the presented cases, or to 
> enhance 
> semantics.

It has been stated in several places that the increasing items in the parser
will lead to various fields needing to go to the next size up which would
increase memory consumption.  There is also the whole backwards
compatibility angle - what would happen if the database was loaded into an
older version of SQLite which then ignored this whole UNIVERSAL thing
allowing "wrongly" typed data to be inserted?

As the Doctor says, if it hurts when you do that, then don't do that!

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEUEARECAAYFAkrqET4ACgkQmOOfHg372QTO1ACfU/LTcp/6Ey3kVLEDgoCx+7Wb
JecAl0xYvI4/EOMG6XBVFHLaz0MhIMM=
=0w5f
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] feature proposal - strong but dynamic typing

[Darren Duncan]

This email is a feature proposal for SQLite; I can rewrite it if desired.

Ted Rolle wrote (in the "Late data typing ..." thread):
> Doesn't dynamic data typing lead to bad data?
> And proliferation of home-grown editing routines?
> It seems that a strict data typing at column definition time would be
> MUCH better.  For instance, date-editing routines...

I should point out, for people reading this thread, that it is very possible to 
have both the amount of type flexibility that SQLite provides and have strict 
typing, at the same time.

All you need to do, to retain SQLite's flexibility but also get strong typing, 
is support data types that are defined as unions of other data types, and that 
there should be a system-defined type that is a union of all data types; say 
call it "UNIVERSAL" for example.

Then you could say something like:

   CREATE TABLE mytbl (
 myint INTEGER,
 mytext TEXT,
 myanything UNIVERSAL,
 ...
   )

When this is supported, the DBMS can be strongly typed, and when users want to 
be able to declare a field that accepts any value, they declare it UNIVERSAL, 
and they use a more specific type otherwise.

Now, because SQLite already supports keeping any value in any field, it already 
has the foundation necessary to do what I indicated, because then INTEGER/etc 
is 
simply UNIVERSAL plus a constraint, expressed in a terser form than CHECK.

Support for what I indicated could conceivably just be added like how support 
for foreign keys was just added, and it could be turned on/off with a pragma 
likewise to aid backwards compatibility, for people who wrote the column types 
in their SQL but expected enforcement to be lax.

In fact this support might even be easier as it may only require enhancements 
to 
the SQL parser, which would generate VM opcodes like for a CHECK constraint, 
unless further work is done to optimize for the presented cases, or to enhance 
semantics.

I also want to emphasize that I strongly support the approach SQLite has taken 
on this issue historically, in contrast to many other DBMSs, by supporting any 
value in any field.  Tacking on the ability to support any value in any field 
would require a more substantial change to other DBMSs that have it deeply 
ingrained that each field is just an INTEGER or TEXT or whatever.

I also want to clarify that I strongly support each VALUE being identified with 
a type, with this being the most important thing, which SQLite does as far as 
it 
goes with its distinct Null|Int|Num|Text|Blob values.  And so a type definition 
for the purposes of a column definition is just a set of what values it allows.

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