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.

*/

/* This is used to map old ID's to new ones */

create or replace table camp2.mapping (

         tablename varchar(32) not null,

         old_id    int unsigned not null,

         new_id    int unsigned not null,

         primary key (tablename,old_id,new_id)

      );

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

    Users

*/

/* grab all users in old table and insert here with the "L" password */

truncate table camp2._users;

insert into camp2._users (login,password,email,isAdmin,enabled,created,removed)

   select

      if (instr(email,'@'), left(email,instr(email,'@')-1), email),

      '$2y$10$GWsjPDZWzCoNuD1lC34PDeyqnjSKI.QAevtRnxpjHpkMIZJJNvK8m',

      if (instr(email,'@'), email, concat(email, '@dailydata.net' )),

      0,

      isnull(removed_date),

      date(added_date),

      date( removed_date )

   from

      camp.login;

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

/* Set up mapping between old and new users */

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

   select 

      '_users',

      camp2._users._user_id,

      camp.login.login_id

   from

      camp2._users

      join camp.login on (camp2._users.login = camp.login.email);

/* Update all user permissions to default */

truncate table  camp2._users_permissions;

insert into camp2._users_permissions (_user_id,_permission_id, value )

   select 

      _user_id,

      _permission_id,

      1

   from 

      _permissions 

      join _users

   where 

      _permissions.default_value = 1

      and _users._user_id not in (

         select 

            _users._user_id 

         from 

            camp2._users_permissions 

            join camp2._users using (_user_id)

         );

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

   Owners

*/

/* add owners */

truncate table camp2.owner;

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

   select 

      name,

      date( added_date ),

      date( removed_date)

   from 

      camp.client;

/* Set up mapping between old and new owners */

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

   select 

      'owner',

      camp2.owner.owner_id,

      camp.client.client_id

   from

      camp2.owner

      join camp.client on (camp2.owner.name = camp.client.name);

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

   Locations. This is a little different.

   We have several things in the old database which were put in there

   because location ownership was not different from device ownership

   Here, we're building a table to break them apart

*/

create or replace table camp2.location_mapping (

   old_value varchar(64),

   new_value varchar(64),

   primary key (old_value,new_value)

);

insert into camp2.location_mapping(old_value,new_value) values ('NOC','Colocation NOC' );

insert into camp2.location_mapping(old_value,new_value) values ('Lori Bryant Office','Lori Bryant, CPA - Stemmons Towers');

insert into camp2.location_mapping(old_value,new_value) values ('DD Vanduzen','Vanduzen Dallas');

insert into camp2.location_mapping(old_value,new_value) values ('Lakewood Title Office','LWT Corp');

insert into camp2.location_mapping(old_value,new_value) values ('AppServe (Daily Data)','AppServe Technologies');

insert into camp2.location_mapping(old_value,new_value) values ('Staffmasters (Daily Data)','Staffmasters - Stemmons Towers');

/*

   Now, we copy the ones which are NOT found in the above over directly

*/

truncate table camp2.location;

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

   select

      name,

      date(added_date),

      date(removed_date)

   from

      camp.site

   where

      camp.site.site_id not in

         (

            select 

               camp.site.site_id

            from 

               camp.site,

               location_mapping

            where 

               camp.site.name like concat( '%',location_mapping.old_value, '%')

               and camp.site.name <> 'Colocation NOC'

         );

/* Set up mapping between old and new locations, except for the aliases */

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

   select 

      'location',

      camp2.location.location_id,

      camp.site.site_id

   from

      camp2.location

      join camp.site on (camp2.location.name = camp.site.name);

/* 

   Finally, we get the stuff we ignored in location_mapping and work 

   with it. We don't add those sites, we just do a mapping for them

*/

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

   select

      'location',

      camp2.location.location_id,

      site2.site_id

   from

      location

      join (

         select 

            camp.site.site_id,

            camp.site.name,

            location_mapping.old_value,

            location_mapping.new_value

         from 

            camp.site,

            camp2.location_mapping

         where 

            camp.site.name like concat( '%',location_mapping.old_value, '%')

            and camp.site.name <> 'Colocation NOC'

         ) site2 on (camp2.location.name = site2.new_value );

/* clean up */

drop table camp2.location_mapping;

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

  Devices

*/

/* grab all that are marked as systems, no PCI cards */

truncate table camp2.device;

insert into camp2.device (uuid,serial,name,created,removed)

   select 

      if ( length( serial ) = 36, serial, null ) uuid,

      if ( length( serial ) < 36, serial, null ) serial,

      device.name,

      date( device.added_date ),

      date( device.removed_date)

   from

      camp.device

      join camp.device_type using ( device_type_id )

   where 

      device_type.show_as_system = 'Y';

/* Set up mapping between old and new device */

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

   select 

      'device',

      camp2.device.device_id,

      camp.device.device_id

   from

      camp2.device

      join camp.device on (camp2.device.name = camp.device.name);

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

  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;

*/