Subversion Repositories computer_asset_manager_v2

Rev

Rev 66 | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 66 Rev 75
Line 1... Line 1...
1 
/*
 1 
/*
2 
   This is a helper script to import CAMP v1 to CAMP v2. There are some
 2 
   This is a helper script to import CAMP v1 to CAMP v2. There are some
3 
   customizations for our purposes, but it is mainly generic.
 3 
   customizations for our purposes, but it is mainly generic.
4 
*/
 4 
*/
5 
 
 5 
 
- 
 
 6 
/*
- 
 
 7 
   You MUST be logged into the correct database before running this
- 
 
 8 
   connect camp2;
- 
 
 9 
*/
- 
 
 10 
 
6 
/* This is used to map old ID's to new ones */
 11 
/* This is used to map old ID's to new ones */
7 
create or replace table camp2.mapping (
 12 
create or replace table mapping (
8 
         tablename varchar(32) not null,
 13 
         tablename varchar(32) not null,
9 
         old_id    int unsigned not null,
 14 
         old_id    int unsigned not null,
10 
         new_id    int unsigned not null,
 15 
         new_id    int unsigned not null,
11 
         primary key (tablename,old_id,new_id)
 16 
         primary key (tablename,old_id,new_id)
12 
      );
 17 
      );
Line 14... Line 19...
14 
/* ***************************************
 19 
/* ***************************************
15 
    Users
 20 
    Users
16 
*/
 21 
*/
17 
 
 22 
 
18 
/* grab all users in old table and insert here with the "L" password */
 23 
/* grab all users in old table and insert here with the "L" password */
19 
truncate table camp2._users;
 24 
truncate table _users;
20 
insert into camp2._users (login,password,email,isAdmin,enabled,created,removed)
 25 
insert into _users (login,password,email,isAdmin,enabled,created,removed)
21 
   select
 26 
   select
22 
      if (instr(email,'@'), left(email,instr(email,'@')-1), email),
 27 
      if (instr(email,'@'), left(email,instr(email,'@')-1), email),
23 
      '$2y$10$GWsjPDZWzCoNuD1lC34PDeyqnjSKI.QAevtRnxpjHpkMIZJJNvK8m',
 28 
      '$2y$10$GWsjPDZWzCoNuD1lC34PDeyqnjSKI.QAevtRnxpjHpkMIZJJNvK8m',
24 
      if (instr(email,'@'), email, concat(email, '@dailydata.net' )),
 29 
      if (instr(email,'@'), email, concat(email, '@dailydata.net' )),
25 
      0,
 30 
      0,
Line 27... Line 32...
27 
      date(added_date),
 32 
      date(added_date),
28 
      date( removed_date )
 33 
      date( removed_date )
29 
   from
 34 
   from
30 
      camp.login;
 35 
      camp.login;
31 
 
 36 
 
32 
update camp2._users set isAdmin = 1 where login = 'admin';
 37 
update _users set isAdmin = 1 where login = 'admin';
33 
 
 38 
 
34 
/* Set up mapping between old and new users */
 39 
/* Set up mapping between old and new users */
35 
insert into camp2.mapping ( tablename,new_id,old_id )
 40 
insert into mapping ( tablename,new_id,old_id )
36 
   select
41 
   select
37 
      '_users',
 42 
      '_users',
38 
      camp2._users._user_id,
 43 
      _users._user_id,
39 
      camp.login.login_id
 44 
      camp.login.login_id
40 
   from
 45 
   from
41 
      camp2._users
 46 
      _users
42 
      join camp.login on (camp2._users.login = camp.login.email);
 47 
      join camp.login on (_users.login = camp.login.email);
43 
 
 48 
 
44 
 
 49 
 
45 
/* Update all user permissions to default */
 50 
/* Update all user permissions to default */
46 
truncate table  camp2._users_permissions;
 51 
truncate table  _users_permissions;
47 
insert into camp2._users_permissions (_user_id,_permission_id, value )
 52 
insert into _users_permissions (_user_id,_permission_id, value )
48 
   select
53 
   select
49 
      _user_id,
 54 
      _user_id,
50 
      _permission_id,
 55 
      _permission_id,
51 
      1
 56 
      1
52 
   from
57 
   from
Line 56... Line 61...
56 
      _permissions.default_value = 1
 61 
      _permissions.default_value = 1
