Re: [sqlite] Re: CAST

[BardzoTajneKonto]

> Sqlite lets us advance our storage 
> capabilities into a more flexible world.

Sure, but it's not allways a good thing. Usually one column stores related 
data.
Related data mostly have the same type. Entering a value of different type is 
an error
which is silently ignored. Allowing different types gives us more 
flexibility, but is also
more error-prone. Ofcourse there are other databases that can be chosen 
instead of
SQLite if type safety is required, but compile time option wouldn't hurt 
SQLite in any way.


---
CTR  brzmi tajemniczo ? 
Sprawd¼ co mo¿esz zyskaæ na >>> http://link.interia.pl/f1a9d


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[John Stanton]

Robert Simpson wrote:

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 31, 2007 4:08 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: CAST

You have explained the problem, which is .NET not Sqlite.  You have
apparently done a fine job marrying the two but it might be more
logical
to suggest that .NET be made more flexible.

As for flexibility, programs in C or Assembler are only inflexible at
the level of the underlying machine or operating system.  They are a
tool to use to build an environment.

Initially the typing rules in Sqlite appeared to be a nusisance but on
deeper thought their utility became apparent and it was possible to use
them to great advantage.  To bloat or impair that advantage just to
match a particular concept like .NET would be a tragedy.

You might consider developing an SQL engine ideally adapted to .NET.



I don't really know how we got here :) I think SQLite is a fantastic engine,
fast, free and flexible -- I wouldn't have spent the last 2 years
maintaining an ADO.NET provider for it if I didn't love it.  I'm certainly
not going to throw the baby out with the bath water and try and roll my own
SQL engine -- it misses the point entirely.  I'm here to support SQLite.

I'm not complaining about SQLite's lack of typing in general.  Could I use a
little help in making SQLite fit more seamlessly into these typed
environments?  Sure, the more help I get the better!

I'd love it if I could issue a CAST([MyCol] AS HAIRYLLAMA) and have some way
to yank HAIRYLLAMA out of the statement's metadata so I could tell what
specific type that column was CAST to.

Heck, I'd love it even more if there was an additional parameter in the
user-def function callback to provide SQLite a string name of a type
returned from a function so we could have more descriptive typing in there
as well.

The problem isn't .NET.  The problem is that every other database on the
planet enforces type restrictions, and hence every generic database access
layer (ODBC, OLEDB, ADO, ADO.NET) is designed around that concept.  "Don't
use a wrapper" doesn't work for every circumstance, and I can't change the
ADO.NET spec -- though I do get around it where I can to support SQLite's
typelessness.

My opinion is: Since SQLite is typeless, then it should probably have a few
more functions dedicated to type description.  Preserving the destination
typename on a CAST as well as in a userdef function would go a long way
toward accomplishing that.  Or even better, some kind of extensible type
system that enabled us to have type-specific comparison callbacks.

However -- we're not there, and may never get there, so I'll continue to
hack.  That's what open source is for, afterall :)

Robert

There was a lot of fuss on the financial news tonight concerning Google 
Gears and its impact.  Sergey Brin was interviewed saying guilelessly 
that Google doesn't think of other companies, only users but the 
resident experts were saying that this is a blow at Microsoft.


Its is significant that the Sqlite based Google Gears seems to take 
advantage of typeless storage to produce a more general storage model. 
That was the point of my comments, Sqlite lets us advance our storage 
capabilities into a more flexible world.


The way we have chosen to use Sqlite has many parallels with the Google 
approach, presumably a logical development of the storage concepts.


We use PostgreSQL to map into environments requiring its capabilities 
and Sqlite where it fits well, such as in an application specific 
language we developed and with Javascript.  We don't use TCL but I 
understand it matches beautifully with Sqlite, by design.


If we were to rigidly lock into existing technologies we would still be 
using punch cards or perhaps clay tablets.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: CAST

[Robert Simpson]

> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 31, 2007 4:08 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Re: CAST
> 
> You have explained the problem, which is .NET not Sqlite.  You have
> apparently done a fine job marrying the two but it might be more
> logical
> to suggest that .NET be made more flexible.
> 
> As for flexibility, programs in C or Assembler are only inflexible at
> the level of the underlying machine or operating system.  They are a
> tool to use to build an environment.
> 
> Initially the typing rules in Sqlite appeared to be a nusisance but on
> deeper thought their utility became apparent and it was possible to use
> them to great advantage.  To bloat or impair that advantage just to
> match a particular concept like .NET would be a tragedy.
> 
> You might consider developing an SQL engine ideally adapted to .NET.

I don't really know how we got here :) I think SQLite is a fantastic engine,
fast, free and flexible -- I wouldn't have spent the last 2 years
maintaining an ADO.NET provider for it if I didn't love it.  I'm certainly
not going to throw the baby out with the bath water and try and roll my own
SQL engine -- it misses the point entirely.  I'm here to support SQLite.

I'm not complaining about SQLite's lack of typing in general.  Could I use a
little help in making SQLite fit more seamlessly into these typed
environments?  Sure, the more help I get the better!

I'd love it if I could issue a CAST([MyCol] AS HAIRYLLAMA) and have some way
to yank HAIRYLLAMA out of the statement's metadata so I could tell what
specific type that column was CAST to.

