/// <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 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); }