Subversion Repositories computer_asset_manager_v2

/* 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;

/* 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 );

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

  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);