Heck, I'd love it even more if there was an additional parameter in the
user-def function callback to provide SQLite a string name of a type
returned from a function so we could have more descriptive typing in there
as well.

The problem isn't .NET.  The problem is that every other database on the
planet enforces type restrictions, and hence every generic database access
layer (ODBC, OLEDB, ADO, ADO.NET) is designed around that concept.  "Don't
use a wrapper" doesn't work for every circumstance, and I can't change the
ADO.NET spec -- though I do get around it where I can to support SQLite's
typelessness.

My opinion is: Since SQLite is typeless, then it should probably have a few
more functions dedicated to type description.  Preserving the destination
typename on a CAST as well as in a userdef function would go a long way
toward accomplishing that.  Or even better, some kind of extensible type
system that enabled us to have type-specific comparison callbacks.

However -- we're not there, and may never get there, so I'll continue to
hack.  That's what open source is for, afterall :)

Robert




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[Joe Wilson]

--- John Stanton <[EMAIL PROTECTED]> wrote:
> Sqlite lets you put in anything as the declared type.  "DEAD PARROT", 
> "MONGOOSE", "GODZILLA" or "DECIMAL(6,1)" are all acceptable declared 
> types.  Sqlite makes the underlying type TEXT if it is not obviously 
> numeric.

The default affinity type is SQLITE_AFF_NUMERIC if SQLite cannot determine 
the type:

  SQLite version 3.3.17
  Enter ".help" for instructions
  sqlite> create table t1(a GODZILLA);
  sqlite> insert into t1 values(3);
  sqlite> insert into t1 values('duck');
  sqlite> insert into t1 values('007');
  sqlite> insert into t1 values('0004.56');
  sqlite> select a, typeof(a) from t1;
  3|integer
  duck|text
  7|integer
  4.56|real

Note, if a column has no type specified, then its affinity is none:

  SQLite version 3.3.17
  Enter ".help" for instructions
  sqlite> create table n1(a);
  sqlite> insert into n1 values('009');
  sqlite> select a, typeof(a) from n1;
  009|text

But it's up to your program or sqlite wrapper to decide how to read each 
column with the appropriate sqlite3_column_* function.

/*
** Scan the column type name zType (length nType) and return the
** associated affinity type.
**
** This routine does a case-independent search of zType for the
** substrings in the following table. If one of the substrings is
** found, the corresponding affinity is returned. If zType contains
** more than one of the substrings, entries toward the top of
** the table take priority. For example, if zType is 'BLOBINT',
** SQLITE_AFF_INTEGER is returned.
**
** Substring | Affinity
** 
** 'INT' | SQLITE_AFF_INTEGER
** 'CHAR'| SQLITE_AFF_TEXT
** 'CLOB'| SQLITE_AFF_TEXT
** 'TEXT'| SQLITE_AFF_TEXT
** 'BLOB'| SQLITE_AFF_NONE
** 'REAL'| SQLITE_AFF_REAL
** 'FLOA'| SQLITE_AFF_REAL
** 'DOUB'| SQLITE_AFF_REAL
**
** If none of the substrings in the above table are found,
** SQLITE_AFF_NUMERIC is returned.
*/
char sqlite3AffinityType(const Token *pType){
  u32 h = 0;
  char aff = SQLITE_AFF_NUMERIC;
  const unsigned char *zIn = pType->z;
  const unsigned char *zEnd = >z[pType->n];

  while( zIn!=zEnd ){
h = (h<<8) + sqlite3UpperToLower[*zIn];
zIn++;
if( h==(('c'<<24)+('h'<<16)+('a'<<8)+'r') ){ /* CHAR */
  aff = SQLITE_AFF_TEXT;
}else if( h==(('c'<<24)+('l'<<16)+('o'<<8)+'b') ){   /* CLOB */
  aff = SQLITE_AFF_TEXT;
}else if( h==(('t'<<24)+('e'<<16)+('x'<<8)+'t') ){   /* TEXT */
  aff = SQLITE_AFF_TEXT;
}else if( h==(('b'<<24)+('l'<<16)+('o'<<8)+'b')  /* BLOB */
&& (aff==SQLITE_AFF_NUMERIC || aff==SQLITE_AFF_REAL) ){
  aff = SQLITE_AFF_NONE;
#ifndef SQLITE_OMIT_FLOATING_POINT
}else if( h==(('r'<<24)+('e'<<16)+('a'<<8)+'l')  /* REAL */
&& aff==SQLITE_AFF_NUMERIC ){
  aff = SQLITE_AFF_REAL;
}else if( h==(('f'<<24)+('l'<<16)+('o'<<8)+'a')  /* FLOA */
&& aff==SQLITE_AFF_NUMERIC ){
  aff = SQLITE_AFF_REAL;
}else if( h==(('d'<<24)+('o'<<16)+('u'<<8)+'b')  /* DOUB */
&& aff==SQLITE_AFF_NUMERIC ){
  aff = SQLITE_AFF_REAL;
#endif
}else if( (h&0x00FF)==(('i'<<16)+('n'<<8)+'t') ){/* INT */
  aff = SQLITE_AFF_INTEGER;
  break;
}
  }

  return aff;
}



   

Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, 
photos & more. 
http://mobile.yahoo.com/go?refer=1GNXIC

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[John Elrick]

