Subversion Repositories computer_asset_manager_v2

Rev

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

Rev 61 Rev 63
Line 87... Line 87...
87 
*/
 87 
*/
88 
drop table if exists owner;
 88 
drop table if exists owner;
89 
create table owner (
 89 
create table owner (
90 
   owner_id       int unsigned not null auto_increment,
 90 
   owner_id       int unsigned not null auto_increment,
91 
   name           varchar(64) comment 'name of owner',
 91 
   name           varchar(64) comment 'name of owner',
- 
 
 92 
   uuid           varchar(36) comment 'unique id of this owner, normally uuid',
92 
   created        date comment 'date record was created',
 93 
   created        date comment 'date record was created',
93 
   removed        date comment 'date record was removed',
 94 
   removed        date comment 'date record was removed',
94 
   primary key    (owner_id)
 95 
   primary key    (owner_id)
95 
) comment 'hold owner information';
 96 
) comment 'hold owner information';
96 
 
 97 
 
Line 99... Line 100...
99 
*/
 100 
*/
100 
drop table if exists location;
 101 
drop table if exists location;
101 
create table location (
 102 
create table location (
102 
   location_id    int unsigned not null auto_increment,
 103 
   location_id    int unsigned not null auto_increment,
103 
   name           varchar(64) comment 'name of location',
 104 
   name           varchar(64) comment 'name of location',
- 
 
 105 
   uuid           varchar(36) comment 'unique id of this location, normally uuid',
104 
   created        date comment 'date record was created',
 106 
   created        date comment 'date record was created',
105 
   removed        date comment 'date record was removed',
 107 
   removed        date comment 'date record was removed',
106 
   primary key    (location_id)
 108 
   primary key    (location_id)
107 
) comment 'hold location information';
 109 
) comment 'hold location information';
108 
 
 110 
 
Line 233... Line 235...
233 
  to have this information in view_device_location_owner_type
 235 
  to have this information in view_device_location_owner_type
234 
*/
 236 
*/
235 
create or replace view view_device_types as
 237 
create or replace view view_device_types as
236 
   select
238 
   select
237 
      device_id,
 239 
      device_id,
238 
      group_concat(distinct device_type.name) as device_types
240 
      group_concat(distinct device_type.name) as device_types,
- 
 
 241 
      group_concat( distinct device_type.device_type_id) as device_type_ids
239 
   from
242 
   from
240 
      device_device_type
243 
      device_device_type
241 
      join device_type using (device_type_id )
244 
      join device_type using (device_type_id )
242 
   group by device_id
245 
   group by device_id
243 
   order by name;
 246 
   order by name;
Line 294... Line 297...
294 
      view_device_types.device_types,
 297 
      view_device_types.device_types,
295 
      location.location_id location_id,
 298 
      location.location_id location_id,
296 
      location.name location,
 299 
      location.name location,
297 
      location.created location_created,
 300 
      location.created location_created,
298 
      location.removed location_removed,
 301 
      location.removed location_removed,
299 
      owner.owner_id owner_id,
 302 
      dev_owner.owner_id owner_id,
300 
      owner.name owner,
 303 
      dev_owner.name owner,
301 
      owner.created owner_created,
 304 
      dev_owner.created owner_created,
302 
      owner.removed owner_removed,
 305 
      dev_owner.removed owner_removed,
303 
      view_owner_location.owner location_owner,
 306 
      loc_owner.name location_owner,
304 
      view_owner_location.owner_id location_owner_id,
 307 
      loc_owner.owner_id location_owner_id,
305 
      view_owner_location.created location_owner_created,
 308 
      loc_owner.created location_owner_created,
306 
      view_owner_location.removed location_owner_removed,
 309 
      loc_owner.removed location_owner_removed,
307 
      (
 - 
 
308 
         owner.removed is null
 - 
 
309 
         and location.removed is null
 - 
 
310 
         and device.removed is null
 - 
 
311 
      ) active,
 - 
 
312 
      parent.parent_id,
 310 
      parent.parent_id,
313 
      parent.parent_name parent,
 311 
      parent.parent_name parent,
314 
      parent.created parent_created,
 312 
      parent.created parent_created,
315 
      parent.removed parent_removed
 313 
      parent.removed parent_removed
316 
   from
 314 
   from
317 
      device
 315 
      device
318 
      join view_device_types using (device_id )
 316 
      join view_device_types using (device_id )
- 
 
 317 
      join owner_device using (device_id)
- 
 
 318 
      join owner dev_owner on (owner_device.owner_id = dev_owner.owner_id)
319 
      join location_device using (device_id)
 319 
      join location_device using (device_id)
320 
      join location using (location_id)
 320 
      join location using (location_id)
321 
      join owner_device using (device_id)
 321 
      join owner_location on (location.location_id = owner_location.location_id)
322 
      join owner using (owner_id)
 - 
 
323 
      join view_owner_location using (owner_id,location_id)
 322 
      join owner loc_owner on (loc_owner.owner_id = owner_location.owner_id)
324 
      left outer join view_device_device parent using (device_id)
 323 
      left outer join view_device_device parent using (device_id)
325 
   where
 324 
   where
326 
      parent.removed is null;
 325 
      parent.removed is null;
327 
 
 326 
 
328 
 
 327