gimli.bioss.sari.ac.uk/gimli.bioss.sari.ac.uk.zip/gimli.bioss.sari.ac.uk/pub/misc/McAfee8.7.Zip/ePOPolicyMigration.exe/CABFILE/1/71To87_Task_Upgrade_Script.sql

Text File | 2007-12-17 | 11KB | 359 lines

-------------------------------------------------------------------------------

-------------  V S E  7 . 1  T O  8 . 7  T A S K  U P G R A D E  --------------

-------------------------------------------------------------------------------

if exists (select * from tempdb..sysobjects where name like '#VseTmpUpgradeTask%')
drop table #VseTmpUpgradeTask
go

CREATE TABLE [dbo].[#VseTmpUpgradeTask] (
    [AutoID] [int] IDENTITY (1, 1) NOT NULL ,
    [ParentType] [tinyint] NOT NULL ,
    [ParentID] [int] NOT NULL ,
    [GlobalTaskID] [int] NOT NULL ,
    [TaskStatus] [tinyint] NULL ,
    [TheTimestamp] [binary] (8) NULL ,
    [ProductCode] [nvarchar] (50) NULL ,
    [DeleteTimestamp] [binary] (8) NULL ,
    [TaskType] [nvarchar] (50) NULL ,
    [TaskName] [nvarchar] (150) NULL ,
    [PlatformsSupported] [nvarchar] (250) NULL ,
    [JustPlaceHolder] [bit] NULL ,
    [Priority] [int] NULL,
    [VSE87AutoID] [int],        -- must link VSE 8.7 autoid
    [VSE71AutoID] [int]            -- with VSE 7.1 autoid
)
go

declare @Continue int
declare @DoNotForce71 nvarchar(30)
declare @DoNotForce nvarchar(30)

select @Continue = 1
select @DoNotForce71 = 'DO_NOT_FORCE_71'
select @DoNotForce = 'DO_NOT_FORCE_%'

-- Don't copy any tasks if 8.0, 8.5 or 8.7 tasks already exist
if exists (select * from Task where ProductCode = 'VIRUSCAN8700' and ParentType = 7 and GlobalTaskID > 0)
begin
    select @Continue = 0
end

if @DoNotForce71 like @DoNotForce
begin
    if exists (select * from Task where ProductCode = 'VIRUSCAN8600' and ParentType = 7 and GlobalTaskID > 0)
    begin
        select @Continue = 0
    end
    if exists (select * from Task where ProductCode = 'VIRUSCAN8000' and ParentType = 7 and GlobalTaskID > 0)
    begin
        select @Continue = 0
    end
end

if @Continue = 1
begin

-- Convert VSE7.1 tasks
-- Copy over all task data and relink globaltaskid field
-- Must copy over task and policies not just move them
-- Copy all non global Task table 7.1 policies to 8.7 policies
-- Copy over global root tasks, but only those with GlobalTaskID > 0

-- copy all VSE 7.1 tasks
insert #VseTmpUpgradeTask
(ParentType, ParentID, GlobalTaskID, TaskStatus, TheTimestamp, ProductCode, DeleteTimestamp,
    TaskType, TaskName, PlatformsSupported, JustPlaceHolder, Priority, VSE87AutoID, VSE71AutoID)
select
ParentType, ParentID, GlobalTaskID, TaskStatus, TheTimestamp, 'VIRUSCAN8700' as ProductCode, DeleteTimestamp,
    TaskType, TaskName, PlatformsSupported, JustPlaceHolder, Priority, 0 as VSE87AutoID, autoid as VSE71AutoID
from Task
where
Task.ProductCode = 'VIRUSCAN7100'
and
(
ParentType in (6,5,4,3,1)
or
(ParentType = 7 and GlobalTaskID > 0 )
)
and Task.TaskType = 'VSC700_Scan_Task'
order by autoid

-- Find next task autoid value
Declare @NextTaskid int
Select @NextTaskid = IDENT_CURRENT('Task')

-- populate VSE87AutoID field in #VseTmpUpgradeTask
Update #VseTmpUpgradeTask
set VSE87AutoID = @NextTaskid + autoid

-- create VSE 8.7 tasks
insert Task
(ParentType, ParentID, GlobalTaskID, TaskStatus, TheTimestamp, ProductCode, DeleteTimestamp,
    TaskType, TaskName, PlatformsSupported, JustPlaceHolder, Priority)
