Re: [sqlite] How to determine the column type? – virtual table?

[Richard Damon]

On 12/14/19 1:55 PM, František Kučera wrote:
> Dne 14. 12. 19 v 18:20 Richard Damon napsal(a):
>> What he wants is different. He takes a basically arbitrary database
>> (user provided) and an arbitrary SQL statement (also user provided) and
>> he wants to determine what type a given column will present.
> Yes, I am looking for something that is usually called ResultSetMetaData.
>
> Currently I have solved it by adding a new command line option, so if the 
> user wants integer in e.g. "size" column, he must say it explicitly by: 
> --type-cast "size" integer.
>
> I can imagine a module, that will introduce optional static typing to SQLite. 
> It could provide a virtual table that will parse the query and return 
> expected result set metadata. So before executing the query, I could do 
> SELECT order, column_name, column_type FROM result_set_metadata WHERE sql = 
> 'SELECT mount_point, pass+1000 AS pass FROM fstab'; and it would return:
>
> result_set_metadata:
>  ╭─┬──┬──╮
>  │ order (integer) │ column_name (string) │ column_type (string) │
>  ├─┼──┼──┤
>  │   1 │ mount_point  │ string   │
>  │   2 │ pass │ integer  │
>  ╰─┴──┴──╯
> Record count: 2
>
> to do this, it would have to:
>
>  - parse the SQL (I am not sure whether internal SQLite parser could be used 
> for it or if I had to do it myself)
>
>  - check whether requested tables and columns exist and check the declared 
> types
>
>  - analyze the operations done in the SELECT clause (operators, function 
> calls or CASE) and derive resulting type
>
>  - return more generic type if types varies e.g. CASE that returns integer or 
> decimal numbers will result in a decimal type
>
>  - return an error if the CASE mixes e.g. integers and strings which does not 
> make much sense and is probably a mistake
>
> expectations and limitations:
>
>  - the table contains only values of declared types
>
>  - null is not perceived as a type itself but rather as a value of another 
> type (missing integer, missing string etc.)
>
> This is IMHO feasible, but would mean probably a lot of work. However it 
> might be beneficial for those who come from other relational databases and 
> prefer static typing rather than dynamic.
>
> i.e. something like optional type hints and checks in dynamic programming 
> languages.
>
> Franta

I think the biggest part of the issue is that you are thinking in
'Standard SQL' and then giving the user free reign in what SQL they are
going to use (so they COULD use some of the relaxation of limitations
provided by SQLite).

One big issue with your proposed module is that it (or at least programs
using it) are going to want to assume its assumptions, but there is not
an easy way to enforce them, as a program that doesn't use the module
could access the data base and break them.

-- 
Richard Damon

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

Re: [sqlite] How to determine the column type? – virtual table?

[Simon Slavin]

On 14 Dec 2019, at 6:55pm, František Kučera  wrote:

> This is IMHO feasible, but would mean probably a lot of work. However it 
> might be beneficial for those who come from other relational databases and 
> prefer static typing rather than dynamic.

I don't think it's practical.  For instance, did you know that SQLite does not 
enforce string lengths ?  If you define a column as CHAR (100) SQLite 
completely ignores the length.  It'll store and return any string, no matter 
how long.

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

Re: [sqlite] How to determine the column type? – virtual table?

[J Decker]

I just don't see the trouble

https://github.com/d3x0r/SACK/blob/master/src/SQLlib/sqlstub.c#L3613-L3680
for each row for each column
= sqlite3_column_type( collection->stmt, idx - 1 )
and then get the data according to the type... sqlite3_column_double  (for
instance)
 It's not very expensive to get the data type; sqlite3 will have already
prepared its internal variant structure...
if you don't know the type, then you don't know what type go get (yes, you
can, get everything as a string, but then why do you care about the type
anyway? :)  )



On Sat, Dec 14, 2019 at 10:55 AM František Kučera 
wrote:

