61 lines
5.3 KiB
Transact-SQL
61 lines
5.3 KiB
Transact-SQL
USE [site_fuchs]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[ocms_merge_properties] Script Date: 02.12.2020 21:05:05 ******/
|
|
DROP PROCEDURE [dbo].[ocms_merge_properties]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[ocms_merge_properties] Script Date: 02.12.2020 21:05:06 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[ocms_merge_properties]
|
|
@newprop [dbo].[ocms_type_properties_base] READONLY
|
|
AS
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
DECLARE @lastpid int = ISNULL ( (SELECT MAX(ISNULL(p.[ocms_pid], ap.[ocms_pid]))
|
|
FROM [dbo].[ocms_properties] as p FULL OUTER JOIN [ocms_archive__properties] as ap on p.[ocms_pid] = ap.[ocms_pid])
|
|
,0);
|
|
|
|
WITH np as (
|
|
-- make sure properties are allowed by templates
|
|
SELECT
|
|
[pid] = isnull(pp.[ocms_pid], ROW_NUMBER() OVER (PARTITION BY pp.[ocms_pid] order BY tp.[ocms_tpid], n.[key]) + @lastpid)
|
|
,n.*
|
|
,tp.[type]
|
|
FROM @newprop as n
|
|
JOIN [dbo].[ocms_items] as i ON n.[ocms_iid] = i.[ocms_iid]
|
|
JOIN [dbo].[ocms_templates] as t ON i.[template_id] = t.[ocms_tid]
|
|
JOIN [dbo].[ocms_template_properties] as tp ON t.[ocms_tid] = tp.[ocms_tid] AND n.[key] = tp.[key]
|
|
LEFT JOIN [dbo].[ocms_properties] as pp ON pp.[key] = n.[key] and pp.[locale] = n.[locale] AND pp.[ocms_iid] = n.[ocms_iid]
|
|
)
|
|
MERGE [dbo].[ocms_properties] as TARGET
|
|
USING np as SOURCE ON TARGET.[key] = SOURCE.[key] and TARGET.[locale] = SOURCE.[locale] AND TARGET.[ocms_iid] = SOURCE.[ocms_iid]
|
|
WHEN MATCHED AND ISNULL(SOURCE.[value], '') = '' AND SOURCE.[type] <> 'array' THEN
|
|
DELETE
|
|
WHEN MATCHED AND TARGET.[Value] <> SOURCE.[value] THEN
|
|
UPDATE SET [value] = SOURCE.[value], [DateModified] = GETUTCDATE()
|
|
WHEN NOT MATCHED BY TARGET THEN
|
|
INSERT ([ocms_pid], [ocms_iid], [key], [value], [locale], [DateCreated], [DateModified])
|
|
VALUES (SOURCE.[pid], SOURCE.[ocms_iid], SOURCE.[key], SOURCE.[value], SOURCE.[locale], GETUTCDATE(), GETUTCDATE())
|
|
OUTPUT
|
|
ISNULL(inserted.[ocms_pid], deleted.[ocms_pid]),
|
|
$action,
|
|
inserted.[ocms_iid], inserted.[key], inserted.[value], inserted.[locale], inserted.[DateCreated], inserted.[DateModified], NULL, NULL,
|
|
deleted.[ocms_iid], deleted.[key], deleted.[value], deleted.[locale], deleted.[DateCreated], deleted.[DateModified], NULL, NULL
|
|
INTO [dbo].[ocms_log__properties]
|
|
;
|
|
|
|
END
|
|
GO
|
|
ALTER AUTHORIZATION ON [dbo].[ocms_merge_properties] TO SCHEMA OWNER
|
|
GO
|