select
ParentType, ParentID, GlobalTaskID, TaskStatus, TheTimestamp, 'VIRUSCAN8700' as ProductCode, DeleteTimestamp,
    TaskType, TaskName + ' (VSE 8.7i)', PlatformsSupported, JustPlaceHolder, Priority
from #VseTmpUpgradeTask
order by autoid

-- Update GlobalTaskID field, link it to VSE 8.7 task instead of VSE 7.1 task
update Task
Set GlobalTaskID = VSE87AutoID
From #VseTmpUpgradeTask
where
Task.GlobalTaskID = #VseTmpUpgradeTask.VSE71AutoID
and Task.ProductCode = 'VIRUSCAN8700'
and
(
Task.ParentType in (6,5,4,3,1)
or
(Task.ParentType = 7 and Task.GlobalTaskID > 0 )
)
and Task.TaskType = 'VSC700_Scan_Task'

-- Update the GlobalTaskID for global root tasks,
-- (but only for those with with GlobalTaskID > 0)
Declare @Root71Taskid int
Select @Root71Taskid = AutoId
From Task
where ParentType = 7
and GlobalTaskID = 0
and ProductCode = 'VIRUSCAN7100'
and TaskType = 'VSC700_Scan_Task'

Declare @Root87Taskid int
Select @Root87Taskid = AutoId
From Task
where ParentType = 7
and GlobalTaskID = 0
and ProductCode = 'VIRUSCAN8700'
and TaskType = 'VSC700_Scan_Task'

update Task
Set GlobalTaskID = @Root87Taskid
where
Task.ProductCode = 'VIRUSCAN8700'
and GlobalTaskID = @Root71Taskid
and
(
ParentType in (6,5,4,3,1)
or
(ParentType = 7 and GlobalTaskID > 0 )
)
and TaskType = 'VSC700_Scan_Task'

-- copy VSE 7.1 tasksettings to VSE 8.7 task settings
-- Copy all non global TaskSetting from 7.1 Task settings to 8.7 Task settings
-- copy only settings where GlobalTaskID > 0
-- Change parentid to point to the VSE 8.7 task
insert TaskSettings
(ParentID, SectionName, SettingName, Value, TheTimestamp)
select VSE87AutoID, SectionName, SettingName, Value, TaskSettings.TheTimestamp
from TaskSettings, #VseTmpUpgradeTask
where
TaskSettings.ParentID = #VseTmpUpgradeTask.VSE71AutoID

/* ----------------------------------------------------------------------------
 Change the SectionName and/or the SettingName of the policies that have moved.
 A "-" sign indicates that the value is no longer used and is to be deleted only.

OLD VALUE:                                                        NEW VALUE:
[Actions]                                                        [Actions]
uAction                                        = 3    (Move)        =>  = 4 (Delete)
uSecAction                                                    =>  = 1 (Continue)

[Actions]                                                        [Actions]
uAction                                        = 5    (Clean)
uSecAction                                    = 3    (Move)        =>  = 4 (Delete)

[Actions]                                                        [Actions]
uAction                                        = 4    (Delete)
uSecAction                                    = 3    (Move)        =>  = 1 (Continue)

[Actions]
-szMoveToFolder
-szSecMoveToFolder

[Reports]
-bLogUsername
----------------------------------------------------------------------------- */

/* ----------------------------------------------------------------------------
[Actions]                                                        [Actions]
uAction                                        = 3    (Move)        =>  = 4 (Delete)
uSecAction                                                    =>  = 1 (Continue)
---------------------------------------------------------------------------- */
update TaskSettings
set Value = '1'
where
SectionName = 'Actions'
and SettingName = 'uSecAction'
and TaskSettings.parentid in
    (select b.parentid
    from #VseTmpUpgradeTask, TaskSettings b
    where
    b.ParentID = #VseTmpUpgradeTask.VSE87AutoID
    and b.SectionName = 'Actions'
    and b.SettingName = 'uAction'
    and b.Value = '3'
    )

update TaskSettings
set Value = '4'
where
SectionName = 'Actions'
and SettingName = 'uAction'
and TaskSettings.parentid in
    (select b.parentid
    from #VseTmpUpgradeTask, TaskSettings b
    where
    b.ParentID = #VseTmpUpgradeTask.VSE87AutoID
    and b.SectionName = 'Actions'
    and b.SettingName = 'uAction'
    and b.Value = '3'
    )

