Subversion Repositories computer_asset_manager_v2

Rev

Rev 66 | Blame | Last modification | View Log | Download | RSS feed

/*
   This is a helper script to import CAMP v1 to CAMP v2. There are some
   customizations for our purposes, but it is mainly generic.
*/

/*
   You MUST be logged into the correct database before running this
   connect camp2;
*/

/* This is used to map old ID's to new ones */
create or replace table 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 _users;
insert into _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 _users set isAdmin = 1 where login = 'admin';

/* Set up mapping between old and new users */
insert into mapping ( tablename,new_id,old_id )
   select 
      '_users',
      _users._user_id,
      camp.login.login_id
   from
      _users
      join camp.login on (_users.login = camp.login.email);


/* Update all user permissions to default */
truncate table  _users_permissions;
insert into _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 
            _users_permissions 
            join _users using (_user_id)
         );
         
update _users set restrictions = '[owner]
Vanduzen' where login = 'jbellah';

update _users set restrictions = '[owner]
Walder IP Law' where login = 'swalder';


/* *************************************
   Owners
*/

/* add owners */
truncate table owner;
insert into 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 mapping ( tablename,new_id,old_id )
   select 
      'owner',
      owner.owner_id,
      camp.client.client_id
   from
      owner
      join camp.client on (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 location_mapping (
   old_value varchar(64),
   new_value varchar(64),
   primary key (old_value,new_value)
);
insert into location_mapping(old_value,new_value) values ('NOC','Colocation NOC' );
insert into location_mapping(old_value,new_value) values ('Lori Bryant Office','Lori Bryant, CPA - Stemmons Towers');
insert into location_mapping(old_value,new_value) values ('DD Vanduzen','Vanduzen Dallas');
insert into location_mapping(old_value,new_value) values ('Lakewood Title Office','LWT Corp');
insert into location_mapping(old_value,new_value) values ('AppServe (Daily Data)','AppServe Technologies');
insert into 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 location;
insert into 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 mapping ( tablename,new_id,old_id )
   select 
      'location',
      location.location_id,
      camp.site.site_id
   from
      location
      join camp.site on (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 mapping (tablename,new_id,old_id)
   select
      'location',
      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,
            location_mapping
         where 
            camp.site.name like concat( '%',location_mapping.old_value, '%')
            and camp.site.name <> 'Colocation NOC'
         ) site2 on (location.name = site2.new_value );
         
/* clean up */
drop table location_mapping;

/* ***************************************
  Devices
*/

/* grab all that are marked as systems, no PCI cards */
truncate table device;
insert into 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 mapping ( tablename,new_id,old_id )
   select 
      'device',
      new.device_id,
      old.device_id
   from
      device new
      join camp.device old using (name);

/* ***************************************
  Devices
*/
truncate table device_type;
insert into device_type (name,created)
   select
      name,
      date(added_date)
   from
      camp.device_type
   where
      camp.device_type.show_as_system = 'Y';

insert into mapping( tablename, new_id, old_id )
   select
      'device_type',
      new.device_type_id,
      old.device_type_id
   from
      device_type new
      join camp.device_type old using (name)
   where
      old.show_as_system = 'Y';

/* *********************************************************
   Linkage Tables
*/

/* device_device */
truncate table device_device;
insert into device_device (device_id,parent_id,created)
   select
      mapping.new_id,
      parent.new_id,
      date(now())
   from
      camp.device
      join 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 mapping on (camp.device.device_id = mapping.old_id and 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 device_device_type;
insert into 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 mapping on (camp.device.device_id = mapping.old_id and mapping.tablename = 'device')
      join 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_types using (device_id);

*/

/* location_device */
truncate table location_device;
insert into location_device ( location_id,device_id,created )
   select
      map_type.new_id,
      mapping.new_id,
      date(camp.site.added_date)
   from
      camp.device
      join camp.site using (site_id)
      join mapping on (camp.device.device_id = mapping.old_id and mapping.tablename = 'device')
      join 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 )
   select
      map_type.new_id,
      mapping.new_id,
      date(camp.device.added_date)
   from
      camp.device
      join camp.site using (site_id)
      join camp.client using (client_id)
      join mapping on (camp.device.device_id = mapping.old_id and mapping.tablename = 'device')
      join 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 )
   select
      map_type.new_id,
      mapping.new_id,
      date(camp.site.added_date)
   from
      camp.site
      join camp.client using (client_id)
      join mapping on (camp.site.site_id = mapping.old_id and mapping.tablename = 'location')
      join 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 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;

*/