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
