--
-- hp_ora_schema.sql
--

DROP TABLE HP_CPU;

CREATE TABLE HP_CPU (
      COMPUTER_SYS_ID      	VARCHAR2(64)  NOT NULL,
	CPU_NUMBER			INTEGER NOT NULL,
	CPU_TYPE			VARCHAR2(128) NULL,
	CPU_SPEED			INTEGER NULL,
	L2_CACHE			VARCHAR2(10) NULL,
	L3_CACHE			VARCHAR2(10) NULL,
	CPU_CORE_INDEX		INTEGER NULL,
	CPU_CORE_COUNT		INTEGER NULL,
	RECORD_TIME          DATE     DEFAULT SYSDATE,
CONSTRAINT HPCPU_PK PRIMARY KEY(COMPUTER_SYS_ID, CPU_NUMBER),
CONSTRAINT HPCPU_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID));

CREATE OR REPLACE TRIGGER HPCPU_TR 
BEFORE UPDATE ON HP_CPU
FOR EACH ROW
BEGIN
       :NEW.RECORD_TIME := SYSDATE;
END;
/

DROP TABLE HP_EISA_PCI_SLOTS;

CREATE TABLE HP_EISA_PCI_SLOTS (
      COMPUTER_SYS_ID      	VARCHAR2(64)  NOT NULL,
	SLOT_NUMBER			VARCHAR2(8) NOT NULL ,
	BOARD_EISA_PCI_ID		VARCHAR2(8) NULL ,
	BOARD_NAME			VARCHAR2(128) NULL ,
	RECORD_TIME          DATE     DEFAULT SYSDATE,
CONSTRAINT HPEISAPCISLOTS_PK PRIMARY KEY(COMPUTER_SYS_ID, SLOT_NUMBER),
CONSTRAINT HPEISAPCISLOTS_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID));

CREATE OR REPLACE TRIGGER HPEISAPCISLOTS_TR 
BEFORE UPDATE ON HP_EISA_PCI_SLOTS
FOR EACH ROW
BEGIN
     :NEW.RECORD_TIME := SYSDATE;
END;
/


DROP TABLE HP_SYSTEM;

CREATE TABLE HP_SYSTEM (
      COMPUTER_SYS_ID      	VARCHAR2(64)  NOT NULL,
	SYSTEM_NAME			VARCHAR2(128) NULL ,
	FLASH_ROM			VARCHAR2(3) NULL ,
	SYSTEM_PARTITION		VARCHAR2(9) NULL ,
	SYSTEM_ROM_REV		VARCHAR2(40) NULL ,
	SERIAL_NUMBER		VARCHAR2(17) NULL ,
	PART_NUMBER			VARCHAR2(18) NULL,
	ASSET_TAG			VARCHAR2(17) NULL ,
	TOTAL_SYS_MEMORY		VARCHAR2(12) NULL ,
	HOT_PLUG_PCI		VARCHAR2(3) NULL ,
	RECORD_TIME          DATE     DEFAULT SYSDATE,
CONSTRAINT HPSYSTEM_PK PRIMARY KEY(COMPUTER_SYS_ID),
CONSTRAINT HPSYSTEM_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID));

CREATE OR REPLACE TRIGGER HPSYSTEM_TR 
BEFORE UPDATE ON HP_SYSTEM
FOR EACH ROW
BEGIN
     :NEW.RECORD_TIME := SYSDATE;
END;
/

DROP TABLE HP_MEMORY_MODULES;

CREATE TABLE HP_MEMORY_MODULES (
      COMPUTER_SYS_ID      	VARCHAR2(64)  NOT NULL,
	BOARD_MODULE_NUM		VARCHAR2(8) NOT NULL ,
	MODULE_SIZE			VARCHAR2(8) NULL ,
	MODULE_TYPE			VARCHAR2(12) NULL ,
	MODULE_SPEED		VARCHAR2(8) NULL ,
	MEMORY_ERROR_CHK_TYPE	VARCHAR2(12) NULL ,
	MEMORY_TECHNOLOGY_TYPE	VARCHAR2(12) NULL ,
	MODULE_PART_NUMBER	VARCHAR2(32) NULL ,
	MODULE_SERIAL_NUMBER	VARCHAR2(32) NULL ,
	MODULE_MANUFACTURE_ID	VARCHAR2(10) NULL ,
	MODULE_MANUFACTURE_DATE	VARCHAR2(10) NULL,
	RECORD_TIME          DATE     DEFAULT SYSDATE,
CONSTRAINT HPMEMORYMODULES_PK PRIMARY KEY(COMPUTER_SYS_ID, BOARD_MODULE_NUM),
CONSTRAINT HPMEMORYMODULES_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID));

