
--
-- Sun Mar 18 14:58:56 PDT 2012

--
-- Database `lopoco_ops`
--

--
-- Table structure for table `products`
--

create table `products` (
  `prod_id` serial,
  `link` bigint unsigned, -- `id` in tables of products,
  `kind` enum('servers', 'switches') not null default 'servers', -- prd tbl name
  `description` text not null,
  `customer_num` int unsigned, -- 0 means in inventory or RMAd or decomissioned
  primary key (`prod_id`)
) engine=MyISAM default charset=utf8 checksum=1;

--
-- Table structure for product table `servers`
--

create table `servers` (
  `product` bigint unsigned,
  `server_id` serial,
  `serial` varchar(32) not null, -- lopoco serial number
  `case_man` varchar(32) not null, -- case manufacturer
  `case_serial` varchar(32) not null,
  `model` varchar(32) not null,    -- server model name
  `description` text not null,
  `height` varchar(4) default '1U',
  `state` enum('inventory','installed','RMAd','RMAd_returned'),
  `condition` enum('new','used','dead'),
  `created` timestamp not null default CURRENT_TIMESTAMP,
  `updated` timestamp null default null,
  `update_count` bigint unsigned default 0,
  constraint `server_prod`
  foreign key (`product`) references products(`prod_id`), -- link to product tbl
  primary key (`serial`),
  key `id` (`server_id`)
) engine=MyISAM default charset=utf8 row_format=fixed checksum=1;

--
-- Table structure for product table `switches`
--

create table `switches` (
  `product` bigint unsigned unique,
  `switch_id` serial,
  `man` varchar(32) not null, -- switch manufacturer
  `sw_serial` varchar(32) not null, -- manufacturer serial number
  `model` varchar(32) not null,  -- manufacturer model name
  `num_ports` mediumint unsigned not null, -- number of general client ports
  `num_ports_upl` tinyint unsigned not null, -- number of uplink/backbone ports
  `num_ports_total` mediumint unsigned not null, -- total number of ports
  `port_speed` int unsigned not null, -- speed (Mb) of general client ports
  `upl_speed` int unsigned not null, -- speed (Mb) of uplink/backbone ports
  `description` text,
  `state` enum('inventory','installed','RMAd','RMAd_returned'),
  `condition` enum('new','used','dead'),
  `created` timestamp not null default CURRENT_TIMESTAMP,
  `updated` timestamp null default null,
  `update_count` int unsigned default 0,
  constraint `sw_prod`
  foreign key (`product`) references products(`prod_id`), -- link to product tbl
  primary key (`switch_id`),
  key `sw_serial` (`sw_serial`)
) engine=MyISAM default charset=utf8 row_format=fixed checksum=1;

--
-- Table structure for product table `motherboards`
--

create table `motherboards` (
  `product` bigint unsigned unique,
  `idmotherboards` serial,
  `man` varchar(32) not null, -- motherboard manufacturer
  `mobo_serial` varchar(32) not null,
  `model` varchar(32) not null,    -- motherboard model name
  `description` text not null,
  `bios` varchar(32) default 'American Megatrends',
  `bios_ver` varchar(32),
  `state` enum('inventory','installed','RMAd','RMAd_returned') default 'inventory',
  `condition` enum('new','used','dead') default 'new',
  `created` timestamp not null default CURRENT_TIMESTAMP,
  `updated` timestamp null default null,
  `update_count` bigint unsigned default 0,
  constraint `mobo_prod`
  foreign key (`product`) references products(`prod_id`), -- link to product tbl
  primary key (`idmotherboards`),
  key (`mobo_serial`)
) engine=MyISAM default charset=utf8 row_format=fixed checksum=1;

--
-- Table structure for component table `processors`
--

create table `processors` (
  `product` bigint unsigned,
  `proc_man` varchar(32) not null,      -- manufacturer
  `proc_serial` varchar(32) not null unique,   -- man serial number
  `proc_model` varchar(32) not null,    -- manufacturer model
  `ncores` int(4) unsigned not null,    -- number of cores
  `clock` int(8) unsigned not null,     -- clock speed in MHz
  `description` text not null,
  `state` enum('inventory','installed','RMAd','RMAd_returned') default 'inventory',
  `condition` enum('new','used','dead') default 'new',
  `created` timestamp not null default CURRENT_TIMESTAMP,
  `updated` timestamp null default null,
  `update_count` bigint unsigned default 0,
  constraint `proc_prod`
  foreign key (`product`) references products(`prod_id`), -- link to product tbl
  key `proc_man` (`proc_man`),
  key `proc_serial` (`proc_serial`),
  key `proc_model` (`proc_model`)
) engine=MyISAM default charset=utf8 row_format=fixed checksum=1;

