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