CREATE OR REPLACE TRIGGER HPMEMORYMODULES_TR 
BEFORE UPDATE ON HP_MEMORY_MODULES
FOR EACH ROW
BEGIN
     :NEW.RECORD_TIME := SYSDATE;
END;
/

DROP TABLE HP_IDE_DEVICES;

 
CREATE TABLE HP_IDE_DEVICES (
      COMPUTER_SYS_ID      	VARCHAR2(64)  NOT NULL,
	CONTROLLER_DRIVE		VARCHAR2(10) NOT NULL ,
	NAME				VARCHAR2(41) NULL ,
	TYPE				VARCHAR2(21) NULL ,
	FIRMWARE_REV		VARCHAR2(9) NULL ,
	DISK_SIZE			VARCHAR2(12) NULL ,
	SERIAL_NUMBER		VARCHAR2(21) NULL ,
	RECORD_TIME          DATE     DEFAULT SYSDATE,
CONSTRAINT HPIDEDEVICES_PK PRIMARY KEY(COMPUTER_SYS_ID, CONTROLLER_DRIVE),
CONSTRAINT HPIDEDEVICES_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID));

CREATE OR REPLACE TRIGGER HPIDEDEVICES_TR 
BEFORE UPDATE ON HP_IDE_DEVICES
FOR EACH ROW
BEGIN
     :NEW.RECORD_TIME := SYSDATE;
END;
/

DROP TABLE HP_SCSI_DEVICES;

 
CREATE TABLE HP_SCSI_DEVICES (
      COMPUTER_SYS_ID      	VARCHAR2(64)  NOT NULL,
	CNTLR_SLOT_SCSI_ID	VARCHAR2(10) NOT NULL ,
	NAME				VARCHAR2(26) NULL ,
	TYPE				VARCHAR2(21) NULL ,
	FIRMWARE_REV		VARCHAR2(5) NULL ,
	BUS_WIDTH			VARCHAR2(7) NULL ,
	DISK_SIZE			VARCHAR2(10) NULL ,
	HOT_PLUGGABLE		VARCHAR2(8) NULL ,
	SERIAL_NUMBER		VARCHAR2(40) NULL,
	RECORD_TIME          DATE     DEFAULT SYSDATE,
CONSTRAINT HPSCSIDEVICES_PK PRIMARY KEY(COMPUTER_SYS_ID, CNTLR_SLOT_SCSI_ID),
CONSTRAINT HPSCSIDEVICES_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID));

CREATE OR REPLACE TRIGGER HPSCSIDEVICES_TR 
BEFORE UPDATE ON HP_SCSI_DEVICES
FOR EACH ROW
BEGIN
     :NEW.RECORD_TIME := SYSDATE;
END;
/

DROP TABLE HP_VIDEO_MONITOR;

 
CREATE TABLE HP_VIDEO_MONITOR (
      COMPUTER_SYS_ID      	VARCHAR2(64)  NOT NULL,
	DESCRIPTION			VARCHAR2(128) NULL ,
	NAME				VARCHAR2(128) NULL ,
	SERIAL_NUMBER		VARCHAR2(48) NULL ,
	MAX_HORIZONTAL_PIXELS	INTEGER NULL ,
	MAX_VERTICAL_PIXELS	INTEGER NULL ,
	EDID_VERSION		VARCHAR2(5) NULL ,
	VERT_HORZ_FREQ_RANGE	VARCHAR2(24) NULL ,
	FAULT_MANAGEMENT_SUPPORT	VARCHAR2(3) NULL ,
	MANUFACTURE			VARCHAR2(64) NULL,
	RECORD_TIME          DATE     DEFAULT SYSDATE,
CONSTRAINT HPVIDEOMONITOR_PK PRIMARY KEY(COMPUTER_SYS_ID),
CONSTRAINT HPVIDEOMONITOR_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID));

CREATE OR REPLACE TRIGGER HPVIDEOMONITOR_TR 
BEFORE UPDATE ON HP_VIDEO_MONITOR
FOR EACH ROW
BEGIN
     :NEW.RECORD_TIME := SYSDATE;
END;
/

