From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 307852D5B4 for ; Fri, 12 Oct 2018 07:19:29 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id Dtki9QN8vKYh for ; Fri, 12 Oct 2018 07:19:29 -0400 (EDT) Received: from smtp39.i.mail.ru (smtp39.i.mail.ru [94.100.177.99]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id BECAC2D5B3 for ; Fri, 12 Oct 2018 07:19:28 -0400 (EDT) From: "n.pettik" Message-Id: <1F4AAE6E-2879-428B-86FA-77C9924046FF@tarantool.org> Content-Type: multipart/alternative; boundary="Apple-Mail=_68CF3330-DACC-4353-B01F-95AF1BE1CF2F" Mime-Version: 1.0 (Mac OS X Mail 10.3 \(3273\)) Subject: [tarantool-patches] Re: [PATCH 3/6] sql: pass true types of columns to Tarantool Date: Fri, 12 Oct 2018 14:19:25 +0300 In-Reply-To: <20180919022335.GB2386@chai> References: <9a94105515bd4f9148f302b24230d0918cfccdf9.1537216078.git.korablev@tarantool.org> <20180919022335.GB2386@chai> Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-subscribe: List-owner: List-post: List-archive: To: tarantool-patches@freelists.org Cc: Konstantin Osipov , Vladislav Shpilevoy --Apple-Mail=_68CF3330-DACC-4353-B01F-95AF1BE1CF2F Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On 19 Sep 2018, at 05:23, Konstantin Osipov = wrote: >=20 > * Nikita Pettik > [18/09/18 01:20]: >> From: Georgy Kirichenko >>=20 >> 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 type in Tarantool core >> (except for when it comes to primary key). Column type = should >> be defined on table creation. Allowed data types are: , = , >> , , , , , , , >> and . However, still any declared data type = is >> converted to one of , , or affinities. >> While affinity reaches space format, it is (again) converted to >> Tarantool's field type. To be more precise, table of conversions: >>=20 >> +----------+----------+------------+ >> | 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 | >> +----------+----------+------------+ >=20 > 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=E2=80=99t 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 =E2=80=99type=E2=80=99 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. --Apple-Mail=_68CF3330-DACC-4353-B01F-95AF1BE1CF2F Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
On 19 Sep 2018, at 05:23, Konstantin Osipov <kostja@tarantool.org> wrote:

* Nikita Pettik <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=E2=80=99t 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 =E2=80=99type=E2=80=99 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.


= --Apple-Mail=_68CF3330-DACC-4353-B01F-95AF1BE1CF2F--