Data Pump

unit uDataPump;

interface

uses
  Winapi.Windows, Winapi.Messages,
  System.SysUtils, System.Variants, System.Classes,
  Vcl.Graphics, Vcl.Controls, Vcl.Forms, Vcl.Dialogs,
  DB, ADODB, Clipbrd,
  Inno.Dataset, Inno.Common, IniFiles;

type
  TDatabase = class;
  TDatabases = class;
  TDataPump = class;
  TFieldLink = class;
  TFieldLinks = class;

  TFieldLinkType = (flText, flInt, flFloat, flLookup);

  TConnectedEvent = procedure(Sender: TObject; const Connected: Boolean) of object;

  TDatabase = class(TObject)
  private
    FCaption: String;
    FConnectionString: String;
    FActive: Boolean;
    FSchema: String;
    FShortCaption: String;
    procedure SetCaption(const Value: String);
    procedure SetActive(const Value: Boolean);
    procedure SetConnectionString(const Value: String);
    procedure SetSchema(const Value: String);
    procedure SetShortCaption(const Value: String);
  public
    constructor Create;
    destructor Destroy; override;
    property Caption: String read FCaption write SetCaption;
    property ShortCaption: String read FShortCaption write SetShortCaption;
    property ConnectionString: String
      read FConnectionString write SetConnectionString;
    property Schema: String read FSchema write SetSchema;
    property Active: Boolean read FActive write SetActive;
  end;

  TDatabases = class(TPersistent)
  private
    FOwner: TDataPump;
    FItems: TStringList;
    function GetItem(Index: Integer): TDatabase;
    procedure SetItem(Index: Integer; const Value: TDatabase);
  public
    constructor Create(AOwner: TDataPump);
    destructor Destroy; override;
    function Add: TDatabase;
    procedure Delete(const Index: Integer);
    function IndexOf(ADatabase: TDatabase): Integer;
    procedure Clear;
    function Count: Integer;
    property Databases[Index: Integer]: TDatabase
      read GetItem write SetItem; default;
  end;

  TFieldLink = class(TPersistent)
  private
    FFieldName: String;
    FInsertSQL: String;
    FDeleteSQL: String;
    FUpdateSQL: String;
    FSelectSQL: String;
    FIsIdentity: Boolean;
    FPrimaryKey: Boolean;
    FIsReadOnly: Boolean;
    FCanCreate: Boolean;
    FLinkType: TFieldLinkType;
    FLookupTable: String;
    FLookupField: String;
    FLookupValue: String;
    FName: String;
    FAlias: String;
    procedure SetFieldName(const Value: String);
    procedure SetInsertSQL(const Value: String);
    procedure SetDeleteSQL(const Value: String);
    procedure SetSelectSQL(const Value: String);
    procedure SetUpdateSQL(const Value: String);
    procedure SetIsIdentity(const Value: Boolean);
    procedure SetIsReadOnly(const Value: Boolean);
    procedure SetPrimaryKey(const Value: Boolean);
    procedure SetLinkType(const Value: TFieldLinkType);
    procedure SetLookupField(const Value: String);
    procedure SetLookupTable(const Value: String);
    procedure SetLookupValue(const Value: String);
    procedure SetName(const Value: String);
    procedure SetCanCreate(const Value: Boolean);
    procedure SetAlias(const Value: String);
  public
    constructor Create;
    destructor Destroy; override;
  published
    property FieldName: String read FFieldName write SetFieldName;
    property Name: String read FName write SetName;
    property Alias: String read FAlias write SetAlias;
    property LinkType: TFieldLinkType read FLinkType write SetLinkType;
    property InsertSQL: String read FInsertSQL write SetInsertSQL;
    property UpdateSQL: String read FUpdateSQL write SetUpdateSQL;
    property DeleteSQL: String read FDeleteSQL write SetDeleteSQL;
    property SelectSQL: String read FSelectSQL write SetSelectSQL;
    property PrimaryKey: Boolean read FPrimaryKey write SetPrimaryKey;
    property IsIdentity: Boolean read FIsIdentity write SetIsIdentity;
    property IsReadOnly: Boolean read FIsReadOnly write SetIsReadOnly;
    property CanCreate: Boolean read FCanCreate write SetCanCreate;
    property LookupTable: String read FLookupTable write SetLookupTable;
    property LookupField: String read FLookupField write SetLookupField;
    property LookupValue: String read FLookupValue write SetLookupValue;
  end;

  TFieldLinks = class(TPersistent)
  private
    FItems: TList;
    FJoins: TStringList;
    FMatch: TStringList;
    FSourceTable: String;
    FSourceID: String;
    FSourceCaption: String;
    FSourceAlias: String;
    function GetItem(Index: Integer): TFieldLink;
    procedure SetItem(Index: Integer; const Value: TFieldLink);
    function GetJoins: TStrings;
    procedure SetJoins(const Value: TStrings);
    procedure SetSourceTable(const Value: String);
    procedure SetSourceCaption(const Value: String);
    procedure SetSourceID(const Value: String);
    function GetMatch: TStrings;
    procedure SetMatch(const Value: TStrings);
    procedure SetSourceAlias(const Value: String);
  public
    constructor Create;
    destructor Destroy; override;
    function Add: TFieldLink; overload;
    function Add(const FieldName: String): TFieldLink; overload;
    procedure Delete(const Index: Integer);
    procedure Clear;
    function Count: Integer;
    property Items[Index: Integer]: TFieldLink
      read GetItem write SetItem; default;
    procedure LoadFromFile(const Filename: String);
    procedure SaveToFile(const Filename: String);
    property Joins: TStrings read GetJoins write SetJoins;
    property Match: TStrings read GetMatch write SetMatch;
    property SourceTable: String read FSourceTable write SetSourceTable;
    property SourceID: String read FSourceID write SetSourceID;
    property SourceCaption: String read FSourceCaption write SetSourceCaption;
    property SourceAlias: String read FSourceAlias write SetSourceAlias;
    function FieldByName(const Name: String): TFieldLink;
  end;

  TDataPump = class(TPersistent)
  private
    FDatabases: TDatabases;
    FSourceDB: TADOConnection;
    FDestDB: TADOConnection;
    FDestIndex: Integer;
    FSourceIndex: Integer;
    FSourceLinks: TFieldLinks;
    FDestLinks: TFieldLinks;
    FSourceData: TInnoDataset;
    FDestData: TInnoDataset;
    FActive: Boolean;
    FTempQry: TADOQuery;
    FWhereSQL: TStringList;
    FOnConnect: TConnectedEvent;
    function ImportField(ASource: TFieldLink; const Value: String): Integer;
    procedure DBConnected(Sender: TObject);
    procedure DBDisconnected(Sender: TObject);
    procedure DoOnConnect(const Connected: Boolean);
    procedure SetDestIndex(const Value: Integer);
    procedure SetSourceIndex(const Value: Integer);
    procedure SetActive(const Value: Boolean);
    function GetWhereSQL: TStrings;
    procedure SetWhereSQL(const Value: TStrings);
    function ReplaceSQL(AFieldLinks: TFieldLinks; ADataset: TInnoDataset;
      const SQL: String): String;
  public
    constructor Create;
    destructor Destroy; override;
    function Filename: String;
    procedure LoadFromFile;
    procedure SaveToFile;
    function SourceDB: TADOConnection;
    function SourceConn: TDatabase;
    function SourceNewQuery: TADOQuery;
    function DestDB: TADOConnection;
    function DestConn: TDatabase;
    function DestNewQuery: TADOQuery;
    function SchemaDir: String;
    procedure PushData;
    function LookupID(ALink: TFieldLink; const Value: String): Integer;
    function GetSourceSQL: String;
  published
    property Active: Boolean read FActive write SetActive;
    property Databases: TDatabases read FDatabases;
    property SourceIndex: Integer read FSourceIndex write SetSourceIndex;
    property DestIndex: Integer read FDestIndex write SetDestIndex;
    property WhereSQL: TStrings read GetWhereSQL write SetWhereSQL;
    property OnConnect: TConnectedEvent read FOnConnect write FOnConnect;
  end;

