---------------
-- VIEWS     --
---------------

drop view HPINVDATA
GO
create view HPINVDATA
as
select 
    COMPUTER.COMPUTER_SYS_ID,
    COMPUTER.TME_OBJECT_ID,
    COMPUTER.TME_OBJECT_LABEL,
    COMPUTER.COMPUTER_SCANTIME,
    IP_ADDR.IP_ADDR,
    IP_ADDR.IP_HOSTNAME,
    COMPUTER.OS_NAME,
    COMPUTER.OS_MAJOR_VERS,
    COMPUTER.OS_MINOR_VERS,
    HP_CPU.CPU_TYPE,
    HP_CPU.CPU_SPEED,
    HP_SYSTEM.SYSTEM_NAME,
    HP_SYSTEM.SYSTEM_ROM_REV,
    HP_SYSTEM.FLASH_ROM,
    HP_SYSTEM.SERIAL_NUMBER,
    HP_SYSTEM.TOTAL_SYS_MEMORY,
    HP_SYSTEM.ASSET_TAG
from
    COMPUTER,IP_ADDR,HP_CPU,HP_SYSTEM
where
    IP_ADDR.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
and
    HP_CPU.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
and
    HP_SYSTEM.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
go

drop view HPSWVER
GO
create view HPSWVER
as
select
    COMPUTER.COMPUTER_SYS_ID,
    COMPUTER.TME_OBJECT_ID,
    COMPUTER.TME_OBJECT_LABEL,
    COMPUTER.COMPUTER_SCANTIME,
    HP_SYSTEM.SYSTEM_NAME,
    HP_SYSTEM.SYSTEM_ROM_REV,
    HP_SYSTEM.FLASH_ROM,
    HP_SOFTWARE_VERSIONS.NAME,
    HP_SOFTWARE_VERSIONS.DESCRIPTION,
    HP_SOFTWARE_VERSIONS.VERSION,
    HP_SYSTEM.RECORD_TIME    
from
    COMPUTER, HP_SOFTWARE_VERSIONS,HP_SYSTEM
where
    HP_SOFTWARE_VERSIONS.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
and
    HP_SYSTEM.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
go

----------------
--- New Views ---
----------------

drop view HPCPU
GO
create view HPCPU
as
select 
    COMPUTER.COMPUTER_SYS_ID,
    COMPUTER.TME_OBJECT_LABEL,
    COMPUTER.TME_OBJECT_ID,
    HP_CPU.CPU_TYPE,
    HP_CPU.CPU_NUMBER,
    HP_CPU.CPU_SPEED,
    HP_CPU.L2_CACHE,
    HP_CPU.L3_CACHE,
    HP_CPU.RECORD_TIME
from
    COMPUTER,HP_CPU
where
    HP_CPU.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
go


drop view HPSLOTS
GO
create view HPSLOTS
as
select 
    COMPUTER.COMPUTER_SYS_ID,
    COMPUTER.TME_OBJECT_LABEL,
    COMPUTER.TME_OBJECT_ID,
    HP_EISA_PCI_SLOTS.SLOT_NUMBER,
    HP_EISA_PCI_SLOTS.BOARD_EISA_PCI_ID,
    HP_EISA_PCI_SLOTS.BOARD_NAME,
    HP_EISA_PCI_SLOTS.RECORD_TIME
from
    COMPUTER,HP_EISA_PCI_SLOTS
where
    HP_EISA_PCI_SLOTS.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
go

drop view HPIDEDEV
GO
create view HPIDEDEV
as
select 
    COMPUTER.COMPUTER_SYS_ID,
    COMPUTER.TME_OBJECT_LABEL,
    COMPUTER.TME_OBJECT_ID,
    HP_IDE_DEVICES.CONTROLLER_DRIVE,
    HP_IDE_DEVICES.NAME,
    HP_IDE_DEVICES.FIRMWARE_REV,
    HP_IDE_DEVICES.DISK_SIZE,
    HP_IDE_DEVICES.SERIAL_NUMBER,
    HP_IDE_DEVICES.RECORD_TIME
from
    COMPUTER,HP_IDE_DEVICES
where
    HP_IDE_DEVICES.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
and
   HP_IDE_DEVICES.TYPE = 'Disk'
go

drop view HPDRVARRY
GO
create view HPDRVARRY
as
select 
    COMPUTER.COMPUTER_SYS_ID,
    COMPUTER.TME_OBJECT_LABEL,
    COMPUTER.TME_OBJECT_ID,
    HP_DRIVE_ARRAY_CONTROLLER.MODEL,
    HP_DA_PHYSICAL.SLOT_PORT_BAY,
    HP_DA_PHYSICAL.FIRMWARE_REV,
    HP_DA_PHYSICAL.DRIVE_SIZE,
    HP_DA_PHYSICAL.HOT_PLUGGABLE
from
    COMPUTER,HP_DRIVE_ARRAY_CONTROLLER,HP_DA_PHYSICAL
where
    HP_DRIVE_ARRAY_CONTROLLER.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
and 
    HP_DA_PHYSICAL.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
go

