Subversion Repositories computer_asset_manager_v2

/*

   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 owner, location, 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

*/

;

/*

  uuid and serial number are stored A) in the table and B) in the attrib_device table.

  We are doing a kludge. If the value is 36 characters long, we assume it is a UUID. If 

  it is not, we assume it is a serial number

*/

alter table temp add device_uuid varchar(36);

update temp a join camp.attrib_device b on (a.old_device_id = b.device_id and b.attrib_id = 23 )

  set

     a.device_uuid = b.value

   where a.device_uuid is null;

update temp a join camp.attrib_device b on (a.old_device_id = b.device_id and b.attrib_id = 3 )

  set

     a.device_serial = b.value

   where a.device_serial is null

      and length( b.value ) <> 36;

update temp a join camp.attrib_device b on (a.old_device_id = b.device_id and b.attrib_id = 3 )

  set

     a.device_uuid = b.value

   where a.device_uuid is null

      and length( b.value ) = 36;

update temp set device_uuid = device_serial where length( device_serial ) = 36 and device_uuid is null;

update temp set device_serial = null where lower( device_serial ) = lower( device_uuid);

update temp set device_uuid = trim(device_uuid);

update temp set device_serial = trim(device_serial);

update temp set device_uuid = null where length( device_uuid ) = 0;

update temp set device_serial = null where length( device_serial ) = 0;

/*

   in the old system, there was no deliniation between location and client, so we created new 'locations' 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

   location '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 location) 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 owner table */

truncate table owner;

insert into owner( name,created, removed)

   select distinct

      client_name,

      client_added,

      client_removed

   from temp;

/* populate location */

truncate table location;

insert into location( 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,is_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,created,removed)

   select distinct

      device_uuid,

      left(device_serial,32),

      device_name,

      device_added,

      device_removed

   from

      temp

      join device_type on ( temp.device_type = device_type.name );

/* get all the device types set up */

truncate table device_device_type;

insert into device_device_type (device_id,device_type_id )

   select 

      device.device_id,

      device_type_id

   from 

      temp 

      join device on (temp.device_name = device.name) 

      join device_type on (temp.device_type = device_type.name);

/* link a owner owner to a location */

truncate table owner_location;

insert into owner_location ( owner_id,location_id,created, removed )

   select 

      owner.owner_id,

      location.location_id,

      oldTable.site_added,

      oldTable.site_removed

   from 

      ( 

         select distinct 

            site_name,

            site_owner,

            site_added, 

            site_removed 

         from temp 

         ) oldTable

      join owner on ( owner.name = oldTable.site_owner )

      join location on ( location.name = oldTable.site_name );

/* verify linkage with 

   select owner.name,location.name from owner join owner_location using ( owner_id ) join location using (location_id );

*/

/* link a client owner to a device */

truncate table owner_device;

insert into owner_device ( owner_id,device_id,created, removed )

   select 

      owner.owner_id,

      device.device_id,

      oldTable.device_added,

      oldTable.device_removed

   from 

      ( 

         select

            device_name,

            client_name,

            device_added, 

            device_removed 

         from temp 

         ) oldTable

      join owner on ( owner.name = oldTable.client_name )

      join device on ( device.name = oldTable.device_name );

/* link a device to a location */

truncate table location_device;

insert into location_device ( location_id,device_id,created, removed )

   select 

      location.location_id,

      device.device_id,

      oldTable.device_added,

      oldTable.device_removed

   from 

      ( 

         select

            device_name,

            site_name,

            device_added, 

            device_removed 

         from temp 

         ) oldTable

      join location on ( location.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 );

-- get rid of the temp table

drop table if exists temp;