Recent

Author Topic: Which simple DB to use for Lazarus application?  (Read 1633 times)

Vodnik

  • Jr. Member
  • **
  • Posts: 85
Which simple DB to use for Lazarus application?
« on: February 14, 2019, 06:11:13 pm »
Hello.
My application is working with Informix DB.
Vendor do not allow to do any changes in it, so for me it is read-only.
I have some additional data that relate to the records of Informix DB.
I have to store it somewhere.
When selecting records from Informix, I want to match them with my data and provide output of both for the user.
I think about using additional database, but I have no much experience with other DBs...
Can you please advice me which one can be used for that aid in Lazarus?
DB will be very simple, at the moment just one table with few fields, though there may be hundreds of thousands rows.
Ideally, this DB should be accessible for application(s) from other PC.
I have read Lazarus Database Overview and put an eye on SQLite, dBase, TSdfDataset and TFixedDataset.
Prior to merging into tests I decided to ask here.

lucamar

  • Hero Member
  • *****
  • Posts: 1943
Re: Which simple DB to use for Lazarus application?
« Reply #1 on: February 14, 2019, 06:18:34 pm »
If your DB must be accessible from other machines then none of the cited will suffice unless you invent a mechanism to synchronize the DBs in the different systems; but in that tesiture nothings beats a standard DB server.

If it's a simple* DB I would recommend Firebird but if the requirements grow too much, use PostgreSQL.


* Of course "simple" is here a very relative term :)
« Last Edit: February 14, 2019, 06:20:26 pm by lucamar »
Turbo Pascal 3 CP/M - Amstrad PCW 8256 (512 KB !!!) :P
Lazarus 2.0.2/2.0.4  - FPC 3.0.4 on:
(K|L)Ubuntu 12..16, Windows XP SP3, various DOSes.

Vodnik

  • Jr. Member
  • **
  • Posts: 85
Re: Which simple DB to use for Lazarus application?
« Reply #2 on: February 14, 2019, 07:18:28 pm »
Well, I see, DB access question comes to the first plan and ruins the simplicity.
As I understand, some of "simple" DBs work just with file.
If such DB file will stay on a server in a shared directory - is this bad idea?

lucamar

  • Hero Member
  • *****
  • Posts: 1943
Re: Which simple DB to use for Lazarus application?
« Reply #3 on: February 14, 2019, 07:36:56 pm »
Yes, it's (usually) a very bad idea: unlike the more robust DB systems those DBMSs are not designed for multiple, simultaneous access by various clients and the probability of data corruption can be anywhere from 98% to 100%.

Unless you can guarantee in some way that no two clients may try to access the flat-file DB at the same time, you're better off using a standard client-server DB.

One advantage of Firebird, beyond being more appropiate for simple DBs, is that you can use the so-called "embedded server" while developing and even in the first stages of deployment, migrating to the full-fledged server once final testing is done.

ETA
Note, also, that according to Wikipedia:
Quote
Informix is generally considered to be optimized for environments with very low or no database administration, including use as an embedded database.
and there's a Free Developer Edition that, according to IBM:
Quote
[...] includes nearly all Informix features for individual app development, testing and prototyping at no cost.
It may allow you to keep better coherence between your application data and the main DB
« Last Edit: February 14, 2019, 07:51:59 pm by lucamar »
Turbo Pascal 3 CP/M - Amstrad PCW 8256 (512 KB !!!) :P
Lazarus 2.0.2/2.0.4  - FPC 3.0.4 on:
(K|L)Ubuntu 12..16, Windows XP SP3, various DOSes.

Vodnik

  • Jr. Member
  • **
  • Posts: 85
Re: Which simple DB to use for Lazarus application?
« Reply #4 on: February 14, 2019, 07:49:43 pm »
I didn't told that user applications will need read-only access both for Informix DB (currently via ODBC) and "simple" file-based DB. As far as I know, simultaneous read of single file is not forbidden.

lucamar

  • Hero Member
  • *****
  • Posts: 1943
Re: Which simple DB to use for Lazarus application?
« Reply #5 on: February 14, 2019, 07:55:40 pm »
If and only if you can guarantee that only one application will ever write to the database at a time and that a write (insert/modify/delete) will never coincide in time with a read, then yes, you're right.

