Subversion Repositories computer_asset_manager_v2

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
3 rodolico 1
/* 
2
order is very important here. We should load child tables first
3
then we can load the main tables. By using more complex joins,
4
we can get the data in the right place
5
*/
6
 
7
/* import users */
8
insert into _user (_user_id,name,passwd,email,added_date,removed_date)
9
   select login_id,email,pass,null,added_date,removed_date from camp.login;
10
/* and _system */
11
insert into _system select _system_id,group_name,key_name,theValue from camp._system;
12
/* 
13
   create an entry for the user sysinfo will use to insert values
14
   this will also be the user we use for all inserts here
15
*/
16
insert into _system  select null,'sysinfo','_user_id',_user_id from _user where _user.name = 'Admin';
17
 
18
/* set up device_type */
19
insert into device_type select device_type_id,name,(show_as_system='Y') from camp.device_type;
20
 
21
/* now, set up category and attribute tables */
22
/* create some basic categories */
23
insert into category (category_id,name) values ( null,'Device');
24
insert into category (category_id,name) values (null,'Location' );
25
insert into category (category_id,name) values (null,'Client' );
26
insert into attribute( attribute_id,name,report_name,category_id) 
27
   select 
28
      camp.attrib.attrib_id,
29
      camp.attrib.name,
30
      camp.attrib.keyname,
31
      category.category_id
32
   from 
33
      camp.attrib 
34
      join camp2.category 
35
   where 
36
      camp2.category.name = 'Device';
37
/* Notes can be used for any category, so we set for all of them */
38
insert into attribute (attribute_id,name,report_name,category_id)
39
   select null,'Notes',null,category.category_id from category;
40
 
41
/* ok, all done with child tables, let's load the three main tables */
42
 
43
/* first, I want a temporary view into device that only lists systems */
44
create or replace view v_temp_system as 
45
   select camp.device.* 
46
   from camp.device join camp.device_type using (device_type_id) 
47
   where device_type.show_as_system = 'Y';
48
 
49
insert into client (client_id,name,_user_id,added_date,removed_date)
50
   select 
51
      client_id,
52
      name,
53
      _system.key_value,
54
      added_date,
55
      removed_date
56
   from
57
      camp.client 
58
      join _system 
59
   where 
60
      _system.group_name = 'sysinfo' 
61
      and _system.key_name='_user_id';
62
 
63
insert into location (location_id,name,_user_id,added_date,removed_date)
64
   select
65
      site_id,
66
      name,
67
      _system.key_value,
68
      added_date,
69
      removed_date
70
   from
71
      camp.site
72
      join _system 
73
   where 
74
      _system.group_name = 'sysinfo' 
75
      and _system.key_name='_user_id';
76
 
77
insert into device (device_id,name,device_type_id,_user_id,added_date,removed_date) 
78
   select 
79
      v_temp_system.device_id,
80
      v_temp_system.name,
81
      v_temp_system.device_type_id,
82
      _system.key_value,
83
      v_temp_system.added_date,
84
      v_temp_system.removed_date
85
   from 
86
      v_temp_system
87
      join _system 
88
   where 
89
      _system.group_name = 'sysinfo' 
90
      and _system.key_name='_user_id';
91
 
92
 
93
/* Add notes to the above tables */
94
insert into location_attribute (location_attribute_id,location_id,attribute_id,report_value,_user_id,added_date,removed_date )
95
   select
96
      null,
97
      camp.site.site_id,
98
      attribute.attribute_id,
99
      camp.site.notes,
100
      _system.key_value,
101
      now(),
102
      null
103
   from
104
      camp.site 
105
      join attribute 
106
      join category using (category_id) 
107
      join _system 
108
   where
109
      camp.site.notes is not null 
110
      and _system.group_name = 'sysinfo' 
111
      and _system.key_name='_user_id'
112
      and length(camp.site.notes) > 0
113
      and category.name = 'Location' 
114
      and attribute.name = 'Notes';
115
 
116
insert into client_attribute (client_attribute_id,client_id,attribute_id,report_value,_user_id,added_date,removed_date )
117
   select
118
      null,
119
      camp.client.client_id,
120
      attribute.attribute_id,
