8.5.0 to 8.5.001 Database Changes - Commands
Copy the following commands and run them in the SQL query window for the SpeechMiner 8.5.0 to 8.5.0.1 Upgrade Procedure:
ALTER TABLE dbo.segmentAudioTbl ADD arrivalTime int NULL
GO
GO
ALTER TABLE [dbo].[coachingStaticCallListCalls] DROP CONSTRAINT [FK_coachingStaticCallListCalls_callMetaTbl]
GO
GO
GO
ALTER TABLE [dbo].[agentEntityTbl] DROP CONSTRAINT [IX_agentEntityTbl]
GO
GO
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_agentFilterCleanByDays]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_agentFilterCleanByDays]
GO
ALTER PROCEDURE [dbo].[sp_rebuild_partition_help_tables]
WITH EXECUTE AS SELF
AS
BEGIN TRAN
TRUNCATE TABLE partitionProgramTbl
insert into partitionprogramTbl
select distinct b.partitionid,a.programid
from (select callId,programId from callMetaTbl union select textId as callId,programId from TextData) a, callPartitionTbl as b
where a.callid=b.callid
COMMIT
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[partitionAgentTbl]') AND type in (N'U'))
DROP TABLE [dbo].[partitionAgentTbl]
GO
GO
ALTER TABLE WordPrecision
ADD "wer" float(20)
ALTER TABLE WordPrecision
ADD "recall" float(20)
GO
GO
ALTER TABLE NGProbs1
ADD "backoff" float(20)
ALTER TABLE NGProbs2
ADD "backoff" float(20)
ALTER TABLE NGProbs3
ADD "backoff" float(20)
ALTER TABLE NGProbs4
ADD "backoff" float(20)
GO
GO
IF NOT EXISTS(select * from sys.columns where Name = N'isBackupConfigurationServer' and Object_ID = Object_ID(N'ComputerList'))
BEGIN
ALTER TABLE [dbo].[ComputerList] ADD isBackupConfigurationServer bit NOT NULL DEFAULT 0
END
GO
CREATE TABLE wildcardGrammars
(
languageId int NOT NULL,
[key] nvarchar(50) NOT NULL,
value nvarchar(50) NOT NULL,
PRIMARY KEY (languageId,[key])
);
GO
INSERT INTO wildcardGrammars values
(0,'[alphanum]','alphanum'),
(0,'[boolean]','boolean'),
(0,'[ccexpdate]','ccexpdate'),
(0,'[creditcard]','creditcard'),
(0,'[currency]','currency'),
(0,'[date]','date'),
(0,'[digits]','digits'),
(0,'[number]','number'),
(0,'[phone]','phone'),
(0,'[socialsecurity]','socialsecurity'),
(0,'[time]','time'),
(0,'[voiceenroll]','voiceenroll'),
(0,'[zipcode]', 'zipcode'),
(2,'[alphanum]','alphanum'),
(2,'[boolean]','boolean'),
(2,'[ccexpdate]','ccexpdate'),
(2,'[creditcard]','creditcard'),
(2,'[currency]','currency'),
(2,'[date]','date'),
(2,'[digits]','digits'),
(2,'[number]','number'),
(2,'[phone]','phone'),
(2,'[time]','time'),
(2,'[voiceenroll]','voiceenroll'),
(2,'[zipcode]','zipcode');
GO
GO
ALTER TABLE NGProbs1
DROP COLUMN backoff
ALTER TABLE NGProbs2
DROP COLUMN backoff
ALTER TABLE NGProbs3
DROP COLUMN backoff
ALTER TABLE NGProbs4
DROP COLUMN backoff
GO
GO
ALTER TABLE RecognitionLanguages
ADD "WordConfidence" varchar(100)
GO
Update RecognitionLanguages
SET WordConfidence = 'wordconfidence'
UPDATE RecognitionLanguages
SET WordConfidence = 'wordconfidence_German' where index1 = 6
UPDATE RecognitionLanguages
SET WordConfidence = 'wordconfidence_Catalan' where index1 = 13
UPDATE RecognitionLanguages
SET WordConfidence = 'wordconfidence_English' where index1 in (0,3,14)
UPDATE RecognitionLanguages
SET WordConfidence = 'wordconfidence_Spanish' where index1 in (1,2)
UPDATE RecognitionLanguages
SET WordConfidence = 'wordconfidence_Portuguese' where index1 = 11
UPDATE RecognitionLanguages
SET WordConfidence = 'wordconfidence_French' where index1 = 5
GO
CREATE TABLE WordConfidenceInterpolation
(
Language int,
wer varchar(255),
precision varchar(255),
recall varchar(255)
)
grant SELECT on WordConfidenceInterpolation to Platform
INSERT INTO WordConfidenceInterpolation values(0,'-0.0356 0.8034','0.0431 0.3559','0.0272 0.5093')
INSERT INTO WordConfidenceInterpolation values(1,'-0.0383 0.7144','0.0226 0.5246','0.0338 0.3492')
INSERT INTO WordConfidenceInterpolation values(2,'-0.0369 0.8605','0.0418 0.3515','0.0343 0.1833')
INSERT INTO WordConfidenceInterpolation values(3,'-0.0328 0.7378','0.0395 0.3265','0.0246 0.3305')
INSERT INTO WordConfidenceInterpolation values(5,'-0.0334 0.9805','0.0386 0.1734','0.0386 0.1734')
INSERT INTO WordConfidenceInterpolation values(6,'-0.0179 0.6302','0.0295 0.3729','0.018 0.3883')
INSERT INTO WordConfidenceInterpolation values(11,'-0.0203 0.3161','0.0339 0.3161','0.0167 0.4534')
INSERT INTO WordConfidenceInterpolation values(13,'-0.0356 0.8034','0.0431 0.3559','0.0272 0.5093')
INSERT INTO WordConfidenceInterpolation values(14,'-0.033 0.6866','0.043 0.3835','0.0379 0.3249')
GO
GO
GRANT select ON [dbo].[wildcardGrammars] TO [SMART],[SMARTCompileGrammar],[InteractionReceiver],[Platform],[SMConfig],[Web]
GO
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[callPartitionTbl]') AND name = N'IX_callPartitionTbl_partitionId')
DROP INDEX [IX_callPartitionTbl_partitionId] ON [dbo].[callPartitionTbl] WITH ( ONLINE = OFF )
GO
GO
Update RecognitionLanguages
set Display='South African English',
NuanceRecognizerLanguagePack='en-ZA',
AS_PER_CHAR_TIME_DURATION=0.075,
culture='en-ZA',
encoding='English_South Africa.20127'
where index1=15
Update RecognitionLanguages
set encoding='French_France.28591' where index1=5
GO
GO
Update RecognitionLanguages
set Display='',
NuanceRecognizerLanguagePack=NULL,
AS_PER_CHAR_TIME_DURATION=NULL,
culture=NULL,
DictionaryName=NULL,
encoding=NULL
where index1=15
GO
GO
Alter FUNCTION [dbo].[sp_getTopicEventSequences]
(
@callid int, @threshold int
)
RETURNS varchar(max)
AS
BEGIN
declare @text as varchar(max)
set @text=''
select @text=@text + ltrim(rtrim(str(e.resourceID))) + ' '
from callEventstbl e
where e.callid = @callid
and e.type=1 and e.subtype=1
and e.confidence>=@threshold
and e.found=1
order by e.starttime
RETURN substring(@text, 1, len(@text))
END
go
GO
update dbo.callrecognizer set ADJUST_CONF = 'False'
GO
GO
Delete from wildcardGrammars
where [key]='[voiceenroll]'
GO
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
EXECUTE sp_rename N'dbo.wildcardGrammars.[key]', N'Tmp_token', 'COLUMN'
GO
EXECUTE sp_rename N'dbo.wildcardGrammars.value', N'Tmp_grammar_1', 'COLUMN'
GO
EXECUTE sp_rename N'dbo.wildcardGrammars.Tmp_token', N'token', 'COLUMN'
GO
EXECUTE sp_rename N'dbo.wildcardGrammars.Tmp_grammar_1', N'grammar', 'COLUMN'
GO
ALTER TABLE dbo.wildcardGrammars SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
GO
declare @table_name nvarchar(256)
declare @col_name nvarchar(256)
declare @Command nvarchar(1000)
set @table_name = N'ComputerList'
set @col_name = N'isBackupConfigurationServer'
select @Command = 'ALTER TABLE ' + @table_name + ' drop constraint ' + d.name
from sys.tables t
join sys.default_constraints d
on d.parent_object_id = t.object_id
join sys.columns c
on c.object_id = t.object_id
and c.column_id = d.parent_column_id
where t.name = @table_name
and c.name = @col_name
execute (@Command)
GO
IF EXISTS(select * from sys.columns where Name = N'isBackupConfigurationServer' and Object_ID = Object_ID(N'ComputerList'))
BEGIN
ALTER TABLE [dbo].[ComputerList] DROP COLUMN isBackupConfigurationServer
END
GO
GO
ALTER PROCEDURE [dbo].[sp_deleteTexts](@TextIDs varchar(max),@deletedTextsNum int OUTPUT)
WITH EXECUTE AS SELF
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ParmDefinition nvarchar(500);
set @deletedTextsNum ='-1'
declare @SelectCounter nvarchar(max)
set @SelectCounter = N'select @sOUT = count(*) from textData where textId in (' + @TextIDs + ')'
SET @ParmDefinition = N'@sOUT int OUTPUT';
exec sp_executesql @SelectCounter,@ParmDefinition,@sOUT=@deletedTextsNum OUTPUT;
declare @deleteTexts as nvarchar (max)
set @deleteTexts = ''
---------
set @deleteTexts = @deleteTexts + 'DELETE FROM callMetaExTbl where callid in (' + @TextIDs + ')'
set @deleteTexts = @deleteTexts + 'DELETE FROM callcategoryTbl where callid in (' + @TextIDs + ')'
set @deleteTexts = @deleteTexts + 'DELETE FROM callEventsTbl where callid in (' + @TextIDs + ')'
set @deleteTexts = @deleteTexts + 'DELETE FROM callAgentTbl where callid in (' + @TextIDs + ')'
set @deleteTexts = @deleteTexts + 'DELETE FROM callPartitionTbl where callid in (' + @TextIDs + ')'
set @deleteTexts = @deleteTexts + 'DELETE FROM textparties where textId in (' + @TextIDs + ')'
set @deleteTexts = @deleteTexts + 'DELETE FROM textStatus where textId in (' + @TextIDs + ')'
set @deleteTexts = @deleteTexts + 'DELETE FROM textData where textId in (' + @TextIDs + ')'
set @deleteTexts = @deleteTexts + 'DELETE FROM repCategoryMetaTbl where callid in (' + @TextIDs + ')'
set @deleteTexts = @deleteTexts + 'DELETE FROM callSpeakersTbl where callid in (' + @TextIDs + ')'
---------
exec sp_executesql @deleteTexts
END
GO
ALTER TABLE callNotesTbl DROP CONSTRAINT FK_callNotesTbl_callMetaTbl
GO
UPDATE RecognitionLanguages
set DictionaryName = 'pt_BR' where index1=11
GO
UPDATE RecognitionLanguages
set DictionaryName = 'es_MX' where index1 = 1
GO
UPDATE RecognitionLanguages
set DictionaryName = 'es_ES' where index1 = 2
GO
GO
delete from reportCachingQueue
GO
alter table reportCachingQueue add runAtTime int NOT NULL
GO
CREATE PROCEDURE [dbo].[sp_getReportCachingNextTask]
AS
BEGIN
BEGIN TRAN
declare @id int
SELECT top 1 @id=id from reportCachingQueue with(updlock holdlock)
where nextTimeToRun<dbo.time2tod(GetUtcDate())
order by nextTimeToRun
IF @id IS NOT NULL
BEGIN
update reportCachingQueue set nextTimeToRun=dbo.time2tod(GetUtcDate())+1*60*60 where id=@id
select reportid, [partitions], runAtTime from reportCachingQueue where id=@id
END
COMMIT
END
GO
grant execute on sp_getReportCachingNextTask to platform
GO
GO
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.indexParamsTbl ADD
autoRestore bit NOT NULL CONSTRAINT DF_indexParamsTbl_autoRestore DEFAULT 0,
timeToWaitForFolder int NULL
GO
ALTER TABLE dbo.indexParamsTbl SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
GRANT update(indexdirectorypath, dailyBackupLocation) ON [indexparamstbl] TO [Platform]
GO
GO
grant update(status,stoppedForPurge) on computerList to interactionReceiver
grant execute on sp_enqueuePurgeTask to interactionReceiver
GO
ALTER PROCEDURE [dbo].[sp_getClusterTrendData]
@clusterId int,
@maxTrendWeeksBack int
AS
BEGIN
declare @firstDayOfWeek as int
set @firstDayOfWeek = dbo.getFirstDayOfWeek()
set DATEFIRST @firstDayOfWeek
declare @paramsId as int
declare @reportDate as datetime
set @paramsId = (select paramsId from ExplorationReports where reportid=(select reportid from ExplorationReportsData where clusterid=@clusterId))
set @reportDate = (select dbo.tod2time(endTime) from ExplorationReportParams where id=@paramsId)
IF @reportDate IS NULL
BEGIN
select NULL as[time], NULL as [numberOfCalls], NULL as [totalNumberOfCalls]
RETURN -- the specified cluster id does not exist.
END
create table #trendData (
[time] datetime NOT NULL,
[numberOfCalls] int,
[totalNumberOfCalls] int
)
insert into #trendData
select dbo.tod2time([time]) as [time], numberOfCalls, totalNumberOfCalls
from dbo.ExplorationTrendData where clusterid=@clusterId
IF NOT EXISTS(select [time] from #trendData)
BEGIN
insert into #trendData values(DATEADD(DAY, 1-DATEPART(WEEKDAY, @reportDate), @reportDate), NULL, NULL)
END
-- add the dates (start of week) from the last date with data to the report time
while dateadd(week, 1, (select max(time) from #trendData)) < @reportDate
BEGIN
declare @newTime as datetime
set @newTime = dateadd(week, 1, (select max(time) from #trendData))
IF not exists(select * from #trendData where [time] = @newTime)
BEGIN
insert into #trendData values(@newTime, NULL, NULL)
END
END
declare @currentTrendDate as datetime
set @currentTrendDate = (select max(time) from #trendData)
-- skip the first week if the current report date is the first day of the week
if @currentTrendDate = @reportDate
BEGIN
set @currentTrendDate = dateadd(week, -1, @currentTrendDate)
END
declare @count as int
set @count = 0
while @count < @maxTrendWeeksBack - 1
BEGIN
IF not exists(select * from #trendData where [time] = @currentTrendDate)
BEGIN
insert into #trendData values(@currentTrendDate, NULL, NULL)
END
set @currentTrendDate = dateadd(week, -1, @currentTrendDate)
set @count = @count + 1
END
set @currentTrendDate = dateadd(week, 1, @currentTrendDate)
delete from #trendData where time<@currentTrendDate -- remove the old items if there are more than @maxTrendWeeksBack items
select * from #trendData order by [time]
drop table #trendData
END
GO
GO
GRANT UPDATE ON dbo.[TextStatus] TO Web
GO
GO
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TextData
DROP CONSTRAINT FK_TextData_ResourceType
GO
ALTER TABLE dbo.ResourceType SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TextData
DROP CONSTRAINT FK_TextData_programInfoTbl
GO
ALTER TABLE dbo.programInfoTbl SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TextData
DROP CONSTRAINT DF_TextData_customerId
GO
ALTER TABLE dbo.TextData
DROP CONSTRAINT DF_TextData_customerGroupId
GO
ALTER TABLE dbo.TextData
DROP CONSTRAINT DF_TextData_programId
GO
CREATE TABLE dbo.Tmp_TextData
(
textId int NOT NULL,
resourceTypeId int NOT NULL,
originalTime int NULL,
customerId varchar(256) NOT NULL,
customerGroupId varchar(256) NOT NULL,
subject nvarchar(MAX) NULL,
body nvarchar(MAX) NULL,
externalTextId varchar(256) NULL,
sender varchar(8000) NULL,
receiver varchar(8000) NULL,
cc varchar(8000) NULL,
bcc varchar(8000) NULL,
programId int NOT NULL,
originalBody varchar(MAX) NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_TextData SET (LOCK_ESCALATION = TABLE)
GO
GRANT DELETE ON dbo.Tmp_TextData TO Platform AS dbo
GO
GRANT INSERT ON dbo.Tmp_TextData TO Platform AS dbo
GO
GRANT SELECT ON dbo.Tmp_TextData TO Web AS dbo
GO
GRANT SELECT ON dbo.Tmp_TextData TO Platform AS dbo
GO
GRANT SELECT ON dbo.Tmp_TextData TO SMART AS dbo
GO
GRANT UPDATE ON dbo.Tmp_TextData TO Web AS dbo
GO
GRANT UPDATE ON dbo.Tmp_TextData TO Platform AS dbo
GO
ALTER TABLE dbo.Tmp_TextData ADD CONSTRAINT
DF_TextData_customerId DEFAULT ('') FOR customerId
GO
ALTER TABLE dbo.Tmp_TextData ADD CONSTRAINT
DF_TextData_customerGroupId DEFAULT ('') FOR customerGroupId
GO
ALTER TABLE dbo.Tmp_TextData ADD CONSTRAINT
DF_TextData_programId DEFAULT ((0)) FOR programId
GO
IF EXISTS(SELECT * FROM dbo.TextData)
EXEC('INSERT INTO dbo.Tmp_TextData (textId, resourceTypeId, originalTime, customerId, customerGroupId, subject, body, externalTextId, sender, receiver, cc, bcc, programId, originalBody)
SELECT textId, resourceTypeId, originalTime, customerId, customerGroupId, CONVERT(nvarchar(MAX), subject), CONVERT(nvarchar(MAX), body), externalTextId, sender, receiver, cc, bcc, programId, originalBody FROM dbo.TextData WITH (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.TextStatus
DROP CONSTRAINT FK_TextStatus_TextData
GO
DROP TABLE dbo.TextData
GO
EXECUTE sp_rename N'dbo.Tmp_TextData', N'TextData', 'OBJECT'
GO
ALTER TABLE dbo.TextData ADD CONSTRAINT
PK_TextDataTbl PRIMARY KEY CLUSTERED
(
textId
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_externalTextId ON dbo.TextData
(
externalTextId
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.TextData ADD CONSTRAINT
FK_TextData_programInfoTbl FOREIGN KEY
(
programId
) REFERENCES dbo.programInfoTbl
(
programId
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
ALTER TABLE dbo.TextData ADD CONSTRAINT
FK_TextData_ResourceType FOREIGN KEY
(
resourceTypeId
) REFERENCES dbo.ResourceType
(
resourceTypeId
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TextStatus WITH NOCHECK ADD CONSTRAINT
FK_TextStatus_TextData FOREIGN KEY
(
textId
) REFERENCES dbo.TextData
(
textId
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
ALTER TABLE dbo.TextStatus
NOCHECK CONSTRAINT FK_TextStatus_TextData
GO
ALTER TABLE dbo.TextStatus SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
GO
UPDATE [webServiceParams] SET [sendFeedbackEmail]='customercare@genesys.com'
GO
delete wildcardGrammars where grammar='digits'
GO
GO
UPDATE indexer set calls_per_chunk=6000
GO
GO
update dbo.versionTbl set version= '8.5.7104' where resource in ('SM', 'SMART')
go
This page was last edited on December 28, 2014, at 14:04.
Comments or questions about this documentation? Contact us for support!