John Stanton wrote:

Sqlite lets you put in anything as the declared type.  "DEAD PARROT", 
"MONGOOSE", "GODZILLA" or "DECIMAL(6,1)" are all acceptable declared 
types.  Sqlite makes the underlying type TEXT if it is not obviously 
numeric.



Thanks for the clarification.  I wasn't aware of that.


John

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[John Stanton]

John Elrick wrote:

John Stanton wrote:


John Elrick wrote:




SNIP

Introspection would occur via this mechanism and would even move all 
introspection for any given system behind a common interface.


Just a thought.


John Elrick

CREATE TABLE already stores the type as its declared type.  The user 
has that available to enforce type restrictions or to direct type 
conversions.



My apologies, I meant "any type" as in types which are not defined by 
Sqlite, such as DATE, TIMESTAMP, ADDRESS, LEFT_HANDED_SPANNER, or 
DEAD_PARROT. 


John

Sqlite lets you put in anything as the declared type.  "DEAD PARROT", 
"MONGOOSE", "GODZILLA" or "DECIMAL(6,1)" are all acceptable declared 
types.  Sqlite makes the underlying type TEXT if it is not obviously 
numeric.
- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[Michael Schlenker]

John Elrick schrieb:

John Stanton wrote:

John Elrick wrote:



SNIP
Introspection would occur via this mechanism and would even move all 
introspection for any given system behind a common interface.


Just a thought.


John Elrick

CREATE TABLE already stores the type as its declared type.  The user 
has that available to enforce type restrictions or to direct type 
conversions.


My apologies, I meant "any type" as in types which are not defined by 
Sqlite, such as DATE, TIMESTAMP, ADDRESS, LEFT_HANDED_SPANNER, or 
DEAD_PARROT.


Which is what John Stanton explained. 'any type' may be the 'declared type' for 
create table and is registered
in the master table.

But those declared types are not used for deriving types in expressions, so if 
any non trivial expression
is used in the query you only get the predefined SQLite types (most often 
Strings).

Michael

--
Michael Schlenker
Software Engineer

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Straße 1-3   Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/  E-Mail: [EMAIL PROTECTED]

Sitz der Gesellschaft: Bremen | Geschäftsführer: Karl Heinz Zachries
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[John Elrick]

John Stanton wrote:

John Elrick wrote:



SNIP
Introspection would occur via this mechanism and would even move all 
introspection for any given system behind a common interface.


Just a thought.


John Elrick

CREATE TABLE already stores the type as its declared type.  The user 
has that available to enforce type restrictions or to direct type 
conversions.


My apologies, I meant "any type" as in types which are not defined by 
Sqlite, such as DATE, TIMESTAMP, ADDRESS, LEFT_HANDED_SPANNER, or 
DEAD_PARROT.




John

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[John Stanton]

John Elrick wrote:

Michael Schlenker wrote:


A. Pagaltzis schrieb:


* Samuel R. Neff <[EMAIL PROTECTED]> [2007-05-30 14:55]:


SQLite's typelessness is an asset if you work only with SQLite
but in any application that uses multiple database engines of
which SQLite is only one supported engine, the non-standard
typelessness is something that has to be worked around.



Can you give an example of such a case? I work with several
different DBMSs, myself, and I have yet to run into trouble with
SQLite’s approach. Can you give a reallife example?



Start by already having a wrapper that allows type introspection (
e.g. DESCRIBE on oracle and reading the result set to find out what 
types are returned)

such a feature is basically non-portable to SQLite.



Although it would require some work, a thought that comes to mind would 
be to build a wrapper for create table which would permit you define any 
types you wish and then store the information in a separate table which 
in some way mirrors sqlite_master.


Introspection would occur via this mechanism and would even move all 
introspection for any given system behind a common interface.


Just a thought.


John Elrick

CREATE TABLE already stores the type as its declared type.  The user has 
that available to enforce type restrictions or to direct type conversions.


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[John Elrick]

Michael Schlenker wrote:

A. Pagaltzis schrieb:

* Samuel R. Neff <[EMAIL PROTECTED]> [2007-05-30 14:55]:

SQLite's typelessness is an asset if you work only with SQLite
but in any application that uses multiple database engines of
which SQLite is only one supported engine, the non-standard
typelessness is something that has to be worked around.


Can you give an example of such a case? I work with several
different DBMSs, myself, and I have yet to run into trouble with
SQLite’s approach. Can you give a reallife example?


Start by already having a wrapper that allows type introspection (
e.g. DESCRIBE on oracle and reading the result set to find out what 
types are returned)

such a feature is basically non-portable to SQLite.


Although it would require some work, a thought that comes to mind would 
be to build a wrapper for create table which would permit you define any 
types you wish and then store the information in a separate table which 
in some way mirrors sqlite_master.


Introspection would occur via this mechanism and would even move all 
introspection for any given system behind a common interface.


Just a thought.


John Elrick

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[John Stanton]

You have explained the problem, which is .NET not Sqlite.  You have 
apparently done a fine job marrying the two but it might be more logical 
to suggest that .NET be made more flexible.


As for flexibility, programs in C or Assembler are only inflexible at 
the level of the underlying machine or operating system.  They are a 
tool to use to build an environment.


