Subversion Repositories computer_asset_manager_v2

/* this is superceded by camp_to_camp2.sql, but kept here for reference */

/* 

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;