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