DROP TABLE HP_HEALTH_SUBSYSTEM;

 
CREATE TABLE HP_HEALTH_SUBSYSTEM (
      COMPUTER_SYS_ID      	VARCHAR2(64)  NOT NULL,
	ASR_STATUS			VARCHAR2(14) NULL ,
	ASR_REBOOT_TO		VARCHAR2(18) NULL ,
	PAGER_STATUS		VARCHAR2(14) NULL ,
	PAGER_NUMBER		VARCHAR2(64) NULL,
	RECORD_TIME          DATE     DEFAULT SYSDATE,
CONSTRAINT HPHEALTHSUBSYSTEM_PK PRIMARY KEY(COMPUTER_SYS_ID),
CONSTRAINT HPHEALTHSUBSYSTEM_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID));


CREATE OR REPLACE TRIGGER HPHEALTHSUBSYSTEM_TR 
BEFORE UPDATE ON HP_HEALTH_SUBSYSTEM
FOR EACH ROW
BEGIN
     :NEW.RECORD_TIME := SYSDATE;
END;
/


DROP TABLE HP_DRIVE_ARRAY_CONTROLLER;

 
CREATE TABLE HP_DRIVE_ARRAY_CONTROLLER (
      COMPUTER_SYS_ID      	VARCHAR2(64)  NOT NULL,
	MODEL				VARCHAR2(48) NULL ,
	FIRMWARE_REV       	VARCHAR2(5) NULL ,
	SLOT				INTEGER NOT NULL,
	RECORD_TIME          DATE     DEFAULT SYSDATE,
CONSTRAINT HPDRIVEARRAYCONTROLLER_PK PRIMARY KEY(COMPUTER_SYS_ID, SLOT),
CONSTRAINT HPDRIVEARRAYCONTROLLER_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID));

CREATE OR REPLACE TRIGGER HPDRIVEARRAYCONTROLLER_TR 
BEFORE UPDATE ON HP_DRIVE_ARRAY_CONTROLLER
FOR EACH ROW
BEGIN
     :NEW.RECORD_TIME := SYSDATE;
END;
/

DROP TABLE HP_DA_LOGICAL;

 
CREATE TABLE HP_DA_LOGICAL (
      COMPUTER_SYS_ID      	VARCHAR2(64)  NOT NULL,
	LOGICAL_DRIVE_INDEX	INTEGER NOT NULL ,
	DRIVE_SIZE			VARCHAR2(10) NULL ,
	CONTROLLER_SLOT		INTEGER NULL ,
	AVAILABLE_SPARES		VARCHAR2(40) NULL,
	PHYSICAL_DRIVES		VARCHAR2(6) NULL,
	FAULT_TOLERANCE		VARCHAR2(5) NULL ,
	RECORD_TIME          DATE     DEFAULT SYSDATE,
CONSTRAINT HPDALOGICAL_PK PRIMARY KEY(COMPUTER_SYS_ID, LOGICAL_DRIVE_INDEX),
CONSTRAINT HPDALOGICAL_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID));

CREATE OR REPLACE TRIGGER HPDALOGICAL_TR 
BEFORE UPDATE ON HP_DA_LOGICAL
FOR EACH ROW
BEGIN
     :NEW.RECORD_TIME := SYSDATE;
END;
/

DROP TABLE HP_DA_PHYSICAL;

 
CREATE TABLE HP_DA_PHYSICAL (
      COMPUTER_SYS_ID      	VARCHAR2(64)  NOT NULL,
	SLOT_PORT_BAY		VARCHAR2(10) NOT NULL ,
	MODEL				VARCHAR2(41) NULL ,
	FIRMWARE_REV 		VARCHAR2(9) NULL ,
	DRIVE_SIZE			VARCHAR2(10) NULL ,
	HOT_PLUGGABLE		VARCHAR2(8) NULL ,
	SERIAL_NUMBER		VARCHAR2(41) NULL ,
	DRIVE_SPEED			VARCHAR2(8) NULL,
	DRIVE_TYPE			VARCHAR2(8) NULL,
	SATA_VERSION		VARCHAR2(8) NULL,
	RECORD_TIME          DATE     DEFAULT SYSDATE,
CONSTRAINT HPDAPHYSICAL_PK PRIMARY KEY(COMPUTER_SYS_ID, SLOT_PORT_BAY),
CONSTRAINT HPDAPHYSICAL_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID));

CREATE OR REPLACE TRIGGER HPDAPHYSICAL_TR 
BEFORE UPDATE ON HP_DA_PHYSICAL
FOR EACH ROW
BEGIN
     :NEW.RECORD_TIME := SYSDATE;
END;
/


DROP TABLE HP_ATA_DISK; 

