Recent

Author Topic: Again on SQLite and date times.  (Read 3815 times)

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: Again on SQLite and date times.
« Reply #30 on: November 17, 2022, 10:05:10 am »
Sorry if I say a stupid thing, I am old school, but I wonder if we can still call SQLite a database. It seems to me rather a byte array logger.
Apart my uninteresting thoughts, with this example we see that column datatype definition is optional, and even different rows of a table can contain unrelated datatypes.
Is the concept of datatype itself to be senseless here.
It's a clear Yes and No  :D
SQLite offers the flexibility to use it kind of in a "typeless" mode (or weakly typed)
OTOH, there is the STRICT-Qualifier --> https://www.sqlite.org/stricttables.html
Quote
SQLite supports a strict typing mode, as of version 3.37.0 (2021-11-27), that is enabled separately for each table.
.....
.....
If the value cannot be losslessly converted in the specified datatype, then an SQLITE_CONSTRAINT_DATATYPE error is raised. 
Bottom Line: Use the modifier, and you get what you're used to
BUT: As the doc's say
Quote
Every column definition must specify a datatype for that column. The freedom to specify a column without a datatype is removed.

The datatype must be one of following:

    INT
    INTEGER
    REAL
    TEXT
    BLOB
    ANY

No other datatype names are allowed, though new types might be added in future releases of SQLite.
So, no DATETIME, VARCHAR and companions allowed (at least up until now)
« Last Edit: November 17, 2022, 10:08:18 am by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

LacaK

  • Hero Member
  • *****
  • Posts: 691
Re: Again on SQLite and date times.
« Reply #31 on: November 17, 2022, 06:28:45 pm »
..., because if the Metadata of the Column is Real, he cannot use Qry.FieldbyName('SomeDate').AsDateTime

He can use AsDateTime as long as real values stored in such column satisfy: "..., the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar. "

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: Again on SQLite and date times.
« Reply #32 on: November 17, 2022, 08:53:29 pm »
..., because if the Metadata of the Column is Real, he cannot use Qry.FieldbyName('SomeDate').AsDateTime

He can use AsDateTime as long as real values stored in such column satisfy: "..., the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar. "
No, he can‘t, as long as the returned Fieldtype is not ftDateTime.
Tested and confirmed.
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: Again on SQLite and date times.
« Reply #33 on: November 18, 2022, 02:31:47 pm »
If I understand correctly, this is the place where the problem arises.

In "C:\lazarus\fpc\3.2.2\source\packages\fcl-db\src\sqlite\sqliteds.pas":