implementation

{ TDatabases }

constructor TDatabases.Create(AOwner: TDataPump);
begin
  FOwner:= AOwner;
  FItems:= TStringList.Create;
end;

destructor TDatabases.Destroy;
begin
  Clear;
  FItems.Free;
  inherited;
end;

function TDatabases.Add: TDatabase;
begin
  Result:= TDatabase.Create;
  FItems.AddObject('', Result);
end;

procedure TDatabases.Clear;
begin
  while Count > 0 do
    Delete(0);
end;

function TDatabases.Count: Integer;
begin
  Result:= FItems.Count;
end;

procedure TDatabases.Delete(const Index: Integer);
begin
  TDatabase(FItems.Objects[Index]).Free;
  FItems.Delete(Index);
end;

function TDatabases.GetItem(Index: Integer): TDatabase;
begin
  Result:= TDatabase(FItems.Objects[Index]);
end;

function TDatabases.IndexOf(ADatabase: TDatabase): Integer;
begin
  Result:= FItems.IndexOfObject(ADatabase);
end;

procedure TDatabases.SetItem(Index: Integer; const Value: TDatabase);
begin
  FItems.Objects[Index]:= Value;
end;

{ TDatabase }

constructor TDatabase.Create;
begin

end;

destructor TDatabase.Destroy;
begin

  inherited;
