Multiple Value Sync Filtering

Jul 17, 2014 at 10:12 PM
I have a database with a huge number of rows that I am hoping to filter with the sync tools. The problem is that I need to feed multiple values to the filter tools so that I can get the right rows. For example I have
context.AddScopeParameters("ColumnA","ValueB");
but I need to be able to do to
context.AddScopeParameters("ColumnA","ValueA");
context.AddScopeParameters("ColumnA","ValueB");
context.AddScopeParameters("ColumnA","ValueC");
I know that AddScopeParameters just adds a value to a dictionary and eventually that is turned into the url for the sync server but is it possible for me to change the code to support multiple values per key or will the sync server complain when you give it multiple values?
Jul 22, 2014 at 12:01 AM
I came up with a somewhat sketch solution to my problem but it works. Basically the sync service receives the filter arguments in a URL which would look like
www.yoursite.com/yourserver.svc?filter1=value1&filter2=value1
However the value for the filtering is passed around as a string up until the stored procedure is run. This means that with a little bit of tweaking you can pass the server a URL written like
www.yoursite.com/yourserver.svc?filter1=value1,value2&filter2=value2,value3
In order to do this though a number of things have to be changed. First the when the server declares the sync parameter configuration it has to declare the type of the filter as a string no matter what the actual type of the column is. For example use:
config.AddFilterParameterConfiguration("filter1", "MyTable", "@FilterColumn", typeof(System.String));
even if the type of FilterColumn is a GUID. Next you have to modify the stored procedure to support splitting up the comma separated list of filters. To do this go to your database -> Programmability -> StoredProcedures and right click on the procedure named after the table you are filtering and followed by "_selectchanges" and click modify. You will have to merge your version of the script with what I have below but it should be pretty self explanatory. My example turns the filter into a list of GUIDs, but it would be fairly easy to modify it to support any other SQL type:
USE [my_database]
GO
/****** Object:  StoredProcedure [dbo].[MyTable_selectchanges]    Script Date: 07/21/2014 14:55:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT 1 FROM Information_schema.Routines
           WHERE Specific_schema = 'dbo'
             AND specific_name = 'GUIDSplit'
             AND Routine_Type = 'FUNCTION') 
    DROP FUNCTION dbo.GUIDSplit
GO

CREATE FUNCTION dbo.GUIDSplit(@Str NVARCHAR(MAX))
RETURNS @Parts TABLE (Part uniqueidentifier)
AS
BEGIN
    DECLARE @NextComma INT SET @NextComma = -1
    DECLARE @LastComma INT SET @LastComma = 0
    WHILE (@NextComma != 0)
    BEGIN
        SET @NextComma = CHARINDEX(',', @Str, @LastComma + 1)
        IF (@NextComma = 0)
            INSERT INTO @Parts(Part) VALUES (convert(uniqueidentifier, LTrim(RTrim(RIGHT(@Str, LEN(@Str) - @LastComma))))) 
        ELSE
            INSERT INTO @Parts(Part) VALUES (convert(uniqueidentifier, LTrim(RTrim(SUBSTRING(@Str, @LastComma + 1, @NextComma - @LastComma - 1)))))
        SET @LastComma = @NextComma
    END
    RETURN
END
GO

ALTER PROCEDURE [dbo].[MyTable_selectchanges]
    @sync_min_timestamp BigInt,
    @sync_scope_local_id Int,
    @sync_scope_restore_count Int,
    @sync_update_peer_key Int,
    @FilterColumn NVARCHAR(MAX)
AS
BEGIN

SELECT [side].[FilterColumn], [side].[sync_row_is_tombstone], [side].[local_update_peer_timestamp] as sync_row_timestamp, case when ([side].[update_scope_local_id] is null or [side].[update_scope_local_id] <> @sync_scope_local_id) then COALESCE([side].[restore_timestamp], [side].[local_update_peer_timestamp]) else [side].[scope_update_peer_timestamp] end as sync_update_peer_timestamp, case when ([side].[update_scope_local_id] is null or [side].[update_scope_local_id] <> @sync_scope_local_id) then case when ([side].[local_update_peer_key] > @sync_scope_restore_count) then @sync_scope_restore_count else [side].[local_update_peer_key] end else [side].[scope_update_peer_key] end as sync_update_peer_key, case when ([side].[create_scope_local_id] is null or [side].[create_scope_local_id] <> @sync_scope_local_id) then [side].[local_create_peer_timestamp] else [side].[scope_create_peer_timestamp] end as sync_create_peer_timestamp, case when ([side].[create_scope_local_id] is null or [side].[create_scope_local_id] <> @sync_scope_local_id) then case when ([side].[local_create_peer_key] > @sync_scope_restore_count) then @sync_scope_restore_count else [side].[local_create_peer_key] end else [side].[scope_create_peer_key] end as sync_create_peer_key FROM [Feature] [base] RIGHT JOIN [Feature_tracking] [side] ON [base].[Feature_GUID] = [side].[Feature_GUID] WHERE (([side].FilterColumn IN (select part from dbo.GUIDSplit(@FilterColumn))) 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].[FilterColumn] IS NULL)) AND  ([side].[update_scope_local_id] IS NULL OR [side].[update_scope_local_id] <> @sync_scope_local_id OR ([side].[update_scope_local_id] = @sync_scope_local_id AND [side].[scope_update_peer_key] <> @sync_update_peer_key)) AND [side].[local_update_peer_timestamp] > @sync_min_timestamp
END
GO
The important parts of the script are where the string split function is declared:
IF EXISTS (SELECT 1 FROM Information_schema.Routines
           WHERE Specific_schema = 'dbo'
             AND specific_name = 'GUIDSplit'
             AND Routine_Type = 'FUNCTION') 
    DROP FUNCTION dbo.GUIDSplit
GO

CREATE FUNCTION dbo.GUIDSplit(@Str NVARCHAR(MAX))
RETURNS @Parts TABLE (Part uniqueidentifier)
AS
BEGIN
    DECLARE @NextComma INT SET @NextComma = -1
    DECLARE @LastComma INT SET @LastComma = 0
    WHILE (@NextComma != 0)
    BEGIN
        SET @NextComma = CHARINDEX(',', @Str, @LastComma + 1)
        IF (@NextComma = 0)
            INSERT INTO @Parts(Part) VALUES (convert(uniqueidentifier, LTrim(RTrim(RIGHT(@Str, LEN(@Str) - @LastComma))))) 
        ELSE
            INSERT INTO @Parts(Part) VALUES (convert(uniqueidentifier, LTrim(RTrim(SUBSTRING(@Str, @LastComma + 1, @NextComma - @LastComma - 1)))))
        SET @LastComma = @NextComma
    END
    RETURN
END
GO
and where the split function is used in the main select statement (this replaces what comes immediately after the WHERE clause):
([side].FilterColumn IN (select part from dbo.GUIDSplit(@FilterColumn)))
Replaces:
([side].FilterColumn = @FilterColumn)
Most of the information that I needed to come up with this came from:
Enabling synchronization and filters within a Windows 8.1 application, SQLite and the Sync Toolkit
and help with parsing the comma seperated list came from:
SQL Server 2008 - Loop through/split a delimited string