Subversion Repositories computer_asset_manager_v2

Rev

Details | Last modification | View Log | RSS feed

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