> Dne 14. 12. 19 v 18:20 Richard Damon napsal(a):
> > What he wants is different. He takes a basically arbitrary database
> > (user provided) and an arbitrary SQL statement (also user provided) and
> > he wants to determine what type a given column will present.
>
> Yes, I am looking for something that is usually called ResultSetMetaData.
>
> Currently I have solved it by adding a new command line option, so if the
> user wants integer in e.g. "size" column, he must say it explicitly by:
> --type-cast "size" integer.
>
> I can imagine a module, that will introduce optional static typing to
> SQLite. It could provide a virtual table that will parse the query and
> return expected result set metadata. So before executing the query, I could
> do SELECT order, column_name, column_type FROM result_set_metadata WHERE
> sql = 'SELECT mount_point, pass+1000 AS pass FROM fstab'; and it would
> return:
>
> result_set_metadata:
>  ╭─┬──┬──╮
>  │ order (integer) │ column_name (string) │ column_type (string) │
>  ├─┼──┼──┤
>  │   1 │ mount_point  │ string   │
>  │   2 │ pass │ integer  │
>  ╰─┴──┴──╯
> Record count: 2
>
> to do this, it would have to:
>
>  - parse the SQL (I am not sure whether internal SQLite parser could be
> used for it or if I had to do it myself)
>
>  - check whether requested tables and columns exist and check the declared
> types
>
>  - analyze the operations done in the SELECT clause (operators, function
> calls or CASE) and derive resulting type
>
>  - return more generic type if types varies e.g. CASE that returns integer
> or decimal numbers will result in a decimal type
>
>  - return an error if the CASE mixes e.g. integers and strings which does
> not make much sense and is probably a mistake
>
> expectations and limitations:
>
>  - the table contains only values of declared types
>
>  - null is not perceived as a type itself but rather as a value of another
> type (missing integer, missing string etc.)
>
> This is IMHO feasible, but would mean probably a lot of work. However it
> might be beneficial for those who come from other relational databases and
> prefer static typing rather than dynamic.
>
> i.e. something like optional type hints and checks in dynamic programming
> languages.
>
> Franta
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to determine the column type? – virtual table?

[František Kučera]

Dne 14. 12. 19 v 18:20 Richard Damon napsal(a):
> What he wants is different. He takes a basically arbitrary database
> (user provided) and an arbitrary SQL statement (also user provided) and
> he wants to determine what type a given column will present.

Yes, I am looking for something that is usually called ResultSetMetaData.

Currently I have solved it by adding a new command line option, so if the user 
wants integer in e.g. "size" column, he must say it explicitly by: --type-cast 
"size" integer.

I can imagine a module, that will introduce optional static typing to SQLite. 
It could provide a virtual table that will parse the query and return expected 
result set metadata. So before executing the query, I could do SELECT order, 
column_name, column_type FROM result_set_metadata WHERE sql = 'SELECT 
mount_point, pass+1000 AS pass FROM fstab'; and it would return:

result_set_metadata:
 ╭─┬──┬──╮
 │ order (integer) │ column_name (string) │ column_type (string) │
 ├─┼──┼──┤
 │   1 │ mount_point  │ string   │
 │   2 │ pass │ integer  │
 ╰─┴──┴──╯
Record count: 2

to do this, it would have to:

 - parse the SQL (I am not sure whether internal SQLite parser could be used 
for it or if I had to do it myself)

 - check whether requested tables and columns exist and check the declared types

 - analyze the operations done in the SELECT clause (operators, function calls 
or CASE) and derive resulting type

 - return more generic type if types varies e.g. CASE that returns integer or 
decimal numbers will result in a decimal type

 - return an error if the CASE mixes e.g. integers and strings which does not 
make much sense and is probably a mistake

expectations and limitations:

 - the table contains only values of declared types

 - null is not perceived as a type itself but rather as a value of another type 
(missing integer, missing string etc.)

This is IMHO feasible, but would mean probably a lot of work. However it might 
be beneficial for those who come from other relational databases and prefer 
static typing rather than dynamic.

i.e. something like optional type hints and checks in dynamic programming 
languages.

