Re: [sqlite] latest sqlite 3 with Delphi 5 professional

[Max Vlasov]

On Fri, Jan 24, 2014 at 9:16 PM, Ralf Junker  wrote:
> On 24.01.2014 10:06, Max Vlasov wrote:
>
>> BCC 5.5 (freely downloadable) compiles any version of sqlite3 to
>> object files linkable to Delphi 5 and later, the only drawback I
>>
>> Don't know about DISQLite3 , but one of the main performance issues
>
>
> DISQLite3 does _not_ show the performance issues you describe for your BCB
> 5.5 compiled object files. Quite the opposite: DISQLite3 outperformed
> sqlite3.dll whenever I tested.

Don't take it personally :) I just talked about c originated code that
we have to compile against OMF library files formats as long as
borland/codegear/embarcadero never supported COFF format (CMIIW). So
BCC is our only choice for static linking (probably Intel compilers
should still support OMF since Intel introduced it, but I did not try)

And when I talked about the x2 difference, it was about pure memory db
having a thousand rows and a query that make a cross join taking
totally about 6-20 seconds depending on the query. So no I/O involved,
pure cpu intensive operations inside sqlite. To my own surprise a dll
compiled with bcc 5.5 with -O2 option  (maximum optimization as I
recall) made it two times slower than the VC dll (from sqlite.org
site) compiled against the same version. So this is a synthetic test
not pretending to be general.

As for DISQLite3, I see from your site, that it is a great library
having support for many Delphi versions and many db features. I looked
at the source, as I see the dataset is unidirectional and processes
query on request. I'm sure there are no performance penalties here.
Good job

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] latest sqlite 3 with Delphi 5 professional

[Ralf Junker]

On 24.01.2014 10:06, Max Vlasov wrote:


BCC 5.5 (freely downloadable) compiles any version of sqlite3 to
object files linkable to Delphi 5 and later, the only drawback I
noticed is that for memory-intensive operations (memory databases) the
performance is twice as worst comparing to the dll on the site
(probably VC compiled), but for databases on disk the difference is
small since I/O overhead compensate it.

Don't know about DISQLite3 , but one of the main performance issues


DISQLite3 does _not_ show the performance issues you describe for your 
BCB 5.5 compiled object files. Quite the opposite: DISQLite3 
outperformed sqlite3.dll whenever I tested.


You can test yourself with the example projects located in 
\DISQLite3_Log_Inserts\ and \DISQLite3_20_Million\ sub-folders of the 
\Demo\ directory.


Ralf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] latest sqlite 3 with Delphi 5 professional

[Clemens Ladisch]

Max Vlasov wrote:
> They had static variant implemented with msvcrt linked, to remove
> the dependency yourself you have to implement the following functions
>
> _malloc,_realloc,_free,_memset,_strncmp,_memmove,_memcpy,_strlen,_qsort,_memcmp,_localtime

malloc/realloc/free are not needed if SQLITE_WIN32_MALLOC is defined
(which is a good idea anyway because the Windows functions are faster
than Borland's nowadays).

If you compile with BCC 5.5, most of these functions can simply be
extracted from its runtime library (i.e., extract the modules ftol,
_ftoul, _ll, memcmp, memcpy, memmove, memset, qsort, strlen, and strncmp
from the cw32.lib file with tlib, and then link with these .obj files).

You also need a variable named __turboFloat (which is used only to link
floating-point initialization code, which is already done by Delphi):

var
  __turboFloat: Integer;

... and the function localtime(), which can simply be ported from
SQLite's Windows CE localtime() emulation:

type
  time_t = Longint;
  P_time_t = ^time_t;

  struct_tm = record
tm_sec: Integer;
tm_min: Integer;
tm_hour: Integer;
tm_mday: Integer;
tm_mon: Integer;
tm_year: Integer;
tm_wday: Integer;
tm_yday: Integer;
tm_isdst: Integer;
  end;
  P_struct_tm = ^struct_tm;

var
  y: struct_tm;

function _localtime(t: P_time_t): P_struct_tm; cdecl;
var
  uTm, lTm: FILETIME;
  pTm: SYSTEMTIME;
  t64: Int64;
begin
  t64 := t^;
  t64 := (t64 + 11644473600)*1000;
  uTm.dwLowDateTime := t64;
  uTm.dwHighDateTime:= t64 shr 32;
  FileTimeToLocalFileTime(uTm, lTm);
  FileTimeToSystemTime(lTm, pTm);
  y.tm_year := pTm.wYear - 1900;
  y.tm_mon := pTm.wMonth - 1;
  y.tm_wday := pTm.wDayOfWeek;
  y.tm_mday := pTm.wDay;
  y.tm_hour := pTm.wHour;
  y.tm_min := pTm.wMinute;
  y.tm_sec := pTm.wSecond;
  Result := @y;
end;


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] latest sqlite 3 with Delphi 5 professional