CREATE TABLE HP_ATA_DISK (
      COMPUTER_SYS_ID      	VARCHAR2(64)  NOT NULL,
	DISK_INDEX			INTEGER NOT NULL,
	DISK_SIZE			VARCHAR2(15) NULL,
	CONTROLLER_SLOT		INTEGER NULL,
	DISK_MODEL			VARCHAR2(41) NULL,
	DISK_FIRMWARE		VARCHAR2(9) NULL,
	DISK_SERIAL_NUMBER	VARCHAR2(21) NULL,
	DISK_TYPE			VARCHAR2(5) NULL,
	SATA_VERSION		VARCHAR2(10) NULL,
	RECORD_TIME          DATE     DEFAULT SYSDATE,
CONSTRAINT HPATADISK_PK PRIMARY KEY(COMPUTER_SYS_ID, DISK_INDEX),
CONSTRAINT HPATADISK_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID))

CREATE OR REPLACE TRIGGER HPATADISK_TR 
BEFORE UPDATE ON HP_ATA_DISK
FOR EACH ROW
BEGIN
     :NEW.RECORD_TIME := SYSDATE;
END;
/

DROP TABLE HP_SAS_HBA; 

CREATE TABLE HP_SAS_HBA (
      COMPUTER_SYS_ID      	VARCHAR2(64)  NOT NULL,
	CONTROLLER_SLOT		INTEGER NOT NULL,
	MODEL				VARCHAR2(50) NULL,
	FIRMWARE			VARCHAR2(17) NULL,
	SERIAL_NUMBER		VARCHAR2(81) NULL,
	RECORD_TIME          DATE     DEFAULT SYSDATE,
CONSTRAINT HPSASHBA_PK PRIMARY KEY(COMPUTER_SYS_ID, CONTROLLER_SLOT),
CONSTRAINT HPSASHBA_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID))

CREATE OR REPLACE TRIGGER HPSASHBA_TR 
BEFORE UPDATE ON HP_SAS_HBA
FOR EACH ROW
BEGIN
     :NEW.RECORD_TIME := SYSDATE;
END;
/

DROP TABLE HP_SAS_LOG_DRV; 

CREATE TABLE HP_SAS_LOG_DRV (
      COMPUTER_SYS_ID      	VARCHAR2(64)  NOT NULL,
	LOGICAL_DRIVE_INDEX	INTEGER NOT NULL,
	HBA_INDEX			INTEGER NULL,
	DRIVE_SIZE			VARCHAR2(15) NULL,
	RECORD_TIME          DATE     DEFAULT SYSDATE,
CONSTRAINT HPSASLOGDRV_PK PRIMARY KEY(COMPUTER_SYS_ID, LOGICAL_DRIVE_INDEX),
CONSTRAINT HPSASLOGDRV_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID))

CREATE OR REPLACE TRIGGER HPSASLOGDRV_TR 
BEFORE UPDATE ON HP_SAS_LOG_DRV
FOR EACH ROW
BEGIN
     :NEW.RECORD_TIME := SYSDATE;
END;
/

DROP TABLE HP_SAS_PHY_DRV; 

CREATE TABLE HP_SAS_PHY_DRV (
      COMPUTER_SYS_ID      	VARCHAR2(64)  NOT NULL,
	PHYSICAL_DRIVE_INDEX	INTEGER NOT NULL,
	HBA_INDEX			INTEGER NULL,
	MODEL				VARCHAR2(41) NULL,
	DRIVE_SIZE			VARCHAR2(15) NULL,
	DRIVE_SPEED			VARCHAR2(10) NULL,
	DRIVE_FIRMWARE		VARCHAR2(9) NULL,
	DRIVE_TYPE			VARCHAR2(10) NULL,
	SERIAL_NUMBER		VARCHAR2(41) NULL,
	HOT_PLUGGABLE		VARCHAR2(10) NULL,
	RECORD_TIME          DATE     DEFAULT SYSDATE,
CONSTRAINT HPSASPHYDRV_PK PRIMARY KEY(COMPUTER_SYS_ID, PHYSICAL_DRIVE_INDEX),
CONSTRAINT HPSASPHYDRV_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID))

CREATE OR REPLACE TRIGGER HPSASPHYDRV_TR 
BEFORE UPDATE ON HP_SAS_PHY_DRV
FOR EACH ROW
BEGIN
     :NEW.RECORD_TIME := SYSDATE;
END;
/

----------------

DROP TABLE HP_STORAGE_SYSTEM;
 