--
-- Table structure for component table `ethernet NICs`
--

create table `ethernet_nics` (
  `product` bigint unsigned,
  `eth_man` varchar(32),
  `eth_serial` varchar(32),
  `eth_mac` varchar(17) not null, -- hex(12)
  `model` varchar(32),
  `speed` int(8) not null,
  `onboard` boolean default true, -- on the main mobo
  `nports` smallint default 1,
  `use` enum('ipmi', 'regular', 'cluster_comm', 'combined') default 'regular',
  `conn_type` enum('RJ45', 'BNC', 'fiber', 'copper') default 'RJ45',
  `description` text,
  `state` enum('inventory','installed','RMAd','RMAd_returned') default 'inventory',
  `condition` enum('new','used','dead') default 'new',
  `created` timestamp not null default CURRENT_TIMESTAMP,
  `updated` timestamp null default null,
  `update_count` bigint default 0,
  constraint `eth_prod`
  foreign key (`product`) references products(`prod_id`), -- link to product tbl
  key `eth_serial` (`eth_serial`),
  key `eth_mac` (`eth_mac`),
  key `eth_man` (`eth_man`)
) engine=MyISAM default charset=utf8 row_format=fixed checksum=1;

--
-- Table structure for component table `disk controller HBAs`
--

create table `disk_hbas` (
  `product` bigint unsigned,
  `hba_man` varchar(32) not null,
  `hba_serial` varchar(32) not null unique,
  `model` varchar(32) not null,
  `interface` enum('sata', 'sas', 'scsi', 'fc') not null default 'sata',
  `level` tinyint(1) unsigned default '2', -- sata, sas or scsi level
  `num_ports` int(8) not null,
  `notes` varchar(128),
  `state` enum('inventory','installed','RMAd','RMAd_returned'),
  `condition` enum('new','used','dead'),
  `created` timestamp not null default CURRENT_TIMESTAMP,
  `updated` timestamp null default null,
  `update_count` bigint default 0,
  constraint `hba_prod`
  foreign key (`product`) references products(`prod_id`), -- link to product tbl
  key (`hba_man`),
  key (`hba_serial`)
) engine=MyISAM default charset=utf8 row_format=fixed checksum=1;

--
-- Table structure for component table `disks`
--

create table `disks` (
  `product` bigint(20) unsigned default null,
  `disk_man` varchar(32) not null,
  `disk_model` varchar(32) not null,
  `disk_serial` varchar(32) not null,
  `capacity` int unsigned,
  `wwn` varchar(16) default null, -- The disk WWN - a 16 digit hex number
  `interface` enum('sata','sas','scsi','fc') not null default 'sata',
  `level` tinyint(1) unsigned default '2', -- sata, sas or scsi level
  `notes` varchar(128),
  `state` enum('inventory','installed','RMAd','RMAd_returned'),
  `condition` enum('new','used','dead'),
  `created` timestamp not null default CURRENT_TIMESTAMP,
  `updated` timestamp null default null,
  `update_count` bigint(20) default '0',
  unique key `wwn` (`wwn`),
  constraint `disk_prod`
  foreign key (`product`) references products(`prod_id`), -- link to product tbl
  key `disk_prod` (`product`),
  key `disk_man` (`disk_man`),
  key `disk_serial` (`disk_serial`),
  key `disk_model` (`disk_model`)
) engine=MyISAM default charset=utf8 checksum=1 row_format=fixed;

--
-- Table structure for component table `memory`
--

create table `memory` (
  `product` bigint unsigned,
  `mem_man` varchar(32) not null,      -- manufacturer
  `mem_serial` varchar(32) unique,     -- manufacturer serial number
  `mem_model` varchar(32) not null,    -- manufacturer model
  `size_gb` int(4) unsigned not null,  -- mem module size in GiB
  `clock` int(8) unsigned,             -- clock speed in MHz
  `mem_type` enum('DDR3','DDR2') default 'DDR3',
  `dimm_type` enum('SODIMM','RDIMM','UDIMM','DIMM') default 'UDIMM',
  `ecc` boolean default true,
  `registered` boolean default false,
  `voltage` enum('1.5','1.35') default '1.5',
  `description` varchar(128),
  `state` enum('inventory','installed','RMAd','RMAd_returned'),
  `condition` enum('new','used','dead'),
  `created` timestamp not null default CURRENT_TIMESTAMP,
  `updated` timestamp null default null,
  `update_count` bigint unsigned default 0,
  constraint `mem_prod`
  foreign key (`product`) references products(`prod_id`), -- link to product tbl
  key `mem_man` (`mem_man`),
  key `mem_serial` (`mem_serial`),
  key `mem_model` (`mem_model`)
) engine=MyISAM default charset=utf8 row_format=fixed checksum=1;

