Difference between revisions of "How to write in-memory database applications in Lazarus/FPC"
(→ZMSQL) |
Sekelsenmat (talk | contribs) |
||
Line 1: | Line 1: | ||
+ | {{Database Programming}} | ||
== Introduction == | == 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. | 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. |
Revision as of 09:39, 6 December 2011
References:
Tutorials/practical articles:
Databases |
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 illustrate how to program relational non-SQL memory databases, focusing on enforcing relation integrity and filtering, simulating autoincrement primary fields and similar. Bear in mind that I don't consider myself a programming guru, or a database expert. I program mostly for fun, as a hobbyist. I just want to share with you what I have learned experimenting with MemDatasets. There might be some 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
- 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. 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
Better way to write in-memory databases is to use 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)