SQL VeriTabanı Kopyalama Hk.

[glagher]

(09-09-2023, Saat: 21:58)erdogan Adlı Kullanıcıdan Alıntı:

interface

uses
  Winapi.Windows,
  Winapi.Messages,
  System.SysUtils,
  System.Variants,
  System.Classes,
  Vcl.Graphics,
  Vcl.Controls,
  Vcl.Forms,
  Vcl.Dialogs,
  Vcl.StdCtrls,
  Data.DB,
  Vcl.Grids,
  Vcl.DBGrids,
  uCheckDataChangeAndSyncThread,
  DBAccess,
  Uni,
  SQLServerUniProvider,
  UniProvider,
  ODBCUniProvider,
  AccessUniProvider;

type
  TForm3 = class(TForm)
    Button1: TButton;
    SourceDB: TUniConnection;
    TargetDB: TUniConnection;
    AccessUniProvider1: TAccessUniProvider;
    SQLServerUniProvider1: TSQLServerUniProvider;
    procedure Button1Click(Sender: TObject);
    procedure FormCloseQuery(Sender: TObject; var CanClose: Boolean);
  private
    { Private declarations }
    FDataCheckThread: TMonitorDataChangeThread;
    FDataCheckThreadTerminated: Boolean;
    procedure OnTerminate(Sender: TObject);
  public
    { Public declarations }
  end;

var
  Form3: TForm3;

implementation

{$R *.dfm}

uses
  System.IOUtils;

procedure TForm3.Button1Click(Sender: TObject);
begin
  // Hafızada Thread iki defa oluşturulmasın
  if Assigned(FDataCheckThread) then
    Exit();

  FDataCheckThread := TMonitorDataChangeThread.Create();
  FDataCheckThread.SourceDatabaseConnectString := SourceDB.ConnectString;
  FDataCheckThread.TargetDatabaseConnectString := TargetDB.ConnectString;
  FDataCheckThread.SourceTablename := 'TARTIMLAR';
  FDataCheckThread.TargetTablename := 'tartimlar';
  FDataCheckThread.AdditionalFieldsToFilter := ['SUBE'];   // SUBE ALANINI FİLTRELİYOR
  FDataCheckThread.AdditionalValuesToFilter := ['ŞUBE A']; // ŞUBE FİLTRELENİYOR
  FDataCheckThread.SourceDateTimeFieldname := 'KAYIT_ZAMANI';
  FDataCheckThread.TargetDateTimeFieldname := 'kayit_zamani';
  FDataCheckThread.OldDaysToIncludeInCheck := 3; // Son tarihi tespit ettikten sonra 3 gün geriye git. Bu arada değişen kayıtları güncelle
  FDataCheckThread.CheckFrequency := 10; // 10 saniyede bir kontrol et
  FDataCheckThread.LogDirectory := TPath.Combine(TPath.GetLibraryPath(), 'SyncLog');
  FDataCheckThread.LogFilename := 'yyyy-mm-dd".log"';
  FDataCheckThread.LoggingActive := True;
  FDataCheckThread.OnTerminate := Self.OnTerminate;
  FDataCheckThreadTerminated := False;
  FDataCheckThread.Start();
end;

procedure TForm3.FormCloseQuery(Sender: TObject; var CanClose: Boolean);
begin
  if Assigned(FDataCheckThread) then
  begin
    // Active is declared specific for this check in here
    if FDataCheckThread.Active then
    begin
      FDataCheckThread.Terminate();
      while not FDataCheckThreadTerminated do
      begin
        Application.ProcessMessages();
        Sleep(100);
      end;
    end;
  end;
end;

procedure TForm3.OnTerminate(Sender: TObject);
begin
  FDataCheckThreadTerminated := True;
end;

end.

unit uCheckDataChangeAndSyncThread;



interface

uses
  System.SysUtils,
  System.Classes,
  UniProvider,
  ODBCUniProvider,
  AccessUniProvider,
  SQLServerUniProvider,
  Data.DB,
  Uni,
  CRBatchMove,
  uUtils.LogClass;