Franta

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

Re: [sqlite] How to determine the column type? – explicit cast

[Richard Damon]

On 12/14/19 11:12 AM, Simon Slavin wrote:
> On 14 Dec 2019, at 10:46am, František Kučera  wrote:
>
>> SELECT cast(dump+100 AS integer) FROM fstab;
>>
>> the sqlite3_column_decltype() still does not return the integer type.
>>
>> Would it be possible to modify this function or add a new one, to tell the 
>> correct type at least if there is an explicit cast like this in given query?
> It works fine for me:
>
> SQLite version 3.28.0 2019-04-15 14:49:49
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> CREATE TABLE t (a TEXT, b INTEGER, c REAL);
> sqlite> INSERT INTO t VALUES ('123', 123, 123.4);
> sqlite> INSERT INTO t VALUES (CAST ('456' AS INTEGER), CAST (456 AS INTEGER), 
> CAST (456.7 AS INTEGER));
> sqlite> SELECT a,typeof(a),b,typeof(b),c,typeof(c) FROM t;
> 123|text|123|integer|123.4|real
> 456|text|456|integer|456.0|real
> sqlite> SELECT cast(a AS INTEGER),typeof(cast(a AS INTEGER)),cast(b AS 
> INTEGER),typeof(cast(b AS INTEGER)),cast(c AS INTEGER),typeof(cast(c AS 
> INTEGER)) FROM t;
> 123|integer|123|integer|123|integer
> 456|integer|456|integer|456|integer
>
> When you do your CAST when you store, and the column type is compatible with 
> the input value, the column type is what you declared the column type to be.
>
> But whether you do your cast() when you recall, the column type is always 
> INTEGER.  Which is what you want.

What he wants is different. He takes a basically arbitrary database
(user provided) and an arbitrary SQL statement (also user provided) and
he wants to determine what type a given column will present.

He is willing to assume that columns hold their declared data type
(otherwise the problem is truly impossible), and if the results goes
back to just a column, the answer is simple, the declared type of the
column (even though in many cases, the answer could also be NULL). The
problem is that there is no built in algebra to deduce what type an
expression will produce (assuming you know the types of the inputs), in
part because in SQLite you can't 'know' the type that the input would
be, and in part because sometimes result type will depend on the values
received.

The only answers I can think of are

1) retrieve all the results, taking whatever type SQLite says that value
is, and then process all the results for a give column to figure out
what (and if) that column produces (if because it might be inconsistent,
so you need to respond 'mixed' and maybe handle mixed INTEGER and REAL
some way).

