Rev 34 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
/*
copy camp data to camp2 database
Note: we have removed_date set to null so we don't copy anything
that is old
*/
/*
Monster table of client, site, device and device_type
*/
drop table if exists temp;
create table temp as
select
camp.device.device_id old_device_id,
camp.device.name device_name,
camp.device.serial device_serial,
camp.device.part_of,
date( camp.device.added_date ) device_added,
date( camp.device.removed_date ) device_removed,
camp.device_type.name device_type,
if( camp.device_type.show_as_system = 'Y', true, false ) show_as_system,
camp.site.site_id old_site_id,
camp.site.name site_name,
camp.client.name site_owner,
date( camp.site.added_date ) site_added,
date( camp.site.removed_date ) site_removed,
camp.client.client_id old_client_id,
camp.client.name client_name,
date(camp.client.added_date) client_added,
date( camp.client.removed_date ) client_removed
from
camp.device
join camp.site using (site_id)
join camp.client using (client_id)
join camp.device_type using (device_type_id)
where
device_type.show_as_system = 'Y'
and device.removed_date is null
and site.removed_date is null
and client.removed_date is null
;
/*
in the old system, there was no deliniation between site and client, so we created new 'sites' with 'NOC' at the end
so we could have ownership and locations. So, fivestar equipment located at the NOC would have been put at the fictitious
site 'fivestar NOC'. Modified original data so every NOC location has NOC in the name.
Following query puts them all in 'Colocation NOC' (Daily Data's site) without losing ownership information
*/
update temp a
join temp b
set
a.old_site_id = b.old_site_id,
a.site_name = b.site_name,
a.site_added = b.site_added,
a.site_owner = b.site_owner
where
b.site_name = 'Colocation NOC'
and a.site_name like '%NOC%' and a.site_name <> 'Colocation NOC';
/* we have some stuff at Lori Bryant's office also */
update temp a
join temp b
set
a.old_site_id = b.old_site_id,
a.site_name = b.site_name,
a.site_added = b.site_added,
a.site_owner = b.site_owner
where
b.site_name = 'Stemmons Towers'
and a.site_name like 'Lori Bryant''s Office';
/* and Lakewood Title */
update temp a
join temp b
set
a.old_site_id = b.old_site_id,
a.site_name = b.site_name,
a.site_added = b.site_added,
a.site_owner = b.site_owner
where
b.site_name = 'Matilda and Prospect'
and a.site_name like 'Lakewood Title Office';
/* populate client table */
truncate table client;
insert into client( name,created, removed)
select distinct
client_name,
client_added,
client_removed
from temp;
/* populate site */
truncate table site;
insert into site( name,created,removed)
select distinct
site_name,
site_added,
site_removed
from
temp;
/* get device_type directly from camp, but ignore anything not set as show_as_system */
truncate table device_type;
insert into device_type (name,show_as_system, created, removed)
select
name,
true,
date( added_date ),
date( removed_date )
from
camp.device_type
where
show_as_system = 'Y';
/* Now, we're ready to get some devices */
truncate table device;
insert into device( uuid,serial,name,device_type_id,created,removed)
select
null,
device_serial,
device_name,
device_type_id,
device_added,
device_removed
from
temp
join device_type on ( temp.device_type = device_type.name )
;
/* link a client owner to a site */
truncate table client_site;
insert into client_site ( client_id,site_id,created, removed )
select
client.client_id,
site.site_id,
oldTable.site_added,
oldTable.site_removed
from
(
select distinct
site_name,
site_owner,
site_added,
site_removed
from temp
) oldTable
join client on ( client.name = oldTable.site_owner )
join site on ( site.name = oldTable.site_name );
/* verify linkage with
select client.name,site.name from client join client_site using ( client_id ) join site using (site_id );
*/
/* link a client owner to a device */
truncate table client_device;
insert into client_device ( client_id,device_id,created, removed )
select
client.client_id,
device.device_id,
oldTable.device_added,
oldTable.device_removed
from
(
select
device_name,
client_name,
device_added,
device_removed
from temp
) oldTable
join client on ( client.name = oldTable.client_name )
join device on ( device.name = oldTable.device_name );
/* link a device to a site */
truncate table site_device;
insert into site_device ( site_id,device_id,created, removed )
select
site.site_id,
device.device_id,
oldTable.device_added,
oldTable.device_removed
from
(
select
device_name,
site_name,
device_added,
device_removed
from temp
) oldTable
join site on ( site.name = oldTable.site_name )
join device on ( device.name = oldTable.device_name );
/* link a device to a parent device */
truncate table device_device;
insert into device_device ( device_id,parent_id,created, removed )
select
device.device_id,
parent_device.device_id,
oldTable.device_added,
oldTable.device_removed
from
(
select
dev.device_name,
parent.device_name parent,
dev.device_added,
dev.device_removed
from temp dev join temp parent on ( dev.part_of = parent.old_device_id )
where dev.part_of is not null
) oldTable
join device on ( device.name = oldTable.device_name )
join device parent_device on ( parent_device.name = oldTable.parent );
/* following dumps all the data */
/*
select
site.name site,
site_own.name site_owner,
client.name device_owned_by,
device_type.name device_type,
device.name device,
parent.parent_name part_of
from
device
join device_type using (device_type_id)
join client_device using (device_id)
join client using (client_id)
join site_device using (device_id)
join site using (site_id )
join client_site using (site_id)
join client site_own on (client_site.client_id = site_own.client_id)
left outer join (
select
device.device_id device_id,
device.name device_name,
parent.device_id parent_id,
parent.name parent_name
from
device
join device_device using (device_id)
join device parent on (parent.device_id = device_device.parent_id)
) parent using ( device_id )
order by
client.name,
site.name,
device_type.name,
device.name
;
*/