private DbCommand CreateInsertTriggerCommand(DbConnection connection, DbTransaction transaction)
{
var insTriggerName = string.Format(this.sqliteObjectNames.GetTriggerCommandName(DbTriggerType.Insert), TableName.Unquoted().ToString());
StringBuilder createTrigger = new StringBuilder($"CREATE TRIGGER IF NOT EXISTS {insTriggerName} AFTER INSERT ON {TableName.Quoted().ToString()} ");
createTrigger.AppendLine();
var stringBuilderArguments = new StringBuilder();
var stringBuilderArguments2 = new StringBuilder();
var stringPkAreNull = new StringBuilder();
string argComma = string.Empty;
string argAnd = string.Empty;
createTrigger.AppendLine();
createTrigger.AppendLine("BEGIN");
createTrigger.AppendLine("-- If row was deleted before, it already exists, so just make an update");
createTrigger.AppendLine($"\tINSERT OR REPLACE INTO {this.TrackingTableName.Quoted().ToString()} (");
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}{TrackingTableName.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]");
createTrigger.AppendLine("\t) ");
createTrigger.AppendLine("\tVALUES (");
createTrigger.Append(stringBuilderArguments2.ToString());
createTrigger.AppendLine("\t\t,NULL");
createTrigger.AppendLine($"\t\t,{SqliteObjectNames.TimestampValue}");
createTrigger.AppendLine("\t\t,0");
createTrigger.AppendLine("\t\t,datetime('now')");
createTrigger.AppendLine("\t);");
createTrigger.AppendLine("END;");
return(new SqliteCommand(createTrigger.ToString(), (SqliteConnection)connection, (SqliteTransaction)transaction));
}
private DbCommand CreateUpdateTriggerCommand(DbConnection connection, DbTransaction transaction)
{
var updTriggerName = string.Format(this.sqliteObjectNames.GetTriggerCommandName(DbTriggerType.Update), TableName.Unquoted().ToString());
StringBuilder createTrigger = new StringBuilder($"CREATE TRIGGER IF NOT EXISTS {updTriggerName} AFTER UPDATE ON {TableName.Quoted().ToString()} ");
createTrigger.AppendLine();
createTrigger.AppendLine();
createTrigger.AppendLine($"Begin ");
createTrigger.AppendLine($"\tUPDATE {TrackingTableName.Quoted().ToString()} ");
createTrigger.AppendLine("\tSET [update_scope_id] = NULL -- scope id is always NULL when update is made locally");
createTrigger.AppendLine($"\t\t,[timestamp] = {SqliteObjectNames.TimestampValue}");
createTrigger.AppendLine("\t\t,[last_change_datetime] = datetime('now')");
createTrigger.Append($"\tWhere ");
createTrigger.Append(SqliteManagementUtils.JoinTwoTablesOnClause(this.TableDescription.PrimaryKeys, TrackingTableName.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($"; ");
var stringBuilderArguments = new StringBuilder();
var stringBuilderArguments2 = new StringBuilder();
var stringPkAreNull = new StringBuilder();
string argComma = string.Empty;
string argAnd = string.Empty;
createTrigger.AppendLine($"\tINSERT OR IGNORE INTO {TrackingTableName.Quoted().ToString()} (");
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}{TrackingTableName.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]");
createTrigger.AppendLine("\t) ");
createTrigger.AppendLine("\tSELECT ");
createTrigger.Append(stringBuilderArguments2.ToString());
createTrigger.AppendLine("\t\t,NULL");
createTrigger.AppendLine($"\t\t,{SqliteObjectNames.TimestampValue}");
createTrigger.AppendLine("\t\t,0");
createTrigger.AppendLine("\t\t,datetime('now')");
createTrigger.Append($"\tWHERE (SELECT COUNT(*) FROM {TrackingTableName.Quoted().ToString()} WHERE ");
var pkeys = this.TableDescription.GetPrimaryKeysColumns();
var str1 = "";
foreach (var pkey in pkeys)
{
var quotedColumn = ParserName.Parse(pkey).Quoted().ToString();
createTrigger.Append($"{str1}{quotedColumn}=new.{quotedColumn}");
str1 = " AND ";
}
createTrigger.AppendLine(")=0");
//if (this.TableDescription.GetMutableColumns().Count() > 0)
//{
// 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($"End; ");
return(new SqliteCommand(createTrigger.ToString(), (SqliteConnection)connection, (SqliteTransaction)transaction));
}