Initially the typing rules in Sqlite appeared to be a nusisance but on 
deeper thought their utility became apparent and it was possible to use 
them to great advantage.  To bloat or impair that advantage just to 
match a particular concept like .NET would be a tragedy.


You might consider developing an SQL engine ideally adapted to .NET.

Robert Simpson wrote:

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 29, 2007 3:56 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: CAST

You are looking for a fit to one particular restrictive, proprietary
environment.  Our approach has been to work with the spirit of Sqlite
and to its strengths and to that end we designed out environment
accordingly.  Sqlite's typing has become a major asset, not a
difficulty.



All environments, proprietary or not, are restrictive in one way or another
- including C.  SQLite is flexible and adaptable, and capable of being
wedged into quite a few places -- which is what makes it a great little
engine.

For you that means captializing on SQLite's strengths and using its
typelessness as an asset.  For me, it means bringing SQLite to a mass of
.NET folks (Mono and MS) who would otherwise pass it by.  If that means
kludging a type system together to hide SQLite's typelessness, so be it.
I'd rather blunt the edge than throw the whole knife out.

Robert




-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[Michael Schlenker]

A. Pagaltzis schrieb:

* Samuel R. Neff <[EMAIL PROTECTED]> [2007-05-30 14:55]:

SQLite's typelessness is an asset if you work only with SQLite
but in any application that uses multiple database engines of
which SQLite is only one supported engine, the non-standard
typelessness is something that has to be worked around.


Can you give an example of such a case? I work with several
different DBMSs, myself, and I have yet to run into trouble with
SQLite’s approach. Can you give a reallife example?


Start by already having a wrapper that allows type introspection (
e.g. DESCRIBE on oracle and reading the result set to find out what types are 
returned)
such a feature is basically non-portable to SQLite.
This works fine if one gets the data direct from a table with declared types, 
which are
introspectable, but it breaks as soon as computed results (aggregates or other) 
are
included.
Basically user code fires any odd SQL at the database and expects to get a 
table structure with data
cast correctly to the 'wrapper native' datatypes. Declaring the expected types 
for the resulting row
would work, but if the wrapper only supports the introspectiv result binding 
and you have some 100k
lines of code using the wrapper you have a problem with the sqlite approach.
(its a problem with the wrapper APIs/usage, not really with SQLite)

Michael

--
Michael Schlenker
Software Engineer

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Straße 1-3   Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/  E-Mail: [EMAIL PROTECTED]

Sitz der Gesellschaft: Bremen | Geschäftsführer: Karl Heinz Zachries
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: CAST

[Samuel R. Neff]

afaik strict affininity mode hasn't been implemented.

>From 

http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq

"
Q) How can the strict affinity mode be used which is claimed to exist on
http://www.sqlite.org/datatype3.html

A) This has not been implemented as of version 3.3.13. 
"

Sam

---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, May 30, 2007 12:04 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Re: CAST


> I for one would be in favor of an option to enforce strict
> typing (compile time option).

"SQLite version 3 will feature two other affinity modes, as follows:
Strict affinity mode. In this mode if a conversion between storage classes
is 
ever required, the database engine returns an error and the current 
statement is rolled back."

I hope it means that SQLite will behave like any other database, and errors 
during insert will be detected while they are made, not in some unspecified 
time in the future.
This is probably not a problem with machine generated SQLs, but if a human
is 
allowed to enter SQLs, working with a database may be difficult.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: CAST

[BardzoTajneKonto]

> I for one would be in favor of an option to enforce strict
> typing (compile time option).

"SQLite version 3 will feature two other affinity modes, as follows:
Strict affinity mode. In this mode if a conversion between storage classes is 
ever required, the database engine returns an error and the current 
statement is rolled back."

I hope it means that SQLite will behave like any other database, and errors 
during insert will be detected while they are made, not in some unspecified 
time in the future.
This is probably not a problem with machine generated SQLs, but if a human is 
allowed to enter SQLs, working with a database may be difficult.


--
Wyjatkowo niegrzeczne kartki na Dzien Dziecka 
>>> http://link.interia.pl/f1a96


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[Michael Schlenker]

Samuel R. Neff schrieb:

SQLite's typelessness is an asset if you work only with SQLite but in any
application that uses multiple database engines of which SQLite is only one
supported engine, the non-standard typelessness is something that has to be
worked around.  I for one would be in favor of an option to enforce strict
typing (compile time option).


Quite true for many cases. Strict typing would help in many cases when using or 
maintaining wrappers,
especially concerning the results of expressions like CAST where there is no 
meaningful defined type.

But your wrong about the 'if working with multiple database engines' 
generalization. Its more
of a language feature, e.g. in Tcl you nearly always have typeless interfaces 
and it works fine,
even when accessing multiple databases (oracle, postgres, mysql + sqlite for 
example).
(the typed interfaces break more often, e.g. Windows COM scripting with 
VARIANTS).

So as a compile time option it would surely be a great addition for all those 
that have to maintain wrappers.

Michael

--
Michael Schlenker
Software Engineer

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Straße 1-3   Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/  E-Mail: [EMAIL PROTECTED]

Sitz der Gesellschaft: Bremen | Geschäftsführer: Karl Heinz Zachries
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: CAST

[Samuel R. Neff]

