Rev 3 | Blame | Last modification | View Log | Download | RSS feed
/*
order is very important here. We should load child tables first
then we can load the main tables. By using more complex joins,
we can get the data in the right place
*/
/* import users */
insert into _user (_user_id,name,passwd,email,added_date,removed_date)
select login_id,email,pass,null,added_date,removed_date from camp.login;
/* and _system */
insert into _system select _system_id,group_name,key_name,theValue from camp._system;
/*
create an entry for the user sysinfo will use to insert values
this will also be the user we use for all inserts here
*/
insert into _system select null,'sysinfo','_user_id',_user_id from _user where _user.name = 'Admin';
/* set up device_type */
insert into device_type select device_type_id,name,(show_as_system='Y') from camp.device_type;
/* now, set up category and attribute tables */
/* create some basic categories */
insert into category (category_id,name) values ( null,'Device');
insert into category (category_id,name) values (null,'Location' );
insert into category (category_id,name) values (null,'Client' );
insert into attribute( attribute_id,name,report_name,category_id)
select
camp.attrib.attrib_id,
camp.attrib.name,
camp.attrib.keyname,
category.category_id
from
camp.attrib
join camp2.category
where
camp2.category.name = 'Device';
/* Notes can be used for any category, so we set for all of them */
insert into attribute (attribute_id,name,report_name,category_id)
select null,'Notes',null,category.category_id from category;
/* ok, all done with child tables, let's load the three main tables */
/* first, I want a temporary view into device that only lists systems */
create or replace view v_temp_system as
select camp.device.*
from camp.device join camp.device_type using (device_type_id)
where device_type.show_as_system = 'Y';
insert into client (client_id,name,_user_id,added_date,removed_date)
select
client_id,
name,
_system.key_value,
added_date,
removed_date
from
camp.client
join _system
where
_system.group_name = 'sysinfo'
and _system.key_name='_user_id';
insert into location (location_id,name,_user_id,added_date,removed_date)
select
site_id,
name,
_system.key_value,
added_date,
removed_date
from
camp.site
join _system
where
_system.group_name = 'sysinfo'
and _system.key_name='_user_id';
insert into device (device_id,name,device_type_id,_user_id,added_date,removed_date)
select
v_temp_system.device_id,
v_temp_system.name,
v_temp_system.device_type_id,
_system.key_value,
v_temp_system.added_date,
v_temp_system.removed_date
from
v_temp_system
join _system
where
_system.group_name = 'sysinfo'
and _system.key_name='_user_id';
/* Add notes to the above tables */
insert into location_attribute (location_attribute_id,location_id,attribute_id,report_value,_user_id,added_date,removed_date )
select
null,
camp.site.site_id,
attribute.attribute_id,
camp.site.notes,
_system.key_value,
now(),
null
from
camp.site
join attribute
join category using (category_id)
join _system
where
camp.site.notes is not null
and _system.group_name = 'sysinfo'
and _system.key_name='_user_id'
and length(camp.site.notes) > 0
and category.name = 'Location'
and attribute.name = 'Notes';
insert into client_attribute (client_attribute_id,client_id,attribute_id,report_value,_user_id,added_date,removed_date )
select
null,
camp.client.client_id,
attribute.attribute_id,
camp.client.notes,
_system.key_value,
now(),
null
from
camp.client
join attribute
join category using (category_id)
join _system
where
camp.client.notes is not null
and _system.group_name = 'sysinfo'
and _system.key_name='_user_id'
and length(camp.client.notes) > 0
and category.name = 'Client'
and attribute.name = 'Notes';
insert into device_attribute (device_attribute_id,device_id,attribute_id,report_value,_user_id,added_date,removed_date )
select
null,
v_temp_system.device_id,
attribute.attribute_id,
v_temp_system.notes,
_system.key_value,
now(),
null
from
v_temp_system
join attribute
join category using (category_id)
join _system
where
v_temp_system.notes is not null
and _system.group_name = 'sysinfo'
and _system.key_name='_user_id'
and length(v_temp_system.notes) > 0
and category.name = 'Device'
and attribute.name = 'Notes';
/* at this point, we need to do the links between device, location and client */
insert into device_location ( device_location_id,device_id,location_id,_user_id,added_date,removed_date)
select
null,
v_temp_system.device_id,
camp.site.site_id,
_system.key_value,
camp.site.added_date,
camp.site.removed_date
from v_temp_system
join camp.site using (site_id)
join _system
where
_system.group_name = 'sysinfo'
and _system.key_name='_user_id';
insert into device_client ( device_client_id,device_id,client_id,_user_id,added_date,removed_date)
select
null,
v_temp_system.device_id,
camp.client.client_id,
_system.key_value,
camp.client.added_date,
camp.client.removed_date
from v_temp_system
join camp.site using (site_id)
join camp.client using (client_id)
join _system
where
_system.group_name = 'sysinfo'
and _system.key_name='_user_id';
insert into location_client( location_client_id,location_id,client_id,_user_id,added_date,removed_date )
select
null,
camp.site.site_id,
camp.client.client_id,
_system.key_value,
camp.site.added_date,
camp.site.removed_date
from
camp.site
join camp.client using (client_id)
join _system
where
_system.group_name = 'sysinfo'
and _system.key_name='_user_id';
/* maintenance module */
insert into maintenance_task (maintenance_task_id,name,description,default_period )
select
maintenance_task_id,
description,
notes,
default_period
from camp.maintenance_task;
insert into maintenance_schedule (maintenance_schedule_id,device_id,maintenance_task_id,schedule,_user_id,added_date,removed_date)
select
maintenance_schedule_id,
device_id,
maintenance_task_id,
schedule,
login_id,
added_date,
removed_date
from
camp.maintenance_schedule;
insert into maintenance_performed (maintenance_performed_id,device_id,maintenance_task_id,date_performed,_user_id,notes )
select
maintenance_performed_id,
device_id,
maintenance_task_id,
maintenance_date,
login_id,
notes
from
camp.maintenance_performed;
insert into maintenance_group values (null,'Windows Server',null,now());
insert into maintenance_group values (null,'Linux Server',null,now());
insert into maintenance_group values (null,'Redhat Server',null,now());
insert into maintenance_group values (null,'Debian Server',null,now());
insert into maintenance_group values (null,'Xen DOM0',null,now());
insert into maintenance_group values (null,'Windows Workstation',null,now());
insert into maintenance_group values (null,'Linux Server',null,now());
insert into maintenance_group values (null,'IPFire Router',null,now());
insert into maintenance_group values (null,'Physical Device',null,now());
insert into maintenance_group_task (maintenance_task_id,maintenance_group_id,default_period)
select
maintenance_task_id,
maintenance_group_id,
default_period
from
maintenance_task,
maintenance_group
where
maintenance_group.name = 'Windows Server'
and maintenance_task_id in ( 3,4,16,14,15);
insert into maintenance_group_task (maintenance_task_id,maintenance_group_id,default_period)
select
maintenance_task_id,
maintenance_group_id,
90
from
maintenance_task,
maintenance_group
where
maintenance_group.name = 'Windows Server'
and maintenance_task_id in ( 16 );
insert into maintenance_group_task (maintenance_task_id,maintenance_group_id,default_period)
select
maintenance_task_id,
maintenance_group_id,
30
from
maintenance_task,
maintenance_group
where
maintenance_group.name = 'Linux Server'
and maintenance_task_id in ( 4,5 );
insert into maintenance_group_task (maintenance_task_id,maintenance_group_id,default_period)
select
maintenance_task_id,
maintenance_group_id,
7
from
maintenance_task,
maintenance_group
where
maintenance_group.name = 'Debian Server'
and maintenance_task_id in ( 1,4,5 );
insert into maintenance_group_task (maintenance_task_id,maintenance_group_id,default_period)
select
maintenance_task_id,
maintenance_group_id,
7
from
maintenance_task,
maintenance_group
where
maintenance_group.name = 'Redhat Server'
and maintenance_task_id in ( 2,4,5 );
insert into maintenance_group_task (maintenance_task_id,maintenance_group_id,default_period)
select
maintenance_task_id,
maintenance_group_id,
180
from
maintenance_task,
maintenance_group
where
maintenance_group.name = 'Xen DOM0'
and maintenance_task_id in ( 1,4,5,14 );
insert into maintenance_group_task (maintenance_task_id,maintenance_group_id,default_period)
select
maintenance_task_id,
maintenance_group_id,
30
from
maintenance_task,
maintenance_group
where
maintenance_group.name = 'Windows Server'
and maintenance_task_id in ( 3,4,16,14,15);
insert into maintenance_group_task (maintenance_task_id,maintenance_group_id,default_period)
select
maintenance_task_id,
maintenance_group_id,
180
from
maintenance_task,
maintenance_group
where
maintenance_group.name = 'Physical Device'
and maintenance_task_id in ( 6,7 );
insert into maintenance_group_task (maintenance_task_id,maintenance_group_id,default_period)
select
maintenance_task_id,
maintenance_group_id,
30
from
maintenance_task,
maintenance_group
where
maintenance_group.name = 'IPFire Router'
and maintenance_task_id in ( 1,4,5,14 );
select
maintenance_group.name,
maintenance_task.name,
maintenance_group_task.default_period
from
maintenance_group
join maintenance_group_task using (maintenance_group_id)
join maintenance_task using (maintenance_task_id)
order by
maintenance_group.name;