public bool NeedToCreateTrigger(DbTriggerType type) { var updTriggerName = string.Format(this.sqliteObjectNames.GetCommandName(DbCommandType.UpdateTrigger), tableName.UnquotedStringWithUnderScore); var delTriggerName = string.Format(this.sqliteObjectNames.GetCommandName(DbCommandType.DeleteTrigger), tableName.UnquotedStringWithUnderScore); var insTriggerName = string.Format(this.sqliteObjectNames.GetCommandName(DbCommandType.InsertTrigger), tableName.UnquotedStringWithUnderScore); string triggerName = string.Empty; switch (type) { case DbTriggerType.Insert: { triggerName = insTriggerName; break; } case DbTriggerType.Update: { triggerName = updTriggerName; break; } case DbTriggerType.Delete: { triggerName = delTriggerName; break; } } return(!SqliteManagementUtils.TriggerExists(connection, transaction, triggerName)); }
public async Task <bool> NeedToCreateTriggerAsync(DbTriggerType type) { var updTriggerName = string.Format(this.sqliteObjectNames.GetCommandName(DbCommandType.UpdateTrigger), tableName.Unquoted().ToString()); var delTriggerName = string.Format(this.sqliteObjectNames.GetCommandName(DbCommandType.DeleteTrigger), tableName.Unquoted().ToString()); var insTriggerName = string.Format(this.sqliteObjectNames.GetCommandName(DbCommandType.InsertTrigger), tableName.Unquoted().ToString()); string triggerName = string.Empty; switch (type) { case DbTriggerType.Insert: { triggerName = insTriggerName; break; } case DbTriggerType.Update: { triggerName = updTriggerName; break; } case DbTriggerType.Delete: { triggerName = delTriggerName; break; } } return(!(await SqliteManagementUtils.TriggerExistsAsync(connection, transaction, triggerName).ConfigureAwait(false))); }
public bool NeedToCreateTrackingTable(DbBuilderOption builderOption) { if (builderOption.HasFlag(DbBuilderOption.CreateOrUseExistingSchema)) { return(!SqliteManagementUtils.TableExists(connection, transaction, trackingName.QuotedString)); } return(false); }
private void CreateDeleteMetadataCommandText() { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendLine($"DELETE FROM {trackingName.QuotedString} "); stringBuilder.Append($"WHERE "); stringBuilder.AppendLine(SqliteManagementUtils.WhereColumnAndParameters(this.TableDescription.PrimaryKey.Columns, "")); stringBuilder.Append(";"); this.AddName(DbCommandType.DeleteMetadata, stringBuilder.ToString()); }
private void CreateUpdateCommandText() { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendLine($"UPDATE {tableName.QuotedString}"); stringBuilder.Append($"SET {SqliteManagementUtils.CommaSeparatedUpdateFromParameters(this.TableDescription)}"); stringBuilder.Append($"WHERE {SqliteManagementUtils.WhereColumnAndParameters(this.TableDescription.PrimaryKey.Columns, "")}"); stringBuilder.AppendLine($" AND ((SELECT [timestamp] FROM {trackingName.QuotedObjectName} "); stringBuilder.AppendLine($" WHERE {SqliteManagementUtils.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 void CreateDeleteCommandText() { var stringBuilder = new StringBuilder(); string str1 = SqliteManagementUtils.JoinTwoTablesOnClause(this.TableDescription.PrimaryKeys, "[c]", "[base]"); string str7 = SqliteManagementUtils.JoinTwoTablesOnClause(this.TableDescription.PrimaryKeys, "[p]", "[side]"); stringBuilder.AppendLine(";WITH [c] AS ("); stringBuilder.Append("\tSELECT "); foreach (var c in this.TableDescription.GetPrimaryKeysColumns()) { var columnName = ParserName.Parse(c).Quoted().ToString(); stringBuilder.Append($"[p].{columnName}, "); } stringBuilder.AppendLine($"[side].[update_scope_id] as [sync_update_scope_id], [side].[timestamp] as [sync_timestamp], [side].[sync_row_is_tombstone]"); stringBuilder.Append($"\tFROM (SELECT "); string comma = ""; foreach (var c in this.TableDescription.GetPrimaryKeysColumns()) { var columnName = ParserName.Parse(c).Quoted().ToString(); var columnParameterName = ParserName.Parse(c).Unquoted().Normalized().ToString(); stringBuilder.Append($"{comma}@{columnParameterName} as {columnName}"); comma = ", "; } stringBuilder.AppendLine($") AS [p]"); stringBuilder.Append($"\tLEFT JOIN {trackingName.Quoted().ToString()} [side] ON "); stringBuilder.AppendLine($"\t{str7}"); stringBuilder.AppendLine($"\t)"); stringBuilder.AppendLine($"DELETE FROM {tableName.Quoted().ToString()} "); stringBuilder.AppendLine($"WHERE {SqliteManagementUtils.WhereColumnAndParameters(this.TableDescription.PrimaryKeys, "")}"); stringBuilder.AppendLine($"AND (EXISTS ("); stringBuilder.AppendLine($" SELECT * FROM [c] "); stringBuilder.AppendLine($" WHERE {SqliteManagementUtils.WhereColumnAndParameters(this.TableDescription.PrimaryKeys, "[c]")}"); stringBuilder.AppendLine($" AND ([sync_timestamp] < @sync_min_timestamp OR [sync_timestamp] IS NULL OR [sync_update_scope_id] = @sync_scope_id))"); stringBuilder.AppendLine($" OR @sync_force_write = 1"); stringBuilder.AppendLine($" );"); stringBuilder.AppendLine(); stringBuilder.AppendLine($"UPDATE OR IGNORE {trackingName.Quoted().ToString()} SET "); stringBuilder.AppendLine("[update_scope_id] = @sync_scope_id,"); stringBuilder.AppendLine("[sync_row_is_tombstone] = 1,"); stringBuilder.AppendLine("[last_change_datetime] = datetime('now')"); stringBuilder.AppendLine($"WHERE {SqliteManagementUtils.WhereColumnAndParameters(this.TableDescription.PrimaryKeys, "")}"); stringBuilder.AppendLine($" AND (select changes()) > 0"); var cmdText = stringBuilder.ToString(); this.AddCommandName(DbCommandType.DeleteRow, cmdText); this.AddCommandName(DbCommandType.DeleteRows, cmdText); }
private void CreateUpdatedMetadataCommandText() { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendLine($"UPDATE {trackingName.QuotedString}"); stringBuilder.AppendLine($"SET [update_scope_id] = @update_scope_id, "); stringBuilder.AppendLine($"\t [update_timestamp] = @update_timestamp, "); stringBuilder.AppendLine($"\t [sync_row_is_tombstone] = @sync_row_is_tombstone, "); stringBuilder.AppendLine($"\t [timestamp] = {SqliteObjectNames.TimestampValue}, "); stringBuilder.AppendLine($"\t [last_change_datetime] = datetime('now') "); stringBuilder.Append($"WHERE {SqliteManagementUtils.WhereColumnAndParameters(this.TableDescription.PrimaryKey.Columns, "")}"); this.AddName(DbCommandType.UpdateMetadata, stringBuilder.ToString()); }
private void CreateUpdateCommandText(bool hasMutableColumns) { var stringBuilderArguments = new StringBuilder(); var stringBuilderParameters = new StringBuilder(); var stringBuilderParametersValues = new StringBuilder(); string empty = string.Empty; string str1 = SqliteManagementUtils.JoinOneTablesOnParametersValues(this.TableDescription.PrimaryKeys, "[side]"); string str2 = SqliteManagementUtils.JoinTwoTablesOnClause(this.TableDescription.PrimaryKeys, "[c]", "[base]"); string str7 = SqliteManagementUtils.JoinTwoTablesOnClause(this.TableDescription.PrimaryKeys, "[p]", "[side]"); // Generate Update command var stringBuilder = new StringBuilder(); foreach (var mutableColumn in this.TableDescription.GetMutableColumns(false, true)) { var columnName = ParserName.Parse(mutableColumn).Quoted().ToString(); var columnParameterName = ParserName.Parse(mutableColumn).Unquoted().Normalized().ToString(); stringBuilderParametersValues.Append($"{empty}@{columnParameterName} as {columnName}"); stringBuilderArguments.Append($"{empty}{columnName}"); stringBuilderParameters.Append($"{empty}[c].{columnName}"); empty = ", "; } stringBuilder.AppendLine($"INSERT OR REPLACE INTO {tableName.Quoted().ToString()}"); stringBuilder.AppendLine($"({stringBuilderArguments.ToString()})"); stringBuilder.AppendLine($"SELECT {stringBuilderParameters.ToString()} "); stringBuilder.AppendLine($"FROM (SELECT {stringBuilderParametersValues.ToString()}) as [c]"); stringBuilder.AppendLine($"LEFT JOIN {trackingName.Quoted().ToString()} AS [side] ON {str1}"); stringBuilder.AppendLine($"LEFT JOIN {tableName.Quoted().ToString()} AS [base] ON {str2}"); stringBuilder.Append($"WHERE ({SqliteManagementUtils.WhereColumnAndParameters(this.TableDescription.PrimaryKeys, "[base]")} "); stringBuilder.AppendLine($"AND ([side].[timestamp] < @sync_min_timestamp OR [side].[update_scope_id] = @sync_scope_id)) "); stringBuilder.Append($"OR ({SqliteManagementUtils.WhereColumnIsNull(this.TableDescription.PrimaryKeys, "[base]")} "); stringBuilder.AppendLine($"AND ([side].[timestamp] < @sync_min_timestamp OR [side].[timestamp] IS NULL)) "); stringBuilder.AppendLine($"OR @sync_force_write = 1;"); stringBuilder.AppendLine(); stringBuilder.AppendLine($"UPDATE OR IGNORE {trackingName.Quoted().ToString()} SET "); stringBuilder.AppendLine("[update_scope_id] = @sync_scope_id,"); stringBuilder.AppendLine("[sync_row_is_tombstone] = 0,"); stringBuilder.AppendLine("[last_change_datetime] = datetime('now')"); stringBuilder.AppendLine($"WHERE {SqliteManagementUtils.WhereColumnAndParameters(this.TableDescription.PrimaryKeys, "")}"); stringBuilder.AppendLine($" AND (select changes()) > 0;"); var cmdtext = stringBuilder.ToString(); this.AddName(DbCommandType.UpdateRow, cmdtext); }
/// <summary> /// For a foreign key, check if the Parent table exists /// </summary> private bool EnsureForeignKeysTableExist(DmRelation foreignKey) { var childTable = foreignKey.ChildTable; var parentTable = foreignKey.ParentTable; // The foreignkey comes from the child table var ds = foreignKey.ChildTable.DmSet; if (ds == null) { return(false); } // Check if the parent table is part of the sync configuration var exist = ds.Tables.Any(t => ds.IsEqual(t.TableName, parentTable.TableName)); if (!exist) { return(false); } bool alreadyOpened = connection.State == ConnectionState.Open; try { if (!alreadyOpened) { connection.Open(); } return(SqliteManagementUtils.TableExists(connection, transaction, parentTable.TableName)); } catch (Exception ex) { Debug.WriteLine($"Error during EnsureForeignKeysTableExist : {ex}"); throw; } finally { if (!alreadyOpened && connection.State != ConnectionState.Closed) { connection.Close(); } } }
private void CreateInitializeCommandText() { var stringBuilderArguments = new StringBuilder(); var stringBuilderParameters = new StringBuilder(); var stringBuilderParametersValues = new StringBuilder(); var stringBuilderParametersValues2 = new StringBuilder(); string empty = string.Empty; string str1 = SqliteManagementUtils.JoinOneTablesOnParametersValues(this.TableDescription.PrimaryKeys, "[side]"); string str2 = SqliteManagementUtils.JoinOneTablesOnParametersValues(this.TableDescription.PrimaryKeys, "[base]"); string str7 = SqliteManagementUtils.JoinTwoTablesOnClause(this.TableDescription.PrimaryKeys, "[p]", "[side]"); // Generate Update command var stringBuilder = new StringBuilder(); foreach (var mutableColumn in this.TableDescription.GetMutableColumns(false, true)) { var columnName = ParserName.Parse(mutableColumn).Quoted().ToString(); var columnParameterName = ParserName.Parse(mutableColumn).Unquoted().Normalized().ToString(); stringBuilderParametersValues.Append($"{empty}@{columnParameterName} as {columnName}"); stringBuilderParametersValues2.Append($"{empty}@{columnParameterName}"); stringBuilderArguments.Append($"{empty}{columnName}"); stringBuilderParameters.Append($"{empty}[c].{columnName}"); empty = ", "; } stringBuilder.AppendLine($"INSERT OR IGNORE INTO {tableName.Quoted().ToString()}"); stringBuilder.AppendLine($"({stringBuilderArguments.ToString()})"); stringBuilder.Append($"VALUES ({stringBuilderParametersValues2.ToString()}) "); stringBuilder.AppendLine($";"); stringBuilder.AppendLine($"UPDATE OR IGNORE {trackingName.Quoted().ToString()} SET "); stringBuilder.AppendLine("[update_scope_id] = @sync_scope_id,"); stringBuilder.AppendLine("[sync_row_is_tombstone] = 0,"); stringBuilder.AppendLine("[last_change_datetime] = datetime('now')"); stringBuilder.AppendLine($"WHERE {SqliteManagementUtils.WhereColumnAndParameters(this.TableDescription.PrimaryKeys, "")}"); stringBuilder.Append($" AND (select changes()) > 0"); stringBuilder.AppendLine($";"); var cmdtext = stringBuilder.ToString(); this.AddCommandName(DbCommandType.InsertRow, cmdtext); this.AddCommandName(DbCommandType.InsertRows, cmdtext); }
private string DeleteTriggerBodyText() { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendLine(); stringBuilder.AppendLine("BEGIN"); stringBuilder.AppendLine($"UPDATE {trackingName.QuotedString} "); 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] = {SqliteObjectNames.TimestampValue}"); stringBuilder.AppendLine($"\t,[timestamp] = {SqliteObjectNames.TimestampValue}"); stringBuilder.AppendLine("\t,[last_change_datetime] = datetime('now')"); // -------------------------------------------------------------------------------- // SQLITE doesnot support (yet) filtering columns, since it's only a client provider // -------------------------------------------------------------------------------- //// Filter columns //if (this.Filters != null) //{ // for (int i = 0; i < this.Filters.Count; i++) // { // var filterColumn = this.Filters[i]; // if (this.tableDescription.PrimaryKey.Columns.Any(c => c.ColumnName == filterColumn.ColumnName)) // continue; // ObjectNameParser columnName = new ObjectNameParser(filterColumn.ColumnName); // stringBuilder.AppendLine($"\t,{columnName.QuotedString} = [d].{columnName.QuotedString}"); // } // stringBuilder.AppendLine(); //} stringBuilder.Append($"WHERE "); stringBuilder.Append(SqliteManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKey.Columns, trackingName.QuotedString, "old")); stringBuilder.AppendLine(";"); stringBuilder.AppendLine("END;"); return(stringBuilder.ToString()); }
private void CreateUpdateUntrackedRowsCommand() { var stringBuilder = new StringBuilder(); var str1 = new StringBuilder(); var str2 = new StringBuilder(); var str3 = new StringBuilder(); var str4 = SqliteManagementUtils.JoinTwoTablesOnClause(this.TableDescription.PrimaryKeys, "[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, {SqliteObjectNames.TimestampValue}, datetime('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(); this.AddName(DbCommandType.UpdateUntrackedRows, r); }
public bool NeedToCreateTrackingTable() { return(!SqliteManagementUtils.TableExists(connection, transaction, trackingName.QuotedString)); }
private void CreateUpdateCommandText() { var stringBuilderArguments = new StringBuilder(); var stringBuilderParameters = new StringBuilder(); var stringBuilderParametersValues = new StringBuilder(); string empty = string.Empty; string str1 = SqliteManagementUtils.JoinOneTablesOnParametersValues(this.TableDescription.PrimaryKeys, "[side]"); string str2 = SqliteManagementUtils.JoinOneTablesOnParametersValues(this.TableDescription.PrimaryKeys, "[base]"); // Generate Update command var stringBuilder = new StringBuilder(); foreach (var mutableColumn in this.TableDescription.GetMutableColumns(false, true)) { var columnName = ParserName.Parse(mutableColumn).Quoted().ToString(); var columnParameterName = ParserName.Parse(mutableColumn).Unquoted().Normalized().ToString(); stringBuilderParametersValues.Append($"{empty}@{columnParameterName} as {columnName}"); stringBuilderArguments.Append($"{empty}{columnName}"); stringBuilderParameters.Append($"{empty}[c].{columnName}"); empty = "\n, "; } // create update statement without PK var emptyUpdate = string.Empty; var columnsToUpdate = false; var stringBuilderUpdateSet = new StringBuilder(); foreach (var mutableColumn in this.TableDescription.GetMutableColumns(false, false)) { var columnName = ParserName.Parse(mutableColumn).Quoted().ToString(); stringBuilderUpdateSet.Append($"{emptyUpdate}{columnName}=excluded.{columnName}"); emptyUpdate = "\n, "; columnsToUpdate = true; } var primaryKeys = string.Join(",", this.TableDescription.PrimaryKeys.Select(name => ParserName.Parse(name).Quoted().ToString())); // add CTE stringBuilder.AppendLine($"WITH CHANGESET as (SELECT {stringBuilderParameters.ToString()} "); stringBuilder.AppendLine($"FROM (SELECT {stringBuilderParametersValues.ToString()}) as [c]"); stringBuilder.AppendLine($"LEFT JOIN {trackingName.Quoted().ToString()} AS [side] ON {str1}"); stringBuilder.AppendLine($"LEFT JOIN {tableName.Quoted().ToString()} AS [base] ON {str2}"); //stringBuilder.AppendLine($"WHERE ({SqliteManagementUtils.WhereColumnAndParameters(this.TableDescription.PrimaryKeys, "[base]")} "); stringBuilder.AppendLine($"WHERE ([side].[timestamp] < @sync_min_timestamp OR [side].[update_scope_id] = @sync_scope_id) "); stringBuilder.Append($"OR ({SqliteManagementUtils.WhereColumnIsNull(this.TableDescription.PrimaryKeys, "[base]")} "); stringBuilder.AppendLine($"AND ([side].[timestamp] < @sync_min_timestamp OR [side].[timestamp] IS NULL)) "); stringBuilder.Append($"OR @sync_force_write = 1"); stringBuilder.AppendLine($")"); stringBuilder.AppendLine($"INSERT INTO {tableName.Quoted().ToString()}"); stringBuilder.AppendLine($"({stringBuilderArguments.ToString()})"); // use CTE here. The CTE is required in order to make the "ON CONFLICT" statement work. Otherwise SQLite cannot parse it // Note, that we have to add the pseudo WHERE TRUE clause here, as otherwise the SQLite parser may confuse the following ON // with a join clause, thus, throwing a parsing error // See a detailed explanation here at the official SQLite documentation: "Parsing Ambiguity" on page https://www.sqlite.org/lang_UPSERT.html stringBuilder.AppendLine($" SELECT * from CHANGESET WHERE TRUE"); if (columnsToUpdate) { stringBuilder.AppendLine($" ON CONFLICT ({primaryKeys}) DO UPDATE SET "); stringBuilder.Append(stringBuilderUpdateSet.ToString()).AppendLine(";"); } else { stringBuilder.AppendLine($" ON CONFLICT ({primaryKeys}) DO NOTHING; "); } stringBuilder.AppendLine(); stringBuilder.AppendLine(); stringBuilder.AppendLine($"UPDATE OR IGNORE {trackingName.Quoted().ToString()} SET "); stringBuilder.AppendLine($"[update_scope_id] = @sync_scope_id,"); stringBuilder.AppendLine($"[sync_row_is_tombstone] = 0,"); stringBuilder.AppendLine($"[timestamp] = {SqliteObjectNames.TimestampValue},"); stringBuilder.AppendLine($"[last_change_datetime] = datetime('now')"); stringBuilder.AppendLine($"WHERE {SqliteManagementUtils.WhereColumnAndParameters(this.TableDescription.PrimaryKeys, "")}"); stringBuilder.AppendLine($" AND (select changes()) > 0;"); var cmdtext = stringBuilder.ToString(); this.AddCommandName(DbCommandType.UpdateRow, cmdtext); this.AddCommandName(DbCommandType.UpdateRows, cmdtext); }
/// <summary> /// Check if we need to create the table in the current database /// </summary> public async Task <bool> NeedToCreateTableAsync() => !(await SqliteManagementUtils.TableExistsAsync(connection, transaction, tableName).ConfigureAwait(false));
/// <summary> /// Check if we need to create the table in the current database /// </summary> public bool NeedToCreateTable() => !SqliteManagementUtils.TableExists(connection, transaction, tableName);
public async Task <bool> NeedToCreateTrackingTableAsync(DbConnection connection, DbTransaction transaction) => !await SqliteManagementUtils.TableExistsAsync((SqliteConnection)connection, (SqliteTransaction)transaction, trackingName).ConfigureAwait(false);
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)); }
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 -- scope id is always NULL when update is made locally"); stringBuilder.AppendLine($"\t\t,[timestamp] = {SqliteObjectNames.TimestampValue}"); stringBuilder.AppendLine("\t\t,[last_change_datetime] = datetime('now')"); stringBuilder.Append($"\tWhere "); stringBuilder.Append(SqliteManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, 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($"; "); var stringBuilderArguments = new StringBuilder(); var stringBuilderArguments2 = new StringBuilder(); var stringPkAreNull = new StringBuilder(); string argComma = string.Empty; string argAnd = string.Empty; stringBuilder.AppendLine($"\tINSERT OR IGNORE INTO {trackingName.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}{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]"); stringBuilder.AppendLine("\t) "); stringBuilder.AppendLine("\tVALUES ("); stringBuilder.Append(stringBuilderArguments2.ToString()); stringBuilder.AppendLine("\t\t,NULL"); stringBuilder.AppendLine($"\t\t,{SqliteObjectNames.TimestampValue}"); stringBuilder.AppendLine("\t\t,0"); stringBuilder.AppendLine("\t\t,datetime('now')"); stringBuilder.AppendLine("\t);"); stringBuilder.AppendLine($"End; "); return(stringBuilder.ToString()); }