Subversion Repositories computer_asset_manager_v1

/* this is only for upgrades */
drop table if exists temp;
create table temp as select * from attrib;
/* end start upgrades */

drop table if exists attrib;
create table attrib (
  attrib_id       int(10) unsigned NOT NULL auto_increment,
  attrib_category_id int unsigned default 1 references attrib_category( attrib_category_id ),
  name            varchar(64) not null unique COMMENT 'the visible displayed name',
  added_date      date not null COMMENT 'date record was added',
  multiples       char(1) default null comment 'set to true if ultiple entries are allowed',
  removed_date    date default NULL COMMENT 'date record was deleted or supserceded',
  keyname         varchar(32) comment 'used for sysinfo really needs to be separate table',
  PRIMARY KEY     (attrib_id)
) comment 'attributes that can be applied to a device';

drop table if exists attrib_category;
create table attrib_category (
   attrib_category_id int unsigned not null auto_increment,
   name           varchar(64),
   added_date     date not null comment 'When this category was added',
   primary key    (attrib_category_id)
) comment 'just allows us to categorize attributes';

drop table if exists attrib_device;
CREATE TABLE attrib_device (
  attrib_device_id int unsigned not null auto_increment,
  device_id       int(10) unsigned NOT NULL REFERENCES device(device_id),
  attrib_id       int(10) unsigned NOT NULL REFERENCES attrib(attrib_id),
  value           text COMMENT 'The actual value of this attribute.',
  added_date      date NOT NULL COMMENT 'date record was added',
  removed_date    date default NULL,
  PRIMARY KEY     (attrib_device_id)
)  COMMENT='links devices and their attributes together';

insert into attrib_category values ( 1,'General',now() );

/* add some indicies */
alter table attrib add index (name);
alter table attrib add index (added_date,removed_date);

alter table attrib_device add index (device_id);
alter table attrib_device add index (added_date,removed_date);



/* set up the _system table to plug in the module */
/* this is required, and officially installs the module */
insert into _system ( group_name,key_name,theValue,added_date) values ( 'Modules', 'Attributes', 'modules/attributes/', now() );
/* we will provide data to the main screen via a callback routine. In this case, function view is located in callable.php */
insert into _system( group_name,key_name,theValue,added_date ) values ( 'device view', 'Attributes', 'callable.php:attributeView', now() );

/* This has a menu entry, so add it */
/* first, make sure we don't have any danglies */
/* create three menu options. First one is the main menu option (ie, no parent_id) */
insert into menu( url, caption, parent_id) values ('/modules/attributes' , 'Attributes', null);
/* two additional menu options for bulk_load and editing the license_product table */
insert into menu( url,caption,parent_id) select '/modules/attributes/bulk_load.html','Bulk Load',menu_id from menu where caption = 'Attributes';
insert into menu( url,caption,parent_id) select '/modules/attributes/dmidecode_load.html','Load dmidecode file',menu_id from menu where caption = 'Attributes';
insert into menu( url,caption,parent_id) select '/modules/attributes/edit_device_attributes.html','Edit Device',menu_id from menu where caption = 'Attributes';

/* give all administrators the new menu */
insert into login_menu
   select null,login_id,menu_id
   from login,menu
   where 
      login.removed_date is null
      and login.where_clause = '1=1'
      and (
         menu.caption = 'Attributes'
         or menu.parent_id = (select menu_id from menu where caption = 'Attributes')
         )
;


/* do this only for upgrades */
insert into attrib (attrib_id,name,added_date,removed_date,keyname) select * from temp;
insert into attrib_device select device_attrib_id,device_id,attrib_id,value,added_date,removed_date from device_attrib;

update attrib set multiples = '1' where name in ( 'Alias','MAC Address','Mass Storage','Memory','Note', 'Power Supply' );

drop table temp;
drop table device_attrib;

update attrib set name = 'CPU Cores' where name = 'Cores per CPU';

update attrib set name = 'Firmware Revision' where name = 'Boot ROM Version';
update report set screen_report = null where name in ( 'Device Attributes', 'Device PCI' );

update attrib_device set attrib_id = 5 where attrib_id = 45;
update attrib set name = 'CPU Manufacturer',multiples=null where attrib_id = 45;

update attrib_device 
   set removed_date = now() 
   where 
      attrib_device_id in (
         select toremove 
         from 
            (select 
               device_id,
               min(attrib_device_id) toremove, 
               count(*) 
            from 
               attrib_device k
            where 
               attrib_id = 5 
               and removed_date is null 
               group by device_id 
               having count(*) > 1) as j
               );


/* end of only for upgrades section */