Subversion Repositories computer_asset_manager_v2

/* 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;
*/