none
Update filter scope after schema change without reprovsion

    Question

  • Hello

    I have configured synchronization between (SQL Server 2012 and SQL Server CE) with Sync Framework, using a filter template. 

    For the future I need to be able to update the provision scope information when the database schema changes (mainly adding new columns to existing tables).

    I do not want to deprovision and reprovision again, so is there a solution for this?


    Right now I am using the solution described here with some additional steps:

    - I use 

    new SqlSyncScopeProvisioning(sqlConnection, scopeDesc, SqlSyncScopeProvisioningType.Template);

    before retrieving the provision script

    - I need to manually insert the filter information to all the SqlSyncProviderScopeConfiguration

    - I need to manually insert the filter information to all SelectChanges SP

    Is there a way to avoid the last steps, or perform them using the MSSync APi?

    Thursday, July 17, 2014 9:28 AM

All replies

  • there's nothing in the API to alter scope definitions

    Tuesday, July 22, 2014 2:05 AM
    Moderator
  • I have used this code and it works in my case. I use a filter on column CompanyNo for each tables that are under sync scope.

    protected override void InternalUpdateProvisionSchema(string scopeName, string newConfigData = "")
    {
        _log.Verbose("UpdateProvisionSchema: " + scopeName);
    
        var scopeDesc = GetScopeDescription(scopeName);
        if (scopeDesc == null)
            throw new Exception("Scope was not found.");
    
        var isFilterScope = SyncData.IsFilterScope(scopeName);
    
        try
        {
            var script = GetGeneratedProvisionScript(scopeDesc);
            var dropCommands = CreateDropStatments(scopeDesc);
            var provisionScript = dropCommands.Append(script).ToString();
    
            //update scope_config table with the new data
            var newConfigEntry = CreateNewConfigEntry(provisionScript, isFilterScope);
            var newProvisionScript = AddNewConfigEntryToScript(provisionScript, newConfigEntry, scopeName);
    
            //remove insert scripts for scope_config data (it already exists, because we do not deprovision and above it is updated to the latest values)
            newProvisionScript = RemoveInsertsForScopeInfoAndScopeConfig(newProvisionScript);
    
            if (isFilterScope)
            {
                var tableName = SyncData.FilteredScopes[scopeName];
                newProvisionScript = UpdateStoreProcedure(newProvisionScript, tableName);
            }
            ExecuteSqlCommands(newProvisionScript);
    
        }
        catch (Exception ex)
        {
            _log.Error(ex, string.Format("Failed to upgrade schema for scope {0}", scopeName));
            throw new Exception(string.Format("Update of provisioned schema failed for scope: {0}", scopeName));
        }
    }
    
    private StringBuilder CreateDropStatments(DbSyncScopeDescription scopeDesc)
    {
        var stringBuilder = new StringBuilder();
    
        foreach (var table in scopeDesc.Tables)
        {
            string tableName = table.UnquotedLocalName;
            stringBuilder.AppendLine(string.Format("DROP PROCEDURE [{0}_insert];", tableName));
            stringBuilder.AppendLine(string.Format("DROP PROCEDURE [{0}_update];", tableName));
            stringBuilder.AppendLine(string.Format("DROP PROCEDURE [{0}_delete];", tableName));
    
            stringBuilder.AppendLine(string.Format("DROP PROCEDURE [{0}_insertmetadata];", tableName));
            stringBuilder.AppendLine(string.Format("DROP PROCEDURE [{0}_updatemetadata];", tableName));
            stringBuilder.AppendLine(string.Format("DROP PROCEDURE [{0}_deletemetadata];", tableName));
    
            stringBuilder.AppendLine(string.Format("DROP PROCEDURE [{0}_selectchanges];", tableName));
            stringBuilder.AppendLine(string.Format("DROP PROCEDURE [{0}_selectrow];", tableName));
        }
    
        return stringBuilder;
    }
    
    /// <summary>
    /// We simulate the provision based on a scope definition (BUT the scope already exists)
    /// in oreder to retrieve the provision script.
    /// 
    /// set SetCreateProceduresDefault so Sync Fx thinks this is a new scope
    /// => create a set of stored procedures that insert, update, and delete data and synchronization metadata
    /// => create almost all the objects except triggers and tracking tables)
    /// </summary>
    /// <param name="scopeDesc">The existing scope description.</param>
    /// <returns></returns>
    private string GetGeneratedProvisionScript(DbSyncScopeDescription scopeDesc)
    {
        var provisioningScript = string.Empty;
    
        using (var sqlConnection = new SqlConnection(_connectionString))
        {
            var scopeProvision = new SqlSyncScopeProvisioning(sqlConnection, scopeDesc, SqlSyncScopeProvisioningType.Template);
            scopeProvision.SetCreateProceduresDefault(DbSyncCreationOption.Create);
    
            //sql 2005 does not allow multiple inserts, updates, and deletes
            scopeProvision.SetUseBulkProceduresDefault(false);
    
            //Generates a string that contains the SQL code to provision the database for a particular scope.
            //Script to create store procedures, to populate CONFIG tables etc
            provisioningScript = scopeProvision.Script();
        }
    
        return provisioningScript;
    }
    
    private string CreateNewConfigEntry(string provisionScript, bool isFilterScope)
    {
        //get generated config entry from provision script
        int x = provisionScript.IndexOf("N'<SqlSyncProviderScopeConfiguration");
        int y = provisionScript.IndexOf("</SqlSyncProviderScopeConfiguration>");
        int diff = y - x;
        var configEntry = provisionScript.Substring(x, diff) + "</SqlSyncProviderScopeConfiguration>'";
    
        //in case of a filter scope we need to add the filter information
        var newConfigEntry = configEntry;
        if (isFilterScope)
        {
            newConfigEntry = AddFilterInfoToConfigEntry(configEntry);
        }
    
        return newConfigEntry;
    }
    
    private string AddFilterInfoToConfigEntry(string configEntry)
    {
        var filterInfo =
            "<FilterParam name=\"@CompanyNo\" /><FilterClause>[base].[CompanyNo] = @CompanyNo</FilterClause><FilterCol>CompanyNo</FilterCol>";
    
        var indexOf = configEntry.IndexOf("</Adapter>");
        var newConfigEntry = configEntry.Insert(indexOf, filterInfo);
    
        return newConfigEntry;
    }
    
    /// <summary>
    ///  1. Replace the update scope_config to update the config_data column with the revised scope definition
    ///  2. Get the existing Guid for the scope name.
    ///  3. Update the replace clause.
    /// </summary>
    /// <param name="provisionScript">Old privision script that we want to edit.</param>
    /// <param name="newConfigEntry">Config entry that contains all new values.</param>
    /// <param name="scopeName">The existing scope that we want to edit.</param>
    /// <returns>The update command for the scope_config table.</returns>
    private string AddNewConfigEntryToScript(string provisionScript, string newConfigEntry, string scopeName)
    {
        // replace the update scope_config to update the config_data column with the revised scope definition
        var alterScopeSql = provisionScript.Replace("scope_status = 'C'", "config_data=" + newConfigEntry);
    
        // remove and replace the where clause to update the current scope with the revised config_data
        // we want to keep the old ids
        var indexOf = alterScopeSql.IndexOf("WHERE [config_id] =");
        var diff = alterScopeSql.Length - indexOf;
        alterScopeSql = alterScopeSql.Remove(indexOf, diff);
        alterScopeSql = alterScopeSql
                        + " WHERE [config_id] = (SELECT scope_config_id FROM scope_info WHERE sync_scope_name='"
                        + scopeName + "')";
    
        return alterScopeSql;
    }
    
    /// <summary>
    /// We already have in the database the data entry in scope_info and scope_config tables.
    /// We do not want to insert it again, but instead edit the config entry with new values.
    /// </summary>
    /// <param name="provisionScript"></param>
    /// <returns>The provision script without the insert command.</returns>
    private string RemoveInsertsForScopeInfoAndScopeConfig(string provisionScript)
    {
        // remove the inserts for the scope_info and scope_config
        var x = provisionScript.IndexOf("-- BEGIN Add scope");
        var y = provisionScript.LastIndexOf("-- END Add Scope");
        var diff = y - x;
        var newScript = provisionScript.Remove(x, diff);
    
        return newScript;
    }
    
    private string UpdateStoreProcedure(string provisionScript, string tableName)
    {
        var procedureStart = string.Format("-- BEGIN Select Incremental Changes command for table [{0}]", tableName);
        int x = provisionScript.IndexOf(procedureStart) + procedureStart.Length;
        int y = provisionScript.IndexOf(string.Format("-- END Select Incremental Changes command for table [{0}]", tableName));
        int diff = y - x;
    
        var oldProcedureValue = provisionScript.Substring(x, diff);
        var newProcedureValue = AddFilterConditionToStoreProcedure(oldProcedureValue, "@CompanyNo Int", "(([base].[CompanyNo] = @CompanyNo) OR ([side].[sync_row_is_tombstone] = 1 AND ([side].[update_scope_local_id] = @sync_scope_local_id OR [side].[update_scope_local_id] IS NULL) AND [side].[CompanyNo] IS NULL))");
    
        var scriptWithNewStoreProc = provisionScript.Replace(oldProcedureValue, string.Format("\n{0}\n", newProcedureValue));
    
        return scriptWithNewStoreProc;
    }
    
    private string AddFilterConditionToStoreProcedure(string procedureBlock, string paramDeclaration, string parameterCondition)
    {
        var lastParamString = "@sync_update_peer_key Int";
        var indexOfLastParam = procedureBlock.IndexOf(lastParamString) + lastParamString.Length;
        procedureBlock = procedureBlock.Insert(indexOfLastParam, string.Format(",\r\n\t{0}\n", paramDeclaration));
    
        var whereCondition = string.Format("WHERE {0} AND ", parameterCondition);
        procedureBlock = procedureBlock.Replace("WHERE", whereCondition);
    
        return procedureBlock;
    }

    Monday, July 28, 2014 8:42 AM