Inventory - Alternative schema
Table of Contents
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:
- devices
- interfaces
- motherboards
- nodes
- locations
- 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 |
motherboards 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 | |
| sn | varchar(16) | NO | UNI | manufacturer serial number of the motherboard | ||
| hd_sn | varchar(16) | NO | UNI | Hard drive serial number | ||
| cpu_type | varchar(X) | YES | NULL | CPU Type | ||
| cpu_speed | int(11) | YES | 0 | CPU speed in MHz | ||
| memory | int(11) | YES | 0 | Memory size in MB | ||
| hd_size | int(11) | YES | 0 | Hard disk size in bytes | ||
| 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.
