if exists (select name from sysobjects where name = 'hn_LinkCheckExternal' and type = 'P')
drop procedure hn_LinkCheckExternal
go
/*
** Update the HIT_RATE of newly inserted links
*/
create procedure hn_LinkCheckExternal
@SOURCE_DOC_UNID varchar(64) OUTPUT,
@SOURCE_SEQNUM smallint OUTPUT,
@SOURCE_LINK_TYPE varchar(16) OUTPUT,
@SOURCE_LINK_CONFIGTYPE nvarchar(16) OUTPUT,
@SOURCE_PARAM1 nvarchar(384) OUTPUT,
@SOURCE_PARAM2 nvarchar(384) OUTPUT,
@SOURCE_PARAM3 nvarchar(384) OUTPUT,
@SOURCE_PARAM4 nvarchar(384) OUTPUT
as
declare @SOURCE_EFFECTIVE bit
declare @SOURCE_AUDIENCE varchar(32)
declare @TARGET_PUB_UNID varchar(64)
declare @hitrate smallint
declare @tempid varchar(64)
declare @linkCheckView varchar(254)
if @SOURCE_LINK_TYPE <> 'external' return
if @SOURCE_LINK_CONFIGTYPE = '*'
begin -- HyperNet default external link
/* @PARAM1 : database
** @PARAM2 : publication title
** @PARAM3 : document heading
** @PARAM4 : additional parameters
** at least one of publication title and document heading has to be nonempty
*/
select
@SOURCE_EFFECTIVE = EFFECTIVE,
@SOURCE_AUDIENCE = AUDIENCE
from HDS_PUBLICATION
where PUB_UNID = (select PUB_UNID from HDS_TOPIC where DOC_UNID = @SOURCE_DOC_UNID)
if @SOURCE_PARAM1 = '' or DB_NAME() = @SOURCE_PARAM1
begin -- to search in this database
select distinct DOC_ID, EFFECTIVE, AUDIENCE, DOC_UNID from HDS_LINKCHECK_VIEW where
(@SOURCE_PARAM2<>'' or @SOURCE_PARAM3<>'') and
(@SOURCE_PARAM2='' or @SOURCE_PARAM2=PUB_TITLE) and
(@SOURCE_PARAM3='' or @SOURCE_PARAM3=DOC_HEADING)
set @hitrate = @@ROWCOUNT
end
else
begin -- to check if the specified database exists
if exists(select name from master.dbo.sysdatabases where name = @SOURCE_PARAM1)
begin -- and to check if the specified database contains the link check view
exec(
'select * from ' + @SOURCE_PARAM1 + '.dbo.sysobjects where
id = object_id(N''' + @SOURCE_PARAM1 + '.dbo.HDS_LINKCHECK_VIEW'') and
OBJECTPROPERTY(id, N''IsView'') = 1'
)
if @@ROWCOUNT > 0
begin -- searching in the specified database
exec(
'select distinct DOC_ID, EFFECTIVE, AUDIENCE, DOC_UNID from ' + @SOURCE_PARAM1 + '.dbo.HDS_LINKCHECK_VIEW where
(''' + @SOURCE_PARAM2 + '''<>'''' or ''' + @SOURCE_PARAM3 + '''<>'''') and
(''' + @SOURCE_PARAM2 + '''='''' or ''' + @SOURCE_PARAM2 + '''=PUB_TITLE) and
(''' + @SOURCE_PARAM3 + '''='''' or ''' + @SOURCE_PARAM3 + '''=DOC_HEADING)'
)
set @hitrate = @@ROWCOUNT
end
else set @hitrate = -2 -- link check view not found
end
else set @hitrate = -1 -- database not found
end
-- ... and update the hit rate
update HDS_PUB_LINK_INFO set HIT_RATE = @hitrate where DOC_UNID = @SOURCE_DOC_UNID and SEQNUM = @SOURCE_SEQNUM
end -- HyperNet default external link
else
if @SOURCE_LINK_CONFIGTYPE = 'CustomLink'
begin -- Customized external link of type 'CustomLink'
/* @PARAM1 : publication title
** @PARAM2 : document heading
** @PARAM3 : language
** @PARAM4 : publication category
** effective version management is disabled (AUDIENCE = 'Production')
** all parameters have to be nonempty
*/
select distinct DOC_ID, EFFECTIVE, AUDIENCE, DOC_UNID from HDS_LINKCHECK_VIEW where
@SOURCE_PARAM1=PUB_TITLE and
@SOURCE_PARAM2=DOC_HEADING and
@SOURCE_PARAM3=LANGUAGE and
@SOURCE_PARAM4=PUB_CATEGORY
set @hitrate = @@ROWCOUNT
update HDS_PUB_LINK_INFO set HIT_RATE = @hitrate where DOC_UNID = @SOURCE_DOC_UNID and SEQNUM = @SOURCE_SEQNUM
end -- Customized external link of type 'CustomLink'
else
begin -- Customized external link which is not implemented
update HDS_PUB_LINK_INFO set HIT_RATE = 0 where DOC_UNID = @SOURCE_DOC_UNID and SEQNUM = @SOURCE_SEQNUM
end
go