[sqlite] Detecting storage class from C
Andrew Wood
actual type stored, but as I understand it sqlite3_column_decltype
returns the SQL type not the SQLite storage class? Is there a way to get
this info?
_______________________________________________
sqlite-users mailing list
sqlite...@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Roger Binns
Hash: SHA1
On 08/31/2010 06:33 AM, Andrew Wood wrote:
> I need to detect the declared storage class of a field rather than the
> actual type stored, but as I understand it sqlite3_column_decltype
> returns the SQL type not the SQLite storage class? Is there a way to get
> this info?
http://www.sqlite.org/c3ref/column_blob.html
Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkx8Yb0ACgkQmOOfHg372QTp4wCeKIQzs1RD0E51ZT94bUoMMRWA
rqcAnjID6Fklsj1cPBQwCVHjXQxLZuu+
=Bscw
-----END PGP SIGNATURE-----
Andrew Wood
maps to (SQLITE_TEXT, SQLITE_FLOAT etc), they just return the SQL type
as a string, of which there are umpteen possibilities.
Presumably then Im going to have to map them to the class myself? My
question was, is this the case, or is there some other function in the
API that will do this for me that Ive overlooked?
My reason for doing this is, if a field is null, I still need to know
what class it 'should' have been if it had been storing a value.
> ______________________________________________________________________
> This email has been scanned by the MessageLabs Email Security System.
> For more information please visit http://www.messagelabs.com/email
> ______________________________________________________________________
Pavel Ivanov
> what class it 'should' have been if it had been storing a value.
Why do you need that? No matter what you declare field can store any
type of data. And in SQLite there's no "declared storage class". You
are talking either about "affinity", or "declared column type", or
"actual data type stored'. So which one of those 3 exactly you want to
know and why?
Pavel
Roger Binns
Hash: SHA1
On 09/02/2010 06:52 PM, Andrew Wood wrote:
> Yes but none of those functions return the class that the declared type
> maps to (SQLITE_TEXT, SQLITE_FLOAT etc), they just return the SQL type
> as a string, of which there are umpteen possibilities.
> http://www.sqlite.org/c3ref/column_blob.html
If you are talking about the functions on that page then you are
incorrect. They all operate on the current row, and the contents and
types of each column can differ for each row.
sqlite3_column_type() returns an int (effectively an enum) not a string
and says what the underlying type is for that column in this result row.
The other functions let you retrieve the column value as a variety of C
based types. Typically if the value is SQLITE_FLOAT then you'd use
sqlite3_column_double etc, but you can call any of the routines and they
will convert as appropriate. For example if the value is SQLITE_FLOAT
and you call sqlite3_column_int then the value will be converted to an
int, sqlite3_column_text will convert it to text etc.
Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkx/rFwACgkQmOOfHg372QRnmACgvZHcWpSk/2YfBZP8Ok/Fh/xr
RUcAoLBIR143KR71X3xqf38NlH07StSP
=esYO
Dan White
Anyone on this list have any experience with it ?
“Sometimes I think the surest sign that intelligent life exists elsewhere in the universe is that none of it has tried to contact us.”
Bill Waterson (Calvin & Hobbes)
Andrew Wood
library which provides a standard API across several DBMSs.
In other DBMSs even if a field contains null you can still ask the API
what type it 'should' have been if something *had* been put in it.
Without this ability, I cant make SQLite work with the library.
I don't understand the reasoning for designing SQLite weakly typed like
this, it just seems bizarre.
On 02/09/10 14:28, Pavel Ivanov wrote:
>> My reason for doing this is, if a field is null, I still need to know
>> what class it 'should' have been if it had been storing a value.
>>
> Why do you need that? No matter what you declare field can store any
> type of data. And in SQLite there's no "declared storage class". You
> are talking either about "affinity", or "declared column type", or
> "actual data type stored'. So which one of those 3 exactly you want to
> know and why?
>
>
_______________________________________________
Jay A. Kreibich
> Because I'm writing glue code between the SQLite API and a higher level
> library which provides a standard API across several DBMSs.
>
> In other DBMSs even if a field contains null you can still ask the API
> what type it 'should' have been if something *had* been put in it.
> Without this ability, I cant make SQLite work with the library.
>
> I don't understand the reasoning for designing SQLite weakly typed like
> this, it just seems bizarre.
SQLite is not "weakly" typed. Each value has a very specific and
strongly enforced type. It just happens that type assignment happens
at the value level, rather than the column level.
When I first came to SQLite from the client/server RDBMS world, I was
somewhat shocked as well. But in the end, it really doesn't make
much of a difference. Like most other languages, all forms of SQL
do a significant amount of type coercion. Opening up that domain
to something larger than what a traditional programmer considers a
"type" doesn't really change anything. Does it really matter that
-3 < 5.35667 < 'abc' ? As long as you have a solid ordering and
conversion rules, there are rarely surprises.
And if someone really cares, they can add CHECK constraints-- which
they should be doing anyways if they're that paranoid about
type-domain checking. Simply limiting a value to an integer (or
whatever) is no more or less "safe" unless you further limit things to
a task-specific sub-domain of that type.
I also have to say that handling NULLs as a value-less type is a very
clean and handy model.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
Schrum, Allan
Since your generic wrapper interface is strongly typed, I would suspect that the values would not be coerced into strange things and maps nicely to one of the above types.
-Allan
Simon Slavin
On 8 Sep 2010, at 10:36pm, Jay A. Kreibich wrote:
> I also have to say that handling NULLs as a value-less type is a very
> clean and handy model.
Today at work I had to thoroughly investigate a program I wrote long ago and look for places where NULL, undefined, -1 and 'false' might appear through unexpected situations. It was educational, not only for how JavaScript handles these values but also for how they effect downstream results. My biggest surprise is that they tend to turn into things later. For instance, if you write a NULL to a file then read it back out you may end up with the four character string 'NULL'. One thing I like about SQLite is that it's almost impossible to have problems like this with NULL since every part of SQLite understands NULL.
(Just in case I get attacked for this, the program I wrote was in no way safety-critical or mission-critical, and does not generate any data which is not immediately inspected by a human who would spot missing results. The reason for the inspection is that I'm shortly going to have to write a new version which won't have those advantages.)
Simon.
Richard Hipp
> Because I'm writing glue code between the SQLite API and a higher level
> library which provides a standard API across several DBMSs.
>
> In other DBMSs even if a field contains null you can still ask the API
> what type it 'should' have been if something *had* been put in it.
> Without this ability, I cant make SQLite work with the library.
>
> I don't understand the reasoning for designing SQLite weakly typed like
> this, it just seems bizarre.
>
SQLite is not "weakly" typed. It is flexibly typed. And all those other
SQL database engines are "rigidly" typed.
Flexible typing in SQLite is a Good Thing, for the same reason that it is a
good thing in AWK, TCL, Perl, Python, Ruby, Lua, Javascript, etc.
--
D. Richard Hipp
d...@sqlite.org
Igor Tandetnik
> I guess I'm confused as to why sqlite3_column_type() would not work? It works for me on queries and I get back either
> SQLITE_INTEGER, SQLITE_FLOAT, or SQLITE3_TEXT. While I know the types could be coerced to something else, at least it is an
> answer of what you originally intended the column to be.
No. sqlite3_column_type returns the type of the value in the given column and the current row. The type reported by sqlite3_column_type may change from row to row. It is largely unrelated to the type "you originally intended the column to be" (SQLite doesn't really have such a concept; column affinity comes closest, but there's no API to report it, directly).
In particular, if the value in the current row is null, sqlite3_column_type returns SQLITE_NULL.
--
Igor Tandetnik
Nicolas Williams
> Schrum, Allan <allan....@rightnow.com> wrote:
> > I guess I'm confused as to why sqlite3_column_type() would not work? It works for me on queries and I get back either
> > SQLITE_INTEGER, SQLITE_FLOAT, or SQLITE3_TEXT. While I know the types could be coerced to something else, at least it is an
> > answer of what you originally intended the column to be.
>
> No. sqlite3_column_type returns the type of the value in the given
> column and the current row. The type reported by sqlite3_column_type
> may change from row to row. It is largely unrelated to the type "you
> originally intended the column to be" (SQLite doesn't really have such
> a concept; column affinity comes closest, but there's no API to report
> it, directly).
>
> In particular, if the value in the current row is null,
> sqlite3_column_type returns SQLITE_NULL.
You can use CHECK() expressions to ensure all values are of the intended
type, and you can use NOT NULL to avoid NULL (or treat NULL as being of
whatever type sqlite3_column_type() reports).
Nico
--
Simon Slavin
On 9 Sep 2010, at 12:01am, Igor Tandetnik wrote:
> sqlite3_column_type returns the type of the value in the given column and the current row. The type reported by sqlite3_column_type may change from row to row. It is largely unrelated to the type "you originally intended the column to be" (SQLite doesn't really have such a concept; column affinity comes closest, but there's no API to report it, directly).
Hmm. What would happen if you wrote a row with '1.1' for every value, then used sqlite3_column_type when you read it back out ? Assuming that it was possible (i.e. no TRIGGER or UNIQUE prevented it).
Simon.
Igor Tandetnik
> On 9 Sep 2010, at 12:01am, Igor Tandetnik wrote:
>
>> sqlite3_column_type returns the type of the value in the given column and the current row. The type reported by
>> sqlite3_column_type may change from row to row. It is largely unrelated to the type "you originally intended the column to be"
>> (SQLite doesn't really have such a concept; column affinity comes closest, but there's no API to report it, directly).
>
> Hmm. What would happen if you wrote a row with '1.1' for every value, then used sqlite3_column_type when you read it back out ?
> Assuming that it was possible (i.e. no TRIGGER or UNIQUE prevented it).
It depends. If you meant those apostrophes literally, you would get SQLITE_TEXT. If you meant to insert 1.1 without apostrophes, that would be SQLITE_FLOAT. However, it is possible that the value got coerced to some other type upon insertion, due to column affinity. I guess I don't quite understand the question.
--
Igor Tandetnik
Simon Slavin
On 9 Sep 2010, at 1:13am, Igor Tandetnik wrote:
> Simon Slavin <sla...@bigfraud.org> wrote:
>> What would happen if you wrote a row with '1.1' for every value, then used sqlite3_column_type when you read it back out ?
>> Assuming that it was possible (i.e. no TRIGGER or UNIQUE prevented it).
>
> It depends. If you meant those apostrophes literally, you would get SQLITE_TEXT. If you meant to insert 1.1 without apostrophes, that would be SQLITE_FLOAT. However, it is possible that the value got coerced to some other type upon insertion, due to column affinity. I guess I don't quite understand the question.
If the column affinity is numeric, and the value represents a number, SQLite coerces the value before it saves the value. Similarly for integer and real. See section 2.3 of
<http://www.sqlite.org/datatype3.html>
I was attempting to find a value which would always coerce directly to the affinity of the column, so when you read it back out it would always be the 'proper' type. My theory would be that this one value could be used to betray the affinity of every column in a table.
Unfortunately I don't think I can tell REAL from NUMERIC with the same value that can tell REAL from INTEGER. You would have to test both the value you read back out and sqlite3_column_type().
Simon.
Igor Tandetnik
> I was attempting to find a value which would always coerce directly to the affinity of the column, so when you read it back out
> it would always be the 'proper' type. My theory would be that this one value could be used to betray the affinity of every
> column in a table.
>
> Unfortunately I don't think I can tell REAL from NUMERIC with the same value that can tell REAL from INTEGER.
If you write 1, REAL will store it as 1.0 (SQLITE_FLOAT) while INTEGER and NUMERIC would store it as 1 (SQLITE_INTEGER). This technique won't be able to tell INTEGER from NUMERIC anyway: "A column that uses INTEGER affinity behaves the same as a column with NUMERIC affinity. The difference between INTEGER and NUMERIC affinity is only evident in a CAST expression."
It'll also be difficult yo detect a column with affinity NONE.
I suspect that, by this point, it would be easier to use sqlite3_column_decltype and do a bit of string manipulation to calculate column affinity the same way SQLite does.
--
Igor Tandetnik
Max Vlasov
> Because I'm writing glue code between the SQLite API and a higher level
> library which provides a standard API across several DBMSs.
>
>
Have in mind that storage class is not a 'type', it's more like an effective
way to store values compactly, you just have 5 variants (SQLITE_INTEGER,
SQLITE_FLOAT, SQLITE_TEXT, SQLITE_BLOB, or SQLITE_NULL), and even getting
affinity instead of decltype, you won't find for example DATETIME mapping
amongst them. Can you create a good interface with a higher lever DBMS
library not providing time-related types? I suppose, no. So the only
effective way is to parse sqlite3_column_decltype result. It's not that
hard, Aducom's library for delphi actually uses this approach and it works.
But if you write something universal, also have in mind that sqlilte won't
raise any exception, for example if you try to read an integer, but there's
a text stored there, you won't get an error, just zero as a result. In my
case I managed this not only by knowing the type from scheme types parsing,
but also knowing the list of compatible affinities for the given type and
when the result of sqlite3_column_type (actual type of a 'cell') doesn't
compatible with this list, I will show some warning about this result set.
Max,
maxerist.net
Simon Slavin
On 9 Sep 2010, at 7:13am, Max Vlasov wrote:
> But if you write something universal, also have in mind that sqlilte won't
> raise any exception, for example if you try to read an integer, but there's
> a text stored there, you won't get an error, just zero as a result
Two different situations can occur. In one, you can rely on your own library/procedures being used to put data into the file. In that one, as long as you've decided on your own standards and stuck to them, you're okay. In the other you have to reliably read files made by any application that can call the sqlite functions.
Simon.
Andrew Wood
> No. sqlite3_column_type returns the type of the value in the given column and the current row. The type reported by sqlite3_column_type may change from row to row. It is largely unrelated to the type "you originally intended the column to be" (SQLite doesn't really have such a concept; column affinity comes closest, but there's no API to report it, directly).
>
> In particular, if the value in the current row is null, sqlite3_column_type returns SQLITE_NULL.
>
On 09/09/10 00:07, Nicolas Williams wrote:
> You can use CHECK() expressions to ensure all values are of the intended
> type, and you can use NOT NULL to avoid NULL (or treat NULL as being of
> whatever type sqlite3_column_type() reports).
>
>
There may be a nugget of a solution here. I'll do some experimenting.
One related question. The library Im using maps numeric types to one of
the following C types:
unsigned long int
signed long int
unsigned int (short)
signed int (short)
float
double
How does SQLite distinguish between ordinary floats and doubles, and
between long & short ints, and signed or unsigned ints?
Igor Tandetnik
> One related question. The library Im using maps numeric types to one of
> the following C types:
>
> unsigned long int
> signed long int
> unsigned int (short)
> signed int (short)
> float
> double
>
> How does SQLite distinguish between ordinary floats and doubles, and
> between long & short ints, and signed or unsigned ints?
It doesn't. All integral values are signed 64-bit integers, all floating point values are 64-bit doubles. Of course, once you retrieved the value from the database, you may interpret it any way you want.
--
Igor Tandetnik
Simon Slavin
On 10 Sep 2010, at 6:01pm, Andrew Wood wrote:
> One related question. The library Im using maps numeric types to one of
> the following C types:
>
> unsigned long int
> signed long int
> unsigned int (short)
> signed int (short)
> float
> double
>
> How does SQLite distinguish between ordinary floats and doubles, and
> between long & short ints, and signed or unsigned ints?
It doesn't. Why should it ?
As you can see from the two pages
<http://www.sqlite.org/c3ref/bind_blob.html>
<http://www.sqlite.org/c3ref/column_blob.html>
numbers can be bound and retrieved as 'int' or 'double'. Conversion from those is probably down to whatever compiler you choose to use: most compilers provide some form of toll-free conversion from one numeric format to another. And entirely down to however you want your application to handle numeric values internally.
Simon.