GNATCOLL SQLite3 vs Ada.Calendar.Time

GNATCOLL SQLite3 vs Ada.Calendar.Time

[Stephen Leake]

I've run into some very odd behavior using GNATCOLL to interface to an SQLite3 database, and querying on a DATETIME field. Here's a very simple program that shows the problem:

-- create_db.sql
-- Create a table, fill it with some test data
-- SQLite3 syntax

CREATE TABLE Title
(ID       INTEGER PRIMARY KEY,
 Modified DATETIME,
 Title    TEXT);

INSERT INTO Title (Modified, Title) VALUES ("2000-01-02 00:00:00", "2001"); 
INSERT INTO Title (Modified, Title) VALUES ("2000-01-02 00:00:00", "Foundation");
INSERT INTO Title (Modified, Title) VALUES ("2000-01-02 00:00:00", "Foundations Edge");
INSERT INTO Title (Modified, Title) VALUES ("2000-01-02 00:00:00", "Foundation and Earth");
INSERT INTO Title (Modified, Title) VALUES ("2000-01-02 00:00:00", "Foundation and Empire");

-- end of file


-- debug_db_times.adb
--  Abstract :
--
--  demonstrate a bug in GNATCOLL/sql related to time field handling

pragma License (GPL);

with Ada.Text_IO; use Ada.Text_IO;
with Ada.Calendar;
with GNATCOLL.SQL.Exec; use GNATCOLL.SQL.Exec;
with GNATCOLL.SQL.Sqlite;
with GNATCOLL.Traces;
procedure Debug_DB_Times
is
   DB     : Database_Connection := Build_Connection (GNATCOLL.SQL.Sqlite.Setup ("debug_db_times.db"));
   Cursor : Forward_Cursor;

   Before_Created : constant Ada.Calendar.Time := Ada.Calendar.Time_Of (1999, 1, 1);
   Jan_1_2000 : constant Ada.Calendar.Time := Ada.Calendar.Time_Of (2000, 1, 1);

   procedure Put_Current
   is begin
      for I  in Field_Index'(0) .. 2 loop
         Put (Cursor.Value (I));
         if I /= 2 then
            Put (", ");
         end if;
      end loop;
      New_Line;
   end Put_Current;
begin
   GNATCOLL.Traces.Parse_Config_File
     (Filename         => "debug_db_times.trace",
      Force_Activation => False);

   Put_Line ("find all:");
   Fetch (Cursor, DB, "SELECT ID, Modified, Title FROM Title ORDER BY ID");
   loop
      exit when not Cursor.Has_Row;
      Put_Current;
      Cursor.Next;
   end loop;

   Put_Line ("find modified time (long before created):");
   Fetch (Cursor, DB, "SELECT ID, Modified, Title FROM Title WHERE Modified >= ? ORDER BY ID", (1 => +Before_Created));
   loop
      exit when not Cursor.Has_Row;
      Put_Current;
      Cursor.Next;
   end loop;

   Put_Line ("find modified time (1 day before created):");
   Fetch (Cursor, DB, "SELECT ID, Modified, Title FROM Title WHERE Modified >= ? ORDER BY ID", (1 => +Jan_1_2000));
   loop
      exit when not Cursor.Has_Row;
      Put_Current;
      Cursor.Next;
   end loop;

   Put_Line ("find modified string:");
   Fetch (Cursor, DB, "SELECT ID, Modified, Title FROM Title WHERE Modified >= ? ORDER BY ID",
          (1 => +"2000-01-01 00:00:00"));
   loop
      exit when not Cursor.Has_Row;
      Put_Current;
      Cursor.Next;
   end loop;
end Debug_DB_Times;
-- end of file

-- debug_db_times.trace
SQL=TRUE
SQL.SELECT=TRUE
-- end of file

Running on Windows 8.1 with GNAT GPL 2016:

sqlite3 -echo -init create_db.sql debug_db_times.db ".quit"
gprbuild -p -P debug_db_times.gpr debug_db_times.adb
./debug_db_times.exe
find all:
[SQL] Connecting to sqlite database debug_db_times.db ()
[SQL] PRAGMA foreign_keys=ON 
[SQL.SELECT] SELECT ID, Modified, Title FROM Title ORDER BY ID 
1, 2000-01-02 00:00:00, 2001
2, 2000-01-02 00:00:00, Foundation
3, 2000-01-02 00:00:00, Foundations Edge
4, 2000-01-02 00:00:00, Foundation and Earth
5, 2000-01-02 00:00:00, Foundation and Empire
find modified time (long before created):
[SQL.SELECT] SELECT ID, Modified, Title FROM Title WHERE Modified >= ? ORDER BY ID, 1999-01-01 06:00:00 
1, 2000-01-02 00:00:00, 2001
find modified time (1 day before created):
[SQL.SELECT] SELECT ID, Modified, Title FROM Title WHERE Modified >= ? ORDER BY ID, 2000-01-01 06:00:00 
1, 2000-01-02 00:00:00, 2001
find modified string:
[SQL.SELECT] SELECT ID, Modified, Title FROM Title WHERE Modified >= ? ORDER BY ID, 2000-01-01 00:00:00 
1, 2000-01-02 00:00:00, 2001
2, 2000-01-02 00:00:00, Foundation
3, 2000-01-02 00:00:00, Foundations Edge
4, 2000-01-02 00:00:00, Foundation and Earth
5, 2000-01-02 00:00:00, Foundation and Empire


