kbmMemTable data persistence challenges

[David Keith]

Hi Kim!

I’ve been trying to figure out what is required to persist the data that I store in a memtable, as well as being able to load the data properly as needed.

I’ve already created a product that is in production, but it doesn’t consistently save data entries to disk using the persistent data method.

My goals for this project going forward is to abstract the data storage backend so that any database could be used for data storage. My short term plans include kbm (persistent) and firebird.

What I want to do is have data changes that are posted to a kbm memtable to either save to disk (csv stream format) as they are posted, or saved to a db such as firebird, oracle, sql server etc. Kind of a ‘ripple effect’.

What examples are available to demonstrate how to save data that is updated in the memtable to either persistent storage, or to an external db?

Here are some code snippets of my current effort, which is failing because the kbmMemTable structure bits are being written with every kbmMemTable data bit, appending to the persistent file. So I get multiple instances of the table schema, multiple instances of the data etc. I’ve attached a sample data file for your perusal.

type
TCSVStreamFormat = class(TkbmCSVStreamFormat)
public
constructor Create(AOwner: TComponent); reintroduce;
end;

constructor TCSVStreamFormat.Create(AOwner: TComponent);
begin
inherited Create(AOwner);
CSVFieldDelimiter := ‘^’;
CSVRecordDelimiter := #3;
sfData := [sfSaveData,sfLoadData];
sfFieldKind := [sfSaveFieldKind,sfLoadFieldKind];
sfDef := [sfSaveDef,sfLoadDef];
sfFromStart := [sfLoadFromStart];
sfAppend := [sfSaveAppend,sfSaveInsert];
sfIndexDef := [sfSaveIndexDef,sfLoadIndexDef];
sfDisplayWidth := [sfLoadDetermineWidth];
sfPlaceHolders := [sfSavePlaceholders];
end;

…

procedure TDataKBMMem.CreateMessageTable;
var
CSV: TkbmCSVStreamFormat;
begin
if not Assigned(FTblMessages) then
begin
FTblMessages := TkbmMemTable.Create(Self);
with FTblMessages do
begin
FCSVMsg := TCSVStreamFormat.Create(FTblMessages);
DefaultFormat := FCSVMsg;
Persistent := True;
PersistentFormat := FCSVMsg;
PersistentFile := MessageDBFileName;
if FileExists(MessageDBFileName) then
LoadMsgData
else
begin
FieldDefs.Add(‘fldMsgDateTime’,ftDateTime,0,True);
FieldDefs[Pred(FieldDefs.Count)].DisplayName := ‘Message Date/Time’;
end;
end;
end;
end;

procedure TDataKBMMem.LoadMsgData;
begin
inherited;
// FTblMessages.LoadFromFile(MessageDBFileName);
FTblMessages.LoadPersistent;
end;

procedure TDataKBMMem.SaveMsgData;
begin
inherited;
FTblMessages.SaveToFile(MessageDBFileName);
// also tried SavePersistent, but doesn’t write to disk every time
end;

procedure TDataKBMMem.SetMsgDBFileName(const AValue: String);
begin
inherited;
FTblMessages.PersistentFile := AValue;
end;

type
TMyStorageMechanism = class(TComponent)
protected
FMessageGridCols: TDBGridColumns; // display for message table
end;

// a class that adds fields/indicies etc. to the existing kbmMemTable, which is created in a base class.
procedure TMyStorageMechanism.ConfigureMessageStoreTable;
var
i: Integer;
begin
with FDM.MessageTable do
begin
if not FileExists(FDM.MessageDBFileName) then
begin
FieldDefs.Add(‘fldStatus’,ftString,50,True);
FieldDefs[Pred(FieldDefs.Count)].DisplayName := ‘Status’;
FieldDefs.Add(‘fldStatusMsg’,ftString,512,True);
FieldDefs[Pred(FieldDefs.Count)].DisplayName := ‘Status Message’;
Open;
AddIndex(‘idxMsgDateTime’,’fldMsgDateTime’,[ixPrimary,ixDescending]);
AddIndex(‘idxStatusMsgDateTime’,’fldStatusMsg;fldMsgDateTime’,[ixDescending,ixCaseInsensitive]);
IndexName := ‘idxStatusMsgDateTime’;
FDM.SaveMsgData;
end;
end;
end;