121
      camp.client.notes,
122
      _system.key_value,
123
      now(),
124
      null
125
   from
126
      camp.client
127
      join attribute 
128
      join category using (category_id) 
129
      join _system 
130
   where
131
      camp.client.notes is not null 
132
      and _system.group_name = 'sysinfo' 
133
      and _system.key_name='_user_id'
134
      and length(camp.client.notes) > 0
135
      and category.name = 'Client' 
136
      and attribute.name = 'Notes';
137
 
138
 
139
insert into device_attribute (device_attribute_id,device_id,attribute_id,report_value,_user_id,added_date,removed_date )
140
   select
141
      null,
142
      v_temp_system.device_id,
143
      attribute.attribute_id,
144
      v_temp_system.notes,
145
      _system.key_value,
146
      now(),
147
      null
148
   from
149
      v_temp_system
150
      join attribute 
151
      join category using (category_id) 
152
      join _system 
153
   where
154
      v_temp_system.notes is not null 
155
      and _system.group_name = 'sysinfo' 
156
      and _system.key_name='_user_id'
157
      and length(v_temp_system.notes) > 0
158
      and category.name = 'Device' 
159
      and attribute.name = 'Notes';
160
 
161
/* at this point, we need to do the links between device, location and client */
162
 
163
insert into device_location ( device_location_id,device_id,location_id,_user_id,added_date,removed_date)
164
   select 
165
      null,
166
      v_temp_system.device_id,
167
      camp.site.site_id,
168
      _system.key_value,
169
      camp.site.added_date,
170
      camp.site.removed_date
171
   from v_temp_system
172
      join camp.site using (site_id)
173
      join _system
174
   where 
175
      _system.group_name = 'sysinfo' 
176
      and _system.key_name='_user_id';
177
 
178
 
179
insert into device_client ( device_client_id,device_id,client_id,_user_id,added_date,removed_date)
180
   select 
181
      null,
182
      v_temp_system.device_id,
183
      camp.client.client_id,
184
      _system.key_value,
185
      camp.client.added_date,
186
      camp.client.removed_date
187
   from v_temp_system
188
      join camp.site using (site_id)
189
      join camp.client using (client_id)
190
      join _system
191
   where 
192
      _system.group_name = 'sysinfo' 
193
      and _system.key_name='_user_id';
194
 
195
insert into location_client( location_client_id,location_id,client_id,_user_id,added_date,removed_date )
196
   select 
197
      null,
198
      camp.site.site_id,
199
      camp.client.client_id,
200
      _system.key_value,
201
      camp.site.added_date,
202
      camp.site.removed_date
203
   from 
204
      camp.site
205
      join camp.client using (client_id)
206
      join _system
207
   where 
208
      _system.group_name = 'sysinfo' 
209
      and _system.key_name='_user_id';
210
 
211
 
212
/* maintenance module */
213
insert into maintenance_task (maintenance_task_id,name,description,default_period )
214
   select 
215
      maintenance_task_id,
216
      description,
217
      notes,
218
      default_period
219
   from camp.maintenance_task;
220
 
221
insert into maintenance_schedule (maintenance_schedule_id,device_id,maintenance_task_id,schedule,_user_id,added_date,removed_date)
222
   select
223
      maintenance_schedule_id,
224
      device_id,
225
      maintenance_task_id,
226
      schedule,
227
      login_id,
228
      added_date,
229
      removed_date
230
   from
231
      camp.maintenance_schedule;
232
 
233
insert into maintenance_performed (maintenance_performed_id,device_id,maintenance_task_id,date_performed,_user_id,notes )
234
   select 
235
      maintenance_performed_id,
236
      device_id,
237
      maintenance_task_id,
238
      maintenance_date,
239
      login_id,
240
      notes
241
   from 
242
      camp.maintenance_performed;
243
 
244
insert into maintenance_group values (null,'Windows Server',null,now());
245
insert into maintenance_group values (null,'Linux Server',null,now());
246
insert into maintenance_group values (null,'Redhat Server',null,now());
247
insert into maintenance_group values (null,'Debian Server',null,now());
248
insert into maintenance_group values (null,'Xen DOM0',null,now());
249
insert into maintenance_group values (null,'Windows Workstation',null,now());
250
insert into maintenance_group values (null,'Linux Server',null,now());
251
insert into maintenance_group values (null,'IPFire Router',null,now());
252
insert into maintenance_group values (null,'Physical Device',null,now());
253
 
