private string UpdateTriggerBodyText() { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendLine(); stringBuilder.AppendLine($"Begin "); stringBuilder.AppendLine($"\tUPDATE {trackingName.Quoted().ToString()} "); stringBuilder.AppendLine("\tSET `update_scope_id` = NULL -- since the update if from local, it's a NULL"); stringBuilder.AppendLine($"\t\t,`update_timestamp` = {MySqlObjectNames.TimestampValue}"); stringBuilder.AppendLine($"\t\t,`timestamp` = {MySqlObjectNames.TimestampValue}"); stringBuilder.AppendLine("\t\t,`last_change_datetime` = utc_timestamp()"); if (this.Filters != null && Filters.Count > 0) { foreach (var filterColumn in this.Filters) { if (this.tableDescription.PrimaryKey.Columns.Any(c => c.ColumnName.ToLowerInvariant() == filterColumn.ColumnName.ToLowerInvariant())) { continue; } var columnName = ParserName.Parse(filterColumn.ColumnName, "`").Quoted().ToString(); stringBuilder.AppendLine($"\t\t,{columnName} = new.{columnName}"); } stringBuilder.AppendLine(); } stringBuilder.Append($"\tWhere "); stringBuilder.Append(MySqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKey.Columns, trackingName.Quoted().ToString(), "new")); stringBuilder.AppendLine($"; "); stringBuilder.AppendLine($"End; "); return(stringBuilder.ToString()); }
private string DeleteTriggerBodyText() { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendLine(); stringBuilder.AppendLine("BEGIN"); stringBuilder.AppendLine($"UPDATE {trackingName.Quoted().ToString()} "); stringBuilder.AppendLine("SET `sync_row_is_tombstone` = 1"); stringBuilder.AppendLine("\t,`update_scope_id` = NULL -- since the update if from local, it's a NULL"); stringBuilder.AppendLine($"\t,`update_timestamp` = {MySqlObjectNames.TimestampValue}"); stringBuilder.AppendLine($"\t,`timestamp` = {MySqlObjectNames.TimestampValue}"); stringBuilder.AppendLine("\t,`last_change_datetime` = utc_timestamp()"); // Filter columns if (this.Filters != null) { foreach (var filterColumn in this.Filters) { if (this.tableDescription.PrimaryKey.Columns.Any(c => c.ColumnName.ToLowerInvariant() == filterColumn.ColumnName.ToLowerInvariant())) { continue; } var columnName = ParserName.Parse(filterColumn.ColumnName, "`").Quoted().ToString(); stringBuilder.AppendLine($"\t,{columnName} = old.{columnName}"); } stringBuilder.AppendLine(); } stringBuilder.Append($"WHERE "); stringBuilder.Append(MySqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKey.Columns, trackingName.Quoted().ToString(), "old")); stringBuilder.AppendLine(";"); stringBuilder.AppendLine("END;"); return(stringBuilder.ToString()); }
private void CreateDeleteCommandText() { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendLine($"DELETE FROM {tableName.QuotedString} "); stringBuilder.Append($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.TableDescription.PrimaryKey.Columns, "")}"); stringBuilder.AppendLine($" AND ((SELECT `timestamp` FROM {trackingName.QuotedObjectName} "); stringBuilder.AppendLine($" WHERE {MySqlManagementUtils.JoinTwoTablesOnClause(this.TableDescription.PrimaryKey.Columns, tableName.QuotedObjectName, trackingName.QuotedObjectName)}"); stringBuilder.AppendLine(" ) <= @sync_min_timestamp OR @sync_force_write = 1"); stringBuilder.AppendLine(");"); this.AddName(DbCommandType.DeleteRow, stringBuilder.ToString()); }
private string CreateUpdateUntrackedRowsCommand() { var stringBuilder = new StringBuilder(); var str1 = new StringBuilder(); var str2 = new StringBuilder(); var str3 = new StringBuilder(); var str4 = MySqlManagementUtils.JoinTwoTablesOnClause(this.TableDescription.GetPrimaryKeysColumns(), "`side`", "`base`"); stringBuilder.AppendLine($"INSERT INTO {trackingName.Schema().Quoted().ToString()} ("); var comma = ""; foreach (var pkeyColumn in TableDescription.GetPrimaryKeysColumns()) { var pkeyColumnName = ParserName.Parse(pkeyColumn, "`").Quoted().ToString(); str1.Append($"{comma}{pkeyColumnName}"); str2.Append($"{comma}`base`.{pkeyColumnName}"); str3.Append($"{comma}`side`.{pkeyColumnName}"); comma = ", "; } stringBuilder.Append(str1.ToString()); stringBuilder.AppendLine($", `update_scope_id`, `sync_row_is_tombstone`, `timestamp`, `last_change_datetime`"); stringBuilder.AppendLine($")"); stringBuilder.Append($"SELECT "); stringBuilder.Append(str2.ToString()); stringBuilder.AppendLine($", NULL, 0, {MySqlObjectNames.TimestampValue}, now()"); stringBuilder.AppendLine($"FROM {tableName.Schema().Quoted().ToString()} as `base` WHERE NOT EXISTS"); stringBuilder.Append($"(SELECT "); stringBuilder.Append(str3.ToString()); stringBuilder.AppendLine($" FROM {trackingName.Schema().Quoted().ToString()} as `side` "); stringBuilder.AppendLine($"WHERE {str4})"); var r = stringBuilder.ToString(); return(r); }
private void CreateUpdateCommandText() { //StringBuilder stringBuilder = new StringBuilder(); //stringBuilder.AppendLine($"SELECT @ts := `timestamp` "); //stringBuilder.AppendLine($"FROM {trackingName.QuotedObjectName}"); //stringBuilder.AppendLine($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.TableDescription.PrimaryKey.Columns, "")};"); //stringBuilder.AppendLine(); //stringBuilder.AppendLine($"UPDATE {tableName.QuotedString}"); //stringBuilder.AppendLine($"SET {MySqlManagementUtils.CommaSeparatedUpdateFromParameters(this.TableDescription)}"); //stringBuilder.AppendLine($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.TableDescription.PrimaryKey.Columns, "")}"); //stringBuilder.AppendLine($"AND (@ts <= @sync_min_timestamp OR @sync_force_write = 1);"); StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendLine($"UPDATE {tableName.QuotedString}"); stringBuilder.Append($"SET {MySqlManagementUtils.CommaSeparatedUpdateFromParameters(this.TableDescription)}"); stringBuilder.Append($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.TableDescription.PrimaryKey.Columns, "")}"); stringBuilder.AppendLine($" AND ((SELECT `timestamp` FROM {trackingName.QuotedObjectName} "); stringBuilder.AppendLine($" WHERE {MySqlManagementUtils.JoinTwoTablesOnClause(this.TableDescription.PrimaryKey.Columns, tableName.QuotedObjectName, trackingName.QuotedObjectName)}"); stringBuilder.AppendLine(" ) <= @sync_min_timestamp OR @sync_force_write = 1"); stringBuilder.AppendLine(");"); this.AddName(DbCommandType.UpdateRow, stringBuilder.ToString()); }
private string UpdateTriggerBodyText() { var stringBuilder = new StringBuilder(); stringBuilder.AppendLine(); stringBuilder.AppendLine($"Begin "); stringBuilder.AppendLine($"\tUPDATE {trackingName.Quoted().ToString()} "); stringBuilder.AppendLine("\tSET `update_scope_id` = NULL "); stringBuilder.AppendLine($"\t\t,`timestamp` = {MySqlObjectNames.TimestampValue}"); stringBuilder.AppendLine("\t\t,`last_change_datetime` = utc_timestamp()"); stringBuilder.Append($"\tWhere "); stringBuilder.Append(MySqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.GetPrimaryKeysColumns(), trackingName.Quoted().ToString(), "new")); if (this.tableDescription.GetMutableColumns().Count() > 0) { stringBuilder.AppendLine(); stringBuilder.AppendLine("\t AND ("); string or = " "; foreach (var column in this.tableDescription.GetMutableColumns()) { var quotedColumn = ParserName.Parse(column, "`").Quoted().ToString(); stringBuilder.Append("\t"); stringBuilder.Append(or); stringBuilder.Append("IFNULL("); stringBuilder.Append("NULLIF("); stringBuilder.Append("`old`."); stringBuilder.Append(quotedColumn); stringBuilder.Append(", "); stringBuilder.Append("`new`."); stringBuilder.Append(quotedColumn); stringBuilder.Append(")"); stringBuilder.Append(", "); stringBuilder.Append("NULLIF("); stringBuilder.Append("`new`."); stringBuilder.Append(quotedColumn); stringBuilder.Append(", "); stringBuilder.Append("`old`."); stringBuilder.Append(quotedColumn); stringBuilder.Append(")"); stringBuilder.AppendLine(") IS NOT NULL"); or = " OR "; } stringBuilder.AppendLine("\t ) "); } stringBuilder.AppendLine($"; "); stringBuilder.AppendLine("IF (SELECT ROW_COUNT() = 0) THEN "); stringBuilder.AppendLine($"\tINSERT INTO {trackingName.Quoted().ToString()} ("); StringBuilder stringBuilderArguments = new StringBuilder(); StringBuilder stringBuilderArguments2 = new StringBuilder(); StringBuilder stringPkAreNull = new StringBuilder(); string argComma = string.Empty; string argAnd = string.Empty; foreach (var mutableColumn in this.tableDescription.GetPrimaryKeysColumns().Where(c => !c.IsReadOnly)) { var columnName = ParserName.Parse(mutableColumn, "`").Quoted().ToString(); stringBuilderArguments.AppendLine($"\t\t{argComma}{columnName}"); stringBuilderArguments2.AppendLine($"\t\t{argComma}new.{columnName}"); stringPkAreNull.Append($"{argAnd}{trackingName.Quoted().ToString()}.{columnName} IS NULL"); argComma = ","; argAnd = " AND "; } stringBuilder.Append(stringBuilderArguments.ToString()); stringBuilder.AppendLine("\t\t,`update_scope_id`"); stringBuilder.AppendLine("\t\t,`timestamp`"); stringBuilder.AppendLine("\t\t,`sync_row_is_tombstone`"); stringBuilder.AppendLine("\t\t,`last_change_datetime`"); var filterColumnsString = new StringBuilder(); var filterColumnsString2 = new StringBuilder(); var filterColumnsString3 = new StringBuilder(); stringBuilder.AppendLine("\t) "); stringBuilder.AppendLine("\tVALUES ("); stringBuilder.Append(stringBuilderArguments2.ToString()); stringBuilder.AppendLine("\t\t,NULL"); stringBuilder.AppendLine($"\t\t,{MySqlObjectNames.TimestampValue}"); stringBuilder.AppendLine("\t\t,0"); stringBuilder.AppendLine("\t\t,utc_timestamp()"); stringBuilder.AppendLine("\t)"); stringBuilder.AppendLine("ON DUPLICATE KEY UPDATE"); stringBuilder.AppendLine("\t`update_scope_id` = NULL, "); stringBuilder.AppendLine("\t`sync_row_is_tombstone` = 0, "); stringBuilder.AppendLine($"\t`timestamp` = {MySqlObjectNames.TimestampValue}, "); stringBuilder.AppendLine("\t`last_change_datetime` = utc_timestamp();"); stringBuilder.AppendLine("END IF;"); stringBuilder.AppendLine($"End; "); return stringBuilder.ToString(); }
public DbCommand CreateUpdateTriggerCommand(DbConnection connection, DbTransaction transaction) { var updTriggerName = string.Format(this.mySqlObjectNames.GetTriggerCommandName(DbTriggerType.Update), tableName.Unquoted().Normalized().ToString()); StringBuilder createTrigger = new StringBuilder(); createTrigger.AppendLine($"CREATE TRIGGER {updTriggerName} AFTER UPDATE ON {tableName.Quoted().ToString()} FOR EACH ROW "); createTrigger.AppendLine(); createTrigger.AppendLine(); createTrigger.AppendLine($"Begin "); createTrigger.AppendLine($"\tUPDATE {trackingName.Quoted().ToString()} "); createTrigger.AppendLine("\tSET `update_scope_id` = NULL "); createTrigger.AppendLine($"\t\t,`timestamp` = {MySqlObjectNames.TimestampValue}"); createTrigger.AppendLine("\t\t,`last_change_datetime` = utc_timestamp()"); createTrigger.Append($"\tWhere "); createTrigger.Append(MySqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.GetPrimaryKeysColumns(), trackingName.Quoted().ToString(), "new")); if (this.tableDescription.GetMutableColumns().Count() > 0) { createTrigger.AppendLine(); createTrigger.AppendLine("\t AND ("); string or = " "; foreach (var column in this.tableDescription.GetMutableColumns()) { var quotedColumn = ParserName.Parse(column, "`").Quoted().ToString(); createTrigger.Append("\t"); createTrigger.Append(or); createTrigger.Append("IFNULL("); createTrigger.Append("NULLIF("); createTrigger.Append("`old`."); createTrigger.Append(quotedColumn); createTrigger.Append(", "); createTrigger.Append("`new`."); createTrigger.Append(quotedColumn); createTrigger.Append(")"); createTrigger.Append(", "); createTrigger.Append("NULLIF("); createTrigger.Append("`new`."); createTrigger.Append(quotedColumn); createTrigger.Append(", "); createTrigger.Append("`old`."); createTrigger.Append(quotedColumn); createTrigger.Append(")"); createTrigger.AppendLine(") IS NOT NULL"); or = " OR "; } createTrigger.AppendLine("\t ) "); } createTrigger.AppendLine($"; "); createTrigger.AppendLine("IF (SELECT ROW_COUNT() = 0) THEN "); createTrigger.AppendLine($"\tINSERT INTO {trackingName.Quoted().ToString()} ("); StringBuilder stringBuilderArguments = new StringBuilder(); StringBuilder stringBuilderArguments2 = new StringBuilder(); StringBuilder stringPkAreNull = new StringBuilder(); string argComma = string.Empty; string argAnd = string.Empty; foreach (var mutableColumn in this.tableDescription.GetPrimaryKeysColumns().Where(c => !c.IsReadOnly)) { var columnName = ParserName.Parse(mutableColumn, "`").Quoted().ToString(); stringBuilderArguments.AppendLine($"\t\t{argComma}{columnName}"); stringBuilderArguments2.AppendLine($"\t\t{argComma}new.{columnName}"); stringPkAreNull.Append($"{argAnd}{trackingName.Quoted().ToString()}.{columnName} IS NULL"); argComma = ","; argAnd = " AND "; } createTrigger.Append(stringBuilderArguments.ToString()); createTrigger.AppendLine("\t\t,`update_scope_id`"); createTrigger.AppendLine("\t\t,`timestamp`"); createTrigger.AppendLine("\t\t,`sync_row_is_tombstone`"); createTrigger.AppendLine("\t\t,`last_change_datetime`"); var filterColumnsString = new StringBuilder(); var filterColumnsString2 = new StringBuilder(); var filterColumnsString3 = new StringBuilder(); createTrigger.AppendLine("\t) "); createTrigger.AppendLine("\tVALUES ("); createTrigger.Append(stringBuilderArguments2.ToString()); createTrigger.AppendLine("\t\t,NULL"); createTrigger.AppendLine($"\t\t,{MySqlObjectNames.TimestampValue}"); createTrigger.AppendLine("\t\t,0"); createTrigger.AppendLine("\t\t,utc_timestamp()"); createTrigger.AppendLine("\t)"); createTrigger.AppendLine("ON DUPLICATE KEY UPDATE"); createTrigger.AppendLine("\t`update_scope_id` = NULL, "); createTrigger.AppendLine("\t`sync_row_is_tombstone` = 0, "); createTrigger.AppendLine($"\t`timestamp` = {MySqlObjectNames.TimestampValue}, "); createTrigger.AppendLine("\t`last_change_datetime` = utc_timestamp();"); createTrigger.AppendLine("END IF;"); createTrigger.AppendLine($"End; "); var command = connection.CreateCommand(); command.Connection = connection; command.Transaction = transaction; command.CommandText = createTrigger.ToString(); return(command); }