1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136 | SET NOCOUNT ON
DECLARE @CommitTransaction INT SET @CommitTransaction = 1 -- AS COMMIT_TRANSACTION
DECLARE @Env VARCHAR(64) SET @Env = CASE dbo.fn_someFunc
WHEN 'staging' THEN 'UAT'
WHEN 'production' THEN 'PROD'
ELSE dbo.fn_someFunc()
END
DECLARE @appname VARCHAR(50) SET @appname = APP_NAME()
DECLARE @time DATETIME SET @time = GETDATE()
DECLARE @user INT SET @user = dbo.fn_someUser()
DECLARE @site VARCHAR(50) SET @site = HOST_NAME()
DECLARE @values TABLE (appname VARCHAR(MAX), section VARCHAR(MAX), name VARCHAR(255), value VARCHAR(MAX))
INSERT INTO @values
SELECT @appname, 'section1', 'setting1', '42'
UNION SELECT @appname, 'section2', 'setting2', 'some_value'
SELECT vs.appname,
'Error: Unknown app' AS Status
FROM @values AS vs
LEFT JOIN dbo.applications AS app ON app.appname = vs.appname
WHERE app.appId IS NULL
SELECT 'Not changed' AS Status,
app.appname,
vs.section + '/' + vs.name AS ConfigName,
ac.configValue AS Value
FROM @values AS vs
INNER JOIN dbo.applications AS app ON app.appname = vs.appname
LEFT JOIN dbo.applicationConfig AS ac ON ac.appId = app.appId
AND vs.section = ac.section
AND vs.name = ac.configName
WHERE ((vs.value IS NOT NULL AND ac.configValue IS NOT NULL AND vs.value = ac.configValue)
OR
(vs.value IS NULL AND ac.configValue IS NULL))
SELECT 'Going to change' AS Status,
app.appname,
vs.section + '/' + vs.name AS ConfigName,
ac.configValue AS OldValue,
vs.value AS NewValue
FROM @values AS vs
INNER JOIN dbo.applications AS app ON app.appname = vs.appname
LEFT JOIN dbo.applicationConfig AS ac ON ac.appId = app.appId
AND vs.section = ac.section
AND vs.name = ac.configName
WHERE NOT ((vs.value IS NOT NULL AND ac.configValue IS NOT NULL AND vs.value = ac.configValue)
OR
(vs.value IS NULL AND ac.configValue IS NULL))
IF EXISTS ( SELECT 1
FROM @values AS vs
INNER JOIN dbo.applications AS app ON app.appname = vs.appname
LEFT JOIN dbo.applicationConfig AS ac ON ac.appId = app.appId
AND vs.section = ac.section
AND vs.name = ac.configName
WHERE NOT ((vs.value IS NOT NULL AND ac.configValue IS NOT NULL AND vs.value = ac.configValue)
OR
(vs.value IS NULL AND ac.configValue IS NULL))
)
BEGIN
BEGIN TRY
BEGIN TRANSACTION
SET NOCOUNT OFF
PRINT 'Deleting config values...'
DELETE FROM dbo.applicationConfig
WHERE configId IN ( SELECT ac.configId
FROM @values AS cv
INNER JOIN dbo.applications AS app ON app.appname = vs.appname
INNER JOIN dbo.applicationConfig AS ac ON ac.appId = app.appId
AND vs.section = ac.section
AND vs.name = ac.configName
WHERE NOT ((vs.value IS NOT NULL AND ac.configValue IS NOT NULL AND vs.value = ac.configValue)
OR
(vs.value IS NULL AND ac.configValue IS NULL))
)
PRINT 'Inserting new config values...';
INSERT INTO dbo.applicationConfig
(
appId,
section,
configName,
configValue,
appname,
time,
user,
site
)
SELECT app.appId,
vs.section,
vs.name,
vs.value,
@appname,
@time,
@user,
@site
FROM @values as vs
INNER JOIN dbo.applications AS app ON app.appname = vs.appname
LEFT JOIN dbo.applicationConfig AS ac ON ac.appId = app.appId
AND vs.section = ac.section
AND vs.name = ac.configName
WHERE vs.value IS NOT NULL AND ac.configValue IS NULL
PRINT 'Checking result:'
SELECT app.appname,
vs.section + '/' + vs.name AS ConfigName,
ac.configValue AS NowValue,
vs.value AS ExpectedValue
FROM @values as vs
INNER JOIN dbo.applications AS app ON app.appname = vs.appname
LEFT JOIN dbo.applicationConfig AS ac ON ac.appId = app.appId
AND vs.section = ac.section
AND vs.name = ac.configName
IF @CommitTransaction = 1
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
SELECT 'Warning: TRANSACTION ROLLED BACK on ERROR' AS Error,
ERROR_MESSAGE() AS ErrorMessage
ROLLBACK TRANSACTION
EXEC dbo.sp_errorHandler;
END CATCH
END
|
Text paste