In that case I would use SQLite for your data: it's the more robust of the options you cited.
« Last Edit: February 14, 2019, 07:57:50 pm by lucamar »
Turbo Pascal 3 CP/M - Amstrad PCW 8256 (512 KB !!!) :P
Lazarus 2.0.2/2.0.4  - FPC 3.0.4 on:
(K|L)Ubuntu 12..16, Windows XP SP3, various DOSes.

Vodnik

  • Jr. Member
  • **
  • Posts: 85
Re: Which simple DB to use for Lazarus application?
« Reply #6 on: February 14, 2019, 08:46:22 pm »
Is it really essential that read should not coincide with write?
Apart from DBs, my previous application was reading the log file that is constantly written by a system process in Windows. I didn't observe problems with that.
Some search in Google tells that only concurrent write to the DB may become a problem:
https://stackoverflow.com/questions/4060772/sqlite-concurrent-access

lucamar

  • Hero Member
  • *****
  • Posts: 1943
Re: Which simple DB to use for Lazarus application?
« Reply #7 on: February 14, 2019, 09:09:12 pm »
Well, it may depend on what you're trying to do: standard understanding is that a write after a read invalidates the read: think of a record being read by one proccess and inmediately after written by another: since the data has changed, what have just been read is meaningless. It may or may not affect you much.

Reading a log file (or similar) is different because you're just adding "records" while another process read the previous ones, so no confussion arises.
Turbo Pascal 3 CP/M - Amstrad PCW 8256 (512 KB !!!) :P
Lazarus 2.0.2/2.0.4  - FPC 3.0.4 on:
(K|L)Ubuntu 12..16, Windows XP SP3, various DOSes.

kcandrews

  • New member
  • *
  • Posts: 42
Re: Which simple DB to use for Lazarus application?
« Reply #8 on: February 14, 2019, 09:14:23 pm »
FWIW, should you decide to go with sqlite, a few years back I set up a bunch of my code to use sqlite 3 via dll calls for recording the results of technical analysis backtests.  See attachment sqlite interface header (converted to .txt file for forum upload but is contents of my .pas file) Still working for me quite well.

But bear in mind, since I wrote this code, lazarus now has sqlite packages, which could provide an easier "higher level" approach.  See:
http://wiki.freepascal.org/SQLite


For creating the SQL tables and viewing them, I use Navicat Essentials for SQLite.  Very reliable and economical product IMO.  In my lazarus code, I simply read and write records to various tables since I have no ongoing need to do the table setup/keys etc. and Navicat makes setup very easy for database/table/field planning and changes.

And probably more than you need to know...snippets from procedure calls that use it (FWIW -- probably not so meaningful without more context, but gives an idea of library call usage):

