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 40
Line 28... Line 28...
28 
create table _system (
 28 
create table _system (
29 
  _system_id      int unsigned not null auto_increment,
 29 
  _system_id      int unsigned not null auto_increment,
30 
  group_name      varchar(45) not null comment 'Group name for matching',
 30 
  group_name      varchar(45) not null comment 'Group name for matching',
31 
  key_name        varchar(45) not null comment 'key name for matching',
 31 
  key_name        varchar(45) not null comment 'key name for matching',
32 
  key_value       varchar(45) not null comment 'value for key_name',
 32 
  key_value       varchar(45) not null comment 'value for key_name',
- 
 
 33 
  unique key      unique_group_name( group_name,key_name ),
33 
  primary key     (_system_id)
 34 
  primary key     (_system_id)
34 
)
 35 
)
35 
comment = 'Stores internal system information like ini file';
 36 
comment = 'Stores internal system information like ini file';
36 
 
 37 
 
37 
/*
 38 
/*
Line 86... Line 87...
86 
   removed        date comment 'date record was removed',
 87 
   removed        date comment 'date record was removed',
87 
   primary key    (device_type_id)
 88 
   primary key    (device_type_id)
88 
) comment 'simple child table to determine the type of device we have';
 89 
) comment 'simple child table to determine the type of device we have';
89
90
90 
/*
 91 
/*
91 
   holds very basic information on a device such as its type, location,
 92 
   holds very basic information on a device such as its name and a unique id.
92 
   and owner. This is the main table for the database, and each device
 93 
   This is the main table for the database, and each device
93 
   should be uniquely identified. We will allow name to be modified
 94 
   should be uniquely identified. We will allow name to be modified
94 
   randomly, however.
 95 
   randomly, however.
95 
 
 96 
 
96 
   Internally, we find this device based on device_id, but for remote
 97 
   Internally, we find this device based on device_id, but for remote
97 
   systems, we use the combined uuid and serial to uniquely identify
 98 
   systems, we use the combined uuid and serial to uniquely identify
Line 100... Line 101...
100 
   and it is suggested to use manufacturer:serial or something like that
 101 
   and it is suggested to use manufacturer:serial or something like that
101 
*/
 102 
*/
102 
drop table if exists device;
 103 
drop table if exists device;
103 
create table device (
 104 
create table device (
104 
   device_id      int unsigned not null auto_increment,
 105 
   device_id      int unsigned not null auto_increment,
105 
   uuid           varchar(32) comment 'unique id of this device, normally uuid',
 106 
   uuid           varchar(36) comment 'unique id of this device, normally uuid',
106 
   serial         varchar(32) comment 'serial number of this device, if we have it',
 107 
   serial         varchar(32) comment 'serial number of this device, if we have it',
107 
   name           varchar(64) comment 'name of device',
 108 
   name           varchar(64) comment 'name of device',
108 
   device_type_id int unsigned not null references device_type( device_type_id ),
 - 
 
109 
   created        date comment 'date record was created',
 109 
   created        date comment 'date record was created',
110 
   removed        date comment 'date record was removed',
 110 
   removed        date comment 'date record was removed',
111 
/*   unique key     unique_uuid( uuid, serial ), */
 111 
   unique key     unique_uuid( uuid, serial ),
112 
   primary key    (device_id)
 112 
   primary key    (device_id)
113 
) comment 'holds individual devices';
 113 
) comment 'holds individual devices';
114 
 
 114 
 
- 
 
 115 
/*
- 
 
 116 
   Many to many join table allowing devices to have multiple device
- 
 
 117 
   types. NOTE: I'm using device_id and device_type_id as the composite
- 
 
 118 
   primary key, so no duplicates, and we don't need an 'id' column
- 
 
 119 
*/
- 
 
 120 
 
- 
 
 121 
drop table if exists device_device_type;
- 
 
 122 
