kbmMemTable data persistence challenges

Home Forums kbmMemTable kbmMemTable data persistence challenges

This topic contains 7 replies, has 3 voices, and was last updated by  kimbomadsen 2 days, 17 hours ago.

  • Author
    Posts
  • #54132

    David Keith
    Participant

    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;

  • #54133

    mrluigi2017
    Participant

    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 1 week, 4 days ago by  mrluigi2017.
  • #54135

    David Keith
    Participant

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

    Thanks again!

  • #54138

    David Keith
    Participant

    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.

  • #54140

    kimbomadsen
    Keymaster

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

  • #54141

    David Keith
    Participant

    Thank you!

  • #54142

    David Keith
    Participant

    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.

  • #54146

    kimbomadsen
    Keymaster

    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.

You must be logged in to reply to this topic.