Code: Pascal  [Select]
  1. procedure TTradeSession.SQLConnectionOpen(LogTrades: Boolean);
  2. var
  3.    i, rc: integer;
  4.    msg: string;
  5.         ErrMsg, tail: Pchar;
  6.         SQLStatement: array[0..56] of char;
  7. begin
  8.         // establish sql connection for results (and optionally backtest - log trades)
  9.         rc := SQLite3_Open(pchar(SystemList.RunResultsDB),      FSQLiteDBHandle);
  10.         if rc <> SQLITE_OK
  11.         then begin
  12.                 msg := 'TradeSession.SQLConnectionOpen: ' + SQLiteErrorMessage(rc) + '!';
  13.                 PostShowMsg(msg);
  14.                 raise ETradeSession.Create(Msg);
  15.         end;
  16.         rc := SQLite3_BusyTimeout(FSQLiteDBHandle, 5000); // 5 seconds to handle busy or locks
  17.         if rc <> SQLITE_OK
  18.         then begin
  19.                 msg := 'TradeSession.SQLConnectionOpen: ' + SQLiteErrorMessage(rc) + '!';
  20.                 PostShowMsg(msg);
  21.                 raise ETradeSession.Create(Msg);
  22.         end;
  23.         // performance improvement, set journal_mode = OFF
  24.         // we could use WAL write-ahead logging journal_mode if we needed Rollbacks
  25.         StrPcopy(SQLStatement, 'PRAGMA journal_mode = OFF');
  26.         rc := sqlite3_exec(FSQLiteDBHandle,
  27.          SQLStatement, nil{@FSQLiteCallback}, nil, ErrMsg);
  28.         if (rc <> SQLITE_OK) or assigned(ErrMsg)
  29.         then begin
  30.                 msg := 'TradeSession.SQLConnectionOpen: ' + SQLStatement + 'failed - ' + ErrMsg;
  31.                 if assigned(Errmsg)
  32.                 then
  33.                         sqlite3_free(ErrMsg);
  34.                 raise ETradeException.Create(msg);
  35.         end;
  36.         // performance improvement...don't wait for disk write completion
  37.         StrPcopy(SQLStatement, 'PRAGMA synchronous = OFF');
  38.         rc := sqlite3_exec(FSQLiteDBHandle,
  39.          SQLStatement, nil{@FSQLiteCallback}, nil, ErrMsg);
  40.         if (rc <> SQLITE_OK) or assigned(ErrMsg)
  41.         then begin
  42.                 msg := 'TradeSession.SQLConnectionOpen: ' + SQLStatement + 'failed - ' + ErrMsg;
  43.                 if assigned(Errmsg)
  44.                 then
  45.                         sqlite3_free(ErrMsg);
  46.                 raise ETradeException.Create(msg);
  47.         end;
  48.         if LogTrades
  49.         then begin
  50.                 // create prepared statement template
  51.         if SystemList.UsingSystemRules // new scheme
  52.       then
  53.                         StrPcopy(FSQLInsertStatement, 'INSERT INTO ' + 'Trades'
  54.                          + ' VALUES (')
  55.       else
  56.                         StrPcopy(FSQLInsertStatement, 'INSERT INTO ' + TransactionsBacktestTableName_
  57.                          + ' VALUES (');
  58.                 for i := 1 to SQLiteBacktestTableColumnCount
  59.                 do begin
  60.                         if i < SQLiteBacktestTableColumnCount
  61.                         then
  62.                                 Strcat(FSQLInsertStatement, pchar('@v' + IntToStr(i) + ','))
  63.                         else
  64.                                 Strcat(FSQLInsertStatement, pchar('@v' + IntToStr(i) + ')'));
  65.                 end;
  66.                 rc := sqlite3_prepare_v2(SQLiteDBHandle, FSQLInsertStatement,
  67.                  strlen(FSQLInsertStatement) + 1, // apparently optimal but could pass -1
  68.                  FSQLInsertHandle, tail);
  69.                 if rc <> SQLITE_OK
  70.                 then begin
  71.                         msg := 'TradeSession.SQLConnectionOpen: Prepare failed!';
  72.                         raise ETradeSession.Create(msg);
  73.                 end;
  74.  
  75.                 FSQLiteBacktestTableInsertCount := 0;
  76.                 StrPcopy(SQLStatement, 'BEGIN TRANSACTION');
  77.                 rc := sqlite3_exec(SQLiteDBHandle,
  78.                  SQLStatement, nil{@FSQLiteCallback}, nil, ErrMsg);
  79.                 if (rc <> SQLITE_OK) or assigned(Errmsg)
  80.                 then begin
  81.                         msg := 'TradeSession.SQLConnectionOpen: BEGIN TRANSACTION failed - ' + ErrMsg;
  82.                         if assigned(Errmsg)
  83.                         then
  84.                                 SQLite3_Free(ErrMsg);
  85.                         raise ETradeSession.Create(msg);
  86.                 end;
  87.         end; // if logtrades
  88. end;
  89.  
  90. procedure TTradeSession.SQLConnectionClose;
  91. var
  92.    rc: integer;
  93. begin
  94.         if assigned(FSQLInsertHandle)
  95.         then begin
  96.                 rc := sqlite3_finalize(FSQLInsertHandle);
  97.                 if rc <> SQLITE_OK
  98.                 then
  99.                         PostShowMsg('TradeSession.SQLConnectionClose sqlite3_finalize: '
  100.                          + SQLiteErrorMessage(rc));
  101.         end;
  102.         rc := SQLite3_Close(FSQLiteDBHandle);
  103.         if rc <> SQLITE_OK
  104.         then
  105.                 PostShowMsg('TradeSession.SQLConnectionClose sqlite3_Close: '
  106.                  + SQLiteErrorMessage(rc));
  107. end;                                  

