public virtual async Task <bool> NeedToCreateTriggerAsync(DbTriggerType type, DbConnection connection, DbTransaction transaction) { var updTriggerName = this.sqlObjectNames.GetCommandName(DbCommandType.UpdateTrigger).name; var delTriggerName = this.sqlObjectNames.GetCommandName(DbCommandType.DeleteTrigger).name; var insTriggerName = this.sqlObjectNames.GetCommandName(DbCommandType.InsertTrigger).name; 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(!await NpgsqlManagementUtils.TriggerExistsAsync((NpgsqlConnection)connection, (NpgsqlTransaction)transaction, triggerName).ConfigureAwait(false)); }
/// <summary> /// Check if we need to create the table in the current database /// </summary> public async Task <bool> NeedToCreateSchemaAsync() { if (string.IsNullOrEmpty(tableName.SchemaName) || tableName.SchemaName.ToLowerInvariant() == "public") { return(false); } return(!await NpgsqlManagementUtils.SchemaExistsAsync(connection, transaction, tableName.SchemaName).ConfigureAwait(false)); }
public override async Task EnsureDatabaseAsync(DbConnection connection, DbTransaction transaction = null) { // Chek if db exists var exists = await NpgsqlManagementUtils.DatabaseExistsAsync(connection as NpgsqlConnection, transaction as NpgsqlTransaction).ConfigureAwait(false); if (!exists) { throw new MissingDatabaseException(connection.Database); } }
private string DeleteTriggerBodyText() { var stringBuilder = new StringBuilder(); stringBuilder.AppendLine(); stringBuilder.AppendLine("SET NOCOUNT ON;"); stringBuilder.AppendLine(); stringBuilder.AppendLine("UPDATE [side] "); stringBuilder.AppendLine("SET [sync_row_is_tombstone] = 1"); stringBuilder.AppendLine("\t,[update_scope_id] = NULL -- scope id is always NULL when update is made locally"); stringBuilder.AppendLine("\t,[last_change_datetime] = GetUtcDate()"); stringBuilder.AppendLine($"FROM {trackingName.Schema().Quoted().ToString()} [side]"); stringBuilder.Append($"JOIN DELETED AS [d] ON "); stringBuilder.AppendLine(NpgsqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[side]", "[d]")); stringBuilder.AppendLine(); stringBuilder.AppendLine($"INSERT INTO {trackingName.Schema().Quoted().ToString()} ("); var stringBuilderArguments = new StringBuilder(); var stringBuilderArguments2 = new StringBuilder(); var stringPkAreNull = new StringBuilder(); string argComma = " "; string argAnd = string.Empty; var primaryKeys = this.tableDescription.GetPrimaryKeysColumns(); foreach (var mutableColumn in primaryKeys.Where(c => !c.IsReadOnly)) { var columnName = ParserName.Parse(mutableColumn).Quoted().ToString(); stringBuilderArguments.AppendLine($"\t{argComma}[d].{columnName}"); stringBuilderArguments2.AppendLine($"\t{argComma}{columnName}"); stringPkAreNull.Append($"{argAnd}[side].{columnName} IS NULL"); argComma = ","; argAnd = " AND "; } stringBuilder.Append(stringBuilderArguments2.ToString()); stringBuilder.AppendLine("\t,[update_scope_id]"); stringBuilder.AppendLine("\t,[sync_row_is_tombstone]"); stringBuilder.AppendLine("\t,[last_change_datetime]"); stringBuilder.AppendLine(") "); stringBuilder.AppendLine("SELECT"); stringBuilder.Append(stringBuilderArguments.ToString()); stringBuilder.AppendLine("\t,NULL"); stringBuilder.AppendLine("\t,1"); stringBuilder.AppendLine("\t,GetUtcDate()"); stringBuilder.AppendLine("FROM DELETED [d]"); stringBuilder.Append($"LEFT JOIN {trackingName.Schema().Quoted().ToString()} [side] ON "); stringBuilder.AppendLine(NpgsqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[d]", "[side]")); stringBuilder.Append("WHERE "); stringBuilder.AppendLine(stringPkAreNull.ToString()); return(stringBuilder.ToString()); }
public async Task <bool> NeedToCreateForeignKeyConstraintsAsync(SyncRelation relation) { // Don't want foreign key on same table since it could be a problem on first // sync. We are not sure that parent row will be inserted in first position //if (relation.GetParentTable() == relation.GetTable()) // return false; string tableName = relation.GetTable().TableName; string schemaName = relation.GetTable().SchemaName; string fullName = string.IsNullOrEmpty(schemaName) ? tableName : $"{schemaName}.{tableName}"; var relationName = NormalizeRelationName(relation.RelationName); bool alreadyOpened = connection.State == ConnectionState.Open; try { if (!alreadyOpened) { await connection.OpenAsync().ConfigureAwait(false); } var syncTable = await NpgsqlManagementUtils.GetRelationsForTableAsync(connection, transaction, tableName, schemaName).ConfigureAwait(false); var foreignKeyExist = syncTable.Rows.Any(r => string.Equals(r["ForeignKey"].ToString(), relationName, SyncGlobalization.DataSourceStringComparison)); return(!foreignKeyExist); } catch (Exception ex) { Debug.WriteLine($"Error during checking foreign keys: {ex}"); throw; } finally { if (!alreadyOpened && connection.State != ConnectionState.Closed) { connection.Close(); } } }
public virtual async Task <bool> NeedToCreateClientScopeInfoTableAsync() { var command = connection.CreateCommand(); if (transaction != null) { command.Transaction = transaction; } bool alreadyOpened = connection.State == ConnectionState.Open; try { if (!alreadyOpened) { await connection.OpenAsync().ConfigureAwait(false); } var exist = await NpgsqlManagementUtils.TableExistsAsync(connection, transaction, this.scopeTableName.Quoted().ToString()); return(!exist); } catch (Exception ex) { Debug.WriteLine($"Error during NeedToCreateScopeInfoTable command : {ex}"); throw; } finally { if (!alreadyOpened && connection.State != ConnectionState.Closed) { connection.Close(); } if (command != null) { command.Dispose(); } } }
/// <summary> /// Check if we need to create the table in the current database /// </summary> public async Task <bool> NeedToCreateTableAsync() { return(!await NpgsqlManagementUtils.TableExistsAsync(connection, transaction, tableName.Schema().Quoted().ToString()).ConfigureAwait(false)); }
public virtual async Task <bool> NeedToCreateClientScopeInfoTableAsync(DbConnection connection, DbTransaction transaction) { var exist = await NpgsqlManagementUtils.TableExistsAsync((NpgsqlConnection)connection, (NpgsqlTransaction)transaction, this.scopeTableName.Quoted().ToString()); return(!exist); }
public override async Task <(string DatabaseName, string Version)> GetHelloAsync(DbConnection connection, DbTransaction transaction = null) { return(await NpgsqlManagementUtils.GetHelloAsync(connection as NpgsqlConnection, transaction as NpgsqlTransaction).ConfigureAwait(false)); }
private string UpdateTriggerBodyText() { var stringBuilder = new StringBuilder(); stringBuilder.AppendLine(); stringBuilder.AppendLine("SET NOCOUNT ON;"); stringBuilder.AppendLine(); stringBuilder.AppendLine("UPDATE [side] "); stringBuilder.AppendLine("SET \t[update_scope_id] = NULL -- since the update if from local, it's a NULL"); stringBuilder.AppendLine("\t,[last_change_datetime] = GetUtcDate()"); stringBuilder.AppendLine(); stringBuilder.AppendLine($"FROM {trackingName.Schema().Quoted().ToString()} [side]"); stringBuilder.Append($"JOIN INSERTED AS [i] ON "); stringBuilder.AppendLine(NpgsqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[side]", "[i]")); if (this.tableDescription.GetMutableColumns().Count() > 0) { stringBuilder.Append($"JOIN DELETED AS [d] ON "); stringBuilder.AppendLine(NpgsqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[d]", "[i]")); stringBuilder.AppendLine("WHERE ("); string or = ""; foreach (var column in this.tableDescription.GetMutableColumns()) { var quotedColumn = ParserName.Parse(column).Quoted().ToString(); stringBuilder.Append("\t"); stringBuilder.Append(or); stringBuilder.Append("ISNULL("); stringBuilder.Append("NULLIF("); stringBuilder.Append("[d]."); stringBuilder.Append(quotedColumn); stringBuilder.Append(", "); stringBuilder.Append("[i]."); stringBuilder.Append(quotedColumn); stringBuilder.Append(")"); stringBuilder.Append(", "); stringBuilder.Append("NULLIF("); stringBuilder.Append("[i]."); stringBuilder.Append(quotedColumn); stringBuilder.Append(", "); stringBuilder.Append("[d]."); stringBuilder.Append(quotedColumn); stringBuilder.Append(")"); stringBuilder.AppendLine(") IS NOT NULL"); or = " OR "; } stringBuilder.AppendLine(") "); } stringBuilder.AppendLine($"INSERT INTO {trackingName.Schema().Quoted().ToString()} ("); var stringBuilderArguments = new StringBuilder(); var stringBuilderArguments2 = new StringBuilder(); var stringPkAreNull = new StringBuilder(); string argComma = " "; string argAnd = string.Empty; var primaryKeys = this.tableDescription.GetPrimaryKeysColumns(); foreach (var mutableColumn in primaryKeys.Where(c => !c.IsReadOnly)) { var columnName = ParserName.Parse(mutableColumn).Quoted().ToString(); stringBuilderArguments.AppendLine($"\t{argComma}[i].{columnName}"); stringBuilderArguments2.AppendLine($"\t{argComma}{columnName}"); stringPkAreNull.Append($"{argAnd}[side].{columnName} IS NULL"); argComma = ","; argAnd = " AND "; } stringBuilder.Append(stringBuilderArguments2.ToString()); stringBuilder.AppendLine("\t,[update_scope_id]"); stringBuilder.AppendLine("\t,[sync_row_is_tombstone]"); stringBuilder.AppendLine("\t,[last_change_datetime]"); stringBuilder.AppendLine(") "); stringBuilder.AppendLine("SELECT"); stringBuilder.Append(stringBuilderArguments.ToString()); stringBuilder.AppendLine("\t,NULL"); stringBuilder.AppendLine("\t,0"); stringBuilder.AppendLine("\t,GetUtcDate()"); stringBuilder.AppendLine("FROM INSERTED [i]"); stringBuilder.Append($"LEFT JOIN {trackingName.Schema().Quoted().ToString()} [side] ON "); stringBuilder.AppendLine(NpgsqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[i]", "[side]")); stringBuilder.Append("WHERE "); stringBuilder.AppendLine(stringPkAreNull.ToString()); return(stringBuilder.ToString()); }
private string InsertTriggerBodyText() { //CREATE OR REPLACE FUNCTION "public"."Address_trigger_insert_function"() //RETURNS TRIGGER AS //$BODY$ //BEGIN // UPDATE "side" // SET "sync_row_is_tombstone" = 0 // ,"update_scope_id" = NULL -- scope id is always NULL when update is made locally // ,"last_change_datetime" = GetUtcDate() // FROM "tProduct" "side" // JOIN NEW AS "i" ON "side"."ProductID" = "i"."ProductID"; // INSERT INTO "tProduct" ( // "ProductID" // ,"update_scope_id" // ,"sync_row_is_tombstone" // ,"last_change_datetime" // ) // SELECT // "i"."ProductID" // ,NULL // ,0 // ,GetUtcDate() // FROM NEW AS "i" // LEFT JOIN "tProduct" "side" ON "i"."ProductID" = "side"."ProductID" // WHERE "side"."ProductID" IS NULL; // END; //$BODY$ //LANGUAGE 'plpgsql'; //CREATE TRIGGER "Address_trigger_insert" AFTER INSERT ON "public"."Address" //EXECUTE PROCEDURE "public"."Address_trigger_insert_function"(); var stringBuilder = new StringBuilder(); stringBuilder.AppendLine(); stringBuilder.AppendLine("-- If row was deleted before, it already exists, so just make an update"); stringBuilder.AppendLine("UPDATE side "); stringBuilder.AppendLine("SET sync_row_is_tombstone = 0"); stringBuilder.AppendLine("\t,update_scope_id = NULL -- scope id is always NULL when update is made locally"); stringBuilder.AppendLine("\t,last_change_datetime = now()"); stringBuilder.AppendLine($"FROM {trackingName.Schema().Quoted().ToString()} AS side"); stringBuilder.Append($"JOIN NEW AS i ON "); stringBuilder.AppendLine(NpgsqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "side", "i")); stringBuilder.AppendLine(); stringBuilder.AppendLine($"INSERT INTO {trackingName.Schema().Quoted().ToString()} ("); var stringBuilderArguments = new StringBuilder(); var stringBuilderArguments2 = new StringBuilder(); var stringPkAreNull = new StringBuilder(); string argComma = " "; string argAnd = string.Empty; var primaryKeys = this.tableDescription.GetPrimaryKeysColumns(); foreach (var mutableColumn in primaryKeys.Where(c => !c.IsReadOnly)) { var columnName = ParserName.Parse(mutableColumn).Quoted().ToString(); stringBuilderArguments.AppendLine($"\t{argComma}[i].{columnName}"); stringBuilderArguments2.AppendLine($"\t{argComma}{columnName}"); stringPkAreNull.Append($"{argAnd}side.{columnName} IS NULL"); argComma = ","; argAnd = " AND "; } stringBuilder.Append(stringBuilderArguments2.ToString()); stringBuilder.AppendLine("\t,update_scope_id"); stringBuilder.AppendLine("\t,sync_row_is_tombstone"); stringBuilder.AppendLine("\t,last_change_datetime"); stringBuilder.AppendLine(") "); stringBuilder.AppendLine("SELECT"); stringBuilder.Append(stringBuilderArguments.ToString()); stringBuilder.AppendLine("\t,NULL"); stringBuilder.AppendLine("\t,0"); stringBuilder.AppendLine("\t,now() at time zone 'utc'"); stringBuilder.AppendLine("FROM NEW i"); stringBuilder.Append($"LEFT JOIN {trackingName.Schema().Quoted().ToString()} side ON "); stringBuilder.AppendLine(NpgsqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "i", "side")); stringBuilder.Append("WHERE "); stringBuilder.AppendLine(stringPkAreNull.ToString()); return(stringBuilder.ToString()); }
public async Task <bool> NeedToCreateTrackingTableAsync(DbConnection connection, DbTransaction transaction) => !await NpgsqlManagementUtils.TableExistsAsync((NpgsqlConnection)connection, (NpgsqlTransaction)transaction, trackingName.Schema().Quoted().ToString()).ConfigureAwait(false);