SQLite's typelessness is an asset if you work only with SQLite but in any
application that uses multiple database engines of which SQLite is only one
supported engine, the non-standard typelessness is something that has to be
worked around.  I for one would be in favor of an option to enforce strict
typing (compile time option).

Sam 


---
We're Hiring! Seeking a passionate developer to join our team building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 29, 2007 6:56 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: CAST

You are looking for a fit to one particular restrictive, proprietary 
environment.  Our approach has been to work with the spirit of Sqlite 
and to its strengths and to that end we designed out environment 
accordingly.  Sqlite's typing has become a major asset, not a difficulty.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: CAST

[Robert Simpson]

> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 29, 2007 3:56 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Re: CAST
>
> You are looking for a fit to one particular restrictive, proprietary
> environment.  Our approach has been to work with the spirit of Sqlite
> and to its strengths and to that end we designed out environment
> accordingly.  Sqlite's typing has become a major asset, not a
> difficulty.

All environments, proprietary or not, are restrictive in one way or another
- including C.  SQLite is flexible and adaptable, and capable of being
wedged into quite a few places -- which is what makes it a great little
engine.

For you that means captializing on SQLite's strengths and using its
typelessness as an asset.  For me, it means bringing SQLite to a mass of
.NET folks (Mono and MS) who would otherwise pass it by.  If that means
kludging a type system together to hide SQLite's typelessness, so be it.
I'd rather blunt the edge than throw the whole knife out.

Robert




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[John Stanton]

Robert Simpson wrote:

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 29, 2007 8:40 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: CAST

You have just given an excellent explanation of why the wrapper
approach
is flawed.  Think about it.



Every approach not deliberately designed around a specific module's exposed
API is fundamentally flawed in that it is limited by the constraints of the
wrapper.  The answer is not "don't use a wrapper" though.

"Think about it"
In my case, I can only speak for .NET and Microsoft environments, so here
goes:

1.  The complexities of interop'ing with SQLite's API are enormous -- an
insurmountable feat for a novice programmer to get right.  As a matter of
fact, most would simply get it wrong, blame it on SQLite, and switch to a
database engine that works better with their chosen design environment.

2.  A mountain of custom code has to be written to interact with SQLite, all
to use an engine who's familiar SQL92 syntax is so tantalizingly close to
the other databases they've used that one can't help to wonder "why do I
have to go to all this trouble just to use the same SQL syntax as my other
database programs?"  A wrapper solves this problem.

3.  A user can learn one API and apply it to multiple databases, instead of
learning one database API and applying it to a single type of database.

4.  The user can write engine-agnostic code and concentrate on the SQL
variations rather than wondering if they get the API code right and called
things in the right sequence.

5.  The wrapper expands the influence and usage of SQLite significantly.

Lets face it ... everyone likes potato chips, but if you had to make them
yourself everytime you got a craving, you'd probably think twice.  On the
other hand, if someone already made them and all you had to do was pick them
off the shelf, you'd be much more inclined to eat them.

So yes, homemade potato chips do taste better than the ones you buy at the
store, provided you get good potatoes, prepare them, peel them with a proper
tool, and watch them in the fryer carefully to avoid burning them.  Of
course you stink up the house and make an enormous mess, but that's the
price you pay for good chips.

So yes, the wrapper approach is flawed, but most folks don't want to stink
up their house trying to roll their own access layer.

Robert

You are looking for a fit to one particular restrictive, proprietary 
environment.  Our approach has been to work with the spirit of Sqlite 
and to its strengths and to that end we designed out environment 
accordingly.  Sqlite's typing has become a major asset, not a difficulty.




-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[Don Lewis]

I agree as well.  We are upgrading a large, old application originally
written almost 20 years ago.  The additional time necessary  for
the most elegant solution can be very expensive.  PCs today are
powerful and cheap.  The end user does not care if there is another
layer inside, he only wants the application to work.  Some of our
customers are very small where SQLite, with its one database file
and no administration required, is perfect.  Many customers will
insist that we connect to their corporate database servers.  Robert's
wrapper should make this possible with minimal fuss.

Don

- Original Message - 
From: "Samuel R. Neff" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Tuesday, May 29, 2007 11:06 AM
Subject: RE: [sqlite] Re: CAST



Actually I'd say he gave a great explanation of why the wrapper approach is
so important.  Robert went through all the work to make SQLite perform in a
scenario compatible with many other databases so now the users of his
wrapper don't have to.

Saying not to use wrappers when programming in straight C and using only
SQLite is one thing, but of course when developing in any other language or
when supporting multiple databases wrappers are essential (all of our
applications are in .NET and some support both SQLite and MSSQL).  We would
never have considered using SQLite for our product if it wasn't for Robert's
SQLite.NET wrapper.

Sam



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: CAST

[Samuel R. Neff]

Actually I'd say he gave a great explanation of why the wrapper approach is
so important.  Robert went through all the work to make SQLite perform in a
scenario compatible with many other databases so now the users of his
wrapper don't have to.

Saying not to use wrappers when programming in straight C and using only
SQLite is one thing, but of course when developing in any other language or
when supporting multiple databases wrappers are essential (all of our
applications are in .NET and some support both SQLite and MSSQL).  We would
never have considered using SQLite for our product if it wasn't for Robert's
SQLite.NET wrapper.

