Subversion Repositories computer_asset_manager_v2

Rev

Go to most recent revision | 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
/*
9
   attribute_category allows us to categorize the attributes based on what they are used for.
10
   for example, allows us to have names out of client table, and names out of device table
11
   as two separate attributes.
12
*/
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' ,
27
   attribute_category_id int unsigned not null references attribute_category(attribute_category_id),
37 rodolico 28
   display_in_summary boolean comment 'if true, indicates it should be displayed when device/site/client screen is open',
34 rodolico 29
   added          date,
30
   removed        date default null,
31
   primary key    (attribute_id),
32
   index          (attribute_category_id)
33
) comment = 'stores attribute names (not values) for other tables';
34
 
35
/*
36
   stores the actual values.
37
   NOTE the key field table_id can reference different tables depending on the category of the
38
   attribute in question
39
*/
40
drop table if exists attribute_value;
41
create table attribute_value (
42
   attribute_device_id int unsigned not null auto_increment,
43
   attribute_id   int unsigned not null references attribute(attribute_id),
44
   table_id       int unsigned not null comment 'reference to arbitrary table',
45
   value          text null default null comment 'the actual value stored',
46
   added          date,
47
   removed        date default null,
48
   index          (table_id,attribute_id),
49
   primary key    (attribute_device_id)
50
) comment 'attributes for devices';
51
 
56 rodolico 52
insert into attribute_category( name ) values ( 'ownder' ), ( 'location' ), ( 'device' );
34 rodolico 53
 
54
/* add one, default, attribute of 'name' for all three tables defined */
55
insert into attribute( name,attribute_category_id )
56
   select
57
      'name',
58
      attribute_category.attribute_category_id
59
   from
60
      attribute_category;
61
 
62
/*
63
   some convenience views on the main three tables which might use these, device, site and client
64
*/
56 rodolico 65
drop view if exists view_attribute_owner;
34 rodolico 66
create view view_attribute_client as
67
   select
68
      client.client_id,
69
      client.name client,
70
      attribute.name attribute,
71
      attribute_value.value,
72
      attribute_value.added,
73
      attribute_value.removed
74
   from
75
      attribute_value
76
      join attribute using (attribute_id)
77
      join attribute_category using (attribute_category_id)
78
      join client on ( attribute_value.table_id = client.client_id)
79
   where
56 rodolico 80
      attribute_category.name = 'owner';
34 rodolico 81
 
56 rodolico 82
drop view if exists view_attribute_location;
34 rodolico 83
create view view_attribute_site as
84
   select
85
      site.site_id,
86
      site.name site,
87
      attribute.name attribute,
88
      attribute_value.value,
89
      attribute_value.added,
90
      attribute_value.removed
91
   from
92
      attribute_value
93
      join attribute using (attribute_id)
94
      join attribute_category using (attribute_category_id)
95
      join site on ( attribute_value.table_id = site.site_id)
96
   where
56 rodolico 97
      attribute_category.name = 'location';
34 rodolico 98
 
99
drop view if exists view_attribute_device;
100
create view view_attribute_device as
101
   select
102
      device.device_id,
103
      device.name device,
104
      attribute.name attribute,
105
      attribute_value.value,
106
      attribute_value.added,
107
      attribute_value.removed
108
   from
109
      attribute_value
110
      join attribute using (attribute_id)
111
      join attribute_category using (attribute_category_id)
112
      join device on ( attribute_value.table_id = device.device_id)
113
   where
114
      attribute_category.name = 'device';
115
 
116