private string CreateUpdateMetadataCommand()
{
var stringBuilder = new StringBuilder();
var pkeysForUpdate = new StringBuilder();
var pkeySelectForInsert = new StringBuilder();
var pkeyISelectForInsert = new StringBuilder();
var pkeyAliasSelectForInsert = new StringBuilder();
var pkeysLeftJoinForInsert = new StringBuilder();
var pkeysIsNullForInsert = new StringBuilder();
string and = string.Empty;
string comma = string.Empty;
foreach (var pkColumn in TableDescription.GetPrimaryKeysColumns())
{
var columnName = ParserName.Parse(pkColumn).Quoted().ToString();
var parameterName = ParserName.Parse(pkColumn).Unquoted().Normalized().ToString();
pkeysForUpdate.Append($"{and}[side].{columnName} = @{parameterName}");
pkeySelectForInsert.Append($"{comma}{columnName}");
pkeyISelectForInsert.Append($"{comma}[i].{columnName}");
pkeyAliasSelectForInsert.Append($"{comma}@{parameterName} as {columnName}");
pkeysLeftJoinForInsert.Append($"{and}[side].{columnName} = [i].{columnName}");
pkeysIsNullForInsert.Append($"{and}[side].{columnName} IS NULL");
and = " AND ";
comma = ", ";
}
stringBuilder.AppendLine($"UPDATE [side] SET ");
stringBuilder.AppendLine($" [update_scope_id] = @sync_scope_id, ");
stringBuilder.AppendLine($" [sync_row_is_tombstone] = @sync_row_is_tombstone, ");
stringBuilder.AppendLine($" [last_change_datetime] = GETUTCDATE() ");
stringBuilder.AppendLine($"FROM {trackingName.Schema().Quoted().ToString()} [side]");
stringBuilder.Append($"WHERE ");
stringBuilder.Append(pkeysForUpdate.ToString());
stringBuilder.AppendLine($";");
stringBuilder.AppendLine();
stringBuilder.AppendLine($"INSERT INTO {trackingName.Schema().Quoted().ToString()} (");
stringBuilder.AppendLine(pkeySelectForInsert.ToString());
stringBuilder.AppendLine(",[update_scope_id], [sync_row_is_tombstone],[last_change_datetime] )");
stringBuilder.AppendLine($"SELECT {pkeyISelectForInsert.ToString()} ");
stringBuilder.AppendLine($" , i.sync_scope_id, i.sync_row_is_tombstone, i.UtcDate");
stringBuilder.AppendLine("FROM (");
stringBuilder.AppendLine($" SELECT {pkeyAliasSelectForInsert}");
stringBuilder.AppendLine($" ,@sync_scope_id as sync_scope_id, @sync_row_is_tombstone as sync_row_is_tombstone, GETUTCDATE() as UtcDate) as i");
stringBuilder.AppendLine($"LEFT JOIN {trackingName.Schema().Quoted().ToString()} [side] ON {pkeysLeftJoinForInsert.ToString()} ");
stringBuilder.AppendLine($"WHERE {pkeysIsNullForInsert.ToString()};");
return(stringBuilder.ToString());
}
private void CreateUpdateMetadataCommandText()
{
var stringBuilder = new StringBuilder();
var pkeySelectForInsert = new StringBuilder();
var pkeyISelectForInsert = new StringBuilder();
var pkeyAliasSelectForInsert = new StringBuilder();
var pkeysLeftJoinForInsert = new StringBuilder();
var pkeysIsNullForInsert = new StringBuilder();
string and = string.Empty;
string comma = string.Empty;
foreach (var pkColumn in TableDescription.GetPrimaryKeysColumns())
{
var columnName = ParserName.Parse(pkColumn).Quoted().ToString();
var parameterName = ParserName.Parse(pkColumn).Unquoted().Normalized().ToString();
pkeySelectForInsert.Append($"{comma}{columnName}");
pkeyISelectForInsert.Append($"{comma}[i].{columnName}");
pkeyAliasSelectForInsert.Append($"{comma}@{parameterName} as {columnName}");
pkeysLeftJoinForInsert.Append($"{and}[side].{columnName} = [i].{columnName}");
pkeysIsNullForInsert.Append($"{and}[side].{columnName} IS NULL");
and = " AND ";
comma = ", ";
}
stringBuilder.AppendLine($"INSERT OR REPLACE INTO {trackingName.Schema().Quoted().ToString()} (");
stringBuilder.AppendLine(pkeySelectForInsert.ToString());
stringBuilder.AppendLine(",[update_scope_id], [sync_row_is_tombstone], [timestamp], [last_change_datetime] )");
stringBuilder.AppendLine($"SELECT {pkeyISelectForInsert.ToString()} ");
stringBuilder.AppendLine($" , i.sync_scope_id, i.sync_row_is_tombstone, i.sync_timestamp, i.UtcDate");
stringBuilder.AppendLine("FROM (");
stringBuilder.AppendLine($" SELECT {pkeyAliasSelectForInsert}");
stringBuilder.AppendLine($" ,@sync_scope_id as sync_scope_id, @sync_row_is_tombstone as sync_row_is_tombstone, {SqliteObjectNames.TimestampValue} as sync_timestamp, datetime('now') as UtcDate) as i;");
var cmdtext = stringBuilder.ToString();
this.AddCommandName(DbCommandType.UpdateMetadata, cmdtext);
}
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);
}
private string CreateUpdateUntrackedRowsCommand()
{
var stringBuilder = new StringBuilder();
var str1 = new StringBuilder();
var str2 = new StringBuilder();
var str3 = new StringBuilder();
var str4 = MySqlManagementUtils.JoinTwoTablesOnClause(this.TableDescription.GetPrimaryKeysColumns(), "`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, {MySqlObjectNames.TimestampValue}, 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();
return(r);
}