/// <summary>
/// Создает таблицу
/// </summary>
/// <param name="schemaName">
/// Имя схемы в которой будет создана таблица
/// </param>
/// <param name="tableName">
/// Имя таблица
/// </param>
/// <param name="tableStruct">
/// Структура таблицы
/// </param>
public void CreateTable(string schemaName, string tableName, SqlTableStruct tableStruct)
{
if (this.IsObjectNotNull(schemaName, tableName))
{
return;
}
string query = @"USE " + this._dbName + @";
CREATE TABLE " + schemaName + "." + tableName + @" (";
query += " " + tableStruct.ColumnNames[0] + " " + tableStruct.ColumnSqlTypes[0];
if (tableStruct.IsPrimaryKey[0] == true)
{
query += " PRIMARY KEY";
}
for (int i = 1; i < tableStruct.ColumnCount; i++)
{
query += ", " + tableStruct.ColumnNames[i] + " " + tableStruct.ColumnSqlTypes[i];
if (tableStruct.IsPrimaryKey[i] == true)
{
query += " PRIMARY KEY";
}
}
query += ");";
this.RunQuery(query);
}
/// <summary>
/// Слияние двух таблиц
/// </summary>
/// <param name="fromDBName">
/// Имя базы данных источника
/// </param>
/// <param name="fromSchemaName">
/// Имя схемы данных источника
/// </param>
/// <param name="fromTableName">
/// Имя таблицы источника
/// </param>
/// <param name="toDBName">
/// Имя базы данных цели
/// </param>
/// <param name="toSchemaName">
/// Имя схемы цели
/// </param>
/// <param name="toTableName">
/// Имя целевой таблицы
/// </param>
public void MergeTables(string fromDBName, string fromSchemaName, string fromTableName, string toDBName, string toSchemaName, string toTableName)
{
string primaryName = this.GetPrimaryKeyName(toSchemaName, toTableName);
string query = @"MERGE " + toDBName + "." + toSchemaName + "." + toTableName + @" AS TGT
USING " + fromDBName + "." + fromSchemaName + "." + fromTableName + @" AS SRC
ON TGT." + primaryName + " = SRC." + primaryName + @"
WHEN MATCHED THEN
UPDATE SET " ;
SqlTableStruct tableStruct = this.GetTableInfo(toSchemaName, toTableName);
query += " TGT." + tableStruct.ColumnNames[0] + " = SRC." + tableStruct.ColumnNames[0];
for (int i = 1; i < tableStruct.ColumnCount; i++)
{
query += " , TGT." + tableStruct.ColumnNames[i] + " = SRC." + tableStruct.ColumnNames[i];
}
query += @" WHEN NOT MATCHED THEN
INSERT (" ;
query += " " + tableStruct.ColumnNames[0];
for (int i = 1; i < tableStruct.ColumnCount; i++)
{
query += ", " + tableStruct.ColumnNames[i];
}
query += @")
VALUES (" ;
query += " SRC." + tableStruct.ColumnNames[0];
for (int i = 1; i < tableStruct.ColumnCount; i++)
{
query += ", SRC." + tableStruct.ColumnNames[i];
}
query += @")
WHEN NOT MATCHED BY SOURCE THEN
DELETE;" ;
this.RunQuery(query);
}
/// <summary>
/// Метод создающий журналы и триггеры
/// </summary>
public void OnStart()
{
if (!this._dbmMaster.IsConnected())
{
this.ConnectMaster(this._config.MaxDBErrorCount);
}
if (!this._dbmSlave.IsConnected())
{
this.ConnectSlave(this._config.MaxDBErrorCount);
}
this._dbmSlave.CreateDB();
this._dbmSlave.CreateSchema(this._config.SchemaName);
SqlDBStruct masterStruct = this._dbmMaster.GetDBInfo();
for (int i = 0; i < masterStruct.TablesCount; i++)
{
SqlTableStruct tempStruct = this._dbmMaster.GetTableInfo(masterStruct.SchemasNames[i], masterStruct.TablesNames[i]);
this._dbmSlave.CreateJournal(this._config.SchemaName, masterStruct.SchemasNames[i], masterStruct.TablesNames[i], tempStruct);
this._dbmMaster.CreateTriggerOnInsert(this._config.SchemaName, masterStruct.SchemasNames[i], masterStruct.TablesNames[i], this._config.SlaveDBName);
this._dbmMaster.CreateTriggerOnUpdate(this._config.SchemaName, masterStruct.SchemasNames[i], masterStruct.TablesNames[i], this._config.SlaveDBName);
this._dbmMaster.CreateTriggerOnDelete(this._config.SchemaName, masterStruct.SchemasNames[i], masterStruct.TablesNames[i], this._config.SlaveDBName);
this._dbmSlave.CreateSchema(masterStruct.SchemasNames[i]);
this._dbmSlave.CreateTable(masterStruct.SchemasNames[i], masterStruct.TablesNames[i], tempStruct);
this._dbmSlave.MergeTables(this._config.MasterDBName, masterStruct.SchemasNames[i], masterStruct.TablesNames[i], this._config.SlaveDBName, masterStruct.SchemasNames[i], masterStruct.TablesNames[i]);
}
}
/// <summary>
/// Создание триггера на событие DELETE
/// </summary>
/// <param name="schemaName">
/// Имя схемы в которой расположена таблица
/// </param>
/// <param name="tableName">
/// Имя таблицы на которую будет создан триггер
/// </param>
/// <param name="slaveDBName">
/// Имя базы данных в которой расположен журнал
/// </param>
public void CreateTriggerOnDelete(string journalSchema, string schemaName, string tableName, string slaveDBName)
{
if (this.IsObjectNotNull(schemaName, "Trigger_" + tableName + "_delete"))
{
return;
}
string query = @"CREATE TRIGGER " + schemaName + @".Trigger_" + tableName + @"_delete
ON " + this._dbName + @"." + schemaName + @"." + tableName + @" AFTER DELETE
AS
SET NOCOUNT ON;
INSERT INTO " + slaveDBName + @"." + journalSchema + "." + schemaName + tableName + @"(event_type";
SqlTableStruct tableStruct = this.GetTableInfo(schemaName, tableName);
for (int i = 0; i < tableStruct.ColumnCount; i++)
{
query += @", " + tableStruct.ColumnNames[i] + "_old";
}
query += @") SELECT event_type = 'DELETED'";
for (int i = 0; i < tableStruct.ColumnCount; i++)
{
query += @", " + tableStruct.ColumnNames[i];
}
query += @" FROM deleted;";
this.RunQuery(query);
}
/// <summary>
/// Генерирует и исполняет запрос, обрабатывающий запись в журнале на событие UPDATED
/// </summary>
/// <param name="journalSchema">
/// Схема в которой расположен журнал
/// </param>
/// <param name="journalName">
/// Имя журнала
/// </param>
/// <param name="slaveSchema">
/// Имя схема в которо расположена таблица Slave
/// </param>
/// <param name="slaveTable">
/// Имя таблицы Slave
/// </param>
/// <param name="journalRowID">
/// id в журнале, которое будет обработано
/// </param>
public void GenerateSqlOnUpdate(string journalSchema, string journalName, string slaveSchema, string slaveTable, string journalRowID)
{
string query = @"USE " + this._dbName + @";
UPDATE " + slaveSchema + "." + slaveTable + @"
SET ";
SqlTableStruct tableStruct = this.GetTableInfo(slaveSchema, slaveTable);
query += " " + tableStruct.ColumnNames[0] + " = " + journalSchema + "." + journalName + "." + tableStruct.ColumnNames[0] + "_new ";
for (int i = 1; i < tableStruct.ColumnCount; i++)
{
query += ", " + tableStruct.ColumnNames[i] + " = " + journalSchema + "." + journalName + "." + tableStruct.ColumnNames[i] + "_new ";
}
string primaryName = this.GetPrimaryKeyName(slaveSchema, slaveTable);
query += @"FROM " + slaveSchema + "." + slaveTable + @"
JOIN " + journalSchema + "." + journalName + @"
ON " + slaveSchema + "." + slaveTable + "." + primaryName + " = " + journalSchema + "." + journalName + "." + primaryName + @"_old
WHERE " + journalSchema + "." + journalName + ".id = " + journalRowID + ";";
this.RunQuery(query);
}
/// <summary>
/// Генерирует и исполняет запрос, обрабатывающий запись в журнале на событие INSERTED
/// </summary>
/// <param name="journalSchema">
/// Схема в которой расположен журнал
/// </param>
/// <param name="journalName">
/// Имя журнала
/// </param>
/// <param name="slaveSchema">
/// Имя схема в которо расположена таблица Slave
/// </param>
/// <param name="slaveTable">
/// Имя таблицы Slave
/// </param>
/// <param name="journalRowID">
/// id в журнале, которое будет обработано
/// </param>
public void GenerateSqlOnInsert(string journalSchema, string journalName, string slaveSchema, string slaveTable, string journalRowID)
{
string query = @"USE " + this._dbName + @" ;
INSERT INTO " + slaveSchema + @"." + slaveTable + @"(";
SqlTableStruct replicStruct = this.GetTableInfo(slaveSchema, slaveTable);
query += replicStruct.ColumnNames[0];
for (int i = 1; i < replicStruct.ColumnCount; i++)
{
query += @", " + replicStruct.ColumnNames[i];
}
query += @") SELECT " + replicStruct.ColumnNames[0] + @"_new ";
for (int i = 1; i < replicStruct.ColumnCount; i++)
{
query += @", " + replicStruct.ColumnNames[i] + @"_new ";
}
query += @" FROM " + journalSchema + "." + journalName + @" WHERE id = " + journalRowID + @";";
this.RunQuery(query);
}
/// <summary>
/// Позволяет получить информацию о таблице
/// </summary>
/// <param name="schemaName">
/// Имя схемы в которой расположена таблица
/// </param>
/// <param name="tableName">
/// Имя таблицы
/// </param>
/// <returns>
/// Имена столбцов таблицы и их типы данных
/// </returns>
public SqlTableStruct GetTableInfo(string schemaName, string tableName)
{
var result = this.RunQuery(@"USE " + this._dbName + @" ;
DECLARE @schemaid AS INT;
DECLARE @tableid AS INT;
SELECT @schemaid = schema_id
FROM sys.schemas
WHERE name = '" + schemaName + @"';
SELECT @tableid = object_id FROM sys.objects
WHERE type = 'U' AND schema_id = @schemaid AND name = '" + tableName + @"';
SELECT SC.name, ST.name, SC.max_length/2
FROM sys.columns AS SC
JOIN sys.types AS ST
ON SC.system_type_id = ST.system_type_id AND SC.user_type_id = ST.user_type_id
WHERE SC.object_id = @tableid;");
SqlTableStruct retval = new SqlTableStruct();
retval.ColumnCount = result.Count;
for (int i = 0; i < result.Count; i++)
{
retval.ColumnNames.Add(result[i].Values[0].ToString());
string columnType = result[i].Values[1].ToString();
if (columnType == "varchar" || columnType == "nvarchar")
{
columnType += "(" + result[i].Values[2].ToString() + ")";
}
retval.ColumnSqlTypes.Add(columnType);
retval.IsPrimaryKey.Add(false);
}
int primary = this.GetPrimaryKeyId(schemaName, tableName);
if (primary != -1)
{
retval.IsPrimaryKey[primary] = true;
}
return(retval);
}
/// <summary>
/// Создает журнал(таблицу)
/// </summary>
/// <param name="schemaName">
/// Схема в которой расположена таблица, на которую создается журнал
/// </param>
/// <param name="tableName">
/// Имя таблицы на которую создается журнал
/// </param>
/// <param name="tableStruct">
/// Структура таблица на которую создается журнал
/// </param>
public void CreateJournal(string journalSchema, string schemaName, string tableName, SqlTableStruct tableStruct)
{
if (this.IsObjectNotNull(journalSchema, schemaName + tableName))
{
return;
}
string query = @"USE " + this._dbName + @"; CREATE TABLE " + journalSchema + "." + schemaName + tableName + @" (
id INT NOT NULL IDENTITY PRIMARY KEY,
event_time DATETIME NOT NULL DEFAULT(CURRENT_TIMESTAMP),
event_type NVARCHAR(10) NOT NULL,
login_name SYSNAME NOT NULL DEFAULT(SUSER_SNAME())" ;
for (int i = 0; i < tableStruct.ColumnCount; i++)
{
query += @" , " + tableStruct.ColumnNames[i] + @"_old " + tableStruct.ColumnSqlTypes[i] + @" DEFAULT(NULL) ";
}
for (int i = 0; i < tableStruct.ColumnCount; i++)
{
query += @" , " + tableStruct.ColumnNames[i] + @"_new " + tableStruct.ColumnSqlTypes[i] + @" DEFAULT(NULL) ";
}
query += @");";
this.RunQuery(query);
}
/// <summary>
/// Позволяет получить информацию о таблице
/// </summary>
/// <param name="schemaName">
/// Имя схемы в которой расположена таблица
/// </param>
/// <param name="tableName">
/// Имя таблицы
/// </param>
/// <returns>
/// Имена столбцов таблицы и их типы данных
/// </returns>
public SqlTableStruct GetTableInfo(string schemaName, string tableName)
{
var result = this.RunQuery(@"USE " + this._dbName + @" ;
DECLARE @schemaid AS INT;
DECLARE @tableid AS INT;
SELECT @schemaid = schema_id
FROM sys.schemas
WHERE name = '" + schemaName + @"';
SELECT @tableid = object_id FROM sys.objects
WHERE type = 'U' AND schema_id = @schemaid AND name = '" + tableName + @"';
SELECT SC.name, ST.name, SC.max_length/2
FROM sys.columns AS SC
JOIN sys.types AS ST
ON SC.system_type_id = ST.system_type_id AND SC.user_type_id = ST.user_type_id
WHERE SC.object_id = @tableid;");
SqlTableStruct retval = new SqlTableStruct();
retval.ColumnCount = result.Count;
for (int i = 0; i < result.Count; i++)
{
retval.ColumnNames.Add(result[i].Values[0].ToString());
string columnType = result[i].Values[1].ToString();
if (columnType == "varchar" || columnType == "nvarchar")
{
columnType += "(" + result[i].Values[2].ToString() + ")";
}
retval.ColumnSqlTypes.Add(columnType);
retval.IsPrimaryKey.Add(false);
}
int primary = this.GetPrimaryKeyId(schemaName, tableName);
if (primary != -1)
{
retval.IsPrimaryKey[primary] = true;
}
return retval;
}
/// <summary>
/// Создает таблицу
/// </summary>
/// <param name="schemaName">
/// Имя схемы в которой будет создана таблица
/// </param>
/// <param name="tableName">
/// Имя таблица
/// </param>
/// <param name="tableStruct">
/// Структура таблицы
/// </param>
public void CreateTable(string schemaName, string tableName, SqlTableStruct tableStruct)
{
if (this.IsObjectNotNull(schemaName, tableName))
{
return;
}
string query = @"USE " + this._dbName + @";
CREATE TABLE " + schemaName + "." + tableName + @" (";
query += " " + tableStruct.ColumnNames[0] + " " + tableStruct.ColumnSqlTypes[0];
if (tableStruct.IsPrimaryKey[0] == true)
{
query += " PRIMARY KEY";
}
for (int i = 1; i < tableStruct.ColumnCount; i++)
{
query += ", " + tableStruct.ColumnNames[i] + " " + tableStruct.ColumnSqlTypes[i];
if (tableStruct.IsPrimaryKey[i] == true)
{
query += " PRIMARY KEY";
}
}
query += ");";
this.RunQuery(query);
}
/// <summary>
/// Создает журнал(таблицу)
/// </summary>
/// <param name="schemaName">
/// Схема в которой расположена таблица, на которую создается журнал
/// </param>
/// <param name="tableName">
/// Имя таблицы на которую создается журнал
/// </param>
/// <param name="tableStruct">
/// Структура таблица на которую создается журнал
/// </param>
public void CreateJournal(string journalSchema, string schemaName, string tableName, SqlTableStruct tableStruct)
{
if (this.IsObjectNotNull(journalSchema, schemaName + tableName))
{
return;
}
string query = @"USE " + this._dbName + @"; CREATE TABLE " + journalSchema + "." + schemaName + tableName + @" (
id INT NOT NULL IDENTITY PRIMARY KEY,
event_time DATETIME NOT NULL DEFAULT(CURRENT_TIMESTAMP),
event_type NVARCHAR(10) NOT NULL,
login_name SYSNAME NOT NULL DEFAULT(SUSER_SNAME())";
for(int i=0; i<tableStruct.ColumnCount; i++) {
query += @" , " + tableStruct.ColumnNames[i] + @"_old " + tableStruct.ColumnSqlTypes[i] + @" DEFAULT(NULL) ";
}
for (int i = 0; i < tableStruct.ColumnCount; i++)
{
query += @" , " + tableStruct.ColumnNames[i] + @"_new " + tableStruct.ColumnSqlTypes[i] + @" DEFAULT(NULL) ";
}
query += @");";
this.RunQuery(query);
}