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.