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