| 38 |
rodolico |
1 |
insert into camp2.client( client_id,name,notes,internal_notes,added,removed )
|
|
|
2 |
select client_id,name,notes,internal_notes,added_date,removed_date from camp.client;
|
| 22 |
rodolico |
3 |
update camp2.client set notes = null where length( notes ) = 0;
|
|
|
4 |
update camp2.client set internal_notes = null where length( internal_notes ) = 0;
|
|
|
5 |
|
|
|
6 |
/* load site */
|
| 38 |
rodolico |
7 |
insert into camp2.site( site_id,name,notes,added,removed )
|
| 22 |
rodolico |
8 |
select site_id,name,notes,added_date,removed_date
|
|
|
9 |
from camp.site;
|
|
|
10 |
|
|
|
11 |
/* load device types, convert show_as_system to flags */
|
| 38 |
rodolico |
12 |
insert into camp2.device_type( device_type_id,name,flags,added,removed)
|
| 22 |
rodolico |
13 |
select device_type_id,name,if(show_as_system='Y',1,0),added_date,removed_date
|
|
|
14 |
from camp.device_type;
|
|
|
15 |
|
|
|
16 |
/* load only actual devices into device */
|
| 38 |
rodolico |
17 |
insert into camp2.device( device_id,device_type_id,name,notes,added,removed )
|
| 22 |
rodolico |
18 |
select device_id,device_type_id,name,notes,added_date,removed_date
|
|
|
19 |
from camp.device
|
|
|
20 |
where camp.device.device_type_id in (
|
|
|
21 |
select device_type_id
|
|
|
22 |
from camp.device_type
|
|
|
23 |
where camp.device_type.show_as_system = 'Y'
|
|
|
24 |
);
|
|
|
25 |
|
|
|
26 |
/* now, populate joining tables site_device and client_device and client_site */
|
| 38 |
rodolico |
27 |
insert into camp2.site_device (site_id,device_id,added,removed)
|
| 22 |
rodolico |
28 |
select site_id,device_id,added_date,removed_date
|
|
|
29 |
from camp.device
|
|
|
30 |
where device_type_id in ( select device_type_id from camp.device_type where show_as_system = 'Y');
|
|
|
31 |
|
| 38 |
rodolico |
32 |
insert into camp2.client_device (client_id,device_id,added,removed)
|
| 22 |
rodolico |
33 |
select client_id,device_id,device.added_date,device.removed_date
|
|
|
34 |
from camp.device join camp.site using (site_id)
|
|
|
35 |
where device_type_id in ( select device_type_id from camp.device_type where show_as_system = 'Y');
|
|
|
36 |
|
| 38 |
rodolico |
37 |
insert into camp2.client_site ( client_id,site_id,added,removed )
|
| 22 |
rodolico |
38 |
select client_id,site_id,added_date,removed_date
|
|
|
39 |
from camp.site;
|
|
|
40 |
|
|
|
41 |
|
|
|
42 |
|
|
|
43 |
/* and fill out device_relationship */
|
|
|
44 |
|
| 38 |
rodolico |
45 |
insert into camp2.device_device( device_id, parent_id, added, removed )
|
|
|
46 |
select camp2.device.device_id, camp.device.part_of, camp2.device.added,camp2.device.removed
|
| 22 |
rodolico |
47 |
from camp2.device join camp.device using (device_id)
|
|
|
48 |
where camp.device.part_of is not null;
|