Having read and understood the documentation on Sqlite data types, I'm really just looking for a single recommendation on which choices to make.
I need to store generic data in 5 types: bool, binary, number/decimal, text/nvarchar, time/date/datetime. Decimal has more than 15 digits of precision. Text is Unicode. Time is years 0000-9999 with fractional seconds. For each type I need to choose: 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 Binary: BLOB, BLOB, value_blob Number: NUMERIC, NUMERIC, value_??? Text: TEXT, TEXT, Encoding utf-8, value_text Time: DATETIME, NUMERIC, value_???. The first two are easy enough. No problems. Number: should I choose text functions like sqlite3_value_text() and do all my own conversions, or is there benefit in using a different function according to the storage class/data type? Or is sqlite3_value_numeric_type() the way to go? Text: I assume this just works, and all text values are UTF-8 by default? Time: the only supported DATETIME format seems to be ISO8601, which has no explicit C API support? That looks like a lot of conversion overhead for something that is easily stored in a 64-bit integer. What would sqlite3_value_numeric_type() do? [Has there been any consideration of extending the range of types to include decimal and a binary datetime?] Sorry if it's a bit scrappy, but I just need to make some choices and then go away and write the code. Regards David M Bennett FACS Andl - A New Database Language - andl.org
