Subversion Repositories computer_asset_manager_v2

Rev

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

Rev 58 Rev 59
Line 240... Line 240...
240 
   from
240 
   from
241 
      device_device_type
241 
      device_device_type
242 
      join device_type using (device_type_id )
242 
      join device_type using (device_type_id )
243 
   group by device_id
243 
   group by device_id
244 
   order by name;
 244 
   order by name;
- 
 
 245
- 
 
 246 
/*
- 
 
 247 
   View to show relationships between machines, ie the part_of scheme
- 
 
 248 
   where one device is actually a "part of" another. Used mainly with
- 
 
 249 
   virtualization to show a virtual machine is on a particular
- 
 
 250 
   hypervisor
- 
 
 251 
*/
- 
 
 252 
create or replace view view_device_device as
- 
 
 253 
   select
- 
 
 254 
      device_device_id,
- 
 
 255 
      device_device.device_id,
- 
 
 256 
      device.name device_name,
- 
 
 257 
      device_device.parent_id,
- 
 
 258 
      parent.name parent_name,
- 
 
 259 
      device_device.created,
- 
 
 260 
      device_device.removed
- 
 
 261 
   from
- 
 
 262 
      device
- 
 
 263 
      join device_device using (device_id)
- 
 
 264 
      join device parent on (device_device.parent_id = parent.device_id);
- 
 
 265
- 
 
 266 
/* link owners and locations together */
- 
 
 267 
create or replace view view_owner_location as
- 
 
 268 
   select distinct
- 
 
 269 
      owner.name owner,
- 
 
 270 
      owner.owner_id,
- 
 
 271 
      location.name location,
- 
 
 272 
      location.location_id,
- 
 
 273 
      owner_location.created,
- 
 
 274 
      owner_location.removed
- 
 
 275 
   from
- 
 
 276 
      owner
- 
 
 277 
      join owner_location using (owner_id)
- 
 
 278 
      join location using (location_id);
245 
 
 279 
 
246 
/*
 280 
/*
247 
   Mongo view that gets all the information together to display
 281 
   Mongo view that gets all the information together to display
248 
   device name, location, owner and type(s)
 282 
   device name, location, owner and type(s)
- 
 
 283 
   NOTE: we are limiting the "part of" to only currently active
- 
 
 284 
   relationships
249 
*/
 285 
*/
250 
 
 286 
 
251 
create or replace view view_device_location_owner_type as
 287 
create or replace view view_device_location_owner_type as
252 
   select
 288 
   select
253 
      device.device_id device_id,
 289 
      device.device_id device_id,
Line 263... Line 299...
263 
      location.removed location_removed,
 299 
      location.removed location_removed,
264 
      owner.owner_id owner_id,
 300 
      owner.owner_id owner_id,
265 
      owner.name owner,
 301 
      owner.name owner,
266 
      owner.created owner_created,
 302 
      owner.created owner_created,
267 
      owner.removed owner_removed,
 303 
      owner.removed owner_removed,
- 
 
 304 
      view_owner_location.owner location_owner,
- 
 
 305 
      view_owner_location.owner_id location_owner_id,
- 
 
 306 
      view_owner_location.created location_owner_created,
- 
 
 307 
      view_owner_location.removed location_owner_removed,
268 
      (
 308 
      (
269 
         owner.removed is null
 309 
         owner.removed is null
270 
         and location.removed is null
 310 
         and location.removed is null
271 
         and device.removed is null
 311 
         and device.removed is null
272 
      ) active
 312 
      ) active,
- 
 
 313 
      parent.parent_id,
- 
 
 314 
      parent.parent_name parent,
- 
 
 315 
      parent.created parent_created,
- 
 
 316 
      parent.removed parent_removed
273 
   from
 317 
   from
274 
      device
 318 
      device
275 
      join view_device_types using (device_id )
 319 
      join view_device_types using (device_id )
276 
      join location_device using (device_id)
 320 
      join location_device using (device_id)
277 
      join location using (location_id)
 321 
      join location using (location_id)
278 
      join owner_device using (device_id)
 322 
      join owner_device using (device_id)
279 
      join owner using (owner_id);
 323 
      join owner using (owner_id)
- 
 
 324 
      join view_owner_location using (owner_id,location_id)
- 
 
 325 
      left outer join view_device_device parent using (device_id)
- 
 
 326 
   where
- 
 
 327 
      parent.removed is null;
280 
 
 328 
 
281 
 
 329 
 
282 
insert into _system ( key_value, group_name, key_name ) values ( '0.1','database','version' )
330 
insert into _system ( key_value, group_name, key_name ) values ( '0.1','database','version' )
283 
   on duplicate key update key_value = '0.1';
 331 
   on duplicate key update key_value = '0.1';
284 
insert into _system ( key_value, group_name, key_name ) values ( '', 'program', 'motd' );
 332 
insert into _system ( key_value, group_name, key_name ) values ( '', 'program', 'motd' );