SQLite with integers > 4 byte
lazarus / 16-Databases

----------------------------------------------------------------------
CCRDude · 2012-10-24 11:16

I've recently started using databases with the included SQLite3
support (units sqldb, sqlite3conn).

sqlite3conn uses sqlite3.dll, which I've used in Delphi with a
different wrapper before.

I now have a Trac database, with INTEGER fields that have values that
do not fit into a simple 4 byte integer. The Delphi wrapper works
fine, and returns the proper data (which is a unix timestamp in
microseconds instead of seconds). Lazarus returns something different:

Database: 130442287200000
TSQLQuery.FieldValues: 1843499136

I assume this is broken somewhere in the Int64/UInt64 support.

The FieldType seems to be detected as ftInteger, not ftLargeint. Might
be this is the issue, but since it's an external database, I cannot
change that, and the Delphi sqlite3.dll wrapper shows that the dll is
able to provide correct data anyway.

Workaround attempt 1: I replaced q.FieldValues['time'] with
q.FieldByName('time').AsLargeInt, but this did not return the proper
value as well.

Workaround attempt 2: I called GetFieldData, which failed the same
way.

Workaround attempt 3: I wrote a tracker for TField that would allow me
to update the DataType property, and set it to ftLargeint, then called
GetFieldData. Failed again. Querying f.AsLargeInt resulted in another
number, showing that treatment is a bit different now.


Question: Since this is an external database, I cannot change the
database scheme to make those fields largeint instead of int. But
since the sqlite3.dll itself is able to handle this, is there any
other workaround dealing with Lazarus DB support to get the proper
value from the table?

----------------------------------------------------------------------
ludob · 2012-10-24 12:43

How did you define the fields in the database? TSQLite3Connection
wants BIGINT or LARGEINT for ftlargeInt fields. The following test
program works without any problem:
Code: [Select]program testlargeint;

{$mode objfpc}{$H+}

uses
  {$IFDEF UNIX}{$IFDEF UseCThreads}
  cthreads,
  {$ENDIF}{$ENDIF}
  Classes ,sqldb, db, sqlite3conn;
var
    SQLite3Connection1: TSQLite3Connection;
    SQLQuery1: TSQLQuery;
    SQLTransaction1: TSQLTransaction;

begin
  SQLite3Connection1:=TSQLite3Connection.Create(nil);
  SQLite3Connection1.DatabaseName:='large.sq3';
  SQLTransaction1:= TSQLTransaction.Create(nil);
  SQLite3Connection1.Transaction:=SQLTransaction1;
  SQLQuery1:= TSQLQuery.Create(nil);
  SQLQuery1.DataBase:=SQLite3Connection1;
  SQLQuery1.SQL.Text:='create table if not exists test (id integer
primary key, large LARGEINT,big BIGINT)';
  SQLQuery1.ExecSQL;
  SQLQuery1.SQL.Text:='insert into test values (1,
1304422872000001234,1304422872000001234)';
  SQLQuery1.ExecSQL;
  SQLQuery1.SQL.Text:='select * from test';
  SQLQuery1.Open;
  writeln(SQLQuery1.fields[1].AsLargeInt,',',SQLQuery1.fields[2].AsLar
geInt);
end.
 
Tested with FPC 2.7.1 but afaik no reason why it would fail on 2.6.0.

----------------------------------------------------------------------
CCRDude · 2012-10-24 12:57

As I said, I did not define the fields, they're already there. The
database is an external one, the standard format created by Trac (a
project management and issue tracking system). And as suspected, the
field is defined as "integer" there.

Quotesqlite> .schema ticket
CREATE TABLE ticket (
    id integer PRIMARY KEY,
    type text,
    time integer,
    changetime integer,
    component text,
    severity text,
    priority text,
    owner text,
    reporter text,
    cc text,
    version text,
    milestone text,
    status text,
    resolution text,
    summary text,
    description text,
    keywords text
);
CREATE INDEX ticket_status_idx ON ticket (status);
CREATE INDEX ticket_time_idx ON ticket (time);
As far as the SQLite 3 documentation goes (here), SQLite3 has just the
data type "integer" and no dedicated types for other sizes.

