[tarantool-patches] Re: [PATCH 3/6] sql: pass true types of columns to Tarantool

["n.pettik" <korablev@tarantool.org>]

[-- Attachment #1: Type: text/plain, Size: 2421 bytes --]


> On 19 Sep 2018, at 05:23, Konstantin Osipov <kostja@tarantool.org> wrote:
> 
> * Nikita Pettik <korablev@tarantool.org <mailto:korablev@tarantool.org>> [18/09/18 01:20]:
>> From: Georgy Kirichenko <georgy@tarantool.org>
>> 
>> As a main part of introducing strict typing in SQL it is required to
>> prohibit typeless columns in parser's grammar. Originally, SQLite simply
>> assigns typeless columns to BLOB affinity. Moreover, due to historical
>> reasons, all columns were stored with <SCALAR> type in Tarantool core
>> (except for <INTEGER> when it comes to primary key).  Column type should
>> be defined on table creation. Allowed data types are: <TEXT>, <VARCHAR>,
>> <CHAR>, <BLOB>, <INT[EGER]>, <REAL>, <FLOAT>, <NUMERIC>, <DECIMAL>,
>> <DOUBLE> <DATE> and <DATETIME>. However, still any declared data type is
>> converted to one of <BLOB>, <TEXT>, <REAL> or <INTEGER> affinities.
>> While affinity reaches space format, it is (again) converted to
>> Tarantool's field type. To be more precise, table of conversions:
>> 
>> +----------+----------+------------+
>> | SQL TYPE | AFFINITY | FIELD TYPE |
>> +----------+----------+------------+
>> | FLOAT    | REAL     | NUMBER     |
>> | REAL     | REAL     | NUMBER     |
>> | DOUBLE   | REAL     | NUMBER     |
>> | NUMERIC  | REAL     | NUMBER     |
>> | DECIMAL  | REAL     | NUMBER     |
>> | INTEGER  | INTEGER  | INTEGER    |
>> | TEXT     | TEXT     | STRING     |
>> | VARCHAR  | TEXT     | STRING     |
>> | CHAR     | TEXT     | STRING     |
>> | BLOB     | BLOB     | SCALAR     |
>> | DATETIME | REAL     | NUMBER     |
>> | DATE     | REAL     | NUMBER     |
>> | TIME     | REAL     | NUMBER     |
>> +----------+----------+------------+
> 
> How do you manage to store datetime/date/time values as numbers?
> What is your conversion procedure? Are you storing it as unix
> timestamp? Note that SQL date begins (AFAIR) at 1900-01-01.

Now we don’t have any facilities to process or store DATETIME
format in any convenient way. SQLite originally only features
DATETIME() function, which converts string to REAL type
(in the same way you mentioned).
Hence, temporary this ’type’ works simply as REAL/FLOAT without
any implicit conversions to unix timestamp or date/time validations.
Another option is remove DATETIME type at all - until it will be implemented,
in order to avoid confusions.



[-- Attachment #2: Type: text/html, Size: 7583 bytes --]