end;

procedure TDatabase.SetActive(const Value: Boolean);
begin
  FActive := Value;
end;

procedure TDatabase.SetCaption(const Value: String);
begin
  FCaption := Value;
end;

procedure TDatabase.SetConnectionString(const Value: String);
begin
  FConnectionString := Value;
end;

procedure TDatabase.SetSchema(const Value: String);
begin
  FSchema := Value;
end;

procedure TDatabase.SetShortCaption(const Value: String);
begin
  FShortCaption := Value;
end;

{ TFieldLink }

constructor TFieldLink.Create;
begin

end;

destructor TFieldLink.Destroy;
begin

  inherited;
end;

procedure TFieldLink.SetAlias(const Value: String);
begin
  FAlias := Value;
end;

procedure TFieldLink.SetCanCreate(const Value: Boolean);
begin
  FCanCreate := Value;
end;

procedure TFieldLink.SetDeleteSQL(const Value: String);
begin
  FDeleteSQL := Value;
end;

procedure TFieldLink.SetFieldName(const Value: String);
begin
  FFieldName := Value;
end;

procedure TFieldLink.SetInsertSQL(const Value: String);
begin
  FInsertSQL := Value;
end;

procedure TFieldLink.SetIsIdentity(const Value: Boolean);
begin
  FIsIdentity := Value;
end;

procedure TFieldLink.SetIsReadOnly(const Value: Boolean);
begin
  FIsReadOnly := Value;
end;

procedure TFieldLink.SetLinkType(const Value: TFieldLinkType);
begin
  FLinkType := Value;
end;

procedure TFieldLink.SetLookupField(const Value: String);
begin
  FLookupField := Value;
end;

procedure TFieldLink.SetLookupTable(const Value: String);
begin
  FLookupTable := Value;
end;

procedure TFieldLink.SetLookupValue(const Value: String);
begin
  FLookupValue := Value;
end;

procedure TFieldLink.SetName(const Value: String);
begin
  FName := Value;
end;

procedure TFieldLink.SetPrimaryKey(const Value: Boolean);
begin
  FPrimaryKey := Value;
end;

procedure TFieldLink.SetSelectSQL(const Value: String);
begin
  FSelectSQL := Value;
end;