Sam


---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 29, 2007 11:40 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: CAST

You have just given an excellent explanation of why the wrapper approach 
is flawed.  Think about it.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: CAST

[Robert Simpson]

> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 29, 2007 8:40 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Re: CAST
>
> You have just given an excellent explanation of why the wrapper
> approach
> is flawed.  Think about it.

Every approach not deliberately designed around a specific module's exposed
API is fundamentally flawed in that it is limited by the constraints of the
wrapper.  The answer is not "don't use a wrapper" though.

"Think about it"
In my case, I can only speak for .NET and Microsoft environments, so here
goes:

1.  The complexities of interop'ing with SQLite's API are enormous -- an
insurmountable feat for a novice programmer to get right.  As a matter of
fact, most would simply get it wrong, blame it on SQLite, and switch to a
database engine that works better with their chosen design environment.

2.  A mountain of custom code has to be written to interact with SQLite, all
to use an engine who's familiar SQL92 syntax is so tantalizingly close to
the other databases they've used that one can't help to wonder "why do I
have to go to all this trouble just to use the same SQL syntax as my other
database programs?"  A wrapper solves this problem.

3.  A user can learn one API and apply it to multiple databases, instead of
learning one database API and applying it to a single type of database.

4.  The user can write engine-agnostic code and concentrate on the SQL
variations rather than wondering if they get the API code right and called
things in the right sequence.

5.  The wrapper expands the influence and usage of SQLite significantly.

Lets face it ... everyone likes potato chips, but if you had to make them
yourself everytime you got a craving, you'd probably think twice.  On the
other hand, if someone already made them and all you had to do was pick them
off the shelf, you'd be much more inclined to eat them.

So yes, homemade potato chips do taste better than the ones you buy at the
store, provided you get good potatoes, prepare them, peel them with a proper
tool, and watch them in the fryer carefully to avoid burning them.  Of
course you stink up the house and make an enormous mess, but that's the
price you pay for good chips.

So yes, the wrapper approach is flawed, but most folks don't want to stink
up their house trying to roll their own access layer.

Robert
 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[John Stanton]

Robert Simpson wrote:

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 29, 2007 6:18 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: CAST

Your comments endorse the approach we took which was to avoid the
wrapper concept entirely with its inherent limitations  We use Sqlite
as
an embedded database in an application server rather than trying to
integrate an API which wraps Sqlite.

In the case of the date we implement date arithmetic and comparison
functions building upon the excellent date primitives in the Sqlite
source.  However your case does not require any special functions as it
is handled simply by the regular Sqlite date functions.

An example of the use of date arithmetic might be:

SELECT * FROM mytab WHERE days_overdue(due_date) > 90;

We implement the user functions either as native code or as Javascript.
 The Javascript has the advantage that the text is stored in the
database so the functions can be state-driven.



Ok, I grant you that was a bad example -- I was in a hurry when I posted it.
It also requires that datetimes are stored in the database as a string.  If
someone used a numeric type instead, you'd be forced into using a
user-defined function for a comparison against a literal string.  If all
users were nice and tidy and used parameterized queries, the world would be
a better place -- but try as you might to drill it into folks, they still
throw their literals into a query and defeat your nice inline type
adjusters.

I've had to use a sturdy shoehorn and a lot of grease to squeeze SQLite into
the ADO.NET vNext (Entity Framework) recently, so a lot of my type
frustrations stem from that effort.

MS's Entity Framework uses CAST() liberally in its SQL construction, and
expects the returned values to match up to the type it was CAST() to --
which is currently impossible to do.  I can't cast to a datetime, guid,
int32, float, etc in SQLite and there's no way for me to tell what datatype
was mentioned in the CAST statement for a given returned column.  So when
I'm queried for the type of a column returned from one of these SELECT's,
there's no way for me to give back an accurate type.

I've hacked it up and done it, but its a bit ugly.  Fortunately users don't
need to see the underlying SQL generated from the Entity Framework :)

Robert

You have just given an excellent explanation of why the wrapper approach 
is flawed.  Think about it.




-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: CAST

[Robert Simpson]

> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 29, 2007 6:18 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Re: CAST
>
> Your comments endorse the approach we took which was to avoid the
> wrapper concept entirely with its inherent limitations  We use Sqlite
> as
> an embedded database in an application server rather than trying to
> integrate an API which wraps Sqlite.
> 
> In the case of the date we implement date arithmetic and comparison
> functions building upon the excellent date primitives in the Sqlite
> source.  However your case does not require any special functions as it
> is handled simply by the regular Sqlite date functions.
> 
> An example of the use of date arithmetic might be:
> 
> SELECT * FROM mytab WHERE days_overdue(due_date) > 90;
> 
> We implement the user functions either as native code or as Javascript.
>   The Javascript has the advantage that the text is stored in the
> database so the functions can be state-driven.

Ok, I grant you that was a bad example -- I was in a hurry when I posted it.
It also requires that datetimes are stored in the database as a string.  If
someone used a numeric type instead, you'd be forced into using a
user-defined function for a comparison against a literal string.  If all
users were nice and tidy and used parameterized queries, the world would be
a better place -- but try as you might to drill it into folks, they still
throw their literals into a query and defeat your nice inline type
adjusters.