CREATE TABLE HP_STORAGE_SYSTEM (
      COMPUTER_SYS_ID      	VARCHAR2(64)  NOT NULL,
	STORAGE_INDEX		INTEGER NOT NULL ,
	MODEL				VARCHAR2(17) NULL ,
	CONTROLLER_SLOT		INTEGER NULL ,
	SCSI_BUS_INDEX		INTEGER NULL ,
	FIRMWARE_REV		VARCHAR2(8) NULL ,
	CONNECT_TYPE		VARCHAR2(8) NULL,
	VENDOR			VARCHAR2(9) NULL,
	RECORD_TIME          DATE     DEFAULT SYSDATE,
CONSTRAINT HPSTORAGESYSTEM_PK PRIMARY KEY(COMPUTER_SYS_ID, STORAGE_INDEX),
CONSTRAINT HPSTORAGESYSTEM_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID));

CREATE OR REPLACE TRIGGER HPSTORAGESYSTEM_TR 
BEFORE UPDATE ON HP_STORAGE_SYSTEM
FOR EACH ROW
BEGIN
     :NEW.RECORD_TIME := SYSDATE;
END;
/

DROP TABLE HP_SOFTWARE_VERSIONS;
 
CREATE TABLE HP_SOFTWARE_VERSIONS (
      COMPUTER_SYS_ID      	VARCHAR2(64)  NOT NULL,
	NAME				VARCHAR2(127) NOT NULL ,
	DESCRIPTION			VARCHAR2(127) NULL ,
	VERSION			VARCHAR2(50) NULL,
	RECORD_TIME          DATE     DEFAULT SYSDATE,
CONSTRAINT HPSOFTWAREVERSIONS_PK PRIMARY KEY(COMPUTER_SYS_ID, NAME),
CONSTRAINT HPSOFTWAREVERSIONS_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID));

CREATE OR REPLACE TRIGGER HPSOFTWAREVERSIONS_TR 
BEFORE UPDATE ON HP_SOFTWARE_VERSIONS
FOR EACH ROW
BEGIN
     :NEW.RECORD_TIME := SYSDATE;
END;
/

DROP TABLE HP_RIB_INFO;

 
CREATE TABLE HP_RIB_INFO (
      COMPUTER_SYS_ID      	VARCHAR2(64)  NOT NULL,
	SERIAL_NUMBER		VARCHAR2(24) NULL ,
	FIRMWARE_REV		VARCHAR2(24) NULL ,
	VIDEO_STATUS		VARCHAR2(24) NULL ,
	KEYBOARD_STATUS		VARCHAR2(24) NULL ,
	ALERT_STATUS		VARCHAR2(24) NULL ,
	BATTERY_STATUS		VARCHAR2(24) NULL,
	RECORD_TIME          DATE     DEFAULT SYSDATE,
CONSTRAINT HPRIBINFO_PK PRIMARY KEY(COMPUTER_SYS_ID),
CONSTRAINT HPRIBINFO_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID));

CREATE OR REPLACE TRIGGER HPRIBINFO_TR 
BEFORE UPDATE ON HP_RIB_INFO
FOR EACH ROW
BEGIN
     :NEW.RECORD_TIME := SYSDATE;
END;
/

DROP TABLE HP_CLUSTER_SUBSYSTEM;

 
CREATE TABLE HP_CLUSTER_SUBSYSTEM (
      COMPUTER_SYS_ID      	VARCHAR2(64)  NOT NULL,
	NODE_NAME			VARCHAR2(24) NULL ,
	NODE_INDEX			INTEGER NULL ,
	CLUSTER_NAME		VARCHAR2(64) NULL ,
	CLUSTER_IP_ADDR		VARCHAR2(64) NULL,
	RECORD_TIME          DATE     DEFAULT SYSDATE,
CONSTRAINT HPCLUSTERSUBSYSTEM_PK PRIMARY KEY(COMPUTER_SYS_ID),
CONSTRAINT HPCLUSTERSUBSYSTEM_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID));

CREATE OR REPLACE TRIGGER HPCLUSTERSUBSYSTEM_TR 
BEFORE UPDATE ON HP_CLUSTER_SUBSYSTEM
FOR EACH ROW
BEGIN
     :NEW.RECORD_TIME := SYSDATE;
END;
/

