How to write in-memory database applications in Lazarus/FPC

From Lazarus wiki
Revision as of 14:16, 10 January 2012 by BigChimp (talk | contribs) (ZMSQL)
Databases portal

References:

Tutorials/practical articles:

Databases

Advantage - MySQL - MSSQL - Postgres - Interbase - Firebird - Oracle - ODBC - Paradox - SQLite - dBASE - MS Access - Zeos

Introduction

There are certain circumstances when in-memory datasets make sense. If you need a fast, single-user, non mission-critical, non SQL database, without need for transactions, MemDataset could suit your needs.

Some benefits are:

  • Fast execution. Since all processing is done in memory, no data is saved on hard disk untill explicitely asked. Memory is surely faster than hard disk.
  • No need for external libraries (no .so or .dll files), no need for server installation
  • Code is multiplatform and can be compiled on any OS instantly
  • Since all programming is done in Lazarus/FPC, such applications are easier for maintenance. Instead of constantly switching from back-end programming to front-end programming, by using MemDatasets you can concentrate on your Pascal code.

Note: later on in this article, BufDataset is introduced. Bufdataset often is a better choice than MemDataset.

I will illustrate how to program relational non-SQL memory databases, focusing on enforcing relation integrity and filtering, simulating autoincrement primary fields and similar. This page shares with you what I have learned experimenting with MemDatasets. There might be some other, more efficient way to do this. If so, please, feel free to contribute to this document for the benefit of the Lazarus/FPC community.

The memds unit provides TMemDataset, so you will need to add that to your uses clause.

Saving MemDatasets to persistent files

In the Interface part of your code, declare an array type for storing information about all the MemDataSets that you want to make persistent at the end of a session and restore at the beginning of the next session. You have to declare a variable of type TSaveTables, too.

I also use a global variable vSuppressEvents of type boolean, for suppressing Dataset events used for referential integrity enforcement, during data restore.

You get this: <delphi>type

 TSaveTables=array[1..15] of TMemDataset;    

var

 //Global variable that holds tables for saving/restoring session
 vSaveTables:TSaveTables;                  
 //Suppress events flag variables. Used during data loading from files.
 vSuppressEvents:Boolean;</delphi>  

Instead of using global variables like I did, you could make them a property of the main form, also. TMemDataset has a way to natively store data to persistent file: the SaveToFile method. But, you could rather choose to save data to CSV files for easier external post processing. Therefore, I will combine both ways into same procedures. I define a constant cSaveRestore in the Interface part, by which I can define whether data will be stored and loaded as native MemDataset files or CSV files. <delphi>const

 //Constant cSaveRestore determines the way for saving and restoring of MemDatasets to persistent files
 cSaveRestore=0; //0=MemDataset native way, 1=saving and restoring from CSV                               

</delphi>

Now, you can save MemDatasets on FormClose event and load them on FormCreate event. Instantiate elements of the array of MemDatasets on the FormCreate event, too.

<delphi>procedure TMainForm.FormCreate(Sender: TObject); begin

 //List of tables to be saved/restored for a session
 vSaveTables[1]:=Products;
 vSaveTables[2]:=Boms;
 vSaveTables[3]:=Stocks;
 vSaveTables[4]:=Orders;
 vSaveTables[5]:=BomCalculationProducts;
 vSaveTables[6]:=BomCalculationComponents;
 vSaveTables[7]:=BomCalculationFooter;
 vSaveTables[8]:=BomCalculationProductsMultiple;
 vSaveTables[9]:=BomCalculationComponentsMultiple;
 vSaveTables[10]:=BomCalculationFooterMultiple;
 vSaveTables[11]:=ImportVariants;
 vSaveTables[12]:=ImportToTables;
 vSaveTables[13]:=ImportToFields;
 vSaveTables[14]:=ImportFromTables;
 vSaveTables[15]:=ImportFromFields;
 //Restore session
 RestoreSession;
 GetAutoincrementPrimaryFields;

end;</delphi>

<delphi>procedure TMainForm.FormClose(Sender: TObject; var CloseAction: TCloseAction); begin

//Save memdatasets to files (to save current session)
SaveSession;

end;</delphi>

<delphi>procedure RestoreSession; var

 I:Integer;