procedure TMyStorageMechanism.StoreMessage;
begin
with FDM.MessageTable do
begin
DisableControls;
try
if RecordCount = 0 then
AppendRecord([Now,Status,StatusMsg,RESTResponse])
else
begin
bm := GetBookmark;
First;
InsertRecord([Now,Status,StatusMsg,RESTResponse]);
end;
GotoBookmark(bm);
finally
FreeBookmark(bm);
// FDM.SaveMsgData;
SavePersistent;
EnableControls;
end;
end;

[mrluigi2017]

You could use the orm for this. I wrote a little very basic demo. I used firedac as a connection but you can change this. You need to setup the firedac  connection before you can use it. (Make a test database for it!) In the demo you have to take care of the primary key(message_id) yourself.

Here is a link to the code:

https://lycaproductions.stackstorage.com/s/5L82VjorZ4ylSNC

is valid till 31-08-2019

Here the code itself:

unit f_main;

interface

uses
kbmMWOrm,
kbmMWNullable,
kbmMWRtti,

Data.DB,
Generics.Collections,
Winapi.Windows,
Winapi.Messages,
System.SysUtils,
System.Variants,
System.Classes,
Vcl.Graphics,
Vcl.Controls,
Vcl.Forms,
Vcl.Dialogs,
kbmMWCustomConnectionPool,
kbmMWFireDAC,
kbmMemTable,
Vcl.ExtCtrls,
Vcl.DBCtrls,
Vcl.Grids,
Vcl.DBGrids,
Vcl.StdCtrls,
kbmMemCSVStreamFormat,
FireDAC.Stan.Intf,
FireDAC.Stan.Option,
FireDAC.Stan.Error,
FireDAC.UI.Intf,
FireDAC.Phys.Intf,
FireDAC.Stan.Def,
FireDAC.Stan.Pool,
FireDAC.Stan.Async,
FireDAC.Phys,
FireDAC.Phys.FB,
FireDAC.Phys.FBDef,
FireDAC.VCLUI.Wait,
FireDAC.Comp.Client,
kbmMWSQLRewriter,
kbmMWCustomSQLMetaData,
kbmMWInterbaseMetaData;

type
TForm1 = class(TForm)
kbmMWFireDACConnectionPool: TkbmMWFireDACConnectionPool;
// Notice that the kbmCSVStreamFormat is set to the AllDataFormat property
mtMessages: TkbmMemTable;
dsMessages: TDataSource;
DBNavigator: TDBNavigator;
grdMessages: TDBGrid;
btnSaveToFile: TButton;
btnSaveToFireBird: TButton;
kbmCSVStreamFormat: TkbmCSVStreamFormat;
btnOpenFromFile: TButton;
btnOpenFromFireBird: TButton;
FDConnection: TFDConnection;
mtMessagesmessage_id: TIntegerField;
mtMessagesmessage_text: TStringField;
btnOpen: TButton;
btnCreateTable: TButton;
kbmMWInterbaseMetaData: TkbmMWInterbaseMetaData;
kbmMWInterbaseSQLRewriter: TkbmMWInterbaseSQLRewriter;
procedure btnCreateTableClick(Sender: TObject);
procedure btnOpenClick(Sender: TObject);
procedure btnOpenFromFileClick(Sender: TObject);
procedure btnSaveToFileClick(Sender: TObject);
procedure btnSaveToFireBirdClick(Sender: TObject);
procedure FormCreate(Sender: TObject);
private
Orm: TkbmMWORM;
public
procedure SaveMessageMemoryTableToFile;
procedure SaveMessageMemoryTableToFireBird;

