david at andl.org wrote: > 1. Type name (string) to use in CREATE TABLE. > 2. Affinity. > 3. Datatype that will be returned by C API calls > 4. Which C API call to use to get and put data values. > > My current choices are: > Bool: TINYINT, INTEGER, value_int
Why not "BOOLEAN"? (This would result in NUMERIC affinity, which would not make any difference.) > Number: NUMERIC, NUMERIC, value_??? > should I choose text functions like sqlite3_value_text() and do all my > own conversions That would be silly. > or is there benefit in using a different function according to the > storage class/data type? If you do not know what type is _actually_ stored, call sqlite3_column_type() first. > Time: DATETIME, NUMERIC, value_???. > the only supported DATETIME format seems to be ISO8601, which has no > explicit C API support? ISO8601 strings are strings, so they are just stored as TEXT. See <http://www.sqlite.org/datatype3.html#datetime>. Things like CURRENT_TIMESTAMP use the text format. Numbers are interpreted as Julian days by default; Unix time is used only when you specify the 'unixepoch' modifier. > That looks like a lot of conversion overhead for something that is > easily stored in a 64-bit integer. You need to store date/time values in one of the three supported formats only if you want to use the built-in date functions, or if you want to be compatible with data that already uses these formats. Quite a few Java programs use milliseconds since 1970. > all text values are UTF-8 by default? Yes. It would be possible to configure databases to store text values as UTF-16, but nobody does this, and they would be converted automatically when using sqlite3_column_text(). Regards, Clemens