2) Parse the expression yourself and determine the types (and know ahead
of time if there are problem columns). There might be ways to use some
internals of SQLite to help, but SQLite isn't going to do the job
itself, as it has no need for that answer (as it is based on assumptions
that SQLite doesn't make on the data).

-- 
Richard Damon

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

Re: [sqlite] How to determine the column type? – explicit cast

[Simon Slavin]

On 14 Dec 2019, at 10:46am, František Kučera  wrote:

> SELECT cast(dump+100 AS integer) FROM fstab;
> 
> the sqlite3_column_decltype() still does not return the integer type.
> 
> Would it be possible to modify this function or add a new one, to tell the 
> correct type at least if there is an explicit cast like this in given query?

It works fine for me:

SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE t (a TEXT, b INTEGER, c REAL);
sqlite> INSERT INTO t VALUES ('123', 123, 123.4);
sqlite> INSERT INTO t VALUES (CAST ('456' AS INTEGER), CAST (456 AS INTEGER), 
CAST (456.7 AS INTEGER));
sqlite> SELECT a,typeof(a),b,typeof(b),c,typeof(c) FROM t;
123|text|123|integer|123.4|real
456|text|456|integer|456.0|real
sqlite> SELECT cast(a AS INTEGER),typeof(cast(a AS INTEGER)),cast(b AS 
INTEGER),typeof(cast(b AS INTEGER)),cast(c AS INTEGER),typeof(cast(c AS 
INTEGER)) FROM t;
123|integer|123|integer|123|integer
456|integer|456|integer|456|integer

When you do your CAST when you store, and the column type is compatible with 
the input value, the column type is what you declared the column type to be.

But whether you do your cast() when you recall, the column type is always 
INTEGER.  Which is what you want.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to determine the column type?

[Richard Damon]

On 12/14/19 5:22 AM, František Kučera wrote:
> Dne 14. 12. 19 v 9:36 Darren Duncan napsal(a):
>> With respect to SQLite, every column is the union of: Null, every Integer, 
>> every Float, every Text, every Blob.
> OK, we can close this thread with that it is currently impossible to track 
> the declared column types through relational operations.
>
It is currently impossible because it is actually impossible to know the
result type of some expressions except by knowing the values and
computing it. Some arithmetic operations have thier result type varied
based on the value of the result (overflow goes to REAL), a CASE
expression can return different types based on the value of an
expression, and then you have the fact that NULL has a unique type.

A common issue with computer systems is that when you have something
that looks easy to do for a lot of simple cases, but there actually are
some cases that are hard or impossible to determine, then those hard
cases make it hard to handle the general problem.

Thus even without using SQLite's flexibility it types stored in columns,
you can't predetermine the type of some expressions.

-- 

Richard Damon

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

Re: [sqlite] How to determine the column type?

[Jean-Christophe Deschamps]

dump+100 (string), pass+1000 (string)

the type information disappears and former integers becomes mere strings


There must be something else going on here:

Z:> sqlite3
SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table T (N integer);
sqlite> insert into T values (1), (2), (3);
sqlite> select N, N+100, typeof(N+100), N+1000, typeof(N+1000) from T;
1|101|integer|1001|integer
2|102|integer|1002|integer
3|103|integer|1003|integer
sqlite> .q

JcD 


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

Re: [sqlite] How to determine the column type? – explicit cast

[František Kučera]

P.S. Even if I do:

SELECT cast(dump+100 AS integer) FROM fstab;

the sqlite3_column_decltype() still does not return the integer type.

Would it be possible to modify this function or add a new one, to tell the 
correct type at least if there is an explicit cast like this in given query?

If I do: SELECT cast("xxx" AS integer); it returns 0. So I hope that it is not 
too immodest to expect that all values of such column will have the same type 
(or be null).

Franta


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

Re: [sqlite] How to determine the column type?

[František Kučera]

Dne 14. 12. 19 v 9:36 Darren Duncan napsal(a):
> With respect to SQLite, every column is the union of: Null, every Integer, 
> every Float, every Text, every Blob.

OK, we can close this thread with that it is currently impossible to track the 
declared column types through relational operations.

Just to explain what my generic program does: it is typically used as a filter 
and works on the fly. The user feeds a stream of relations (tables) into it, 
the program does some transformations (execute SELECTs in this case) and sends 
a stream of another relations on the standard output.

The input relations have declared attribute types (currently boolean, integer 
and string) and it does not allow mixing various types in the same column.

For example the user can do this:

relpipe-in-fstab | relpipe-tr-sql --relation "x" "SELECT * FROM fstab WHERE 
type = ?" --parameter "ext4" | relpipe-out-tabular
x:
 
╭─┬──┬──┬───┬───┬┬╮
 │ scheme (string) │ device  (string) │ mount_point 
(string) │ type (string) │ options  (string) │ dump 
(integer) │ pass (integer) │
 
├─┼──┼──┼───┼───┼┼┤
 │ UUID    │ 29758270-fd25-4a6c-a7bb-9a18302816af │ /   
 │ ext4  │ relatime,user_xattr,errors=remount-ro │  0 │ 
 1 │
 │ │ /dev/sde │ /mnt/data   
 │ ext4  │ relatime,user_xattr,errors=remount-ro │  0 │ 
 2 │
 
╰─┴──┴──┴───┴───┴┴╯
Record count: 2

If you look at the header, the integer types of the "dump" and "pass" columns 
are preserved (I get the type from the sqlite3_column_decltype() function).

But if the user just slightly modifies the query:

relpipe-in-fstab | relpipe-tr-sql --relation x "SELECT mount_point, dump+100, 
pass+1000 FROM fstab WHERE type = ?" --parameter "ext4" | relpipe-out-tabular
x:
 ╭──┬───┬╮
 │ mount_point (string) │ dump+100 (string) │ pass+1000 (string) │
 ├──┼───┼┤
 │ /    │ 100   │ 1001   │
 │ /mnt/data    │ 100   │ 1002   │
 ╰──┴───┴╯
Record count: 2

the type information disappears and former integers becomes mere strings (a 
fallback generic type here). From the point of view of a developer who knows 
SQLite internals, this is obvious. But from the user's point of view, this is 
quite weird and unexpected behavior. Users expect that a mathematical operation 
will return the original (integer) type or at least some other numeric type – 
but not a text string. I would have to add something like --type-cast "dump" 
"integer" option to explicitly specify the types and convert the strings back 
to the integers. But I was wondering whether there is a more user-friendly 
automatic way.

The source codes and more information are available here: 
.

>
> With respect to a generic application programming language, every column is 
> of the most generic type, such as "Object" or "Any" for examples.
>
> Now, if you want to be more precise, you can say that the type of every 
> column is the union of all values currently in it.  This means that the type 
> of an empty column is the empty type consisting of zero values, which is a 
> subset of all other types just as the universal type is the superset of all 
> other types.
>
> Generally speaking, you want to support union types.

In my software, the most generic type is the text string. e.g. 123 will become 
"123", true will become "true" or byte array (when implemented) will become a 
hexadecimal text string. And it is currently also used as a fallback – if I can 
not get a better type from SQLite, the attribute is returned as a string. So 
the text string serves as that union type for me.

Franta

relpipe-in-fstab | relpipe-tr-sql --relation x "SELECT * FROM fstab WHERE type 
= ?" --parameter "ext4" | relpipe-out-tabular 
x:
 
╭─┬──┬──┬───┬───┬┬╮
 │ scheme (string) │ device  (string) │ mount_point 
(string) │ type (string) │ options  (string) │ dump 
(integer) │ pass (integer) │
 

Re: [sqlite] How to determine the column type?

[Darren Duncan]

František,

Having done this myself, I will tell you that:

The only effective design for your "generic software that should work with any 
database model" is that every column is the universal type, the union of all 
other types; the type is the set of all representable values.


With respect to SQLite, every column is the union of: Null, every Integer, every 
Float, every Text, every Blob.


With respect to a generic application programming language, every column is of 
the most generic type, such as "Object" or "Any" for examples.


Now, if you want to be more precise, you can say that the type of every column 
is the union of all values currently in it.  This means that the type of an 
empty column is the empty type consisting of zero values, which is a subset of 
all other types just as the universal type is the superset of all other types.


Generally speaking, you want to support union types.

Do you have any questions to help you understand this?

-- Darren Duncan

On 2019-12-13 2:49 p.m., František Kučera wrote:

I know that SQLite uses dynamic types, so it is not easy… But what is the best 
way to determine the column type of a result set?

The sqlite3_column_decltype() works only if I select directly a column, but not 
when I do some other operations (call function, increment etc.).

The sqlite3_column_type() works for while iterating over particular rows. I can 
fetch the first row and get type here (expecting that all values in that column 
will have same type), but the problem is a) if the first value is NULL or b) if 
the result set is empty.

