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
