Using the Python HWDB Upload Tool

Overview

Teaching: 120 min
Exercises: 0 min

Questions

• Active user privilage is required.

• How does one upload bulk data with the Python HWDB Upload Tool?

Objectives

• upload a component specification (thus, obtain its assigned DUNE PID)

• update a component specification (for a given PID)

• create and/or update a serial number of the component

• create and/or update the component’s location

• create links to its sub-components

• upload images to the component

• create and/or update the component’s tests

Contents

	Description
Introduction
Requirements
Installation
Configuration
Lesson 1: Some Simple Examples
HWDB Setup
Example 1.1: A very simple example
Example 1.2: Providing Default Values
Example 1.3: Editing Items
Example 1.4: Providing values on command line
Example 1.5: Providing values via a docket file
Example 1.6: Specification fields
Lesson 2: Subcomponents
HWDB Setup
Example 2.1: Linking a subcomponent
Lesson 3: Item Images
Example 3.1: Uploading images
Lesson 4: Tests
Example 4.1: Simple Test
Example 4.2: Complex Structures
Lesson 5: Item Locations
Example 5.1: Uploading location info
Lesson 6: Do them all at once!
QuickStartExample.xlsx: "My Items" sheet
QuickStartExample.xlsx: "My Images" sheet
QuickStartExample.xlsx: "My Tests" sheet
QE-docket.json: "Values" blob
QE-docket.json: "Sources"
QE-docket.json: Encoders: "My Item Encoder"
QE-docket.json: Encoders: "My Image Encoder"
QE-docket.json: Encoders: "My Test Encoder"
Lesson 7: Generating bar/QR-code labels with the Tool
Lesson 8: Filling out the DUNE Shipping checklists with the GUI-based Tool

Introduction

The Python HWDB Upload Tool is an application written in Python that helps users to upload massive data to the HWDB. User provides data by means of spreadsheets to the app. The app then simply uploads them.

The app can also accept a docket file, along with spreadsheets, in which complex database schema can be defined as well.

back to top

Requirements

This software was developed on Ubuntu 20.04 running under WSL for Windows 11, and was tested for compatibility on macOS 14.7 (Sonoma; M1) as well as on Ubuntu 24.04 with an Intel Linux box. We strongly recommend to employ Anaconda (Python distribution), which should get you most of the needed packages for the Tool. It can be obtained from https://www.anaconda.com/download for free and is available for Windows, Linux, and macOS.

back to top

Installation

Follow these instructions to install the Python HWDB API:

• Download the zipped (or tar.gz, whichever you prefer) source code of the Tool from https://github.com/DUNE/DUNE-HWDB-Python/releases/latest.

• Extract the source code to a directory of your choosing. For the purposes of this document, we will assume this directory is $HOME/DUNE-HWDB-Python.

• To have the necessary paths available in your Bash shell, add the following lines to your .bashrc file. (If you are using a different shell, modify accordingly.)

  export HWDB=~/DUNE-HWDB-Python
  export PATH=$HWDB:$HWDB/bin:$HWDB/devtools:$PATH
  export PYTHONPATH=$HWDB/lib:$PYTHONPATH
  

• Although we have attempted to have the executable flag set in GitHub on the appropriate files, it appears to be inconsistent, so you may need to set these manually.
  • In your Bash shell, go to your installation directory ($HOME/DUNE-HWDB-Python)
  • Enter the following to set executable permissions:

    chmod +x hwdb-*
    

• If you don’t have reportlab on your devide, which doesn’t come with the Anaconda distro unfortunately, the Tool will suggest you to install it. The Tool itself will run fine without this extra library, but it will not generate bar/QR-code labels in that case. To install this, simple execute the following command:

  pip install reportlab
  

  or

  conda install conda-forge::reportlab
  

• And as described in these steps, make sure you have htgettoken installed. Again, you could install it via;

  pip install htgettoken
  

  or

  conda install conda-forge::htgettoken
  

back to top

Configuration

Try to execute the following command:

hwdb-configure

If everything is setup correctly, you should see a screen similar to the one below.

This will check to see if your two tokens (Bearer and Vault) are still valid. If your Vault token is expired or if you are running the Tool for the first time, you will be prompted to provide your FNAL SSO credential in your web browser session. Once you are done with the authentication process there, you should see the above screen. The obtained two tokens are saved in $HOME/.sisyphus/.

When your Bearer token is expired, which happens every 3 hours, the token is refreshed automatically in background. Whereas when your Vault token is expired (its lifetime is 7days), you will be prompted again to provide your credential in a browser session.