procedure TFieldLink.SetUpdateSQL(const Value: String);
begin
  FUpdateSQL := Value;
end;

{ TFieldLinks }

constructor TFieldLinks.Create;
begin
  FItems:= TList.Create;
  FJoins:= TStringList.Create;
  FMatch:= TStringList.Create;
end;

destructor TFieldLinks.Destroy;
begin
  Clear;
  FMatch.Free;
  FJoins.Free;
  FItems.Free;
  inherited;
end;

function TFieldLinks.FieldByName(const Name: String): TFieldLink;
var
  X: Integer;
  L: TFieldLink;
begin
  Result:= nil;
  for X := 0 to FItems.Count - 1 do begin
    L:= TFieldLink(FItems[X]);
    if UpperCase(L.FName) = UpperCase(Name) then begin
      Result:= L;
      Break;
    end;
  end;
end;

function TFieldLinks.Add: TFieldLink;
begin
  Result:= TFieldLink.Create;
  FItems.Add(Result);
end;

function TFieldLinks.Add(const FieldName: String): TFieldLink;
begin
  Result:= Add;
  Result.FieldName:= FieldName;
end;

procedure TFieldLinks.Clear;
begin
  while Count > 0 do
    Delete(0);
  FJoins.Clear;
  FMatch.Clear;
end;

function TFieldLinks.Count: Integer;
begin
  Result:= FItems.Count;
end;

procedure TFieldLinks.Delete(const Index: Integer);
begin
  TFieldLink(FItems[Index]).Free;
  FItems.Delete(Index);
end;

function TFieldLinks.GetItem(Index: Integer): TFieldLink;
begin
  Result:= TFieldLink(FItems[Index]);
end;

procedure TFieldLinks.SetItem(Index: Integer; const Value: TFieldLink);
begin
  FItems[Index]:= Value;
end;

function TFieldLinks.GetJoins: TStrings;
begin
  Result:= TStrings(FJoins);
end;

function TFieldLinks.GetMatch: TStrings;
begin
  Result:= TStrings(FMatch);
end;

procedure TFieldLinks.SetJoins(const Value: TStrings);
begin
  FJoins.Assign(Value);
end;

procedure TFieldLinks.SetMatch(const Value: TStrings);
begin
  FMatch.Assign(Value);
end;

procedure TFieldLinks.SetSourceAlias(const Value: String);
begin
  FSourceAlias := Value;
end;

procedure TFieldLinks.SetSourceCaption(const Value: String);
begin
  FSourceCaption := Value;
end;

procedure TFieldLinks.SetSourceID(const Value: String);
begin
  FSourceID := Value;
end;

procedure TFieldLinks.SetSourceTable(const Value: String);
begin
  FSourceTable := Value;
end;

procedure TFieldLinks.LoadFromFile(const Filename: String);
var
  I: TIniFile;
  L: TStringList;
  FL: TFieldLink;
  X: Integer;
  T: String;
