--2007-03-26
--if you create from scratch these columns will exist
--alter table hostbase_host add column  `serial_num` varchar(128) NULL;
--alter table hostbase_host add column  `_model` varchar(128) NULL;
--alter table hostbase_host add column  `_make` varchar(128) NULL;                

-- 2007-03-05                                                
--alter table hostbase_role add column `pri` integer(1) NOT NULL default 0;

-- 2007-03-08 TM: Created views to check for division reorganizations by linking to HR tables in ANLCOMMON
--need to get ANLCOMON from IHW svn repo, issue this command in shell:
--mysql -u root -p mysql < ./trunk/sql/ANLCOMMONtestdata
-- 2007-04-16 TM: Created views to check for active ALDs
--mysql -u root -p mysql < ./trunk/sql/ANLCOMMONALDtestdata
-- View of newly created divisions in HR tables

-- View of retired divisions (no longer active) in HR tables
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`ihw`@`localhost` SQL SECURITY DEFINER VIEW `hostbase`.`retireddivisions` AS select `o`.`id` AS `id`,`o`.`name` AS `name`,`o`.`parent` AS `parent` from `hostbase`.`hostbase_org` `o` where ((`o`.`parent` = 1) and not(`o`.`name` in (select `v`.`division` AS `division` from `ANLCOMMON`.`ActiveDivisionsView` v)))

CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`ihw`@`localhost` SQL SECURITY DEFINER VIEW `hostbase`.`newdivisions` AS select `v`.`division` AS `division` from `ANLCOMMON`.`ActiveDivisionsView` v where not(`v`.`division` in (select `o`.`name` AS `name` from `hostbase`.`hostbase_org` `o` where (`o`.`parent` = 1)))


-- Need to grant privileges to above 

-- 2007-03-26 need default columns in the database for tables supporting
-- hostbase_host: hostbase_org, hostbase_os, hostbase_building, hostbase_room

INSERT INTO hostbase_org (id,name,parent) VALUES (1,'ANL',0)
-- make the parent ANL but the ID is 2 becasue ANL is '1', id '0' is NULL

INSERT INTO hostbase_operatingsystem (id,name,version,revision,notes)  VALUES (0,'Undefined','Undefined','Undefined','Undefined');
INSERT INTO hostbase_building (id,building_no,description)  VALUES (0,'Undefined','Undefined');
INSERT INTO hostbase_room (id,room_no,building_id) VALUES (0,'Undefined',0);

--Alter existing hostbase_host table to know about the defaults created above
ALTER TABLE hostbase_host MODIFY os_id int(11) NULL DEFAULT 0;
ALTER TABLE hostbase_host MODIFY org_id int(11) NULL DEFAULT 2;
ALTER TABLE hostbase_host MODIFY building_id int(11) NULL DEFAULT 0;
ALTER TABLE hostbase_host MODIFY room_id int(11) NULL DEFAULT 0;


--here is how the hostbase_host tablle should look:
--mysql> desc hostbase_host;
--+----------------------+--------------+------+-----+---------+----------------+
--| Field                | Type         | Null | Key | Default | Extra          |
--+----------------------+--------------+------+-----+---------+----------------+
--| id                   | int(11)      | NO   | PRI | NULL    | auto_increment | 
--| hostname             | varchar(256) | NO   |     |         |                --| 
--| user                 | varchar(512) | NO   |     |         |                --| 
--| administrator        | varchar(512) | NO   |     |         |                | 
--| comments             | longtext     | NO   |     |         |                | 
--| last                 | date         | NO   |     |         |                | 
--| status               | varchar(7)   | NO   |     |         |                | 
--| org_id               | int(11)      | YES  | MUL | 2       |                | 
--| os_id                | int(11)      | YES  | MUL | 0       |                | 
--| building_id          | int(11)      | YES  | MUL | 0       |                | 
--| room_id              | int(11)      | YES  | MUL | 0       |                | 
--| dirty                | tinyint(1)   | NO   |     |         |                | 
--| expiration_date      | date         | YES  |     | NULL    |                | 
--| location             | varchar(16)  | NO   |     |         |                | 
--| csi                  | varchar(32)  | NO   | UNI |         |                | 
--| host_type            | varchar(16)  | NO   |     |         |                | 
--| security_class       | varchar(64)  | NO   |     |         |                | 
--| business_division    | varchar(8)   | NO   |     |         |                | 
--| division_unique_name | varchar(256) | NO   |     |         |                | 
--| is_org_property      | tinyint(1)   | YES  |     | NULL    |                | 
--| has_personal_info    | tinyint(1)   | YES  |     | NULL    |                | 
--| is_sensitive         | tinyint(1)   | YES  |     | NULL    |                | 
--| support              | varchar(8)   | NO   |     |         |                | 
--| netgroup             | varchar(32)  | YES  |     | NULL    |                | 
--| whatami              | varchar(16)  | YES  |     | NULL    |                | 
--| printq               | varchar(32)  | YES  |     | NULL    |                | 
--| outbound_smtp        | tinyint(1)   | YES  |     | NULL    |                | 
--| primary_user         | varchar(75)  | YES  |     | NULL    |                | 
--| serial_num           | varchar(128) | YES  |     | NULL    |                | 
--| _model               | varchar(128) | YES  |     | NULL    |                | 
--| _make                | varchar(128) | YES  |     | NULL    |                | 
--+----------------------+--------------+------+-----+---------+----------------+
--31 rows in set (0.00 sec)

--2007-03-30: added new column to UploadEvent table: status VARCHAR(32) NOT NULL DEFAULT 'new'

ALTER TABLE hostbase_uploadevent ADD COLUMN status VARCHAR(32) NOT NULL DEFAULT 'new'

--2007-03-30: added column back to hostbase_host 

ALTER TABLE hostbase_host ADD COLUMN security_class VARCHAR(64) NULL

--2007-05-15: added columns to hostbase_host 

alter table hostbase_host add column user_badge varchar(5) null
alter table hostbase_host add column administrator_badge varchar(5) null

--2007-05-18: changed location size due to regex
ALTER TABLE `ihw_lamp_test`.`hostbase_host` MODIFY COLUMN `location` VARCHAR(64) CHARACTER SET latin1 COLLATE latin1_swedish_ci;