Code: Pascal  [Select][+][-]
  1. procedure TSqliteDataset.RetrieveFieldDefs;
  2. var
  3.   ColumnCount, i, DataSize:Integer;
  4.   AType: TFieldType;
  5.   vm: Pointer;
  6.   ColumnNames, ColumnValues:PPAnsiChar;
  7.   ColumnStr: String;
  8. begin
  9.   FieldDefs.Clear;
  10.   FAutoIncFieldNo := -1;
  11.   FReturnCode := sqlite_compile(FSqliteHandle, PAnsiChar(FEffectiveSQL), nil, @vm, nil);
  12.   if FReturnCode <> SQLITE_OK then
  13.     DatabaseError(ReturnString, Self);
  14.   sqlite_step(vm, @ColumnCount, @ColumnValues, @ColumnNames);
  15.   //Prepare the array of pchar2sql functions
  16.   SetLength(FGetSqlStr, ColumnCount);
  17.   // Sqlite is typeless (allows any type in any field)
  18.   // regardless of what is in Create Table, but returns
  19.   // exactly what is in Create Table statement
  20.   // here is a trick to get the datatype.
  21.   // If the field contains another type, may have problems
  22.   for i := 0 to ColumnCount - 1 do
  23.   begin
  24.     DataSize := 0;
  25.     ColumnStr := UpperCase(String(ColumnNames[i + ColumnCount]));
  26.     if (ColumnStr = 'INTEGER') or (ColumnStr = 'INT') then
  27.     begin
  28.       if AutoIncrementKey and
  29.            (UpperCase(String(ColumnNames[i])) = UpperCase(PrimaryKey)) then
  30.       begin
  31.         AType := ftAutoInc;
  32.         FAutoIncFieldNo := i;
  33.       end
  34.       else
  35.         AType := ftInteger;
  36.     end else if Pos('VARCHAR', ColumnStr) = 1 then
  37.     begin
  38.       AType := ftString;
  39.       DataSize := StrToIntDef(Trim(ExtractDelimited(2, ColumnStr, ['(', ')'])), DefaultStringSize);
  40.     end else if Pos('BOOL', ColumnStr) = 1 then
  41.     begin
  42.       AType := ftBoolean;
  43.     end else if Pos('AUTOINC', ColumnStr) = 1 then
  44.     begin
  45.       AType := ftAutoInc;
  46.       if FAutoIncFieldNo = -1 then
  47.         FAutoIncFieldNo := i;
  48.     end else if (Pos('FLOAT', ColumnStr)=1) or (Pos('NUMERIC', ColumnStr) = 1) then
  49.     begin
  50.       AType := ftFloat;
  51.     end else if (ColumnStr = 'DATETIME') then
  52.     begin
  53.       AType := ftDateTime;
  54.     end else if (ColumnStr = 'DATE') then
  55.     begin
  56.       AType := ftDate;
  57.     end else if (ColumnStr = 'TIME') then
  58.     begin
  59.       AType := ftTime;
  60.     end else if (ColumnStr = 'LARGEINT') or (ColumnStr = 'BIGINT') then
  61.     begin
  62.       AType := ftLargeInt;
  63.     end else if (ColumnStr = 'TEXT') then
  64.     begin
  65.       AType := ftMemo;
  66.     end else if (ColumnStr = 'CURRENCY') then
  67.     begin
  68.       AType := ftCurrency;
  69.     end else if (ColumnStr = 'WORD') then
  70.     begin
  71.       AType := ftWord;
  72.     end else
  73.     begin
  74.       AType := ftString;
  75.     end;
  76.     FieldDefs.Add(FieldDefs.MakeNameUnique(String(ColumnNames[i])), AType, DataSize);
  77.     //Set the pchar2sql function
  78.     case AType of
  79.       ftString:
  80.         FGetSqlStr[i] := @Char2SQLStr;
  81.       ftMemo:
  82.         FGetSqlStr[i] := @Memo2SQLStr;
  83.     else
  84.       FGetSqlStr[i] := @Num2SQLStr;
  85.     end;
  86.   end;
  87.   sqlite_finalize(vm, nil);
  88.   {
  89.   if FReturnCode <> SQLITE_ABORT then
  90.      DatabaseError(ReturnString,Self);
  91.   }
  92. end;
  93.  

I was thinking that a possible way is:

1. tell the TSQLQuery that we want it not to try to reconstruct the AType because we will force it.
2. make in a way that this info is passed to this method that will avoid any guessing.
3. set the AtType manually from outside
4. call the As<something> we like.

or event better

1. modify TField so that AType can be forced
2. leave TSQLQuery do all its guessings
3. force for a certain Tfield to have the AType we want
4. call the As<something> we like on that TField
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: Again on SQLite and date times.
« Reply #34 on: November 18, 2022, 02:47:41 pm »
I was thinking about that, too, this morning when i was driving to work.

