Data Updater 1.1

DataLogic.pas

unit DataLogic;

interface

uses
  SysUtils, Classes, DB, DBClient;

type
  Tmm = class(TDataModule)
  private
    { Private declarations }
  public
    { Public declarations }

    { Public static declarations }
    class procedure writeTsvFile( //
      var AFile: Textfile; source: TDataSource);
    class procedure writeCsvFile( //
      var AFile: Textfile; source: TDataSource);
    class procedure writeDsvFile( //
      var AFile: Textfile; source: TDataSource; delimiter: String);
    class procedure parseDsvString( //
      const sl: TStrings; const value: string; const delimiter: string);
    class procedure saveDataSet( //
      FileName: String; source: TClientDataSet); overload;
    class procedure loadBuffer( //
      source, buffer, audit: TClientDataSet; FileName: string);
    class procedure restoreTransform( //
      source, buffer, audit: TClientDataSet);
    class procedure loadCurrentFieldsValues( //
      source, buffer, audit: TClientDataSet);

  end;

var
  mm: Tmm;
  isAutoupdate: Boolean;

implementation

uses DataView;

{$R *.dfm}

{
  Writes datatable to file, in tab separated format.
}
class procedure Tmm.writeTsvFile(var AFile: Textfile; source: TDataSource);
begin
  writeDsvFile(AFile, source, Chr(9) { This is tab character. } );
end;

{
  Writes datatable to file, in comma separated format.
}
class procedure Tmm.writeCsvFile(var AFile: Textfile; source: TDataSource);
begin
  writeDsvFile(AFile, source, ',');
end;

{
  Writes datatable to file, in delimiter separated format.
  TODO: `cdMaster.DataSet` should be passed as a parameter.
}
class procedure Tmm.writeDsvFile(var AFile: Textfile; source: TDataSource;
  delimiter: String);
var
  I: Integer;
begin
  with source.DataSet do
  begin
    // field names
    for I := 0 to fieldcount - 2 do
      Write(AFile, Fields[I].fieldName, delimiter);
    Writeln(AFile, Fields[fieldcount - 1].fieldName);

    // content
    First;
    while not source.DataSet.Eof do
    begin
      for I := 0 to fieldcount - 2 do
        Write(AFile, FieldByName(Fields[I].fieldName).AsString, delimiter);
      Writeln(AFile, FieldByName(Fields[fieldcount - 1].fieldName).AsString);
      Next;
    end;
  end;
end;

{

}
class procedure Tmm.parseDsvString(const sl: TStrings; const value: string;
  const delimiter: string);
var
  dx: Integer;
  ns: string;
  txt: string;
  delta: Integer;
begin
  delta := Length(delimiter);
  txt := value + delimiter;
  sl.BeginUpdate;
  sl.Clear;
  try
    while Length(txt) > 0 do
    begin
      dx := Pos(delimiter, txt);
      ns := Copy(txt, 0, dx - 1);
      sl.Add(ns);
      txt := Copy(txt, dx + delta, MaxInt);
    end;
  finally
    sl.EndUpdate;
  end;
end;

{ In local memory, compareing takes more time, then reinserting.

  If we would want to minimize number of transactions, then
  something like following would be more appropriate:

  if newDataset.id < oldDataset.id then newDataset.delete
  if newDataset.id = oldDataset.id then newDataset.update
  if newDataset.id > oldDataset.id then newDataset.add
}
class procedure Tmm.restoreTransform( //
  source, buffer, audit: TClientDataSet);
begin
  if (not source.Active) or (not buffer.Active) then
    Exit;

  source.First;
  while NOT source.Eof do
  begin
    source.delete;
  end;

  source.First;
  buffer.First;
  while NOT buffer.Eof do
  begin
    loadCurrentFieldsValues(source, buffer, audit);
    source.Next;
    buffer.Next
  end;
end;

class procedure Tmm.loadCurrentFieldsValues( //
  source, buffer, audit: TClientDataSet);
var
  fromField, toField: TField;
  fieldName: String;
  I: Integer;
begin
  source.Insert;

  for I := 0 to source.FieldDefs.Count - 1 do
  begin
    fieldName := source.FieldDefs[I].Name;
    fromField := buffer.FindField(fieldName);
    toField := source.FindField(fieldName);

    if assigned(fromField) and assigned(toField) then
      toField.value := fromField.value;
  end;

  source.Post;
end;

{

}
class procedure Tmm.loadBuffer(source, buffer, audit: TClientDataSet;
  FileName: string);