[dean gwilliam]

On 24/01/2014 09:06, Max Vlasov wrote:

So, if you plan get best performance

I do! and thank you for your very detailed analysis.
I had no idea about nearly all of what you've said and very much 
appreciate you sharing your findings.

It helps a lot.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] latest sqlite 3 with Delphi 5 professional

[Max Vlasov]

On Thu, Jan 23, 2014 at 1:33 AM, dean gwilliam
 wrote:
> I'm just wondering what my options are here?
> Any advice much appreciated.
> ___

My two cents...

Historically I took Aducom TDataSet-compatible classes
(http://www.aducom.com/cms/page.php?2 , author - Albert Drent) and
used it ever since, but along the road there were many changes that I
made, so I'm not sure I can recommend one of the current (last
version). They had static variant implemented with msvcrt linked
(maybe it was removed to the moment), to remove the dependency
yourself you have to implement the following functions

_malloc,_realloc,_free,_memset,_strncmp,_memmove,_memcpy,_strlen,_qsort,_memcmp,_localtime

part of them might be just calls to Delphi existing rtl, for another
part a little work needed. This allowed me for example to monitor the
number of memory requests different queries make.

BCC 5.5 (freely downloadable) compiles any version of sqlite3 to
object files linkable to Delphi 5 and later, the only drawback I
noticed is that for memory-intensive operations (memory databases) the
performance is twice as worst comparing to the dll on the site
(probably VC compiled), but for databases on disk the difference is
small since I/O overhead compensate it.

Don't know about DISQLite3 , but one of the main performance issues
when using sqlite is that BDE was made for virtual access of data with
moving cursor, but it's not possible with sqlite. You have a query and
you can only move forward. So, the easiest approach is to load all
data, but imagine this for a very large table, activating TDataset in
this case may take very long time (and space). One of the approach is
to change it to load all rowids of the table and request record data
on the fly based on this array.

So, if you plan get best performance and don't need borland database
components and controls, then your best bet is to use sqlite api or
simple object wrappers around it. Otherwise, be aware that "impedance
mismatch" between sqlite and BDE may cost you performance penalties
depending on the library you use.

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] latest sqlite 3 with Delphi 5 professional

[dean gwilliam]

Clemens, Ralph
Thank you very much for your detailed advice on the subject.
So far my options are
1) the dll +

http://code.google.com/p/hiasm/source/browse/elements/delphi/code/SqLite3Api.pas


2) linking an obj file made using e.g. BC5.5

3) using wrappers off the net

4) using DISQLite3 pro

Is it just option 3 that requires msvcrt.dll?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] latest sqlite 3 with Delphi 5 professional

[Clemens Ladisch]

dean gwilliam wrote:
> On 23/01/2014 08:45, Clemens Ladisch wrote:
>> What exactly do you want to know?  What is your goal?
>
> my goal is to be able to use sqlite 3 in my delphi 5 programs by,
> ideally, accessing the raw sqlite api [...]
> I mostly compile the amalgamation into the code I use my self. I'd
> like to do it both ways in D5 but if I can have only one it would be
> using the standard sqlite api via the dll.

Using the DLL should be easy with a unit like this:
http://code.google.com/p/hiasm/source/browse/elements/delphi/code/SqLite3Api.pas

Embedding the code into your program is possible by using some compiler
that can generate Relocatable Object Module Format .obj files (typically
the free Borland C++ 5.5), but all the solutions on the internet then
require another DLL (msvcrt.dll) for some of the C runtime functions.
I'll have to publish my own solution some day ...


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] latest sqlite 3 with Delphi 5 professional

[Ralf Junker]

On 23.01.2014 11:28, dean gwilliam wrote:


More specifically...is there the equivalent of that powerbasic include
file for D5 i.e. that enables you to access the dll's function calls
unchanged?
Failing that...anything that will let me work with the latest sqlite 3 dll
The meaner and leaner...the better.


DISQLite3 meets your requirements:

  http://yunqa.de/delphi/doku.php/products/sqlite3/index

* Delphi 5 support, among a dozen other Delphi compilers.
* Compiles directly into applications, no need for sqlite3.dll.
* Supports the *complete* SQLite3 API.
* Includes FTS and numerous extensions.
* UTF-8 string conversion functions provided.
* Many Delphi demo projects.
* Exhaustive documentation.

Ralf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] latest sqlite 3 with Delphi 5 professional

[dean gwilliam]

On 23/01/2014 08:45, Clemens Ladisch wrote:

