Subversion Repositories computer_asset_manager_v2

/*

   This is a helper script to import CAMP v1 to CAMP v2. There are some

   customizations for our purposes, but it is mainly generic.

*/

update camp2._users set isAdmin = 1 where login = 'admin';

/* clean up */

drop table camp2.location_mapping;

/* ***************************************

  Devices

*/

truncate table camp2.device_type;

insert into camp2.device_type (name,created,removed)

   select

      name,

      date(added_date),

      date(removed_date)

   from

      camp.device_type

   where

      camp.device_type.show_as_system = 'Y';

insert into camp2.mapping( tablename, new_id, old_id )

   select

      'device_type',

      camp2.device_type.device_type_id,

      camp.device_type.device_type_id

   from

      camp2.device_type

      join camp.device_type using (name)

   where

      camp.device_type.show_as_system = 'Y';

/* *********************************************************

   Linkage Tables

*/

/* device_device */

truncate table camp2.device_device;

insert into camp2.device_device (device_id,parent_id,created,removed)

   select

      camp2.mapping.new_id,

      parent.new_id,

      date(now()),

      null

   from

      camp.device

      join camp2.mapping on (camp.device.device_id = mapping.old_id and mapping.tablename = 'device' )

      join (

         select

            device.device_id,

            mapping.new_id

         from

            camp.device

            join camp2.mapping on (camp.device.device_id = mapping.old_id and camp2.mapping.tablename = 'device')

         ) parent on ( camp.device.part_of = parent.device_id )

   where

      camp.device.device_type_id in (select device_type_id from camp.device_type where show_as_system = 'Y')

      and camp.device.part_of is not null

      and camp.device.removed_date is null;

/* use following to verify this is working

select 

   device.name device,

   parent.name parent

from 

   device 

   join device_device using (device_id) 

   join device parent on (device_device.parent_id = parent.device_id) 

where 

   device.removed is null

order by parent.name,device.name;

*/

/* device_device_type */

truncate camp2.device_device_type;

insert into camp2.device_device_type (device_id,device_type_id)

   select

      mapping.new_id,

      map_type.new_id

   from

      camp.device

      join camp.device_type on (camp.device.device_type_id = camp.device_type.device_type_id)

      join camp2.mapping on (camp.device.device_id = mapping.old_id and mapping.tablename = 'device')

      join camp2.mapping map_type on (camp.device_type.device_type_id = map_type.old_id and map_type.tablename = 'device_type' )

;      

/* verify with the following query 

select device.name,device_types from device join view_device_device_types using (device_id);

*/

/* location_device */

truncate table location_device;

insert into location_device ( location_id,device_id,created,removed )

   select

      map_type.new_id,

      mapping.new_id,

      date(camp.site.added_date),

      date(camp.site.removed_date)

   from

      camp.device

      join camp.site using (site_id)

      join camp2.mapping on (camp.device.device_id = mapping.old_id and mapping.tablename = 'device')

      join camp2.mapping map_type on (camp.site.site_id = map_type.old_id and map_type.tablename = 'location' )

;      

/* Verify with the following query 

select 

   device.name,

   location.name

from 

   device 

   join location_device using (device_id) 

   join location using (location_id);

*/

/* owner_device */

truncate table owner_device;

insert into owner_device ( owner_id,device_id,created,removed )

   select

      map_type.new_id,

      mapping.new_id,

      date(camp.device.added_date),

      date(camp.device.removed_date)

   from

      camp.device

      join camp.site using (site_id)

      join camp.client using (client_id)

      join camp2.mapping on (camp.device.device_id = mapping.old_id and mapping.tablename = 'device')

      join camp2.mapping map_type on (camp.client.client_id = map_type.old_id and map_type.tablename = 'owner' )

;      

/* Verify with the following query

select owner.name owner, device.name device from owner join owner_device using (owner_id) join device using (device_id);

*/

/* owner_location */

truncate table owner_location;

insert into owner_location ( owner_id,location_id,created,removed )

   select

      map_type.new_id,

      mapping.new_id,

      date(camp.site.added_date),

      date(camp.site.removed_date)

   from

      camp.site

      join camp.client using (client_id)

      join camp2.mapping on (camp.site.site_id = mapping.old_id and mapping.tablename = 'location')

      join camp2.mapping map_type on (camp.client.client_id = map_type.old_id and map_type.tablename = 'owner' )

   where

      camp.site.name in (select name from camp2.location)

;

/* Verify with one of the following queries, or, simply open the app

select owner.name owner, location.name location from owner join owner_location using (owner_id) join location using (location_id);

select * from view_device_location_owner_type limit 10;

*/

/* Set a UUID for everything that doesn't have one */

update owner set uuid = uuid() where uuid is null;

update location set uuid = uuid() where uuid is null;

update device set uuid = uuid() where uuid is null;