drop view HPSASDRV
GO
create view HPSASDRV
as
select 
    COMPUTER.COMPUTER_SYS_ID,
    COMPUTER.TME_OBJECT_LABEL,
    COMPUTER.TME_OBJECT_ID,
    HP_SAS_HBA.MODEL,
    HP_SAS_PHY_DRV.HBA_INDEX,
    HP_SAS_PHY_DRV.DRIVE_SIZE,
    HP_SAS_PHY_DRV.DRIVE_SPEED,
    HP_SAS_PHY_DRV.DRIVE_FIRMWARE,
    HP_SAS_PHY_DRV.DRIVE_TYPE,
    HP_SAS_PHY_DRV.HOT_PLUGGABLE
from
    COMPUTER,HP_SAS_HBA,HP_SAS_PHY_DRV
where
    HP_SAS_HBA.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
and 
    HP_SAS_PHY_DRV.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
go

drop view HPATADISK
GO
create view HPATADISK
as
select 
    COMPUTER.COMPUTER_SYS_ID,
    COMPUTER.TME_OBJECT_LABEL,
    COMPUTER.TME_OBJECT_ID,
    HP_ATA_DISK.DISK_INDEX,
    HP_ATA_DISK.DISK_SIZE,
    HP_ATA_DISK.CONTROLLER_SLOT,
    HP_ATA_DISK.DISK_MODEL,
    HP_ATA_DISK.DISK_FIRMWARE,
    HP_ATA_DISK.DISK_TYPE,
    HP_ATA_DISK.SATA_VERSION
from
    COMPUTER,HP_ATA_DISK
where
    HP_ATA_DISK.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
go

drop view HPSCSI
GO
create view HPSCSI
as
select 
    COMPUTER.COMPUTER_SYS_ID,
    COMPUTER.TME_OBJECT_LABEL,
    COMPUTER.TME_OBJECT_ID,
    HP_SCSI_DEVICES.CNTLR_SLOT_SCSI_ID,
    HP_SCSI_DEVICES.NAME,
    HP_SCSI_DEVICES.FIRMWARE_REV,
    HP_SCSI_DEVICES.DISK_SIZE,
    HP_SCSI_DEVICES.HOT_PLUGGABLE,
    HP_SCSI_DEVICES.RECORD_TIME
from
    COMPUTER,HP_SCSI_DEVICES
where
    HP_SCSI_DEVICES.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
and 
    HP_SCSI_DEVICES.TYPE = 'Disk'
go

drop view HPFCA
GO
create view HPFCA
as
select 
    COMPUTER.COMPUTER_SYS_ID,
    COMPUTER.TME_OBJECT_LABEL,
    COMPUTER.TME_OBJECT_ID,
    HP_FCA_CONTROLLER.MODEL,
    HP_FCA_PHYSICAL_DRIVES.STORAGE_BOX_INDEX,
    HP_FCA_PHYSICAL_DRIVES.DRIVE_INDEX,
    HP_FCA_PHYSICAL_DRIVES.DRIVE_SIZE,
    HP_FCA_PHYSICAL_DRIVES.FIRMWARE_REV,
    HP_FCA_PHYSICAL_DRIVES.RECORD_TIME
from
    COMPUTER,HP_FCA_CONTROLLER,HP_FCA_PHYSICAL_DRIVES
where
    HP_FCA_CONTROLLER.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
and
    HP_FCA_PHYSICAL_DRIVES.COMPUTER_SYS_ID =
    COMPUTER.COMPUTER_SYS_ID
go

delete from QUERY_VIEWS where VIEW_NAME = 'HPINVDATA'
go
delete from QUERY_VIEWS where VIEW_NAME = 'HPSWVER'
go
delete from QUERY_VIEWS where VIEW_NAME = 'HPCPU'
go
delete from QUERY_VIEWS where VIEW_NAME = 'HPSLOTS'
go
delete from QUERY_VIEWS where VIEW_NAME = 'HPIDEDEV'
go
delete from QUERY_VIEWS where VIEW_NAME = 'HPDRVARRY'
go
delete from QUERY_VIEWS where VIEW_NAME = 'HPSASDRV'
go
delete from QUERY_VIEWS where VIEW_NAME = 'HPATADISK'
go
delete from QUERY_VIEWS where VIEW_NAME = 'HPSCSI'
go
delete from QUERY_VIEWS where VIEW_NAME = 'HPFCA'
go

insert into QUERY_VIEWS (VIEW_NAME) values ('HPCPU')
insert into QUERY_VIEWS (VIEW_NAME) values ('HPINVDATA')
insert into QUERY_VIEWS (VIEW_NAME) values ('HPSWVER')
insert into QUERY_VIEWS (VIEW_NAME) values ('HPSLOTS')
insert into QUERY_VIEWS (VIEW_NAME) values ('HPIDEDEV')
insert into QUERY_VIEWS (VIEW_NAME) values ('HPDRVARRY')
insert into QUERY_VIEWS (VIEW_NAME) values ('HPSASDRV')
insert into QUERY_VIEWS (VIEW_NAME) values ('HPATADISK')
insert into QUERY_VIEWS (VIEW_NAME) values ('HPSCSI')
insert into QUERY_VIEWS (VIEW_NAME) values ('HPFCA')
go