type
  TMonitorDataChangeThread = class(TThread)
  private
    FSourceDatabaseConnectString: string;
    FTargetDatabaseConnectString: string;
    FSourceTableName: string;
    FTargetTableName: string;
    FAdditionalFieldsToFilter: TArray<string>;
    FAdditionalValuesToFilter: TArray<string>;
    FSourceDB: TUniConnection;
    FTargetDB: TUniConnection;
    FBatchMove: TCRBatchMove;
    FSourceQuery: TUniQuery;
    FTargetQuery: TUniQuery;
    FMetaDataIndexName: TUniMetaData;
    FMetaDataColumnName: TUniMetaData;
    FSourceDateTimeFieldname: string;
    FTargetDateTimeFieldname: string;
    FTargetLastRecordValue: string;
    FOldDaysToIncludeInCheck: Word;
    FComInitialized: Boolean;
    FLastDate: TDateTime;
    FActive: Boolean;
    FCheckFrequency: Word;
    FLog: TLog;
    FLogDir: string;
    FLogFilename: string;
    FLoggingActive: Boolean;
    FFormatSettings: TFormatSettings;
    procedure SetLogDir(const Value: string);
    procedure SetLogFilename(const Value: string);
    procedure SetLoggingActive(const Value: Boolean);
    procedure DoDBConnection(DB: TUniConnection; const ConnectionString: string);
    function GetPrimaryKeyFieldname(DB: TUniConnection; const Tablename: string; out FieldName: string): Boolean;
    function GetDateTimeFormatted(const ProviderName: string; const ADate: TDateTime): string;
    function PrepareSourceQuery(): Boolean;
    function PrepareTargetQuery(): Boolean;
    function GetTargetLastDate(): Boolean;
    function DeleteMissingFromTarget(): Boolean;
  protected
    procedure Execute(); override;
  public
    constructor Create(); reintroduce; overload;
    destructor Destroy(); override;
    procedure DoTerminate(); reintroduce;

    property SourceDatabaseConnectString: string read FSourceDatabaseConnectString write FSourceDatabaseConnectString;
    property TargetDatabaseConnectString: string read FTargetDatabaseConnectString write FTargetDatabaseConnectString;
    property SourceTablename: string read FSourceTableName write FSourceTableName;
    property TargetTablename: string read FTargetTableName write FTargetTableName;
    property AdditionalFieldsToFilter: TArray<string> read FAdditionalFieldsToFilter write FAdditionalFieldsToFilter;
    property AdditionalValuesToFilter: TArray<string> read FAdditionalValuesToFilter write FAdditionalValuesToFilter;
    property SourceDateTimeFieldname: string read FSourceDateTimeFieldname write FSourceDateTimeFieldname;
    property TargetDateTimeFieldname: string read FTargetDateTimeFieldname write FTargetDateTimeFieldname;
    property OldDaysToIncludeInCheck: Word read FOldDaysToIncludeInCheck write FOldDaysToIncludeInCheck;
    property CheckFrequency: Word read FCheckFrequency write FCheckFrequency;
    property LogDirectory: string read FLogDir write SetLogDir;
    property LogFilename: string read FLogFilename write SetLogFilename;
    property LoggingActive: Boolean read FLoggingActive write SetLoggingActive;
    property Active: Boolean read FActive;
  end;


implementation


uses
  ActiveX,
  System.Diagnostics,
  DateUtils;


//==============================================================================
{ TMonitorDataChangeThread }
//==============================================================================
//------------------------------------------------------------------------------
constructor TMonitorDataChangeThread.Create();
begin
  inherited Create(True);
  FreeOnTerminate := True;

  FLog := TLog.Create(nil);
  FSourceDB := TUniConnection.Create(nil);
  FTargetDB := TUniConnection.Create(nil);
  FSourceQuery := TUniQuery.Create(nil);
  FTargetQuery := TUniQuery.Create(nil);
  FMetaDataIndexName := TUniMetaData.Create(nil);
  FMetaDataColumnName := TUniMetaData.Create(nil);
  FBatchMove := TCRBatchMove.Create(nil);
  FBatchMove.FieldMappingMode := mmFieldName;
  FBatchMove.Mode := bmAppendUpdate;

  FSourceQuery.Connection := FSourceDB;
  FTargetQuery.Connection := FTargetDB;
  FBatchMove.Source := FSourceQuery;
  FBatchMove.Destination := FTargetQuery;

  FActive := False;
  FCheckFrequency := 10;
  FLogDir := ExtractFileDir(ParamStr(0)) + PathDelim + 'SyncLog';
  SetLogFilename('yyyy-mm-dd".log"');
  SetLoggingActive(True);

  FFormatSettings := TFormatSettings.Create('en-US');

  FSourceTableName := EmptyStr;
  FTargetTableName := EmptyStr;
  FTargetLastRecordValue := EmptyStr;
  FSourceDateTimeFieldname := EmptyStr;
  FTargetDateTimeFieldname := EmptyStr;
  FOldDaysToIncludeInCheck := 0;