Notice that the REST API base path points to the development version of the HWDB, which is the default setup. When you are ready to work with the official Production server, enter:

hwdb-configure --prod 

Likewise, to set it back to Development, enter:

hwdb-configure --dev 

Note that the –prod and –dev switches may also be added to the initial configuration command, $HOME/.sisyphus/config.json instead of being run separately.

Configuring a Component Type and a Test Type in the HWDB:

• If your Component Type is already setup, skip this step. Otherwise your Component Type needs to be setup as described in the 03-Setting-up-Types.

• In the Datasheet of your Component Specifications, insert the following (notice that you must have the Administrator privilege in order to modify Component Type definitions):

  DATA: {}
  _meta: {}
  

• If you are planning to store data in Tests, create and define a new Test Type as described in the 03-Setting-up-Types. And then in the Datasheet of the newly created Test Type, insert the following:

  DATA: {}
  _meta: {}
  

• All contents uploaded by the Tool are stored within “DATA: {}”.

• “_meta: {}” is used by the Tool only and users should just ignore it.

back to top

Lesson 1: Some Simple Examples

The examples in this lesson demonstrate how to upload simple spreadsheets that do not require an additional “docket” file that describes their contents. Typically, if the component type has no specifications, or the specifications are simple key/value pairs, it is possible to upload item sheets directly, as demonstated in these examples.

HWDB Setup

The examples below assume that the following configuration has been set up in the HWDB. These should already be available on the Development server, but since they are not protected from modification, it is possible that they may be edited by others. If the examples appear not to work, check the following:

• A component type named “Z.Sandbox.HWDBUnitTest.doodad” has been created, which has a type ID of Z00100300012.

• The component is managed by (at least) the group “tester,” and the user running the examples is a member of this group.

• The component has “Acme Corporation” listed as one of its available manufacturers.

• The component’s specifications are: { “DATA”: {}, “_meta”: {} }

For the first several examples, we will only be uploading Item information with no tests attached, but most of the principles involved with uploading tests is the same as uploading items, so it is worthwhile to follow all the item examples before moving on to tests.

Example 1.1: A very simple example

