summaryrefslogtreecommitdiffstats
path: root/cmso-robot/robot/assets/sql
diff options
context:
space:
mode:
authorRamaPrasad Amaranarayana (ra5425) <ra5425@att.com>2018-09-19 17:40:40 -0400
committerRamaPrasad Amaranarayana (ra5425) <ra5425@att.com>2018-09-19 17:40:40 -0400
commitfbe4bb98345727d190cefcfb3ec54ad9075fba9d (patch)
treef2e0c091a4c132c89dafb061c81eeebfb0e9a773 /cmso-robot/robot/assets/sql
parentb031d50f82f8ca5b7fd0609a92c719f43a9f518d (diff)
Change Management Schedule Optimization
Adding Robot Test Scripts for Change Management Schedule Optimization Change-Id: Id5edf9d3fb3c2390791362692c8b25f7607045c6 Issue-ID: OPTFRA-352 Signed-off-by: RamaPrasad Amaranarayana (ra5425) <ra5425@att.com>
Diffstat (limited to 'cmso-robot/robot/assets/sql')
-rw-r--r--cmso-robot/robot/assets/sql/InitDB.sql3
-rw-r--r--cmso-robot/robot/assets/sql/OptimizationInProgresss.sql3
-rw-r--r--cmso-robot/robot/assets/sql/VericaQueries.sql9
-rw-r--r--cmso-robot/robot/assets/sql/clearthetable.sql11
-rw-r--r--cmso-robot/robot/assets/sql/demo.sql11
-rw-r--r--cmso-robot/robot/assets/sql/searchSQL.sql20
-rw-r--r--cmso-robot/robot/assets/sql/selectDetails.sql4
-rw-r--r--cmso-robot/robot/assets/sql/selectall.sql9
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