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