Inventory Schema

This document contains details about the inventory database schema.

DNS and Mac Address tables

--
-- Table structure for table `inv_dns`
--

CREATE TABLE IF NOT EXISTS `inv_dns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pKey` int(11) NOT NULL COMMENT 'Foreign Key to inventory table',
  `hostDomainName` varchar(247) NOT NULL DEFAULT '' COMMENT 'host portion of FQDN',
  `domain` varchar(253) NOT NULL DEFAULT '' COMMENT 'domain portion of FQDN',
  `ipAddress` varchar(247) NOT NULL DEFAULT '',
  `macAddressKey` int(11) DEFAULT NULL COMMENT 'Foreign Key to inv_macAddresses table',
  `ipPurpose` varchar(247) NOT NULL DEFAULT '' COMMENT 'fulltext description/purpose',
  `syncWithDns` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0=do not sync, 1=sync',
  PRIMARY KEY (`id`),
  KEY `macAddressfKey` (`macAddressKey`),
  KEY `pKey` (`pKey`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='DNS records for inventory associated items';

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

--
-- Table structure for table `inv_macAddress`
--

CREATE TABLE IF NOT EXISTS `inv_macAddress` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pKey` int(11) NOT NULL COMMENT 'Foreign Key to inventory table',
  `macName` varchar(247) NOT NULL DEFAULT '' COMMENT 'Optional fulltext description, eg., eth0',
  `macAddress` varchar(247) DEFAULT NULL COMMENT 'MAC Address in ww:ww:ww:ww:ww:ww format',
  PRIMARY KEY (`id`),
  KEY `pKey` (`pKey`),
  KEY `macAddress` (`macAddress`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='macAddresses associated with the physical network cards attached to inventory items' 
;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `inv_dns`
--
ALTER TABLE `inv_dns`
  ADD CONSTRAINT `inv_dns_ibfk_2` FOREIGN KEY (`macAddressKey`) REFERENCES `inv_macAddress` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `inv_dns_ibfk_1` FOREIGN KEY (`pKey`) REFERENCES `inventory` (`pKey`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `inv_macAddress`
--
ALTER TABLE `inv_macAddress`
  ADD CONSTRAINT `inv_macAddress_ibfk_1` FOREIGN KEY (`pKey`) REFERENCES `inventory` (`pKey`) ON DELETE CASCADE ON UPDATE NO ACTION;

Inventory Table

See InventoryFields page for comments (at this time).

--
-- Table structure for table `inventory`
--

CREATE TABLE IF NOT EXISTS `inventory` (
  `pKey` int(11) NOT NULL AUTO_INCREMENT,
  `accountNumber` varchar(247) DEFAULT NULL,
  `active` varchar(247) DEFAULT 'y',
  `authUser` varchar(247) DEFAULT NULL,
  `barcode` varchar(247) DEFAULT NULL,
  `comments` mediumtext,
  `contractAFF` varchar(247) DEFAULT NULL,
  `contractBegins` date DEFAULT NULL,
  `contractCost` float DEFAULT NULL,
  `contractEnds` date DEFAULT NULL,
  `contractPurchaseOrder` varchar(247) DEFAULT NULL,
  `contractQuoteNumber` varchar(247) DEFAULT NULL,
  `contractVendor` varchar(247) DEFAULT NULL,
  `cpuModel` varchar(247) DEFAULT NULL,
  `description` mediumtext,
  `dns_admin` varchar(247) DEFAULT NULL,
  `dns_contact` varchar(247) DEFAULT NULL,
  `edocsUrl` varchar(250) DEFAULT NULL,
  `entered` date DEFAULT NULL,
  `equipmentType` varchar(247) DEFAULT NULL,
  `fixedAssetTag` varchar(247) DEFAULT NULL,
  `found` date DEFAULT NULL,
  `groups` varchar(247) DEFAULT NULL,
  `hardware` varchar(247) DEFAULT NULL,
  `lastModified` varchar(247) DEFAULT NULL,
  `memory` varchar(247) DEFAULT NULL,
  `model` varchar(247) DEFAULT NULL,
  `numberOfCPUs` int(11) DEFAULT NULL,
  `numberOfCores` int(11) DEFAULT NULL,
  `operatingSystem` varchar(247) DEFAULT NULL,
  `operatingSystemSNMP` varchar(247) DEFAULT NULL,
  `parentBarcode` varchar(247) DEFAULT NULL,
  `policyEight` varchar(100) NOT NULL DEFAULT 'Restricted',
  `processor` varchar(247) DEFAULT NULL,
  `purchaseCost` varchar(247) DEFAULT NULL,
  `purchaseOrder` varchar(247) DEFAULT NULL,
  `purpose` mediumtext,
  `region` varchar(247) DEFAULT NULL,
  `room` varchar(247) DEFAULT NULL,
  `serialNumber` varchar(247) DEFAULT NULL,
  `special` varchar(247) DEFAULT NULL,
  `speed` varchar(247) DEFAULT NULL,
  `sponsorCode` varchar(32) DEFAULT NULL,
  `subscriptionCode` varchar(247) DEFAULT NULL,
  `supportAFF` varchar(247) DEFAULT NULL,
  `supportBegins` varchar(247) DEFAULT NULL,
  `supportClass` varchar(247) DEFAULT NULL,
  `supportEnds` varchar(247) DEFAULT NULL,
  `systemDescription` varchar(247) DEFAULT NULL,
  `systemDescriptionDate` date DEFAULT NULL,
  `unit` varchar(247) DEFAULT NULL,
  `vendor` varchar(247) DEFAULT NULL,
  `warrantyStart` date DEFAULT NULL,
  `warrantyStop` date DEFAULT NULL,
  PRIMARY KEY (`pKey`),
  UNIQUE KEY `barcode` (`barcode`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

DNS Cache

--
-- Table structure for table `aux_hosts`
--

CREATE TABLE IF NOT EXISTS `aux_hosts` (
  `id` varchar(255) NOT NULL COMMENT 'host name for infoblox record - infoblox's primary key',
  `ip` int(10) unsigned NOT NULL COMMENT 'IP address, numeric format',
  `ipa` varchar(15) NOT NULL COMMENT 'IP address, dotted quad (ascii) format',
  `mac` varchar(17) DEFAULT NULL COMMENT 'MAC address, separated by colons',
  `CSasset` varchar(16) DEFAULT NULL COMMENT 'CS asset number reported by infoblox. Format: nnnnn.mmmmm. (n is the CS pKey, and m is the CS inv_dns id).  A CS pKey can associate with more than one host.',
  `pKey` varchar(16) DEFAULT NULL COMMENT 'derived CS pKey',
  `DNSid` varchar(16) DEFAULT NULL COMMENT 'derived CS inv_dns id',
  `disabled` tinyint(1) DEFAULT NULL COMMENT 'infoblox 0=enabled 1=disabled',
  `BusinessContact` varchar(255) DEFAULT NULL COMMENT 'fulltext email address of business contact',
  `TechnicalContact` varchar(255) DEFAULT NULL COMMENT 'fulltext email address of technical contact',
  `PolicyEight` varchar(255) DEFAULT NULL COMMENT 'fulltext allowable PolicyEight value',
  `LegacyAdminID` varchar(255) DEFAULT NULL COMMENT 'legacy: Maintain admin email address',
  `LegacyContactID` varchar(255) DEFAULT NULL COMMENT 'legacy: Maintain contact email address',
  `comment` varchar(255) DEFAULT NULL COMMENT 'fulltext infoblox comment field',
  `LocalUpdate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Other tables

--
-- Table structure for table `attachments`
--
CREATE TABLE IF NOT EXISTS `attachments` (
  `aKey` int(11) NOT NULL AUTO_INCREMENT,
  `pKey` int(11) NOT NULL,
  `suffix` varchar(32) NOT NULL,
  `bytes` bigint(20) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `filename` varchar(247) NOT NULL,
  `comment` varchar(247) DEFAULT NULL,
  `type` varchar(247) DEFAULT NULL,
  `who` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`aKey`),
  KEY `pKey` (`pKey`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

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

--
-- Table structure for table `aux_barcode_prefix`
--

CREATE TABLE IF NOT EXISTS `aux_barcode_prefix` (
  `prefix` char(2) NOT NULL DEFAULT '',
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`prefix`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `aux_buildings`
--

CREATE TABLE IF NOT EXISTS `aux_buildings` (
  `prefix` varchar(8) NOT NULL DEFAULT '',
  `numberOfDigits` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`prefix`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `aux_charges`
--

CREATE TABLE IF NOT EXISTS `aux_charges` (
  `type` varchar(32) DEFAULT NULL,
  `architecture` varchar(247) DEFAULT NULL,
  `connection` int(11) DEFAULT NULL,
  `administration` int(11) DEFAULT NULL,
  `service` int(11) DEFAULT NULL,
  `damping` float DEFAULT NULL,
  `subsidy` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `aux_cname`
--

CREATE TABLE IF NOT EXISTS `aux_cname` (
  `cname` varchar(50) NOT NULL,
  PRIMARY KEY (`cname`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `aux_domain`
--

CREATE TABLE IF NOT EXISTS `aux_domain` (
  `domain` varchar(50) NOT NULL,
  `type` varchar(50) NOT NULL DEFAULT 'user',
  PRIMARY KEY (`domain`),
  KEY `domain` (`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `aux_equipment_type`
--

CREATE TABLE IF NOT EXISTS `aux_equipment_type` (
  `value` varchar(32) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `aux_groups`
--

CREATE TABLE IF NOT EXISTS `aux_groups` (
  `gr_unit` varchar(8) NOT NULL DEFAULT '',
  `gr_name` varchar(255) NOT NULL DEFAULT '',
  `gr_title` varchar(255) DEFAULT NULL,
  `gr_subTitle` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`gr_unit`,`gr_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `aux_licences`
--

CREATE TABLE IF NOT EXISTS `aux_licences` (
  `lKey` int(11) NOT NULL AUTO_INCREMENT,
  `licenceCharge` varchar(247) DEFAULT NULL,
  `licName` varchar(247) DEFAULT NULL,
  `licenceName` varchar(247) DEFAULT NULL,
  PRIMARY KEY (`lKey`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

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

--
-- Table structure for table `aux_policyEight`
--

CREATE TABLE IF NOT EXISTS `aux_policyEight` (
  `value` varchar(32) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `aux_ranges`
--

CREATE TABLE IF NOT EXISTS `aux_ranges` (
  `id` varchar(247) NOT NULL,
  `name` varchar(247) NOT NULL,
  `start` int(10) unsigned NOT NULL,
  `end` int(10) unsigned NOT NULL,
  `comment` varchar(247) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `aux_ranges_tmp`
--

CREATE TABLE IF NOT EXISTS `aux_ranges_tmp` (
  `id` varchar(247) NOT NULL,
  `name` varchar(247) NOT NULL,
  `start` int(10) unsigned NOT NULL,
  `end` int(10) unsigned NOT NULL,
  `comment` varchar(247) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `aux_services`
--

CREATE TABLE IF NOT EXISTS `aux_services` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ServiceName` varchar(75) NOT NULL,
  `Monitored` tinyint(1) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2752 ;

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

--
-- Table structure for table `aux_special`
--

CREATE TABLE IF NOT EXISTS `aux_special` (
  `value` varchar(32) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `aux_sponsors`
--

CREATE TABLE IF NOT EXISTS `aux_sponsors` (
  `unit` varchar(8) NOT NULL DEFAULT '',
  `code` varchar(32) NOT NULL DEFAULT '',
  `userid` varchar(32) NOT NULL DEFAULT '',
  `fullName` varchar(64) NOT NULL DEFAULT '',
  `department` varchar(255) NOT NULL DEFAULT '',
  `description` text,
  KEY `unit` (`unit`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `aux_support_class`
--

CREATE TABLE IF NOT EXISTS `aux_support_class` (
  `value` varchar(32) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `history`
--

CREATE TABLE IF NOT EXISTS `history` (
  `id` int(16) NOT NULL AUTO_INCREMENT,
  `pKey` int(11) DEFAULT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `userName` varchar(32) DEFAULT NULL,
  `fieldName` varchar(32) DEFAULT NULL,
  `oldValue` varchar(255) DEFAULT NULL,
  `newValue` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pKey` (`pKey`),
  KEY `fieldName` (`fieldName`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=147494 ;

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

--
-- Stand-in structure for view `inv_view`
--
CREATE TABLE IF NOT EXISTS `inv_view` (
`pKey` int(11)
,`accountNumber` varchar(247)
,`active` varchar(247)
,`authUser` varchar(247)
,`barcode` varchar(247)
,`comments` mediumtext
,`contractAFF` varchar(247)
,`contractBegins` date
,`contractCost` float
,`contractEnds` date
,`contractPurchaseOrder` varchar(247)
,`contractQuoteNumber` varchar(247)
,`contractVendor` varchar(247)
,`cpuModel` varchar(247)
,`description` mediumtext
,`dns_admin` varchar(247)
,`dns_contact` varchar(247)
,`edocsUrl` varchar(250)
,`entered` date
,`equipmentType` varchar(247)
,`fixedAssetTag` varchar(247)
,`found` date
,`groups` varchar(247)
,`hardware` varchar(247)
,`lastModified` varchar(247)
,`memory` varchar(247)
,`model` varchar(247)
,`numberOfCPUs` int(11)
,`numberOfCores` int(11)
,`operatingSystem` varchar(247)
,`operatingSystemSNMP` varchar(247)
,`parentBarcode` varchar(247)
,`policyEight` varchar(100)
,`processor` varchar(247)
,`purchaseCost` varchar(247)
,`purchaseOrder` varchar(247)
,`purpose` mediumtext
,`region` varchar(247)
,`room` varchar(247)
,`serialNumber` varchar(247)
,`special` varchar(247)
,`speed` varchar(247)
,`sponsorCode` varchar(32)
,`subscriptionCode` varchar(247)
,`supportAFF` varchar(247)
,`supportBegins` varchar(247)
,`supportClass` varchar(247)
,`supportEnds` varchar(247)
,`systemDescription` varchar(247)
,`systemDescriptionDate` date
,`unit` varchar(247)
,`vendor` varchar(247)
,`warrantyStart` date
,`warrantyStop` date
,`hostDomainName` text
,`domain` text
,`ipAddress` text
,`macAddress` text
);
-- --------------------------------------------------------

--
-- Table structure for table `licences`
--

CREATE TABLE IF NOT EXISTS `licences` (
  `lKey` int(11) NOT NULL DEFAULT '0',
  `pKey` int(11) NOT NULL DEFAULT '9',
  `licenceSponsor` varchar(32) DEFAULT NULL,
  `startDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `licenceNumber` varchar(247) DEFAULT NULL,
  `licenceComments` varchar(247) DEFAULT NULL,
  PRIMARY KEY (`lKey`,`pKey`),
  KEY `lKey` (`lKey`),
  KEY `pKey` (`pKey`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `loan`
--

CREATE TABLE IF NOT EXISTS `loan` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `barcode` varchar(247) NOT NULL,
  `cabinet` varchar(247) NOT NULL DEFAULT 'DC2560g',
  `date_out` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_in` timestamp NULL DEFAULT NULL,
  `given_to` varchar(247) NOT NULL,
  `given_by` varchar(247) NOT NULL,
  `due_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=561 ;

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

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `userid` varchar(32) NOT NULL DEFAULT '',
  `comment` varchar(255) DEFAULT NULL,
  `expires` date NOT NULL DEFAULT '0000-00-00',
  `unit` varchar(4) NOT NULL DEFAULT '',
  `suFlag` char(1) DEFAULT NULL,
  `swFlag` char(1) DEFAULT NULL,
  `flags` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Structure for view `inv_view`
--
DROP TABLE IF EXISTS `inv_view`;
-- in use(#1142 - SHOW VIEW command denied to user 'drallen'@'www152.cs.uwaterloo.ca' for table 'inv_view')

--
-- Constraints for dumped tables
--

--
-- Constraints for table `attachments`
--
ALTER TABLE `attachments`
  ADD CONSTRAINT `attachments_ibfk_1` FOREIGN KEY (`pKey`) REFERENCES `inventory` (`pKey`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `licences`
--
ALTER TABLE `licences`
  ADD CONSTRAINT `licences_ibfk_1` FOREIGN KEY (`lKey`) REFERENCES `aux_licences` (`lKey`) ON UPDATE CASCADE,
  ADD CONSTRAINT `licences_ibfk_2` FOREIGN KEY (`pKey`) REFERENCES `inventory` (`pKey`) ON DELETE CASCADE ON UPDATE CASCADE;