| | 1 | = Inventory - Alternative schema = |
| | 2 | |
| | 3 | [[TOC(Internal/Inventory,depth=2)]] |
| | 4 | |
| | 5 | This temporary page is there to discuss a potential alternative for the Inventory database schema. |
| | 6 | |
| | 7 | [Thierry] Please feel free to edit it and add any comments/suggestions. |
| | 8 | |
| | 9 | == Database == |
| | 10 | |
| | 11 | Inventory database lives on internal1 and consists of 6 tables: |
| | 12 | |
| | 13 | 1. devices |
| | 14 | 2. interfaces |
| | 15 | 3. motherboards |
| | 16 | 4. nodes |
| | 17 | 5. locations |
| | 18 | 6. testbeds ( = "resources" in the previous schema ) |
| | 19 | |
| | 20 | |
| | 21 | === devices table === |
| | 22 | |
| | 23 | This is similar to the previous schema - see: [wiki:Internal/Inventory Inventory] |
| | 24 | |
| | 25 | === interfaces table === |
| | 26 | |
| | 27 | || Field || Type || Null || Key || Default || Extra || Description || |
| | 28 | || id || int(11) || NO || PRI || 0 || auto_increment || || |
| | 29 | || mboard_id || varchar(64) || NO || || || || Link to a specific 'id' in the 'motherboards' table || |
| | 30 | || device_id || varchar(16) || YES || || NULL || || Link to device_id in devices table || |
| | 31 | || MAC || varchar(17) || YES || UNI || NULL || || MAC address of the interface || |
| | 32 | || ord || int(11) || NO || || 1 || || Interface ordinal number within the node || |
| | 33 | |
| | 34 | |
| | 35 | === motherboard table === |
| | 36 | || Field || Type || Null || Key || Default || Extra || Description || |
| | 37 | || id || varchar(64) || NO || PRI || || || UUID of the motherboard || |
| | 38 | || node_id || varchar(64) || YES || UNI || NULL || || Link to 'id' in nodes table || |
| | 39 | || mboard_sn || varchar(16) || NO || UNI || || || manufacturer serial number of the motherboard || |
| | 40 | || HDSN || varchar(16) || NO || UNI || || || Hard drive serial number || |
| | 41 | || CPU || varchar(X) || YES || || NULL || || CPU Type || |
| | 42 | || speed || int(11) || YES || || 0 || || CPU speed in MHz || |
| | 43 | || memory || int(11) || YES || || 0 || || Memory size in MB || |
| | 44 | || HDSZ || int(11) || YES || || 0 || || Hard disk size in GB || |
| | 45 | || time || timestamp || NO || || CURRENT_TIMESTAMP || || || |
| | 46 | |
| | 47 | (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) |
| | 48 | |
| | 49 | === nodes table === |
| | 50 | || Field || Type || Null || Key || Default || Extra || Description || |
| | 51 | || id || varchar(64) || NO || PRI || || || UUID of the node (i.e. the chassis). || |
| | 52 | || chassis_sn || varchar(16) || NO || UNI || || || Manufacturer serial number of the node's chassis || |
| | 53 | || location_id || varchar(64) || YES || UNI || NULL || || Link to 'id' in 'locations' table || |
| | 54 | |
| | 55 | (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) |
| | 56 | |
| | 57 | === locations table === |
| | 58 | |
| | 59 | || Field || Type || Null || Key || Default || Extra || Description || |
| | 60 | || id || varchar(64) || NO || PRI || || || UUID of the location || |
| | 61 | || x || int(11) || NO || || 0 || || || |
| | 62 | || y || int(11) || NO || || 0 || || || |
| | 63 | || z || int(11) || NO || || 0 || || || |
| | 64 | || unit || int(11) || NO || || 0 || || || |
| | 65 | || testbed_id || varchar(64) || NO || || 0 || || Link to 'id' in 'testbeds' table || |
| | 66 | |
| | 67 | |
| | 68 | === testbeds (resources) table === |
| | 69 | |
| | 70 | || Field || Type || Null || Key || Default || Extra || Description || |
| | 71 | || id || varchar(64) || NO || PRI || || || UUID of the testbed || |
| | 72 | || domain || varchar(4) || NO || UNI || || || || |
| | 73 | || control_ip || varchar(12) || NO || UNI || || || || |
| | 74 | || data_ip || varchar(12) || NO || UNI || || || || |
| | 75 | || cm_ip || varchar(12) || NO || || || || || |
| | 76 | || latitude || int(11) || NO || || 0 || || || |
| | 77 | || longitude || int(11) || NO || || 0 || || || |
| | 78 | || elevation || int(11) || NO || || 0 || || || |
| | 79 | |
| | 80 | == DESCRIPTION == |
| | 81 | |
| | 82 | The design goal of this schema is to allow the double use of the Inventory database as: |
| | 83 | * a source of information for user experiment scripts |
| | 84 | * a 'real' hardware inventory giving operators information on which piece of hardware (chassis, motherboard) is used (or not) in which testbed/location. |
| | 85 | |
| | 86 | The entries in the ''testbeds'', ''locations'', ''nodes'' tables are manually created and updated by operators, when: |
| | 87 | * a new testbed is being deployed |
| | 88 | * 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) |
| | 89 | * 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 |
| | 90 | |
| | 91 | 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...) |
| | 92 | |
| | 93 | 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). |
| | 94 | |
| | 95 | The ''interfaces'' and ''devices'' tables are created and updated as in the previous schema. |
| | 96 | |
| | 97 | |
| | 98 | == DISCUSSION == |
| | 99 | |
| | 100 | 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. |
| | 101 | |
| | 102 | |
| | 103 | 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. |
| | 104 | |
| | 105 | |
| | 106 | 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. |
| | 107 | |
| | 108 | |
| | 109 | 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. |