According to the documentation, BIGINT and LARGEINT are treated as INT
anyway, and "only the size matters".

So while your example works, it should also work if the field is
INTEGER but the datasize is 8 (which it is in my case, since other
tools can show it).

TSQLite3Connection seems to ignore this independency of aliases and
does not treat the data based on its size.

Is there any way I can trick it into believing the field would be
defined as BIGINT or LARGEINT? My third workaround went into that
direction, but failed.

----------------------------------------------------------------------
KpjComp · 2012-10-24 13:06

Do you have persistent fields on this project!!..

Eg.  If you double click the TSQLQuery do you get a fields designer?. 
If so doing AsLargeInt would have no effect, the FieldDef would
override this.

----------------------------------------------------------------------
CCRDude · 2012-10-24 13:21

I can't click anything since I'm doing this code-only, in a console
application (background: parsing Trac databases and emailing people
who've got critical open tickets that have been unattended for a
specified span of time - here comes the time field into play).

I've attached the unit I use to retrieve the property, including
temporary debug code. And the following is how I access the database:
Code: [Select]   procedure
TTracProgressReminder.ProcessDatabase(AFilename: string);
   var
      db: TSQLite3Connection;
      q: TSQLQuery;
      t: TTracTicket;
      sl: TStringList;
   begin
      db := TSQLite3Connection.Create(nil);
      q := TSQLQuery.Create(nil);
      t := TTracTicket.Create;
      sl := TStringList.Create;
      try
         DB.DatabaseName := AFilename;
         DB.Open;
         DB.Transaction := TSQLTransaction.Create(nil);
         try
            q.DataBase := db;
            q.SQL.Text :=
               'SELECT * FROM ticket WHERE status <> ''closed'' ORDER
BY changetime DESC;';
            q.Open;
            try
               while not q.EOF do begin
                  t.FromQuery(q);
                  if (t.ChangedTime < (Now - 2)) // not updated in
past two days
                     and (t.Status <> 'closed') // not closed
                     and (Pos('Workshifts', t.Milestone)=0) // not
Workshifts related
                     and ((t.Priority='blocker') or
(t.Priority='critical') or (t.Priority='major')) // important
                  then begin                   // output
                     sl.Clear;
                   
 sl.Add('--------------------------------------------------------');
                     t.ToShortSummary(sl);
                     sl.Add('');
                     WriteLn(sl.Text);
                  end;
                  q.Next;
               end;
            finally
               q.Close;
            end;
         finally
            DB.Close;
         end;
      finally
         DB.Free;
         q.Free;
         t.Free;
         WriteLn(sl.Text);
         sl.Free;
      end;
   end;[/quote]

----------------------------------------------------------------------
BigChimp · 2012-10-24 13:39

Looking here:
http://sqlite.org/datatype3.html
perhaps
CAST (yourfield) AS REAL
may work... which returns an 8 byte floating point value but...
well... it might work.

Alternatively, perhaps having a look in the sqlite3connection and
underlying code to see if that can easily be modified may be worth
it...

----------------------------------------------------------------------
ludob · 2012-10-24 13:48

QuoteAccording to the documentation, BIGINT and LARGEINT are treated
as INT anyway, and "only the size matters".SQLite is a very loosely
typed database. You can put any type in any column. The declaration
only gives a hint and is used in the affinity algorithm. See
http://www.sqlite.org/faq.html#q3 on storing strings in integer
columns. Pascal is a strictly typed language and matching sqlite to
pascal is a difficult task. Some assumptions need to be made otherwise
sqlite becomes unworkable.
The type to field map is the following:
Code: [Select]   (n:'INT'; t: ftInteger),
   (n:'LARGEINT'; t:ftlargeInt),
   (n:'BIGINT'; t:ftlargeInt),
   (n:'WORD'; t: ftWord),
   (n:'SMALLINT'; t: ftSmallint),
   (n:'BOOLEAN'; t: ftBoolean),
   (n:'REAL'; t: ftFloat),
   (n:'FLOAT'; t: ftFloat),
   (n:'DOUBLE'; t: ftFloat),
   (n:'TIMESTAMP'; t: ftDateTime),
   (n:'DATETIME'; t: ftDateTime), // MUST be before date
   (n:'DATE'; t: ftDate),
   (n:'TIME'; t: ftTime),
   (n:'CURRENCY'; t: ftCurrency),
   (n:'VARCHAR'; t: ftString),
   (n:'CHAR'; t: ftFixedChar),
   (n:'NUMERIC'; t: ftBCD),
   (n:'DECIMAL'; t: ftBCD),
   (n:'TEXT'; t: ftmemo),
   (n:'CLOB'; t: ftmemo),
   (n:'BLOB'; t: ftBlob),
   (n:'NCHAR'; t: ftFixedWideChar),
   (n:'NVARCHAR'; t: ftWideString),
   (n:'NCLOB'; t: ftWideMemo),
   (n:'VARBINARY'; t: ftVarBytes),
   (n:'BINARY'; t: ftBytes)