begin
  Clear;
  if FileExists(Filename) then begin
    I:= TIniFile.Create(Filename);
    try
      L:= TStringList.Create;
      try
        FSourceTable:= I.ReadString('table', 'tbl', '');
        FSourceID:= I.ReadString('table', 'id', '');
        FSourceCaption:= I.ReadString('table', 'caption', '');
        FSourceAlias:= I.ReadString('table', 'alias', 'Source');
        I.ReadSection('joins', L);
        for X := 0 to L.Count - 1 do
          FJoins.Append(I.ReadString('joins', L[X], ''));
        I.ReadSection('match', L);
        for X := 0 to L.Count - 1 do
          FMatch.Append(I.ReadString('match', L[X], ''));
        I.ReadSections(L);
        for X := 0 to L.Count - 1 do begin
          if StrToIntDef(L[X], -1) >= 0 then begin
            FL:= Add;
            FL.FieldName:= I.ReadString(L[X], 'fld', '');
            FL.Name:= I.ReadString(L[X], 'nam', FL.FieldName);
            FL.Alias:= I.ReadString(L[X], 'alias', '');
            FL.InsertSQL:= I.ReadString(L[X], 'ins', '');
            FL.UpdateSQL:= I.ReadString(L[X], 'upd', '');
            FL.DeleteSQL:= I.ReadString(L[X], 'del', '');
            FL.SelectSQL:= I.ReadString(L[X], 'sel', '');
            FL.FPrimaryKey:= UpperCase(I.ReadString(L[X], 'prk', '')) = 'Y';
            FL.FIsIdentity:= UpperCase(I.ReadString(L[X], 'idt', '')) = 'Y';
            FL.FIsReadOnly:= UpperCase(I.ReadString(L[X], 'rdo', '')) = 'Y';
            FL.FCanCreate:= UpperCase(I.ReadString(L[X], 'new', '')) = 'Y';
            FL.LookupTable:= I.ReadString(L[X], 'tbl', '');
            FL.LookupField:= I.ReadString(L[X], 'lnk', '');
            FL.LookupValue:= I.ReadString(L[X], 'val', '');
            T:= LowerCase(I.ReadString(L[X], 'typ', 'text'));
            if T = 'text' then
              FL.FLinkType:= flText
            else if T = 'int' then
              FL.FLinkType:= flInt
            else if T = 'float' then
              FL.FLinkType:= flFloat
            else if T = 'lookup' then
              FL.FLinkType:= flLookup
            else FL.FLinkType:= flText;
          end;
        end;
      finally
        L.Free;
      end;
    finally
      I.Free;
    end;
  end else begin
    raise Exception.Create('Schema file does not exist "'+Filename+'"');
  end;
end;

procedure TFieldLinks.SaveToFile(const Filename: String);
begin

end;

{ TDataPump }

constructor TDataPump.Create;
begin
  FDatabases:= TDatabases.Create(Self);
  FSourceLinks:= TFieldLinks.Create;
  FDestLinks:= TFieldLinks.Create;
  FWhereSQL:= TStringList.Create;
  FSourceDB:= TADOConnection.Create(nil);
  FDestDB:= TADOConnection.Create(nil);
  FSourceDB.LoginPrompt:= False;
  FDestDB.LoginPrompt:= False;
  FSourceData:= TInnoDataset.Create(nil);
  FDestData:= TInnoDataset.Create(nil);
  FSourceIndex:= -1;
  FDestIndex:= -1;
  FSourceDB.AfterConnect:= DBConnected;
  FDestDB.AfterConnect:= DBConnected;
  FSourceDB.AfterDisconnect:= DBDisconnected;
  FDestDB.AfterDisconnect:= DBDisconnected;
  FTempQry:= DestNewQuery;
end;

destructor TDataPump.Destroy;
begin
  FTempQry.Free;
  FSourceData.Free;
  FDestData.Free;
  FSourceDB.Free;
  FDestDB.Free;
  FSourceLinks.Free;
  FDestLinks.Free;
  FDatabases.Free;
  FWhereSQL.Free;
  inherited;
end;

function TDataPump.GetSourceSQL: String;
var
  X: Integer;
  L: TFieldLink;
  J: TStringList;
  AL: String;
  S: String;
  T: String;
  procedure A(const Text: String);
  begin
    Result:= Result + Text + sLineBreak;
  end;
