Which simple DB to use for Lazarus application?
lazarus / 16-Databases

----------------------------------------------------------------------
Vodnik · 2019-02-14 17:11

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 · 2019-02-14 17:18

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

----------------------------------------------------------------------
Vodnik · 2019-02-14 18:18

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 · 2019-02-14 18:36

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: QuoteInformix 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

----------------------------------------------------------------------
Vodnik · 2019-02-14 18:49

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 · 2019-02-14 18:55

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.

----------------------------------------------------------------------
Vodnik · 2019-02-14 19:46

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 · 2019-02-14 20:09

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.

----------------------------------------------------------------------
kcandrews · 2019-02-14 20:14

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TTradeSession.SQLConnectionOpen(LogTrades: Boolean);var   i, rc:
integer;   msg: string;        ErrMsg, tail: Pchar;       
SQLStatement: array[0..56] of char;begin        // establish sql
connection for results (and optionally backtest - log trades)       
rc := SQLite3_Open(pchar(SystemList.RunResultsDB),     
FSQLiteDBHandle);        if rc <> SQLITE_OK        then begin        
      msg := 'TradeSession.SQLConnectionOpen: ' +
SQLiteErrorMessage(rc) + '!';                PostShowMsg(msg);       
       raise ETradeSession.Create(Msg);        end;        rc :=
SQLite3_BusyTimeout(FSQLiteDBHandle, 5000); // 5 seconds to handle
busy or locks        if rc <> SQLITE_OK        then begin            
  msg := 'TradeSession.SQLConnectionOpen: ' + SQLiteErrorMessage(rc) +
'!';                PostShowMsg(msg);                raise
ETradeSession.Create(Msg);        end;        // performance
improvement, set journal_mode = OFF        // we could use WAL write-
ahead logging journal_mode if we needed Rollbacks       
StrPcopy(SQLStatement, 'PRAGMA journal_mode = OFF');        rc :=
sqlite3_exec(FSQLiteDBHandle,         SQLStatement,
nil{@FSQLiteCallback}, nil, ErrMsg);        if (rc <> SQLITE_OK) or
assigned(ErrMsg)        then begin                msg :=
'TradeSession.SQLConnectionOpen: ' + SQLStatement + 'failed - ' +
ErrMsg;                if assigned(Errmsg)                then       
               sqlite3_free(ErrMsg);                raise
ETradeException.Create(msg);        end;        // performance
improvement...don't wait for disk write completion       
StrPcopy(SQLStatement, 'PRAGMA synchronous = OFF');        rc :=
sqlite3_exec(FSQLiteDBHandle,         SQLStatement,
nil{@FSQLiteCallback}, nil, ErrMsg);        if (rc <> SQLITE_OK) or
assigned(ErrMsg)        then begin                msg :=
'TradeSession.SQLConnectionOpen: ' + SQLStatement + 'failed - ' +
ErrMsg;                if assigned(Errmsg)                then       
               sqlite3_free(ErrMsg);                raise
ETradeException.Create(msg);        end;        if LogTrades       
then begin                // create prepared statement template      
if SystemList.UsingSystemRules // new scheme      then               
       StrPcopy(FSQLInsertStatement, 'INSERT INTO ' + 'Trades'       
                + ' VALUES (')      else                       
