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