254
insert into maintenance_group_task (maintenance_task_id,maintenance_group_id,default_period)
255
   select 
256
      maintenance_task_id,
257
      maintenance_group_id,
258
      default_period
259
   from
260
      maintenance_task,
261
      maintenance_group
262
   where
263
      maintenance_group.name = 'Windows Server'
264
      and maintenance_task_id in ( 3,4,16,14,15);
265
 
266
insert into maintenance_group_task (maintenance_task_id,maintenance_group_id,default_period)
267
   select 
268
      maintenance_task_id,
269
      maintenance_group_id,
270
      90
271
   from
272
      maintenance_task,
273
      maintenance_group
274
   where
275
      maintenance_group.name = 'Windows Server'
276
      and maintenance_task_id in ( 16 );
277
 
278
insert into maintenance_group_task (maintenance_task_id,maintenance_group_id,default_period)
279
   select 
280
      maintenance_task_id,
281
      maintenance_group_id,
282
      30
283
   from
284
      maintenance_task,
285
      maintenance_group
286
   where
287
      maintenance_group.name = 'Linux Server'
288
      and maintenance_task_id in ( 4,5 );
289
 
290
insert into maintenance_group_task (maintenance_task_id,maintenance_group_id,default_period)
291
   select 
292
      maintenance_task_id,
293
      maintenance_group_id,
294
      7
295
   from
296
      maintenance_task,
297
      maintenance_group
298
   where
299
      maintenance_group.name = 'Debian Server'
300
      and maintenance_task_id in ( 1,4,5 );
301
 
302
insert into maintenance_group_task (maintenance_task_id,maintenance_group_id,default_period)
303
   select 
304
      maintenance_task_id,
305
      maintenance_group_id,
306
      7
307
   from
308
      maintenance_task,
309
      maintenance_group
310
   where
311
      maintenance_group.name = 'Redhat Server'
312
      and maintenance_task_id in ( 2,4,5 );
313
 
314
insert into maintenance_group_task (maintenance_task_id,maintenance_group_id,default_period)
315
   select 
316
      maintenance_task_id,
317
      maintenance_group_id,
318
      180
319
   from
320
      maintenance_task,
321
      maintenance_group
322
   where
323
      maintenance_group.name = 'Xen DOM0'
324
      and maintenance_task_id in ( 1,4,5,14 );
325
 
326
insert into maintenance_group_task (maintenance_task_id,maintenance_group_id,default_period)
327
   select 
328
      maintenance_task_id,
329
      maintenance_group_id,
330
      30
331
   from
332
      maintenance_task,
333
      maintenance_group
334
   where
335
      maintenance_group.name = 'Windows Server'
336
      and maintenance_task_id in ( 3,4,16,14,15);
337
 
338
insert into maintenance_group_task (maintenance_task_id,maintenance_group_id,default_period)
339
   select 
340
      maintenance_task_id,
341
      maintenance_group_id,
342
      180
343
   from
344
      maintenance_task,
345
      maintenance_group
346
   where
347
      maintenance_group.name = 'Physical Device'
348
      and maintenance_task_id in ( 6,7 );
349
 
350
insert into maintenance_group_task (maintenance_task_id,maintenance_group_id,default_period)
351
   select 
352
      maintenance_task_id,
353
      maintenance_group_id,
354
      30
355
   from
356
      maintenance_task,
357
      maintenance_group
358
   where
359
      maintenance_group.name = 'IPFire Router'
360
      and maintenance_task_id in ( 1,4,5,14 );
361
 
362
select
363
   maintenance_group.name,
364
   maintenance_task.name,
365
   maintenance_group_task.default_period
366
from
367
   maintenance_group 
368
   join maintenance_group_task using (maintenance_group_id) 
369
   join maintenance_task using (maintenance_task_id) 
370
order by
371
   maintenance_group.name;
372
 
373