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

From Lazarus wiki
Revision as of 15:07, 12 October 2009 by Tatamata (talk | contribs)
Jump to navigationJump to search

Introduction

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

  • Fast execution. Since any 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.

I will ilustrate how to program relational non-SQL memory databases, focusing on enforcing relation integrity and filtration, mimicring autoincrement primary fields and similar. Bare in mind that I don't consider myself neither programming guru, neither databases expert. I'm programming mostly for fun, as hobbiest. I just want to share with you what I have learned experimenting with MemDatasets. There might be some more efficient ways to do the same. If so, please, feel free to contribute to this document for the benefit of Lazarus/FPC community.

Saving MemDatasets To Persistent Files

In Interface part of your code, declare array type for storing information about all your MemDataSets that you want to make persistent at the end of a session and restored at the beginning of next session. You have to declare variable, too. I use global variable vSupressEvents of type boolean, for supressing Dataset events used for referential integrity enforcing, during data restore. <delphi> type

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

var

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

</delphi> Instead of using global variable like I did, you could make it a property of the main form, also. TMemDataset has way to natively store data to persistent file, 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 calculator for autoincrement fields. We need global variables or public properties for storing current autoincrement field value. I prefere 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 vSupressEvents 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
  • Filtration by using Filter and Filtered property does not work. You must use hardcoding in 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 current cursor to the end of 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. Since there is not component, you shall 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 DataSet.CreateTable you have to use DataSet.CreateDataSet etc.

Sorting DBGrid on TitleClick event for TBufDataSet

If you wish to enable consequtive ascending and descending sorting od DBGrid showing some data from TBufDataSet, you could use the following method: <delphi> function TFormMain.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>

Contributors

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