57 
      and _users._user_id not in (
 62 
      and _users._user_id not in (
58 
         select
63 
         select
59 
            _users._user_id
64 
            _users._user_id
60 
         from
65 
         from
61 
            camp2._users_permissions
66 
            _users_permissions
62 
            join camp2._users using (_user_id)
 67 
            join _users using (_user_id)
63 
         );
 68 
         );
64
69
65 
update _users set restrictions = '[owner]
 70 
update _users set restrictions = '[owner]
66 
Vanduzen' where login = 'jbellah';
 71 
Vanduzen' where login = 'jbellah';
67 
 
 72 
 
Line 72... Line 77...
72 
/* *************************************
 77 
/* *************************************
73 
   Owners
 78 
   Owners
74 
*/
 79 
*/
75 
 
 80 
 
76 
/* add owners */
 81 
/* add owners */
77 
truncate table camp2.owner;
 82 
truncate table owner;
78 
insert into camp2.owner (name,created,removed)
 83 
insert into owner (name,created,removed)
79 
   select
84 
   select
80 
      name,
 85 
      name,
81 
      date( added_date ),
 86 
      date( added_date ),
82 
      date( removed_date)
 87 
      date( removed_date)
83 
   from
88 
   from
84 
      camp.client;
 89 
      camp.client;
85 
 
 90 
 
86 
/* Set up mapping between old and new owners */
 91 
/* Set up mapping between old and new owners */
87 
insert into camp2.mapping ( tablename,new_id,old_id )
 92 
insert into mapping ( tablename,new_id,old_id )
88 
   select
93 
   select
89 
      'owner',
 94 
      'owner',
90 
      camp2.owner.owner_id,
 95 
      owner.owner_id,
91 
      camp.client.client_id
 96 
      camp.client.client_id
92 
   from
 97 
   from
93 
      camp2.owner
 98 
      owner
94 
      join camp.client on (camp2.owner.name = camp.client.name);
 99 
      join camp.client on (owner.name = camp.client.name);
95 
 
 100 
 
96 
/* *************************************************
101 
/* *************************************************
97 
   Locations. This is a little different.
 102 
   Locations. This is a little different.
98 
   We have several things in the old database which were put in there
 103 
   We have several things in the old database which were put in there
99 
   because location ownership was not different from device ownership
 104 
   because location ownership was not different from device ownership
100 
   Here, we're building a table to break them apart
 105 
   Here, we're building a table to break them apart
101 
*/
 106 
*/
102 
create or replace table camp2.location_mapping (
 107 
create or replace table location_mapping (
103 
   old_value varchar(64),
 108 
   old_value varchar(64),
104 
   new_value varchar(64),
 109 
   new_value varchar(64),
105 
   primary key (old_value,new_value)
 110 
   primary key (old_value,new_value)
106 
);
 111 
);
107 
insert into camp2.location_mapping(old_value,new_value) values ('NOC','Colocation NOC' );
 112 
insert into location_mapping(old_value,new_value) values ('NOC','Colocation NOC' );
108 
insert into camp2.location_mapping(old_value,new_value) values ('Lori Bryant Office','Lori Bryant, CPA - Stemmons Towers');
 113 
insert into location_mapping(old_value,new_value) values ('Lori Bryant Office','Lori Bryant, CPA - Stemmons Towers');
109 
insert into camp2.location_mapping(old_value,new_value) values ('DD Vanduzen','Vanduzen Dallas');
 114 
insert into location_mapping(old_value,new_value) values ('DD Vanduzen','Vanduzen Dallas');
110 
insert into camp2.location_mapping(old_value,new_value) values ('Lakewood Title Office','LWT Corp');
 115 
insert into location_mapping(old_value,new_value) values ('Lakewood Title Office','LWT Corp');
111 
insert into camp2.location_mapping(old_value,new_value) values ('AppServe (Daily Data)','AppServe Technologies');
 116 
insert into location_mapping(old_value,new_value) values ('AppServe (Daily Data)','AppServe Technologies');
112 
insert into camp2.location_mapping(old_value,new_value) values ('Staffmasters (Daily Data)','Staffmasters - Stemmons Towers');
 117 
insert into location_mapping(old_value,new_value) values ('Staffmasters (Daily Data)','Staffmasters - Stemmons Towers');
113 
 
 118 
 
114 
 
 119 
 
115 
/*
 120 
/*
116 
   Now, we copy the ones which are NOT found in the above over directly
 121 
   Now, we copy the ones which are NOT found in the above over directly
117 
*/
 122 
*/
118 
truncate table camp2.location;
 123 
truncate table location;
119 
insert into camp2.location (name,created,removed)
 124 
