Subversion Repositories computer_asset_manager_v2

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
34 rodolico 1 
/*
2 
   table structure for module attributes.
3 
   Three tables; one to categorize attributes, one to hold the attribute names
4 
   and one to link attribute to device with storage for a value
5 
*/
6 
 
7 
 
8 
/*
70 rodolico 9 
   Allows us to group attributes into different categories, for example
10 
   hardware (disk size, cpu, etc...), financial (cost, vendor), network,
11 
   etc
12 
*/
34 rodolico 13 
drop table if exists attribute_category;
14 
create table attribute_category (
15 
   attribute_category_id int unsigned not null auto_increment,
16 
   name           varchar(64) comment 'display name for category',
17 
   primary key    ( attribute_category_id )
18 
) comment 'categorizes attributes';
19 
 
20 
/*
21 
   this will hold the actual names of the attributes, such as name, uuid, serial number, cpu information, etc...
22 
*/
23 
drop table if exists attribute;
24 
create table attribute (
25 
   attribute_id   int unsigned not null auto_increment,
26 
   name           varchar(64) not null comment 'name for display' ,
73 rodolico 27 
   attribute_category_id int unsigned not null,
28 
   multiples      bool default 0 comment 'if set, multiple instances of the same attribute are allowed',
37 rodolico 29 
   display_in_summary boolean comment 'if true, indicates it should be displayed when device/site/client screen is open',
34 rodolico 30 
   added          date,
31 
   removed        date default null,
73 rodolico 32 
   primary key    (attribute_id)
34 rodolico 33 
) comment = 'stores attribute names (not values) for other tables';
34 
 
35 
/*
36 
   stores the actual values.
73 rodolico 37 
   NOTE the key field table_id can reference different tables depending
38 
   on the _base_class_id of attribute in question
70 rodolico 39 
 
40 
   We can get the first line of a text field with
41 
   select substring_index( value, '\n',1 ) from attribute_value where ...
34 rodolico 42 
*/
43 
drop table if exists attribute_value;
44 
create table attribute_value (
73 rodolico 45 
   attribute_value_id int unsigned not null auto_increment,
46 
   attribute_id   int unsigned not null references attribute (attribute_id),
47 
   _base_class_id int unsigned not null references _base_class( _base_class_id),
48 
   entity_id      int unsigned not null comment 'references ${_base_class}.{id}',
34 rodolico 49 
   value          text null default null comment 'the actual value stored',
50 
   added          date,
51 
   removed        date default null,
73 rodolico 52 
   index          (_base_class_id,entity_id),
53 
   primary key    (attribute_value_id)
54 
) comment 'attributes for entities (devices, locations, owners)';
34 rodolico 55 
 
56 
/*
57 
   some convenience views on the main three tables which might use these, device, site and client
58 
*/
56 rodolico 59 
drop view if exists view_attribute_owner;
73 rodolico 60 
create view view_attribute_owner as
34 rodolico 61 
   select
73 rodolico 62 
      owner.owner_id,
63 
      owner.name owner,
34 rodolico 64 
      attribute.name attribute,
65 
      attribute_value.value,
66 
      attribute_value.added,
67 
      attribute_value.removed
68 
   from
69 
      attribute_value
70 
      join attribute using (attribute_id)
71 
      join attribute_category using (attribute_category_id)
73 rodolico 72 
      join owner on ( attribute_value._base_class_id = owner.owner_id)
34 rodolico 73 
   where
73 rodolico 74 
      attribute_value._base_class_id in (select _base_class_id from _base_class where class_name = 'Owner');
34 rodolico 75 
 
56 rodolico 76 
drop view if exists view_attribute_location;
73 rodolico 77 
create view view_attribute_location as
34 rodolico 78 
   select
73 rodolico 79 
      location.location_id,
80 
      location.name location,
34 rodolico 81 
      attribute.name attribute,
82 
      attribute_value.value,
83 
      attribute_value.added,
84 
      attribute_value.removed
85 
   from
86 
      attribute_value
87 
      join attribute using (attribute_id)
88 
      join attribute_category using (attribute_category_id)
73 rodolico 89 
      join location on ( attribute_value._base_class_id = location.location_id)
34 rodolico 90 
   where
73 rodolico 91 
      attribute_value._base_class_id in (select _base_class_id from _base_class where class_name = 'Location');
34 rodolico 92 
 
93 
drop view if exists view_attribute_device;
94 
create view view_attribute_device as
95 
   select
96 
      device.device_id,
97 
      device.name device,
98 
      attribute.name attribute,
99 
      attribute_value.value,
100 
      attribute_value.added,
101 
      attribute_value.removed
102 
   from
103 
      attribute_value
104 
      join attribute using (attribute_id)
105 
      join attribute_category using (attribute_category_id)
73 rodolico 106 
      join device on ( attribute_value._base_class_id = device.device_id)
34 rodolico 107 
   where
73 rodolico 108 
      attribute_value._base_class_id in (select _base_class_id from _base_class where class_name = 'Device');
34 rodolico 109 
 
110