The simplest way to upload hardware items to the HWDB is to create a spreadsheet where the top several rows form a “header” that indicates, at minimum, that the “Record Type” being uploaded is “Item,” and either the “Part Type ID” or the “Part Type Name.” (Both may be given, but they must indicate the exact same component type. There must be an empty row between the header and the section containing the actual data.

This sheet is available here; Ex_1-1.xlsx.

To do a mock test of uploading this spreadsheet, enter the following. This will not actually commit the changes to the HWDB. It will only do a trial run to test for errors.

hwdb-upload Ex_1-1.xlsx

If this appears to run without errors, you may commit the update by using “–submit”

hwdb-upload Ex_1-1.xlsx --submit

Reconstructed Excel file

When the above command is executed, the Tool also generates an Excel file locally inside of folder with a timestamp being its folder name. This Excel file contains the contents of Items just uploaded, including the newly assigned PIDs in External ID column.

About label names in spreadsheet

In spreadsheet(s), user provides contents with specific labels. There are specific label names that are reserved for the Tool. We list those special labels below. These special labels have specific meanings and we’ll go through them in the following examples.

• External ID
• Serial Number
• Comments
• Manufacturer
• Manufacturer ID
• Manufacturer Name
• Institution
• Institution ID
• Institution Name
• Location
• Location ID
• Location Name
• Arrived
• Location Comments
• Image File

back to top

Example 1.2: Providing default values

In the above example, every item had the exact same Institution and Manufacturer. A shortcut is available for this case. Additional fields may be provided in the header indicating default values for a column. The column itself may be omitted if there are no exceptions to the default values.

This sheet is available here; Ex_1-2.xlsx.

back to top

Example 1.3: Editing Items

Once a sheet has been uploaded to the HWDB, any subsequent attempts to upload the sheet will only result in the HWDB attempting to edit the items instead of adding new ones. But, if you explicity want to edit an item, you may use an “External ID” column instead of or in addition to the “Serial Number” column. You may even change the existing serial numbers this way!

Note that you may not change the Institution for an item after it was initially set.

This sheet is available here; Ex_1-3.xlsx.

Example 1.4: Providing values on the command line

If you do not wish to provide a header inside a spreadsheet, you may provide values on the command line.

This sheet is available here; Ex_1-4.xlsx.

To upload this example:

hwdb-upload \
    --part-type-id=Z00100300012 \
    --record-type=Item \
    --value "Manufacturer ID" 50 \
    --value "Institution ID" 186 \
    Items.xlsx \
    --submit

back to top

Example 1.5: Providing values via a docket file

Another alternative way to provide external values is by using a docket file.

This sheet is available here; Ex_1-5.xlsx.

{
    "Sources": [
        {
            "Source Name": "Item Source",
            "Files": "Ex_1-5.xlsx"
        }
    ],
    "Values": {
        "Record Type": "Item",
        "Part Type ID": "Z00100300012",
        "Part Type Name": "Z.Sandbox.HWDBUnitTest.doodad",
        "Institution": "(186) University of Minnesota Twin Cities",
        "Manufacturer": "(50) Acme Corporation"
    }
}
        

This sheet is available here; Ex_1-5_docket.json.

To upload this example:

hwdb-upload Ex_1-5_docket.json --submit

Example 1.6: Specification fields

The type “doodad” has a specification field named “DATA.” (Ignore the “_meta” field. It is for internal use by the application.) Top-level specification fields such as “DATA” can be provided the same way as Institution or Manufacturer.

This sheet is available here; Ex_1-6.xlsx.

back to top

Lesson 2: Subcomponents

HWDB Setup

For this lesson, we want to create a hierarchy of components. We will therefore need a second component type. The examples below assume that the following configuration has been set up in the HWDB, in addition to the configuration from Lesson 1:

• A component type named “Z.Sandbox.HWDBUnitTest.doohickey” has been created, which has a type ID of Z00100300013.

• The component is managed by (at least) the group “tester,” and the user running the examples is a member of this group.

• The component has “Acme Corporation” listed as one of its available manufacturers.

• The component’s specifications are: { “DATA”: {}, “_meta”: {} }

• The component type has a single subcomponent slot named “Doodad” of type “Z.Sandbox.HWDBUnitTest.doodad”

Example 2.1: Linking a subcomponent

For this example, we need to upload items of two different component types. We can do this by adding multiple worksheets to our Excel file.

This sheet is available here; Ex_2-1_doodad.xlsx.

This sheet is available here; Ex_2-1_doohickey.xlsx.

To upload this example, first make the two items newly:

hwdb-upload Ex_2-1_doodad.xlsx --submit

then to make links to those two;

hwdb-upload Ex_2-1_doohickey.xlsx --submit

Some notes on linking subcomponents:

• The “Doodad” column may contain the serial number of the item to be connected, or the Part ID if the item already exists in the database.

• If the subcomponent item is already in the database, it is not strictly necessary to include a sheet defining the item.

• The subcomponent to be linked must not have its “status” set to anything besides “available.” (“Available” is the default status for an item, so an item will not have a different status unless deliberately changed.)

Some notes on linking subcomponents:

• The “Doodad” column may contain the serial number of the item to be connected, or the Part ID if the item already exists in the database.
• If the subcomponent item is already in the database, it is not strictly necessary to include a sheet defining the item.
• The subcomponent to be linked must not have its “status” set to anything besides “available.” (“Available” is the default status for an item, so an item will not have a different status unless deliberately changed.)

Lesson 3: Item Images

Example 3.1: Uploading images

This example demonstrates how to upload image files for items.

This sheet is available here; Ex_3-1.xlsx.

back to top

Lesson 4: Tests

Now let’s try to post some in the Test area of the HWDB. As mentioned before, a Test Type must be created and pre-defined as described in the 03-Setting-up-Types.

Example 4.1: Simple Test

This sheet will post “pass” under Test Type, Doodad Test, of Serial Number of SN000001. Notice that the Record Type is Test, instead of Item.

This sheet is available here; Ex_4-1.xlsx.

Example 4.2: Complex Structures

Now let’s try with more complex scheme. The docket file below will post the contents of the sheet to the corresponding serial number entries under Test Type, Doodad Test.

This sheet is available here; Ex_4-2.xlsx.

{
    "Sources": [
        {
            "Source Name": "Doodad Tests",
            "Files": "Ex_4-2.xlsx",
            "Encoder": "Doodad Test Encoder"
        }
    ],
    "Encoders": [
        {
            "Encoder Name": "Doodad Test Encoder",
            "Record Type": "Test",
            "Part Type Name": "Z.Sandbox.HWDBUnitTest.doodad",
            "Part Type ID": "Z00100300012",
            "Test Name": "Doodad Test",
            "Schema": {
                "Test Results": {
                    "DATA": {
                        "type": "group",
                        "key": [
                            "Test Date",
                            "Test Operator"
                        ],
                        "members": {
                            "Test Date": "string",
                            "Test Operator": "string",
                            "Overall Status": "string",
                            "Operator Note": "null,string",
                            "Subtest": {
                                "type": "group",
                                "key": "Subtest Name",
                                "members": {
                                    "Subtest Name": "string",
                                    "Trial": {
                                        "type": "group",
                                        "key": "Trial Number",
                                        "members": {
                                            "Trial Number": "integer",
                                            "Trial Description": "null,string",
                                            "Trial Result": "any"
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    ]
}



        

This sheet is available here; Ex_4-2_docket.json.

Notice that the Tool sort out rows based on column labels specified by “key”. For instance, with the above docket file, it sorts out based on two columns “Test Date” and “Test Operator”, forst. And then there will be a sub-structure “Subtest”, sub-sub-structure “Subtest Name” and “Trial” and so on. In each of structures, things are sorted by the specified “key”.

back to top

Lesson 5: Item Locations

Example 5.1: Uploading location info

This example demonstrates how to upload location information for two items.

This sheet is available here; Ex_5-1.xlsx.

back to top

Lesson 6: Do them all at once!

Now let’s try to upload Item specifications, Test specifications, some images, make links to a few sub-components, and upload locations of Items, all at once.

The spreadsheet file can be obtained from here: QuickStartExample.xlsx. It has 3 tabs, “My Items”, “My Images”, and “My Tests”.

The corresponding docket file can be obtained from here: QE-docket.json.

We’ll start to look at the example spreadsheet file first and understand our contents. We’ll then go through the docket file to see how it works, including setting up the database schema for Specifications.

QuickStartExample.xlsx: “My Items” sheet:

The example spreadsheet file includes 3 sheets. One of them, “My Items” sheet, looks like the below:

• If you don’t have any sub-component to be linked, you could just remove the columns D & E altogether.

• Similarly, if you don’t need to enter locations of your Items, go ahead to remove columns F, G, and H.

back to top

QuickStartExample.xlsx: “My Images” sheet:

“My Images” sheet should look like the below:

• Again, if you don’t have any image to be uploaded, you could delete the entire “My Images” sheet.

back to top

QuickStartExample.xlsx: “My Tests” sheet:

“My Tests” sheet should look like the below:

• Again, if you don’t need to upload any test data, but only Items, you could just delete this “My Tests” sheet.

back to top

QE-docket.json: “Values” blob:

“Values” blob in QE-docket.json should look like the below:

 "Values": {
        "Part Type ID": "D00599800015",
        "Institution": "(186) University of Minnesota Twin Cities",
        "Manufacturer": "(7) Hajime Inc"
    }

Here, you provide Component Type ID of the Items you like to upload (in this case, D00599800015), your Institution, and Manufacturer.

back to top

QE-docket.json: “Sources”:

“Sources” in QE-docket.json should look like the below:

 "Sources": [
        {
            "Files": "QuickStartExample.xlsx",
	        "Sheet Name": "My Items",
            "Encoder": "My Item Encoder"
        },
	    {
            "Files": "QuickStartExample.xlsx",
	        "Sheet Name": "My Images",
            "Encoder": "My Image Encoder"
        },
	    {
            "Files": "QuickStartExample.xlsx",
	        "Sheet Name": "My Tests",
            "Encoder": "My Test Encoder"
        }
    ]

• Here you specify the 3 spreadsheet file names, the 3 sheet names, and the 3 Encoder names.

• We have only one single spreadsheet file in our example. But we could have those 3 sheets in 3 different files separately.

• We’ll describe each of the Encoders in the next sub-sections.

back to top

QE-docket.json: Encoders: “My Item Encoder”:

“My Item Encoder” in QE-docket.json should look like the below:

            "Encoder Name": "My Item Encoder",
            "Record Type": "Item",
            "Part Type ID": "D00599800015",
            "Schema": {
                "Specifications": {
                    "DATA": {
                        "type": "group",
                        "key": "Serial Number",
                        "members": {
                            "Serial Number": "string",
                            "Drawing Number": "string",
			                "My Doc": {
				               "value": "https://edms.cern.ch/document/2505353/"
			                },
			                "Serial Number": "string",
			                "Location": "string",
			                "Arrived": "string",
			                "Location Comments": "string"
                        }
                    }
                },
		        "Subcomponents": {
		            "MySubComp 1": {
			            "column": "MySubComp 1"
		            },
		            "MySubComp 2": {
			           "column": "MySubComp 2"
		            }
		        }
            }

• Within the Specifications blob, its database scheme is defined.

• “key” tells the Tool that “Serial Number” defines an Item (one-to-one correspondance between a “Serial Number” and an Item).

• The “members” blob defines the scheme. In the database, we like to store them like the one shown below:

  "Drawing Number": "DFD-XX-FF00",
  "My Doc"        : "https ://edms.cern.ch/document/2505353/"
  

• For “My Doc”, since we know the documentation link is the same for this Component Type, instead of specifying it in every row of “My Items” sheet, we provide the constant value here. Notice that we don’t need to specify the label name, “My Doc” in our sheet either.

• The “Subcomponents” blob just specifies label names for sub-component PIDs (or Serial Numbers).

back to top

QE-docket.json: Encoders: “My Image Encoder”:

“My Image Encoder” in QE-docket.json should look like the below:

            "Encoder Name": "My Image Encoder",
            "Record Type": "Item Image",
            "Part Type ID": "D00599800015",
            "Schema": {
                "type": "group",
                "key": "Serial Number",
                "members": {
                    "Serial Number": "string",
                    "Image File": "string",
		            "Comments": "string"
                }
            }

• Similar to “My Item Encoder” typesets of the labels are defined.

back to top

QE-docket.json: Encoders: “My Test Encoder”:

“My Test Encoder” in QE-docket.json should look like the below:

            "Encoder Name": "My Test Encoder",
            "Record Type": "Test",
	        "Part Type ID": "D00599800015",
	        "Test Name": "My Test",
            "Schema": {
                "Test Results": {
                    "DATA": {
                        "type": "group",
                        "key": "Serial Number",
                        "members": {
			                "Serial Number": "string",
                            "Operator": "string",
			                "Location": "string",
			                "PSU": {
				                "type": "group",
				                "key": "PSU Serial Number",
				                "members": {
				                     "PSU Serial Number": "string",
				                     "Visual Inspection": "string",
				                     "PSU Manufacturer": "string",
				                     "PSU Test 1": "string",
				                     "PSU Test 2": "string"
				                }
			                },
			                "Fans": {
				                "type": "group",
				                "key": "Fan Serial Number",
				                "members": {
				                     "Fan Serial Number": "string",
				                     "Fan Manufacturer": "string",
				                     "Fan Test 1": "string",
				                     "Fan Test 2": "string"
				                }
			                }
                        }
                    }
                }
            }

• Again, it has a very similar form to what we have seen in Item and Image Encoders.

• This time, however, it has a nested structure in its scheme. E.g., this scheme would store the contents in “My Tests” sheet as shown below:

  "Serial Number": "HA-MURAMATSU-00003",
  "Operator"     : "Hajime",
  "Location"     : "Minneapolis",
  "PSU"          : [ 
                       "PSU Serial Number": "2PH30-230",
                       "Visual Inspection": "Passed",
                       "PSU Manufacturer" : "TOSHIBA",
                       "PSU Test 1"       : "Failed",
                       "PSU Test 2"       : "Passed" 
  				   ],
  "Fans"         : [ 
                       "Fan Serial Number": "W0909101451",
                       "Fan Manufacturer" : "Champion Lab",
                       "Fan Test 1"       : "Passed",
                       "Fan Test 2"       : "Failed" 
  				   ]
  

back to top

Let’s upload them:

• If you have modified your sheet(s) and docket file according to your needs, you should be ready to upload them. Execute the following command. Don’t worry. It will not upload, yet. It will only check the contents of the provided files.

  hwdb-upload QE-docket.json
  

• If you don’t see any error message from the above command-line, let’s upload them for real:

  hwdb-upload QE-docket.json --submit
  

• If everything goes well, you should see a screen similar to the one shown below:

and new PIDs are assigned to each of the newly uploaded Items (i.e., the corresponding Serial Numbers).

• Again, if you don’t need to upload Tests, you can remove the Test sheet from the spreadsheet file and the Tests portion from “Sources” and “Endoder” of the docket file.

• Likewise, if you don’t plan to upload images, remove the corresponding parts from the spreadsheet and docket files.

with External IDs (or Serial Numbers) that already exist in the HWDB

• If you upload with Serial Numbers (or External IDs) that already exist in the HWDB, it will overwrite the existing Item Specifications and Tests (don’t worry, their histories are kept in the HWDB). This is actually a useful feature when you need to update contents in the HWDB.

back to top

Lesson 7: Generating bar/QR-code labels with the Tool

One can produce massive numbers of labels with the Tool.

Please refer to our Generating bar/QR-code labels section.

Lesson 8: Filling out the DUNE Shipping checklists with the GUI-based Tool

A link to this new section is coming soon.

Key Points

• keypoint 1