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]
-----------------------------------------------------------------------------
