WebSVN – computer_asset_manager_v2 – Diff – /head/installer/camp2.sql

    setting field removed to non-null value, then creating a new record,
    we can track ownership of locations.
 */
 drop table if exists owner_location;
 create table owner_location (
-   owner_location_id int unsigned not null auto_increment,
    owner_id      int unsigned not null references owner( owner_id ),
    location_id        int unsigned not null references location( location_id ),
    created        date default current_timestamp comment 'date record was created',
    removed        date comment 'date record was removed',
-   index          location_device ( owner_id,location_id ),
-   primary key    (owner_location_id)
+   unique key    (owner_id,location_id, removed)
 ) comment 'links ownership of a location to a owner';
 /*
    Set location of a device. These records are not deleted, but by
    setting field removed to non-null value, then creating a new record,
    we can track movement of devices.
 */
 drop table if exists location_device;
 create table location_device (
-   location_device_id int unsigned not null auto_increment,
    location_id        int unsigned not null references location( location_id ),
    device_id      int unsigned not null references device( device_id ),
    created        date default current_timestamp comment 'date record was created',
    removed        date comment 'date record was removed',
-   index          location_device ( location_id,device_id ),
-   primary key    (location_device_id)
+   unique key    (location_id,device_id, removed)
 ) comment 'links a device to its location';
 /*
    Set ownership of a device. These records are not deleted, but by
    setting field removed to non-null value, then creating a new record,
    we can track ownership of devices.
 */
 drop table if exists owner_device;
 create table owner_device (
-   owner_device_id  int unsigned not null auto_increment,
    owner_id      int unsigned not null references owner( owner_id ),
    device_id      int unsigned not null references device( device_id ),
    created        date default current_timestamp comment 'date record was created',
-   removed        date comment 'date record was removed',
+   removed        date default null comment 'date record was removed',
-   index          owner_device( owner_id, device_id ),
-   primary key    ( owner_device_id )
+   unique key    ( owner_id, device_id, removed )
 ) comment 'links a device to its owner';
 /*
    There can be a parent/child relationship with devices. For example, a virtual
    resides on a DOM0, so the parent_id of a DOMU is the device_id of the DOM0.
    or, a printer can be attached to a computer, so the printers parent is the
    device_id of the computer it is attached to.
 */
 drop table if exists device_device;
 create table device_device (
-   device_device_id int unsigned not null auto_increment,
    device_id      int unsigned not null references device( device_id ),
    parent_id      int unsigned not null references device( device_id ),
    created        date default current_timestamp comment 'date record was created',
-   removed        date comment 'date record was removed',
+   removed        date default null comment 'date record was removed',
-   index          device_device( device_id, parent_id ),
-   primary key    ( device_device_id )
+   unique key    ( device_id, parent_id, removed )
 ) comment 'links a device to another device';
 /*
    Some views so we don't have to reinvent the wheel when we're trying
    virtualization to show a virtual machine is on a particular
    hypervisor
 */
 create or replace view view_device_device as
    select
-      device_device_id,
       device_device.device_id,
       device.name device_name,
       device_device.parent_id,
       parent.name parent_name,
       device_device.created,
       device_device.removed
    from
       device
       join device_device using (device_id)
-      join device parent on (device_device.parent_id = parent.device_id);
+      join device parent on (device_device.parent_id = parent.device_id)
+   where
+      device_device.removed is null;
+/*
+   View combines view_device_types, device, and the parent relationship
+   (device_device) to give us all the information about one simple device
+*/
+create or replace view view_device as
+   select
+      device.device_id,
+      device.uuid,
+      device.serial,
+      device.name device,
+      device.created device_created,
+      device.removed device_removed,
+      view_device_types.device_types,
+      view_device_types.device_type_ids,
+      view_device_device.parent_id,
+      view_device_device.parent_name parent,
+      view_device_device.created parent_added,
+      view_device_device.removed parent_removed
+   from
+      device
+      join view_device_types using (device_id)
+      left outer join view_device_device using (device_id);
+/*
-/* link owners and locations together */
+   link owners and locations together for current connection only
+   Note that even if an owner doesn't have a location, it will still
+   show up here with a null location
+*/
 create or replace view view_owner_location as
    select distinct
       owner.name owner,
       owner.owner_id,
       location.name location,
       location.location_id,
       owner_location.created,
       owner_location.removed
    from
       owner
