Yorumları: 143
Konuları: 44
Kayıt Tarihi: 24-04-2017
Rep Puanı: 149 Başlangıç
Merhaba,
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.
Yorumları: 277
Konuları: 25
Kayıt Tarihi: 10-08-2019
Aktif Kullandığınız Delphi Sürümü:
Rep Puanı: 1.137 Programcı
Yorumları: 143
Konuları: 44
Kayıt Tarihi: 24-04-2017
Rep Puanı: 149 Başlangıç
(07-09-2023, Saat: 12:00)serdar Adlı Kullanıcıdan Alıntı: Merhaba
Replication konusunu araştırın derim.
http://www.veritabani.gen.tr/2016/11/01/...ion-nedir/
Bunu daha önce araştırdım yalnız veri tabanı sürekli güncelleniyor yani yeni tablolar, tablaya yeni alanlar ekleniyor. Sürekli günellenen bir veritabanına yapılması nasıl olur. Örneğin Deneme tablasounda A ve B alanları var bu şekliyle replikasyon yaptık aradan 2 ay geçti aynı tabloya C alanı eklendi sunucu tarafına A ve B alanlarımı replike edilie yoksa A,B ve C alanları mı? Bu konu beni düşündürüyor çünkü bunu ile alakalı bir bilgiye rastlayamadım
Yorumları: 227
Konuları: 69
Kayıt Tarihi: 07-09-2016
Rep Puanı: 269 Acemi
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.
Yorumları: 143
Konuları: 44
Kayıt Tarihi: 24-04-2017
Rep Puanı: 149 Başlangıç
(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.
|