If I have e.g. "SELECT a + 1 AS x FROM t", is there a way to say that the x will be 
numeric? Or if I have "SELECT 1+1 AS x"?

I am writing a generic software that should work with any database model 
(tables are defined by the user) and I need to know the types, so I can 
properly present the results. Currently I use sqlite3_column_decltype() and 
will add options so the user could explicitly specify types of particular 
columns, but I am looking for a better way…


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

Re: [sqlite] How to determine the column type?

[Keith Medcalf]

On Friday, 13 December, 2019 18:35, Richard Damon  
wrote:

>One big thing to watch out is that columns of NUMERIC type can easily
>return values of either INTEGER or REAL type. Your single type
>expectation is easily broken here. I also don't know if
>9223372036854775807 (the biggest integer value) from an INTEGER field +
>1 gives a REAL result, or some incorrect INTEGER value.

The result is 9223372036854775807.999 which is the closest representable 
double precision floating point number.  Of course, you get the same answer 
unless you add 1025 instead of 1, in which case you get the next representable 
floating point number which is 9223372036854777855.999.  The arithmetic 
functions carry out the operations using the "type" of their arguments -- and 
if one of them is a float, then the other is converted to a float.  If both are 
integers and an overflow (or underflow) occurs, then both arguments are 
converted to floating point and the operation is carried out in floating point.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