/* ----------------------------------------------------------------------------
[Actions]                                                        [Actions]
uAction                                        = 5 (Clean)
uSecAction                                    = 3    (Move)        =>  = 4 (Delete)
---------------------------------------------------------------------------- */
update TaskSettings
set Value = '4'
where
SectionName = 'Actions'
and SettingName = 'uSecAction'
and TaskSettings.parentid in
    (select b.parentid
    from #VseTmpUpgradeTask, TaskSettings b, TaskSettings c
    where
    b.ParentID = #VseTmpUpgradeTask.VSE87AutoID
    and b.SectionName = 'Actions'
    and b.SettingName = 'uAction'
    and b.Value = '5'
    and b.ParentID = c.ParentID
    and c.SectionName = 'Actions'
    and c.SettingName = 'uSecAction'
    and c.Value = '3'
    )

/* ----------------------------------------------------------------------------
[Actions]                                                        [Actions]
uAction                                        = 4    (Delete)
uSecAction                                    = 3    (Move)        =>  = 1 (Continue)
---------------------------------------------------------------------------- */
update TaskSettings
set Value = '1'
where
SectionName = 'Actions'
and SettingName = 'uSecAction'
and TaskSettings.parentid in
    (select b.parentid
    from #VseTmpUpgradeTask, TaskSettings b, TaskSettings c
    where
    b.ParentID = #VseTmpUpgradeTask.VSE87AutoID
    and b.SectionName = 'Actions'
    and b.SettingName = 'uAction'
    and b.Value = '4'
    and b.ParentID = c.ParentID
    and c.SectionName = 'Actions'
    and c.SettingName = 'uSecAction'
    and c.Value = '3'
    )

/* ----------------------------------------------------------------------------
[Actions]
-szMoveToFolder
-szSecMoveToFolder
---------------------------------------------------------------------------- */
delete TaskSettings
where
TaskSettings.SectionName = 'Actions'
and TaskSettings.SettingName = 'szMoveToFolder'
and TaskSettings.parentid in
    (select b.parentid
    from #VseTmpUpgradeTask, TaskSettings b
    where
    b.ParentID = #VseTmpUpgradeTask.VSE87AutoID
    )

delete TaskSettings
where
TaskSettings.SectionName = 'Actions'
and TaskSettings.SettingName = 'szSecMoveToFolder'
and TaskSettings.parentid in
    (select b.parentid
    from #VseTmpUpgradeTask, TaskSettings b
    where
    b.ParentID = #VseTmpUpgradeTask.VSE87AutoID
    )

/* ----------------------------------------------------------------------------
[Reports]
-bLogUsername
---------------------------------------------------------------------------- */
delete TaskSettings
where
TaskSettings.SectionName = 'Reports'
and TaskSettings.SettingName = 'bLogUsername'
and TaskSettings.parentid in
    (select b.parentid
    from #VseTmpUpgradeTask, TaskSettings b
    where
    b.ParentID = #VseTmpUpgradeTask.VSE87AutoID
    )
end
go

if exists (select * from tempdb..sysobjects where name like '#VseTmpUpgradeTask%')
drop table #VseTmpUpgradeTask
go

/* ----------------------------------------------------------------------------
Update the task timestamps
---------------------------------------------------------------------------- */
if exists (select * from tempdb..sysobjects where name like '#vsetasktime%')
drop table #vsetasktime
go

begin
-- Update the timestamps so that the agents know there are new tasks
declare @Currenttime int
select @Currenttime = @@dbts

update task set TheTimestamp = @Currenttime
where ProductCode = 'VIRUSCAN8700'

select parentid as autoid, parenttype, max(thetimestamp)as maxtime
into #vsetasktime
from task
where ProductCode = 'VIRUSCAN8700'
group by parentid, parenttype

update leafnode
set tasktimestamp = #vsetasktime.maxtime + 1
from #vsetasktime
where leafnode.autoid = #vsetasktime.autoid
and #vsetasktime.parenttype = leafnode.type

update branchnode
set tasktimestamp = #vsetasktime.maxtime + 1
from #vsetasktime
where branchnode.autoid = #vsetasktime.autoid
and #vsetasktime.parenttype = branchnode.type

update tasksettings set TheTimestamp = @Currenttime
from task
where tasksettings.parentid = task.autoid
and task.ProductCode = 'VIRUSCAN8700'

end
go

if exists (select * from tempdb..sysobjects where name like '#vsetasktime%')
drop table #vsetasktime
go