VSA subclient information #2

Continued topic from Community Topic #10 VSA subclient information #1

As we can copy VSA subclients from one VSA client to another using tricky and slightly dangerous approacy utilizing Clone CLI, unfortunately there's another issue that we cannot clone individual schedules (not schedule policy settings, this would be cloned).

To achieve this, we need to modify CSDB again.
Basically schedule information is stored in TM_Task and TM_SubTask tables, but other tables started from TM_ also be involved.
And another thing is, the format to generate schedule is quite complicated so cannot easily mimic the behavior, so I took an approach to copy all records to new schedule at SQL level.

Digging a lot, we can achieve like this:

• List up all schedules per subclient.
• Create dummy schedules on this subclient (this can be done by creating simple schedule on CommCell Console and retrieve parameter XML file from "Save As Script").
• Replace the schedule "contents" with SQL queries.

Workflow images like this:

Listing up all schedules like:
 

declare @appid int = ?  -- This is subclient ID

select subTaskId from TM_SubTask where taskId in (select taskId from TM_AssocEntity where subclientId = @appid)

Looping all schedules corresponding to this subclient, generating dummy schedule with dummy name:

Any dummy schedule can be specified in this XML, since all information will be replaced later (I'm using one-time schedule for this).

Then copy all original schedule, including patterns, exceptions, etc.:

declare @stid_orig int = ?
declare @stid_dest int

declare @patassocid_orig int
declare @patassocid_dest int
declare @patid_orig int
declare @patid_dest int
declare @patid_new int

-- get destination subtask id from schedule name (dummy_scidorig)
set @stid_dest = (select subTaskId from TM_SubTask with(nolock) where subTaskName = concat('dummy_', @stid_orig))

-- copy TM_SubTaskOptions and TM_SubTaskXMLOptions info
delete from TM_SubTaskOptions where subTaskId = @stid_dest
delete from TM_SubTaskXMLOptions where subTaskId = @stid_dest

insert into TM_SubTaskOptions (subTaskId, optionId, type, value)
select @stid_dest, optionId, type, value from TM_SubTaskOptions where subTaskId = @stid_orig

insert into TM_SubTaskXMLOptions (subTaskId, xmlValue)
select @stid_dest, xmlValue from TM_SubTaskXMLOptions where subTaskId = @stid_orig

-- update subtask name
update TM_SubTask
set subTaskName = (select subTaskName from TM_SubTask with(nolock) where subTaskId = @stid_orig)
where subTaskId = @stid_dest

-- get schedule associated info
set @patassocid_orig = (select patternAssocId from TM_PatternAssoc where subTaskId = @stid_orig)
set @patassocid_dest = (select patternAssocId from TM_PatternAssoc where subTaskId = @stid_dest)

set @patid_orig = (select patternId from TM_PatternAssoc where subTaskId = @stid_orig)
set @patid_dest = (select patternId from TM_PatternAssoc where subTaskId = @stid_dest)

-- re-generate pattern
delete from TM_PatternAssoc where patternAssocId = @patassocid_dest
delete from TM_Pattern where patternId = @patid_dest
delete from TM_RepeatPattern where patternId = @patid_dest

insert into TM_Pattern (name, ownerId, status, flags, freq_type, freq_interval, freq_relative_interval, freq_recurrence_factor, freq_subday_interval, skipOccurence, skipDayNumber, active_start_date, active_start_time, active_end_date, active_end_time, active_end_occurence, created, modified, tzId, calendarId, description, local_start_date, local_end_date, disabled, deleted, scheduling, freq_restart_interval)
select name, ownerId, status, flags, freq_type, freq_interval, freq_relative_interval, freq_recurrence_factor, freq_subday_interval, skipOccurence, skipDayNumber, active_start_date, active_start_time, active_end_date, active_end_time, active_end_occurence, created, modified, tzId, calendarId, description, local_start_date, local_end_date, disabled, deleted, scheduling, freq_restart_interval from TM_Pattern where patternId = @patid_orig

set @patid_new = scope_identity()
--select @patid_new

insert into TM_PatternAssoc (patternId, subTaskId, flags, disabled, deleted, scheduling)
select @patid_new, @stid_dest, flags, disabled, deleted, scheduling from TM_PatternAssoc where patternAssocId = @patassocid_orig

-- exception on repeated patterns
insert into TM_RepeatPattern
select @patid_new, onOccurence, onDay, repeatOn, onDayNumber, created, modified, tzId, exception, description from TM_RepeatPattern where patternid = @patid_orig

Again this also involve direct modification of CSDB and specific for my customers only, but researching CSDB in deep would be fun and sometimes this is the only way to achieve customer's demands, somebody would be interested in this.

Thanks and regards,