Subversion Repositories computer_asset_manager_v1

/* this is only for upgrades */

create or replace table temp as select * from attrib;

/* end start upgrades */

/* this contains the attributes we can use information itself */

CREATE or replace 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      datetime not null COMMENT 'date record was added',

  removed_date    datetime default NULL COMMENT 'date record was deleted/supserceded',

  keyname         varchar(32) comment 'used for sysinfo, really needs to be separate table',

  PRIMARY KEY     (attrib_id)

)   COMMENT='These are attributes that can be applied to a device';

CREATE or replace 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      datetime NOT NULL COMMENT 'date record was added',

  removed_date    datetime default NULL,

  PRIMARY KEY     (attrib_device_id)

)  COMMENT='links devices and their attributes together';

create or replace table attrib_category (

   attrib_category_id int unsigned not null auto_increment,

   name           varchar(64),

   added_date     datetime not null comment 'When this category was added',

   primary key    (attrib_category_id)

) comment 'just allows us to categorize attributes';

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

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

drop table temp;

drop table device_attrib;

/* end of only for upgrades section */