DROP TABLE HP_CLUSTER_INTERCONNECT;

 
CREATE TABLE HP_CLUSTER_INTERCONNECT (
      COMPUTER_SYS_ID      	VARCHAR2(64)  NOT NULL,
	INTERCONN_PHYSICAL_ID	VARCHAR2(64) NULL  ,
	INTERCONN_TRANSPORT	VARCHAR2(64) NULL ,
	INTERCONN_ADDRESS		VARCHAR2(64) NULL,
	RECORD_TIME          DATE     DEFAULT SYSDATE,
CONSTRAINT HPCLUSTERINTERCONNECT_PK PRIMARY KEY(COMPUTER_SYS_ID),
CONSTRAINT HPCLUSTERINTERCONNECT_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID));

CREATE OR REPLACE TRIGGER HPCLUSTERINTERCONNECT_TR 
BEFORE UPDATE ON HP_CLUSTER_INTERCONNECT
FOR EACH ROW
BEGIN
     :NEW.RECORD_TIME := SYSDATE;
END;
/

DROP TABLE HP_FCA_CONTROLLER;

 
CREATE TABLE HP_FCA_CONTROLLER (
      COMPUTER_SYS_ID      	VARCHAR2(64) NOT NULL,
	MODEL				VARCHAR2(64) NULL ,
	FIRMWARE_REV		VARCHAR2(5) NULL ,
	SERIAL_NUMBER		VARCHAR2(32) NULL ,
	SLOT_INDEX			INTEGER NOT NULL,
	RECORD_TIME          DATE     DEFAULT SYSDATE,
CONSTRAINT HPFCACONTROLLER_PK PRIMARY KEY(COMPUTER_SYS_ID, SLOT_INDEX),
CONSTRAINT HPFCACONTROLLER_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID));

CREATE OR REPLACE TRIGGER HPFCACONTROLLER_TR 
BEFORE UPDATE ON HP_FCA_CONTROLLER
FOR EACH ROW
BEGIN
     :NEW.RECORD_TIME := SYSDATE;
END;
/

DROP TABLE HP_FCA_HOST_CONTROLLER;

 
CREATE TABLE HP_FCA_HOST_CONTROLLER (
      COMPUTER_SYS_ID      	VARCHAR2(64)  NOT NULL,
	MODEL				VARCHAR2(72) NULL ,
	SLOT_INDEX			INTEGER NOT NULL ,
	WORLD_WIDE_NAME		VARCHAR2(17) NULL,
	RECORD_TIME          DATE     DEFAULT SYSDATE,
CONSTRAINT HPFCAHOSTCONTROLLER_PK PRIMARY KEY(COMPUTER_SYS_ID, SLOT_INDEX),
CONSTRAINT HPFCAHOSTCONTROLLER_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID));

CREATE OR REPLACE TRIGGER HPFCAHOSTCONTROLLER_TR 
BEFORE UPDATE ON HP_FCA_HOST_CONTROLLER
FOR EACH ROW
BEGIN
     :NEW.RECORD_TIME := SYSDATE;
END;
/

DROP TABLE HP_FCA_LOGICAL_DRIVES;

 
CREATE TABLE HP_FCA_LOGICAL_DRIVES (
      COMPUTER_SYS_ID      	VARCHAR2(64)  NOT NULL,
	STORAGE_BOX_INDEX		INTEGER NOT NULL ,
	LOGICAL_DRIVE_INDEX	INTEGER NOT NULL ,
	FAULT_TOLERANCE		VARCHAR2(24) NULL ,
	LOGICAL_DRIVE_SIZE	VARCHAR2(10) NULL ,
	PHYSICAL_DRIVE_IDS	VARCHAR2(10) NULL ,
	ACCELERATOR_STATUS	VARCHAR2(16) NULL,
	RECORD_TIME          DATE     DEFAULT SYSDATE, 
CONSTRAINT HPFCALOGICALDRIVES_PK PRIMARY KEY(COMPUTER_SYS_ID, STORAGE_BOX_INDEX, LOGICAL_DRIVE_INDEX),
CONSTRAINT HPFCALOGICALDRIVES_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID));

CREATE OR REPLACE TRIGGER HPFCALOGICALDRIVES_TR 
BEFORE UPDATE ON HP_FCA_LOGICAL_DRIVES
FOR EACH ROW
BEGIN
     :NEW.RECORD_TIME := SYSDATE;
END;
/