What exactly do you want to know?  What is your goal?
More specifically...is there the equivalent of that powerbasic include 
file for D5 i.e. that enables you to access the dll's function calls 
unchanged?

Failing that...anything that will let me work with the latest sqlite 3 dll
The meaner and leaner...the better.
Thanks for your question
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] latest sqlite 3 with Delphi 5 professional

[dean gwilliam]

On 23/01/2014 08:45, Clemens Ladisch wrote:

dean gwilliam wrote:

I'm just wondering what my options are here?

Many.

What exactly do you want to know?  What is your goal?


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
my goal is to be able to use sqlite 3 in my delphi 5 programs by, 
ideally, accessing the raw sqlite api as I do in c++ and powerbasic i.e. 
with powerbasic I just use an include file and the dll and in C++ I 
mostly compile the amalgamation into the code I use my self. I'd like to 
do it both ways in D5 but if I can have only one it would be using the 
standard sqlite api via the dll. Does that answer your question?


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] latest sqlite 3 with Delphi 5 professional

[Clemens Ladisch]

dean gwilliam wrote:
> I'm just wondering what my options are here?

Many.

What exactly do you want to know?  What is your goal?


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] latest sqlite 3 with Delphi 5 professional

[dean gwilliam]

On 23/01/2014 02:52, Bogdan Ureche wrote:

you may want to take a look
at UTF-8VCL.

Again that's very useful information and thank you for the link.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] latest sqlite 3 with Delphi 5 professional

[Bogdan Ureche]

If you want to support UTF-8 in Delphi 5 then you may want to take a look
at UTF-8VCL. I have been using with Delphi 2007 with no major issues.

http://sourceforge.net/projects/utf8vcl/

Bogdan Ureche


On Wed, Jan 22, 2014 at 5:11 PM, dean gwilliam
wrote:

> On 22/01/2014 22:56, RSmith wrote:
>
>> Hope some of this helps!
>>
> Yes that's extremely helpful information Ryan.
> Thanks very much indeed.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] latest sqlite 3 with Delphi 5 professional

[dean gwilliam]

On 22/01/2014 22:56, RSmith wrote:
Hope some of this helps! 

Yes that's extremely helpful information Ryan.
Thanks very much indeed.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] latest sqlite 3 with Delphi 5 professional

[RSmith]

On 2014/01/22 23:33, dean gwilliam wrote:

I'm just wondering what my options are here?
Any advice much appreciated.


Firstly, high-five on using D5 - All the High-speed Pascal/C /Assembler coding goodness with none of the fat (as far as 32-bit goes 
anyway) - I use it all the time for critical/server side apps or services, and XE3 or newer for RAD stuff.  I use D5/XE libraries 
from open sources but adapted over time (which you are welcome to have) all working exceptionally well.


Biggest stumbling block for us on D5 is no natively supported UTF8 strings... the type "String" in D5 still aliased to AnsiString 
which is not MBCS/Unicode compatible, or at least, suitable converters did not exist at the time.  In Later versions (2009 onwards) 
"String" started referring to "WideString" or now the newest native type: "UTF8String" - all capable of Unicode seamless conversion, 
so no problem these days. When using D5 I simply don't consider it for systems with Unicode requirements, or run it through explicit 
converters (which adds some cycles but still get the job done pretty fast). Of course it is only one thing solved to be able to 
store a Unicode anything in a DB, but it also lacks the ability to display it correctly in, say, a TLabel or TEdit sans native 
Unicode-string properties. (Again - all fixed since 2009).


As an aside:  for-loops had a really bad implementation in D5 (a fix from earlier versions went slower by an order of magnitude, it 
was fixed again in D7 I think) - best to use while-do or repeat-until loops for speedy needs.  It really is time to lay D5 to rest, 
but it still is my favourite toy.


Fascinating article here on String history in Delphi:
http://www.codexterity.com/delphistrings.htm

On the IDE side, D5 IDE does not keep revision history or integrate SVN in any way and profilers/memory leak checkers exist but are 
not included natively.


These mentioned problems aside, it's still one of the sleekest compilers around producing sub-350KB optimized exe's  for a 
hello-world full windows-forms application and compiles rock-steady huge applications in milliseconds every time, making the 
altering/testing/debugging cycle the biggest pleasure.


Oh yes - plus D5/D7 is free nowdays. (Or rather, officially it does not exist anymore, neither any official legal controls for 
it) which makes it great for start-up devs shying away from the 4000+ dollar XE[n] behemoths.


Hope some of this helps!
Ryan

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] latest sqlite 3 with Delphi 5 professional

[dean gwilliam]

I'm just wondering what my options are here?
Any advice much appreciated.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users