Code: Pascal  [Select]
  1. procedure TTrade.WriteTradeFields;
  2. var
  3.         ErrMsg: Pchar;
  4.         i, rc : integer;
  5.         msg, str: string;
  6.         hndl : Pointer;
  7. begin
  8.         hndl := TTradeSession(TTradeList(FParentList).TradeSession).SQLInsertHandle;
  9.  
  10.         for i := 1 to SQLiteBacktestTableColumnCount
  11.         do begin
  12.                 str := ''; // null assumption
  13.                 case i of
  14.                 1: if (EntryDateTime <> 0)
  15.                          then begin
  16.                                 if (fracDateTime(EntryDateTime) = 0)
  17.                                 then
  18.                                          str := formatDateTime('yyyy-mm-dd', EntryDateTime)
  19.                                 else
  20.                                          str := formatDateTime('yyyy-mm-dd hh:nn:ss', EntryDateTime);
  21.                          end;
  22.                 2:      str := SymbolName;
  23.                 3:      str := IntToStr(SystemID);
  24.                 4: if (ExitDateTime <> 0)
  25.                          then begin
  26.                                 if (fracDateTime(ExitDateTime) = 0)
  27.                                 then
  28.                                          str := formatDateTime('yyyy-mm-dd', ExitDateTime)
  29.                                 else
  30.                                          str := formatDateTime('yyyy-mm-dd hh:nn:ss', ExitDateTime);
  31.                          end;
  32.                 5:
  33.       begin
  34.         str := IntToStr(Quantity);
  35.       end;
  36.                 6: str := format('%8.*n', [GenericSymbol.DecimalPrecision,
  37.                          GenericSymbol.RoundToTick(EntryPriceActual)]);
  38.                 7: str := format('%8.*n',
  39.                          [GenericSymbol.DecimalPrecision,
  40.                           GenericSymbol.RoundToTick(ExitPriceActual)]);
  41.                 8:
  42.                 // NOTE: since this '%n' creates thousands separator commas,
  43.                 // the column must be TEXT(10) in sqlite
  44.                 // (cannot be NUMERIC because it will store as integer when possible)
  45.                 // aggregate math can be done by removing commas in SELECT statements
  46.                         str := format('%.2f', [TradeProfit]);
  47.                 9: str := format('%.2f', [TradeProfitPercent]);
  48.                 // entry stuff
  49.                 10: if EntryReason <> ''
  50.                          then
  51.                                 str := EntryReason
  52.                     else
  53.                       str := 'Unknown'; // can't be null since is key
  54.                 11: if FInitialEntryWavePattern <> wpNone//''
  55.                          then begin
  56.                                 str := WavePatternToString(FInitialEntryWavePattern);
  57.             if FInitialEntryWaveIsWaveUp
  58.                                 then
  59.                str := str + ' UP'
  60.             else
  61.                 str := str + ' DN';
  62.           end;
  63.                 12: if EntryDegree <> 0
  64.                          then
  65.                                 str := IntToStr(EntryDegree);
  66.                 13: if EnteredAt <> ''
  67.                          then
  68.                                 str := EnteredAt;
  69.                 14: if EntryDetail <> ''
  70.                          then
  71.                                 str := EntryDetail;
  72.                 15: str := TimeIntervalAsString(FBardatalist.TimeInterval);
  73.                 // exit stuff
  74.                 16: if ExitReason <> ''
  75.                          then
  76.                             str := ExitReason
  77.           else
  78.              str := 'Unknown'; // can't be null since is key
  79.                 17: if ExitedAt <> ''
  80.                          then
  81.                                         str := ExitedAt;
  82.                 18: if ExitDetail <> ''
  83.                          then
  84.                                         str := ExitDetail;
  85.                 19: str := TimeIntervalAsString(FBardatalist.TimeInterval);
  86.                 20: if BETriggerDateTime <> 0
  87.                          then
  88.                                 str := FloatToStr(GenericSymbol.RoundToTick(BETrigger));
  89.                 21: if (BETriggerDateTime <> 0)
  90.                          then begin
  91.                                 if (frac(BETriggerDateTime) = 0)
  92.                                 then
  93.                                          str := formatDateTime('yyyy-mm-dd', BETriggerDateTime)
  94.                                 else
  95.                                          str := formatDateTime('yyyy-mm-dd hh:nn:ss', BETriggerDateTime);
  96.                          end;
  97.                 22: str := IntToStr(DaysHeld);
  98.                 23: if TimeHeld > 0
  99.                          then
  100.                                         str := format('%.2f', [TimeHeld / OneHour]);
  101.                 // temporary variables
  102.                 24: if tmp1 <> 0
  103.                          then
  104.                                         str := format('%.2f', [Tmp1]);
  105.                 25: if tmp2 <> 0
  106.                          then
  107.                                         str := format('%.2f', [Tmp2]);
  108.                 26: if tmp3 <> 0
  109.                          then
  110.                                         str := format('%.2f', [Tmp3]);
  111.                 27: if tmp4 <> 0
  112.                          then
  113.                                         str := format('%.2f', [Tmp4]);
  114.                 28: if tmp5 <> 0
  115.                          then
  116.                                         str := format('%.2f', [Tmp5]);
  117.                 end; // case
  118.                 if str = ''
  119.                 then
  120.                         rc := sqlite3_bind_null(hndl, i)
  121.                 else
  122.                         rc := sqlite3_bind_text(hndl, i, pchar(str), -1, TSQLite3Destructor(-1));
  123.                 if (rc <> SQLITE_OK)
  124.                 then begin
  125.                         msg := 'TTrade.WriteTradeFields: Bind Column' + IntToStr(i) + ' failed';
  126.                         raise ETradeException.Create(msg);
  127.                 end;
  128.         end; //for i := 1 to SQLiteBacktestTableColumnCount
  129. //str := IntTostr(quantity) + ' ' + ExitReason + ' ' + floattostr(exitprice);
  130.         repeat
  131.                 rc := sqlite3_step(hndl);
  132.                 case rc of
  133.                         SQLITE_DONE:
  134.                         begin
  135.                                 break;
  136.                         end;
  137.                         SQLITE_BUSY:
  138.                         begin
  139.                                 ShowMessage('Database busy!');
  140.                         end;
  141.                         else begin
  142.                                 msg := 'TTrade.WriteTradeFields Sqlite3_step: '
  143.                                  + SQLiteErrorMessage(rc);
  144.                                 raise ETradeException.Create(msg);
  145.                         end;
  146.                 end; // case
  147.         until false = true; // not so likely
  148.  
  149.         rc := sqlite3_reset(hndl);
  150.         if rc <> 0
  151.         then begin
  152.                 msg := 'TTrade.WriteTradeFields Sqlite3_reset: '
  153.                  + SQLiteErrorMessage(rc);
  154.                 raise ETradeException.Create(msg);
  155.         end;
  156.         TTradeSession(TTradeList(FParentList).TradeSession).
  157.          IncrementSQLiteBacktestTableInsertCount;
  158. end;                                
