kbmMemTable data persistence challenges

Home Forums kbmMemTable kbmMemTable data persistence challenges

  • This topic has 7 replies, 3 voices, and was last updated 5 months ago by kimbomadsen.
Viewing 7 reply threads
  • 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 5 months, 1 week 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.

Viewing 7 reply threads
  • You must be logged in to reply to this topic.
Skip to toolbar