-      join owner_location using (owner_id)
+      left outer join owner_location using (owner_id)
-      join location using (location_id);
+      left outer join location using (location_id)
+   where
+      owner_location.removed is null;
+/*
+   link location to device in such a way as location will still show up
+   if it has no devices, ie left outer join
+   will not display historical where location_device is not null (ie, moved)
+*/
+create or replace view view_location_device as
+   select
+      location.location_id,
+      location.name location,
+      location.created location_created,
+      location.removed location_removed,
+      view_device.device_id,
+      view_device.device device,
+      view_device.device_created,
+      view_device.device_removed
+   from
+      location
+      left outer join location_device using (location_id)
+      left outer join view_device using (device_id)
+   where
+      location_device.removed is null;
+/*
+   link owner to device in such a way as owner will still show up
+   if it has no devices, ie left outer join
+*/
+create or replace view view_owner_device as
+   select
+      owner.owner_id,
+      owner.name owner,
+      owner.created owner_created,
+      owner.removed owner_removed,
+      view_device.device_id,
+      view_device.device,
+      view_device.device_created,
+      view_device.device_removed
+   from
+      owner
+      left outer join owner_device using (owner_id)
+      left outer join view_device using (device_id)
+   where
+      owner_device.removed is null;
 /*
    Mongo view that gets all the information together to display
    device name, location, owner and type(s)
    NOTE: we are limiting the "part of" to only currently active
    relationships
 */
 create or replace view view_device_location_owner_type as
    select
-      device.device_id device_id,
+      view_owner_device.device_id device_id,
-      device.uuid uuid,
-      device.serial serial,
-      device.name device,
+      view_owner_device.device device,
-      device.created device_created,
+      view_owner_device.device_created,
-      device.removed device_removed,
+      view_owner_device.device_removed,
-      view_device_types.device_types,
+      view_owner_device.owner_id owner_id,
-      location.location_id location_id,
-      location.name location,
+      view_owner_device.owner,
-      location.created location_created,
+      view_owner_device.owner_created,
-      location.removed location_removed,
+      view_owner_device.owner_removed,
-      dev_owner.owner_id owner_id,
+      view_location_device.location_id location_id,
-      dev_owner.name owner,
+      view_location_device.location,
-      dev_owner.created owner_created,
+      view_location_device.location_created,
-      dev_owner.removed owner_removed,
+      view_location_device.location_removed,
-      loc_owner.name location_owner,
+      view_owner_location.owner location_owner,
-      loc_owner.owner_id location_owner_id,
+      view_owner_location.owner_id location_owner_id,
-      loc_owner.created location_owner_created,
-      loc_owner.removed location_owner_removed,
-      parent.parent_id,
+      view_device.parent_id,
-      parent.parent_name parent,
+      view_device.parent,
-      parent.created parent_created,
-      parent.removed parent_removed
+      view_device.device_types
    from
-      device
+      view_owner_device
-      join view_device_types using (device_id )
+      join view_location_device using (device_id)
-      join owner_device using (device_id)
+      join view_device using (device_id)
-      join owner dev_owner on (owner_device.owner_id = dev_owner.owner_id)
-      join location_device using (device_id)
-      join location using (location_id)
+      join view_owner_location using (location_id);
-      join owner_location on (location.location_id = owner_location.location_id)
-      join owner loc_owner on (loc_owner.owner_id = owner_location.owner_id)
-      left outer join view_device_device parent using (device_id)
-   where
-      parent.removed is null
-      and owner_device.removed is null
-      and location_device.removed is null
-      and owner_location.removed is null;
 insert into _system ( key_value, group_name, key_name ) values ( '0.1','database','version' )
    on duplicate key update key_value = '0.1';
 insert into _system ( key_value, group_name, key_name ) values ( '', 'program', 'motd' );

Subversion Repositories computer_asset_manager_v2

(root)/head/installer/camp2.sql @ 66 – Rev 64 → 66