create table device_device_type (
- 
 
 123 
   device_id      int unsigned not null references device( device_id ),
- 
 
 124 
   device_type_id int unsigned not null references device_type( device_type_id ),
- 
 
 125 
   primary key (device_id,device_type_id)
- 
 
 126 
) comment 'many to many join for device and device_type tables';
115 
 
 127 
 
116 
/*
 128 
/*
117 
   Set ownership of a site. These records are not deleted, but by
 129 
   Set ownership of a site. These records are not deleted, but by
118 
   setting field removed to non-null value, then creating a new record,
 130 
   setting field removed to non-null value, then creating a new record,
119 
   we can track ownership of sites.
 131 
   we can track ownership of sites.
Line 177... Line 189...
177 
   index          device_device( device_id, parent_id ),
 189 
   index          device_device( device_id, parent_id ),
178 
   primary key    ( device_device_id )
 190 
   primary key    ( device_device_id )
179 
) comment 'links a device to another device';
 191 
) comment 'links a device to another device';
180 
 
 192 
 
181 
 
 193 
 
- 
 
 194 
/*
- 
 
 195 
   Some views so we don't have to reinvent the wheel when we're trying
- 
 
 196 
   to grab some data
- 
 
 197 
*/
- 
 
 198 
 
- 
 
 199 
/*
- 
 
 200 
  a simple view that concats the values in device_device_type for
- 
 
 201 
  display. Since mySQL will not allow subqueries in views, required
- 
 
 202 
  to have this information in view_device_site_client_type
- 
 
 203 
*/
- 
 
 204 
create view view_device_types as
- 
 
 205 
   select
- 
 
 206 
      device_id,
- 
 
 207 
      group_concat(distinct device_type.name) as device_types
- 
 
 208 
   from
- 
 
 209 
      device_device_type
- 
 
 210 
      join device_type using (device_type_id )
- 
 
 211 
   group by device_id
- 
 
 212 
   order by name;
- 
 
 213 
 
- 
 
 214 
/*
- 
 
 215 
   Mongo view that gets all the information together to display
- 
 
 216 
   device name, location, owner and type(s)
- 
 
 217 
*/
- 
 
 218 
 
182 
drop view if exists view_device_site_client;
 219 
drop view if exists view_device_site_client_type;
183 
create view view_device_site_client as
 220 
create view view_device_site_client_type as
184 
   select
 221 
   select
- 
 
 222 
      device.device_id device_id,
185 
      device.device_id,
 223 
      device.uuid uuid,
- 
 
 224 
      device.serial serial,
186 
      device.name device,
 225 
      device.name device,
187 
      device.created device_created,
 226 
      device.created device_created,
188 
      device.removed device_removed,
 227 
      device.removed device_removed,
189 
      device_type.device_type_id,
 228 
      view_device_types.device_types,
190 
      device_type.name device_type,
 - 
 
191 
      site.site_id,
 229 
      site.site_id site_id,
192 
      site.name site,
 230 
      site.name site,
193 
      site.created site_created,
 231 
      site.created site_created,
194 
      site.removed site_removed,
 232 
      site.removed site_removed,
195 
      client.client_id,
 233 
      client.client_id client_id,
196 
      client.name client,
 234 
      client.name client,
197 
      client.created client_created,
 235 
      client.created client_created,
198 
      client.removed client_removed
 236 
      client.removed client_removed
199 
   from
 237 
   from
200 
      device
 238 
      device
- 
 
 239 
      join view_device_types using (device_id )
201 
      join site_device using (device_id)
 240 
      join site_device using (device_id)
202 
      join site using (site_id)
 241 
      join site using (site_id)
203 
      join client_device using (device_id)
 242 
      join client_device using (device_id)
204 
      join client using (client_id)
 243 
      join client using (client_id);
205 
      join device_type using (device_type_id);
 - 
 
206
244 
 
207 
 
 245 
 
208 
insert into _system ( key_value, group_name, key_name ) values ( '0.1','database','version' );
 246 
insert into _system ( key_value, group_name, key_name ) values ( '0.1','database','version' )
- 
 
 247 
   on duplicate key update key_value = '0.1';