insert into location (name,created,removed)
120 
   select
 125 
   select
121 
      name,
 126 
      name,
122 
      date(added_date),
 127 
      date(added_date),
123 
      date(removed_date)
 128 
      date(removed_date)
124 
   from
 129 
   from
Line 135... Line 140...
135 
               camp.site.name like concat( '%',location_mapping.old_value, '%')
 140 
               camp.site.name like concat( '%',location_mapping.old_value, '%')
136 
               and camp.site.name <> 'Colocation NOC'
 141 
               and camp.site.name <> 'Colocation NOC'
137 
         );
 142 
         );
138 
 
 143 
 
139 
/* Set up mapping between old and new locations, except for the aliases */
 144 
/* Set up mapping between old and new locations, except for the aliases */
140 
insert into camp2.mapping ( tablename,new_id,old_id )
 145 
insert into mapping ( tablename,new_id,old_id )
141 
   select
146 
   select
142 
      'location',
 147 
      'location',
143 
      camp2.location.location_id,
 148 
      location.location_id,
144 
      camp.site.site_id
 149 
      camp.site.site_id
145 
   from
 150 
   from
146 
      camp2.location
 151 
      location
147 
      join camp.site on (camp2.location.name = camp.site.name);
 152 
      join camp.site on (location.name = camp.site.name);
148 
 
 153 
 
149 
 
 154 
 
150 
/*
155 
/*
151 
   Finally, we get the stuff we ignored in location_mapping and work
156 
   Finally, we get the stuff we ignored in location_mapping and work
152 
   with it. We don't add those sites, we just do a mapping for them
 157 
   with it. We don't add those sites, we just do a mapping for them
153 
*/
 158 
*/
154 
insert into camp2.mapping (tablename,new_id,old_id)
 159 
insert into mapping (tablename,new_id,old_id)
155 
   select
 160 
   select
156 
      'location',
 161 
      'location',
157 
      camp2.location.location_id,
 162 
      location.location_id,
158 
      site2.site_id
 163 
      site2.site_id
159 
   from
 164 
   from
160 
      location
 165 
      location
161 
      join (
 166 
      join (
162 
         select
167 
         select
Line 164... Line 169...
164 
            camp.site.name,
 169 
            camp.site.name,
165 
            location_mapping.old_value,
 170 
            location_mapping.old_value,
166 
            location_mapping.new_value
 171 
            location_mapping.new_value
167 
         from
172 
         from
168 
            camp.site,
 173 
            camp.site,
169 
            camp2.location_mapping
 174 
            location_mapping
170 
         where
175 
         where
171 
            camp.site.name like concat( '%',location_mapping.old_value, '%')
 176 
            camp.site.name like concat( '%',location_mapping.old_value, '%')
172 
            and camp.site.name <> 'Colocation NOC'
 177 
            and camp.site.name <> 'Colocation NOC'
173 
         ) site2 on (camp2.location.name = site2.new_value );
 178 
         ) site2 on (location.name = site2.new_value );
174
179
175 
/* clean up */
 180 
/* clean up */
176 
drop table camp2.location_mapping;
 181 
drop table location_mapping;
177 
 
 182 
 
178 
/* ***************************************
 183 
/* ***************************************
179 
  Devices
 184 
  Devices
180 
*/
 185 
*/
181 
 
 186 
 
182 
/* grab all that are marked as systems, no PCI cards */
 187 
/* grab all that are marked as systems, no PCI cards */
183 
truncate table camp2.device;
 188 
truncate table device;
184 
insert into camp2.device (uuid,serial,name,created,removed)
 189 
insert into device (uuid,serial,name,created,removed)
185 
   select
190 
   select
186 
      if ( length( serial ) = 36, serial, null ) uuid,
 191 
      if ( length( serial ) = 36, serial, null ) uuid,
187 
      if ( length( serial ) < 36, serial, null ) serial,
 192 
      if ( length( serial ) < 36, serial, null ) serial,
188 
      device.name,
 193 
      device.name,
189 
      date( device.added_date ),
 194 
      date( device.added_date ),
Line 193... Line 198...
193 
      join camp.device_type using ( device_type_id )
 198 
      join camp.device_type using ( device_type_id )
194 
   where
199 
   where
195 
      device_type.show_as_system = 'Y';
 200 
      device_type.show_as_system = 'Y';
196 
 
 201 
 
197 
/* Set up mapping between old and new device */
 202 
/* Set up mapping between old and new device */
198 
insert into camp2.mapping ( tablename,new_id,old_id )
 203 
