Re: [sqlite] How to determine the column type? – virtual table?
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?
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?
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?
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