begin

 try
   MemoMessages.Append(TimeToStr(Now())+' Starting restoration of previously saved session.');
   vSuppressEvents:=True; //Supress events used for referential integrity enforcing
   //Disable controls and refresh all datasets
   for I:=Low(vSaveTables) to High(vSaveTables) do begin
     vSaveTables[I].DisableControls;
     vSaveTables[I].Refresh; //Important if dataset was filtered
   end;
   //Load memdatasets from files (to restore previous session)
   for I:=Low(vSaveTables) to High(vSaveTables) do begin
     vSaveTables[I].First;
     MemoMessages.Append(TimeToStr(Now())+' Starting restoration of table: '+vSaveTables[I].Name);
     try
       //If data is loaded from a csv file, then table must be deleted first.
       if cSaveRestore=1 then begin
         MemoMessages.Append(TimeToStr(Now())+' Starting delete of all records in table: '+vSaveTables[I].Name);
         //This way of deleting all records is incredibly slow.
         {while not vSaveTables[I].EOF do begin
           vSaveTables[I].Delete;
         end;}
         //This method for deleting of all records is much faster
         EmptyMemDataSet(vSaveTables[I]);
         MemoMessages.Append(TimeToStr(Now())+' All records from table: '+vSaveTables[I].Name+' deleted.');
       end;
     except
       on E:Exception do begin
         MemoMessages.Append(TimeToStr(Now())+' Error while deleteing records from table: '+vSaveTables[I].Name +'. '+E.Message);
       end;
     end;
     try
       try
         MemoMessages.Append(TimeToStr(Now())+' Restoring table: '+vSaveTables[I].Name);
         //Check constant for way of saving/restoring data and load saved session
         case cSaveRestore of
           0:vSaveTables[I].LoadFromFile(vSaveTables[I].Name);
           1:LoadFromCsv(vSaveTables[I]);
         end;
       except
         on E:Exception do begin
           MemoMessages.Append(TimeToStr(Now())+' Error while restoring table: '+vSaveTables[I].Name +'. '+E.Message);
         end;
       end;
     finally
       vSaveTables[I].Active:=True;//Needed because of LoadFromFile method....
     end;
     MemoMessages.Append(TimeToStr(Now())+' Table: '+vSaveTables[I].Name+' restored.');
   end;
 finally
   vSuppressEvents:=False;
   //Refresh all datasets  and enable controls
   for I:=Low(vSaveTables) to High(vSaveTables) do begin
     vSaveTables[I].Refresh; //Needed for tables that are filtered.
     vSaveTables[I].EnableControls;
   end;
    MemoMessages.Append(TimeToStr(Now())+' All tables restored from saved files.');
 end;

end;</delphi>

<delphi>procedure SaveSession; var

 I:Integer;

begin

 try
   MemoMessages.Append(TimeToStr(Now())+' Starting saving session to persistent files.');
   vSuppressEvents:=True;
   //Disable controls and refresh all datasets
   for I:=Low(vSaveTables) to High(vSaveTables) do begin
     vSaveTables[I].DisableControls;
     vSaveTables[I].Refresh; //Important if dataset was filtered
   end;
   //Save session to file
   for I:=Low(vSaveTables) to High(vSaveTables) do begin
     vSaveTables[I].First;
     MemoMessages.Append(TimeToStr(Now())+' Saving table: '+vSaveTables[I].Name);
     try
       //Check constant for way of saving/restoring data and save session
       case cSaveRestore of
         0:vSaveTables[I].SaveToFile(vSaveTables[I].Name);
         1:SaveToCsv(vSaveTables[I]);
       end;
     except
       on E:Exception do begin
         MemoMessages.Append(TimeToStr(Now())+' Error while saving table: '+vSaveTables[I].Name +'. '+E.Message);
       end;
     end;
     MemoMessages.Append(TimeToStr(Now())+' Table: '+vSaveTables[I].Name+' saved.');
   end;
 finally
   vSuppressEvents:=False;
   //Refresh all datasets  and enable controls
   for I:=Low(vSaveTables) to High(vSaveTables) do begin
     vSaveTables[I].Refresh; //Needed for tables that are filtered
     vSaveTables[I].EnableControls;
   end;
    MemoMessages.Append(TimeToStr(Now())+' All tables saved to files.');
 end;

end;</delphi>

<delphi>procedure EmptyMemDataSet(DataSet:TMemDataSet); var

 vTemporaryMemDataSet:TMemDataSet;
 vFieldDef:TFieldDef;
 I:Integer;

begin

 try
   //Create temporary MemDataSet
   vTemporaryMemDataSet:=TMemDataSet.Create(nil);
   //Store FieldDefs to Temporary MemDataSet
   for I:=0 to DataSet.FieldDefs.Count-1 do begin
     vFieldDef:=vTemporaryMemDataSet.FieldDefs.AddFieldDef;
     with DataSet.FieldDefs[I] do begin
       vFieldDef.Name:=Name;
       vFieldDef.DataType:=DataType;
       vFieldDef.Size:=Size;
       vFieldDef.Required:=Required;
     end;
   end;
   //Clear existing fielddefs
   DataSet.Clear;
   //Restore fielddefs
   DataSet.FieldDefs:=vTemporaryMemDataSet.FieldDefs;
   DataSet.Active:=True;
 finally
 vTemporaryMemDataSet.Clear;
 vTemporaryMemDataSet.Free;
 end;

