· 5 years ago · Feb 14, 2020, 12:10 PM
1USE [impresario]
2GO
3
4----------------------------------------------
5-- Add MADE_S2R_SERVER_TIMEZONE to T_DEFAULTS
6---------------------------------------------
7
8INSERT INTO [dbo].[T_DEFAULTS]
9 ([parent_table], [field_name], [default_value], [default_value_desc], [id])
10SELECT TOP(1)
11 'Impresario',
12 'MADE_S2R_SERVER_TIMEZONE',
13 name,
14 'The Timezone of the Tessitura DB Server; The Timezone is recorded when the Made S2R install script is ran',
15 (SELECT MAX(id)+1
16FROM [dbo].[T_DEFAULTS]) FROM sys.time_zone_info
17WHERE
18 current_utc_offset = REVERSE(SUBSTRING(REVERSE(CAST(SYSDATETIMEOFFSET() as varchar(250))), 0, (CHARINDEX(' ', REVERSE(SYSDATETIMEOFFSET())))))
19GO
20
21---------------------------------------------------
22-- END - Add MADE_S2R_SERVER_TIMEZONE to T_DEFAULTS
23---------------------------------------------------
24
25------------------------------------------------
26-- S2R Creation and Population of Data Services
27------------------------------------------------
28
29DROP TABLE IF EXISTS [dbo].[LTR_MADE_S2R_LOCAL_DATA]
30GO
31
32SET ANSI_NULLS ON
33GO
34
35SET QUOTED_IDENTIFIER ON
36GO
37
38CREATE TABLE [dbo].[LTR_MADE_S2R_LOCAL_DATA](
39 [id] [int] IDENTITY(1,1) NOT NULL,
40 [local_data_id] [int] NOT NULL,
41 [reference_field] [varchar](max) NOT NULL,
42 [update_field] [varchar](max) NULL
43) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
44GO
45
46INSERT INTO [dbo].[LTR_MADE_S2R_LOCAL_DATA] ([local_data_id], [reference_field], [update_field])
47SELECT [id], [retrieval_column], COALESCE([key_column], IIF([id]>0,'id',NULL), cast((
48 SELECT COLUMN_NAME
49 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
50 WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
51 AND TABLE_NAME = 'TR_LOCAL_DATA' AND TABLE_SCHEMA = 'dbo'
52)as varchar))
53FROM [dbo].[TR_LOCAL_DATA];
54GO
55
56-----------------------------------------------------
57-- END - S2R Creation and Population of Data Services
58-----------------------------------------------------
59
60---------------------------------------------------------------------
61-- S2R Create LTR_MADE_S2R_LOCAL_DATA Record in TR_DATASERVICE_TABLES
62---------------------------------------------------------------------
63
64IF NOT EXISTS(SELECT * FROM [dbo].[TR_DATASERVICE_TABLES] WHERE table_name = 'LTR_MADE_S2R_LOCAL_DATA')
65 INSERT INTO [dbo].[TR_DATASERVICE_TABLES]
66 ([name], [table_name], [schema_name], [singular_name], [next_id_method], [unique_key_column], [control_group])
67 SELECT
68 'MADE_S2R', 'LTR_MADE_S2R_LOCAL_DATA', 'dbo', 'item', 3, 'id', -1
69ELSE
70 UPDATE [dbo].[TR_DATASERVICE_TABLES]
71 SET
72 [name] = 'MADE_S2R_REFERENCE',
73 [table_name] = 'LTR_MADE_S2R_LOCAL_DATA',
74 [schema_name] = 'dbo',
75 [singular_name] = 'item',
76 [next_id_method] = 3,
77 [unique_key_column] = 'id',
78 [control_group] = -1
79 WHERE table_name = 'LTR_MADE_S2R_LOCAL_DATA'
80GO
81
82----------------------------------------------------------------------------
83-- END - S2R Create LTR_MADE_S2R_LOCAL_DATA Record in TR_DATASERVICE_TABLES
84----------------------------------------------------------------------------
85
86-------------------------------------------------------------
87-- S2R Create Data Reference Records in TR_DATASERVICE_TABLES
88-------------------------------------------------------------
89
90INSERT INTO [dbo].[TR_DATASERVICE_TABLES]
91([name], [table_name], [schema_name], [singular_name], [next_id_method], [next_id_type], [unique_key_column], [control_group])
92SELECT
93 CONCAT('MADE_S2R_', [TR_LOCAL_DATA].[id]), [TR_LOCAL_DATA].[table_name], 'dbo', 'item', 3, 'CT', COALESCE([key_column], IIF([TR_LOCAL_DATA].[id]>0,'id',NULL), cast((
94 SELECT COLUMN_NAME
95 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
96 WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
97 AND TABLE_NAME = 'TR_LOCAL_DATA' AND TABLE_SCHEMA = 'dbo'
98)as varchar)), -1
99FROM [dbo].[TR_LOCAL_DATA]
100 LEFT JOIN [dbo].[TR_DATASERVICE_TABLES] on [TR_DATASERVICE_TABLES].[name] = CONCAT('MADE_S2R_', [TR_LOCAL_DATA].[id])
101WHERE [TR_DATASERVICE_TABLES].[id] is null
102GO
103
104---------------------------------------------------------------
105-- END - Create Data Reference Records in TR_DATASERVICE_TABLES
106---------------------------------------------------------------
107
108-----------------------------------------------------------------
109-- S2R Create Data Reference Records in LTR_MADE_TEMPLATE_MAPPING
110-----------------------------------------------------------------
111
112CREATE TABLE [dbo].[LTR_MADE_S2R_TEMPLATE_MAPPING](
113 [id] [int] IDENTITY(1,1) NOT NULL,
114 [soap_template_id] [int] NOT NULL,
115 [rest_template_id] [int] NOT NULL,
116) ON [PRIMARY]
117GO
118
119-------------------------------------------------------------------
120-- END - Create Data Reference Records in LTR_MADE_TEMPLATE_MAPPING
121-------------------------------------------------------------------
122
123-----------------------------------------------------------------------
124-- S2R Create MADE_S2R_TEMPLATE_MAPPING record in TR_DATASERVICE_TABLES
125-----------------------------------------------------------------------
126
127INSERT INTO [dbo].[TR_DATASERVICE_TABLES]
128 ([name], [table_name], [schema_name], [singular_name], [next_id_method], [next_id_type], [unique_key_column], [control_group])
129VALUES
130 ('MADE_S2R_TEMPLATE_MAPPING', 'LTR_MADE_S2R_TEMPLATE_MAPPING', 'dbo', 'item', 3, 'CT', 'id', -1)
131GO
132
133-------------------------------------------------------------------------
134-- END - Create MADE_S2R_TEMPLATE_MAPPING record in TR_DATASERVICE_TABLES
135-------------------------------------------------------------------------