This has to be read as "every type name starting with xyz is mapped to
n". NCHAR is the same as NCHARACTER. None of this is standard sqlite
but allows TSQLite3Connection to work with different field types
without doing conversions all the time. Relaxing this and use a
LongInt for all integers is a possibility but I think users of wince
or other memory limited systems won't be too happy with such a change.
If you think this is too strict please do raise a bug report.

QuoteIs there any way I can trick it into believing the field would be
defined as BIGINT or LARGEINT? My third workaround went into that
direction, but failed.AFAIK, no. Fielddefs defined before open are
destroyed and re-created from above table. Changing fielddefs after
open doesn't solve anything because only 32 bits have been retrieved
from the database. Hence your weird results.

----------------------------------------------------------------------
CCRDude · 2012-10-24 13:51

Thank quite a lot BigChimp, that's quite what I was looking for

Code: [Select]q.SQL.Text := 'SELECT *, CAST(time AS BIGINT) AS time2,
CAST(changetime AS BIGINT) AS changetime2 FROM ticket WHERE status <>
''closed'' ORDER BY changetime DESC;';
Worked right away!

I was already trying to look at the underlying code, but didn't find
an option like Delphi's "Use debug DCUs" yet, so I couldn't debug it,
and by reading only, I didn't find the issue.

...

Thanks a lot as well ludob for the explanations! I agree that avoiding
too many type conversions is a good goal indeed. I thought the
matching could be done using the datasize, but of course that might be
entry-wise and not field-wise, so on a different level.

With BigChimps method (that is kind of what I meant of tricking it
into believing it has a different type) it works, and I can live with
a different field name to query indeed

----------------------------------------------------------------------
ludob · 2012-10-24 13:54

Quote from: BigChimp on October 24, 2012, 03:39:35 pmLooking here:
http://sqlite.org/datatype3.html
perhaps
CAST (yourfield) AS REAL
may work... which returns an 8 byte floating point value but...
well... it might work.
Good idea!Code: [Select] cast(yourintfield as bigint) does work.

----------------------------------------------------------------------
DikSoft · 2016-09-07 14:44

Quote from: ludob on October 24, 2012, 03:54:00 pm... does work.
Lazarus 1.6 SQLite 3.13 x64 - not work! Neither throw TSQLQuery not
ZSQLQuery. All component are in actual updated state, database is
external. ((

Any ideas?

----------------------------------------------------------------------
LacaK · 2016-09-08 05:42

Can you give more detailed description of your problem + probably
attach any source program  / data , which will show error ?

----------------------------------------------------------------------
eny · 2016-09-10 10:45

Quote from: DikSoft on September 07, 2016, 04:44:25 pmQuote from:
ludob on October 24, 2012, 03:54:00 pm... does work.
Lazarus 1.6 SQLite 3.13 x64 - not work! Neither throw TSQLQuery not
ZSQLQuery. All component are in actual updated state, database is
external. ((

Any ideas?
D%&*mn... New Topic button broken again???

----------------------------------------------------------------------
DikSoft · 2016-09-23 09:06

Select Cast( FieldName as BIGINT) as FieldNameToShow - yes. It work
now. I can read this value.
But, how to write ??