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