create procedure sys.sp_MSmakeorcheck_joinfilter_using_dri
@publication sysname,
@article sysname,
@base_objid int,
@join_objid int,
@join_unique int,
@check_filter bit=0,
@join_filterclause nvarchar(1000)=NULL
AS
/* Declare additional variables */
declare @table_name nvarchar(140)
declare @join_table nvarchar(140)
declare @join_article sysname
declare @filt_name sysname
declare @basecol sysname
declare @joincol sysname
declare @basecolid int
declare @joincolid int
declare @keycnt int
declare @base_columns varbinary(128)
declare @join_columns varbinary(128)
declare @keyindex int
declare @filtclause nvarchar(3000)
declare @filtpiece nvarchar(500)
declare @qual_jointable nvarchar(270)
declare @retcode smallint
declare @pubid uniqueidentifier
declare @first_piece bit
declare @dri_rowcount int
declare @constid int
declare @base_nick int
declare @join_nick int
if @check_filter = 1 and (@join_filterclause is null or @join_filterclause = N' ')
begin
raiserror (14043, 11, -1, '@join_filterclause', 'sp_MSmakeorcheck_joinfilter_using_dri')
return (1)
end
select @pubid = pubid from dbo.sysmergepublications where name = @publication and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name()
if @check_filter = 0
begin
-- making the filters, so consider only columns that are in vertical partition.
select @base_columns=0x00 -- so that no base column name will be returned if following query does not find a match
select @join_columns=0x00 -- same as above
select @base_columns=columns from dbo.sysmergearticles where pubid=@pubid and objid=@base_objid
select @join_columns=columns from dbo.sysmergearticles where pubid=@pubid and objid=@join_objid
end
else
begin
-- checking the filters, so check all columns. if dri happens to be on a column that has been excluded,
-- then return error (can't have logical record relationship for that case).
select @base_columns=NULL
select @join_columns=NULL
end
/* Are we adding join filter on referencing table (@join_unique = 1) or on unique key table ? */
if @join_unique = 1
select @constid = object_id,
@keycnt = (select count(*) from sys.foreign_key_columns k where k.constraint_object_id = f.object_id),
@filt_name = object_name(object_id)
from sys.foreign_keys f where f.parent_object_id = @base_objid and f.referenced_object_id = @join_objid
else
select @constid = object_id,
@keycnt = (select count(*) from sys.foreign_key_columns k where k.constraint_object_id = f.object_id),
@filt_name = object_name(object_id)
from sys.foreign_keys f where f.referenced_object_id = @base_objid and f.parent_object_id = @join_objid
select @dri_rowcount = @@rowcount
/* Set up object names - we use them as correlation values */
set @table_name = QUOTENAME(object_name(@base_objid))
set @join_table = QUOTENAME(object_name(@join_objid))
select @join_article = name, @join_nick = nickname from dbo.sysmergearticles
where objid = @join_objid and pubid=@pubid
select @base_nick = nickname from dbo.sysmergearticles where pubid = @pubid and objid = @base_objid
if @check_filter = 1
begin
if @dri_rowcount = 0
begin
raiserror(21570, 16, -1, @table_name, @join_table)
return 1
end
if ObjectProperty(@constid, 'CnstIsDisabled') = 1
begin
raiserror(21572, 16, -1, @filt_name, @table_name)
return 1
end
if ObjectProperty(@constid, 'CnstIsNotRepl') = 1
begin
--raiserror(21573, 16, -1, @filt_name, @table_name)
-- for better usability of feature, reset NFR instead of raising error
exec @retcode = sys.sp_MSsetreset_NFR_FK @fkid = @constid, @set=0
if @@error <> 0 or @retcode <> 0
return 1
end
if exists (
select * from sys.columns sc
where sc.object_id = @base_objid and --(sys.fn_IsTypeBlob(system_type_id,max_length)= 1)
(sc.system_type_id in (type_id('image'), type_id('text'), type_id('ntext'), type_id('xml'))
or sc.max_length = -1)
and
(exists (select * from dbo.sysmergearticles where nickname = @base_nick
and sys.fn_MSisfilteredcolumn(subset_filterclause, sc.name, @base_objid) = 1)
or exists (select * from dbo.sysmergesubsetfilters where (art_nickname = @base_nick or join_nickname = @base_nick)
and sys.fn_MSisfilteredcolumn(join_filterclause, sc.name, @base_objid) = 1 )
)
)
or exists (
select * from sys.columns sc
where sc.object_id = @join_objid and --(sys.fn_IsTypeBlob(system_type_id,max_length)= 1)
(sc.system_type_id in (type_id('image'), type_id('text'), type_id('ntext'), type_id('xml'))
or sc.max_length = -1)
and
( exists (select * from dbo.sysmergearticles where nickname = @join_nick
and sys.fn_MSisfilteredcolumn(subset_filterclause, sc.name, @join_objid) = 1 )
or exists (select * from dbo.sysmergesubsetfilters where (art_nickname = @join_nick or join_nickname = @join_nick)
and sys.fn_MSisfilteredcolumn(join_filterclause, sc.name, @join_objid) = 1 )
)
)
begin
raiserror(22519, 16, -1, @table_name, @join_table)
return 1
end
end
-- set @qual_jointable = @join_owner + '.' + @join_table
-- Loop over keys, building up our join filter clause
set @keyindex = 1
set @first_piece = 0
while @keyindex <= @keycnt
begin
/* Get the column names */
/* also pass in vertical partitioning binary to excluded columns that are not in the current partition */
/*
exec sys.sp_MSindexcolfrombin @base_objid, @keyindex, @basekeys, @basecol output, @base_columns
if @@ERROR<>0 return (1)
exec sys.sp_MSindexcolfrombin @join_objid, @keyindex, @joinkeys, @joincol output, @join_columns
if @@ERROR<>0 return (1)
*/
if @join_unique = 1
select @basecolid = parent_column_id, @joincolid = referenced_column_id
from sys.foreign_keys f, sys.foreign_key_columns k
where f.parent_object_id = @base_objid and
f.referenced_object_id = @join_objid and
k.constraint_object_id = f.object_id and
k.constraint_column_id = @keyindex
else
select @basecolid = referenced_column_id, @joincolid = parent_column_id
from sys.foreign_keys f, sys.foreign_key_columns k
where f.parent_object_id = @join_objid and
f.referenced_object_id = @base_objid and
k.constraint_object_id = f.object_id and
k.constraint_column_id = @keyindex
if @base_columns is not NULL
begin
exec @retcode = sys.sp_MStestbit @bm=@base_columns, @coltotest=@basecolid
if @retcode=0
select @basecol = NULL
else
select @basecol = name from sys.columns where object_id = @base_objid and column_id = @basecolid
end
if @join_columns is not NULL
begin
exec @retcode = sys.sp_MStestbit @bm=@join_columns, @coltotest=@joincolid
if @retcode=0
select @joincol = NULL
else
select @joincol = name from sys.columns where object_id = @join_objid and column_id = @joincolid
end
if @basecol is not NULL and @joincol is not NULL
begin
if @check_filter = 0
begin
select @basecol = quotename(@basecol), @joincol = quotename(@joincol)
-- Make filter
/* Make the piece of predicate pertaining to this key column */
set @filtpiece = @table_name + '.' + @basecol + ' = ' + @join_table + '.' + @joincol
/* If first time through, initialize clause, else add to it */
if @first_piece=0
begin
set @first_piece=1
set @filtclause = @filtpiece
end
else
set @filtclause = @filtclause + ' and ' + @filtpiece
end
else
begin
-- Check filter. Filter should contain the
if sys.fn_MSisfilteredcolumn(@join_filterclause, @basecol, @base_objid) = 0
begin
raiserror(21537, 16, -1, @basecol, @table_name, @join_table)
return 1
end
if sys.fn_MSisfilteredcolumn(@join_filterclause, @joincol, @join_objid) = 0
begin
raiserror(21537, 16, -1, @joincol, @join_table, @table_name)
return 1
end
if exists (select * from sys.columns where object_id = object_id(@table_name)
and name = @basecol and is_nullable = 1)
begin
raiserror(21576, 16, -1, @table_name, @join_table, @basecol, @table_name)
return 1
end
if exists (select * from sys.columns where object_id = object_id(@join_table)
and name = @joincol and is_nullable = 1)
begin
raiserror(21576, 16, -1, @table_name, @join_table, @joincol, @join_table)
return 1
end
end
end
/* move on to the next key */
set @keyindex = @keyindex + 1
end
/* no filter generated due to vertical partitioning */
if @first_piece>0 and @check_filter = 0
begin
/* Add the join filter */
exec @retcode = sys.sp_addmergefilter @publication, @article, @filt_name, @join_article, @filtclause, @join_unique
if @@ERROR<>0 or @retcode<>0 return (1)
-- If the parent is well-partitioned, and the join_unique_key is 1, and there is only one
-- join filter on this base article, then the base article can be well-partitioned as well.
if @join_unique = 1
and exists (select * from dbo.sysmergepartitioninfoview
where pubid = @pubid
and objid = @join_objid
and partition_options = 3)
and exists (select * from dbo.sysmergesubsetfilters
where pubid = @pubid
and art_nickname = @base_nick
group by art_nickname having count(*) = 1)
begin
exec @retcode = sys.sp_changemergearticle @publication, @article, 'partition_options', '3'
if @@ERROR<>0 or @retcode<>0 return (1)
end
else if @join_unique = 1
and exists (select * from dbo.sysmergepartitioninfoview
where pubid = @pubid
and objid = @join_objid
and partition_options = 2)
and exists (select * from dbo.sysmergesubsetfilters
where pubid = @pubid
and art_nickname = @base_nick
group by art_nickname having count(*) = 1)
begin
exec @retcode = sys.sp_changemergearticle @publication, @article, 'partition_options', '2'
if @@ERROR<>0 or @retcode<>0 return (1)
end
end
return (0)