insert into mapping ( tablename,new_id,old_id )
199 
   select
204 
   select
200 
      'device',
 205 
      'device',
201 
      camp2.device.device_id,
 206 
      new.device_id,
202 
      camp.device.device_id
 207 
      old.device_id
203 
   from
 208 
   from
204 
      camp2.device
 209 
      device new
205 
      join camp.device on (camp2.device.name = camp.device.name);
 210 
      join camp.device old using (name);
206 
 
 211 
 
207 
/* ***************************************
 212 
/* ***************************************
208 
  Devices
 213 
  Devices
209 
*/
 214 
*/
210 
truncate table camp2.device_type;
 215 
truncate table device_type;
211 
insert into camp2.device_type (name,created)
 216 
insert into device_type (name,created)
212 
   select
 217 
   select
213 
      name,
 218 
      name,
214 
      date(added_date)
 219 
      date(added_date)
215 
   from
 220 
   from
216 
      camp.device_type
 221 
      camp.device_type
217 
   where
 222 
   where
218 
      camp.device_type.show_as_system = 'Y';
 223 
      camp.device_type.show_as_system = 'Y';
219 
 
 224 
 
220 
insert into camp2.mapping( tablename, new_id, old_id )
 225 
insert into mapping( tablename, new_id, old_id )
221 
   select
 226 
   select
222 
      'device_type',
 227 
      'device_type',
223 
      camp2.device_type.device_type_id,
 228 
      new.device_type_id,
224 
      camp.device_type.device_type_id
 229 
      old.device_type_id
225 
   from
 230 
   from
226 
      camp2.device_type
 231 
      device_type new
227 
      join camp.device_type using (name)
 232 
      join camp.device_type old using (name)
228 
   where
 233 
   where
229 
      camp.device_type.show_as_system = 'Y';
 234 
      old.show_as_system = 'Y';
230 
 
 235 
 
231 
/* *********************************************************
 236 
/* *********************************************************
232 
   Linkage Tables
 237 
   Linkage Tables
233 
*/
 238 
*/
234 
 
 239 
 
235 
/* device_device */
 240 
/* device_device */
236 
truncate table camp2.device_device;
 241 
truncate table device_device;
237 
insert into camp2.device_device (device_id,parent_id,created)
 242 
insert into device_device (device_id,parent_id,created)
238 
   select
 243 
   select
239 
      camp2.mapping.new_id,
 244 
      mapping.new_id,
240 
      parent.new_id,
 245 
      parent.new_id,
241 
      date(now())
 246 
      date(now())
242 
   from
 247 
   from
243 
      camp.device
 248 
      camp.device
244 
      join camp2.mapping on (camp.device.device_id = mapping.old_id and mapping.tablename = 'device' )
 249 
      join mapping on (camp.device.device_id = mapping.old_id and mapping.tablename = 'device' )
245 
      join (
 250 
      join (
246 
         select
 251 
         select
247 
            device.device_id,
 252 
            device.device_id,
248 
            mapping.new_id
 253 
            mapping.new_id
249 
         from
 254 
         from
250 
            camp.device
 255 
            camp.device
251 
            join camp2.mapping on (camp.device.device_id = mapping.old_id and camp2.mapping.tablename = 'device')
 256 
            join mapping on (camp.device.device_id = mapping.old_id and mapping.tablename = 'device')
252 
         ) parent on ( camp.device.part_of = parent.device_id )
 257 
         ) parent on ( camp.device.part_of = parent.device_id )
253 
   where
 258 
   where
254 
      camp.device.device_type_id in (select device_type_id from camp.device_type where show_as_system = 'Y')
 259 
      camp.device.device_type_id in (select device_type_id from camp.device_type where show_as_system = 'Y')
255 
      and camp.device.part_of is not null
 260 
      and camp.device.part_of is not null
256 
      and camp.device.removed_date is null;
 261 
      and camp.device.removed_date is null;
Line 270... Line 275...
270 
 
 275 
 
271 
*/
 276 
*/
272 
 
 277 
 
273 
/* device_device_type */
 278 
/* device_device_type */
274 
 
 279 
 
275 
truncate camp2.device_device_type;
 280 
truncate device_device_type;
276 
insert into camp2.device_device_type (device_id,device_type_id)
 281 
insert into device_device_type (device_id,device_type_id)
277 
   select
 282 
   select
278 
      mapping.new_id,
 283 
      mapping.new_id,
279 
      map_type.new_id
 284 
      map_type.new_id
