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


DEFINITELY needs to be re-thought

/*
   attribute_table allows us to categorize the attributes based on what they are used for.
   for example, allows us to have names out of client table, and names out of device table
   as two separate attributes.
*/
drop table if exists attribute_table;
create table attribute_table (
   attribute_table_id int unsigned not null auto_increment,
   name           varchar(64) comment 'display name for category',
   primary key    ( attribute_category_id )
) comment 'which table attribute is for';

/*
   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 auto_increment,
   attribute_table_id int unsigned not null references attribute_table(attribute_table_id),
   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),
   index          (attribute_table_id, attribute_category_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 category of the
   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_device_id int unsigned not null auto_increment,
   attribute_id   int unsigned not null references attribute(attribute_id),
   table_id       int unsigned not null comment 'reference to arbitrary table',
   value          text null default null comment 'the actual value stored',
   added          date,
   removed        date default null,
   index          (table_id,attribute_id),
   primary key    (attribute_device_id)
) comment 'attributes for devices';

insert into attribute_category( name ) values ( 'owner' ), ( 'location' ), ( 'device' );

/* add one, default, attribute of 'name' for all three tables defined */
insert into attribute( name,attribute_category_id )
   select
      'name',
      attribute_category.attribute_category_id
   from
      attribute_category;

/*
   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_client as
   select
      client.client_id,
      client.name client,
      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 client on ( attribute_value.table_id = client.client_id)
   where
      attribute_category.name = 'owner';

drop view if exists view_attribute_location;
create view view_attribute_site as
   select
      site.site_id,
      site.name site,
      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 site on ( attribute_value.table_id = site.site_id)
   where
      attribute_category.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.table_id = device.device_id)
   where
      attribute_category.name = 'device';