Hi Darren Yes, I get that, but the idea is that as far as possible the underlying database retains native values and types, so that (a) SQL queries work as expected (b) non-Andl programs can access the data. I could simply encode everything as my own private bit strings, but making maximum use of the built in types would seem the way to go.
Regards David M Bennett FACS Andl - A New Database Language - andl.org -----Original Message----- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Darren Duncan Sent: Tuesday, 9 February 2016 12:21 PM To: SQLite mailing list <sqlite-users at mailinglists.sqlite.org> Subject: Re: [sqlite] Andl: choices for generic types: bool, binary, number/decimal, date/datetime, text/varchar David, unless you're wanting to use SQLite's built-in datetime operators, then just encode yours somehow and put them in another field type, and decode them on retrieval into your own datetime types. Depending what you encode them as, pick the appropriate built-in type. -- Darren Duncan On 2016-02-08 5:00 PM, david at andl.org wrote: > 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 _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