procedure LoadMessagesFromFile;
procedure LoadMessageFromFireBird;
end;

[kbmMW_Table(‘name:my_messages’)]
TMyMessage = class
private
FMessageID: kbmMWNullable<Integer>;
FMessageText: kbmMWNullable<string>;
public
[kbmMW_Field(‘name:message_id, primary:true’, ftInteger)]
property MessageID: kbmMWNullable<Integer> read FMessageID write FMessageID;

[kbmMW_Field(‘name:message_text’, ftString)]
property MessageText: kbmMWNullable<string> read FMessageText write FMessageText;
end;

var
Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.btnCreateTableClick(Sender: TObject);
begin
if not Orm.ExistsTable(TMyMessage) then
begin

if Orm.CreateTable(TMyMessage) then
ShowMessage(‘TMyMessage Table Created’)
else
ShowMessage(‘Error creating table’);

end;
end;

procedure TForm1.btnOpenClick(Sender: TObject);
begin
mtMessages.Open;
end;

procedure TForm1.btnOpenFromFileClick(Sender: TObject);
begin
LoadMessagesFromFile;
end;

procedure TForm1.btnSaveToFileClick(Sender: TObject);
begin
SaveMessageMemoryTableToFile;
end;

procedure TForm1.btnSaveToFireBirdClick(Sender: TObject);
begin
SaveMessageMemoryTableToFireBird;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
// You can change the connection pool to the one you like.
// Before you can persist to the database,
// you have to make sure that the connection pool is setup.
Orm := TkbmMWORM.Create(kbmMWFireDACConnectionPool);
end;

procedure TForm1.LoadMessageFromFireBird;
var
MessageList: TObjectList<TMyMessage>;
begin
MessageList := Orm.QueryList<TMyMessage>;
Orm.ToDataset<TMyMessage>(MessageList, True);
end;

procedure TForm1.LoadMessagesFromFile;
begin
mtMessages.LoadFromFileViaFormat(‘.\MyMessagFile.csv’, kbmCSVStreamFormat);
end;

procedure TForm1.SaveMessageMemoryTableToFile;
begin
mtMessages.SaveToFileViaFormat(‘.\MyMessagFile.csv’, kbmCSVStreamFormat);
end;

procedure TForm1.SaveMessageMemoryTableToFireBird;
var
MessageList: TObjectList<TMyMessage>;
begin
MessageList := Orm.ListFromDataset<TMyMessage>(mtMessages, [usModified, usInserted, usDeleted]);
Orm.Persist(MessageList);
end;

initialization

TkbmMWRTTI.EnableRTTI([TMyMessage, TObjectList<TMyMessage>]);
kbmMWRegisterKnownClasses([TMyMessage, TObjectList<TMyMessage>]);

end.

 

 

• This reply was modified 4 years, 8 months ago by mrluigi2017.

[David Keith]

Thank you! I’ll take a look at this and see if this will fit the bill.

Thanks again!

[David Keith]

Can someone please approve my license request for kbmMemTable? I can’t compile kbmMW without it and I forgot to request that license approval when I requested the license approval for kbmMW.

Thanks.

[kimbomadsen]

The request has been approved and the product is available for download.

[David Keith]

Thank you!

[David Keith]

Okay so I changed my streamFormat assignment from PersistentFormat to the AllDataFormat; turned off persistence/set Persistent := False;

In saving with my streamFormat each time a transaction occurs, I get an entire new copy of the table structure AND data appended to the end of the storage file.

Should I use… two streamFormats? One for creation and one for transactions? Should I… change the settings on the one stream format so that once the table structure is saved it only saves data after that?

Please advise.

[kimbomadsen]

You can use the same streamformat, but just set the stream options before use. In multithread scenarios (like when using it in a service in kbmMW), just make sure that each concurrent thread will have its own streamformat component to work with on each occation. That will happen if you are placing it on kbmMW’s service data module.

[Author]

Posts