I've had to use a sturdy shoehorn and a lot of grease to squeeze SQLite into
the ADO.NET vNext (Entity Framework) recently, so a lot of my type
frustrations stem from that effort.

MS's Entity Framework uses CAST() liberally in its SQL construction, and
expects the returned values to match up to the type it was CAST() to --
which is currently impossible to do.  I can't cast to a datetime, guid,
int32, float, etc in SQLite and there's no way for me to tell what datatype
was mentioned in the CAST statement for a given returned column.  So when
I'm queried for the type of a column returned from one of these SELECT's,
there's no way for me to give back an accurate type.

I've hacked it up and done it, but its a bit ugly.  Fortunately users don't
need to see the underlying SQL generated from the Entity Framework :)

Robert




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[John Stanton]

Robert Simpson wrote:

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Monday, May 28, 2007 4:21 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: CAST

We actually do that with our Sqlite interfaces.  We use the declared
type to specify the type and perform a conversion when necessary.  For
example if the declared type of a column is DATE we know that we
actually have a FLOAT so when we call a Javascript SQL function for
example the Sqlite user function transforms the floating point number
declared as a DATE to be a Javascript Date object.

A FLOAT with declared type DATE may be transformed into a date string
in
accordance with the rules of the chosen locale when being embedded in
an
HTML page.

Our applications use DECIMAL arithmetic for accuracy so when a column
is
declared as DECIMAL its actual type is TEXT but arithmetic rules are
applied.  If Sqlite has decided to make it an INTEGER or a FLOAT the
correct conversion is made with the declared precision and scale.



I use the declared type as well (where possible) in the SQLite .NET
provider.  Still, it'd be better if we could have an extensible type system
built into the engine itself so one could perform literal to column
comparisons and other SQL statements that are beyond the scope of a wrapper
to provide type adjustments for.

Your date example is a perfect example of a place where an extensible type
system would be ideal.  A SQL statement against a DATE field such as ...

  SELECT * FROM FOO WHERE [TheDate] < '2007/01/01'

...is pretty much impossible to fix in a wrapper -- but if we had an
extensible type system we could provide our own comparison func that takes
[TheDate]'s value and the literal value and figures out how to interpret and
compare the two values.

Robert

Your comments endorse the approach we took which was to avoid the 
wrapper concept entirely with its inherent limitations  We use Sqlite as 
an embedded database in an application server rather than trying to 
integrate an API which wraps Sqlite.


In the case of the date we implement date arithmetic and comparison 
functions building upon the excellent date primitives in the Sqlite 
source.  However your case does not require any special functions as it 
is handled simply by the regular Sqlite date functions.


An example of the use of date arithmetic might be:

SELECT * FROM mytab WHERE days_overdue(due_date) > 90;

We implement the user functions either as native code or as Javascript. 
 The Javascript has the advantage that the text is stored in the 
database so the functions can be state-driven.




-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: CAST

[Robert Simpson]

> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED]
> Sent: Monday, May 28, 2007 4:21 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Re: CAST
>
> We actually do that with our Sqlite interfaces.  We use the declared
> type to specify the type and perform a conversion when necessary.  For
> example if the declared type of a column is DATE we know that we
> actually have a FLOAT so when we call a Javascript SQL function for
> example the Sqlite user function transforms the floating point number
> declared as a DATE to be a Javascript Date object.
> 
> A FLOAT with declared type DATE may be transformed into a date string
> in
> accordance with the rules of the chosen locale when being embedded in
> an
> HTML page.
> 
> Our applications use DECIMAL arithmetic for accuracy so when a column
> is
> declared as DECIMAL its actual type is TEXT but arithmetic rules are
> applied.  If Sqlite has decided to make it an INTEGER or a FLOAT the
> correct conversion is made with the declared precision and scale.

I use the declared type as well (where possible) in the SQLite .NET
provider.  Still, it'd be better if we could have an extensible type system
built into the engine itself so one could perform literal to column
comparisons and other SQL statements that are beyond the scope of a wrapper
to provide type adjustments for.

Your date example is a perfect example of a place where an extensible type
system would be ideal.  A SQL statement against a DATE field such as ...

  SELECT * FROM FOO WHERE [TheDate] < '2007/01/01'

...is pretty much impossible to fix in a wrapper -- but if we had an
extensible type system we could provide our own comparison func that takes
[TheDate]'s value and the literal value and figures out how to interpret and
compare the two values.

Robert




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[John Stanton]

Read about manifest typing and it will become clear.

[EMAIL PROTECTED] wrote:
SQLite does not have a dedicated DATE type. 



I know that, but why it does't create appropriate column definition ?
create table tab(col date);
creates a table with "date" type. 
create table tab2 as select * from tab;

also.
This type does't do much, but it can be queried with sqlite3_column_decltype.
This way I know what to do with text stored in the database.
Currently the only way I see is to create table and then insert .. as select
which seems to be weird in presence of a function that "changes the datatype".



- Original Message -
From: "Igor Tandetnik" <[EMAIL PROTECTED]>
To: "SQLite" 
Date: Mon, 28 May 2007 10:36:50 -0400
Subject: [sqlite] Re: CAST



[EMAIL PROTECTED] wrote:


I'm wandering if CAST is supposed to work?