DROP TABLE HP_FCA_PHYSICAL_DRIVES;

 
CREATE TABLE HP_FCA_PHYSICAL_DRIVES (
      COMPUTER_SYS_ID      	VARCHAR2(64)  NOT NULL,
	STORAGE_BOX_INDEX		INTEGER NOT NULL ,
	DRIVE_INDEX			INTEGER NOT NULL ,
	MODEL				VARCHAR2(41) NULL ,
	DRIVE_SIZE			VARCHAR2(10) NULL ,
	FIRMWARE_REV		VARCHAR2(9) NULL ,
	SERIAL_NUMBER		VARCHAR2(41) NULL,
	DRIVE_SPEED			VARCHAR2(8) NULL,
	DRIVE_TYPE			VARCHAR2(8) NULL,
	SATA_VERSION		VARCHAR2(8) NULL,
	RECORD_TIME          DATE     DEFAULT SYSDATE,
CONSTRAINT HPFCAPHYSICALDRIVES_PK PRIMARY KEY(COMPUTER_SYS_ID, STORAGE_BOX_INDEX, DRIVE_INDEX),
CONSTRAINT HPFCAPHYSICALDRIVES_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID));

CREATE OR REPLACE TRIGGER HPFCAPHYSICALDRIVES_TR 
BEFORE UPDATE ON HP_FCA_PHYSICAL_DRIVES
FOR EACH ROW
BEGIN
     :NEW.RECORD_TIME := SYSDATE; 
END;
/

DROP TABLE HP_PORTABLE_BATTERIES;

 
CREATE TABLE HP_PORTABLE_BATTERIES (
      COMPUTER_SYS_ID      	VARCHAR2(64)  NOT NULL,
	BATTERY_INDEX		INTEGER NULL ,
	MANUFACTURER		VARCHAR2(80) NULL ,
	BATTERY_DATE		VARCHAR2(10) NULL ,
	SERIAL_NUMBER		VARCHAR2(16) NULL ,
	ASSET_TAG			VARCHAR2(16) NULL ,
	CHEMISTRY			VARCHAR2(80) NULL,
	RECORD_TIME          DATE     DEFAULT SYSDATE,
CONSTRAINT HPPORTABLEBATTERIES_PK PRIMARY KEY(COMPUTER_SYS_ID),
CONSTRAINT HPPORTABLEBATTERIES_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID));

CREATE OR REPLACE TRIGGER HPPORTABLEBATTERIES_TR 
BEFORE UPDATE ON HP_PORTABLE_BATTERIES
FOR EACH ROW
BEGIN
     :NEW.RECORD_TIME := SYSDATE; 
END;
/

DROP TABLE HP_DOCKING_STATION;

 
CREATE TABLE HP_DOCKING_STATION (
      COMPUTER_SYS_ID      	VARCHAR2(64)  NOT NULL,
	DOCK_STATUS			VARCHAR2(8) NULL ,
	MODEL_NAME			VARCHAR2(128) NULL ,
	SERIAL_NUMBER		VARCHAR2(16) NULL ,
	ASSET_TAG			VARCHAR2(16) NULL ,
	ROM_REVISION		VARCHAR2(10) NULL,
	RECORD_TIME          DATE     DEFAULT SYSDATE,
CONSTRAINT HPDOCKINGSTATION_PK PRIMARY KEY(COMPUTER_SYS_ID),
CONSTRAINT HPDOCKINGSTATION_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID));

CREATE OR REPLACE TRIGGER HPDOCKINGSTATION_TR 
BEFORE UPDATE ON HP_DOCKING_STATION
FOR EACH ROW
BEGIN
     :NEW.RECORD_TIME := SYSDATE; 
END;
/


DROP TABLE HP_PORTABLE_PC_CARD_SLOTS;

 
CREATE TABLE HP_PORTABLE_PC_CARD_SLOTS (
      COMPUTER_SYS_ID      	VARCHAR2(64)  NOT NULL,
	PC_CARD_INDEX		INTEGER NULL ,
	SLOT_LOCATION		VARCHAR2(40) NULL ,
	CARD_PRESENCE		VARCHAR2(3) NULL ,
	CARD_STATUS			VARCHAR2(80) NULL ,
	MANUFACTURER_NAME		VARCHAR2(80) NULL ,
	DEVICE_NAME			VARCHAR2(80) NULL,
	RECORD_TIME             DATE     DEFAULT SYSDATE,
CONSTRAINT HPPORTABLEPCCARDSLOTS_PK PRIMARY KEY(COMPUTER_SYS_ID),
CONSTRAINT HPPORTABLEPCCARDSLOTS_FK FOREIGN KEY(COMPUTER_SYS_ID)
REFERENCES COMPUTER(COMPUTER_SYS_ID));

CREATE OR REPLACE TRIGGER HPPORTABLEPCCARDSLOTS_TR 
BEFORE UPDATE ON HP_PORTABLE_PC_CARD_SLOTS
FOR EACH ROW
BEGIN
     :NEW.RECORD_TIME := SYSDATE;