var
  I: Integer;
  text: String;
  delimiter: String;
  ext: String;
  AFile: Textfile;
  columnNames: TStrings;
  contentRow: TStrings;
begin
  if not source.Active then
    Exit;

  ext := ExtractFileExt(FileName);
  if (AnsiCompareText(ext, '.txt') = 0) or
    (AnsiCompareText(ext, '.csv') = 0) then
    delimiter := ','
  else
    delimiter := Chr(9);

  // create buffer table
  buffer.Close;
  buffer.FieldDefs.Assign(source.FieldDefs);
  buffer.CreateDataSet;
  buffer.Open;

  // init
  contentRow := TStringList.Create;
  columnNames := TStringList.Create;

  try
    // open file for reading
    AssignFile(AFile, FileName);
    FileMode := fmOpenRead;
    Reset(AFile);

    // read header
    if not Eof(AFile) then
    begin
      try
        ReadLn(AFile, text);
        parseDsvString(columnNames, text, delimiter);
      except
        on E: Exception do
        begin
          Exit;
        end;
      end;
    end;

    // read content
    while not Eof(AFile) do
    begin
      ReadLn(AFile, text);
      parseDsvString(contentRow, text, delimiter);

      try
        buffer.Append;

        for I := 0 to contentRow.Count - 1 do
        begin
          buffer.FieldByName(columnNames[I]).AsString := contentRow[I];
        end;
        buffer.Post;
      except
        on E: Exception do
        begin
          buffer.delete;
        end;
      end;
    end;
  finally
    // close file and free
    contentRow.Free;
    columnNames.Free;
    CloseFile(AFile);
  end;
end;

{

}
class procedure Tmm.saveDataSet(FileName: String; source: TClientDataSet);
Const
  TAB = Chr(9);
var
  AFile: Textfile;
  ext: string;
begin
  ext := ExtractFileExt(FileName);
  if (AnsiCompareText(ext, '.cds') = 0) or
    (AnsiCompareText(ext, '.xml') = 0) then
  begin
    source.SaveToFile(FileName);
  end
  else
  begin
    AssignFile(AFile, FileName);
    Rewrite(AFile);

    try
      if (AnsiCompareText(ext, '.tab') = 0) or
        (AnsiCompareText(ext, '.tsv') = 0) then
        writeTsvFile(AFile, source.DataSource)
      else if (AnsiCompareText(ext, '.txt') = 0) or
        (AnsiCompareText(ext, '.csv') = 0) then
        writeCsvFile(AFile, source.DataSource);
    finally
      CloseFile(AFile);
    end;
  end;

end;

end.

DataView.pas

unit DataView;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ActnList, StdCtrls, Grids, DBGrids, Menus, DB, DBClient, ExtCtrls,
  DBCtrls, Generics.Collections;