When I pass an Ada.Calendar.Time as a parameter, I only get one record back. When I pass a string as a parameter, I get all the records back.

Note that Ada.Calendar.Time messes with time zones in an undocumented way (I happen to be in GMT+6); that's not a problem, since I figured out how to deal with it in my full program.

In my full program, I have similar but different behavior, and always passing a string doesn't fix it.

One solution would be to replace the "DATETIME" field type in the .sql file with "TEXT", always pass strings for times, and rely on lexicographic sorting for time comparison. But that seems extreme.

Any ideas what's going on here?

Re: GNATCOLL SQLite3 vs Ada.Calendar.Time

[Dmitry A. Kazakov]

On 17/08/2016 23:05, Stephen Leake wrote:
> One solution would be to replace the "DATETIME" field type in the
> .sql  file with "TEXT", always pass strings for times, and rely on
> lexicographic sorting for time comparison. But that seems extreme.

AFAIK SQLite3 does not have date/time types. DATETIME is NUMERIC 
according to:

https://www.sqlite.org/datatype3.html

> Any ideas what's going on here?

I would say, never rely on built-in DB types conversions.

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de

Re: GNATCOLL SQLite3 vs Ada.Calendar.Time

[G.B.]

On 17.08.16 23:05, Stephen Leake wrote:
> One solution would be to replace the "DATETIME" field type in the .sql file with "TEXT", always pass strings for times, and rely on lexicographic sorting for time comparison. But that seems extreme.

It seems advisable to use SQLite's Date And Time Functions
when using SQLite for SQL. Thus,

    " ... WHERE Modified >= datetime(?) ... "

With Ubuntu's standard GNAT, this change results in:

find modified time (1 day before created):
[SQL.SELECT] SELECT ID, Modified, Title FROM Title WHERE Modified >= 
datetime(?) ORDER BY ID, 1999-12-31 23:00:00
1, 2000-01-02 00:00:00, 2001
2, 2000-01-02 00:00:00, Foundation
3, 2000-01-02 00:00:00, Foundations Edge
4, 2000-01-02 00:00:00, Foundation and Earth
5, 2000-01-02 00:00:00, Foundation and Empire

Also, I think that +"some string" has only recently been added
as an overloaded "+" in GNATCOLL.SQL.Exec, in case it's resolved
to be from there and portability to older installation matters.

