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

From Free Pascal wiki
Jump to: navigation, search

Contents

Database Programming Articles

References:

Tutorials/practical articles:

  • Lazarus Database Tutorial - An introduction about the usage of Lazarus with databases.
  • SQLdb Tutorial0 - Set up instructions for sample data for the following database tutorials
  • SQLdb Tutorial1 - Tutorial focused on data-bound components to get beginners started. Usable with any SQLDB-supported database; Firebird, PostgreSQL, sqlite specific hints
  • SQLdb Tutorial2 - Followup tutorial focused on data-bound components: editing data. Usable with any SQLDB-supported database; Firebird, PostgreSQL, sqlite specific hints
  • SQLdb Tutorial3 - Login forms, database-independent programming, parameterized queries. Usable with any SQLDB-supported database.
  • SQLdb Tutorial4 - Data modules
  • How to write in-memory database applications in Lazarus/FPC - Practical instructions for programming with BufDataset and MemDataset
  • MySQLDatabases - Creating a database application using MySQL
  • Zeos tutorial - Creating database applications with the Zeos components.
  • Zeos+SQLite Tutorial - Good tutorial using screenshots and screencasts it explain how to use SQLite and Zeos, Spanish (google translate does a good work in translating it to English)
  • Pascal Data Objects is a database API that worked for both FPC and Delphi and utilises native MySQL libraries for version 4.1 and 5.0 and Firebird SQL 1.5, and 2.0. It's inspired by PHP's PDO class.
  • Lazarus Tdbf Tutorial - Tutorial and usage instructions for DBF files (DBase, Visual FoxPro) with Lazarus/FPC
  • Firebird in action - Using Firebird with FPC/Lazarus
  • ODBCConn - Using ODBC with FPC/Lazarus
  • MS Access - How to access an MS Access database
  • SQLite for beginners (spanish) - SQLite oriented tutorial for beginners (From Héctor F. Fiandor Rosario)

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:

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;

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.

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

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.

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;
procedure TMainForm.FormClose(Sender: TObject; var CloseAction: TCloseAction);
begin
 //Save memdatasets to files (to save current session)
 SaveSession;
end;
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;
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;
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;
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;
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;

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.

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;

Then we have a procedure for autoincrement field values calculation:

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;
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;

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:

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

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 the 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 the Filtered property of a detail table to True.

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;
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;

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.

procedure TMainForm.MasterTableAfterScroll(DataSet: TDataSet);
begin
  if vSuppressEvents=True then Exit;
  DetailTable.Refresh;
end;
procedure TMainForm.DetailTableFilterRecord(DataSet: TDataSet;
  var Accept: Boolean);
begin
  if vSuppressEvents=True then Exit;
  Accept:=DataSet.FieldByName('MasterTableId').AsInteger=MasterTable.FieldByName('MasterTableId').AsInteger;
end;

Known problems

There are 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 incredibly slow. Therefore I use my EmptyMemDataset procedure instead of while not EOF do Delete;
  • CopyFromDataSet method copies 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);. Fixed in FPC trunk revision 26233.

TBufDataSet

As previously mentioned, MemDataSet lacks custom filters and the 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 a 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 for details.

You can use the same methods for enforcing referential integrity and primary autoincrement fields as explained for MemDataSet.

There are only small differences between MemDataSet and BufDataset:

MemDataSet BufDataset
DataSet.ClearFields DataSet.Fields.Clear
DataSet.CreateTable DataSet.CreateDataSet

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:

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.Updated:=false; {<<<<---This line is critical as IndexDefs.Update will do nothing on the next sort if it's already true}
  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;

So, you can call this function from a DBGrid in this way:

procedure TFormMain.DBGridProductsTitleClick(Column: TColumn);
begin
  SortBufDataSet(Products, Column.FieldName);
end;

ZMSQL

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

Contributors

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

Personal tools