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 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

;

*/

/* now, load the attributes */

/*

   this is a big kludge, but since it is only run once, I did not bother optimizing it.

   basically, get all the attributes which are used by devices which are systems

   and add them to the attribute table

*/

insert into attribute ( name,attribute_category_id,added,removed)

select

   camp.attrib.name,

   attribute_category.attribute_category_id,

   date(camp.attrib.added_date),

   date(camp.attrib.removed_date)

from

   camp.attrib

   join camp2.attribute_category

where

   attribute_category.name = 'device'

   and camp.attrib.attrib_id in (

      select distinct

         attrib_id

      from 

         camp.device_attrib

      where 

         camp.device_attrib.device_id in

         (

            select 

               device_id 

            from 

               camp.device 

               join camp.device_type using (device_type_id) 

            where 

               device_type.show_as_system = 'Y'

         )

   )

   and camp.attrib.name not in (

      select 

         attribute.name 

      from 

         attribute 

         join attribute_category using (attribute_category_id) 

      where 

         attribute_category.name = 'device'

   );

/*

   now, load the values for the devices. NOTE: we should clean up data coming in since sysinfo

   brings in values which are only slightly different for memory and cpu speed.

*/

insert into attribute_value( attribute_id,table_id,value,added,removed)

select 

   camp2.attribute.attribute_id,

   camp.device_attrib.device_id,

   camp.device_attrib.value,

   date( camp.device_attrib.added_date ) added,

   date( camp.device_attrib.removed_date ) removed

from

   camp.device_attrib

   join camp.attrib on (camp.device_attrib.attrib_id = camp.attrib.attrib_id)

   join camp2.attribute on (camp.attrib.name = camp2.attribute.name)

   join camp2.temp on ( camp.device_attrib.device_id = camp2.temp.old_device_id )

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

;

/*

   select * from view_attribute_device order by device,attribute,added limit 10;

*/