begin
  J:= TStringList.Create;
  try
    A('select');
    for X := 0 to FSourceLinks.Count - 1 do begin
      L:= FSourceLinks[X];
      case L.FLinkType of
        flText, flInt, flFloat: begin
          S:= FSourceLinks.FSourceAlias+'.'+L.FFieldName+' as '''+L.FName+'''';
        end;
        flLookup: begin
          if L.FAlias <> '' then
            AL:= L.FAlias
          else
            AL:= 'Link'+IntToStr(X);
          S:= AL+'.'+L.FLookupValue+' as '''+L.FName+'''';
          T:= 'join '+L.FLookupTable+' '+AL+' on '+
            AL+'.'+L.LookupField + ' = ';
          if Pos('.', L.FFieldName) < 1 then
            T:= T+FSourceLinks.FSourceAlias+'.';
          T:= T+L.FFieldName;
          J.Append(T);
        end;
      end;
      if X > 0 then S:= ', '+S;
      A(S);
    end;
    A('from '+FSourceLinks.FSourceTable+' '+FSourceLinks.FSourceAlias);
    for X := 0 to FSourceLinks.FJoins.Count - 1 do
      A(FSourceLinks.FJoins[X]);
    for X := 0 to J.Count - 1 do
      A(J[X]);
    if FWhereSQL.Text <> '' then begin
      A('where');
      for X := 0 to FWhereSQL.Count - 1 do
        A(FWhereSQL[X]);
    end;
  finally
    J.Free;
  end;
end;

function TDataPump.GetWhereSQL: TStrings;
begin
  Result:= TStrings(FWhereSQL);
end;

procedure TDataPump.LoadFromFile;
var
  I: TIniFile;
  L: TStringList;
  X: Integer;
  S: String;
  R: TDatabase;
begin
  FDatabases.Clear;
  if FileExists(Filename) then begin
    I:= TIniFile.Create(Filename);
    try
      L:= TStringList.Create;
      try
        I.ReadSections(L);
        L.Sorted:= True;
        L.Sort;
        for X := 0 to L.Count - 1 do begin
          S:= UpperCase(L[X]);
          if Pos('CONN:', S) = 1 then begin
            R:= FDatabases.Add;
            R.Caption:= I.ReadString(S, 'Caption', '[NO NAME]');
            R.ShortCaption:= I.ReadString(S, 'ShortCaption', '');
            R.ConnectionString:=
              I.ReadString(S, 'ConnectionString', '');
            R.Schema:= I.ReadString(S, 'Schema', '');
            R.Active:= I.ReadBool(S, 'Active', True);
          end;
        end;
      finally
        L.Free;
      end;
    finally
      I.Free;
    end;
  end else begin
    I:= TIniFile.Create(Filename);
    try
      I.WriteString('Settings', 'LastUsed', DateTimeToStr(Now));
      I.UpdateFile;
    finally
      I.Free;
    end;
  end;
end;

procedure TDataPump.SaveToFile;
var
  I: TIniFile;
  X: Integer;
  D: TDatabase;
  L: TStringList;
begin
  ForceDirectories(ExtractFilePath(Filename));
  if DirectoryExists(ExtractFilePath(Filename)) then begin
    I:= TIniFile.Create(Filename);
    try
      L:= TStringList.Create;
      try
        I.ReadSections(L);
        for X := 0 to L.Count - 1 do
          I.EraseSection(L[X]);
        for X := 0 to FDatabases.Count - 1 do begin
          D:= FDatabases[X];
          I.WriteString('Conn:'+IntToStr(X), 'Caption', D.Caption);
          I.WriteString('Conn:'+IntToStr(X), 'ShortCaption', D.ShortCaption);
          I.WriteString('Conn:'+IntToStr(X), 'ConnectionString', D.ConnectionString);
          I.WriteString('Conn:'+IntToStr(X), 'Schema', D.Schema);
          I.WriteBool('Conn:'+IntToStr(X), 'Active', D.Active);
        end;
      finally
        L.Free;
      end;
    finally
      I.Free;
    end;
  end else begin
    raise Exception.Create('Could not create file "'+Filename+'"');
  end;
end;

function TDataPump.LookupID(ALink: TFieldLink; const Value: String): Integer;
var
  Q: TADOQuery;
  S: String;
begin
  Result:= 0;
  Q:= DestNewQuery;
  try
    S:= Trim(ALink.FSelectSQL);
    if S = '' then begin


    end;


    Q.SQL.Text:= S;

  finally
    Q.Free;
  end;
end;

function TDataPump.ImportField(ASource: TFieldLink; const Value: String): Integer;
var
  Dst: TFieldLink;
  X: Integer;
  SQL: String;
  ID: Integer;
  procedure A(const S: String);
  begin
    SQL:= SQL + S + sLineBreak;
  end;
  function ExecSQL(const S: String): Integer;
  begin
    FTempQry.SQL.Text:= S;
    Result:= FTempQry.ExecSQL;
  end;
begin
  Result:= 0;
  FTempQry.Connection:= FDestDB;
  for X := 0 to FDestLinks.Count - 1 do begin
    if LowerCase(FDestLinks[X].FName) = LowerCase(ASource.FName) then begin
      Dst:= FDestLinks[X];
      case Dst.LinkType of
        flText: begin
          //Update with quotes
          SQL:= Trim(Dst.FUpdateSQL);
          if SQL = '' then begin
            SQL:= 'update '+FSourceLinks.FSourceTable+' set '+Dst.FFieldName+' = '''+Value+''' where';
          end;
          ExecSQL(SQL);
        end;
        flInt, flFloat: begin
          //Update without quotes
          SQL:= Trim(Dst.FUpdateSQL);
          if SQL = '' then begin
            SQL:= 'update '+FSourceLinks.FSourceTable+' set '+Dst.FFieldName+' = '+Value+'';
          end;
          ExecSQL(SQL);
        end;
        flLookup: begin
          //Update with quotes in linked lookup table
          SQL:= ''; // Trim(Dst.FInsertSQL);
          if SQL = '' then begin
            A('select');
            A(ASource.FLookupField+' as ID,');
            A(ASource.FLookupValue+' as Value');
            A('from '+ASource.FLookupTable);
            A('where '+ASource.FLookupValue+' = '''+Value+'''');
            FTempQry.Open;
            if not FTempQry.IsEmpty then begin
              FTempQry.First;
              ID:= FTempQry.FieldByName('ID').AsInteger;
            end else begin
              //NOTE: How to handle if Identity is not enabled?
              FTempQry.Append;
              FTempQry.FieldByName(ASource.FLookupValue).AsString:= Value;
              FTempQry.Post;
              ID:= FTempQry.FieldByName(ASource.FLookupField).AsInteger;
            end;
            FTempQry.Close;
            Result:= ID;
          end else begin
            SQL:= StringReplace(SQL, '<value>', Value, [rfReplaceAll,rfIgnoreCase]);
            ExecSQL(SQL);
          end;
        end;
      end;
      Break;
    end;
  end;
end;

//Replaces markup tags in a SQL statement with corresponding data
function TDataPump.ReplaceSQL(AFieldLinks: TFieldLinks; ADataset: TInnoDataset;
   const SQL: String): String;
var
  X: Integer;
  L: TFieldLink;
begin
  Result:= SQL;
  for X := 0 to AFieldLinks.Count - 1 do begin
    L:= AFieldLinks[X];
    Result:= StringReplace(Result, '<Source.'+L.Name+'>',
      ADataset.FieldByName[L.FName].AsString,
      [rfReplaceAll, rfIgnoreCase]);
  end;
end;

procedure TDataPump.PushData;
var
  Qry: TADOQuery;
  Src: TInnoDataset;
  X, Y: Integer; //Iterators
  //S: String;
  C: TInnoDatasetCol;
  SL, DL: TFieldLink;
  ID: Integer;
  T: String; //Temp
  SQL: String;
begin
  Src:= FSourceData;
  Src.Clear;
  Qry:= SourceNewQuery;
  try
    Qry.SQL.Text:= GetSourceSQL;
    //Clipboard.AsText:= Qry.SQL.Text;
    Qry.Open;
    Src.LoadFromQuery(Qry);
    Qry.Close;
  finally
    Qry.Free;
  end;
  Src.First;

  Qry:= DestNewQuery;
  try
    while not Src.EOF do begin
      //Check for matching record, append if not
      SQL:= FSourceLinks.Match.Text;
      SQL:= ReplaceSQL(FSourceLinks, Src, SQL);
      Qry.SQL.Text:= SQL;
      Qry.Open;
      if Qry.IsEmpty then begin
        Qry.Append;
        Qry[FSourceLinks.FSourceCaption]:= '';
        Qry.Post;
      end;
      ID:= Qry.FieldByName('ID').AsInteger;
      Qry.Close;
      //Populate fields
      for X := 0 to Src.FieldCount - 1 do begin
        C:= Src.Cols[X];
        SL:= FSourceLinks.FieldByName(C.FieldName);
        if Assigned(SL) then begin
          //ID:= ImportField(L, Src.FieldByIndex[X].AsString);
        end;
      end;
      Src.Next;
    end;
  finally
    Qry.Free;
  end;
end;

procedure TDataPump.SetActive(const Value: Boolean);
begin
  if Value then begin
    if not FActive then begin
      if (FSourceIndex >= 0) and (FDestIndex >= 0) and
        (FSourceIndex < FDatabases.Count) and
        (FDestIndex < FDatabases.Count) then
      begin
        FActive:= True;
        try
          FSourceLinks.LoadFromFile(SchemaDir+SourceConn.Schema);
          FDestLinks.LoadFromFile(SchemaDir+DestConn.Schema);
          FSourceDB.ConnectionString:= SourceConn.ConnectionString;
          FDestDB.ConnectionString:= DestConn.ConnectionString;
          FSourceDB.Connected:= True;
          FDestDB.Connected:= True;
        finally

        end;
      end else begin
        raise Exception.Create('Database Index out of bounds');
      end;
    end;
  end else begin
    if FActive then begin
      try
        FSourceDB.Connected:= False;
        FDestDB.Connected:= False;
      finally
        FActive:= False;
      end;
    end;
  end;
end;

procedure TDataPump.DoOnConnect(const Connected: Boolean);
begin
  if assigned(FOnConnect) then
    FOnConnect(Self, Connected);
end;

procedure TDataPump.DBConnected(Sender: TObject);
begin
  if (FSourceDB.Connected) and (FDestDB.Connected) then
    DoOnConnect(True);
end;

procedure TDataPump.DBDisconnected(Sender: TObject);
begin
  if not (FSourceDB.Connected or FDestDB.Connected) then
    DoOnConnect(False);
  FSourceDB.Connected:= False;
  FDestDB.Connected:= False;
end;

function TDataPump.SourceNewQuery: TADOQuery;
begin
  Result:= TADOQuery.Create(nil);
  Result.Connection:= FSourceDB;
end;

function TDataPump.DestNewQuery: TADOQuery;
begin
  Result:= TADOQuery.Create(nil);
  Result.Connection:= FDestDB;
end;

function TDataPump.Filename: String;
begin
  Result:= ChangeFileExt(Application.ExeName, '.ini');
end;

function TDataPump.SchemaDir: String;
begin
  Result:= IncludeTrailingPathDelimiter(ExtractFilePath(Application.ExeName))+'Schemas\';
end;

function TDataPump.SourceConn: TDatabase;
begin
  Result:= FDatabases[FSourceIndex];
end;

function TDataPump.DestConn: TDatabase;
begin
  Result:= FDatabases[FDestIndex];
end;

function TDataPump.SourceDB: TADOConnection;
begin
  Result:= FSourceDB;
end;

function TDataPump.DestDB: TADOConnection;
begin
  Result:= FDestDB;
end;

procedure TDataPump.SetDestIndex(const Value: Integer);
begin
  FDestIndex := Value;
end;

procedure TDataPump.SetSourceIndex(const Value: Integer);
begin
  FSourceIndex := Value;
end;

procedure TDataPump.SetWhereSQL(const Value: TStrings);
begin
  FWhereSQL.Assign(Value);
end;

end.