Thanks for the response. It helps. > 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.) Fair enough. Will do. > Number: NUMERIC, NUMERIC, value_??? > should I choose text functions like sqlite3_value_text() and do all my > own conversions >>>That would be silly. Less code I think? > 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. That looks like one call to get the type, a choice of 3 calls for INTEGER, REAL or TEXT and a choice of 3 conversion/casts to get it to the final type, plus unit tests for each range of values. Is it worth it, I ask myself? > 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>. Yes, I read that but that's for the SQL level. No such calls in C? >>>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. Yes, not much use to me. > 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. And negative numbers for 'before Unix'? I guess that would do. > 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(). Just asking. I got a few mojibakes and wondered if there were code pages lurking around there anywhere. UTF-8 is fine. Regards David M Bennett FACS Andl - A New Database Language - andl.org
