Rev 61 | 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.
*/
/* 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;
*/
/* 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;