StrPcopy(FSQLInsertStatement, 'INSERT INTO ' +
TransactionsBacktestTableName_                         + ' VALUES
(');                for i := 1 to SQLiteBacktestTableColumnCount     
         do begin                        if i <
SQLiteBacktestTableColumnCount                        then           
                   Strcat(FSQLInsertStatement, pchar('@v' +
IntToStr(i) + ','))                        else                      
        Strcat(FSQLInsertStatement, pchar('@v' + IntToStr(i) + ')')); 
              end;                rc :=
sqlite3_prepare_v2(SQLiteDBHandle, FSQLInsertStatement,              
 strlen(FSQLInsertStatement) + 1, // apparently optimal but could pass
-1                 FSQLInsertHandle, tail);                if rc <>
SQLITE_OK                then begin                        msg :=
'TradeSession.SQLConnectionOpen: Prepare failed!';                   
   raise ETradeSession.Create(msg);                end;              
 FSQLiteBacktestTableInsertCount := 0;               
StrPcopy(SQLStatement, 'BEGIN TRANSACTION');                rc :=
sqlite3_exec(SQLiteDBHandle,                 SQLStatement,
nil{@FSQLiteCallback}, nil, ErrMsg);                if (rc <>
SQLITE_OK) or assigned(Errmsg)                then begin             
         msg := 'TradeSession.SQLConnectionOpen: BEGIN TRANSACTION
failed - ' + ErrMsg;                        if assigned(Errmsg)      
                then                               
SQLite3_Free(ErrMsg);                        raise
ETradeSession.Create(msg);                end;        end; // if
logtradesend; procedure TTradeSession.SQLConnectionClose;var   rc:
integer;begin        if assigned(FSQLInsertHandle)        then begin 
             rc := sqlite3_finalize(FSQLInsertHandle);               
if rc <> SQLITE_OK                then                       
PostShowMsg('TradeSession.SQLConnectionClose sqlite3_finalize: '     
                  + SQLiteErrorMessage(rc));        end;        rc :=
SQLite3_Close(FSQLiteDBHandle);        if rc <> SQLITE_OK        then 
              PostShowMsg('TradeSession.SQLConnectionClose
sqlite3_Close: '                 + SQLiteErrorMessage(rc));end;      
                         
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TTrade.WriteTradeFields;var        ErrMsg: Pchar;        i, rc :
integer;        msg, str: string;        hndl : Pointer;begin       
hndl :=
TTradeSession(TTradeList(FParentList).TradeSession).SQLInsertHandle;  
      for i := 1 to SQLiteBacktestTableColumnCount        do begin   
           str := ''; // null assumption                case i of    
          1: if (EntryDateTime <> 0)                         then
begin                                if (fracDateTime(EntryDateTime) =
0)                                then                               
        str := formatDateTime('yyyy-mm-dd', EntryDateTime)           
                   else                                         str :=
formatDateTime('yyyy-mm-dd hh:nn:ss', EntryDateTime);                
       end;                2:      str := SymbolName;               
3:      str := IntToStr(SystemID);                4: if (ExitDateTime
<> 0)                         then begin                             
 if (fracDateTime(ExitDateTime) = 0)                               
then                                         str :=
formatDateTime('yyyy-mm-dd', ExitDateTime)                           
   else                                         str :=
formatDateTime('yyyy-mm-dd hh:nn:ss', ExitDateTime);                 
      end;                5:      begin        str :=
IntToStr(Quantity);      end;                6: str := format('%8.*n',
[GenericSymbol.DecimalPrecision,                       
 GenericSymbol.RoundToTick(EntryPriceActual)]);                7: str
:= format('%8.*n',                       
 [GenericSymbol.DecimalPrecision,                         
GenericSymbol.RoundToTick(ExitPriceActual)]);                8:      
        // NOTE: since this '%n' creates thousands separator commas, 
             // the column must be TEXT(10) in sqlite               
// (cannot be NUMERIC because it will store as integer when possible) 
              // aggregate math can be done by removing commas in
SELECT statements                        str := format('%.2f',
[TradeProfit]);                9: str := format('%.2f',
[TradeProfitPercent]);                // entry stuff               
10: if EntryReason <> ''                         then                
              str := EntryReason                    else             
       str := 'Unknown'; // can't be null since is key               
11: if FInitialEntryWavePattern <> wpNone//''                       
 then begin                                str :=
WavePatternToString(FInitialEntryWavePattern);            if
FInitialEntryWaveIsWaveUp                                then        
     str := str + ' UP'            else                str := str + '
DN';          end;                12: if EntryDegree <> 0            
           then                                str :=
IntToStr(EntryDegree);                13: if EnteredAt <> ''         
              then                                str := EnteredAt;  
            14: if EntryDetail <> ''                         then    
                          str := EntryDetail;                15: str
:= TimeIntervalAsString(FBardatalist.TimeInterval);                //
exit stuff                16: if ExitReason <> ''                    
   then                            str := ExitReason          else   
        str := 'Unknown'; // can't be null since is key              
17: if ExitedAt <> ''                         then                   
                   str := ExitedAt;                18: if ExitDetail
<> ''                         then                                   
   str := ExitDetail;                19: str :=
TimeIntervalAsString(FBardatalist.TimeInterval);                20: if
BETriggerDateTime <> 0                         then                  
            str := FloatToStr(GenericSymbol.RoundToTick(BETrigger)); 
             21: if (BETriggerDateTime <> 0)                       
 then begin                                if (frac(BETriggerDateTime)
= 0)                                then                             
          str := formatDateTime('yyyy-mm-dd', BETriggerDateTime)     
                         else                                       
 str := formatDateTime('yyyy-mm-dd hh:nn:ss', BETriggerDateTime);    
                   end;                22: str := IntToStr(DaysHeld); 
              23: if TimeHeld > 0                         then       
                               str := format('%.2f', [TimeHeld /
OneHour]);                // temporary variables                24: if
tmp1 <> 0                         then                               
       str := format('%.2f', [Tmp1]);                25: if tmp2 <> 0 
                       then                                        str
:= format('%.2f', [Tmp2]);                26: if tmp3 <> 0           
            then                                        str :=
format('%.2f', [Tmp3]);                27: if tmp4 <> 0              
         then                                        str :=
format('%.2f', [Tmp4]);                28: if tmp5 <> 0              
         then                                        str :=
format('%.2f', [Tmp5]);                end; // case                if
str = ''                then                        rc :=
sqlite3_bind_null(hndl, i)                else                       
rc := sqlite3_bind_text(hndl, i, pchar(str), -1,
TSQLite3Destructor(-1));                if (rc <> SQLITE_OK)         
     then begin                        msg :=
'TTrade.WriteTradeFields: Bind Column' + IntToStr(i) + ' failed';    
                  raise ETradeException.Create(msg);               
end;        end; //for i := 1 to SQLiteBacktestTableColumnCount//str
:= IntTostr(quantity) + ' ' + ExitReason + ' ' +
floattostr(exitprice);        repeat                rc :=
sqlite3_step(hndl);                case rc of                       
SQLITE_DONE:                        begin                            
  break;                        end;                       
SQLITE_BUSY:                        begin                            
  ShowMessage('Database busy!');                        end;         
             else begin                                msg :=
'TTrade.WriteTradeFields Sqlite3_step: '                             
  + SQLiteErrorMessage(rc);                                raise
ETradeException.Create(msg);                        end;             
 end; // case        until false = true; // not so likely         rc
:= sqlite3_reset(hndl);        if rc <> 0        then begin          
    msg := 'TTrade.WriteTradeFields Sqlite3_reset: '                 +
SQLiteErrorMessage(rc);                raise
ETradeException.Create(msg);        end;       
TTradeSession(TTradeList(FParentList).TradeSession).       
 IncrementSQLiteBacktestTableInsertCount;end;

----------------------------------------------------------------------
Vodnik · 2019-02-14 20:34

@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 · 2019-02-14 21:08

Quote from: Vodnik on February 14, 2019, 09:34:57 pmIn 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!

----------------------------------------------------------------------
Vodnik · 2019-02-15 19:24

Thank you for true links, Lucamar.

Thank you for the code, kcandrews.