Inventory - Alternative schema

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

This is similar to the previous schema - see: Inventory

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

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
time	timestamp	NO		CURRENT_TIMESTAMP

(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)

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

(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

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

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.