diff options
Diffstat (limited to 'cmso-robot/robot/assets/sql')
| -rw-r--r-- | cmso-robot/robot/assets/sql/InitDB.sql | 3 | ||||
| -rw-r--r-- | cmso-robot/robot/assets/sql/OptimizationInProgresss.sql | 3 | ||||
| -rw-r--r-- | cmso-robot/robot/assets/sql/VericaQueries.sql | 9 | ||||
| -rw-r--r-- | cmso-robot/robot/assets/sql/clearthetable.sql | 11 | ||||
| -rw-r--r-- | cmso-robot/robot/assets/sql/demo.sql | 11 | ||||
| -rw-r--r-- | cmso-robot/robot/assets/sql/searchSQL.sql | 20 | ||||
| -rw-r--r-- | cmso-robot/robot/assets/sql/selectDetails.sql | 4 | ||||
| -rw-r--r-- | cmso-robot/robot/assets/sql/selectall.sql | 9 |
8 files changed, 70 insertions, 0 deletions
diff --git a/cmso-robot/robot/assets/sql/InitDB.sql b/cmso-robot/robot/assets/sql/InitDB.sql new file mode 100644 index 0000000..72667cb --- /dev/null +++ b/cmso-robot/robot/assets/sql/InitDB.sql @@ -0,0 +1,3 @@ +INSERT INTO `scheduler`.`domains` (`domain`) VALUES ('ChangeManagement'); +INSERT INTO `scheduler`.`approval_types` (`domain`, `approval_type`, `approval_count`) VALUES ('ChangeManagement', 'Tier 2', '1'); +SET @@global.time_zone='+00:00';
\ No newline at end of file diff --git a/cmso-robot/robot/assets/sql/OptimizationInProgresss.sql b/cmso-robot/robot/assets/sql/OptimizationInProgresss.sql new file mode 100644 index 0000000..3123ad7 --- /dev/null +++ b/cmso-robot/robot/assets/sql/OptimizationInProgresss.sql @@ -0,0 +1,3 @@ +SET SQL_SAFE_UPDATES = 0; +update schedules set status = 'Pending Schedule' where status = 'Optimization in Progress'; +SET SQL_SAFE_UPDATES = 1;
\ No newline at end of file diff --git a/cmso-robot/robot/assets/sql/VericaQueries.sql b/cmso-robot/robot/assets/sql/VericaQueries.sql new file mode 100644 index 0000000..9d59f43 --- /dev/null +++ b/cmso-robot/robot/assets/sql/VericaQueries.sql @@ -0,0 +1,9 @@ + SELECT distinct vnf_name, pserver_fqdn, pserver_hostname + from qa0_dcaecommon_views.v_vnf_vm_pserver; + +SELECT distinct vnf_name, pserver_fqdn, pserver_hostname from qa0_dcaecommon_views.v_vce_vm_pserver where vnf_name in ('ZRDM1MMSC04') +UNION +SELECT distinct vnf_name, pserver_fqdn, pserver_hostname from qa0_dcaecommon_views.v_vnf_vm_pserver where vnf_name in ('ZRDM1MMSC04') +UNION +SELECT distinct vnf_name, pserver_fqdn, pserver_hostname from qa0_dcaecommon_views.v_vpe_vm_pserver where vnf_name in ('ZRDM1MMSC04') +;
\ No newline at end of file diff --git a/cmso-robot/robot/assets/sql/clearthetable.sql b/cmso-robot/robot/assets/sql/clearthetable.sql new file mode 100644 index 0000000..e382222 --- /dev/null +++ b/cmso-robot/robot/assets/sql/clearthetable.sql @@ -0,0 +1,11 @@ +SET SQL_SAFE_UPDATES = 0; +delete from DOMAIN_DATA; +delete from CHANGE_MANAGEMENT_CHANGE_WINDOWS; +delete from CHANGE_MANAGEMENT_RELATED_ASSETS; +delete from CHANGE_MANAGEMENT_SCHEDULES; +delete from CHANGE_MANAGEMENT_GROUPS; +delete from SCHEDULE_APPROVALS; +delete from SCHEDULE_EVENTS; +delete from SCHEDULES; + +SET SQL_SAFE_UPDATES = 1; diff --git a/cmso-robot/robot/assets/sql/demo.sql b/cmso-robot/robot/assets/sql/demo.sql new file mode 100644 index 0000000..c099283 --- /dev/null +++ b/cmso-robot/robot/assets/sql/demo.sql @@ -0,0 +1,11 @@ + SELECT DISTINCT + s.schedule_id, s.status, s.schedule_info, s.schedule, g.start_time, + d.vnf_name, d.status vnf_status, d.status_message, d.aots_change_id, d.aots_approval_status, d.mso_request_id, d.mso_status, d.aots_status, s.user_id, FROM_UNIXTIME(d.start_time/1000) as StartTime, FROM_UNIXTIME(s.create_date_time/1000) as CreateDateTime +FROM + SCHEDULES s, + CHANGE_MANAGEMENT_GROUPS g, + CHANGE_MANAGEMENT_SCHEDULES d +WHERE + g.schedules_id = s.id + AND d.change_management_groups_id = g.id + order by CreateDateTime desc;
\ No newline at end of file diff --git a/cmso-robot/robot/assets/sql/searchSQL.sql b/cmso-robot/robot/assets/sql/searchSQL.sql new file mode 100644 index 0000000..952800c --- /dev/null +++ b/cmso-robot/robot/assets/sql/searchSQL.sql @@ -0,0 +1,20 @@ +select distinct +s.vnf_name as vnFName, +s.vnf_id as vnfId, +s.status as status, +s.aots_change_id as aotsChangeId, +s.start_time as startTime, +s.finish_time as finishTime, +g.group_id as groupId, +g.last_instance_start_time as lastInstanceStartTime, +g.policy_id as policyId, +g.schedules_id as id, +ss.schedule_id as scheduleId, +dd.name +from change_management_schedules s +inner join change_management_groups g on s.change_management_groups_id = g.id +inner join schedules ss on g.schedules_id = ss.id +left outer join domain_data dd on ss.id = dd.schedules_id +left outer join schedule_approvals sa on ss.id = sa.schedules_id +inner join approval_types at on sa.approval_type_id = at.id +left outer join change_management_related_assets cmra on cmra.change_management_schedule_id = s.id diff --git a/cmso-robot/robot/assets/sql/selectDetails.sql b/cmso-robot/robot/assets/sql/selectDetails.sql new file mode 100644 index 0000000..74fa390 --- /dev/null +++ b/cmso-robot/robot/assets/sql/selectDetails.sql @@ -0,0 +1,4 @@ +SET @@global.time_zone='+00:00'; +select distinct ss.create_date_time, s.id as id, s.vnf_name as vnf_name, s.vnf_id as vnf_id, s.status as status, s.aots_change_id as aots_change_Id, s.start_time as start_time, s.finish_time as finish_time, g.group_id as group_id, g.last_instance_start_time as last_instance_start_time, g.policy_id as policy_id, g.schedules_id as schedules_id from change_management_schedules s inner join change_management_groups g on s.change_management_groups_id = g.id inner join schedules ss on g.schedules_id = ss.id left outer join domain_data dd on ss.id = dd.schedules_id left outer join schedule_approvals sa on ss.id = sa.schedules_id inner join approval_types at on sa.approval_type_id = at.id left outer join change_management_related_assets cmra on cmra.change_management_schedule_id = s.id +-- where ((ss.create_date_time >= TIMESTAMP('2017-07-14T21:30:02.000-0000') AND ss.create_date_time <= TIMESTAMP('2017-07-14T21:30:03.000-0000') )) +order by id
\ No newline at end of file diff --git a/cmso-robot/robot/assets/sql/selectall.sql b/cmso-robot/robot/assets/sql/selectall.sql new file mode 100644 index 0000000..ba41595 --- /dev/null +++ b/cmso-robot/robot/assets/sql/selectall.sql @@ -0,0 +1,9 @@ +select * from DOMAINS; +select * from DOMAIN_DATA order by schedules_id desc; +select * from CHANGE_MANAGEMENT_SCHEDULES order by id desc; +select * from CHANGE_MANAGEMENT_CHANGE_WINDOWS; +select * from CHANGE_MANAGEMENT_GROUPS; +select * from SCHEDULES order by id desc; +select * from APPROVAL_TYPES; +select * from SCHEDULE_APPROVALS; +select * from SCHEDULE_EVENTS;
\ No newline at end of file |