END;
/

delete from INVENTORY_TABLES where TABLE_NAME = 'HP_CPU' ;
delete from INVENTORY_TABLES where TABLE_NAME = 'HP_EISA_PCI_SLOTS' ;
delete from INVENTORY_TABLES where TABLE_NAME = 'HP_SYSTEM' ;
delete from INVENTORY_TABLES where TABLE_NAME = 'HP_MEMORY_MODULES' ;
delete from INVENTORY_TABLES where TABLE_NAME = 'HP_IDE_DEVICES' ;
delete from INVENTORY_TABLES where TABLE_NAME = 'HP_SCSI_DEVICES' ;
delete from INVENTORY_TABLES where TABLE_NAME = 'HP_VIDEO_MONITOR' ;
delete from INVENTORY_TABLES where TABLE_NAME = 'HP_HEALTH_SUBSYSTEM' ;
delete from INVENTORY_TABLES where TABLE_NAME = 'HP_DRIVE_ARRAY_CONTROLLER' ;
delete from INVENTORY_TABLES where TABLE_NAME = 'HP_DA_LOGICAL' ;
delete from INVENTORY_TABLES where TABLE_NAME = 'HP_DA_PHYSICAL' ;
delete from INVENTORY_TABLES where TABLE_NAME = 'HP_ATA_DISK' ;
delete from INVENTORY_TABLES where TABLE_NAME = 'HP_SAS_HBA' ;
delete from INVENTORY_TABLES where TABLE_NAME = 'HP_SAS_LOG_DRV' ;
delete from INVENTORY_TABLES where TABLE_NAME = 'HP_SAS_PHY_DRV' ;
delete from INVENTORY_TABLES where TABLE_NAME = 'HP_STORAGE_SYSTEM' ;
delete from INVENTORY_TABLES where TABLE_NAME = 'HP_SOFTWARE_VERSIONS' ;
delete from INVENTORY_TABLES where TABLE_NAME = 'HP_RIB_INFO' ;
delete from INVENTORY_TABLES where TABLE_NAME = 'HP_CLUSTER_SUBSYSTEM' ;
delete from INVENTORY_TABLES where TABLE_NAME = 'HP_FCA_CONTROLLER' ;
delete from INVENTORY_TABLES where TABLE_NAME = 'HP_CLUSTER_INTERCONNECT' ;
delete from INVENTORY_TABLES where TABLE_NAME = 'HP_FCA_HOST_CONTROLLER' ;
delete from INVENTORY_TABLES where TABLE_NAME = 'HP_FCA_LOGICAL_DRIVES' ;
delete from INVENTORY_TABLES where TABLE_NAME = 'HP_FCA_PHYSICAL_DRIVES' ;
delete from INVENTORY_TABLES where TABLE_NAME = 'HP_PORTABLE_BATTERIES' ;
delete from INVENTORY_TABLES where TABLE_NAME = 'HP_DOCKING_STATION' ;
delete from INVENTORY_TABLES where TABLE_NAME = 'HP_PORTABLE_PC_CARD_SLOTS' ;

commit; 

insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_CPU') ;
insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_EISA_PCI_SLOTS') ;
insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_SYSTEM') ;
insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_MEMORY_MODULES') ;
insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_IDE_DEVICES') ;
insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_SCSI_DEVICES') ;
insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_VIDEO_MONITOR') ;
insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_HEALTH_SUBSYSTEM') ;
insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_DRIVE_ARRAY_CONTROLLER') ;
insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_DA_LOGICAL') ;
insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_DA_PHYSICAL') ;
insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_ATA_DISK') ;
insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_SAS_HBA') ;
insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_SAS_LOG_DRV') ;
insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_SAS_PHY_DRV') ;
insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_STORAGE_SYSTEM') ;
insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_SOFTWARE_VERSIONS') ;
insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_RIB_INFO') ;
insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_CLUSTER_SUBSYSTEM') ;
insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_FCA_CONTROLLER') ;
insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_CLUSTER_INTERCONNECT') ;
insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_FCA_HOST_CONTROLLER') ;
insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_FCA_LOGICAL_DRIVES') ;
insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_FCA_PHYSICAL_DRIVES') ;
insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_PORTABLE_BATTERIES') ;
insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_DOCKING_STATION') ;
insert into INVENTORY_TABLES (TABLE_NAME) values ('HP_PORTABLE_PC_CARD_SLOTS') ;

commit; 
