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

[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?

[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

[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

Re: [sqlite] Roadmap?

[František Kučera]

Dne 27. 10. 19 v 15:24 Simon Slavin napsal(a):
> And I agree with you on RIGHT JOIN: it may duplicate what can be done with 
> LEFT JOIN but many SQL facilities are duplicates.  It's in SQL92 and people 
> expect to see it.

+1 This would increase portability of SQL scripts – it would be easier
to have a single set SQL queries that run on both SQLite and e.g.
PostgreSQL.

Franta

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

Re: [sqlite] Roadmap? - modular design

[František Kučera]

Dne 27. 10. 19 v 10:12 Thomas Kurz napsal(a):
> at least some basic math would be very helpful as well. I don't want to 
> suggest a complete math support, that would really be far away from liteness

Generally speaking, it might be difficult to say where is the line
between „light“ and „heavy“. But this issue might be resolved by modular
design (and SQLite already support modules). There might be an extension
full of mathematical functions. It might be „too heavy“ for someone, but
it would not be a problem, because you would be able to use just the
light core without this module and enjoy less complexity.

Franta


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

Re: [sqlite] Stream loading SQL script

[František Kučera]

Dne 25. 10. 19 v 21:41 Keith Medcalf napsal(a):
> The sqlite3 command line shell already does this.  see function process_input

Thanks, it helped.

I see that it checks whether the input contains a semicolon and only
then it calls sqlite3_complete(). So I implemented it in a similar way
in C++.

Franta


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

Re: [sqlite] Single or double quotes when defining alias?

[František Kučera]

Dne 25. 10. 19 v 23:55 Thomas Kurz napsal(a)
> this might be a stupid question, but do I have to use single or double quotes 
> when defining an alias?
>
> SELECT column1 AS 'c'
> --or--
> SELECT column2 AS "d"
>
> On the one hand, the name refers to a column or table identifier. On the 
> other hand, at the time of using this statement, the identifier does not 
> exist yet. At that moment, it is a string literal telling that an identifier 
> with that name should be created.
>
> So which one is correct?

I was bit surprised that sqlite accepts also AS 'c' (other DBMS do not).
But the standard way is AS "d" – it is an identifier. It is like if you
are defining a variable in a programming language – the variable with
this name does not exist yet, but same rules apply for the name syntax.

Franta


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

[sqlite] Stream loading SQL script

[František Kučera]

Hello,

I am developing a tool* in C++ and one of its features will be that it
will load an SQL script (CREATE TABLE, INSERT), execute it, then execute
some queries and print results.

The SQL script might be long and I do not want to load it whole in the
memory. Usually it will easily fit, but the tool should be capable to
process longer scripts with constant memory usage. What is recommended way?

I see that there is sqlite3_complete() function, which can detect
complete query, but I would have to check the input using this function
character by character, to stop exactly at the semicolon and then execute.

Or I can try to execute what I have loaded and if it fails, load more –
but this way I risk that I load incomplete statement, which could be
valid and mistakenly executed (e.g. DELETE or SELECT without WHERE
condition). So this will also not work.

Franta

*) if anyone interested, it is free software, described here:
 sources:



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