end;


//------------------------------------------------------------------------------
destructor TMonitorDataChangeThread.Destroy();
begin
  FLog.Free();
  FMetaDataIndexName.Free();
  FMetaDataColumnName.Free();
  FBatchMove.Free();
  FTargetQuery.Free();
  FSourceQuery.Free();
  FSourceDB.Free();
  FTargetDB.Free();

  inherited;
end;


//------------------------------------------------------------------------------
procedure TMonitorDataChangeThread.DoDBConnection(DB: TUniConnection; const ConnectionString: string);
begin
  DB.ConnectString := ConnectionString;
  try
    DB.Open();
  except
    on E: EUniError do
    begin
      FLog.Log('DoDBConnection(): ' + E.Message);
    end;
  end;
end;


//------------------------------------------------------------------------------
function TMonitorDataChangeThread.GetPrimaryKeyFieldname(DB: TUniConnection; const Tablename: string; out FieldName: string): Boolean;
var
  IndexName: string;
begin
  FMetaDataIndexName.Close();
  FMetaDataIndexName.Connection := DB;
  FMetaDataIndexName.MetaDataKind := 'Indexes';
  FMetaDataIndexName.Restrictions.AddPair('TABLE_NAME', Tablename);
  FMetaDataIndexName.Filter := 'UNIQUE=1';
  try
    FMetaDataIndexName.Open();
    FMetaDataIndexName.Filtered := True;
    case FMetaDataIndexName.RecordCount of
      0:
      begin
        FLog.Log('GetPrimaryKeyFieldname(): Cannot identify index name');
        Exit(False);
      end;
      1:
      begin
        IndexName := FMetaDataIndexName.FieldByName('index_name').AsString;
        FMetaDataIndexName.Close();
      end;

      else
      begin
        FLog.Log('GetPrimaryKeyFieldname(): There are more than one unique index in table "%s". We cannot identify primary key');
        Exit(False);
      end;
    end;
  except
    on E: EUniError do
    begin
      FLog.Log('GetPrimaryKeyFieldname()-1: ' + E.Message);
      Exit(False);
    end;
  end;

  FMetaDataColumnName.Close();
  FMetaDataColumnName.Connection := DB;
  FMetaDataColumnName.MetaDataKind := 'IndexColumns';
  FMetaDataColumnName.Restrictions.AddPair('TABLE_NAME', Tablename);
  FMetaDataColumnName.Filter := 'index_name=' + QuotedStr(IndexName);
  try
    FMetaDataColumnName.Open();
    FMetaDataColumnName.Filtered := True;
    case FMetaDataColumnName.RecordCount of
      0:
      begin
        FMetaDataColumnName.Close();
        FLog.Log('GetPrimaryKeyFieldname(): Cannot identify column name');
        Exit(False);
      end;

      else
      begin
        FMetaDataColumnName.First();
        FieldName := EmptyStr;
        while not FMetaDataColumnName.Eof do
        begin
          FieldName := FieldName + FMetaDataColumnName.FieldByName('column_name').AsString + ';';
          FMetaDataColumnName.Next();
        end;
        FMetaDataColumnName.Close();
        if FieldName.Length > 0 then
          SetLength(FieldName, FieldName.Length-1);
        Exit(True);
      end;
    end;
  except
    on E: EUniError do
    begin
      FLog.Log('GetPrimaryKeyFieldname()-2: ' + E.Message);
      Exit(False);
    end;
  end;
end;


