Subversion Repositories computer_asset_manager_v2

/*
   table structure for module attributes.
   Three tables; one to categorize attributes, one to hold the attribute names
   and one to link attribute to device with storage for a value
*/


/*
   Allows us to group attributes into different categories, for example
   hardware (disk size, cpu, etc...), financial (cost, vendor), network,
   etc
*/
drop table if exists attribute_category;
create table attribute_category (
   attribute_category_id int unsigned not null auto_increment,
   name           varchar(64) comment 'display name for category',
   primary key    ( attribute_category_id )
) comment 'categorizes attributes';

/*
   this will hold the actual names of the attributes, such as name, uuid, serial number, cpu information, etc...
*/
drop table if exists attribute;
create table attribute (
   attribute_id   int unsigned not null auto_increment,
   name           varchar(64) not null comment 'name for display' ,
   attribute_category_id int unsigned not null,
   multiples      bool default 0 comment 'if set, multiple instances of the same attribute are allowed',
   display_in_summary boolean comment 'if true, indicates it should be displayed when device/site/client screen is open',
   added          date,
   removed        date default null,
   primary key    (attribute_id)
) comment = 'stores attribute names (not values) for other tables';

/*
   stores the actual values.
   NOTE the key field table_id can reference different tables depending 
   on the _base_class_id of attribute in question
   
   We can get the first line of a text field with
   select substring_index( value, '\n',1 ) from attribute_value where ...
*/
drop table if exists attribute_value;
create table attribute_value (
   attribute_value_id int unsigned not null auto_increment,
   attribute_id   int unsigned not null references attribute (attribute_id),
   _base_class_id int unsigned not null references _base_class( _base_class_id),
   entity_id      int unsigned not null comment 'references ${_base_class}.{id}',
   value          text null default null comment 'the actual value stored',
   added          date,
   removed        date default null,
   index          (_base_class_id,entity_id),
   primary key    (attribute_value_id)
) comment 'attributes for entities (devices, locations, owners)';

/*
   some convenience views on the main three tables which might use these, device, site and client
*/
drop view if exists view_attribute_owner;
create view view_attribute_owner as
   select
      owner.owner_id,
      owner.name owner,
      attribute.name attribute,
      attribute_value.value,
      attribute_value.added,
      attribute_value.removed
   from
      attribute_value
      join attribute using (attribute_id)
      join attribute_category using (attribute_category_id)
      join owner on ( attribute_value._base_class_id = owner.owner_id)
   where
      attribute_value._base_class_id in (select _base_class_id from _base_class where class_name = 'Owner');

drop view if exists view_attribute_location;
create view view_attribute_location as
   select
      location.location_id,
      location.name location,
      attribute.name attribute,
      attribute_value.value,
      attribute_value.added,
      attribute_value.removed
   from
      attribute_value
      join attribute using (attribute_id)
      join attribute_category using (attribute_category_id)
      join location on ( attribute_value._base_class_id = location.location_id)
   where
      attribute_value._base_class_id in (select _base_class_id from _base_class where class_name = 'Location');

drop view if exists view_attribute_device;
create view view_attribute_device as
   select
      device.device_id,
      device.name device,
      attribute.name attribute,
      attribute_value.value,
      attribute_value.added,
      attribute_value.removed
   from
      attribute_value
      join attribute using (attribute_id)
      join attribute_category using (attribute_category_id)
      join device on ( attribute_value._base_class_id = device.device_id)
   where
      attribute_value._base_class_id in (select _base_class_id from _base_class where class_name = 'Device');