-
Notifications
You must be signed in to change notification settings - Fork 0
/
DbSchemaSetup.sql
346 lines (280 loc) · 8.29 KB
/
DbSchemaSetup.sql
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
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
use IntranetMacStats;
go
if exists (select * from sys.tables where name = N'SvcAccessCredential')
begin
drop table SvcAccessCredential;
end;
-- This table holds pairs "machine & key", that work as credentials to access the server:
create table SvcAccessCredential (
machine nvarchar(50) not null primary key,
idKey nvarchar(30) not null
);
go
if exists (select * from sys.tables where name = N'StatsValFloat32')
begin
drop table StatsValFloat32;
end;
/* This table holds historical data for statistics whose value has
data type compatible with "floating point 32-bits precision" */
create table StatsValFloat32 (
macId smallint not null,
statId smallint not null,
instant bigint not null, -- time in milliseconds since 1970
statVal float(24) not null,
quality tinyint not null,
primary key (macId, statId, instant)
);
go
if exists (select * from sys.tables where name = N'StagingStatsValFloat32')
begin
drop index IdxStagStatsValFloat32ByBatch on StagingStatsValFloat32;
drop table StagingStatsValFloat32;
end;
-- This table is an staging area for insertion into StatsValFloat32
create table StagingStatsValFloat32 (
batchId smallint not null,
macName nvarchar(50) not null,
statName nvarchar(50) not null,
instant bigint not null, -- time in milliseconds since 1970
statVal float(24) not null,
quality tinyint not null
);
go
create nonclustered index IdxStagStatsValFloat32ByBatch on StagingStatsValFloat32(batchId);
go
if exists (select * from sys.tables where name = N'StatsValInt32')
begin
drop table StatsValInt32;
end;
/* This table holds historical data for statistics whose value
has data type compatible with "signed integer 32-bits long" */
create table StatsValInt32 (
macId smallint not null,
statId smallint not null,
instant bigint not null, -- time in milliseconds since 1970
statVal int not null,
quality tinyint not null,
primary key (macId, statId, instant)
);
go
if exists (select * from sys.tables where name = N'StagingStatsValInt32')
begin
drop index IdxStagStatsValInt32ByBatch on StagingStatsValInt32;
drop table StagingStatsValInt32;
end;
-- This table is an staging area for insertion into StatsValInt32
create table StagingStatsValInt32 (
batchId smallint not null,
macName nvarchar(50) not null,
statName nvarchar(50) not null,
instant bigint not null, -- time in milliseconds since 1970
statVal int not null,
quality tinyint not null
);
go
create nonclustered index IdxStagStatsValInt32ByBatch on StagingStatsValInt32(batchId);
go
-- Normalization for machine ID and statitic ID:
if exists (select * from sys.tables where name = N'Machine')
begin
drop index IdxMachineByName on Machine;
drop table Machine;
end;
create table Machine (
macId smallint identity(1,1) primary key,
macName nvarchar(50) not null
);
go
if exists (select * from sys.tables where name = N'Statistic')
begin
drop index IdxStatisticByName on Statistic;
drop table Statistic;
end;
create table Statistic (
statId smallint identity(1,1) primary key,
statName nvarchar(50) not null
);
go
create nonclustered index IdxMachineByName on Machine(macName);
create nonclustered index IdxStatisticByName on Statistic(statName);
go
alter table StatsValFloat32
add foreign key (macId)
references Machine(macId);
alter table StatsValFloat32
add foreign key (statId)
references Statistic(statId);
alter table StatsValInt32
add foreign key (macId)
references Machine(macId);
alter table StatsValInt32
add foreign key (statId)
references Statistic(statId);
go
/* These stored procedures ensure consistency of inserted data
across all the tables in database, plus adds some level of
independence from the tables design. */
if object_id(N'InsertIntoStatsFloat32Proc', N'P') is not null
begin
drop procedure InsertIntoStatsFloat32Proc;
end;
go
/* Because ODBC implementation on the application side cannot reliably/efficiently
pass parameters to issue massive calls to stored procedures (that would lead to
several isolated calls leading to a growing overhead of data round-trips), the
strategy adopted here is to bulk-insert the parameters for all calls at once into
a "staging table", then calling this procedure that process all those calls. */
create procedure InsertIntoStatsFloat32Proc (@batchId smallint) as
begin
declare @timeSinceEpochInMillisecs bigint;
declare @macName nvarchar(50);
declare @statName nvarchar(50);
declare @statValue float(24);
declare @quality tinyint;
declare stagingCursor cursor for (
select instant
,macName
,statName
,statVal
,quality
from StagingStatsValFloat32
where batchId = @batchId
);
open stagingCursor;
fetch next from stagingCursor
into @timeSinceEpochInMillisecs
,@macName
,@statName
,@statValue
,@quality;
while @@FETCH_STATUS = 0
begin
-- ensure consistency regarding machine:
declare @macId smallint;
set @macId = (select macId from Machine where macName = @macName);
if @macId is null
begin
insert into Machine (macName) values (@macName);
set @macId = (select macId from Machine where macName = @macName);
end;
-- ensure consistency regarding statistic:
declare @statId smallint;
set @statId = (select statId from Statistic where statName = @statName);
if @statId is null
begin
insert into Statistic (statName) values (@statName);
set @statId = (select statId from Statistic where statName = @statName);
end;
-- finally insert data:
insert into StatsValFloat32 (
macId,
statId,
instant,
statVal,
quality
)
values (
@macId,
@statId,
@timeSinceEpochInMillisecs,
@statValue,
@quality
);
fetch next from stagingCursor
into @timeSinceEpochInMillisecs
,@macName
,@statName
,@statValue
,@quality;
end; -- end of loop
close stagingCursor;
deallocate stagingCursor;
delete from StagingStatsValFloat32
where batchId = @batchId;
end; -- end of stored procedure
go
if object_id(N'InsertIntoStatsInt32Proc', N'P') is not null
begin
drop procedure InsertIntoStatsInt32Proc;
end;
go
create procedure InsertIntoStatsInt32Proc (@batchId smallint) as
begin
declare @timeSinceEpochInMillisecs bigint;
declare @macName nvarchar(50);
declare @statName nvarchar(50);
declare @statValue int;
declare @quality tinyint;
declare stagingCursor cursor for (
select instant
,macName
,statName
,statVal
,quality
from StagingStatsValInt32
where batchId = @batchId
);
open stagingCursor;
fetch next from stagingCursor
into @timeSinceEpochInMillisecs
,@macName
,@statName
,@statValue
,@quality;
while @@FETCH_STATUS = 0
begin
-- ensure consistency regarding machine:
declare @macId smallint;
set @macId = (select macId from Machine where macName = @macName);
if @macId is null
begin
insert into Machine (macName) values (@macName);
set @macId = (select macId from Machine where macName = @macName);
end;
-- ensure consistency regarding statistic:
declare @statId smallint;
set @statId = (select statId from Statistic where statName = @statName);
if @statId is null
begin
insert into Statistic (statName) values (@statName);
set @statId = (select statId from Statistic where statName = @statName);
end;
-- finally insert data:
insert into StatsValInt32 (
macId,
statId,
instant,
statVal,
quality
)
values (
@macId,
@statId,
@timeSinceEpochInMillisecs,
@statValue,
@quality
);
fetch next from stagingCursor
into @timeSinceEpochInMillisecs
,@macName
,@statName
,@statValue
,@quality;
end; -- end of loop
close stagingCursor;
deallocate stagingCursor;
delete from StagingStatsValInt32
where batchId = @batchId;
end; -- end of stored procedure
go
begin transaction;
delete from Machine;
insert into Machine (macName) values (N'dummyMachine');
delete from SvcAccessCredential;
-- this is necessary for unit tests:
insert into SvcAccessCredential (machine, idKey) values (N'dummyMachine', N'dummyIdKey');
-- this is necessary for integration tests on my machine:
insert into SvcAccessCredential (machine, idKey) values (N'CASE', N'Entschuldigung');
-- this is necessary for integration tests on YOUR machine:
--insert into SvcAccessCredential (machine, idKey) values (N'YOUR_PC_NAME_RUNNING_UNIT_TESTS', N'Entschuldigung');
commit transaction;