end;</delphi>

<delphi>procedure LoadFromCsv(DataSet:TDataSet); var

 vFieldCount:Integer;
 I:Integer;

begin

 try
   //Assign SdfDataSetTemporary
   with SdfDataSetTemporary do begin
     Active:=False;
     ClearFields;
     FileName:=DataSet.Name+'.txt';
     FirstLineAsSchema:=True;
     Active:=True;
     //Determine number of fields
     vFieldCount:=FieldDefs.Count;
   end;
   //Iterate through SdfDataSetTemporary and insert records into MemDataSet
   SdfDataSetTemporary.First;
   while not SdfDataSetTemporary.EOF do begin
     DataSet.Append;
     //Iterate through FieldDefs
     for I:=0 to vFieldCount-1 do begin
       try
         DataSet.Fields[I].Value:=SdfDataSetTemporary.Fields[I].Value;
       except
         on E:Exception do begin
           MemoMessages.Append(TimeToStr(Now())+' Error while setting value for field: '
            +DataSet.Name+'.'+DataSet.Fields[I].Name +'. '+E.Message);
         end;
       end;
     end;
     try
       DataSet.Post;
     except
       on E:Exception do begin
         MemoMessages.Append(TimeToStr(Now())+' Error while posting record to table: '
          +DataSet.Name+'.'+E.Message);
       end;
     end;
     SdfDataSetTemporary.Next;
   end;
 finally
   SdfDataSetTemporary.Active:=False;
   SdfDataSetTemporary.ClearFields;
 end;

end;</delphi>

<delphi>procedure SaveToCsv(DataSet:TDataSet); var

 myFileName:string;
 myTextFile: TextFile;
 i: integer;
 s: string;

begin

 myFileName:=DataSet.Name+'.txt';
 //create a new file
 AssignFile(myTextFile, myFileName);
 Rewrite(myTextFile);
 s := ; //initialize empty string
 try
   //write field names (as column headers)
   for i := 0 to DataSet.Fields.Count - 1 do
     begin
       s := s + Format('%s,', [DataSet.Fields[i].FieldName]);
     end;
   Writeln(myTextFile, s);
   DataSet.First;
   //write field values
   while not DataSet.Eof do
     begin
       s := ;
       for i := 0 to DataSet.FieldCount - 1 do
         begin
           //Numerical fields without quotes, string fields with quotes
           if ((DataSet.FieldDefs[i].DataType=ftInteger)
            or (DataSet.FieldDefs[i].DataType=ftFloat)) then
             s := s + Format('%s,', [DataSet.Fields[i].AsString])
           else
             s := s + Format('"%s",', [DataSet.Fields[i].AsString]);
         end;
       Writeln(myTextfile, s);
       DataSet.Next;
     end;
 finally
   CloseFile(myTextFile);
 end;

end;</delphi>

Autoincrement Primary Keys

Autoincrement field type is not supported by MemDataset. Nevertheless, you can imitate it by using Integer field type and providing a calculator for autoincrement fields. We need global variables or public properties for storing current autoincrement field value. I prefer global variables, declared in Interface part. <delphi>var

 //Global variables used for calculation of autoincrement primary key fields of MemDatasets
 vCurrentId:Integer=0;
 vProductsId:Integer=0;
 vBomsId:Integer=0;
 vBomCalculationProductsId:Integer=0;
 vBomCalculationComponentsId:Integer=0;
 vBomCalculationFooterId:Integer=0;
 vBomCalculationProductsMultipleId:Integer=0;
 vBomCalculationComponentsMultipleId:Integer=0;
 vBomCalculationFooterMultipleId:Integer=0;
 vStocksId:Integer=0;
 vOrdersId:Integer=0;
 vImportVariantsId:Integer=0;
 vImportToTablesId:Integer=0;
 vImportToFieldsId:Integer=0;
 vImportFromTablesId:Integer=0;
 vImportFromFieldsId:Integer=0;</delphi>

Then we have a procedure for autoincrement field values calculation:

<delphi>procedure GetAutoincrementPrimaryFields; var

 I:Integer;
 vId:^Integer;