I was thinking about maybe a Class Helper for TField (because it's TField offering "AsDateTime"), overriding the Property AsDateTime including its Getter/Setter

But there is still something i don't understand:
https://www.freepascal.org/docs-html/fcl/db/tfield.asdatetime.html
Quote
If the native type of the field is not a date or time value, then an attempt will be made to convert the field value from the native format to a TDateTime value when reading the field's content.
...
Therefor, when reading or writing a field value for a field whose native data type is not a TDateTime-compatible value (dates or string values), an exception may be raised.
That's what i don't understand.
In my tests, if in the SQLite-Table the Column was declared as REAL, AsDateTime failed with the Error-Message from the beginning of the Thread.
I could read it as a Float.

Funny thing: In my test i had two columns, one delcared REAL, the other declared DATETIME (a DataType which officially (!!) doesn't exist in SQLite)
I entered exactly the same floating point values in both columns

But i had no problem to
Code: Pascal  [Select][+][-]
  1. Var
  2.   d:TDateTime;
  3. Begin
  4.   d:=Query.FieldByName('DateAsReal').AsFloat;  //No Exception!
  5.   writeln(d);
  6.   d:=Query.FieldByName('DateAsDateTime').AsDateTime;  //No Exception!
  7.   writeln(d);
  8. End;
  9.  
both times the writeln showed the same value.

That's what threw me off.

Anyone a clou if there is a bug here?
Quote
then an attempt will be made to convert the field value from the native format to a TDateTime
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

LacaK

  • Hero Member
  • *****
  • Posts: 691
Re: Again on SQLite and date times.
« Reply #35 on: November 22, 2022, 12:15:34 pm »
C:\lazarus\fpc\3.2.2\source\packages\fcl-db\src\sqlite\sqliteds.pas is not related to TSQLite3Connection and TSQLQuery
You must look into C:\lazarus\fpc\3.2.2\source\packages\fcl-db\src\sqldb\sqlite\sqlite3conn.pp

According to TField (I wrote it before): do not look at methods of TField but at TDateTimeField.
For given FieldType, in our case ftDateTime is created corresponding Field object. In our case TDateTimeField. So TField does not enters into "game".

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: Again on SQLite and date times.
« Reply #36 on: November 22, 2022, 12:30:26 pm »
For given FieldType, in our case ftDateTime is created corresponding Field object. In our case TDateTimeField. So TField does not enters into "game".

The problem is that in the situation described we do not obtain a FieldType whose value is a ftDateTime.

The only way to have this is to create the table in SQLite indicating the data type "DATETIME".
For all other cases in which is possible to write a datetime (1. REAL, 2.TEXT, 3.NO DATATYPE) the fp library does not understand that the values we are reading are TDateTime, so if you try to call AsDateTime in this case you get a Exeception. The reason why our library does not understand that we are dealing with a TDateTime is that uses the "DATETIME" in the table creation statement to understand what kind of field type is this. In SQLite such a question is more or less without meaning, due to it's type freedom.

Just try creating a table with a REAL field, popolate it with TDateTime double values a read it doing AsDateTime. I will get the Execption.

« Last Edit: November 22, 2022, 12:32:14 pm by tt »
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

LacaK

  • Hero Member
  • *****
  • Posts: 691
Re: Again on SQLite and date times.
« Reply #37 on: November 22, 2022, 12:41:08 pm »
The problem is that in the situation described we do not obtain a FieldType whose value is a ftDateTime.
The only way to have this is to create the table in SQLite indicating the data type "DATETIME".
Yes this is the only way - use DATETIME as column data type in create table statement.

Just try creating a table with a REAL field, popolate it with TDateTime double values a read it doing AsDateTime. I will get the Execption.
No use REAL as column data type is not a way.

Thaddy

  • Hero Member
  • *****
  • Posts: 14201
  • Probably until I exterminate Putin.
Re: Again on SQLite and date times.
« Reply #38 on: November 22, 2022, 01:15:55 pm »
Yes no workarounds. Stick to that advice.
Specialize a type, not a var.

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: Again on SQLite and date times.
« Reply #39 on: November 22, 2022, 01:23:04 pm »
Yes this is the only way - use DATETIME as column data type in create table statement.
No use REAL as column data type is not a way.

Of course your suggestions are senseful and I could do as you say.

But there are at least two situations in which these are not applicable with the current status to the free pascal SQLite library:

1. Situations where SQLite table creation statements are managed by software tools (in my case was liquibase) and not written directly. For example, liquibase automatically remaps the columns DATETIME to REAL when generating the actual SQLite creation statement. One could force with tricks to stick to DATETIME in the generated SQL, but it is senseful what liquibase does, since the storage class is REAL. I don't think the liquibase guys are stupid to make this remapping automatically.
2. Situations in which I cannot create the table creation statements because they are pre-existing.

My idea is that the free pascal SQLite library should be modified so that:

1. if the column is DATETIME then is read as such (and this is already like this now)
2. if the column is of a type that allows DATETIME to be stored (REAL, TEXT, NOTHING) allow the client of the library to tell that a value is AsDateType even if the column seems be something else (this is what is missing).

I will try to see If I can make it on my own and later to make it publicly available. 

@Thaddy: we should give the same order to the ones dealing with the situations 1 and 2.
« Last Edit: November 22, 2022, 02:46:40 pm by tt »
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

LacaK

  • Hero Member
  • *****
  • Posts: 691
Re: Again on SQLite and date times.
« Reply #40 on: November 22, 2022, 07:57:29 pm »
Yes I understand.
At client side you can use event handler TField.OnGetText, OnSetText https://www.freepascal.org/docs-html/fcl/db/tfield.ongettext.html to handle content of column and prepare it for DB-aware controls (such as TDBGrid etc.)

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: Again on SQLite and date times.
« Reply #41 on: November 23, 2022, 09:34:08 am »
Yes I understand.
At client side you can use event handler TField.OnGetText, OnSetText https://www.freepascal.org/docs-html/fcl/db/tfield.ongettext.html to handle content of column and prepare it for DB-aware controls (such as TDBGrid etc.)

This is for sure one possible way.

However it does not solve the original problem:

Code: Text  [Select][+][-]
  1. given Table = (dateTimeColumn REAL) // liquibase did that, I asked for DATETIME but he did that, blame on it
  2.  
  3. qwrite = 'insert into Table (dateTimeColumn) values (:dateTimeColumn)';
  4. qwrite.ParamByName('dateTimeColumn').AsDateTime := Today;
  5.  
  6. qread = 'select dateTimeColumn from Table';
  7. value := qread.FiledByName('dateTimeColumn').AsDateTime; // -> Exception is thrown
  8.  
  9.  

Clients (like I was) could be confused when this occurs.
So I still think that is necessary something that solves this problem.
« Last Edit: November 23, 2022, 09:36:53 am by tt »
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

Thaddy

  • Hero Member
  • *****
  • Posts: 14201
  • Probably until I exterminate Putin.
Re: Again on SQLite and date times.
« Reply #42 on: November 23, 2022, 09:48:54 am »
External tools that do no adhere to the standard are not supported on this platform, nor on the Sqlite platforms.
Drop the external tool because it is broken.
Lacak - who is the walking encyclopedia concerning databases and Freepascal - gave you enough info on how to properly handle it.
I am not going to investigate any further, since it is the external tool that is broken, not FPC's bindings and not Sqlite itself.
« Last Edit: November 23, 2022, 09:58:46 am by Thaddy »
Specialize a type, not a var.

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: Again on SQLite and date times.
« Reply #43 on: November 23, 2022, 10:14:58 am »
External tools that do no adhere to the standard are not supported on this platform, nor on the Sqlite platforms.
Drop the external tool because it is broken.
Lacak - who is the walking encyclopedia concerning databases and Freepascal - gave you enough info on how to properly handle it.
I am not going to investigate any further, since it is the external tool that is broken, not FPC's bindings and not Sqlite itself.

I also trust that Lacak is a walking encyclopedia databases and Freepascal.

Liquibase broken? Maybe. If you help me, we could try to advance these observation to their forum. Maybe supported by previous statement.

But here things are very simple: the normal (and IMHO very senseful) approach to the db access software based on the concept of field with a "fixed/explicit datatype" does not work. Not because is wrong, just because SQLite does not respect this fixed/explicit datatype model common to all the normal dbs. They say is a feature. I do not discuss this. Of course this requires to write a software that copes with it. And current software does not. As I repeatedly demonstrated with many examples, that everyone can reproduce on it own PC if necessary.

Thanks for investigating so far.
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

LacaK

  • Hero Member
  • *****
  • Posts: 691
Re: Again on SQLite and date times.
« Reply #44 on: November 25, 2022, 01:44:20 pm »
You can create descendant of TSQLQuery where you can override virtual method GetFieldClass() and return own TField descendant which will handle AsDateTime in a special way
(you can create own TSqliteFloatField = class(FloatField) ... define there GetAsDateTime and use this new field type for REAL columns (ftFloat))
For usage you can look at fpjsondataset.

 

TinyPortal © 2005-2018