280 
   from
 285 
   from
281 
      camp.device
 286 
      camp.device
282 
      join camp.device_type on (camp.device.device_type_id = camp.device_type.device_type_id)
 287 
      join camp.device_type on (camp.device.device_type_id = camp.device_type.device_type_id)
283 
      join camp2.mapping on (camp.device.device_id = mapping.old_id and mapping.tablename = 'device')
 288 
      join mapping on (camp.device.device_id = mapping.old_id and mapping.tablename = 'device')
284 
      join camp2.mapping map_type on (camp.device_type.device_type_id = map_type.old_id and map_type.tablename = 'device_type' )
 289 
      join mapping map_type on (camp.device_type.device_type_id = map_type.old_id and map_type.tablename = 'device_type' )
285 
;
290 
;
286 
 
 291 
 
287 
/* verify with the following query
292 
/* verify with the following query
288 
 
 293 
 
289 
select device.name,device_types from device join view_device_types using (device_id);
 294 
select device.name,device_types from device join view_device_types using (device_id);
Line 298... Line 303...
298 
      mapping.new_id,
 303 
      mapping.new_id,
299 
      date(camp.site.added_date)
 304 
      date(camp.site.added_date)
300 
   from
 305 
   from
301 
      camp.device
 306 
      camp.device
302 
      join camp.site using (site_id)
 307 
      join camp.site using (site_id)
303 
      join camp2.mapping on (camp.device.device_id = mapping.old_id and mapping.tablename = 'device')
 308 
      join mapping on (camp.device.device_id = mapping.old_id and mapping.tablename = 'device')
304 
      join camp2.mapping map_type on (camp.site.site_id = map_type.old_id and map_type.tablename = 'location' )
 309 
      join mapping map_type on (camp.site.site_id = map_type.old_id and map_type.tablename = 'location' )
305 
;
310 
;
306 
 
 311 
 
307 
/* Verify with the following query
312 
/* Verify with the following query
308 
 
 313 
 
309 
select
314 
select
Line 325... Line 330...
325 
      date(camp.device.added_date)
 330 
      date(camp.device.added_date)
326 
   from
 331 
   from
327 
      camp.device
 332 
      camp.device
328 
      join camp.site using (site_id)
 333 
      join camp.site using (site_id)
329 
      join camp.client using (client_id)
 334 
      join camp.client using (client_id)
330 
      join camp2.mapping on (camp.device.device_id = mapping.old_id and mapping.tablename = 'device')
 335 
      join mapping on (camp.device.device_id = mapping.old_id and mapping.tablename = 'device')
331 
      join camp2.mapping map_type on (camp.client.client_id = map_type.old_id and map_type.tablename = 'owner' )
 336 
      join mapping map_type on (camp.client.client_id = map_type.old_id and map_type.tablename = 'owner' )
332 
;
337 
;
333 
 
 338 
 
334 
/* Verify with the following query
 339 
/* Verify with the following query
335 
 
 340 
 
336 
select owner.name owner, device.name device from owner join owner_device using (owner_id) join device using (device_id);
 341 
select owner.name owner, device.name device from owner join owner_device using (owner_id) join device using (device_id);
Line 346... Line 351...
346 
      mapping.new_id,
 351 
      mapping.new_id,
347 
      date(camp.site.added_date)
 352 
      date(camp.site.added_date)
348 
   from
 353 
   from
349 
      camp.site
 354 
      camp.site
350 
      join camp.client using (client_id)
 355 
      join camp.client using (client_id)
351 
      join camp2.mapping on (camp.site.site_id = mapping.old_id and mapping.tablename = 'location')
 356 
      join mapping on (camp.site.site_id = mapping.old_id and mapping.tablename = 'location')
352 
      join camp2.mapping map_type on (camp.client.client_id = map_type.old_id and map_type.tablename = 'owner' )
 357 
      join mapping map_type on (camp.client.client_id = map_type.old_id and map_type.tablename = 'owner' )
353 
   where
 358 
   where
354 
      camp.site.name in (select name from camp2.location)
 359 
      camp.site.name in (select name from location)
355 
;
 360 
;
356 
 
 361 
 
357 
/* Verify with one of the following queries, or, simply open the app
 362 
/* Verify with one of the following queries, or, simply open the app
358 
 
 363 
 
359 
select owner.name owner, location.name location from owner join owner_location using (owner_id) join location using (location_id);
 364 
select owner.name owner, location.name location from owner join owner_location using (owner_id) join location using (location_id);