· 8 years ago · Apr 11, 2018, 03:18 PM
1/*
2Deployment script for UpstreamAppConfig
3
4This code was generated by a tool.
5Changes to this file may cause incorrect behavior and will be lost if
6the code is regenerated.
7*/
8
9GO
10SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
11
12SET NUMERIC_ROUNDABORT OFF;
13
14
15GO
16
17USE UpstreamAppConfig;
18
19
20GO
21PRINT N'Rename refactoring operation with key 9ffeb4e0-c26e-4a2e-9ed3-e641f4627337 is skipped, element [dbo].[cache_logins].[Tiimestamp] (SqlSimpleColumn) will not be renamed to Timestamp';
22
23
24GO
25PRINT N'Dropping [dbo].[Sources].[NonClusteredIndex-20140711-112730]...';
26
27
28GO
29DROP INDEX [NonClusteredIndex-20140711-112730]
30 ON [dbo].[Sources];
31
32
33GO
34PRINT N'Dropping [dbo].[DF_cfg_emailaccounts_businessInstance]...';
35
36
37GO
38ALTER TABLE [dbo].[cfg_emailaccounts] DROP CONSTRAINT [DF_cfg_emailaccounts_businessInstance];
39
40
41GO
42PRINT N'Dropping [dbo].[DF__cfg_email__accou__239E4DCF]...';
43
44
45GO
46ALTER TABLE [dbo].[cfg_emailaccounts] DROP CONSTRAINT [DF__cfg_email__accou__239E4DCF];
47
48
49GO
50PRINT N'Dropping [dbo].[DF__cfg_email__accou__24927208]...';
51
52
53GO
54ALTER TABLE [dbo].[cfg_emailaccounts] DROP CONSTRAINT [DF__cfg_email__accou__24927208];
55
56
57GO
58PRINT N'Dropping [dbo].[DF__cfg_email__accou__25869641]...';
59
60
61GO
62ALTER TABLE [dbo].[cfg_emailaccounts] DROP CONSTRAINT [DF__cfg_email__accou__25869641];
63
64
65GO
66PRINT N'Dropping [dbo].[DF__cfg_email__alter__267ABA7A]...';
67
68
69GO
70ALTER TABLE [dbo].[cfg_emailaccounts] DROP CONSTRAINT [DF__cfg_email__alter__267ABA7A];
71
72
73GO
74PRINT N'Dropping [dbo].[DF__cfg_email__alter__276EDEB3]...';
75
76
77GO
78ALTER TABLE [dbo].[cfg_emailaccounts] DROP CONSTRAINT [DF__cfg_email__alter__276EDEB3];
79
80
81GO
82PRINT N'Dropping [dbo].[DF__cfg_email__throt__29572725]...';
83
84
85GO
86ALTER TABLE [dbo].[cfg_emailaccounts] DROP CONSTRAINT [DF__cfg_email__throt__29572725];
87
88
89GO
90PRINT N'Dropping [dbo].[DF__cfg_email__pollI__2A4B4B5E]...';
91
92
93GO
94ALTER TABLE [dbo].[cfg_emailaccounts] DROP CONSTRAINT [DF__cfg_email__pollI__2A4B4B5E];
95
96
97GO
98PRINT N'Dropping [dbo].[DF_cfg_emailaccounts_mailDownloadFrequency]...';
99
100
101GO
102ALTER TABLE [dbo].[cfg_emailaccounts] DROP CONSTRAINT [DF_cfg_emailaccounts_mailDownloadFrequency];
103
104
105GO
106PRINT N'Dropping [dbo].[DF_cfg_emailaccounts_maxFileSize]...';
107
108
109GO
110ALTER TABLE [dbo].[cfg_emailaccounts] DROP CONSTRAINT [DF_cfg_emailaccounts_maxFileSize];
111
112
113GO
114PRINT N'Dropping [dbo].[DF_cfg_emailaccounts_maxTotalSize]...';
115
116
117GO
118ALTER TABLE [dbo].[cfg_emailaccounts] DROP CONSTRAINT [DF_cfg_emailaccounts_maxTotalSize];
119
120
121GO
122PRINT N'Dropping [dbo].[DF_cfg_emailaccounts_maxAttachmentCount]...';
123
124
125GO
126ALTER TABLE [dbo].[cfg_emailaccounts] DROP CONSTRAINT [DF_cfg_emailaccounts_maxAttachmentCount];
127
128
129GO
130PRINT N'Dropping [dbo].[DF_cfg_emailaccounts_autosaveInterval]...';
131
132
133GO
134ALTER TABLE [dbo].[cfg_emailaccounts] DROP CONSTRAINT [DF_cfg_emailaccounts_autosaveInterval];
135
136
137GO
138PRINT N'Dropping [dbo].[DF_cfg_emailaccounts_blockedFileTypes]...';
139
140
141GO
142ALTER TABLE [dbo].[cfg_emailaccounts] DROP CONSTRAINT [DF_cfg_emailaccounts_blockedFileTypes];
143
144
145GO
146PRINT N'Dropping [dbo].[DF_cfg_emailaccounts_smtpPort]...';
147
148
149GO
150ALTER TABLE [dbo].[cfg_emailaccounts] DROP CONSTRAINT [DF_cfg_emailaccounts_smtpPort];
151
152
153GO
154PRINT N'Dropping [dbo].[DF_cfg_emailaccounts_smtpTimeout]...';
155
156
157GO
158ALTER TABLE [dbo].[cfg_emailaccounts] DROP CONSTRAINT [DF_cfg_emailaccounts_smtpTimeout];
159
160
161GO
162PRINT N'Dropping [dbo].[DF_cfg_emailaccounts_sendNumRetries]...';
163
164
165GO
166ALTER TABLE [dbo].[cfg_emailaccounts] DROP CONSTRAINT [DF_cfg_emailaccounts_sendNumRetries];
167
168
169GO
170PRINT N'Dropping [dbo].[DF_cfg_emailaccounts_sendRetryDelay]...';
171
172
173GO
174ALTER TABLE [dbo].[cfg_emailaccounts] DROP CONSTRAINT [DF_cfg_emailaccounts_sendRetryDelay];
175
176
177GO
178PRINT N'Dropping [dbo].[DF_cfg_emailaccounts_forcedBcc]...';
179
180
181GO
182ALTER TABLE [dbo].[cfg_emailaccounts] DROP CONSTRAINT [DF_cfg_emailaccounts_forcedBcc];
183
184
185GO
186PRINT N'Dropping [dbo].[DF_cfg_emailaccounts_collectorMaxLockMinutes]...';
187
188
189GO
190ALTER TABLE [dbo].[cfg_emailaccounts] DROP CONSTRAINT [DF_cfg_emailaccounts_collectorMaxLockMinutes];
191
192
193GO
194PRINT N'Dropping [dbo].[DF_cfg_emailaccounts_STARTTLS]...';
195
196
197GO
198ALTER TABLE [dbo].[cfg_emailaccounts] DROP CONSTRAINT [DF_cfg_emailaccounts_STARTTLS];
199
200
201GO
202PRINT N'Dropping [dbo].[DF_cfg_emailaccounts_apiBaseUri]...';
203
204
205GO
206ALTER TABLE [dbo].[cfg_emailaccounts] DROP CONSTRAINT [DF_cfg_emailaccounts_apiBaseUri];
207
208
209GO
210PRINT N'Dropping [dbo].[DF__cfg_ctios__apiBa__1DE57479]...';
211
212
213GO
214ALTER TABLE [dbo].[cfg_ctiossettings] DROP CONSTRAINT [DF__cfg_ctios__apiBa__1DE57479];
215
216
217GO
218PRINT N'Dropping [dbo].[DF__cfg_ctios__heart__22AA2996]...';
219
220
221GO
222ALTER TABLE [dbo].[cfg_ctiossettings] DROP CONSTRAINT [DF__cfg_ctios__heart__22AA2996];
223
224
225GO
226PRINT N'Dropping [dbo].[DF__cfg_ctios__hostA__1ED998B2]...';
227
228
229GO
230ALTER TABLE [dbo].[cfg_ctiossettings] DROP CONSTRAINT [DF__cfg_ctios__hostA__1ED998B2];
231
232
233GO
234PRINT N'Dropping [dbo].[DF__cfg_ctios__hostB__20C1E124]...';
235
236
237GO
238ALTER TABLE [dbo].[cfg_ctiossettings] DROP CONSTRAINT [DF__cfg_ctios__hostB__20C1E124];
239
240
241GO
242PRINT N'Dropping [dbo].[DF__cfg_ctios__portA__1FCDBCEB]...';
243
244
245GO
246ALTER TABLE [dbo].[cfg_ctiossettings] DROP CONSTRAINT [DF__cfg_ctios__portA__1FCDBCEB];
247
248
249GO
250PRINT N'Dropping [dbo].[DF__cfg_ctios__portB__21B6055D]...';
251
252
253GO
254ALTER TABLE [dbo].[cfg_ctiossettings] DROP CONSTRAINT [DF__cfg_ctios__portB__21B6055D];
255
256
257GO
258PRINT N'Dropping [dbo].[DF__cfg_healt__enabl__3D5E1FD2]...';
259
260
261GO
262ALTER TABLE [dbo].[cfg_health_uris] DROP CONSTRAINT [DF__cfg_healt__enabl__3D5E1FD2];
263
264
265GO
266PRINT N'Dropping [dbo].[DF__cfg_healt__polle__3B75D760]...';
267
268
269GO
270ALTER TABLE [dbo].[cfg_health_uris] DROP CONSTRAINT [DF__cfg_healt__polle__3B75D760];
271
272
273GO
274PRINT N'Dropping [dbo].[DF__cfg_healt__polli__3C69FB99]...';
275
276
277GO
278ALTER TABLE [dbo].[cfg_health_uris] DROP CONSTRAINT [DF__cfg_healt__polli__3C69FB99];
279
280
281GO
282PRINT N'Dropping [dbo].[DF__cfg_healt__apiBa__3E52440B]...';
283
284
285GO
286ALTER TABLE [dbo].[cfg_healthsettings] DROP CONSTRAINT [DF__cfg_healt__apiBa__3E52440B];
287
288
289GO
290PRINT N'Dropping [dbo].[DF__cfg_tabbe__uriLe__3F466844]...';
291
292
293GO
294ALTER TABLE [dbo].[cfg_tabbersettings] DROP CONSTRAINT [DF__cfg_tabbe__uriLe__3F466844];
295
296
297GO
298PRINT N'Dropping [dbo].[DF__cfg_tabbe__uriTr__403A8C7D]...';
299
300
301GO
302ALTER TABLE [dbo].[cfg_tabbersettings] DROP CONSTRAINT [DF__cfg_tabbe__uriTr__403A8C7D];
303
304
305GO
306PRINT N'Dropping [dbo].[DF__cfg_table__Colum__412EB0B6]...';
307
308
309GO
310ALTER TABLE [dbo].[cfg_tablefields] DROP CONSTRAINT [DF__cfg_table__Colum__412EB0B6];
311
312
313GO
314PRINT N'Dropping [dbo].[DF__cfg_table__Colum__4222D4EF]...';
315
316
317GO
318ALTER TABLE [dbo].[cfg_tablefields] DROP CONSTRAINT [DF__cfg_table__Colum__4222D4EF];
319
320
321GO
322PRINT N'Dropping [dbo].[DF__tmp_ms_xx__allow__66EA454A]...';
323
324
325GO
326ALTER TABLE [dbo].[cfg_taskchannelsettings] DROP CONSTRAINT [DF__tmp_ms_xx__allow__66EA454A];
327
328
329GO
330PRINT N'Dropping [dbo].[DF__tmp_ms_xx__enabl__68D28DBC]...';
331
332
333GO
334ALTER TABLE [dbo].[cfg_taskchannelsettings] DROP CONSTRAINT [DF__tmp_ms_xx__enabl__68D28DBC];
335
336
337GO
338PRINT N'Dropping [dbo].[DF__tmp_ms_xx__taskQ__67DE6983]...';
339
340
341GO
342ALTER TABLE [dbo].[cfg_taskchannelsettings] DROP CONSTRAINT [DF__tmp_ms_xx__taskQ__67DE6983];
343
344
345GO
346PRINT N'Dropping [dbo].[DF__cfg_uptak__apiBa__44FF419A]...';
347
348
349GO
350ALTER TABLE [dbo].[cfg_uptakesettings] DROP CONSTRAINT [DF__cfg_uptak__apiBa__44FF419A];
351
352
353GO
354PRINT N'Dropping [dbo].[DF__cfg_uptak__imasN__46E78A0C]...';
355
356
357GO
358ALTER TABLE [dbo].[cfg_uptakesettings] DROP CONSTRAINT [DF__cfg_uptak__imasN__46E78A0C];
359
360
361GO
362PRINT N'Dropping [dbo].[DF__cfg_uptak__imasP__47DBAE45]...';
363
364
365GO
366ALTER TABLE [dbo].[cfg_uptakesettings] DROP CONSTRAINT [DF__cfg_uptak__imasP__47DBAE45];
367
368
369GO
370PRINT N'Dropping [dbo].[DF__cfg_uptak__imasS__45F365D3]...';
371
372
373GO
374ALTER TABLE [dbo].[cfg_uptakesettings] DROP CONSTRAINT [DF__cfg_uptak__imasS__45F365D3];
375
376
377GO
378PRINT N'Dropping [dbo].[FK_Skills_EmailAccounts]...';
379
380
381GO
382ALTER TABLE [dbo].[Skills] DROP CONSTRAINT [FK_Skills_EmailAccounts];
383
384
385GO
386PRINT N'Creating [dbo].[SkillIds]...';
387
388
389GO
390CREATE TYPE [dbo].[SkillIds] AS TABLE (
391 [Id] UNIQUEIDENTIFIER NOT NULL,
392 PRIMARY KEY CLUSTERED ([Id] ASC));
393
394
395GO
396/*
397The column [dbo].[cfg_emailaccounts].[mailDownloadFrequency] is being dropped, data loss could occur.
398*/
399GO
400PRINT N'Starting rebuilding table [dbo].[cfg_emailaccounts]...';
401
402
403GO
404BEGIN TRANSACTION;
405
406SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
407
408SET XACT_ABORT ON;
409
410CREATE TABLE [dbo].[tmp_ms_xx_cfg_emailaccounts] (
411 [accountId] INT IDENTITY (1, 1) NOT NULL,
412 [businessInstance] VARCHAR (64) CONSTRAINT [DF_cfg_emailaccounts_businessInstance] DEFAULT ('DEFAULT') NOT NULL,
413 [accountAddress] VARCHAR (256) NULL,
414 [accountName] VARCHAR (32) NULL,
415 [accountDescription] VARCHAR (128) CONSTRAINT [DF__cfg_email__accou__239E4DCF] DEFAULT (NULL) NULL,
416 [accountUserName] VARCHAR (256) CONSTRAINT [DF__cfg_email__accou__24927208] DEFAULT (NULL) NULL,
417 [accountPassword] VARCHAR (MAX) CONSTRAINT [DF__cfg_email__accou__25869641] DEFAULT (NULL) NULL,
418 [addressOrHostName] VARCHAR (256) NULL,
419 [port] INT NULL,
420 [alternateHost] VARCHAR (256) CONSTRAINT [DF__cfg_email__alter__267ABA7A] DEFAULT (NULL) NULL,
421 [alternatePort] VARCHAR (256) CONSTRAINT [DF__cfg_email__alter__276EDEB3] DEFAULT (NULL) NULL,
422 [throttle] VARCHAR (256) CONSTRAINT [DF__cfg_email__throt__29572725] DEFAULT (NULL) NULL,
423 [pollItemLimit] INT CONSTRAINT [DF__cfg_email__pollI__2A4B4B5E] DEFAULT ((10)) NULL,
424 [downloadFrequencySecs] INT CONSTRAINT [DF_cfg_emailaccounts_mailDownloadFrequency] DEFAULT ((20)) NOT NULL,
425 [maxPollingFallbacks] INT CONSTRAINT [DF_cfg_emailaccounts_maxPollingFallbacks] DEFAULT ((5)) NOT NULL,
426 [maxFileSize] INT CONSTRAINT [DF_cfg_emailaccounts_maxFileSize] DEFAULT ((5000000)) NOT NULL,
427 [maxTotalSize] INT CONSTRAINT [DF_cfg_emailaccounts_maxTotalSize] DEFAULT ((10000000)) NOT NULL,
428 [maxAttachmentCount] INT CONSTRAINT [DF_cfg_emailaccounts_maxAttachmentCount] DEFAULT ((3)) NOT NULL,
429 [autosaveInterval] INT CONSTRAINT [DF_cfg_emailaccounts_autosaveInterval] DEFAULT ((60000)) NOT NULL,
430 [blockedFileTypes] VARCHAR (64) CONSTRAINT [DF_cfg_emailaccounts_blockedFileTypes] DEFAULT ('exe,cmd,bat') NOT NULL,
431 [smtpPort] INT CONSTRAINT [DF_cfg_emailaccounts_smtpPort] DEFAULT ((25)) NULL,
432 [smtpTimeout] INT CONSTRAINT [DF_cfg_emailaccounts_smtpTimeout] DEFAULT ((-1)) NULL,
433 [sendNumRetries] INT CONSTRAINT [DF_cfg_emailaccounts_sendNumRetries] DEFAULT ((5)) NULL,
434 [sendRetryDelay] INT CONSTRAINT [DF_cfg_emailaccounts_sendRetryDelay] DEFAULT ((30)) NULL,
435 [forcedBcc] VARCHAR (64) CONSTRAINT [DF_cfg_emailaccounts_forcedBcc] DEFAULT ('') NULL,
436 [smtpHost] VARCHAR (64) NULL,
437 [smtpUserName] VARCHAR (64) NULL,
438 [smtpPassword] VARCHAR (MAX) NULL,
439 [collectorMaxLockMinutes] INT CONSTRAINT [DF_cfg_emailaccounts_collectorMaxLockMinutes] DEFAULT ((5)) NOT NULL,
440 [smtpAccount] VARCHAR (256) NULL,
441 [STARTTLS] BIT CONSTRAINT [DF_cfg_emailaccounts_STARTTLS] DEFAULT ((0)) NULL,
442 [apiBaseUri] VARCHAR (64) CONSTRAINT [DF_cfg_emailaccounts_apiBaseUri] DEFAULT ('http://localhost:8086') NOT NULL,
443 CONSTRAINT [tmp_ms_xx_constraint_PK_cfg_emailaccounts1] PRIMARY KEY CLUSTERED ([accountId] ASC)
444);
445
446IF EXISTS (SELECT TOP 1 1
447 FROM [dbo].[cfg_emailaccounts])
448 BEGIN
449 SET IDENTITY_INSERT [dbo].[tmp_ms_xx_cfg_emailaccounts] ON;
450 INSERT INTO [dbo].[tmp_ms_xx_cfg_emailaccounts] ([accountId], [businessInstance], [accountAddress], [accountName], [accountDescription], [accountUserName], [accountPassword], [addressOrHostName], [port], [alternateHost], [alternatePort], [throttle], [pollItemLimit], [maxFileSize], [maxTotalSize], [maxAttachmentCount], [autosaveInterval], [blockedFileTypes], [smtpPort], [smtpTimeout], [sendNumRetries], [sendRetryDelay], [forcedBcc], [smtpHost], [smtpUserName], [smtpPassword], [collectorMaxLockMinutes], [smtpAccount], [STARTTLS], [apiBaseUri])
451 SELECT [accountId],
452 [businessInstance],
453 [accountAddress],
454 [accountName],
455 [accountDescription],
456 [accountUserName],
457 [accountPassword],
458 [addressOrHostName],
459 [port],
460 [alternateHost],
461 [alternatePort],
462 [throttle],
463 [pollItemLimit],
464 [maxFileSize],
465 [maxTotalSize],
466 [maxAttachmentCount],
467 [autosaveInterval],
468 [blockedFileTypes],
469 [smtpPort],
470 [smtpTimeout],
471 [sendNumRetries],
472 [sendRetryDelay],
473 [forcedBcc],
474 [smtpHost],
475 [smtpUserName],
476 [smtpPassword],
477 [collectorMaxLockMinutes],
478 [smtpAccount],
479 [STARTTLS],
480 [apiBaseUri]
481 FROM [dbo].[cfg_emailaccounts]
482 ORDER BY [accountId] ASC;
483 SET IDENTITY_INSERT [dbo].[tmp_ms_xx_cfg_emailaccounts] OFF;
484 END
485
486DROP TABLE [dbo].[cfg_emailaccounts];
487
488EXECUTE sp_rename N'[dbo].[tmp_ms_xx_cfg_emailaccounts]', N'cfg_emailaccounts';
489
490EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_cfg_emailaccounts1]', N'PK_cfg_emailaccounts', N'OBJECT';
491
492COMMIT TRANSACTION;
493
494SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
495
496
497GO
498PRINT N'Creating [dbo].[cfg_emailaccounts].[IX_cfg_accountName]...';
499
500
501GO
502CREATE UNIQUE NONCLUSTERED INDEX [IX_cfg_accountName]
503 ON [dbo].[cfg_emailaccounts]([accountName] ASC);
504
505
506GO
507PRINT N'Creating [dbo].[cfg_emailaccounts].[IX_cfg_accountAddress]...';
508
509
510GO
511CREATE UNIQUE NONCLUSTERED INDEX [IX_cfg_accountAddress]
512 ON [dbo].[cfg_emailaccounts]([accountAddress] ASC);
513
514
515GO
516PRINT N'Altering [dbo].[Skills]...';
517
518
519GO
520ALTER TABLE [dbo].[Skills]
521 ADD [CriticalSLATimeSec] INT NULL,
522 [CriticalSLAUnit] NVARCHAR (64) NULL,
523 [LastAgentDegradeTimeSecUnit] NVARCHAR (64) NULL;
524
525
526GO
527PRINT N'Creating [dbo].[Sources].[IX_Sources_Name]...';
528
529
530GO
531CREATE UNIQUE NONCLUSTERED INDEX [IX_Sources_Name]
532 ON [dbo].[Sources]([Name] ASC);
533
534
535GO
536PRINT N'Creating [dbo].[Sources].[IX_Sources_SkillAddress]...';
537
538
539GO
540CREATE UNIQUE NONCLUSTERED INDEX [IX_Sources_SkillAddress]
541 ON [dbo].[Sources]([SkillAddress] ASC)
542 INCLUDE([ChannelType], [Id], [SkillId], [Name], [Description]);
543
544
545GO
546PRINT N'Creating [dbo].[UserSkills].[UserSkills_SkillId_Filtered]...';
547
548
549GO
550CREATE NONCLUSTERED INDEX [UserSkills_SkillId_Filtered]
551 ON [dbo].[UserSkills]([SkillId] ASC) WHERE ([Status]=(1));
552
553
554GO
555PRINT N'Creating unnamed constraint on [dbo].[cfg_ctiossettings]...';
556
557
558GO
559ALTER TABLE [dbo].[cfg_ctiossettings]
560 ADD DEFAULT ('http://localhost:8088') FOR [apiBaseUri];
561
562
563GO
564PRINT N'Creating unnamed constraint on [dbo].[cfg_ctiossettings]...';
565
566
567GO
568ALTER TABLE [dbo].[cfg_ctiossettings]
569 ADD DEFAULT ('localhost') FOR [hostA];
570
571
572GO
573PRINT N'Creating unnamed constraint on [dbo].[cfg_ctiossettings]...';
574
575
576GO
577ALTER TABLE [dbo].[cfg_ctiossettings]
578 ADD DEFAULT ((42028)) FOR [portA];
579
580
581GO
582PRINT N'Creating unnamed constraint on [dbo].[cfg_ctiossettings]...';
583
584
585GO
586ALTER TABLE [dbo].[cfg_ctiossettings]
587 ADD DEFAULT ('localhost') FOR [hostB];
588
589
590GO
591PRINT N'Creating unnamed constraint on [dbo].[cfg_ctiossettings]...';
592
593
594GO
595ALTER TABLE [dbo].[cfg_ctiossettings]
596 ADD DEFAULT ((42028)) FOR [portB];
597
598
599GO
600PRINT N'Creating unnamed constraint on [dbo].[cfg_ctiossettings]...';
601
602
603GO
604ALTER TABLE [dbo].[cfg_ctiossettings]
605 ADD DEFAULT ((3)) FOR [heartbeatSecs];
606
607
608GO
609PRINT N'Creating unnamed constraint on [dbo].[cfg_health_uris]...';
610
611
612GO
613ALTER TABLE [dbo].[cfg_health_uris]
614 ADD DEFAULT ('') FOR [polledUri];
615
616
617GO
618PRINT N'Creating unnamed constraint on [dbo].[cfg_health_uris]...';
619
620
621GO
622ALTER TABLE [dbo].[cfg_health_uris]
623 ADD DEFAULT ('30') FOR [pollingIntervalSeconds];
624
625
626GO
627PRINT N'Creating unnamed constraint on [dbo].[cfg_health_uris]...';
628
629
630GO
631ALTER TABLE [dbo].[cfg_health_uris]
632 ADD DEFAULT ((1)) FOR [enabled];
633
634
635GO
636PRINT N'Creating unnamed constraint on [dbo].[cfg_healthsettings]...';
637
638
639GO
640ALTER TABLE [dbo].[cfg_healthsettings]
641 ADD DEFAULT ('http://localhost:8088') FOR [apiBaseUri];
642
643
644GO
645PRINT N'Creating unnamed constraint on [dbo].[cfg_tabbersettings]...';
646
647
648GO
649ALTER TABLE [dbo].[cfg_tabbersettings]
650 ADD DEFAULT ('') FOR [uriLeader];
651
652
653GO
654PRINT N'Creating unnamed constraint on [dbo].[cfg_tabbersettings]...';
655
656
657GO
658ALTER TABLE [dbo].[cfg_tabbersettings]
659 ADD DEFAULT ('') FOR [uriTrailer];
660
661
662GO
663PRINT N'Creating unnamed constraint on [dbo].[cfg_tablefields]...';
664
665
666GO
667ALTER TABLE [dbo].[cfg_tablefields]
668 ADD DEFAULT ('VARCHAR') FOR [ColumnValueType];
669
670
671GO
672PRINT N'Creating unnamed constraint on [dbo].[cfg_tablefields]...';
673
674
675GO
676ALTER TABLE [dbo].[cfg_tablefields]
677 ADD DEFAULT ((0)) FOR [ColumnNullable];
678
679
680GO
681PRINT N'Creating unnamed constraint on [dbo].[cfg_taskchannelsettings]...';
682
683
684GO
685ALTER TABLE [dbo].[cfg_taskchannelsettings]
686 ADD DEFAULT ((0)) FOR [allowPhoneInterrupt];
687
688
689GO
690PRINT N'Creating unnamed constraint on [dbo].[cfg_taskchannelsettings]...';
691
692
693GO
694ALTER TABLE [dbo].[cfg_taskchannelsettings]
695 ADD DEFAULT ((0)) FOR [taskQueueChannel];
696
697
698GO
699PRINT N'Creating unnamed constraint on [dbo].[cfg_taskchannelsettings]...';
700
701
702GO
703ALTER TABLE [dbo].[cfg_taskchannelsettings]
704 ADD DEFAULT ((1)) FOR [enabled];
705
706
707GO
708PRINT N'Creating unnamed constraint on [dbo].[cfg_uptakesettings]...';
709
710
711GO
712ALTER TABLE [dbo].[cfg_uptakesettings]
713 ADD DEFAULT ('http://localhost:8087') FOR [apiBaseUri];
714
715
716GO
717PRINT N'Creating unnamed constraint on [dbo].[cfg_uptakesettings]...';
718
719
720GO
721ALTER TABLE [dbo].[cfg_uptakesettings]
722 ADD DEFAULT ('') FOR [imasServerID];
723
724
725GO
726PRINT N'Creating unnamed constraint on [dbo].[cfg_uptakesettings]...';
727
728
729GO
730ALTER TABLE [dbo].[cfg_uptakesettings]
731 ADD DEFAULT ('localhost') FOR [imasName];
732
733
734GO
735PRINT N'Creating unnamed constraint on [dbo].[cfg_uptakesettings]...';
736
737
738GO
739ALTER TABLE [dbo].[cfg_uptakesettings]
740 ADD DEFAULT ('8089') FOR [imasPort];
741
742
743GO
744PRINT N'Creating [dbo].[FK_Skills_EmailAccounts]...';
745
746
747GO
748ALTER TABLE [dbo].[Skills] WITH NOCHECK
749 ADD CONSTRAINT [FK_Skills_EmailAccounts] FOREIGN KEY ([ReplyEmailAccountId]) REFERENCES [dbo].[cfg_emailaccounts] ([accountId]) ON DELETE SET NULL ON UPDATE CASCADE;
750
751
752GO
753PRINT N'Refreshing [dbo].[ServiceLevels]...';
754
755
756GO
757EXECUTE sp_refreshsqlmodule N'[dbo].[ServiceLevels]';
758
759
760GO
761PRINT N'Creating [dbo].[GetSkillAddressesWithDetail]...';
762
763
764GO
765-- =============================================
766-- Author: selam siyoum
767-- Create date: 2/18/2015
768-- Description:
769-- =============================================
770CREATE PROCEDURE [dbo].[GetSkillAddressesWithDetail]
771 @keyword varchar(256)=NULL,
772 @locale char(2)=null
773AS
774BEGIN
775 -- SET NOCOUNT ON added to prevent extra result sets from
776 SET NOCOUNT ON;
777 IF(@keyword IS NOT NULL)
778 BEGIN
779 SET @keyword='%' + @keyword + '%';
780
781 SELECT a.Id as SourceId,a.Name as SourceName,a.ChannelType as SourceChannelType,a.Description as SourceDescription,a.SkillAddress,b.Id as SkillId, b.Name as SkillName,
782 (CASE @locale
783 WHEN 'fr' THEN d.[fr]
784 WHEN 'es' THEN d.[es]
785 WHEN 'da' THEN d.[da]
786 WHEN 'de' THEN d.[de] ELSE d.[wca_Base] END) as SourceChannelDisplay
787 FROM Sources a
788 INNER JOIN Skills b on a.SkillId=b.Id
789 INNER JOIN UWF_IRDB_Config.dbo.cfg_Channel c ON a.ChannelType = c.ChannelType
790 INNER JOIN UWF_IRDB_Config.dbo.RES_Resource_Strings d ON c.DisplayResId = d.wca_ResourceID
791 WHERE a.Name like @keyword OR a.Description like @keyword OR a.SkillAddress like @keyword
792 END
793 ELSE
794 BEGIN
795 SELECT a.Id as SourceId,a.Name as SourceName,a.ChannelType as SourceChannelType,a.Description as SourceDescription,a.SkillAddress,b.Id as SkillId, b.Name as SkillName,
796 (CASE @locale
797 WHEN 'fr' THEN ISNULL(d.[fr],d.[wca_Base])
798 WHEN 'es' THEN ISNULL(d.[es],d.[wca_Base])
799 WHEN 'da' THEN ISNULL(d.[da],d.[wca_Base])
800 WHEN 'de' THEN ISNULL(d.[de],d.[wca_Base])
801 ELSE ISNULL(d.[wca_Base],'unknown') END) as ChannelDisplay
802 FROM Sources a
803 INNER JOIN Skills b on a.SkillId=b.Id
804 INNER JOIN UWF_IRDB_Config.dbo.cfg_Channel c ON a.ChannelType = c.ChannelType
805 INNER JOIN UWF_IRDB_Config.dbo.RES_Resource_Strings d ON c.DisplayResId = d.wca_ResourceID
806 END
807END
808GO
809PRINT N'Creating [dbo].[GetSkillsWithDetail]...';
810
811
812GO
813-- =============================================
814-- Author: selam siyoum
815-- Create date: 2/18/2015
816-- Description:
817-- =============================================
818CREATE PROCEDURE [dbo].[GetSkillsWithDetail]
819 @keyword varchar(256)=NULL,
820 @locale char(2)=null
821AS
822BEGIN
823 -- SET NOCOUNT ON added to prevent extra result sets from
824 SET NOCOUNT ON;
825
826CREATE TABLE #TempSkills
827(
828 Id uniqueidentifier,
829 Name nvarchar (64),
830 ChannelType int,
831 ChannelDisplay nvarchar (64),
832 SkillDescription nvarchar (max),
833 SkillDegradeTimeSec int,
834 SkillDegradeToId uniqueidentifier,
835 SLATimeSec int,
836 CriticalSLATimeSec int,
837 SLAUnit nvarchar(64),
838 CriticalSLAUnit nvarchar(64),
839 BusinessHoursScheduleId uniqueidentifier,
840 ScheduleName nvarchar (64),
841 LastAgentDegradeTimeSec int,
842 LastAgentDegradeTimeSecUnit nvarchar (64),
843 LastAgentWindowDays int,
844 ReplyEmailAccountId int,
845 UsersCount int
846)
847
848
849 INSERT INTO #TempSkills(Id, Name, ChannelType,SkillDescription,SkillDegradeTimeSec,SkillDegradeToId,SLATimeSec,CriticalSLATimeSec,SLAUnit,CriticalSLAUnit,
850 BusinessHoursScheduleId,LastAgentDegradeTimeSec,LastAgentDegradeTimeSecUnit,LastAgentWindowDays,ReplyEmailAccountId,ScheduleName,ChannelDisplay,UsersCount)
851 SELECT a.Id,a.Name,a.ChannelType,a.Description,a.SkillDegradeTimeSec,a.SkillDegradeToId,a.SLATimeSec,a.CriticalSLATimeSec,a.SLAUnit,a.CriticalSLAUnit,
852 a.BusinessHoursScheduleId,a.LastAgentDegradeTimeSec,a.LastAgentDegradeTimeSecUnit,a.LastAgentWindowDays,a.ReplyEmailAccountId,s.Name as ScheduleName,
853 (CASE @locale
854 WHEN 'fr' THEN ISNULL(c.[fr],c.[wca_Base])
855 WHEN 'es' THEN ISNULL(c.[es],c.[wca_Base])
856 WHEN 'da' THEN ISNULL(c.[da],c.[wca_Base])
857 WHEN 'de' THEN ISNULL(c.[de],c.[wca_Base])
858 ELSE ISNULL(c.[wca_Base],'unknown') END) as ChannelDisplay,0
859 FROM Skills a
860 LEFT OUTER JOIN bh_Schedules s on a.BusinessHoursScheduleId = s.ScheduleId
861 INNER JOIN UWF_IRDB_Config.dbo.cfg_Channel b ON a.ChannelType = b.ChannelType
862 INNER JOIN UWF_IRDB_Config.dbo.RES_Resource_Strings c ON b.DisplayResId = c.wca_ResourceID
863
864
865 update s
866 set s.UsersCount=ISNULL(AgentCount,0)
867 from #TempSkills s
868 LEFT OUTER JOIN(select SkillId,count(DISTINCT Login) as AgentCount from UserSkills Group By SkillId) u on s.Id=u.SkillId
869
870 IF(@keyword IS NOT NULL)
871 BEGIN
872 SELECT * FROM #TempSkills WHERE LOWER(Name) LIKE LOWER('%' + @keyword + '%') OR LOWER(SkillDescription) LIKE LOWER('%' + @keyword + '%')
873 END
874 ELSE
875 BEGIN
876 SELECT * FROM #TempSkills
877 END
878
879 DROP TABLE #TempSkills
880
881END
882GO
883PRINT N'Altering [dbo].[ScheduleHolidays].[MS_DiagramPane1]...';
884
885
886GO
887EXECUTE sp_updateextendedproperty @name = N'MS_DiagramPane1', @value = N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
888Begin DesignProperties =
889 Begin PaneConfigurations =
890 Begin PaneConfiguration = 0
891 NumPanes = 4
892 Configuration = "(H (1[48] 4[14] 2[20] 3) )"
893 End
894 Begin PaneConfiguration = 1
895 NumPanes = 3
896 Configuration = "(H (1 [50] 4 [25] 3))"
897 End
898 Begin PaneConfiguration = 2
899 NumPanes = 3
900 Configuration = "(H (1 [50] 2 [25] 3))"
901 End
902 Begin PaneConfiguration = 3
903 NumPanes = 3
904 Configuration = "(H (4 [30] 2 [40] 3))"
905 End
906 Begin PaneConfiguration = 4
907 NumPanes = 2
908 Configuration = "(H (1 [56] 3))"
909 End
910 Begin PaneConfiguration = 5
911 NumPanes = 2
912 Configuration = "(H (2 [66] 3))"
913 End
914 Begin PaneConfiguration = 6
915 NumPanes = 2
916 Configuration = "(H (4 [50] 3))"
917 End
918 Begin PaneConfiguration = 7
919 NumPanes = 1
920 Configuration = "(V (3))"
921 End
922 Begin PaneConfiguration = 8
923 NumPanes = 3
924 Configuration = "(H (1[56] 4[18] 2) )"
925 End
926 Begin PaneConfiguration = 9
927 NumPanes = 2
928 Configuration = "(H (1 [75] 4))"
929 End
930 Begin PaneConfiguration = 10
931 NumPanes = 2
932 Configuration = "(H (1[66] 2) )"
933 End
934 Begin PaneConfiguration = 11
935 NumPanes = 2
936 Configuration = "(H (4 [60] 2))"
937 End
938 Begin PaneConfiguration = 12
939 NumPanes = 1
940 Configuration = "(H (1) )"
941 End
942 Begin PaneConfiguration = 13
943 NumPanes = 1
944 Configuration = "(V (4))"
945 End
946 Begin PaneConfiguration = 14
947 NumPanes = 1
948 Configuration = "(V (2))"
949 End
950 ActivePaneConfig = 0
951 End
952 Begin DiagramPane =
953 Begin Origin =
954 Top = 0
955 Left = 0
956 End
957 Begin Tables =
958 Begin Table = "bh_HolidayGroupDetails"
959 Begin Extent =
960 Top = 6
961 Left = 38
962 Bottom = 101
963 Right = 211
964 End
965 DisplayFlags = 280
966 TopColumn = 0
967 End
968 Begin Table = "bh_HolidayGroups"
969 Begin Extent =
970 Top = 4
971 Left = 358
972 Bottom = 116
973 Right = 531
974 End
975 DisplayFlags = 280
976 TopColumn = 0
977 End
978 Begin Table = "bh_Holidays"
979 Begin Extent =
980 Top = 195
981 Left = 209
982 Bottom = 324
983 Right = 379
984 End
985 DisplayFlags = 280
986 TopColumn = 0
987 End
988 Begin Table = "bh_ScheduleDetails"
989 Begin Extent =
990 Top = 199
991 Left = 515
992 Bottom = 294
993 Right = 688
994 End
995 DisplayFlags = 280
996 TopColumn = 0
997 End
998 Begin Table = "bh_Schedules"
999 Begin Extent =
1000 Top = 114
1001 Left = 760
1002 Bottom = 243
1003 Right = 935
1004 End
1005 DisplayFlags = 280
1006 TopColumn = 0
1007 End
1008 End
1009 End
1010 Begin SQLPane =
1011 End
1012 Begin DataPane =
1013 Begin ParameterDefaults = ""
1014 End
1015 End
1016 Begin CriteriaPane =
1017 Begin ColumnWidths = 11
1018 Column = 1440
1019 Alias = 900
1020 Table = 1170
1021 Output = 720
1022 Append = 1400
1023 NewValue = 1170
1024 SortType = 1350
1025 SortOrder = 1410
1026 GroupBy = 1350
1027 Filter = 1350
1028 Or ', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'ScheduleHolidays';
1029
1030
1031GO
1032PRINT N'Altering [dbo].[ScheduleHolidays].[MS_DiagramPane2]...';
1033
1034
1035GO
1036EXECUTE sp_updateextendedproperty @name = N'MS_DiagramPane2', @value = N'= 1350
1037 Or = 1350
1038 Or = 1350
1039 End
1040 End
1041End
1042', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'ScheduleHolidays';
1043
1044
1045GO
1046PRINT N'Altering [dbo].[ServiceLevels].[MS_DiagramPane1]...';
1047
1048
1049GO
1050EXECUTE sp_updateextendedproperty @name = N'MS_DiagramPane1', @value = N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
1051Begin DesignProperties =
1052 Begin PaneConfigurations =
1053 Begin PaneConfiguration = 0
1054 NumPanes = 4
1055 Configuration = "(H (1[40] 4[20] 2[20] 3) )"
1056 End
1057 Begin PaneConfiguration = 1
1058 NumPanes = 3
1059 Configuration = "(H (1 [50] 4 [25] 3))"
1060 End
1061 Begin PaneConfiguration = 2
1062 NumPanes = 3
1063 Configuration = "(H (1 [50] 2 [25] 3))"
1064 End
1065 Begin PaneConfiguration = 3
1066 NumPanes = 3
1067 Configuration = "(H (4 [30] 2 [40] 3))"
1068 End
1069 Begin PaneConfiguration = 4
1070 NumPanes = 2
1071 Configuration = "(H (1 [56] 3))"
1072 End
1073 Begin PaneConfiguration = 5
1074 NumPanes = 2
1075 Configuration = "(H (2 [66] 3))"
1076 End
1077 Begin PaneConfiguration = 6
1078 NumPanes = 2
1079 Configuration = "(H (4 [50] 3))"
1080 End
1081 Begin PaneConfiguration = 7
1082 NumPanes = 1
1083 Configuration = "(V (3))"
1084 End
1085 Begin PaneConfiguration = 8
1086 NumPanes = 3
1087 Configuration = "(H (1[56] 4[18] 2) )"
1088 End
1089 Begin PaneConfiguration = 9
1090 NumPanes = 2
1091 Configuration = "(H (1 [75] 4))"
1092 End
1093 Begin PaneConfiguration = 10
1094 NumPanes = 2
1095 Configuration = "(H (1[66] 2) )"
1096 End
1097 Begin PaneConfiguration = 11
1098 NumPanes = 2
1099 Configuration = "(H (4 [60] 2))"
1100 End
1101 Begin PaneConfiguration = 12
1102 NumPanes = 1
1103 Configuration = "(H (1) )"
1104 End
1105 Begin PaneConfiguration = 13
1106 NumPanes = 1
1107 Configuration = "(V (4))"
1108 End
1109 Begin PaneConfiguration = 14
1110 NumPanes = 1
1111 Configuration = "(V (2))"
1112 End
1113 ActivePaneConfig = 0
1114 End
1115 Begin DiagramPane =
1116 Begin Origin =
1117 Top = 0
1118 Left = 0
1119 End
1120 Begin Tables =
1121 Begin Table = "a"
1122 Begin Extent =
1123 Top = 6
1124 Left = 38
1125 Bottom = 135
1126 Right = 269
1127 End
1128 DisplayFlags = 280
1129 TopColumn = 0
1130 End
1131 End
1132 End
1133 Begin SQLPane =
1134 End
1135 Begin DataPane =
1136 Begin ParameterDefaults = ""
1137 End
1138 Begin ColumnWidths = 9
1139 Width = 284
1140 Width = 1500
1141 Width = 1500
1142 Width = 1500
1143 Width = 1500
1144 Width = 1500
1145 Width = 1500
1146 Width = 1500
1147 Width = 1500
1148 End
1149 End
1150 Begin CriteriaPane =
1151 Begin ColumnWidths = 11
1152 Column = 1440
1153 Alias = 900
1154 Table = 1170
1155 Output = 720
1156 Append = 1400
1157 NewValue = 1170
1158 SortType = 1350
1159 SortOrder = 1410
1160 GroupBy = 1350
1161 Filter = 1350
1162 Or = 1350
1163 Or = 1350
1164 Or = 1350
1165 End
1166 End
1167End
1168', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'ServiceLevels';
1169
1170
1171GO
1172PRINT N'Refreshing [dbo].[Set_MailCollectorController]...';
1173
1174
1175GO
1176EXECUTE sp_refreshsqlmodule N'[dbo].[Set_MailCollectorController]';
1177
1178
1179GO
1180PRINT N'Refreshing [dbo].[DeleteSkill]...';
1181
1182
1183GO
1184EXECUTE sp_refreshsqlmodule N'[dbo].[DeleteSkill]';
1185
1186
1187GO
1188-- Refactoring step to update target server with deployed transaction logs
1189
1190IF OBJECT_ID(N'dbo.__RefactorLog') IS NULL
1191BEGIN
1192 CREATE TABLE [dbo].[__RefactorLog] (OperationKey UNIQUEIDENTIFIER NOT NULL PRIMARY KEY)
1193 EXEC sp_addextendedproperty N'microsoft_database_tools_support', N'refactoring log', N'schema', N'dbo', N'table', N'__RefactorLog'
1194END
1195GO
1196IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = '9ffeb4e0-c26e-4a2e-9ed3-e641f4627337')
1197INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('9ffeb4e0-c26e-4a2e-9ed3-e641f4627337')
1198
1199GO
1200
1201GO
1202PRINT N'Checking existing data against newly created constraints';
1203
1204
1205GO
1206USE UpstreamAppConfig;
1207
1208
1209GO
1210ALTER TABLE [dbo].[Skills] WITH CHECK CHECK CONSTRAINT [FK_Skills_EmailAccounts];
1211
1212
1213GO
1214PRINT N'Update complete.';
1215
1216
1217GO