Re: [sqlite] How to determine the column type?

[Richard Damon]

On 12/13/19 7:16 PM, František Kučera wrote:
> Dne 14. 12. 19 v 0:09 Keith Medcalf napsal(a):
>> On Friday, 13 December, 2019 15:49, František Kučera 
>>  wrote:
>>
>>> I know that SQLite uses dynamic types, so it is not easy… But what is the
>>> best way to determine the column type of a result set?
>> Result sets do not have "column types".  Each result value (the intersection 
>> of row and column) has a type associated with it.
> I know that SQLite is that flexible, but I am using just a subset of its 
> features and looking for a way how to propagate the types through the queries 
> and result sets.
>
>> Your expectation would be incorrect. You have to call sqlite3_column_type 
>> for each column of each row in order to determine the type of data contained 
>> there. Every intersection of row and column can contain data of any type. 
>> Think of an Excel (or VisiCalc) spreadsheet. Just because you labeled a 
>> column as containing only integers does not mean that someone did not put 
>> something else there.
> Yes, I can do:
>
> sqlite> create table t (i integer, f float, s text);
> sqlite> insert into t values (1, 1.0, 'abc');
> sqlite> insert into t values (1.5, 1.0, 'abc');
> sqlite> insert into t values (1.5, 'xxx', 'abc');
> sqlite> select * from t;
> 1|1.0|abc
> 1.5|1.0|abc
> 1.5|xxx|abc
> sqlite> select typeof(i), typeof(f), typeof(s) from t;
> integer|real|text
> real|real|text
> real|text|text
>
> but this would be unsupported scenario and the user will expect, that if he 
> declares an integer column, he could put only integers into it. If the types 
> become into such inconsistent state, then my software could crash, throw 
> exception, refuse working… it is OK (user can recover his data directly 
> through SQLite, but it is not expected that this would happen).
>
> In case of my software I can really expect that all values in a column will 
> have the same type or be NULL (and everything else means error).
>
> So if I have a table with an integer column and I do e.g. a +1 operation on 
> it, the database has theoretically everything it needs to say that the 
> resulting type will be also integer. Or can the "+" operation result anything 
> than number (or null or error)?
>
> Would not it be useful to have optional function to determine the types 
> before executing the query? (yes, it would require that the columns contains 
> only values of declared type… but it is quite obvious and who call such 
> function will expect such constraint… and it might also require adding some 
> optional metadata to existing functions – hint what types they return…).
>
> Franta

As I said in my reply, you can keep asking for the type of columns that
have returned NULL previously to get the type.

Part of the issue is that you ARE allowing multiple types (since NULL is
its own type), so you need to be prepared for differing types.

One big thing to watch out is that columns of NUMERIC type can easily
return values of either INTEGER or REAL type. Your single type
expectation is easily broken here. I also don't know if
9223372036854775807 (the biggest integer value) from an INTEGER field +
1 gives a REAL result, or some incorrect INTEGER value.

-- 
Richard Damon

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

Re: [sqlite] How to determine the column type?

[Simon Slavin]

On 14 Dec 2019, at 12:16am, František Kučera  wrote:

> In case of my software I can really expect that all values in a column will 
> have the same type or be NULL (and everything else means error).

In that case, execute your query and use sqlite3_column_type() on each column 
of the first row returned.

Alternatively, execute your query with " LIMIT 1" added to the end, use the 
column types that one gives, then execute your real query.

> Would not it be useful to have optional function to determine the types 
> before executing the query? (yes, it would require that the columns contains 
> only values of declared type…

The problem with that "optional function" is that the requirement you listed is 
not a requirement of SQLite.

You can read the types of every column in a table using a PRAGMA:



Of course, this does not help when some columns returned by your query are not 
table columns.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to determine the column type?

[František Kučera]

Dne 14. 12. 19 v 0:09 Keith Medcalf napsal(a):
> On Friday, 13 December, 2019 15:49, František Kučera  
> wrote:
>
>> I know that SQLite uses dynamic types, so it is not easy… But what is the
>> best way to determine the column type of a result set?
> Result sets do not have "column types".  Each result value (the intersection 
> of row and column) has a type associated with it.

I know that SQLite is that flexible, but I am using just a subset of its 
features and looking for a way how to propagate the types through the queries 
and result sets.

> Your expectation would be incorrect. You have to call sqlite3_column_type for 
> each column of each row in order to determine the type of data contained 
> there. Every intersection of row and column can contain data of any type. 
> Think of an Excel (or VisiCalc) spreadsheet. Just because you labeled a 
> column as containing only integers does not mean that someone did not put 
> something else there.

Yes, I can do:

sqlite> create table t (i integer, f float, s text);
sqlite> insert into t values (1, 1.0, 'abc');
sqlite> insert into t values (1.5, 1.0, 'abc');
sqlite> insert into t values (1.5, 'xxx', 'abc');
sqlite> select * from t;
1|1.0|abc
1.5|1.0|abc
1.5|xxx|abc
sqlite> select typeof(i), typeof(f), typeof(s) from t;
integer|real|text
real|real|text
real|text|text

but this would be unsupported scenario and the user will expect, that if he 
declares an integer column, he could put only integers into it. If the types 
become into such inconsistent state, then my software could crash, throw 
exception, refuse working… it is OK (user can recover his data directly through 
SQLite, but it is not expected that this would happen).

In case of my software I can really expect that all values in a column will 
have the same type or be NULL (and everything else means error).

So if I have a table with an integer column and I do e.g. a +1 operation on it, 
the database has theoretically everything it needs to say that the resulting 
type will be also integer. Or can the "+" operation result anything than number 
(or null or error)?

Would not it be useful to have optional function to determine the types before 
executing the query? (yes, it would require that the columns contains only 
values of declared type… but it is quite obvious and who call such function 
will expect such constraint… and it might also require adding some optional 
metadata to existing functions – hint what types they return…).

Franta


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

Re: [sqlite] How to determine the column type?

[Keith Medcalf]

On Friday, 13 December, 2019 15:49, František Kučera  
wrote:

>I know that SQLite uses dynamic types, so it is not easy… But what is the
>best way to determine the column type of a result set?

Result sets do not have "column types".  Each result value (the intersection of 
row and column) has a type associated with it.

>The sqlite3_column_decltype() works only if I select directly a column,
>but not when I do some other operations (call function, increment etc.).

See above comment.  The declared type of a column has no bearing on what type 
of data is stored in the column.

>The sqlite3_column_type() works for while iterating over particular rows.
>I can fetch the first row and get type here (expecting that all values in
>that column will have same type), but the problem is a) if the first
>value is NULL or b) if the result set is empty.

Your expectation would be incorrect.  You have to call sqlite3_column_type for 
each column of each row in order to determine the type of data contained there. 
 Every intersection of row and column can contain data of any type.  Think of 
an Excel (or VisiCalc) spreadsheet.  Just because you labeled a column as 
containing only integers does not mean that someone did not put something else 
there.  You only know what type of data is contained in a cell by looking and 
asking using the sqlite3_column_type interface.  Every time.