//------------------------------------------------------------------------------
function TMonitorDataChangeThread.GetDateTimeFormatted(const ProviderName: string; const ADate: TDateTime): string;
begin
  if SameText(ProviderName, 'Access') then
    Exit(FormatDateTime('"#"mm/dd/yyyy hh:nn:ss"#"', FLastDate, FFormatSettings))
  else
  if SameText(ProviderName, 'SQL Server') then
    Exit(FormatDateTime('yyyy-mm-dd hh:nn:ss', FLastDate, FFormatSettings));
end;


//------------------------------------------------------------------------------
function TMonitorDataChangeThread.GetTargetLastDate(): Boolean;
var
  QueryText: string;
  I: Integer;
begin
  QueryText := 'select max(' + AnsiQuotedStr(FTargetDateTimeFieldname, '"') + ') from ' + AnsiQuotedStr(FTargetTableName, '"');
  if Length(FAdditionalFieldsToFilter) > 0 then
  begin
    QueryText := QueryText + ' where ';
    for I := Low(FAdditionalValuesToFilter) to High(FAdditionalValuesToFilter) do
      QueryText := QueryText + AnsiQuotedStr(FAdditionalFieldsToFilter[I], '"') + ' = ' + QuotedStr(FAdditionalValuesToFilter[I]) + ' and ';
    SetLength(QueryText, QueryText.Length - 5); // remove additional " and "
  end;

  FTargetQuery.SQL.Text := QueryText;
  try
    FTargetQuery.Open();
    if FTargetQuery.Fields[0].IsNull then
      FLastDate := 0
    else
    begin
      FLastDate := FTargetQuery.Fields[0].AsDateTime;
    end;
    FTargetQuery.Close();
  except
    on E: EUniError do
    begin
      FLog.Log('GetTargetLastDate(): ' + E.Message);
      Exit(False);
    end;
  end;

  Result := True;
end;


//------------------------------------------------------------------------------
function TMonitorDataChangeThread.PrepareSourceQuery(): Boolean;
var
  QueryText: string;
  I: Integer;
begin
  if not Assigned(FSourceQuery) then
  begin
    FLog.Log('PrepareSourceQuery(): SourceQuery is not assigned! Exiting.');
    Exit(False);
  end;

  QueryText := 'select * from ' + AnsiQuotedStr(FSourceTableName, '"');
  QueryText := QueryText + ' where ' + AnsiQuotedStr(FSourceDateTimeFieldname, '"') + ' > ' + GetDateTimeFormatted(FSourceDB.ProviderName, FLastDate);
  for I := Low(FAdditionalValuesToFilter) to High(FAdditionalValuesToFilter) do
    QueryText := QueryText + ' and ' + AnsiQuotedStr(FAdditionalFieldsToFilter[I], '"') + ' = ' + QuotedStr(FAdditionalValuesToFilter[I]);

  FSourceQuery.SQL.Text := QueryText;
  try
    FSourceQuery.Open();
  except
    on E: EUniError do
    begin
      FLog.Log('PrepareSourceQuery(): ' + E.Message);
      Exit(False);
    end;
  end;

  Result := True;
end;


//------------------------------------------------------------------------------
function TMonitorDataChangeThread.PrepareTargetQuery(): Boolean;
var
  QueryText: string;
  I: Integer;
begin
  if not Assigned(FTargetQuery) then
  begin
    FLog.Log('PrepareTargetQuery(): TargetQuery is not assigned! Exiting.');
    Exit(False);
  end;

  QueryText := 'select * from ' + AnsiQuotedStr(FTargetTableName, '"');
  QueryText := QueryText + ' where ' + AnsiQuotedStr(FTargetDateTimeFieldname, '"') + ' > ' + QuotedStr(GetDateTimeFormatted(FTargetDB.ProviderName, FLastDate));
  for I := Low(FAdditionalValuesToFilter) to High(FAdditionalValuesToFilter) do
    QueryText := QueryText + ' and ' + AnsiQuotedStr(FAdditionalFieldsToFilter[I], '"') + ' = ' + QuotedStr(FAdditionalValuesToFilter[I]);

  FTargetQuery.SQL.Text := QueryText;
  try
    FTargetQuery.Open();
  except
    on E: EUniError do
    begin
      FLog.Log('PrepareTargetQuery(): ' + E.Message);
      Exit(False);
    end;
  end;

  Result := True;
end;