Testing SQL scripts using standard SQL syntax and several
SQL DBMSs has proven to be useful in detecting caveats.
SQLite's .dump command seems pretty good at producing
standard SQL syntax. (I have had cases where using double
quotes for VARCHAR literals had made the DBMS take "foo"
for column FOO, and that's formally correct.)

Re: GNATCOLL SQLite3 vs Ada.Calendar.Time

[Stephen Leake]

On Thursday, August 18, 2016 at 6:19:45 AM UTC-5, G.B. wrote:
> On 17.08.16 23:05, Stephen Leake wrote:
> > One solution would be to replace the "DATETIME" field type in the .sql file with "TEXT", always pass strings for times, and rely on lexicographic sorting for time comparison. But that seems extreme.
> 
> It seems advisable to use SQLite's Date And Time Functions
> when using SQLite for SQL. Thus,
> 
>     " ... WHERE Modified >= datetime(?) ... "
> 

Yes, that works. Although I'm not clear why; 
https://www.sqlite.org/lang_datefunc.html defines that function to return a string, and the examples are about output formatting. Apparently it has a different meaning when processing bound parameters.

> Testing SQL scripts using standard SQL syntax and several
> SQL DBMSs has proven to be useful in detecting caveats.
> SQLite's .dump command seems pretty good at producing
> standard SQL syntax. 

That gives:

INSERT INTO "Title" VALUES(2,'2000-01-02 00:00:00','Foundation');

no "datetime" function call.

But we are talking about bound parameters, which could easily be different.

I need to remember SQL is not Ada; it can silently do the wrong thing any time it wants :(.

-- Stephe

Re: GNATCOLL SQLite3 vs Ada.Calendar.Time

[Stephen Leake]

On Thursday, August 18, 2016 at 12:45:11 PM UTC-5, Stephen Leake wrote:
> On Thursday, August 18, 2016 at 6:19:45 AM UTC-5, G.B. wrote:
> > On 17.08.16 23:05, Stephen Leake wrote:
> > > One solution would be to replace the "DATETIME" field type in the .sql file with "TEXT", always pass strings for times, and rely on lexicographic sorting for time comparison. But that seems extreme.
> > 
> > It seems advisable to use SQLite's Date And Time Functions
> > when using SQLite for SQL. Thus,
> > 
> >     " ... WHERE Modified >= datetime(?) ... "
> > 
> 
> Yes, that works. 

I take it back; it fixed most of the problems in the full program, but not all.

So I'm switching to CHAR[19] for date/time fields.

-- Stephe

Re: GNATCOLL SQLite3 vs Ada.Calendar.Time

[Jeffrey R. Carter]

On 08/18/2016 10:45 AM, Stephen Leake wrote:
> 
> Yes, that works. Although I'm not clear why; 
> https://www.sqlite.org/lang_datefunc.html defines that function to return a string, and the examples are about output formatting. Apparently it has a different meaning when processing bound parameters.

I know nothing about SQLite or how GNATColl accesses it, but ...

Clearly, the query produced using a bare '?' and "+" applied to an
Ada.Calendar.Time is different from the query produced using a bare '?' and "+"
applied to a string [with contents in the correct format]. The query produced
using "datetime(?)" and "+" applied to an Ada.Calendar.Time is equivalent to the
query produced using a bare '?' and "+" applied to a string. Presumably, the
result of "+" applied to an Ada.Calendar.Time is different from "+" applied to a
string, but passing the former to datetime corrects it.

I would guess that, since you used strings to insert the datetime values, SQLite
is using TEXT representation for them. Also that "+" applied to an
Ada.Calendar.Time is producing a non-string result (numeric?) and SQLite has
problems dealing with the combination. Or something.

Personally, I'd want to compare the results of the two "+" calls (which might
require some cheating). I'd also want to see the resulting SQL statements from
substituting them for the '?'. One could then feed those SQL statements directly
into SQLite to see if the problem is in GNATColl or SQLite.

-- 
Jeff Carter
"Why don't you bore a hole in yourself and let the sap run out?"
Horse Feathers
49

Re: GNATCOLL SQLite3 vs Ada.Calendar.Time

[Dmitry A. Kazakov]

On 2016-08-18 20:28, Jeffrey R. Carter wrote:

> I would guess that, since you used strings to insert the datetime values, SQLite
> is using TEXT representation for them.

No, SQLite3 uses NUMERIC which is INTEGER or REAL at SQLite's choice 
when the conversion is reversible. Otherwise it is up to 15 decimal 
points of precision which is not enough for 64-bit time stamps.

When storing timestamps into SQLite3 it is better to use TEXT and some 
custom time/string conversion that would guarantee proper ordering and 
precision.

> Also that "+" applied to an
> Ada.Calendar.Time is producing a non-string result (numeric?) and SQLite has
> problems dealing with the combination. Or something.

That depends on how GNATCOLL treats bound parameters. I am not familiar 
with it.

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de

Re: GNATCOLL SQLite3 vs Ada.Calendar.Time

[Jeffrey R. Carter]

On 08/18/2016 12:48 PM, Dmitry A. Kazakov wrote:
> 
> No, SQLite3 uses NUMERIC which is INTEGER or REAL at SQLite's choice when the
> conversion is reversible. Otherwise it is up to 15 decimal points of precision
> which is not enough for 64-bit time stamps.

The on-line SQLite documentation (https://www.sqlite.org/datatype3.html) says of
NUMERIC (section 3), "If the lossless conversion of TEXT to INTEGER or REAL is
not possible then the value is stored using the TEXT storage class." So storage
as TEXT is a possibility. The discussion of date and time fields (2.2) says,
"SQLite does not have a storage class set aside for storing dates and/or times.
Instead, the built-in Date And Time Functions of SQLite are capable of storing
dates and times as TEXT, REAL, or INTEGER values:" and goes on to discuss
strings in ISO8601 format stored as TEXT, Julian days as REAL, and Unix times as
INTEGER.

-- 
Jeff Carter
"Why don't you bore a hole in yourself and let the sap run out?"
Horse Feathers
49

Re: GNATCOLL SQLite3 vs Ada.Calendar.Time

[Randy Brukardt]

"Dmitry A. Kazakov" <mailbox@dmitry-kazakov.de> wrote in message 
news:np53eg$1o71$1@gioia.aioe.org...
...
> When storing timestamps into SQLite3 it is better to use TEXT and some 
> custom time/string conversion that would guarantee proper ordering and 
> precision.

For many purposes, the ISO-standard format used by Ada.Calendar.Formatting 
is sufficient; the package includes conversions both ways. The ISO format 
has a fixed size (so it works well with Ada strings), and it is formatted 
such that normal string comparisons give an appropriate ordering.

The only reasons to use a custom format would be if one needed to use a 
shorter format for some reason, or if one needs more precision than 1/100 
seconds. Otherwise, sticking with the standard format allows using 
language-defined routines available on all Ada 2005 and later compilers 
(*much* less chance of making a conversion mistake).

                             Randy.

Re: GNATCOLL SQLite3 vs Ada.Calendar.Time

[Stephen Leake]

On Thursday, August 18, 2016 at 1:13:40 PM UTC-5, Stephen Leake wrote:
> On Thursday, August 18, 2016 at 12:45:11 PM UTC-5, Stephen Leake wrote:
> > On Thursday, August 18, 2016 at 6:19:45 AM UTC-5, G.B. wrote:
> > > On 17.08.16 23:05, Stephen Leake wrote:
> > > > One solution would be to replace the "DATETIME" field type in the .sql file with "TEXT", always pass strings for times, and rely on lexicographic sorting for time comparison. But that seems extreme.
> > > 
> > > It seems advisable to use SQLite's Date And Time Functions
> > > when using SQLite for SQL. Thus,
> > > 
> > >     " ... WHERE Modified >= datetime(?) ... "
> > > 
> > 
> > Yes, that works. 
> 
> I take it back; it fixed most of the problems in the full program, but not all.
> 
> So I'm switching to CHAR[19] for date/time fields.

Which _also_ did not fix the problem. Which makes sense, given that SQL apparently uses TEXT for DATETIME.

So I wrote code to substitute the time string for the ?, and pass a single string to SQLite3 with no bound parameters; that fixed the problem.

There is apparently a problem with bound string parameters and >= in queries. Bound parameters with = INTEGER or LIKE %STRING work (so far, anyway).

-- Stephe

Re: GNATCOLL SQLite3 vs Ada.Calendar.Time

[G.B.]

On 19.08.16 17:30, Stephen Leake wrote:
> On Thursday, August 18, 2016 at 1:13:40 PM UTC-5, Stephen Leake wrote:
>> On Thursday, August 18, 2016 at 12:45:11 PM UTC-5, Stephen Leake wrote:
>>> On Thursday, August 18, 2016 at 6:19:45 AM UTC-5, G.B. wrote:
>>>> On 17.08.16 23:05, Stephen Leake wrote:
>>>>> One solution would be to replace the "DATETIME" field type in the .sql file with "TEXT", always pass strings for times, and rely on lexicographic sorting for time comparison. But that seems extreme.
>>>>
>>>> It seems advisable to use SQLite's Date And Time Functions
>>>> when using SQLite for SQL. Thus,
>>>>
>>>>     " ... WHERE Modified >= datetime(?) ... "
>>>>
>>>
>>> Yes, that works.
>>
>> I take it back; it fixed most of the problems in the full program, but not all.
>>
>> So I'm switching to CHAR[19] for date/time fields.
>
> Which _also_ did not fix the problem. Which makes sense, given that SQL apparently uses TEXT for DATETIME.
>
> So I wrote code to substitute the time string for the ?, and pass a single string to SQLite3 with no bound parameters; that fixed the problem.
>
> There is apparently a problem with bound string parameters and >= in queries. Bound parameters with = INTEGER or LIKE %STRING work (so far, anyway).

TTBOMK, DATETIME, as opposed to TIMESTAMP, is not really present
in standard SQL (1999). DATE and TIME are. However, DATETIME and its
variations are offered by many DBMS products. There are hints at
varying ranges, though. Also, literals of DATETIME may differ.
Found these, for example, for two rather popular ones:

from: 1753-01-01 00:00:00.000	to: 31.12.9999 23:59:59.997
from: 1000-01-01 00:00:00	to: 9999-12-31 23:59:59

And for some version of Oracle, the docs say:
   "Dates between January 1, 4712 B.C. and December 31, 9999 A.D.,
    and times in hours, minutes and seconds."
DATE literals will depend on NLS settings.

For TIMESTAMP and TIME, timezones may enter the picture, as in
TIMESTAMP [WITH TIME ZONE]. Handling zones may depend on the DBMS
or even on SQL session settings, and is therefore not easy to
predict from just looking at SQL queries.

So, if portability or QoI are an issue, Randy Brukardt's suggestion
to use standard Ada conversions, to and from text, looks simple and
safe to me.

In the past, I had successfully used a combination of
DATE and TIME fields, not needing the fractions of TIMESTAMP.
The programs would be using UTC when storing points in time.
This worked reasonably well, since it didn't need complicated
comparisons of points in time. (In fact, when some results need
grouping by day, the split was a logical advantage.)