« Last Edit: February 15, 2019, 06:11:57 pm by kcandrews »

Vodnik

  • Jr. Member
  • **
  • Posts: 85
Re: Which simple DB to use for Lazarus application?
« Reply #9 on: February 14, 2019, 09:34:57 pm »
@lucamar, I understand the idea. It is true for general DB.
In my case DB will be used in a way similar to log file:
new telephone call arrives, new entry appears in DB.
Records will not be updated or deleted (except after retention period).
So I will make a try with SQLite.
Thank you for comments and ideas!

lucamar

  • Hero Member
  • *****
  • Posts: 1943
Re: Which simple DB to use for Lazarus application?
« Reply #10 on: February 14, 2019, 10:08:46 pm »
In my case DB will be used in a way similar to log file:
new telephone call arrives, new entry appears in DB.
Records will not be updated or deleted (except after retention period).
So I will make a try with SQLite.

In that case yes, a little SQLite DB might well be what you're looking for; see here: Appropriate Uses For SQLite. I guess the paragraphs about "Data analysis", "Cache for enterprise data" apply to your application?

But just in case, read the answer to question 5 of the FAQ; if you can live with that, then go ahead!

Have luck! :)
« Last Edit: February 14, 2019, 10:12:56 pm by lucamar »
Turbo Pascal 3 CP/M - Amstrad PCW 8256 (512 KB !!!) :P
Lazarus 2.0.2/2.0.4  - FPC 3.0.4 on:
(K|L)Ubuntu 12..16, Windows XP SP3, various DOSes.

Vodnik

  • Jr. Member
  • **
  • Posts: 85
Re: Which simple DB to use for Lazarus application?
« Reply #11 on: February 15, 2019, 08:24:46 pm »
Thank you for true links, Lucamar.

Thank you for the code, kcandrews.