Yes.



sqlite> create table tab(col date);
sqlite> insert into tab values('1994-11-11');
sqlite> create table tab2 as select cast(col as DATE) from tab;
sqlite> .schema tab2
CREATE TABLE tab2("cast(col as DATE)");
sqlite> select * from tab2;
1994


SQLite does not have a dedicated DATE type. See 
http://sqlite.org/datatype3.html . When given an unknown type, SQlite 
assumes numeric. That's why CAST('1994-11-11' as DATE) produces 1994. So 
would CAST('1994-11-11' as ANY_RANDOM_STRING).


It is customary to store dates as strings in SQLite. Several functions 
are provided to manipulate dates in this representation.


Igor Tandetnik 







-


To unsubscribe, send email to [EMAIL PROTECTED]




-







--
Ile masz w domu niepotrzebnych rzeczy?
Wymien sie z sasiadami >> http://link.interia.pl/f1a93



-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[John Stanton]

Robert Simpson wrote:

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Monday, May 28, 2007 9:11 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: CAST




SQLite does not have a dedicated DATE type.


I know that, but why it does't create appropriate column definition ?
create table tab(col date);
creates a table with "date" type.
create table tab2 as select * from tab;
also.
This type does't do much, but it can be queried with
sqlite3_column_decltype.
This way I know what to do with text stored in the database.
Currently the only way I see is to create table and then insert .. as
select
which seems to be weird in presence of a function that "changes the
datatype".



sqlite3_column_decltype() returns the declared type of the column in the
table, not the name of the type declared in a particular row in a particular
column of a table.

I wonder how feasible it'd be to implement an extensible type system into
SQLite's core.  We have extensibility through user-defined functions and
collating sequences, why not user-defined types?

Robert

We actually do that with our Sqlite interfaces.  We use the declared 
type to specify the type and perform a conversion when necessary.  For 
example if the declared type of a column is DATE we know that we 
actually have a FLOAT so when we call a Javascript SQL function for 
example the Sqlite user function transforms the floating point number 
declared as a DATE to be a Javascript Date object.


A FLOAT with declared type DATE may be transformed into a date string in 
accordance with the rules of the chosen locale when being embedded in an 
HTML page.


Our applications use DECIMAL arithmetic for accuracy so when a column is 
declared as DECIMAL its actual type is TEXT but arithmetic rules are 
applied.  If Sqlite has decided to make it an INTEGER or a FLOAT the 
correct conversion is made with the declared precision and scale.




-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: CAST

[Robert Simpson]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Monday, May 28, 2007 9:11 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Re: CAST
> 
> 
> > SQLite does not have a dedicated DATE type.
> 
> I know that, but why it does't create appropriate column definition ?
> create table tab(col date);
> creates a table with "date" type.
> create table tab2 as select * from tab;
> also.
> This type does't do much, but it can be queried with
> sqlite3_column_decltype.
> This way I know what to do with text stored in the database.
> Currently the only way I see is to create table and then insert .. as
> select
> which seems to be weird in presence of a function that "changes the
> datatype".

sqlite3_column_decltype() returns the declared type of the column in the
table, not the name of the type declared in a particular row in a particular
column of a table.

I wonder how feasible it'd be to implement an extensible type system into
SQLite's core.  We have extensibility through user-defined functions and
collating sequences, why not user-defined types?

Robert




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[BardzoTajneKonto]

> SQLite does not have a dedicated DATE type. 

I know that, but why it does't create appropriate column definition ?
create table tab(col date);
creates a table with "date" type. 
create table tab2 as select * from tab;
also.
This type does't do much, but it can be queried with sqlite3_column_decltype.
This way I know what to do with text stored in the database.
Currently the only way I see is to create table and then insert .. as select
which seems to be weird in presence of a function that "changes the datatype".



- Original Message -
From: "Igor Tandetnik" <[EMAIL PROTECTED]>
To: "SQLite" 
Date: Mon, 28 May 2007 10:36:50 -0400
Subject: [sqlite] Re: CAST

> [EMAIL PROTECTED] wrote:
> > I'm wandering if CAST is supposed to work?
> 
> Yes.
> 
> > sqlite> create table tab(col date);
> > sqlite> insert into tab values('1994-11-11');
> > sqlite> create table tab2 as select cast(col as DATE) from tab;
> > sqlite> .schema tab2
> > CREATE TABLE tab2("cast(col as DATE)");
> > sqlite> select * from tab2;
> > 1994
> 
> SQLite does not have a dedicated DATE type. See 
> http://sqlite.org/datatype3.html . When given an unknown type, SQlite 
> assumes numeric. That's why CAST('1994-11-11' as DATE) produces 1994. So 
> would CAST('1994-11-11' as ANY_RANDOM_STRING).
> 
> It is customary to store dates as strings in SQLite. Several functions 
> are provided to manipulate dates in this representation.
> 
> Igor Tandetnik 
> 
> 
> 
> 
-
> To unsubscribe, send email to [EMAIL PROTECTED]
> 
> 
-
> 
> 
> 


--
Ile masz w domu niepotrzebnych rzeczy?
Wymien sie z sasiadami >> http://link.interia.pl/f1a93



-
To unsubscribe, send email to [EMAIL PROTECTED]
-