Subversion Repositories computer_asset_manager_v2

Rev

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

Rev 37 Rev 56
Line 47... Line 47...
47 
   removed        date default null,
 47 
   removed        date default null,
48 
   index          (table_id,attribute_id),
 48 
   index          (table_id,attribute_id),
49 
   primary key    (attribute_device_id)
 49 
   primary key    (attribute_device_id)
50 
) comment 'attributes for devices';
 50 
) comment 'attributes for devices';
51 
 
 51 
 
52 
insert into attribute_category( name ) values ( 'client' ), ( 'site' ), ( 'device' );
 52 
insert into attribute_category( name ) values ( 'ownder' ), ( 'location' ), ( 'device' );
53 
 
 53 
 
54 
/* add one, default, attribute of 'name' for all three tables defined */
 54 
/* add one, default, attribute of 'name' for all three tables defined */
55 
insert into attribute( name,attribute_category_id )
 55 
insert into attribute( name,attribute_category_id )
56 
   select
 56 
   select
57 
      'name',
 57 
      'name',
Line 60... Line 60...
60 
      attribute_category;
 60 
      attribute_category;
61 
 
 61 
 
62 
/*
 62 
/*
63 
   some convenience views on the main three tables which might use these, device, site and client
 63 
   some convenience views on the main three tables which might use these, device, site and client
64 
*/
 64 
*/
65 
drop view if exists view_attribute_client;
 65 
drop view if exists view_attribute_owner;
66 
create view view_attribute_client as
 66 
create view view_attribute_client as
67 
   select
 67 
   select
68 
      client.client_id,
 68 
      client.client_id,
69 
      client.name client,
 69 
      client.name client,
70 
      attribute.name attribute,
 70 
      attribute.name attribute,
Line 75... Line 75...
75 
      attribute_value
 75 
      attribute_value
76 
      join attribute using (attribute_id)
 76 
      join attribute using (attribute_id)
77 
      join attribute_category using (attribute_category_id)
 77 
      join attribute_category using (attribute_category_id)
78 
      join client on ( attribute_value.table_id = client.client_id)
 78 
      join client on ( attribute_value.table_id = client.client_id)
79 
   where
 79 
   where
80 
      attribute_category.name = 'client';
 80 
      attribute_category.name = 'owner';
81 
 
 81 
 
82 
drop view if exists view_attribute_site;
 82 
drop view if exists view_attribute_location;
83 
create view view_attribute_site as
 83 
create view view_attribute_site as
84 
   select
 84 
   select
85 
      site.site_id,
 85 
      site.site_id,
86 
      site.name site,
 86 
      site.name site,
87 
      attribute.name attribute,
 87 
      attribute.name attribute,
Line 92... Line 92...
92 
      attribute_value
 92 
      attribute_value
93 
      join attribute using (attribute_id)
 93 
      join attribute using (attribute_id)
94 
      join attribute_category using (attribute_category_id)
 94 
      join attribute_category using (attribute_category_id)
95 
      join site on ( attribute_value.table_id = site.site_id)
 95 
      join site on ( attribute_value.table_id = site.site_id)
96 
   where
 96 
   where
97 
      attribute_category.name = 'site';
 97 
      attribute_category.name = 'location';
98 
 
 98 
 
99 
drop view if exists view_attribute_device;
 99 
drop view if exists view_attribute_device;
100 
create view view_attribute_device as
 100 
create view view_attribute_device as
101 
   select
 101 
   select
102 
      device.device_id,
 102 
      device.device_id,