| 1 |
rodolico |
1 |
/* do some housecleaning. This sync's the original tags with what we use in the new database */
|
|
|
2 |
update camp.device_type set name = 'Virtual' where name = 'Xen Virtual';
|
|
|
3 |
update camp.device_type set name = 'PCI Card' where name = 'PCI Periphial';
|
|
|
4 |
update camp.device_type set name = 'Switch' where name = 'Network Switch';
|
|
|
5 |
|
|
|
6 |
/* get some device types not in the new database */
|
|
|
7 |
insert into camp_test.device_type (name,added_date,removed_date,show_as_system)
|
|
|
8 |
select name,added_date,removed_date,show_as_system
|
|
|
9 |
from camp.device_type
|
|
|
10 |
where name not in (select name from camp_test.device_type);
|
|
|
11 |
|
|
|
12 |
/* copy any devices that don't exist in the new database into it */
|
|
|
13 |
|
|
|
14 |
insert into device (site_id,device_type_id,name,notes,added_date,removed_date)
|
|
|
15 |
select min_site.default_site_id,
|
|
|
16 |
device_type.device_type_id,
|
|
|
17 |
to_be_updated.devicename,
|
|
|
18 |
to_be_updated.notes,
|
|
|
19 |
to_be_updated.added_date,
|
|
|
20 |
to_be_updated.removed_date
|
|
|
21 |
from
|
|
|
22 |
camp_test.client,
|
|
|
23 |
camp_test.device_type,
|
|
|
24 |
(
|
|
|
25 |
select client_id,min(site_id) default_site_id
|
|
|
26 |
from site
|
|
|
27 |
group by client_id
|
|
|
28 |
) min_site,
|
|
|
29 |
(
|
|
|
30 |
select device.name devicename,device.notes, device.added_date,device.removed_date,client.name clientname,device_type.name devicetypename
|
|
|
31 |
from camp.device join camp.site using (site_id) join camp.client using (client_id) join camp.device_type using (device_type_id)
|
|
|
32 |
where device.name not like 'HASH%'
|
|
|
33 |
and device.name not like '[memory%'
|
|
|
34 |
and device.name not in
|
|
|
35 |
(
|
|
|
36 |
select device.name from camp_test.device join camp_test.device_type using (device_type_id) where device_type.show_as_system = 'Y'
|
|
|
37 |
union
|
|
|
38 |
select alias from device_alias
|
|
|
39 |
)
|
|
|
40 |
) to_be_updated
|
|
|
41 |
where client.name = to_be_updated.clientname
|
|
|
42 |
and device_type.name = to_be_updated.devicetypename
|
|
|
43 |
and min_site.client_id = client.client_id
|
|
|
44 |
;
|
|
|
45 |
|
|
|
46 |
/* we need login populated for the maintenance schedule */
|
|
|
47 |
insert into camp_test.login (email,pass,where_clause,added_date,removed_date)
|
|
|
48 |
select email,pass,where_clause,added_date,removed_date from camp.login where email not in (select email from camp_test.login);
|
|
|
49 |
|
|
|
50 |
/* get all the maintenance tasks */
|
|
|
51 |
insert into camp_test.maintenance_task(description, default_period,notes,added_date,removed_date)
|
|
|
52 |
select description, default_period,notes,added_date,removed_date from camp.maintenance_task;
|
|
|
53 |
|
|
|
54 |
|
|
|
55 |
/* now, get the maintenance schedules in */
|
|
|
56 |
insert into maintenance_schedule (device_id,maintenance_task_id,schedule,login_id,added_date,removed_date)
|
|
|
57 |
select deviceinfo.device_id,maintenance_task.maintenance_task_id,oldtasks.schedule,login.login_id,oldtasks.added_date,oldtasks.removed_date
|
|
|
58 |
from camp_test.client join camp_test.site using (client_id),
|
|
|
59 |
(select device_alias.alias name, device_alias.device_id device_id, device.site_id site_id from device_alias join device using (device_id) join site using (site_id)
|
|
|
60 |
union
|
|
|
61 |
select device.name name, device.device_id device_id, device.site_id site_id from device join device_type using (device_type_id) where device_type.show_as_system = 'Y'
|
|
|
62 |
) deviceinfo,
|
|
|
63 |
camp_test.login,
|
|
|
64 |
camp_test.maintenance_task,
|
|
|
65 |
(
|
|
|
66 |
select camp.device.name devicename,
|
|
|
67 |
camp.client.name clientname,
|
|
|
68 |
camp.maintenance_task.description task,
|
|
|
69 |
camp.device_maintenance_schedule.schedule,
|
|
|
70 |
camp.login.email loginname,
|
|
|
71 |
camp.device_maintenance_schedule.added_date,
|
|
|
72 |
camp.device_maintenance_schedule.removed_date
|
|
|
73 |
from camp.device_maintenance_schedule
|
|
|
74 |
join camp.maintenance_task using (maintenance_task_id)
|
|
|
75 |
join camp.login using (login_id)
|
|
|
76 |
join camp.device using (device_id)
|
|
|
77 |
join camp.site using (site_id)
|
|
|
78 |
join camp.client using (client_id)
|
|
|
79 |
) oldtasks
|
|
|
80 |
where camp_test.client.client_id = camp_test.site.client_id
|
|
|
81 |
and camp_test.site.site_id = deviceinfo.site_id
|
|
|
82 |
and deviceinfo.name = oldtasks.devicename
|
|
|
83 |
and camp_test.login.email = oldtasks.loginname
|
|
|
84 |
and camp_test.maintenance_task.description = oldtasks.task
|
|
|
85 |
;
|
|
|
86 |
|
|
|
87 |
/* finally, get the maintenance actually performed */
|
|
|
88 |
|
|
|
89 |
insert into camp_test.maintenance_performed(device_id,maintenance_task_id,maintenance_date,notes,login_id)
|
|
|
90 |
select deviceinfo.device_id,maintenance_task.maintenance_task_id,oldtasks.maintenance_date,oldtasks.notes,login.login_id
|
|
|
91 |
from camp_test.client,
|
|
|
92 |
camp_test.site,
|
|
|
93 |
(select device_alias.alias name, device_alias.device_id device_id, device.site_id site_id from device_alias join device using (device_id) join site using (site_id)
|
|
|
94 |
union
|
|
|
95 |
select device.name name, device.device_id device_id, device.site_id site_id from device join device_type using (device_type_id) where device_type.show_as_system = 'Y'
|
|
|
96 |
) deviceinfo,
|
|
|
97 |
camp_test.login,
|
|
|
98 |
camp_test.maintenance_task,
|
|
|
99 |
(
|
|
|
100 |
select camp.device.name devicename,
|
|
|
101 |
camp.client.name clientname,
|
|
|
102 |
camp.maintenance_task.description task,
|
|
|
103 |
camp.login.email loginname,
|
|
|
104 |
camp.maintenance_performed.maintenance_date maintenance_date,
|
|
|
105 |
camp.maintenance_performed.notes notes
|
|
|
106 |
from camp.maintenance_performed
|
|
|
107 |
join camp.maintenance_task using (maintenance_task_id)
|
|
|
108 |
join camp.login using (login_id)
|
|
|
109 |
join camp.device using (device_id)
|
|
|
110 |
join camp.site using (site_id)
|
|
|
111 |
join camp.client using (client_id)
|
|
|
112 |
) oldtasks
|
|
|
113 |
where camp_test.client.client_id = camp_test.site.client_id
|
|
|
114 |
and camp_test.site.site_id = deviceinfo.site_id
|
|
|
115 |
and deviceinfo.name = oldtasks.devicename
|
|
|
116 |
and camp_test.login.email = oldtasks.loginname
|
|
|
117 |
and camp_test.maintenance_task.description = oldtasks.task
|
|
|
118 |
;
|
|
|
119 |
|
|
|
120 |
select count(*) from camp_test.maintenance_schedule;
|
|
|
121 |
select count(*) from camp.device_maintenance_schedule;
|
|
|
122 |
select count(*) from camp_test.maintenance_performed;
|
|
|
123 |
select count(*) from camp.maintenance_performed;
|