//------------------------------------------------------------------------------
function TMonitorDataChangeThread.DeleteMissingFromTarget(): Boolean;
var
  Count: Integer;
begin
  if not Assigned(FSourceQuery) then
  begin
    FLog.Log('DeleteMissingFromTarget(): SourceQuery is not assigned! Exiting.');
    Exit(False);
  end;

  if not FSourceQuery.Active then
  begin
    FLog.Log('DeleteMissingFromTarget(): SourceQuery is not open! Exiting.');
    Exit(False);
  end;

  if not Assigned(FTargetQuery) then
  begin
    FLog.Log('DeleteMissingFromTarget(): TargetQuery is not assigned! Exiting.');
    Exit(False);
  end;

  if not FTargetQuery.Active then
  begin
    FLog.Log('DeleteMissingFromTarget(): TargetQuery is not open! Exiting.');
    Exit(False);
  end;

  Count := 0;
  FTargetQuery.Last();
  while not FTargetQuery.Bof do
  begin
    if not FSourceQuery.Locate(FSourceDateTimeFieldname, FTargetQuery.FieldByName(FTargetDateTimeFieldname).AsDateTime, []) then
    begin
      FLog.Log('DeleteMissingFromTarget(): Deleting record: ' + FormatDateTime('yyyy-mm-dd hh:nn:ss', FTargetQuery.FieldByName(FTargetDateTimeFieldname).AsDateTime) + '...');
      try
        FTargetQuery.Delete();
        Inc(Count);
      except
        on E: EUniError do
        begin
          FLog.Log('DeleteMissingFromTarget(): Failed to delete record. ERROR: ' + E.Message);
        end;
      end;
    end
    else
      FTargetQuery.Prior();
  end;
  FLog.Log('Deleted record count: ' + Count.ToString());

  Result := True;
end;


//------------------------------------------------------------------------------
procedure TMonitorDataChangeThread.Execute();
label
  UpdateIt;

var
  Timing: TStopwatch;
  InitialRun: Boolean;
  Updated: Boolean;
  Mode: string;
begin
  FComInitialized := Succeeded(CoInitialize(nil));

  if FSourceDatabaseConnectString = EmptyStr then
  begin
    FLog.Log('ERROR: SourceDatabaseConnectString is empty.');
    Exit();
  end;

  if FTargetDatabaseConnectString = EmptyStr then
  begin
    FLog.Log('ERROR: TargetDatabaseConnectString is empty.');
    Exit();
  end;

  if FSourceTableName = EmptyStr then
  begin
    FLog.Log('ERROR: SourceTablename is empty.');
    Exit();
  end;

  if FTargetTableName = EmptyStr then
  begin
    FLog.Log('ERROR: TargetTablename is empty.');
    Exit();
  end;

  if Length(FAdditionalFieldsToFilter) <> Length(FAdditionalValuesToFilter) then
  begin
    FLog.Log('ERROR: AdditionalFieldsToFilter <> AdditionalValuesToFilter.');
    Exit();
  end;

  if FSourceDateTimeFieldname = EmptyStr then
  begin
    FLog.Log('ERROR: SourceDateTimeFieldname is empty.');
    Exit();
  end;

  if FTargetDateTimeFieldname = EmptyStr then
  begin
    FLog.Log('ERROR: TargetDateTimeFieldname is empty.');
    Exit();
  end;

  FActive := True;
  InitialRun := True; // used for initial (very first time after thread starts) trigger of event
  Timing := TStopwatch.StartNew();
  while not Terminated do
  begin
    if Terminated then Break;
    if ((Timing.ElapsedMilliseconds / 1000) >= FCheckFrequency) or (InitialRun) then
    begin
      InitialRun := False;
      Timing.Stop();
      FLog.Log('TMonitorDataChangeThread.Execute(): Starting batch operation...');
      FLog.IncIndent();
      try
        FLog.Log('Checking SourceDB connection...');
        if not FSourceDB.Connected then
          DoDBConnection(FSourceDB, FSourceDatabaseConnectString);

        FLog.Log('SourceDB connection: ' + BoolToStr(FSourceDB.Connected, True));
        if not FSourceDB.Connected then
          Continue;

        FLog.Log('Checking TargetDB connection...');
        if not FTargetDB.Connected then
          DoDBConnection(FTargetDB, FTargetDatabaseConnectString);

        FLog.Log('TargetDB connection: ' + BoolToStr(FTargetDB.Connected, True));
        if not FTargetDB.Connected then
          Continue;