type
  TAlternitive = class(TForm)
    gMaster: TDBGrid;
    gBuffer: TDBGrid;
    gAudit: TDBGrid;
    al: TActionList;
    import: TAction;
    export: TAction;
    restore: TAction;
    audit: TAction;
    OpenDialog: TOpenDialog;
    SaveDialog: TSaveDialog;
    DBNavigator1: TDBNavigator;
    dsMaster: TDataSource;
    dsBuffer: TDataSource;
    dsAudit: TDataSource;
    cdsMaster: TClientDataSet;
    cdsBuffer: TClientDataSet;
    cdsAudit: TClientDataSet;
    appTerminate: TAction;
    MainMenu: TMainMenu;
    File1: TMenuItem;
    Import1: TMenuItem;
    Export1: TMenuItem;
    Exit1: TMenuItem;
    Actions1: TMenuItem;
    ransform1: TMenuItem;
    Help: TMenuItem;
    About1: TMenuItem;
    GroupBoxAudit: TGroupBox;
    GroupBoxBuffer: TGroupBox;
    GroupBoxMaster: TGroupBox;
    advanced: TAction;
    Panel1: TPanel;
    Panel2: TPanel;
    AdvancedView: TMenuItem;
    SaveAuditAs1: TMenuItem;
    exportAudit: TAction;
    autoupdate: TAction;
    filter: TAction;
    procedure auditExecute(Sender: TObject);
    procedure importExecute(Sender: TObject);
    procedure exportExecute(Sender: TObject);
    procedure restoreExecute(Sender: TObject);
    procedure advancedExecute(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure cdsMasterAfterPost(DataSet: TDataSet);
    procedure About1Click(Sender: TObject);
    procedure Exit1Click(Sender: TObject);

  private
    { Private declarations }

  public
    { Public declarations }
  end;

Const
  FILE_FILTER1 = 'Text file|*.txt|' + 'Comma-separated values file|*.csv|' +
    'Tab-separated values file|*.tsv|' + 'Tab-separated values file|*.tab|';
  FILE_FILTER2 = 'Extensible Markup Language file|*.xml|' +
    'Delphi TClientDataset data file|*.cds|';

var
  Alternitive: TAlternitive;
  map: TDictionary<String, Integer>;
  aCount: Integer;
  isAutoupdate: Boolean;

implementation

{
  Todo: add minimum size for a window.
}
uses DataLogic;

{$R *.dfm}

procedure TAlternitive.FormCreate(Sender: TObject);
begin
  isAutoupdate := true;
  aCount := 0;

  SaveDialog := TSaveDialog.Create(nil);
  with SaveDialog do
  begin
    Title := 'Save data as.';
    InitialDir := GetCurrentDir;
    filter := FILE_FILTER1 + FILE_FILTER2;
    DefaultExt := 'tab';
    FilterIndex := 4;
  end;

  OpenDialog := TOpenDialog.Create(nil);
  with OpenDialog do
  begin
    Title := 'Open.';
    InitialDir := GetCurrentDir;
    filter := FILE_FILTER2;
    DefaultExt := 'cds';
    FilterIndex := 2;
  end;

  map := TDictionary<String, Integer>.Create();
  with map do
  begin
    Add('.txt', 0);
    Add('.csv', 1);
    Add('.tab', 2);
    Add('.tsv', 3);
    Add('.xml', 4);
    Add('.cds', 5);
  end;
end;

procedure TAlternitive.FormDestroy(Sender: TObject);
begin
  SaveDialog.Free;
  OpenDialog.Free;
  map.Clear;
  map.Destroy;
end;

procedure TAlternitive.About1Click(Sender: TObject);
begin
  ShowMessage('Data Updater 1.1 ( MIT License ) ' + char(10) + char(10) +
    'Program is single-user database application, that maps to a local file. ' +
    'Updates are automatically fired, when field post event is thrown.');
end;

procedure TAlternitive.advancedExecute(Sender: TObject);
begin
  AdvancedView.Checked := not AdvancedView.Checked;
  if AdvancedView.Checked then
    Panel2.Align := alRight
  else
    Panel2.Align := alNone;
end;

procedure TAlternitive.auditExecute(Sender: TObject);
begin
  if cdsAudit.Active and SaveDialog.Execute then
  begin
    Tmm.saveDataSet(SaveDialog.FileName, cdsAudit);
  end;
end;

procedure TAlternitive.cdsMasterAfterPost(DataSet: TDataSet);
begin
  if cdsMaster.ChangeCount > 0 then
  begin
    cdsAudit.data := cdsMaster.Delta;
    cdsAudit.Open;
  end;
  with TClientDataSet(DataSet) do
  begin
    if Active and isAutoupdate then
    begin
      SaveToFile(FileName);
      Close;
      LoadFromFile(FileName);
    end;
  end;
end;

procedure TAlternitive.Exit1Click(Sender: TObject);
begin
  Application.Terminate;
end;

procedure TAlternitive.exportExecute(Sender: TObject);
begin
  if cdsMaster.Active and SaveDialog.Execute then
  begin
    Tmm.saveDataSet(SaveDialog.FileName, cdsMaster);
  end;
end;

procedure TAlternitive.importExecute(Sender: TObject);
var
  ext: string;
  val: Integer;
begin
  OpenDialog.filter := FILE_FILTER2;

  if OpenDialog.Execute then
  begin
    ext := LowerCase(ExtractFileExt(OpenDialog.FileName));

    if map.TryGetValue(ext, val) then
      if val > 3 then
      begin
        cdsMaster.Close;
        cdsMaster.FileName := OpenDialog.FileName;
        cdsMaster.Open;
        cdsMasterAfterPost(cdsMaster);
      end;
  end;
end;

procedure TAlternitive.restoreExecute(Sender: TObject);
var
  ext: string;
  val: Integer;
begin
  OpenDialog.filter := FILE_FILTER1;

  if cdsMaster.Active and OpenDialog.Execute then
  begin
    ext := LowerCase(ExtractFileExt(OpenDialog.FileName));

    if map.TryGetValue(ext, val) then
      if val < 4 then
      begin
        Tmm.loadBuffer(cdsMaster, cdsBuffer, cdsAudit, OpenDialog.FileName);
        Tmm.restoreTransform(cdsMaster, cdsBuffer, cdsAudit);
      end;
  end;
end;

end.