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 */