Subversion Repositories computer_asset_manager_v2

Rev

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

Rev 56 Rev 70
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 
 
8 
/*
 10 
/*
9 
   attribute_category allows us to categorize the attributes based on what they are used for.
 11 
   attribute_table 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
 12 
   for example, allows us to have names out of client table, and names out of device table
11 
   as two separate attributes.
 13 
   as two separate attributes.
12 
*/
 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 
/*
- 
 
 23 
   Allows us to group attributes into different categories, for example
- 
 
 24 
   hardware (disk size, cpu, etc...), financial (cost, vendor), network,
- 
 
 25 
   etc
- 
 
 26 
*/
13 
drop table if exists attribute_category;
 27 
drop table if exists attribute_category;
14 
create table attribute_category (
 28 
create table attribute_category (
15 
   attribute_category_id int unsigned not null auto_increment,
 29 
   attribute_category_id int unsigned not null auto_increment,
16 
   name           varchar(64) comment 'display name for category',
 30 
   name           varchar(64) comment 'display name for category',
17 
   primary key    ( attribute_category_id )
 31 
   primary key    ( attribute_category_id )
Line 22... Line 36...
22 
*/
 36 
*/
23 
drop table if exists attribute;
 37 
drop table if exists attribute;
24 
create table attribute (
 38 
create table attribute (
25 
   attribute_id   int unsigned not null auto_increment,
 39 
   attribute_id   int unsigned not null auto_increment,
26 
   name           varchar(64) not null comment 'name for display' ,
 40 
   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 references attribute_category(attribute_category_id),
 42 
   attribute_table_id int unsigned not null references attribute_table(attribute_table_id),
28 
   display_in_summary boolean comment 'if true, indicates it should be displayed when device/site/client screen is open',
 43 
   display_in_summary boolean comment 'if true, indicates it should be displayed when device/site/client screen is open',
29 
   added          date,
 44 
   added          date,
30 
   removed        date default null,
 45 
   removed        date default null,
31 
   primary key    (attribute_id),
 46 
   primary key    (attribute_id),
32 
   index          (attribute_category_id)
 47 
   index          (attribute_table_id, attribute_category_id)
33 
) comment = 'stores attribute names (not values) for other tables';
 48 
) comment = 'stores attribute names (not values) for other tables';
34 
 
 49 
 
35 
/*
 50 
/*
36 
   stores the actual values.
 51 
   stores the actual values.
37 
   NOTE the key field table_id can reference different tables depending on the category of the
 52 
   NOTE the key field table_id can reference different tables depending on the category of the
38 
   attribute in question
 53 
   attribute in question
- 
 
 54
- 
 
 55 
   We can get the first line of a text field with
- 
 
 56 
   select substring_index( value, '\n',1 ) from attribute_value where ...
39 
*/
 57 
*/
40 
drop table if exists attribute_value;
 58 
drop table if exists attribute_value;
41 
create table attribute_value (
 59 
create table attribute_value (
42 
   attribute_device_id int unsigned not null auto_increment,
 60 
   attribute_device_id int unsigned not null auto_increment,
43 
   attribute_id   int unsigned not null references attribute(attribute_id),
 61 
   attribute_id   int unsigned not null references attribute(attribute_id),
Line 47... Line 65...
47 
   removed        date default null,
 65 
   removed        date default null,
48 
   index          (table_id,attribute_id),
 66 
   index          (table_id,attribute_id),
49 
   primary key    (attribute_device_id)
 67 
   primary key    (attribute_device_id)
50 
) comment 'attributes for devices';
 68 
) comment 'attributes for devices';
51 
 
 69 
 
52 
insert into attribute_category( name ) values ( 'ownder' ), ( 'location' ), ( 'device' );
 70 
insert into attribute_category( name ) values ( 'owner' ), ( 'location' ), ( 'device' );
53 
 
 71 
 
54 
/* add one, default, attribute of 'name' for all three tables defined */
 72 
/* add one, default, attribute of 'name' for all three tables defined */
55 
insert into attribute( name,attribute_category_id )
 73 
insert into attribute( name,attribute_category_id )
56 
   select
 74 
   select
57 
      'name',
 75 
      'name',