Although I suppose you could write a check constraint for each column to ensure 
that only "approved" datatypes are stored in a column:

sqlite> create table x(x integer check (typeof(x) in ('integer', 'null')));
sqlite> insert into x values (null);
sqlite> insert into x values (1);
sqlite> insert into x values (1.0);
Error: CHECK constraint failed: x
sqlite> insert into x values ('1');
Error: CHECK constraint failed: x
sqlite> insert into x values (X'41');
Error: CHECK constraint failed: x

but most databases/tables will not have done that, so you need to check for 
EACH value.

>If I have e.g. "SELECT a + 1 AS x FROM t", is there a way to say that the
>x will be numeric? Or if I have "SELECT 1+1 AS x"?

>I am writing a generic software that should work with any database model
>(tables are defined by the user) and I need to know the types, so I can
>properly present the results. Currently I use sqlite3_column_decltype()
>and will add options so the user could explicitly specify types of
>particular columns, but I am looking for a better way…

You need to call sqlite3_column_type on each column of each row to determine 
what type of data is contained therein.

>Or will SQLite4 work differently (for me better) with types?

There is no SQLite4, it was cancelled.  SQLite3 is strongly typed, just those 
types are dynamic.

https://sqlite.org/datatype3.html

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



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

Re: [sqlite] How to determine the column type?

[Richard Damon]

On 12/13/19 5:49 PM, František Kučera wrote:
> Hello,
>
> I know that SQLite uses dynamic types, so it is not easy… But what is the 
> best way to determine the column type of a result set?
>
> The sqlite3_column_decltype() works only if I select directly a column, but 
> not when I do some other operations (call function, increment etc.).
>
> The sqlite3_column_type() works for while iterating over particular rows. I 
> can fetch the first row and get type here (expecting that all values in that 
> column will have same type), but the problem is a) if the first value is NULL 
> or b) if the result set is empty.
>
> If I have e.g. "SELECT a + 1 AS x FROM t", is there a way to say that the x 
> will be numeric? Or if I have "SELECT 1+1 AS x"?
>
> I am writing a generic software that should work with any database model 
> (tables are defined by the user) and I need to know the types, so I can 
> properly present the results. Currently I use sqlite3_column_decltype() and 
> will add options so the user could explicitly specify types of particular 
> columns, but I am looking for a better way…
>
> Or will SQLite4 work differently (for me better) with types?
>
> Thanks,
>
> Franta
>
My guess is that your statement '(expecting that all values in that
column will have same type)' is where your problems arise. The SQLite
model doesn't assume that. A column in a result set doesn't necessarily
have *A* type. What do you want you software to do if the data in the
database has differing types in a given column?

If you are going to enforce a uniform type (excepting allowing NULL as a
value), then you could check your recorded column type for each column
for each row, and if you have it currently recorded as NULL, check the
type in this row and update if needed, otherwise use the recorded type.
Note that you need to be prepared for different queries of the same set
of columns (or the same query at different times) may give you changing
types for a given column at different times.

-- 
Richard Damon

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

[sqlite] How to determine the column type?

[František Kučera]

Hello,

I know that SQLite uses dynamic types, so it is not easy… But what is the best 
way to determine the column type of a result set?

The sqlite3_column_decltype() works only if I select directly a column, but not 
when I do some other operations (call function, increment etc.).

The sqlite3_column_type() works for while iterating over particular rows. I can 
fetch the first row and get type here (expecting that all values in that column 
will have same type), but the problem is a) if the first value is NULL or b) if 
the result set is empty.

If I have e.g. "SELECT a + 1 AS x FROM t", is there a way to say that the x 
will be numeric? Or if I have "SELECT 1+1 AS x"?

I am writing a generic software that should work with any database model 
(tables are defined by the user) and I need to know the types, so I can 
properly present the results. Currently I use sqlite3_column_decltype() and 
will add options so the user could explicitly specify types of particular 
columns, but I am looking for a better way…

Or will SQLite4 work differently (for me better) with types?

Thanks,

Franta


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