begin

 try
   MemoMessages.Lines.Append(TimeToStr(Now())+' Getting information about autoincrement fields');
   vSuppressEvents:=True;
   //Disable controls and refresh all datasets
   for I:=Low(vSaveTables) to High(vSaveTables) do begin
     vSaveTables[I].DisableControls;
     vSaveTables[I].Refresh; //Important if dataset was filtered
   end;
   for I:=Low(vSaveTables) to High(vSaveTables) do begin
     with vSaveTables[I] do begin
       //Use appropriate global variable
       case StringToCaseSelect(Name,
         ['Products','Boms','Stocks','Orders',
           'BomCalculationProducts','BomCalculationComponents','BomCalculationFooter',
           'BomCalculationProductsMultiple','BomCalculationComponentsMultiple','BomCalculationFooterMultiple',
           'ImportVariants','ImportToTables','ImportToFields','ImportFromTables','ImportFromFields']) of
         0:vId:=@vProductsId;
         1:vId:=@vBomsId;
         2:vId:=@vStocksId;
         3:vId:=@vOrdersId;
         4:vId:=@vBomCalculationProductsId;
         5:vId:=@vBomCalculationComponentsId;
         6:vId:=@vBomCalculationFooterId;
         7:vId:=@vBomCalculationProductsMultipleId;
         8:vId:=@vBomCalculationComponentsMultipleId;
         9:vId:=@vBomCalculationFooterMultipleId;
         10:vId:=@vImportVariantsId;
         11:vId:=@vImportToTablesId;
         12:vId:=@vImportToFieldsId;
         13:vId:=@vImportFromTablesId;
         14:vId:=@vImportFromFieldsId;
       end;
       try
         //Find last value of Id and save it to global variable
         Last;
         vCurrentId:=FieldByName(Name+'Id').AsInteger;
         if (vCurrentId>vId^) then vId^:=vCurrentId;
       finally
         //Remove reference;
         vId:=nil;
       end;
     end;
   end;
 finally
   vSuppressEvents:=False;
   //Refresh all datasets  and enable controls
   for I:=Low(vSaveTables) to High(vSaveTables) do begin
     vSaveTables[I].Refresh;
     vSaveTables[I].EnableControls;
   end;
    MemoMessages.Lines.Append(TimeToStr(Now())+' Autoincrement fields - done.');
 end;

end;</delphi>

<delphi>function StringToCaseSelect(Selector:string;CaseList:array of string):Integer; var

 cnt: integer;

begin

 Result:=-1;
 for cnt:=0 to Length(CaseList)-1 do
 begin
   if CompareText(Selector, CaseList[cnt]) = 0 then
   begin
     Result:=cnt;
     Break;
   end;
 end;

end;</delphi>

The GetAutoincrementPrimaryFields procedure is called every time after you restore (load) data from persistent files, in order to load last autoincrement values into global variables (or properties, as you prefer). Autoincrementing is done in OnNewRecord event of every MemDataset. For example, for MemDataset Orders:

<delphi>procedure TMainForm.OrdersNewRecord(DataSet: TDataSet); begin

 if vSuppressEvents=True then Exit;
 //Set new autoincrement value
 vOrdersId:=vOrdersId+1;
 DataSet.FieldByName('OrdersId').AsInteger:=vOrdersId;

end;</delphi>

As already explained, I use vSuppressEvents global variable as flag for the case of restoring data from persistent files.

Enforcing Referential Integrity

There is no enforced referential integrity implemented in MemDataset component, so you have to do it on your own. Let's assume we have two tables: MasterTable and DetailTable. Insert/Update referential integrity code is provided in BeforePost event of the DetailTable, while Delete referential integrity is provided in BeforeDelete event of the MasterTable. Please, read this again. Insert/Update - BeforePost of detail table, Delete - BeforeDelete of master table. Don't forget to set Filtered property of a detail table to True.

<delphi>procedure TMainForm.MasterTableBeforeDelete(DataSet: TDataSet); begin

 if vSuppressEvents=True then Exit;
 try
   DetailTable.DisableControls;
   //Enforce referential delete for table "MasterTable"
   while not DetailTable.EOF do begin
     DetailTable.Delete;
   end;
   DetailTable.Refresh;
 finally
   DetailTable.EnableControls;
 end;

end;</delphi>

<delphi>procedure TMainForm.DetailTableBeforePost(DataSet: TDataSet); begin

 if vSuppressEvents=True then Exit;
 //Enforce referential insert/update for table "DetailTable"
 DataSet.FieldByName('MasterTableId').AsInteger:=MasterTable.FieldByName('MasterTableId').AsInteger;

end;</delphi>

After you provided referential Insert/Update/Delete, all you must do is to provide code for master/detail filtration of data. You do it in AfterScroll event of MasterTable and in OnFilter event of DetailTable.

