private string DeleteTriggerBodyText()
{
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine();
stringBuilder.AppendLine("SET NOCOUNT ON;");
stringBuilder.AppendLine();
stringBuilder.AppendLine("UPDATE [side] ");
stringBuilder.AppendLine("SET [sync_row_is_tombstone] = 1");
stringBuilder.AppendLine("\t,[update_scope_id] = NULL -- scope id is always NULL when update is made locally");
stringBuilder.AppendLine("\t,[last_change_datetime] = GetUtcDate()");
stringBuilder.AppendLine($"FROM {trackingName.Schema().Quoted().ToString()} [side]");
stringBuilder.Append($"JOIN DELETED AS [d] ON ");
stringBuilder.AppendLine(NpgsqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[side]", "[d]"));
stringBuilder.AppendLine();
stringBuilder.AppendLine($"INSERT INTO {trackingName.Schema().Quoted().ToString()} (");
var stringBuilderArguments = new StringBuilder();
var stringBuilderArguments2 = new StringBuilder();
var stringPkAreNull = new StringBuilder();
string argComma = " ";
string argAnd = string.Empty;
var primaryKeys = this.tableDescription.GetPrimaryKeysColumns();
foreach (var mutableColumn in primaryKeys.Where(c => !c.IsReadOnly))
{
var columnName = ParserName.Parse(mutableColumn).Quoted().ToString();
stringBuilderArguments.AppendLine($"\t{argComma}[d].{columnName}");
stringBuilderArguments2.AppendLine($"\t{argComma}{columnName}");
stringPkAreNull.Append($"{argAnd}[side].{columnName} IS NULL");
argComma = ",";
argAnd = " AND ";
}
stringBuilder.Append(stringBuilderArguments2.ToString());
stringBuilder.AppendLine("\t,[update_scope_id]");
stringBuilder.AppendLine("\t,[sync_row_is_tombstone]");
stringBuilder.AppendLine("\t,[last_change_datetime]");
stringBuilder.AppendLine(") ");
stringBuilder.AppendLine("SELECT");
stringBuilder.Append(stringBuilderArguments.ToString());
stringBuilder.AppendLine("\t,NULL");
stringBuilder.AppendLine("\t,1");
stringBuilder.AppendLine("\t,GetUtcDate()");
stringBuilder.AppendLine("FROM DELETED [d]");
stringBuilder.Append($"LEFT JOIN {trackingName.Schema().Quoted().ToString()} [side] ON ");
stringBuilder.AppendLine(NpgsqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[d]", "[side]"));
stringBuilder.Append("WHERE ");
stringBuilder.AppendLine(stringPkAreNull.ToString());
return(stringBuilder.ToString());
}
private string UpdateTriggerBodyText()
{
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine();
stringBuilder.AppendLine("SET NOCOUNT ON;");
stringBuilder.AppendLine();
stringBuilder.AppendLine("UPDATE [side] ");
stringBuilder.AppendLine("SET \t[update_scope_id] = NULL -- since the update if from local, it's a NULL");
stringBuilder.AppendLine("\t,[last_change_datetime] = GetUtcDate()");
stringBuilder.AppendLine();
stringBuilder.AppendLine($"FROM {trackingName.Schema().Quoted().ToString()} [side]");
stringBuilder.Append($"JOIN INSERTED AS [i] ON ");
stringBuilder.AppendLine(NpgsqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[side]", "[i]"));
if (this.tableDescription.GetMutableColumns().Count() > 0)
{
stringBuilder.Append($"JOIN DELETED AS [d] ON ");
stringBuilder.AppendLine(NpgsqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[d]", "[i]"));
stringBuilder.AppendLine("WHERE (");
string or = "";
foreach (var column in this.tableDescription.GetMutableColumns())
{
var quotedColumn = ParserName.Parse(column).Quoted().ToString();
stringBuilder.Append("\t");
stringBuilder.Append(or);
stringBuilder.Append("ISNULL(");
stringBuilder.Append("NULLIF(");
stringBuilder.Append("[d].");
stringBuilder.Append(quotedColumn);
stringBuilder.Append(", ");
stringBuilder.Append("[i].");
stringBuilder.Append(quotedColumn);
stringBuilder.Append(")");
stringBuilder.Append(", ");
stringBuilder.Append("NULLIF(");
stringBuilder.Append("[i].");
stringBuilder.Append(quotedColumn);
stringBuilder.Append(", ");
stringBuilder.Append("[d].");
stringBuilder.Append(quotedColumn);
stringBuilder.Append(")");
stringBuilder.AppendLine(") IS NOT NULL");
or = " OR ";
}
stringBuilder.AppendLine(") ");
}
stringBuilder.AppendLine($"INSERT INTO {trackingName.Schema().Quoted().ToString()} (");
var stringBuilderArguments = new StringBuilder();
var stringBuilderArguments2 = new StringBuilder();
var stringPkAreNull = new StringBuilder();
string argComma = " ";
string argAnd = string.Empty;
var primaryKeys = this.tableDescription.GetPrimaryKeysColumns();
foreach (var mutableColumn in primaryKeys.Where(c => !c.IsReadOnly))
{
var columnName = ParserName.Parse(mutableColumn).Quoted().ToString();
stringBuilderArguments.AppendLine($"\t{argComma}[i].{columnName}");
stringBuilderArguments2.AppendLine($"\t{argComma}{columnName}");
stringPkAreNull.Append($"{argAnd}[side].{columnName} IS NULL");
argComma = ",";
argAnd = " AND ";
}
stringBuilder.Append(stringBuilderArguments2.ToString());
stringBuilder.AppendLine("\t,[update_scope_id]");
stringBuilder.AppendLine("\t,[sync_row_is_tombstone]");
stringBuilder.AppendLine("\t,[last_change_datetime]");
stringBuilder.AppendLine(") ");
stringBuilder.AppendLine("SELECT");
stringBuilder.Append(stringBuilderArguments.ToString());
stringBuilder.AppendLine("\t,NULL");
stringBuilder.AppendLine("\t,0");
stringBuilder.AppendLine("\t,GetUtcDate()");
stringBuilder.AppendLine("FROM INSERTED [i]");
stringBuilder.Append($"LEFT JOIN {trackingName.Schema().Quoted().ToString()} [side] ON ");
stringBuilder.AppendLine(NpgsqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[i]", "[side]"));
stringBuilder.Append("WHERE ");
stringBuilder.AppendLine(stringPkAreNull.ToString());
return(stringBuilder.ToString());
}
private string InsertTriggerBodyText()
{
//CREATE OR REPLACE FUNCTION "public"."Address_trigger_insert_function"()
//RETURNS TRIGGER AS
//$BODY$
//BEGIN
// UPDATE "side"
// SET "sync_row_is_tombstone" = 0
// ,"update_scope_id" = NULL -- scope id is always NULL when update is made locally
// ,"last_change_datetime" = GetUtcDate()
// FROM "tProduct" "side"
// JOIN NEW AS "i" ON "side"."ProductID" = "i"."ProductID";
// INSERT INTO "tProduct" (
// "ProductID"
// ,"update_scope_id"
// ,"sync_row_is_tombstone"
// ,"last_change_datetime"
// )
// SELECT
// "i"."ProductID"
// ,NULL
// ,0
// ,GetUtcDate()
// FROM NEW AS "i"
// LEFT JOIN "tProduct" "side" ON "i"."ProductID" = "side"."ProductID"
// WHERE "side"."ProductID" IS NULL;
// END;
//$BODY$
//LANGUAGE 'plpgsql';
//CREATE TRIGGER "Address_trigger_insert" AFTER INSERT ON "public"."Address"
//EXECUTE PROCEDURE "public"."Address_trigger_insert_function"();
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine();
stringBuilder.AppendLine("-- If row was deleted before, it already exists, so just make an update");
stringBuilder.AppendLine("UPDATE side ");
stringBuilder.AppendLine("SET sync_row_is_tombstone = 0");
stringBuilder.AppendLine("\t,update_scope_id = NULL -- scope id is always NULL when update is made locally");
stringBuilder.AppendLine("\t,last_change_datetime = now()");
stringBuilder.AppendLine($"FROM {trackingName.Schema().Quoted().ToString()} AS side");
stringBuilder.Append($"JOIN NEW AS i ON ");
stringBuilder.AppendLine(NpgsqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "side", "i"));
stringBuilder.AppendLine();
stringBuilder.AppendLine($"INSERT INTO {trackingName.Schema().Quoted().ToString()} (");
var stringBuilderArguments = new StringBuilder();
var stringBuilderArguments2 = new StringBuilder();
var stringPkAreNull = new StringBuilder();
string argComma = " ";
string argAnd = string.Empty;
var primaryKeys = this.tableDescription.GetPrimaryKeysColumns();
foreach (var mutableColumn in primaryKeys.Where(c => !c.IsReadOnly))
{
var columnName = ParserName.Parse(mutableColumn).Quoted().ToString();
stringBuilderArguments.AppendLine($"\t{argComma}[i].{columnName}");
stringBuilderArguments2.AppendLine($"\t{argComma}{columnName}");
stringPkAreNull.Append($"{argAnd}side.{columnName} IS NULL");
argComma = ",";
argAnd = " AND ";
}
stringBuilder.Append(stringBuilderArguments2.ToString());
stringBuilder.AppendLine("\t,update_scope_id");
stringBuilder.AppendLine("\t,sync_row_is_tombstone");
stringBuilder.AppendLine("\t,last_change_datetime");
stringBuilder.AppendLine(") ");
stringBuilder.AppendLine("SELECT");
stringBuilder.Append(stringBuilderArguments.ToString());
stringBuilder.AppendLine("\t,NULL");
stringBuilder.AppendLine("\t,0");
stringBuilder.AppendLine("\t,now() at time zone 'utc'");
stringBuilder.AppendLine("FROM NEW i");
stringBuilder.Append($"LEFT JOIN {trackingName.Schema().Quoted().ToString()} side ON ");
stringBuilder.AppendLine(NpgsqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "i", "side"));
stringBuilder.Append("WHERE ");
stringBuilder.AppendLine(stringPkAreNull.ToString());
return(stringBuilder.ToString());
}