Subversion Repositories computer_asset_manager_v2

Rev

Rev 70 | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

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