Rev 73 | Blame | Last modification | View Log | Download | RSS feed
/*
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.entity_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.entity_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.entity_id = device.device_id)
where
attribute_value._base_class_id in (select _base_class_id from _base_class where class_name = 'Device');