Stefan/Fuchs_Intranet
1
0
Fork 0
Code Issues Pull Requests Actions 1 Packages Projects Releases Wiki Activity
Files
main
Fuchs_Intranet/hlp/site_fuchs_bm__scripts__nodata.sql
T

2879 lines
185 KiB
Transact-SQL
Raw Permalink Blame History

USE [master]
GO
/****** Object: Database [site_fuchs_bm] Script Date: 25.09.2020 12:39:23 ******/
CREATE DATABASE [site_fuchs_bm]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'site_fuchs_bm', FILENAME = N'C:\DATA\MSSQL14.SQL\MSSQL\DATA\site_fuchs_bm.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
LOG ON
( NAME = N'site_fuchs_bm_log', FILENAME = N'C:\DATA\MSSQL14.SQL\MSSQL\DATA\site_fuchs_bm_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [site_fuchs_bm] SET COMPATIBILITY_LEVEL = 140
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [site_fuchs_bm].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [site_fuchs_bm] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [site_fuchs_bm] SET ANSI_NULLS OFF
GO
ALTER DATABASE [site_fuchs_bm] SET ANSI_PADDING OFF
GO
ALTER DATABASE [site_fuchs_bm] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [site_fuchs_bm] SET ARITHABORT OFF
GO
ALTER DATABASE [site_fuchs_bm] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [site_fuchs_bm] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [site_fuchs_bm] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [site_fuchs_bm] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [site_fuchs_bm] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [site_fuchs_bm] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [site_fuchs_bm] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [site_fuchs_bm] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [site_fuchs_bm] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [site_fuchs_bm] SET DISABLE_BROKER
GO
ALTER DATABASE [site_fuchs_bm] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [site_fuchs_bm] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [site_fuchs_bm] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [site_fuchs_bm] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [site_fuchs_bm] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [site_fuchs_bm] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [site_fuchs_bm] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [site_fuchs_bm] SET RECOVERY FULL
GO
ALTER DATABASE [site_fuchs_bm] SET MULTI_USER
GO
ALTER DATABASE [site_fuchs_bm] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [site_fuchs_bm] SET DB_CHAINING OFF
GO
ALTER DATABASE [site_fuchs_bm] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [site_fuchs_bm] SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [site_fuchs_bm] SET DELAYED_DURABILITY = DISABLED
GO
ALTER DATABASE [site_fuchs_bm] SET QUERY_STORE = OFF
GO
USE [site_fuchs_bm]
GO
/****** Object: User [fuchs_web] Script Date: 25.09.2020 12:39:23 ******/
CREATE USER [fuchs_web] FOR LOGIN [fuchs_web] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object: DatabaseRole [ocms_rwe] Script Date: 25.09.2020 12:39:23 ******/
CREATE ROLE [ocms_rwe]
GO
/****** Object: DatabaseRole [fuchs_rwe] Script Date: 25.09.2020 12:39:23 ******/
CREATE ROLE [fuchs_rwe]
GO
ALTER ROLE [fuchs_rwe] ADD MEMBER [fuchs_web]
GO
ALTER ROLE [ocms_rwe] ADD MEMBER [fuchs_web]
GO
/****** Object: UserDefinedTableType [dbo].[ocms_type_itemtree] Script Date: 25.09.2020 12:39:23 ******/
CREATE TYPE [dbo].[ocms_type_itemtree] AS TABLE(
[ocms_iid] [bigint] NOT NULL,
[parent_iid] [bigint] NULL,
[ocms_pid] [bigint] NULL,
[name] [nvarchar](255) NULL,
[view] [bit] NOT NULL,
[template_id] [bigint] NULL,
[hide] [bit] NOT NULL,
[order] [int] NOT NULL,
[DateCreated] [datetime] NULL,
[DateModified] [datetime] NULL,
[level] [int] NULL,
[sub] [bit] NULL,
[focus] [bit] NULL,
[new_order] [int] NULL
)
GO
/****** Object: UserDefinedTableType [dbo].[ocms_type_navigation_base] Script Date: 25.09.2020 12:39:23 ******/
CREATE TYPE [dbo].[ocms_type_navigation_base] AS TABLE(
[ocms_iid] [bigint] NOT NULL,
[locale] [varchar](5) NOT NULL,
[alias] [varchar](25) NULL
)
GO
/****** Object: UserDefinedTableType [dbo].[ocms_type_properties_base] Script Date: 25.09.2020 12:39:23 ******/
CREATE TYPE [dbo].[ocms_type_properties_base] AS TABLE(
[ocms_iid] [bigint] NOT NULL,
[key] [varchar](50) NULL,
[value] [nvarchar](max) NOT NULL,
[locale] [varchar](5) NULL
)
GO
/****** Object: UserDefinedTableType [dbo].[ocms_type_viewtree_full] Script Date: 25.09.2020 12:39:23 ******/
CREATE TYPE [dbo].[ocms_type_viewtree_full] AS TABLE(
[ocms_iid] [bigint] NOT NULL,
[parent_iid] [bigint] NULL,
[ocms_pid] [bigint] NULL,
[name] [nvarchar](255) NULL,
[view] [bit] NOT NULL,
[template_id] [bigint] NULL,
[hide] [bit] NOT NULL,
[order] [int] NOT NULL,
[DateCreated] [datetime] NULL,
[DateModified] [datetime] NULL,
[locale] [varchar](5) NOT NULL,
[alias] [varchar](25) NULL,
[path] [varchar](2000) NULL,
[level] [int] NULL,
[sub] [bit] NULL,
[focus] [bit] NULL,
[nav] [bit] NULL,
[new_order] [int] NULL
)
GO
/****** Object: UserDefinedFunction [dbo].[ocms_admin_authenticate] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[ocms_admin_authenticate]
(
@email varchar(255),
@password nvarchar(20)
)
RETURNS @RET TABLE ( [useraccount_id] varchar(5), [auth] smallint, [name] nvarchar(255) null, [email] varchar(255) NULL, [mobile] varchar(50) null)
AS
BEGIN
DECLARE @auth smallint = -1;
DECLARE @useraccount_id varchar(10), @name nvarchar(255), @mobile varchar(50);
SELECT TOP(1) @useraccount_id = [useraccount_id]
,@name = DECRYPTBYKEY(ua.[name_enc])
,@email = DECRYPTBYKEY(ua.[email_enc])
,@mobile = DECRYPTBYKEY(ua.[mobile_enc])
FROM [dbo].[ocms_useraccounts] as ua
WHERE ua.[DateDeleted] is null AND ua.[DateDisabled] is null
AND DECRYPTBYKEY(ua.[email_enc]) = @email
AND DECRYPTBYKEY(ua.[password_enc]) = @password
AND ua.[password_enc] is not null;
INSERT INTO @RET
VALUES(
@useraccount_id
,IIF(@useraccount_id is null, -1, 1)
,IIF(@useraccount_id is null, null, @name)
,IIF(@useraccount_id is null, null, @email)
,IIF(@useraccount_id is null, null, @mobile)
);
RETURN;
END
GO
/****** Object: UserDefinedFunction [dbo].[ocms_admin_checkUserExists] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[ocms_admin_checkUserExists]
(
@useraccount_id varchar(5)
)
RETURNS bit
AS
BEGIN
DECLARE @RET bit;
SET @RET = IIF(EXISTS (SELECT * FROM [dbo].[ocms_useraccounts] as ua WHERE [useraccount_id] = @useraccount_id AND ua.[DateDeleted] is null AND ua.[DateDisabled] is null), 1, 0);
RETURN @RET;
END
GO
/****** Object: UserDefinedFunction [dbo].[ocms_admin_getUserAccount] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[ocms_admin_getUserAccount]
(
@useraccount_id varchar(5)
)
RETURNS @RET TABLE ( [useraccount_id] varchar(5), [auth] smallint, [name] nvarchar(255) null, [email] varchar(255) NULL, [mobile] varchar(50) null)
AS
BEGIN
DECLARE @auth smallint = -1;
DECLARE @name nvarchar(255), @email varchar(255), @mobile varchar(50);
SELECT TOP(1) @useraccount_id = [useraccount_id]
,@name = DECRYPTBYKEY(ua.[name_enc])
,@email = DECRYPTBYKEY(ua.[email_enc])
,@mobile = DECRYPTBYKEY(ua.[mobile_enc])
FROM [dbo].[ocms_useraccounts] as ua
WHERE ua.[DateDeleted] is null
AND ua.[DateDisabled] is null
AND ua.[useraccount_id] = @useraccount_id;
INSERT INTO @RET
VALUES(
@useraccount_id
,IIF(@useraccount_id is null, -1, 1)
,IIF(@useraccount_id is null, null, @name)
,IIF(@useraccount_id is null, null, @email)
,IIF(@useraccount_id is null, null, @mobile)
);
RETURN;
END
GO
/****** Object: UserDefinedFunction [dbo].[ocms_fn_generatePassword] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ocms_fn_generatePassword] (
@length int
, @specialChars bit = 1
, @nums bit = 1
, @lowerCase bit = 1
)
RETURNS varchar(255)
AS
BEGIN
DECLARE @randInt int;
DECLARE @NewCharacter varchar(1);
DECLARE @NewPassword TABLE([typ] char(1), [char] char(1), [order] int);
SET @length = CASE WHEN @length > 255 THEN 255 ELSE @length END;
DECLARE @cnt int = 0, @position as int;
--random characters
WHILE (@cnt < @length)
BEGIN
SELECT @randInt = [dbo].[ocms_fn_randbetween](33,122)
IF (CASE WHEN (@randInt>=33 AND @randInt<=47) AND @specialChars = 1 THEN 1
WHEN (@randInt>=48 AND @randInt<=57) AND @nums = 1 THEN 1
WHEN (@randInt>=58 AND @randInt<=64) AND @specialChars = 1 THEN 1
WHEN (@randInt>=65 AND @randInt<=90) THEN 1
WHEN (@randInt>=91 AND @randInt<=93) AND @specialChars = 1 THEN 1
WHEN (@randInt>=97 AND @randInt<=122) AND @lowerCase = 1 THEN 1
ELSE 0
END) = 1
BEGIN
SET @cnt = @cnt + 1;
INSERT INTO @NewPassword
VALUES (CASE WHEN (@randInt>=33 AND @randInt<=47) THEN 's'
WHEN (@randInt>=48 AND @randInt<=57) THEN 'n'
WHEN (@randInt>=58 AND @randInt<=64) THEN 's'
WHEN (@randInt>=65 AND @randInt<=90) THEN 'u'
WHEN (@randInt>=91 AND @randInt<=93) THEN 's'
WHEN (@randInt>=97 AND @randInt<=122) THEN 'l'
END,
CHAR(@randInt), @cnt);
END
END
DECLARE @min_n int = CASE WHEN @length > 4 AND @length < 11 THEN 1 ELSE FLOOR(@length * 0.1) END;
DECLARE @min_s int = FLOOR(@length * 0.1);
DECLARE @min_l int = FLOOR(@length * 0.4);
DECLARE @min_u int = FLOOR(@length * 0.4);
WHILE (SELECT COUNT(*) FROM @NewPassword WHERE [typ] = 'n') < @min_n AND @length > 0 AND @nums = 1
BEGIN
SET @position = [dbo].[ocms_fn_randbetween](1,@length);
UPDATE @NewPassword SET [char] = CHAR([dbo].[ocms_fn_randbetween](48,57)), [typ] = 'n' WHERE [order] = @position;
END
WHILE (SELECT COUNT(*) FROM @NewPassword WHERE [typ] = 'l') < @min_l AND @length > 0 AND @lowerCase = 1
BEGIN
SET @position = [dbo].[ocms_fn_randbetween](1,@length);
UPDATE @NewPassword SET [char] = CHAR([dbo].[ocms_fn_randbetween](97,122)), [typ] = 'l' WHERE [order] = @position;
END
WHILE (SELECT COUNT(*) FROM @NewPassword WHERE [typ] = 'u') < @min_n AND @length > 0
BEGIN
SET @position = [dbo].[ocms_fn_randbetween](1,@length);
UPDATE @NewPassword SET [char] = CHAR([dbo].[ocms_fn_randbetween](65,90)), [typ] = 'u' WHERE [order] = @position;
END
WHILE (SELECT COUNT(*) FROM @NewPassword WHERE [typ] = 's') < @min_s AND @length > 0 AND @specialChars = 1
BEGIN
SET @position = [dbo].[ocms_fn_randbetween](1,@length);
DECLARE @nc char(1), @nt char(1);
select @randInt=[dbo].[ocms_fn_randbetween](33,122)
UPDATE @NewPassword SET [char] = CHAR(@randint), [typ] = 's' WHERE [order] = @position AND (CASE WHEN (@randInt>=33 AND @randInt<=47) THEN 's'
WHEN (@randInt>=48 AND @randInt<=57) THEN 'n'
WHEN (@randInt>=58 AND @randInt<=64) THEN 's'
WHEN (@randInt>=65 AND @randInt<=90) THEN 'u'
WHEN (@randInt>=91 AND @randInt<=93) THEN 's'
WHEN (@randInt>=97 AND @randInt<=122) THEN 'l'
END) = 's';
END
RETURN (Cast((SELECT [CHAR] as [text()] FROM @NewPassword FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') as varchar(255)));
END;
GO
/****** Object: UserDefinedFunction [dbo].[ocms_fn_link_csv] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[ocms_fn_link_csv]
(
@inp1 varchar(4000)
,@inp2 varchar(4000)
)
RETURNS varchar(4000)
AS
BEGIN
DECLARE @RET varchar(4000) = ISNULL(@inp1, '')
+ CASE WHEN ISNULL(@inp1, '') <> '' AND ISNULL(@inp2, '') <> '' THEN ',' ELSE '' END
+ ISNULL(@inp2, '');
RETURN @RET;
END
GO
/****** Object: UserDefinedFunction [dbo].[ocms_fn_randbetween] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ocms_fn_randbetween](@bottom integer, @top integer)
RETURNS integer
AS
BEGIN
RETURN (select cast(round((@top-@bottom) * [rn] +@bottom,0) as integer) from [dbo].[ocms_fn_rand])
END
GO
/****** Object: UserDefinedFunction [dbo].[ocms_fn_useraccount_id] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ocms_fn_useraccount_id] (
)
RETURNS varchar(5)
AS
BEGIN
DECLARE @NewAccount varchar(5) = [dbo].[ocms_fn_generatePassword] (5,0,1,0);
WHILE EXISTS( SELECT * FROM [dbo].[ocms_useraccounts] WHERE [useraccount_id] = @NewAccount)
BEGIN
SET @NewAccount = [dbo].[ocms_fn_generatePassword] (5,0,1,0);
END
RETURN @NewAccount;
END;
GO
/****** Object: UserDefinedFunction [dbo].[ocms_getFullItemTree] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION [dbo].[ocms_getFullItemTree]
(
@view_iid bigint
,@include_hidden bit
)
RETURNS @itemtree TABLE (
[ocms_iid] [bigint] NOT NULL,
[parent_iid] [bigint] NULL,
[ocms_pid] [bigint] NULL,
[name] [nvarchar](255) NULL,
[view] [bit] NOT NULL,
[template_id] [bigint] NULL,
[hide] [bit] NOT NULL,
[order] [int] NOT NULL,
[DateCreated] [datetime] NULL,
[DateModified] [datetime] NULL,
[level] int, [sub] bit, [focus] bit, [new_order] int
)
AS
BEGIN
-- get basics
DECLARE @site bigint = (SELECT TOP(1) [ocms_iid] FROM [dbo].[ocms_items] as i WHERE i.[parent_iid] is null);
IF @site is NOT null
BEGIN
-- tree of all items
WITH cteLevels as (
SELECT t.*
, CAST(0 as int) as [level]
, 0 as [sub]
FROM [dbo].[ocms_items] as t
WHERE t.[ocms_iid] = @site
UNION ALL
SELECT t.*
, CAST(c.[level] + 1 as int) as [level]
, CASE WHEN c.[sub] = 1 THEN 1 WHEN t.[parent_iid] = @view_iid THEN 1 ELSE 0 END as [sub]
FROM [dbo].[ocms_items] as t
inner JOIN cteLevels c on t.[parent_iid] = c.[ocms_iid]
WHERE (t.[hide] = 0 Or @include_hidden = 1)
)
INSERT INTO @itemtree
SELECT t.*
, IIF( t.[ocms_iid] = @view_iid , 1, 0) as [focus]
, ROW_NUMBER() OVER (PARTITION BY [parent_iid] ORDER BY [order], [ocms_iid]) as [new_order]
FROM cteLevels as t;
--SELECT * FROM @itemtree
END
RETURN
END
GO
/****** Object: UserDefinedFunction [dbo].[ocms_getFullViewTree] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION [dbo].[ocms_getFullViewTree]
(
@view_path varchar(255),
@locale varchar(5)
)
RETURNS @itemtree TABLE (
[ocms_iid] [bigint] NOT NULL,
[parent_iid] [bigint] NULL,
[ocms_pid] [bigint] NULL,
[name] [nvarchar](255) NULL,
[view] [bit] NOT NULL,
[template_id] [bigint] NULL,
[hide] [bit] NOT NULL,
[order] [int] NOT NULL,
[DateCreated] datetime null,
[DateModified] datetime null,
[locale] varchar(5) NOT NULL, [alias] varchar(25) NULL, [path] varchar(2000), [level] int, [sub] bit, [focus] bit, [nav] bit, [new_order] int
)
AS
BEGIN
-- get basics
DECLARE @site bigint = (SELECT TOP(1) [ocms_iid] FROM [dbo].[ocms_items] as i WHERE i.[parent_iid] is null);
SET @view_path = ISNULL(@view_path, '');
SET @locale = IIF(ISNULL(@locale, '')='', null, @locale);
IF @site is NOT null
BEGIN
--get locales
DECLARE @locales TABLE([locale] varchar(5), [order] int);
INSERT INTO @locales
SELECT * FROM [dbo].[ocms_locales]();
-- tree in all locales
WITH itm as(
-- items are not localized
SELECT i.*, l.[locale]
FROM [dbo].[ocms_items] as i CROSS JOIN (SELECT * FROM @locales as l_ WHERE l_.[locale] <> '*') as l
WHERE i.[view] = 1 OR ([ocms_iid] = @site)
), navitm as (
select itm.*
,IIF(itm.[name] COLLATE Latin1_General_CI_AS = 'index', 'index', n.[alias]) as 'alias'
FROM itm
LEFT JOIN [dbo].[ocms_navigation] as n ON itm.[ocms_iid] = n.[ocms_iid] and itm.[locale] = n.[locale] AND n.[locale] <> '*'
), cteLevels as (
SELECT t.*
, Cast('//' as varchar(2000)) as [path]
, CAST(0 as int) as [level]
, 0 as [sub]
FROM navitm as t
WHERE t.[ocms_iid] = @site
UNION ALL
SELECT navitm.*
, IIF( navitm.[view] = 0, '', CAST(IIF( c.[path] = '//' , '' , c.[path] + '/' ) + LOWER(navitm.[alias]) as varchar(2000)) ) as [path]
, CAST(c.[level] + 1 as int) as [level]
, CASE WHEN c.[sub] = 1 THEN 1 WHEN IIF( CAST(IIF( c.[path] = '//' , '' , c.[path] + '/' ) + ISNULL(LOWER(navitm.[name]), '') as varchar(2000)) = LOWER(@view_path), 1, 0) = 1 THEN 1 ELSE 0 END as [sub]
FROM navitm
inner JOIN cteLevels c on navitm.[parent_iid] = c.[ocms_iid] AND navitm.[locale] = c.[locale]
)
INSERT INTO @itemtree
SELECT t.*
, IIF( [path] COLLATE Latin1_General_CI_AS = @view_path OR [path] COLLATE Latin1_General_CI_AS like (@view_path + '?%'), 1, 0) as [focus]
, IIF( [view] = 1 AND [level] > 0 AND [path] <> '' AND [path] <> '//' AND ISNULL(t.[hide],0) = 0 , 1, 0 ) as [nav]
, ROW_NUMBER() OVER (PARTITION BY [parent_iid] ORDER BY [order], [ocms_iid]) as [new_order]
FROM cteLevels as t;
UPDATE @itemtree SET [path] = null WHERE [nav] = 0;
IF(ISNULL(@locale, '') <> '')
BEGIN
DECLARE @view_iid bigint = null, @view_level int;
SELECT TOP(1)
@view_iid = it.[ocms_iid]
, @view_level = it.[level]
, @locale = it.[locale] -- will override the selected language
FROM @itemtree as it
WHERE [focus] = 1
AND ([locale] = @locale OR [name] COLLATE Latin1_General_CI_AS <> 'index'); -- index is available in any language
DELETE FROM @itemtree WHERE [locale] <> @locale; -- remove anything that is not in selected language
END
--SELECT * FROM @itemtree
END
RETURN
END
GO
/****** Object: UserDefinedFunction [dbo].[ocms_locales] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION [dbo].[ocms_locales]
(
)
RETURNS @RET TABLE ([locale] varchar(5), [order] int)
AS
BEGIN
DECLARE @set_value varchar(2000) = ISNULL((SELECT [value] FROM [ocms_settings] where [key] = 'locales'), '*,de');
DECLARE @c char = ',';
INSERT INTO @RET
SELECT [value], [order] = ROW_NUMBER() OVER (ORDER BY CHARINDEX(@c + value + @c, @c + @set_value + @c))
FROM STRING_SPLIT(@set_value, @c);
RETURN;
END
GO
/****** Object: UserDefinedFunction [dbo].[ott_20_randomStrings] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION [dbo].[ott_20_randomStrings]
(
@digits tinyint
)
RETURNS @RET TABLE
(
[s] varchar(5)
)
AS
BEGIN
DECLARE @new_sessionid varchar(36), @trys int, @chars varchar(100), @UPPER int, @LOWER int;
SELECT @chars = '012KLMN34STUVWEFGHIJXYZ567OPQR89ABCD';
SELECT @UPPER = LEN(@chars), @LOWER = 1;
INSERT INTO @RET
SELECT * FROM (SELECT TOP(20) SUBSTRING(SUBSTRING(@chars, [dbo].[ott_random_int](@UPPER,@LOWER), 1) + SUBSTRING(@chars, [dbo].[ott_random_int](@UPPER,@LOWER), 1) + SUBSTRING(@chars, [dbo].[ott_random_int](@UPPER,@LOWER), 1) + SUBSTRING(@chars, [dbo].[ott_random_int](@UPPER,@LOWER), 1) + SUBSTRING(@chars, [dbo].[ott_random_int](@UPPER,@LOWER), 1) + SUBSTRING(@chars, [dbo].[ott_random_int](@UPPER,@LOWER), 1) + SUBSTRING(@chars, [dbo].[ott_random_int](@UPPER,@LOWER), 1) + SUBSTRING(@chars, [dbo].[ott_random_int](@UPPER,@LOWER), 1) + SUBSTRING(@chars, [dbo].[ott_random_int](@UPPER,@LOWER), 1) + SUBSTRING(@chars, [dbo].[ott_random_int](@UPPER,@LOWER), 1), 1, @digits) as id FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)) vl(id))z WHERE LEN(id) = @digits;
RETURN
END
GO
/****** Object: UserDefinedFunction [dbo].[ott_b10] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[ott_b10]
(
@string varchar(10)
)
RETURNS int
AS
BEGIN
DECLARE @RET int;
DECLARE @str VARCHAR(10) = REVERSE(LOWER(@string));
WITH Split(stpos,endpos) AS(
SELECT 1 AS stpos, 2 AS endpos
UNION ALL
SELECT endpos, endpos+1
FROM Split
WHERE endpos <= LEN(@str)
)
, c as (
SELECT
'character' = SUBSTRING(@str,stpos,COALESCE(NULLIF(endpos,0),LEN(@str)+1)-stpos)
,'charindex' = stpos
FROM Split
)
, s as (
SELECT
*
,'num' = ASCII(character) - ASCII('a') + 1
,'fac' = POWER(26,([charindex] -1))
,'val' = POWER(26,([charindex] -1)) * (ASCII(character) - ASCII('a') + 1)
FROM c
)
select @RET = SUM([val]) FROM s;
RETURN @RET;
END
GO
/****** Object: UserDefinedFunction [dbo].[ott_b26] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[ott_b26]
(
@number int
)
RETURNS varchar(10)
AS
BEGIN
DECLARE @RET varchar(10)
/*
SELECT
*,
LetterCode =
Coalesce((SELECT Char(65 + (N.Num - 475255) / 456976 % 26) WHERE N.Num >= 475255), '')
+ Coalesce((SELECT Char(65 + (N.Num - 18279) / 17576 % 26) WHERE N.Num >= 18279), '')
+ Coalesce((SELECT Char(65 + (N.Num - 703) / 676 % 26) WHERE N.Num >= 703), '')
+ Coalesce((SELECT Char(65 + (N.Num - 27) / 26 % 26) WHERE N.Num >= 27), '')
+ (SELECT Char(65 + (N.Num - 1) % 26))
FROM (VALUES(18931)) as N(Num)
ORDER BY N.Num
*/
SET @RET = LOWER(
Coalesce((SELECT Char(65 + (@number - 475255) / 456976 % 26) WHERE @number >= 475255), '')
+ Coalesce((SELECT Char(65 + (@number - 18279) / 17576 % 26) WHERE @number >= 18279), '')
+ Coalesce((SELECT Char(65 + (@number - 703) / 676 % 26) WHERE @number >= 703), '')
+ Coalesce((SELECT Char(65 + (@number - 27) / 26 % 26) WHERE @number >= 27), '')
+ (SELECT Char(65 + (@number - 1) % 26))
);
RETURN @RET;
END
GO
/****** Object: UserDefinedFunction [dbo].[ott_csv_contains] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[ott_csv_contains]
(
@text varchar(500)
,@fragment varchar(500)
)
RETURNS bit
AS
BEGIN
DECLARE @RET bit;
-- Will be true
-- if any of the fragments exist in the list of text-items
WITH texts as (SELECT * FROM string_split(ISNULL(@text,''), ','))
,fragments as (SELECT * FROM string_split(ISNULL(@fragment,''), ','))
SELECT @RET = CASE WHEN EXISTS (SELECT * FROM texts JOIN fragments ON texts.[value] = fragments.[value]) THEN 1 ELSE 0 END;
RETURN @RET;
END
GO
/****** Object: UserDefinedFunction [dbo].[ott_jcontent] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[ott_jcontent]
(
@groupcode varchar(10)
,@text varchar(4000)
)
RETURNS varchar(4000)
AS
BEGIN
DECLARE @RET varchar(4000);
DECLARE @textinput varchar(4000) = REPLACE(ISNULL(@text, ''), ' ', '');
IF (@textinput = '' OR @textinput = '{}')
BEGIN
SET @RET = 0;
END
ELSE
BEGIN
DECLARE @Pattern varchar(20) = '%"' + @groupcode + '":"%';
DECLARE @P0 int = PATINDEX(@pattern, @textinput);
DECLARE @P1 int = @P0 + LEN(@pattern) -2;
DECLARE @P2 int = CHARINDEX('"',@textinput,@p1);
SELECT @RET = CASE WHEN @P0 = 0 THEN '' ELSE SUBSTRING(@textinput, @P1, @P2 - @P1) END;
END
RETURN @RET;
END
GO
/****** Object: UserDefinedFunction [dbo].[ott_jcsv_contains] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[ott_jcsv_contains]
(
@groupcode varchar(10)
,@text varchar(4000)
,@fragment varchar(500)
)
RETURNS bit
AS
BEGIN
DECLARE @RET bit;
DECLARE @textinput varchar(4000) = REPLACE(ISNULL(@text, ''), ' ', '');
IF (@textinput = '' OR @textinput = '{}' OR @fragment = '')
BEGIN
SET @RET = 0;
END
ELSE
BEGIN
-- Will be true
-- if any of the fragments exist in the list of text-items
DECLARE @Pattern varchar(20) = '%"' + @groupcode + '":"%';
DECLARE @P0 int = PATINDEX(@pattern, @textinput);
DECLARE @P1 int = @P0 + LEN(@pattern) -2;
DECLARE @P2 int = CHARINDEX('"',@textinput,@p1);
IF @P0 = 0
BEGIN
SET @RET = 0;
END
ELSE
BEGIN
DECLARE @text2 varchar(4000) = SUBSTRING(@textinput, @P1, @P2 - @P1);
WITH texts as (SELECT * FROM string_split(ISNULL(@text2,''), ','))
,fragments as (SELECT * FROM string_split(ISNULL(@fragment,''), ','))
SELECT @RET = CASE WHEN EXISTS (SELECT * FROM texts JOIN fragments ON texts.[value] = fragments.[value]) THEN 1 ELSE 0 END;
END
END
RETURN @RET;
END
GO
/****** Object: UserDefinedFunction [dbo].[ott_jcsv_containskey] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[ott_jcsv_containskey]
(
@groupcode varchar(10)
,@text varchar(4000)
)
RETURNS bit
AS
BEGIN
DECLARE @RET bit;
DECLARE @textinput varchar(4000) = REPLACE(ISNULL(@text, ''), ' ', '');
IF (@textinput = '' OR @textinput = '{}')
BEGIN
SET @RET = 0;
END
ELSE
BEGIN
-- Will be true
-- if any of the fragments exist in the list of text-items
DECLARE @Pattern varchar(20) = '%"' + @groupcode + '":"%';
DECLARE @P0 int = PATINDEX(@pattern, @textinput);
SET @RET = Case WHEN @P0 = 0 THEN 0 ELSE 1 END;
END
RETURN @RET;
END
GO
/****** Object: UserDefinedFunction [dbo].[ott_random_int] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[ott_random_int]
(@UPPER int, @LOWER int)
RETURNS int
AS
BEGIN
DECLARE @RET INT = CAST(ROUND(((@UPPER - @LOWER - 1) * (SELECT TOP(1) [rn] FROM [dbo].[ocms_fn_rand]) + @LOWER), 0) as int);
RETURN @RET;
END
GO
/****** Object: View [dbo].[ocms_fn_rand] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[ocms_fn_rand]
AS
select rand(Cast(newid() as varbinary)) as rn
GO
/****** Object: Table [dbo].[fuchs_debug] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[fuchs_debug](
[rn] [bigint] IDENTITY(1,1) NOT NULL,
[interface] [varchar](50) NOT NULL,
[url] [varchar](500) NULL,
[data] [nvarchar](max) NULL,
[date] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[ocms_archive__items] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ocms_archive__items](
[ocms_iid] [bigint] NOT NULL,
[parent_iid] [bigint] NULL,
[ocms_pid] [bigint] NULL,
[name] [nvarchar](255) NULL,
[view] [bit] NOT NULL,
[template_id] [bigint] NULL,
[hide] [bit] NOT NULL,
[order] [int] NOT NULL,
[DateCreated] [datetime] NULL,
[DateModified] [datetime] NULL,
[DateDeleted] [datetime] NULL,
[UserDeleted] [varchar](36) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[ocms_archive__properties] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ocms_archive__properties](
[ocms_pid] [bigint] NULL,
[ocms_iid] [bigint] NULL,
[key] [varchar](50) NULL,
[value] [nvarchar](max) NULL,
[locale] [varchar](5) NULL,
[DateCreated] [datetime] NULL,
[DateModified] [datetime] NULL,
[DateDeleted] [datetime] NULL,
[UserDeleted] [varchar](36) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[ocms_debug] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ocms_debug](
[uid] [bigint] IDENTITY(1,1) NOT NULL,
[machine] [varchar](50) NULL,
[error] [varchar](2000) NULL,
[exception] [varchar](max) NULL,
[procedure] [varchar](255) NULL,
[authuser] [varchar](25) NULL,
[data] [nvarchar](max) NULL,
[isdebug] [bit] NULL,
[timestamp] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[ocms_items] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ocms_items](
[ocms_iid] [bigint] NOT NULL,
[parent_iid] [bigint] NULL,
[ocms_pid] [bigint] NULL,
[name] [nvarchar](255) NULL,
[view] [bit] NOT NULL,
[template_id] [bigint] NULL,
[hide] [bit] NOT NULL,
[order] [int] NOT NULL,
[DateCreated] [datetime] NULL,
[DateModified] [datetime] NULL,
CONSTRAINT [PK_ocms_items] PRIMARY KEY CLUSTERED
(
[ocms_iid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[ocms_log__properties] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ocms_log__properties](
[ocms_pid] [bigint] NULL,
[action] [varchar](50) NULL,
[n_ocms_iid] [bigint] NULL,
[n_key] [varchar](50) NULL,
[n_value] [nvarchar](max) NULL,
[n_locale] [varchar](5) NULL,
[n_DateCreated] [datetime] NULL,
[n_DateModified] [datetime] NULL,
[n_DateDeleted] [datetime] NULL,
[n_UserDeleted] [varchar](36) NULL,
[o_ocms_iid] [bigint] NULL,
[o_key] [varchar](50) NULL,
[o_value] [nvarchar](max) NULL,
[o_locale] [varchar](5) NULL,
[o_DateCreated] [datetime] NULL,
[o_DateModified] [datetime] NULL,
[o_DateDeleted] [datetime] NULL,
[o_UserDeleted] [varchar](36) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[ocms_navigation] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ocms_navigation](
[ocms_nid] [bigint] IDENTITY(1,1) NOT NULL,
[ocms_iid] [bigint] NOT NULL,
[parent_iid] [bigint] NULL,
[locale] [varchar](5) NOT NULL,
[alias] [varchar](25) NOT NULL,
CONSTRAINT [PK_ocms_navigation] PRIMARY KEY CLUSTERED
(
[ocms_nid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[ocms_properties] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ocms_properties](
[ocms_pid] [bigint] NOT NULL,
[ocms_iid] [bigint] NOT NULL,
[key] [varchar](50) NULL,
[value] [nvarchar](max) NOT NULL,
[locale] [varchar](5) NULL,
[DateCreated] [datetime] NULL,
[DateModified] [datetime] NULL,
CONSTRAINT [PK_ocms_properties] PRIMARY KEY CLUSTERED
(
[ocms_pid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[ocms_settings] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ocms_settings](
[ocms_set] [bigint] IDENTITY(1,1) NOT NULL,
[key] [varchar](25) NOT NULL,
[value] [varchar](2000) NULL,
CONSTRAINT [PK_ocms_settings] PRIMARY KEY CLUSTERED
(
[ocms_set] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[ocms_template_properties] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ocms_template_properties](
[ocms_tpid] [bigint] NOT NULL,
[ocms_tid] [bigint] NOT NULL,
[key] [varchar](50) NOT NULL,
[mandatory] [bit] NOT NULL,
[type] [varchar](50) NOT NULL,
[default] [nvarchar](max) NULL,
[condition] [varchar](max) NULL,
[order] [int] NOT NULL,
CONSTRAINT [PK_ocms_template_properties] PRIMARY KEY CLUSTERED
(
[ocms_tpid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[ocms_templates] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ocms_templates](
[ocms_tid] [bigint] NOT NULL,
[name] [varchar](255) NULL,
[partial_view] [varchar](255) NULL,
CONSTRAINT [PK_ocms_templates] PRIMARY KEY CLUSTERED
(
[ocms_tid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[ocms_translations] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ocms_translations](
[ocms_tln] [bigint] IDENTITY(1,1) NOT NULL,
[context_typ] [nvarchar](15) NOT NULL,
[context] [bigint] NULL,
[value] [nvarchar](255) NOT NULL,
[translation] [nvarchar](255) NOT NULL,
[locale] [varchar](5) NOT NULL,
CONSTRAINT [PK_ocms_translations] PRIMARY KEY CLUSTERED
(
[ocms_tln] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[ocms_useraccounts] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ocms_useraccounts](
[useraccount_id] [varchar](5) NOT NULL,
[name_enc] [varbinary](600) NOT NULL,
[email_enc] [varbinary](350) NOT NULL,
[mobile_enc] [varbinary](120) NULL,
[password_enc] [varbinary](100) NOT NULL,
[UserDisabled] [varchar](36) NULL,
[DateDisabled] [datetime] NULL,
[UserCreated] [varchar](36) NOT NULL,
[DateCreated] [datetime] NOT NULL,
[UserModified] [varchar](36) NULL,
[DateModified] [datetime] NOT NULL,
[UserDeleted] [varchar](36) NULL,
[DateDeleted] [datetime] NULL,
CONSTRAINT [PK_ocms_useraccounts] PRIMARY KEY CLUSTERED
(
[useraccount_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [ocms_items__ix0] Script Date: 25.09.2020 12:39:23 ******/
CREATE UNIQUE NONCLUSTERED INDEX [ocms_items__ix0] ON [dbo].[ocms_items]
(
[parent_iid] ASC,
[name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [ocms_items__ix1] Script Date: 25.09.2020 12:39:23 ******/
CREATE UNIQUE NONCLUSTERED INDEX [ocms_items__ix1] ON [dbo].[ocms_items]
(
[name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [ocms_navigation__ix1] Script Date: 25.09.2020 12:39:23 ******/
CREATE UNIQUE NONCLUSTERED INDEX [ocms_navigation__ix1] ON [dbo].[ocms_navigation]
(
[ocms_iid] ASC,
[locale] ASC,
[alias] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [ocms_template_properties__ix1] Script Date: 25.09.2020 12:39:23 ******/
CREATE UNIQUE NONCLUSTERED INDEX [ocms_template_properties__ix1] ON [dbo].[ocms_template_properties]
(
[ocms_tid] ASC,
[key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [ocms_templates__ix1] Script Date: 25.09.2020 12:39:23 ******/
CREATE UNIQUE NONCLUSTERED INDEX [ocms_templates__ix1] ON [dbo].[ocms_templates]
(
[name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [ocms_useraccounts__ix1] Script Date: 25.09.2020 12:39:23 ******/
CREATE UNIQUE NONCLUSTERED INDEX [ocms_useraccounts__ix1] ON [dbo].[ocms_useraccounts]
(
[email_enc] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[fuchs_debug] ADD CONSTRAINT [DF_fuchs_debug_date] DEFAULT (getutcdate()) FOR [date]
GO
ALTER TABLE [dbo].[ocms_debug] ADD CONSTRAINT [DF_ocms_debug_timestamp] DEFAULT (getutcdate()) FOR [timestamp]
GO
ALTER TABLE [dbo].[ocms_items] ADD CONSTRAINT [DF_ocms_items_view] DEFAULT ((0)) FOR [view]
GO
ALTER TABLE [dbo].[ocms_items] ADD CONSTRAINT [DF_ocms_items_hide] DEFAULT ((0)) FOR [hide]
GO
ALTER TABLE [dbo].[ocms_items] ADD CONSTRAINT [DF_ocms_items_order] DEFAULT ((0)) FOR [order]
GO
ALTER TABLE [dbo].[ocms_items] ADD CONSTRAINT [DF_ocms_items_DateCreated] DEFAULT (getutcdate()) FOR [DateCreated]
GO
ALTER TABLE [dbo].[ocms_items] ADD CONSTRAINT [DF_ocms_items_DateModified] DEFAULT (getutcdate()) FOR [DateModified]
GO
ALTER TABLE [dbo].[ocms_properties] ADD CONSTRAINT [DF_ocms_properties_DateCreated] DEFAULT (getutcdate()) FOR [DateCreated]
GO
ALTER TABLE [dbo].[ocms_properties] ADD CONSTRAINT [DF_ocms_properties_DateModified] DEFAULT (getutcdate()) FOR [DateModified]
GO
ALTER TABLE [dbo].[ocms_template_properties] ADD CONSTRAINT [DF_ocms_template_properties_mandatory] DEFAULT ((0)) FOR [mandatory]
GO
ALTER TABLE [dbo].[ocms_template_properties] ADD CONSTRAINT [DF_ocms_template_properties_order] DEFAULT ((0)) FOR [order]
GO
ALTER TABLE [dbo].[ocms_useraccounts] ADD CONSTRAINT [DF_ocms_useraccounts_DateCreated] DEFAULT (getutcdate()) FOR [DateCreated]
GO
ALTER TABLE [dbo].[ocms_useraccounts] ADD CONSTRAINT [DF_ocms_useraccounts_DateModified] DEFAULT (getutcdate()) FOR [DateModified]
GO
ALTER TABLE [dbo].[ocms_items] WITH CHECK ADD CONSTRAINT [FK_ocms_items_ocms_items] FOREIGN KEY([ocms_iid])
REFERENCES [dbo].[ocms_items] ([ocms_iid])
GO
ALTER TABLE [dbo].[ocms_items] CHECK CONSTRAINT [FK_ocms_items_ocms_items]
GO
ALTER TABLE [dbo].[ocms_items] WITH CHECK ADD CONSTRAINT [FK_ocms_items_ocms_properties] FOREIGN KEY([ocms_pid])
REFERENCES [dbo].[ocms_properties] ([ocms_pid])
GO
ALTER TABLE [dbo].[ocms_items] CHECK CONSTRAINT [FK_ocms_items_ocms_properties]
GO
ALTER TABLE [dbo].[ocms_items] WITH CHECK ADD CONSTRAINT [FK_ocms_items_ocms_templates] FOREIGN KEY([template_id])
REFERENCES [dbo].[ocms_templates] ([ocms_tid])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[ocms_items] CHECK CONSTRAINT [FK_ocms_items_ocms_templates]
GO
ALTER TABLE [dbo].[ocms_items] WITH CHECK ADD CONSTRAINT [FK_ocms_items_parent__ocms_items] FOREIGN KEY([parent_iid])
REFERENCES [dbo].[ocms_items] ([ocms_iid])
GO
ALTER TABLE [dbo].[ocms_items] CHECK CONSTRAINT [FK_ocms_items_parent__ocms_items]
GO
ALTER TABLE [dbo].[ocms_properties] WITH CHECK ADD CONSTRAINT [FK_ocms_properties_ocms_items] FOREIGN KEY([ocms_iid])
REFERENCES [dbo].[ocms_items] ([ocms_iid])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[ocms_properties] CHECK CONSTRAINT [FK_ocms_properties_ocms_items]
GO
ALTER TABLE [dbo].[ocms_template_properties] WITH CHECK ADD CONSTRAINT [FK_ocms_template_properties_ocms_items] FOREIGN KEY([ocms_tid])
REFERENCES [dbo].[ocms_templates] ([ocms_tid])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[ocms_template_properties] CHECK CONSTRAINT [FK_ocms_template_properties_ocms_items]
GO
/****** Object: StoredProcedure [dbo].[ocms_admin_createUserAccount] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE PROCEDURE [dbo].[ocms_admin_createUserAccount]
@name nvarchar(255)
,@email varchar(255)
,@mobile varchar(50)
,@password nvarchar(20)
,@enc_key uniqueidentifier
AS
BEGIN
SET NOCOUNT ON;
DECLARE @utcnow datetime = GETUTCDATE();
INSERT INTO [dbo].[ocms_useraccounts]
([useraccount_id]
,[name_enc]
,[email_enc]
,[mobile_enc]
,[password_enc]
,[UserDisabled]
,[DateDisabled]
,[UserCreated]
,[DateCreated]
,[UserModified]
,[DateModified]
,[UserDeleted]
,[DateDeleted])
OUTPUT inserted.[useraccount_id]
VALUES
([dbo].[ocms_fn_useraccount_id] ()
,ENCRYPTBYKEY(@enc_key, @name)
,ENCRYPTBYKEY(@enc_key, @email)
,ENCRYPTBYKEY(@enc_key, @mobile)
,CASE WHEN @password is null then null ELSE ENCRYPTBYKEY(@enc_key, @password) END
,NULL --[UserDisabled]
,NULL --[DateDisabled]
,'sa'
,@utcnow
,'sa'
,@utcnow
,NULL
,NULL);
END
GO
/****** Object: StoredProcedure [dbo].[ocms_admin_debug_log] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Dr. Stefan Ott
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ocms_admin_debug_log]
@machine varchar(50)
,@error varchar(2000)
,@exception varchar(max)
,@procedure varchar(255)
,@authuser varchar(25)
,@data nvarchar(max)
,@isdebug bit
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [dbo].[ocms_debug]
([machine]
,[error]
,[exception]
,[procedure]
,[authuser]
,[data]
,[isdebug])
VALUES
(
@machine
,@error
,@exception
,@procedure
,@authuser
,@data
,@isdebug
);
END
GO
/****** Object: StoredProcedure [dbo].[ocms_cleanups] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ocms_cleanups]
AS
BEGIN
SET NOCOUNT ON;
/* UPDATE Template Property Numbering */
with tpo as (
select *
, ROW_NUMBER() OVER (PARTITION BY ocms_tid ORDER BY [order], ocms_tpid) as 'tpo'
from [dbo].[ocms_template_properties]
)
update tp
SET tp.[order] = tpo.[tpo]
From [dbo].[ocms_template_properties] as tp
JOIN tpo ON tp.[ocms_tpid] = tpo.[ocms_tpid];
/* UPDATE ITEM NUMBERING */
with ino as (
select *
, ROW_NUMBER() OVER (PARTITION BY [parent_iid] ORDER BY [order], [ocms_iid]) as 'itmo' -- 999 for any new
from [dbo].[ocms_items]
)
update i
SET i.[order] = ino.[itmo]
From [dbo].[ocms_items] as i
JOIN ino ON i.[ocms_iid] = ino.[ocms_iid]
WHERE i.[order] >= 0 and i.[parent_iid] is not null
END
GO
/****** Object: StoredProcedure [dbo].[ocms_cloneItem] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ocms_cloneItem]
@old_ocms_iid bigint
,@new_parent_iid bigint
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT * from [dbo].[ocms_items] as i where i.[ocms_iid] = @old_ocms_iid)
AND EXISTS (SELECT * from [dbo].[ocms_items] as i where i.[ocms_iid] = @new_parent_iid)
BEGIN
DECLARE @NEW TABLE ([ocms_iid] bigint, [old_ocms_pid] bigint);
INSERT INTO [dbo].[ocms_items]
([parent_iid]
,[ocms_pid]
,[name]
,[view]
,[template_id]
,[hide]
,[order]
,[DateCreated]
,[DateModified])
OUTPUT inserted.[ocms_iid], inserted.[ocms_pid] INTO @new
SELECT @new_parent_iid
,[ocms_pid]
,[name]
,[view]
,[template_id]
,[hide]
,[order]
,GETUTCDATE()
,GETUTCDATE()
FROM [dbo].[ocms_items] as i
WHERE [ocms_iid] = @old_ocms_iid;
DECLARE @new_ocms_iid bigint, @old_ocms_pid bigint;
SELECT TOP(1) @new_ocms_iid = [ocms_iid], @old_ocms_pid = [old_ocms_pid] FROM @NEW;
IF NOT EXISTS (SELECT pn.* from [dbo].[ocms_properties] as pn JOIN [dbo].[ocms_properties] as po ON pn.[key] = po.[key] and pn.[locale] = po.[locale]
WHERE pn.[ocms_iid] = @new_parent_iid AND po.[ocms_pid] = @old_ocms_pid)
BEGIN
DECLARE @newprop [dbo].[ocms_type_properties_base];
DECLARE @property_key varchar(50), @locale varchar(5), @new_ocms_pid bigint;
SELECT TOP(1) @property_key = [key], @locale = [locale] FROM [dbo].[ocms_properties] as p WHERE [ocms_pid] = @old_ocms_pid;
IF ISNULL(@property_key, '') <> ''
BEGIN
INSERT INTO @newprop VALUES(@new_parent_iid, @property_key, '', @locale);
EXECUTE [dbo].[ocms_merge_properties] @newprop; --makes sure is ok with template
END
SELECT TOP(1) @new_ocms_pid = [ocms_pid] FROM [dbo].[ocms_properties] WHERE [ocms_iid] = @new_parent_iid AND [key] = @property_key;
UPDATE [dbo].[ocms_items]
SET [ocms_pid] = @new_ocms_pid
,[DateModified] = GETUTCDATE()
WHERE [ocms_iid] = @new_ocms_iid;
END
IF @new_ocms_iid is not null
BEGIN
INSERT INTO [dbo].[ocms_properties]
([ocms_iid]
,[key]
,[value]
,[locale]
,[DateCreated]
,[DateModified]
)
SELECT
@new_ocms_iid
,[key]
,[value]
,[locale]
,GETUTCDATE()
,GETUTCDATE()
FROM [dbo].[ocms_properties]
WHERE [ocms_iid] = @old_ocms_iid;
END
END
END
GO
/****** Object: StoredProcedure [dbo].[ocms_createPropertyItem] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ocms_createPropertyItem]
@name nvarchar(255),
@parent_iid bigint,
@ocms_pid bigint,
@property_key varchar(50),
@template_id bigint,
@locale varchar(5)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @site bigint = (SELECT TOP(1) [ocms_iid] FROM [dbo].[ocms_items] as i WHERE i.[parent_iid] is null);
IF ISNULL(@name, '') = ''
THROW 61010, 'name missing', 1;
IF EXISTS (SELECT * FROM [dbo].[ocms_items] WHERE [name] = @name)
THROW 61010, 'name already exists', 1;
IF NOT EXISTS (SELECT * FROM [dbo].[ocms_items] WHERE [ocms_iid] = @parent_iid)
THROW 61010, 'parent does not exist', 1;
IF NOT EXISTS (SELECT * FROM [dbo].[ocms_templates] WHERE [ocms_tid] = @template_id)
THROW 61010, 'template does not exist', 1;
IF NOT EXISTS (SELECT * FROM [dbo].[ocms_properties] WHERE [ocms_iid] = @parent_iid AND ([ocms_pid] = @ocms_pid OR (@ocms_pid is null AND [key] = @property_key)))
BEGIN
DECLARE @newprop [dbo].[ocms_type_properties_base];
INSERT INTO @newprop VALUES(@parent_iid, @property_key, '', @locale);
EXECUTE [dbo].[ocms_merge_properties] @newprop; --makes sure is ok with template
END
SELECT TOP(1) @ocms_pid = [ocms_pid] FROM [dbo].[ocms_properties] WHERE [ocms_iid] = @parent_iid AND ([ocms_pid] = @ocms_pid OR (@ocms_pid is null AND [key] = @property_key));
--get last ocms_iid
DECLARE @lastiid int = ISNULL ( (SELECT MAX(ISNULL(p.[ocms_iid], ap.[ocms_iid]))
FROM [dbo].[ocms_items] as p FULL OUTER JOIN [ocms_archive__items] as ap on p.[ocms_iid] = ap.[ocms_iid])
,0);
IF NOT EXISTS (SELECT * FROM [dbo].[ocms_properties] WHERE [ocms_iid] = @parent_iid AND [ocms_pid] = @ocms_pid)
THROW 61010, 'parent''s property does not exist', 1;
IF @site is not null
BEGIN
MERGE [dbo].[ocms_items] as TARGET
USING (VALUES(
@parent_iid
,@ocms_pid
,@name
,0
,@template_id
,0
,999 -- append always
)) as SOURCE ([parent_iid]
,[ocms_pid]
,[name]
,[view]
,[template_id]
,[hide]
,[order])
ON TARGET.[name] = SOURCE.[name]
WHEN NOT MATCHED BY TARGET THEN
INSERT
([ocms_iid]
,[parent_iid]
,[ocms_pid]
,[name]
,[view]
,[template_id]
,[hide]
,[order]
,[DateCreated]
,[DateModified])
VALUES( @lastiid + 1, SOURCE.[parent_iid], SOURCE.[ocms_pid], SOURCE.[name], SOURCE.[view], SOURCE.[template_id], SOURCE.[hide], SOURCE.[order], GETUTCDATE(), GETUTCDATE())
OUTPUT inserted.[ocms_iid];
END
END
GO
/****** Object: StoredProcedure [dbo].[ocms_createView] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ocms_createView]
@name nvarchar(255),
@parent_iid bigint
AS
BEGIN
SET NOCOUNT ON;
DECLARE @site bigint = (SELECT TOP(1) [ocms_iid] FROM [dbo].[ocms_items] as i WHERE i.[parent_iid] is null);
DECLARE @template_id bigint = (SELECT TOP(1) [ocms_tid] FROM [dbo].[ocms_templates] as t WHERE t.[name] = 'view');
IF ISNULL(@name, '') = ''
THROW 61010, 'name missing', 1;
IF EXISTS (SELECT * FROM [dbo].[ocms_items] WHERE [name] = @name)
THROW 61110, 'name already exists', 1;
IF @site is null
THROW 61110, 'site does not exist', 1;
SELECT @parent_iid = [ocms_iid] FROM [dbo].[ocms_items] as i WHERE [ocms_iid] = @parent_iid AND (i.[parent_iid] is null OR i.[view] = 1);
--apply site as fallback
SET @parent_iid = ISNULL(@parent_iid, @site);
--get last ocms_iid
DECLARE @lastiid int = ISNULL ( (SELECT MAX(ISNULL(p.[ocms_iid], ap.[ocms_iid]))
FROM [dbo].[ocms_items] as p FULL OUTER JOIN [ocms_archive__items] as ap on p.[ocms_iid] = ap.[ocms_iid])
,0);
-- get locales setting
DECLARE @locales TABLE([locale] varchar(5), [order] int);
INSERT INTO @locales
SELECT * FROM [dbo].[ocms_locales]();
-- add item
DECLARE @output TABLE([ocms_iid] bigint);
DECLARE @ocms_iid bigint;
MERGE [dbo].[ocms_items] as TARGET
USING (VALUES(
@site
,NULL
,@name
,1
,@template_id
,0
,999 -- append always
)) as SOURCE ([parent_iid]
,[ocms_pid]
,[name]
,[view]
,[template_id]
,[hide]
,[order])
ON TARGET.[name] = SOURCE.[name]
WHEN NOT MATCHED BY TARGET THEN
INSERT
([ocms_iid]
,[parent_iid]
,[ocms_pid]
,[name]
,[view]
,[template_id]
,[hide]
,[order]
,[DateCreated]
,[DateModified])
VALUES(@lastiid + 1, SOURCE.[parent_iid], SOURCE.[ocms_pid], SOURCE.[name], SOURCE.[view], SOURCE.[template_id], SOURCE.[hide], SOURCE.[order], GETUTCDATE(), GETUTCDATE())
OUTPUT inserted.[ocms_iid]
INTO @output;
SELECT TOP(1) @ocms_iid = [ocms_iid] FROM @output;
-- add navigation
-- update nav table
UPDATE n
SET n.[parent_iid] = i.[parent_iid]
FROM [dbo].[ocms_navigation] as n
JOIN [ocms_items] as i ON n.[ocms_iid] = i.[ocms_iid];
DECLARE @newnav [dbo].[ocms_type_navigation_base];
DECLARE @suffix int = 0;
INSERT INTO @newnav
SELECT @ocms_iid, [locale], @name + '_' + [locale]
FROM @locales as l
WHERE l.[locale] <> '*';
WHILE EXISTS (SELECT * FROM [dbo].[ocms_navigation] as cn JOIN @newnav as nn
ON cn.[parent_iid] = @parent_iid
AND cn.[alias] = nn.[alias])
BEGIN
SET @suffix = @suffix + 1;
UPDATE @newnav
SET [alias] = @name + '_' + [locale] + CAST(@suffix as varchar(3));
END
EXECUTE [dbo].[ocms_merge_navigation] @newnav;
--output
SELECT @ocms_iid;
END
GO
/****** Object: StoredProcedure [dbo].[ocms_deleteItem] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ocms_deleteItem]
@ocms_iid bigint
AS
BEGIN
SET NOCOUNT ON;
DECLARE @children TABLE([ocms_iid] bigint, [parent_iid] bigint);
with cte as ( select [ocms_iid], [parent_iid] from [ocms_items] as i WHERE i.[parent_iid] = @ocms_iid --only children and below
UNION ALL
select i.[ocms_iid], i.[parent_iid] from [ocms_items] as i JOIN cte on cte.ocms_iid = i.[parent_iid]
)
insert into @children select * from cte;
--first delete childrens properties, bc else the foreign key constraint will prevent to continue */
DELETE p
OUTPUT deleted.*, GETUTCDATE(), null
INTO [dbo].[ocms_archive__properties]
FROM [dbo].[ocms_properties] as p
WHERE p.[ocms_iid] in (SELECT c.[ocms_iid] FROM @children as c); -- only children's as selected at beginning /
DELETE i
OUTPUT deleted.*, GETUTCDATE(), null
INTO [dbo].[ocms_archive__items]
FROM [ocms_items] as i
WHERE i.[ocms_iid] in (SELECT c.[ocms_iid] FROM @children as c);; -- only children as selected at beginning /
DELETE p
OUTPUT deleted.*, GETUTCDATE(), null
INTO [dbo].[ocms_archive__properties]
FROM [dbo].[ocms_properties] as p
WHERE p.[ocms_iid] = @ocms_iid; -- only children's as selected at beginning /
DELETE FROM [ocms_items]
OUTPUT deleted.*, GETUTCDATE(), null
INTO [dbo].[ocms_archive__items]
WHERE [ocms_iid] = @ocms_iid;
SELECT TOP(1) [ocms_iid]
FROM [dbo].[ocms_archive__items]
WHERE [ocms_iid] = @ocms_iid;
END
GO
/****** Object: StoredProcedure [dbo].[ocms_getAvailableTemplates] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ocms_getAvailableTemplates]
@ocms_iid bigint
,@ocms_pid bigint
,@property_key varchar(50)
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS (SELECT * FROM [dbo].[ocms_items] WHERE [ocms_iid] = @ocms_iid)
THROW 61010, 'item does not exist', 1;
DECLARE @TP TABLE(
[ocms_tpid] [bigint] NOT NULL,
[ocms_pid] [bigint],
[condition] [varchar](max) NULL
);
INSERT INTO @TP
SELECT tp.[ocms_tpid], p.[ocms_pid], tp.[condition]
FROM [dbo].[ocms_items] as i
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 tp.[type] = 'array'
LEFT JOIN [dbo].[ocms_properties] as p ON i.[ocms_iid] = p.[ocms_iid] AND p.[key] = tp.[key]
WHERE i.[ocms_iid] = @ocms_iid
AND (p.[ocms_pid] = @ocms_pid OR (@ocms_pid is null AND tp.[key] = @property_key))
AND tp.[type] = 'array';
IF NOT EXISTS (SELECT * FROM @TP)
THROW 61010, 'items''s property type is not array', 1;
DECLARE @condition varchar(max);
SET @condition = ISNULL((SELECT TOP(1) [condition] FROM @TP), '');
DECLARE @TIDS TABLE ([ocms_tid] bigint);
IF LEFT(@condition, 1) = '{' AND RIGHT(@condition, 1) = '}'
BEGIN
INSERT INTO @TIDS SELECT RTRIM(LTRIM(value)) FROM string_split(SUBSTRING(@condition,2,LEN(@condition) - 2), ',');
END
ELSE IF @condition <> ''
BEGIN
INSERT INTO @TIDS SELECT [ocms_tid] FROM [ocms_templates] WHERE [name] like @condition;
END
--output
SELECT t.[ocms_tid],t.[name] FROM [dbo].[ocms_templates] as t JOIN @TIDS as tids ON t.[ocms_tid] = tids.ocms_tid;
--select STUFF(
-- (SELECT ',' + [name] as [text()]
-- FROM [dbo].[ocms_templates]
-- WHERE name like 'section%'
-- FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)')
-- ,1,1,'')
END
GO
/****** Object: StoredProcedure [dbo].[ocms_getFullTree] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ocms_getFullTree]
@locale varchar(5)
AS
BEGIN
/* FULL TREE is only intended for OCMS admin portal
Hidden items will be shown !
*/
SET NOCOUNT ON;
-- get basics
DECLARE @site bigint = (SELECT TOP(1) [ocms_iid] FROM [dbo].[ocms_items] as i WHERE i.[parent_iid] is null);
IF @site is null
THROW 61110, 'site does not exist', 1;
--get locales
DECLARE @locales TABLE([locale] varchar(5), [order] int);
INSERT INTO @locales
SELECT * FROM [dbo].[ocms_locales]();
-- calculate tree and paths
DECLARE @itemtree TABLE(
[ocms_iid] [bigint] NOT NULL,
[parent_iid] [bigint] NULL,
[ocms_pid] [bigint] NULL,
[name] [nvarchar](255) NULL,
[view] [bit] NOT NULL,
[template_id] [bigint] NULL,
[hide] [bit] NOT NULL,
[order] [int] NOT NULL,
[DateCreated] datetime null,
[DateModified] datetime null,
[level] int, [new_order] int
);
WITH cteLevels as (
SELECT t.*
, cast(0 as int) as [level]
FROM [dbo].[ocms_items] t
WHERE t.[ocms_iid] = @site
UNION ALL
SELECT t.*
, CAST(c.[level] + 1 as int) as [level]
FROM [dbo].[ocms_items] t
inner JOIN cteLevels c on t.[parent_iid] = c.[ocms_iid]
)
INSERT INTO @itemtree
SELECT t.*
, ROW_NUMBER() OVER (PARTITION BY [parent_iid] ORDER BY [order], [ocms_iid]) as [new_order]
FROM cteLevels as t;
--WHERE [hide] = 0;
-- output site properties
WITH SP As (
SELECT
p.*
, ROW_NUMBER() OVER (PARTITION BY p.[ocms_iid], p.[key] ORDER BY CAST((CASE WHEN p.[locale] = '*' THEN 1 ELSE 0 END) as int)) as 'rwi'
FROM @itemtree as it
JOIN [dbo].[ocms_properties] as p ON it.[ocms_iid] = p.[ocms_iid]
WHERE it.[level] = 0 --site
AND (p.[locale] = @locale OR p.[locale] = '*')
)
SELECT
it.[ocms_iid]
,p.[ocms_pid]
,ISNULL(tp.[key], p.[key]) as [key]
,IIF(ISNULL(tp.[mandatory],0) = 0, p.[value], ISNULL(p.[value],'')) as [value]
,tp.[type]
FROM @itemtree as it
FULL OUTER JOIN [dbo].[ocms_template_properties] as tp ON it.[template_id] = tp.[ocms_tid]
FULL OUTER JOIN SP as p ON p.[ocms_iid] = it.[ocms_iid] AND (tp.[key] = p.[key] OR tp.[key] is null)
AND p.[rwi] = 1 -- only the right language or fallback
WHERE it.[level] = 0;
-- output locales
SELECT * FROM @locales;
-- output children
SELECT
it.*
,p.[key] as 'property_key'
,p.[locale] as 'property_locale'
,tpl.[name] as 'template_name'
,ROW_NUMBER() OVER (PARTITION BY it.[parent_iid] ORDER BY IIF(it.[view] = 1, 0, 1), it.[ocms_pid], it.[ORDER], it.[ocms_iid]) as 'item_order'
FROM @itemtree as it
LEFT JOIN [dbo].[ocms_templates] as tpl on it.[template_id] = tpl.[ocms_tid]
LEFT JOIN [dbo].[ocms_properties] as p on it.[ocms_pid] = p.[ocms_pid]; --only arrays
-- output childrens' properties ONLY arrays
SELECT
it.[ocms_iid]
,p.[ocms_pid]
,ISNULL(tp.[key], p.[key]) as [key]
,IIF(ISNULL(tp.[mandatory],0) = 0, p.[value], ISNULL(p.[value],'')) as [value]
,p.locale
,tp.[type]
FROM @itemtree as it
JOIN [dbo].[ocms_template_properties] as tp ON it.[template_id] = tp.[ocms_tid]
FULL OUTER JOIN [dbo].[ocms_properties] as p ON p.[ocms_iid] = it.[ocms_iid] AND (tp.[key] = p.[key] OR tp.[key] is null)
WHERE ISNULL(ISNULL(tp.[key], p.[key]),'') <> ''
AND tp.[type] = 'array';
END
GO
/****** Object: StoredProcedure [dbo].[ocms_getItem] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ocms_getItem]
@ocms_iid bigint
AS
BEGIN
SET NOCOUNT ON;
-- get basics
DECLARE @site bigint = (SELECT TOP(1) [ocms_iid] FROM [dbo].[ocms_items] as i WHERE i.[parent_iid] is null);
IF @site is null
THROW 61110, 'site does not exist', 1;
--get locales
DECLARE @locales TABLE([locale] varchar(5), [order] int);
INSERT INTO @locales
SELECT * FROM [dbo].[ocms_locales]();
-- get viewtree and paths
DECLARE @viewtree [dbo].[ocms_type_viewtree_full];
INSERT INTO @viewtree
SELECT * FROM [dbo].[ocms_getFullViewTree](null, null) -- get full with all languages
WHERE [ocms_iid] = @ocms_iid; -- only selected view
--get itemtree
DECLARE @itemtree [dbo].[ocms_type_itemtree];
INSERT INTO @itemtree SELECT * FROM [dbo].[ocms_getFullItemTree](@ocms_iid, 1);
/*-----------------------------------------------------------------------------------------------------------------------------------------------------*/
-- output site properties
SELECT
t.[ocms_iid]
,p.[ocms_pid]
,ISNULL(tp.[key], p.[key]) as [key]
,IIF(ISNULL(tp.[mandatory],0) = 0, p.[value], ISNULL(p.[value],'')) as [value]
,tp.[type]
FROM @itemtree as t
FULL OUTER JOIN [dbo].[ocms_template_properties] as tp ON t.[template_id] = tp.[ocms_tid]
FULL OUTER JOIN [dbo].[ocms_properties] as p ON p.[ocms_iid] = t.[ocms_iid] AND p.[value] is not null AND (tp.[key] = p.[key] OR tp.[key] is null)
WHERE t.[level] = 0;
-- output locales
SELECT * FROM @locales;
-- output item
SELECT TOP(1)
it.*
,p.[key] as 'property_key'
,p.[locale] as 'property_locale'
,tpl.[name] as 'template_name'
,ROW_NUMBER() OVER (PARTITION BY it.[parent_iid], it.[ocms_pid] ORDER BY it.[ORDER], it.[ocms_iid]) as 'item_order'
FROM @itemtree as it --not necessary a view
LEFT JOIN [dbo].[ocms_templates] as tpl on it.[template_id] = tpl.[ocms_tid]
LEFT JOIN [dbo].[ocms_properties] as p on it.[ocms_pid] = p.ocms_pid and p.[value] is not null
WHERE it.[ocms_iid] = @ocms_iid; -- item in focus
-- output children
SELECT
it.*
,p.[key] as 'property_key'
,p.[locale] as 'property_locale'
,tpl.[name] as 'template_name'
,ROW_NUMBER() OVER (PARTITION BY it.[parent_iid], it.[ocms_pid] ORDER BY it.[ORDER], it.[ocms_iid]) as 'item_order'
FROM @itemtree as it
LEFT JOIN [dbo].[ocms_templates] as tpl on it.[template_id] = tpl.[ocms_tid]
LEFT JOIN [dbo].[ocms_properties] as p on it.[ocms_pid] = p.ocms_pid and p.[value] is not null
WHERE it.[ocms_iid] <> @ocms_iid
AND it.[sub] = 1;
-- output items properties
WITH tp as (
SELECT tp_.*, ROW_NUMBER() OVER (ORDER BY tp_.[order], tp_.[ocms_tpid], tp_.[key]) as 'tpo', l.locale, l.[order] as [locorder] FROM [dbo].[ocms_template_properties] as tp_ CROSS JOIN @locales as l -- incl. fallback locale '*'
)
SELECT
it.[ocms_iid]
,p.[ocms_pid]
,ISNULL(tp.[key], p.[key]) as [key]
,IIF(ISNULL(tp.[mandatory],0) = 0, p.[value], ISNULL(p.[value],'')) as [value]
,tp.locale
,tp.[type]
,tp.[mandatory]
,IIF(tp.[type] <> 'array', tp.[condition], '') as [condition]
,ROW_NUMBER() OVER (ORDER BY it.[ocms_iid], tp.[tpo], p.[ocms_pid]) as [order]
FROM @itemtree as it
JOIN tp ON it.[template_id] = tp.[ocms_tid]
FULL OUTER JOIN [dbo].[ocms_properties] as p
ON p.[ocms_iid] = it.[ocms_iid]
AND p.[value] is not null
AND (tp.[key] = p.[key] OR tp.[key] is null)
AND p.[locale] = tp.[locale]
WHERE ISNULL(ISNULL(tp.[key], p.[key]),'') <> ''
AND it.[ocms_iid] = @ocms_iid;
-- output items navs
WITH nav as (
select [ocms_iid], [locale], [alias]
from [dbo].[ocms_navigation] as n WHERE ISNULL(n.[alias], '') <> '' AND n.[locale] <> '*'
UNION
SELECT i.[ocms_iid], l.[locale], i.[name]
FROM [dbo].[ocms_items] as i
CROSS JOIN @locales as l
WHERE i.[parent_iid] = @site AND i.[name] = 'index' and l.[locale] <> '*'
)SELECT
l.[locale]
,n.[alias]
,vt.[path]
FROM @viewtree as vt
CROSS JOIN @locales as l
LEFT JOIN nav as n ON vt.[ocms_iid] = n.[ocms_iid] AND l.[locale] = n.[locale]
WHERE vt.[ocms_iid] = @ocms_iid
AND l.[locale] <> '*';
END
GO
/****** Object: StoredProcedure [dbo].[ocms_getItem_byName] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ocms_getItem_byName]
@name nvarchar(255)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ocms_iid bigint = (SELECT TOP(1) [ocms_iid] FROM [dbo].[ocms_items] as i WHERE [name] = @name);
EXECUTE [dbo].[ocms_getItem] @ocms_iid;
END
GO
/****** Object: StoredProcedure [dbo].[ocms_getView] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ocms_getView]
@view_path varchar(255)
,@locale varchar(5)
AS
BEGIN
SET NOCOUNT ON;
IF ISNULL(@locale, '') = ''
SET @locale = 'de'; -- fallback
IF ISNULL(@view_path, '') = ''
SET @view_path = 'index';
-- get basics
DECLARE @site bigint = (SELECT TOP(1) [ocms_iid] FROM [dbo].[ocms_items] as i WHERE i.[parent_iid] is null);
IF @site is null
THROW 61110, 'site does not exist', 1;
--get locales
DECLARE @locales TABLE([locale] varchar(5), [order] int);
INSERT INTO @locales
SELECT * FROM [dbo].[ocms_locales]();
-- get viewtree and paths
DECLARE @view_iid bigint = null, @view_level int, @view_name nvarchar(255), @view_is_hidden bit;
DECLARE @viewtree [dbo].[ocms_type_viewtree_full];
INSERT INTO @viewtree SELECT * FROM [dbo].[ocms_getFullViewTree](@view_path, null); -- any locale to be able to determine language from path
SELECT TOP(1)
@view_iid = it.[ocms_iid]
, @view_level = it.[level]
, @locale = it.[locale] -- will override the selected language
, @view_name = it.[name]
, @view_is_hidden = it.[hide]
FROM @viewtree as it
ORDER BY (CASE WHEN [focus] = 1 AND [locale] = @locale THEN 0 WHEN [focus] = 1 THEN 1 ELSE 2 END) ASC;
--get itemtree
DECLARE @itemtree [dbo].[ocms_type_itemtree];
INSERT INTO @itemtree SELECT * FROM [dbo].[ocms_getFullItemTree](@view_iid, @view_is_hidden); -- items are not localized
--get properties
DECLARE @PROP TABLE(
[ocms_pid] [bigint] NOT NULL,
[ocms_iid] [bigint] NOT NULL,
[key] [varchar](50) NULL,
[value] [nvarchar](max) NOT NULL,
[DateModified] [datetime] NULL
);
WITH SP As (
SELECT
p.*
,it.[focus]
,it.[level]
, ROW_NUMBER() OVER (PARTITION BY p.[ocms_iid], p.[key] ORDER BY CAST((CASE WHEN p.[locale] = '*' THEN 1 ELSE 0 END) as int)) as 'rwi'
FROM @itemtree as it
JOIN [dbo].[ocms_properties] as p ON it.[ocms_iid] = p.[ocms_iid]
WHERE (it.[level] = 0 --site
OR it.[sub] = 1
OR it.[focus] = 1
OR it.[parent_iid] = @site) -- or itms below selected
AND (p.[locale] = @locale OR p.[locale] = '*')
)
INSERT INTO @PROP
SELECT [ocms_pid],[ocms_iid],[key],[value],[DateModified]
FROM SP
WHERE rwi = 1;
DECLARE @translations TABLE (
[ocms_tln] [bigint] NOT NULL,
[context_typ] [nvarchar](15) NOT NULL,
[context] [bigint] NULL,
[value] [nvarchar](255) NOT NULL,
[translation] [nvarchar](255) NOT NULL,
[locale] [varchar](5) NOT NULL
);
/* outputs *********************************************************************************************************************************************************/
-- output nav
SELECT vt.[ocms_iid]
,[parent_iid]
,vt.[ocms_pid]
,[name]
,ISNULL(p.[value], vt.[name]) as [title]
,[view]
,[template_id]
,[hide]
,[new_order] as [order]
, [path], [level], [sub], [focus], [nav]
FROM @viewtree as vt -- vt must be filtered by locale
LEFT JOIN [dbo].[ocms_properties] as p ON vt.[ocms_iid] = p.[ocms_iid] AND p.[key] = 'title' AND p.[locale] = vt.[locale] -- ! localized
WHERE [nav] = 1
AND vt.[locale] = @locale;
-- output site properties
SELECT
vt.[ocms_iid]
,p.[ocms_pid]
,ISNULL(tp.[key], p.[key]) as [key]
,IIF(ISNULL(tp.[mandatory],0) = 0, p.[value], ISNULL(p.[value],'')) as [value]
,tp.[type]
FROM @viewtree as vt -- site is part if viewtree // -- vt must be filtered by locale
FULL OUTER JOIN [dbo].[ocms_template_properties] as tp ON vt.[template_id] = tp.[ocms_tid]
FULL OUTER JOIN @PROP as p ON p.[ocms_iid] = vt.[ocms_iid] AND (tp.[key] = p.[key] OR tp.[key] is null) -- properties are ! localized, but already filtered for @PROP
WHERE vt.[ocms_iid] = @site
AND vt.[locale] = @locale
UNION
SELECT
NULL as [ocms_iid]
,NULL as [ocms_pid]
,'DateModified' as [key]
,FORMAT((SELECT MAX([DateModified]) FROM @PROP),'yyyy-MM-ddTHH:mm:ssZ') as [value]
,'string';
INSERT INTO @translations
SELECT * from [dbo].[ocms_translations] as TL WHERE TL.[context_typ] = 'site' AND TL.[locale] = @locale;
-- output locales
WITH pd as (SELECT DISTINCT [ocms_iid], [path], [locale] FROM @viewtree WHERE [ocms_iid] = @view_iid)
SELECT
pd.*
, CAST(IIF(l.[locale] = @locale, 1, 0) as bit) as 'active'
FROM @locales as l JOIN pd ON l.[locale] = pd.[locale]
WHERE l.[locale] <> '*';
-- output view properties
SELECT
vt.[ocms_iid]
,p.[ocms_pid]
,ISNULL(tp.[key], p.[key]) as [key]
,IIF(ISNULL(tp.[mandatory],0) = 0, p.[value], ISNULL(p.[value],'')) as [value]
,tp.[type]
FROM @viewtree as vt -- vt must be filtered by locale
FULL OUTER JOIN [dbo].[ocms_template_properties] as tp ON vt.[template_id] = tp.[ocms_tid]
FULL OUTER JOIN @PROP as p ON p.[ocms_iid] = vt.[ocms_iid] AND (tp.[key] = p.[key] OR tp.[key] is null) -- properties are ! localized, but already filtered for @PROP
WHERE vt.[focus] = 1
AND ISNULL(ISNULL(tp.[key], p.[key]),'') <> ''
AND vt.[locale] = @locale;
INSERT INTO @translations
SELECT * from [dbo].[ocms_translations] as TL
WHERE TL.[context] = @view_iid AND TL.[locale] = @locale; --context = 'view' and all other context_types
-- output children
SELECT
it.*
,tpl.[partial_view]
,tpl.[name] as [template_name]
,ROW_NUMBER() OVER (PARTITION BY it.[parent_iid], it.[ocms_pid] ORDER BY it.[ORDER], it.[ocms_iid]) as 'item_order'
FROM @itemtree as it -- items are not localized
LEFT JOIN [dbo].[ocms_templates] as tpl on it.[template_id] = tpl.[ocms_tid]
WHERE [sub] = 1 OR it.[parent_iid] = @site; --to include also site relevant children
INSERT INTO @translations
SELECT TL.* from @itemtree as it JOIN [dbo].[ocms_translations] as TL ON TL.[context_typ] = 'template' AND TL.[context] = it.[template_id] AND TL.[locale] = @locale;
-- output childrens' properties
SELECT
it.[ocms_iid]
,p.[ocms_pid]
,ISNULL(tp.[key], p.[key]) as [key]
,IIF(ISNULL(tp.[mandatory],0) = 0, p.[value], ISNULL(p.[value],'')) as [value]
,tp.[type]
FROM @itemtree as it -- items are not localized
JOIN [dbo].[ocms_template_properties] as tp ON it.[template_id] = tp.[ocms_tid] -- properties are ! localized, but already filtered for @PROP
FULL OUTER JOIN @PROP as p ON p.[ocms_iid] = it.[ocms_iid] AND (tp.[key] = p.[key] OR tp.[key] is null)
WHERE ([sub] = 1 OR it.[parent_iid] = @site) AND [focus] = 0
AND ISNULL(ISNULL(tp.[key], p.[key]),'') <> '';
--output translations
SELECT DISTINCT * FROM @translations;
--output breadcrumbs
WITH vws as(
SELECT vt.[ocms_iid], [parent_iid], ISNULL(p.[value], vt.[name]) as [title], [path] FROM @viewtree as vt -- vt must be filtered by locale
LEFT JOIN [dbo].[ocms_properties] as p ON vt.[ocms_iid] = p.[ocms_iid] AND p.[key] = 'title' AND p.[locale] = vt.[locale] -- ! localized
)
,
cte as (
SELECT vws.*, [title] as [breadcrumb], 0 as [level]
FROM vws
WHERE vws.[ocms_iid] = @view_iid
UNION ALL
SELECT vws.*, ( vws.[title] + ' > ' + cte.[breadcrumb]) as [breadcrumb]
, [level] + 1 as [level]
FROM vws JOIN cte ON vws.[ocms_iid] = cte.[parent_iid]
)
select [title]
, [path]
, CASE WHEN [level] = 0 THEN (SELECT TOP(1) [breadcrumb] FROM cte as cte1 ORDER BY [level] DESC) ELSE NULL END as [breadcrumb]
, ROW_NUMBER() OVER (ORDER BY [level] DESC) as [order]
FROM cte;
END
GO
/****** Object: StoredProcedure [dbo].[ocms_getView_byName] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ocms_getView_byName]
@name nvarchar(255)
,@locale varchar(5)
,@include_hidden bit
AS
BEGIN
SET NOCOUNT ON;
IF ISNULL(@locale, '') = ''
SET @locale = 'de'; -- fallback
DECLARE @view_path varchar(255) = 'index';
-- get basics
DECLARE @site bigint = (SELECT TOP(1) [ocms_iid] FROM [dbo].[ocms_items] as i WHERE i.[parent_iid] is null);
IF @site is null
THROW 61110, 'site does not exist', 1;
--get locales
DECLARE @locales TABLE([locale] varchar(5), [order] int);
INSERT INTO @locales
SELECT * FROM [dbo].[ocms_locales]();
-- get viewtree and paths
DECLARE @view_iid bigint = null, @view_level int, @view_name nvarchar(255);
DECLARE @viewtree [dbo].[ocms_type_viewtree_full];
INSERT INTO @viewtree SELECT * FROM [dbo].[ocms_getFullViewTree](@view_path, null); -- any locale to be able to determine language from path
DECLARE @tgt_ocms_iid bigint = (SELECT TOP(1) [ocms_iid] FROM [dbo].[ocms_items] as i WHERE [name] = @name and (i.[hide] = 0 OR @include_hidden = 1));
SELECT TOP(1)
@view_iid = it.[ocms_iid]
, @view_level = it.[level]
, @locale = it.[locale] -- will override the selected language
, @view_name = it.[name]
FROM @viewtree as it
WHERE it.[ocms_iid] = @tgt_ocms_iid
ORDER BY (CASE WHEN [focus] = 1 AND [locale] = @locale THEN 0 WHEN [focus] = 1 THEN 1 ELSE 2 END) ASC;
--get itemtree
DECLARE @itemtree [dbo].[ocms_type_itemtree];
INSERT INTO @itemtree SELECT * FROM [dbo].[ocms_getFullItemTree](@view_iid, @include_hidden); -- items are not localized
--get properties
DECLARE @PROP TABLE(
[ocms_pid] [bigint] NOT NULL,
[ocms_iid] [bigint] NOT NULL,
[key] [varchar](50) NULL,
[value] [nvarchar](max) NOT NULL,
[DateModified] [datetime] NULL
);
WITH SP As (
SELECT
p.*
,it.[focus]
,it.[level]
, ROW_NUMBER() OVER (PARTITION BY p.[ocms_iid], p.[key] ORDER BY CAST((CASE WHEN p.[locale] = '*' THEN 1 ELSE 0 END) as int)) as 'rwi'
FROM @itemtree as it
JOIN [dbo].[ocms_properties] as p ON it.[ocms_iid] = p.[ocms_iid]
WHERE (it.[level] = 0 --site
OR it.[sub] = 1
OR it.[focus] = 1
OR it.[parent_iid] = @site) -- or itms below selected
AND (p.[locale] = @locale OR p.[locale] = '*')
)
INSERT INTO @PROP
SELECT [ocms_pid],[ocms_iid],[key],[value],[DateModified]
FROM SP
WHERE rwi = 1;
DECLARE @translations TABLE (
[ocms_tln] [bigint] NOT NULL,
[context_typ] [nvarchar](15) NOT NULL,
[context] [bigint] NULL,
[value] [nvarchar](255) NOT NULL,
[translation] [nvarchar](255) NOT NULL,
[locale] [varchar](5) NOT NULL
);
/* outputs *********************************************************************************************************************************************************/
-- output nav
SELECT vt.[ocms_iid]
,[parent_iid]
,vt.[ocms_pid]
,[name]
,ISNULL(p.[value], vt.[name]) as [title]
,[view]
,[template_id]
,[hide]
,[new_order] as [order]
, [path], [level], [sub], [focus], [nav]
FROM @viewtree as vt -- vt must be filtered by locale
LEFT JOIN [dbo].[ocms_properties] as p ON vt.[ocms_iid] = p.[ocms_iid] AND p.[key] = 'title' AND p.[locale] = vt.[locale] -- ! localized
WHERE [nav] = 1
AND vt.[locale] = @locale;
-- output site properties
SELECT
vt.[ocms_iid]
,p.[ocms_pid]
,ISNULL(tp.[key], p.[key]) as [key]
,IIF(ISNULL(tp.[mandatory],0) = 0, p.[value], ISNULL(p.[value],'')) as [value]
,tp.[type]
FROM @viewtree as vt -- site is part if viewtree // -- vt must be filtered by locale
FULL OUTER JOIN [dbo].[ocms_template_properties] as tp ON vt.[template_id] = tp.[ocms_tid]
FULL OUTER JOIN @PROP as p ON p.[ocms_iid] = vt.[ocms_iid] AND (tp.[key] = p.[key] OR tp.[key] is null) -- properties are ! localized, but already filtered for @PROP
WHERE vt.[ocms_iid] = @site
AND vt.[locale] = @locale
UNION
SELECT
NULL as [ocms_iid]
,NULL as [ocms_pid]
,'DateModified' as [key]
,FORMAT((SELECT MAX([DateModified]) FROM @PROP),'yyyy-MM-ddTHH:mm:ssZ') as [value]
,'string';
INSERT INTO @translations
SELECT * from [dbo].[ocms_translations] as TL WHERE TL.[context_typ] = 'site' AND TL.[locale] = @locale;
-- output locales
WITH pd as (SELECT DISTINCT [ocms_iid], [path], [locale] FROM @viewtree WHERE [ocms_iid] = @view_iid)
SELECT
pd.*
, CAST(IIF(l.[locale] = @locale, 1, 0) as bit) as 'active'
FROM @locales as l JOIN pd ON l.[locale] = pd.[locale]
WHERE l.[locale] <> '*';
-- output view properties
SELECT
vt.[ocms_iid]
,p.[ocms_pid]
,ISNULL(tp.[key], p.[key]) as [key]
,IIF(ISNULL(tp.[mandatory],0) = 0, p.[value], ISNULL(p.[value],'')) as [value]
,tp.[type]
FROM @viewtree as vt -- vt must be filtered by locale
FULL OUTER JOIN [dbo].[ocms_template_properties] as tp ON vt.[template_id] = tp.[ocms_tid]
FULL OUTER JOIN @PROP as p ON p.[ocms_iid] = vt.[ocms_iid] AND (tp.[key] = p.[key] OR tp.[key] is null) -- properties are ! localized, but already filtered for @PROP
WHERE vt.[focus] = 1
AND ISNULL(ISNULL(tp.[key], p.[key]),'') <> ''
AND vt.[locale] = @locale;
INSERT INTO @translations
SELECT * from [dbo].[ocms_translations] as TL
WHERE TL.[context] = @view_iid AND TL.[locale] = @locale; --context = 'view' and all other context_types
-- output children
SELECT
it.*
,tpl.[partial_view]
,tpl.[name] as [template_name]
,ROW_NUMBER() OVER (PARTITION BY it.[parent_iid], it.[ocms_pid] ORDER BY it.[ORDER], it.[ocms_iid]) as 'item_order'
FROM @itemtree as it -- items are not localized
LEFT JOIN [dbo].[ocms_templates] as tpl on it.[template_id] = tpl.[ocms_tid]
WHERE [sub] = 1 OR it.[parent_iid] = @site; --to include also site relevant children
INSERT INTO @translations
SELECT TL.* from @itemtree as it JOIN [dbo].[ocms_translations] as TL ON TL.[context_typ] = 'template' AND TL.[context] = it.[template_id] AND TL.[locale] = @locale;
-- output childrens' properties
SELECT
it.[ocms_iid]
,p.[ocms_pid]
,ISNULL(tp.[key], p.[key]) as [key]
,IIF(ISNULL(tp.[mandatory],0) = 0, p.[value], ISNULL(p.[value],'')) as [value]
,tp.[type]
FROM @itemtree as it -- items are not localized
JOIN [dbo].[ocms_template_properties] as tp ON it.[template_id] = tp.[ocms_tid] -- properties are ! localized, but already filtered for @PROP
FULL OUTER JOIN @PROP as p ON p.[ocms_iid] = it.[ocms_iid] AND (tp.[key] = p.[key] OR tp.[key] is null)
WHERE ([sub] = 1 OR it.[parent_iid] = @site) AND [focus] = 0
AND ISNULL(ISNULL(tp.[key], p.[key]),'') <> '';
--output translations
SELECT DISTINCT * FROM @translations;
--output breadcrumbs
WITH vws as(
SELECT vt.[ocms_iid], [parent_iid], ISNULL(p.[value], vt.[name]) as [title], [path] FROM @viewtree as vt -- vt must be filtered by locale
LEFT JOIN [dbo].[ocms_properties] as p ON vt.[ocms_iid] = p.[ocms_iid] AND p.[key] = 'title' AND p.[locale] = vt.[locale] -- ! localized
)
,
cte as (
SELECT vws.*, [title] as [breadcrumb], 0 as [level]
FROM vws
WHERE vws.[ocms_iid] = @view_iid
UNION ALL
SELECT vws.*, ( vws.[title] + ' > ' + cte.[breadcrumb]) as [breadcrumb]
, [level] + 1 as [level]
FROM vws JOIN cte ON vws.[ocms_iid] = cte.[parent_iid]
)
select [title]
, [path]
, CASE WHEN [level] = 0 THEN (SELECT TOP(1) [breadcrumb] FROM cte as cte1 ORDER BY [level] DESC) ELSE NULL END as [breadcrumb]
, ROW_NUMBER() OVER (ORDER BY [level] DESC) as [order]
FROM cte;
END
GO
/****** Object: StoredProcedure [dbo].[ocms_merge_navigation] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ocms_merge_navigation]
@newnav [dbo].[ocms_type_navigation_base] READONLY
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- update nav table
UPDATE n
SET n.[parent_iid] = i.[parent_iid]
FROM [dbo].[ocms_navigation] as n
JOIN [ocms_items] as i ON n.[ocms_iid] = i.[ocms_iid];
-- merge nav
WITH np as (
-- make sure nav are allowed by locales and path constraints
SELECT
n.*
,i.[parent_iid]
FROM @newnav as n
JOIN [dbo].[ocms_items] as i ON n.[ocms_iid] = i.[ocms_iid] AND [view] = 1
FULL OUTER JOIN [dbo].[ocms_locales]() as l on n.[locale] = l.[locale] AND l.[locale] <> '*'
WHERE NOT EXISTS (SELECT * FROM [dbo].[ocms_navigation] as cn
WHERE cn.[parent_iid] = i.[parent_iid]
AND cn.[alias] = n.[alias]
AND [cn].[ocms_iid] <> n.[ocms_iid])
AND i.[name] <> 'index' -- nav not allowed for index
AND n.[alias] not in ('index') -- keyword index not allowed
)
MERGE [dbo].[ocms_navigation] as TARGET
USING np as SOURCE ON TARGET.[locale] = SOURCE.[locale] AND TARGET.[ocms_iid] = SOURCE.[ocms_iid]
WHEN MATCHED AND SOURCE.[alias] is null THEN
DELETE
WHEN MATCHED THEN
UPDATE SET [alias] = LOWER(SOURCE.[alias])
WHEN NOT MATCHED BY TARGET THEN
INSERT ([ocms_iid], [locale], [alias])
VALUES (SOURCE.[ocms_iid], SOURCE.[locale], LOWER(SOURCE.[alias]));
END
GO
/****** Object: StoredProcedure [dbo].[ocms_merge_properties] Script Date: 25.09.2020 12:39:23 ******/
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
/****** Object: StoredProcedure [dbo].[ocms_recoverItem] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ocms_recoverItem]
@ocms_iid bigint
AS
BEGIN
SET NOCOUNT ON;
SET IDENTITY_INSERT [dbo].[ocms_items] ON;
insert into ocms_items
([ocms_iid]
,[parent_iid]
,[ocms_pid]
,[name]
,[view]
,[template_id]
,[hide]
,[order]
,[DateCreated]
,[DateModified])
select [ocms_iid]
,[parent_iid]
,[ocms_pid]
,[name]
,[view]
,[template_id]
,[hide]
,[order]
,[DateCreated]
,[DateModified]
from ocms_archive__items
where ocms_iid = @ocms_iid
SET IDENTITY_INSERT [dbo].[ocms_items] OFF;
SET IDENTITY_INSERT [dbo].[ocms_properties] ON;
insert into ocms_properties
([ocms_pid]
,[ocms_iid]
,[key]
,[value]
,[locale]
,[DateCreated]
,[DateModified])
select
[ocms_pid]
,[ocms_iid]
,[key]
,[value]
,[locale]
,[DateCreated]
,[DateModified]
from ocms_archive__properties where ocms_iid = @ocms_iid
SET IDENTITY_INSERT [dbo].[ocms_properties] OFF;
END
GO
/****** Object: StoredProcedure [dbo].[ocms_setItemName] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ocms_setItemName]
@ocms_iid bigint,
@name nvarchar(255)
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS (SELECT * FROM [dbo].[ocms_items] WHERE [ocms_iid] = @ocms_iid)
THROW 61110, 'item does not exist', 1;
IF EXISTS (SELECT * FROM [dbo].[ocms_items] WHERE [ocms_iid] = @ocms_iid AND [name] = 'index')
THROW 61110, 'index cannot be renamed', 1;
IF ISNULL(@name, '') = ''
THROW 61010, 'name missing', 1;
IF EXISTS (SELECT * FROM [dbo].[ocms_items] WHERE [name] = @name AND [ocms_iid] <> @ocms_iid)
THROW 61110, 'name already exists', 1;
DECLARE @OUTPUT TABLE ([ocms_iid] bigint, [name] nvarchar(255));
UPDATE i
SET [name] = @name
,[DateModified] = GETUTCDATE()
OUTPUT inserted.[ocms_iid], inserted.[name]
INTO @OUTPUT
FROM [dbo].[ocms_items] as i
WHERE i.[ocms_iid] = @ocms_iid;
SELECT TOP(1) * FROM @OUTPUT;
END
GO
/****** Object: StoredProcedure [dbo].[ocms_setItemOrder] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ocms_setItemOrder]
@ocms_iid_csv varchar(2000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @neworder TABLE([ocms_iid] bigint, [order] int);
DECLARE @c char(1) = ','; --delimiter
INSERT INTO @neworder
SELECT value, idx = ROW_NUMBER() OVER (ORDER BY CHARINDEX(@c + value + @c, @c + @ocms_iid_csv + @c))
FROM STRING_SPLIT(@ocms_iid_csv, ',');
DECLARE @parents TABLE([parent_iid] bigint);
INSERT INTO @parents
SELECT DISTINCT [parent_iid]
FROM [dbo].[ocms_items] as i
JOIN @neworder as no ON i.[ocms_iid] = no.[ocms_iid]
WHERE i.[parent_iid] is not null;
select * from @parents
IF (SELECT COUNT(*) FROM @parents) <> 1
THROW 61010, 'order can only be set for exact one parent', 1;
DECLARE @parent_iid bigint = (SELECT TOP(1) [parent_iid] FROM @parents);
UPDATE i
SET [order] = ISNULL(no.[order], 999)
OUTPUT
inserted.[ocms_iid], inserted.[order]
FROM [dbo].[ocms_items] as i
LEFT JOIN @neworder as no ON i.[ocms_iid] = no.[ocms_iid]
WHERE i.[parent_iid] = @parent_iid;
END
GO
/****** Object: StoredProcedure [dbo].[ocms_setViewItem_HiddenStatus] Script Date: 25.09.2020 12:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ocms_setViewItem_HiddenStatus]
@ocms_iid bigint,
@hidden bit
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS (SELECT * FROM [dbo].[ocms_items] WHERE [ocms_iid] = @ocms_iid)
THROW 61110, 'item does not exist', 1;
IF NOT EXISTS (SELECT * FROM [dbo].[ocms_items] WHERE [ocms_iid] = @ocms_iid AND [view] = 1)
THROW 61110, 'item is not a view', 1;
DECLARE @OUTPUT TABLE ([ocms_iid] bigint, [name] nvarchar(255), [hide] bit);
UPDATE i
SET i.[hide] = @hidden
OUTPUT inserted.[ocms_iid], inserted.[name], inserted.[hide]
INTO @OUTPUT
FROM [dbo].[ocms_items] as i
WHERE i.[ocms_iid] = @ocms_iid
AND [view] = 1;
SELECT TOP(1) * FROM @OUTPUT;
END
GO
USE [master]
GO
ALTER DATABASE [site_fuchs_bm] SET READ_WRITE
GO
Reference in New Issue View Git Blame Copy Permalink