wiki:Internal/Inventory-alternative

Version 3 (modified by Joseph F. Miklojcik III, 17 years ago) ( diff )

copied in device table schema from old page

Inventory - Alternative schema

Table of Contents

    Error: Page Internal/Inventory does not exist

This temporary page is there to discuss a potential alternative for the Inventory database schema.

[Thierry] Please feel free to edit it and add any comments/suggestions.

Database

Inventory database lives on internal1 and consists of 6 tables:

  1. devices
  2. interfaces
  3. motherboards
  4. nodes
  5. locations
  6. testbeds ( = "resources" in the previous schema )

devices table

Field Type Null Key Default Extra Description
ID int(11) NO PRI NULL auto_increment
vendor_id varchar(8) NO 0
device_id varchar(8) NO 0
OUI varchar(8) YES NULL Organizationally Unique Identifier
description varchar(255) NO

interfaces table

Field Type Null Key Default Extra Description
id int(11) NO PRI 0 auto_increment
mboard_id varchar(64) NO Link to a specific 'id' in the 'motherboards' table
device_id varchar(16) YES NULL Link to device_id in devices table
MAC varchar(17) YES UNI NULL MAC address of the interface
ord int(11) NO 1 Interface ordinal number within the node
updated_on timestamp NO CURRENT_TIMESTAMP
updated_by varchar(64) NO

motherboard table

Field Type Null Key Default Extra Description
id varchar(64) NO PRI UUID of the motherboard
node_id varchar(64) YES UNI NULL Link to 'id' in nodes table
mboard_sn varchar(16) NO UNI manufacturer serial number of the motherboard
HDSN varchar(16) NO UNI Hard drive serial number
CPU varchar(X) YES NULL CPU Type
speed int(11) YES 0 CPU speed in MHz
memory int(11) YES 0 Memory size in MB
HDSZ int(11) YES 0 Hard disk size in GB
updated_on timestamp NO CURRENT_TIMESTAMP
updated_by varchar(64) NO

(NOTE: 'node_id' is NULL when this motherboard is not installed on any node, i.e. new parts that just got in, or stored extra/spare parts)

We could also move the hard-drive info in a separate table if we allow hard-drive swapping between motherboards.

nodes table

Field Type Null Key Default Extra Description
id varchar(64) NO PRI UUID of the node (i.e. the chassis).
chassis_sn varchar(16) NO UNI Manufacturer serial number of the node's chassis
location_id varchar(64) YES UNI NULL Link to 'id' in 'locations' table
updated_on timestamp NO CURRENT_TIMESTAMP
updated_by varchar(64) NO

(NOTE: 'location_id' is NULL when this chassis is not installed at any location, i.e. new parts that just got in, or stored extra/spare parts)

locations table

Field Type Null Key Default Extra Description
id varchar(64) NO PRI UUID of the location
x int(11) NO 0
y int(11) NO 0
z int(11) NO 0
unit int(11) NO 0
testbed_id varchar(64) NO 0 Link to 'id' in 'testbeds' table
updated_on timestamp NO CURRENT_TIMESTAMP
updated_by varchar(64) NO

testbeds (resources) table

Field Type Null Key Default Extra Description
id varchar(64) NO PRI UUID of the testbed
domain varchar(4) NO UNI
control_ip varchar(12) NO UNI
data_ip varchar(12) NO UNI
cm_ip varchar(12) NO
latitude int(11) NO 0
longitude int(11) NO 0
elevation int(11) NO 0
updated_on timestamp NO CURRENT_TIMESTAMP
updated_by varchar(64) NO

DESCRIPTION

The design goal of this schema is to allow the double use of the Inventory database as:

  • a source of information for user experiment scripts
  • a 'real' hardware inventory giving operators information on which piece of hardware (chassis, motherboard) is used (or not) in which testbed/location.

The entries in the testbeds, locations, nodes tables are manually created and updated by operators, when:

  • a new testbed is being deployed
  • a new location is added to the testbed (e.g. physical place-holder creation on a sandbox testbed for future addition of a third node)
  • a new purchased chassis (i.e. empty node box) is delivered, or mounted to a new location, or switched from a location to another one

We do not expect these events to happen very often, thus it should be ok to make the operator responsible for creating/updating the related entries. (furthermore he/she could also use some scripts to do this job…)

The entries in the motherboards table are also manually created upon delivery of a new purchased motherboard. The only field that needs to be manually filled by the operator is the node_id, which will happen when the operator installs a new motherboard inside a node/chassis. All the other fields are automatically populated by the Inventory process (i.e. the scripts in the inventory package).

The interfaces and devices tables are created and updated as in the previous schema.

DISCUSSION

The division of the original nodes table into three separate tables gives the operators the freedom to move around hardware (empty chassis/motherboards) between locations within and between testbeds or storage room while still keeping track of what is where. This would be the case when fixing hardware failure in given location by using spare parts or diverting parts from less used/low priority locations/testbeds.

The automatic population of the motherboards and interfaces tables can still be done using a similar process as with the previous Inventory schema. The inventory script on the node would get the motherboard serial number and use it as a key to access the corresponding entry (previously manually created by the operator) in the motherboards table. The script will then populate the remaining fields of this motherboards entry, and finally create+fill entries in the interfaces table based on the results of its probing process.

All the entry IDs proposed in this schema are UUIDs. This is because in future possible testbed federations, a given user experiment might be using resources spanning different testbeds managed by different organizations. If these organizations all use OMF, but each host their own Inventory database, then UUIDs will prevent id collisions in referring to a given resources.

NOTE on node location: we propose to associate a fixed Origin to a given testbed, which will be described as its geographical latitude/longitude/elevation coordinates. Then each node location within this tesbed is described as a x/y/z offset of a certain unit from this Origin. From a user-point of view, only a node id should be used to refer to a node (and not its x/y/z coordinates), users can always query the database if more info is needed. This scheme would also be compatible with the current WINLAB grid with the correct choice of Origin and unit.

Note: See TracWiki for help on using the wiki.