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 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());
}
/// <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)
{
Logger.Current.Error($"Error during EnsureForeignKeysTableExist : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
public bool NeedToCreateTrigger(DbTriggerType type, DbBuilderOption option)
{
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);
if (option.HasFlag(DbBuilderOption.CreateOrUseExistingSchema))
{
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));
}
if (option.HasFlag(DbBuilderOption.UseExistingSchema))
{
return(false);
}
return(false);
}
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());
}