{
        if FSourcePrimaryKeyFieldname = EmptyStr then
          if not GetPrimaryKeyFieldname(FSourceDB, FSourceTableName, FSourcePrimaryKeyFieldname) then
            Continue;

        if FTargetPrimaryKeyFieldname = EmptyStr then
          if not GetPrimaryKeyFieldname(FTargetDB, FTargetTableName, FTargetPrimaryKeyFieldname) then
            Continue;
}

        FLog.Log('Reading last record date on Target...');
        if not GetTargetLastDate() then
          Continue;
        FLog.Log('Insert check date: ' + DateToISO8601(FLastDate));

        FBatchMove.Mode := bmAppend;
        Updated := False;
        Mode := 'insert';
UpdateIt:
        FLog.Log(Format('Preparing source query for %s...', [Mode]));
        if not PrepareSourceQuery() then
          Continue;

        FLog.Log(Format('Preparing target query for %s...', [Mode]));
        if not PrepareTargetQuery() then
          Continue;

        FLog.Log(Format('Starting %s operation...', [Mode]));
        try
          FBatchMove.Execute();
          FLog.Log('Modified record count: ' + FBatchMove.ChangedCount.ToString());
          FLog.Log('Moved (Inserted & Updated) record count: ' + FBatchMove.MovedCount.ToString());
        except
          on E: EUniError do
          begin
            FLog.Log('TMonitorDataChangeThread.Execute(): ' + E.Message);
          end;
        end;

        if (FOldDaysToIncludeInCheck > 0) and not Updated then
        begin
          FLastDate := IncDay(FLastDate, -FOldDaysToIncludeInCheck);
          FLog.Log('Update check date: ' + DateToISO8601(FLastDate));
          FBatchMove.Mode := bmUpdate;
          Updated := True;
          Mode := 'update';
          goto UpdateIt;
        end;

        // Bu noktada artık Query eski kayıtlar ile açılmış oluyor
        if (FOldDaysToIncludeInCheck > 0) then
        begin
          Mode := 'identifying missing records';
          FLog.Log(Format('Preparing source query for %s...', [Mode]));
          if not PrepareSourceQuery() then
            Continue;

          FLog.Log(Format('Preparing target query for %s...', [Mode]));
          if not PrepareTargetQuery() then
            Continue;

          FLog.Log('Starting deleting missing records from target...');
          DeleteMissingFromTarget();
        end;
      finally
        FSourceDB.Close();
        FTargetDB.Close();
        Timing := TStopwatch.StartNew();
        FLog.DecIndent();
        FLog.Log('TMonitorDataChangeThread.Execute(): Finished batch operation...');
      end;
    end;
    TThread.Sleep(100);
  end;
end;


//------------------------------------------------------------------------------
procedure TMonitorDataChangeThread.SetLogDir(const Value: string);
begin
  FLog.BaseDir := Value;
  FLogDir := Value;
end;


//------------------------------------------------------------------------------
procedure TMonitorDataChangeThread.SetLogFilename(const Value: string);
begin
  FLog.LogFilename := Value;
  FLogFilename := Value;
end;


//------------------------------------------------------------------------------
procedure TMonitorDataChangeThread.SetLoggingActive(const Value: Boolean);
begin
  FLog.LoggingActive := Value;
  FLoggingActive := Value;
end;


//------------------------------------------------------------------------------
procedure TMonitorDataChangeThread.DoTerminate();
begin
  if FComInitialized then CoUninitialize();
  inherited;
end;

end.

glagherMerhaba,

Lokalda çalışan ve anlık olarak veri girişi olan bir veritabanını sunucu tarafında bulunan MS SQL Server 2008'e nasıl kopyalayabilirim. Lokalde çalışan veri tabanında bulunan tablo alanları,view ler,procedure ler ve trigerler 2 aya bir güncelleme yapılmakta. Yapılan bu güncellemeler ile birlikte ve içindeki veriler ile birlikte günlük olarak nasıl kopyalanabilir? Yardımlarınız için şimdiden teşekkür ederim.

Teşekkür ederim.