<delphi>procedure TMainForm.MasterTableAfterScroll(DataSet: TDataSet); begin

 if vSuppressEvents=True then Exit;
 DetailTable.Refresh;

end;</delphi>

<delphi>procedure TMainForm.DetailTableFilterRecord(DataSet: TDataSet;

 var Accept: Boolean);

begin

 if vSuppressEvents=True then Exit;
 Accept:=DataSet.FieldByName('MasterTableId').AsInteger=MasterTable.FieldByName('MasterTableId').AsInteger;

end;</delphi>

Known problems

I have realized several limitations when using MemDatasets.

  • Locate method does not work
  • Filtering by using Filter and Filtered property does not work. You must use hardcoding in the OnFilter event.
  • Looping deletion of records seems to be increadibly slow. Therefore I use my EmptyMemDataset procedure instead of while not EOF do Delete;
  • CopyFromDataSet method seems to copy data only from the current cursor position to the end of the source dataset. So, you have to write MemDataset1.First; before MemDataSet2.CopyFromDataSet(MemDataset1);.

TBufDataSet

As previously mentioned, MemDataSet lacks custom filters and Locate method, so it is better to use TBufDataSet instead. TBufDataset is provided by the BufDataset unit. Since there is no component for design-time editing of TBufDataSet, you should create your custom wrapper component or use it through code, in the same way as ClientDataSet in Delphi. Look at the Delphi documentation relating to client datasets. You can use same methods for enforcing referential integrity and primary autoincrement fields as explained for MemDataSet. There are only small differences. For example, you can't use DataSet.ClearFields, but should use DataSet.Fields.Clear; DataSet.FieldDefs.,Clear; instead. Instead of DataSet.CreateTable you have to use DataSet.CreateDataSet etc.

Sorting DBGrid on TitleClick event for TBufDataSet

If you wish to enable consecutive ascending and descending sorting of a DBGrid showing some data from TBufDataSet, you could use the following method:

<delphi>function SortBufDataSet(DataSet: TBufDataSet;const FieldName: String): Boolean; var

 i: Integer;
 IndexDefs: TIndexDefs;
 IndexName: String;
 IndexOptions: TIndexOptions;
 Field: TField;

begin

 Result := False;
 Field := DataSet.Fields.FindField(FieldName);
 //If invalid field name, exit.
 if Field = nil then Exit;
 //if invalid field type, exit.
 if {(Field is TObjectField) or} (Field is TBlobField) or
   {(Field is TAggregateField) or} (Field is TVariantField)
    or (Field is TBinaryField) then Exit;
 //Get IndexDefs and IndexName using RTTI
 if IsPublishedProp(DataSet, 'IndexDefs') then
   IndexDefs := GetObjectProp(DataSet, 'IndexDefs') as TIndexDefs
 else
   Exit;
 if IsPublishedProp(DataSet, 'IndexName') then
   IndexName := GetStrProp(DataSet, 'IndexName')
 else
   Exit;
 //Ensure IndexDefs is up-to-date
 IndexDefs.Update;
 //If an ascending index is already in use,
 //switch to a descending index
 if IndexName = FieldName + '__IdxA'
 then
   begin
     IndexName := FieldName + '__IdxD';
     IndexOptions := [ixDescending];
   end
 else
   begin
     IndexName := FieldName + '__IdxA';
     IndexOptions := [];
   end;
 //Look for existing index
 for i := 0 to Pred(IndexDefs.Count) do
 begin
   if IndexDefs[i].Name = IndexName then
     begin
       Result := True;
       Break
     end;  //if
 end; // for
 //If existing index not found, create one
 if not Result then
     begin
       if IndexName=FieldName + '__IdxD' then
         DataSet.AddIndex(IndexName, FieldName, IndexOptions, FieldName)
       else
         DataSet.AddIndex(IndexName, FieldName, IndexOptions);
       Result := True;
     end; // if not
 //Set the index
 SetStrProp(DataSet, 'IndexName', IndexName);

end;</delphi>

So, you can call this function from a DBGrid in this way: <delphi>procedure TFormMain.DBGridProductsTitleClick(Column: TColumn); begin

 SortBufDataSet(Products, Column.FieldName);

end;</delphi>

ZMSQL

A better way to write in-memory databases is to use the ZMSQL package:

http://wiki.lazarus.freepascal.org/ZMSQL

http://sourceforge.net/projects/lazarus-ccr/files/zmsql/

http://www.lazarus.freepascal.org/index.php/topic,13821.30.html

Contributors

Original tekst written by: Zlatko Matić (matalab@gmail.com)