Project

General

Profile

Hardware DB Project Wiki

Hardware DB Project

This project converted the original NOvA Hardware Database to a generalized form useable by all experiments. It now consists of a core which is common to all experiments and a section of code which maps to the experiments database. The physical hardware database is specific to the experiment using it. The tools to create, display and maintain the data is part of the core, common to all.

Schemas by Experiment

General Entity Relationship Design (ERD) descriptions. MU2E Schema Dune Schema Protodune Schema Icarus Schema

Adding or Updating Data

Web Forms

Hardware database web forms are provided to each experiment specific to their database. If authorized, a user can add, insert, update or delete records via the forms. Deletes are only support through the web forms.

CSV file

Authorized users can insert data into a table by uploading a CSV file through the web forms. CSV uploads support inserting data only. Note: unless specifically requested these forms are not configured for use.

Python API

A Python API exists which allows the users to push data into the database over the web. It supports both inserts and updates. Deletes are not supported. Use of the API requires Python 2.7.3 or later (Not Python 3). The client library is available on the Files page.

In the main page of the web interface, tables are set in groups. You will need the name of the table and table group, the password for the table group the URL to communicate with. Optionally, you will need the query URL for QueryEngine .

The password is never sent across the web, but is used separately by client and server.

Every table includes the fields create_time, create_user, update_time and update_user. These are system managed fields and should not be included with your data.


""" 
Example table "sipm_measure_tests", table group is "SIPM Tables".
   sipm_id - text
   test_type - text
   test_date - timestampz
   worker_id - text
   bias_voltage - float
   gain - float
   create_time - timestampz
   create_user - text
   update_time - timestampz
   update_user - text
""" 

   password = os.environ.get("LOADER_PWD", "xxxxxxxxx")
   url = "https://somenode.fnal.gov:8443/hardwaredb/yourexp/HdbHandler.py/loader" 
   group = "SIPM Tables" 
   table = "sipm_measure_tests" 

   row = {'sipm_id' : 'sipm-284',
          'worker_id' : 'fn4873',
          'test_type' : 'internal',
          'test_date' : '01/12/2017 14:12',  # mm/dd/yyyy hh:mm (hh:mm is optional)
          'bias_voltage' : 54.69,
          'gain': 0.15 }

   dataLoader = DataLoader(password,url,group,table)

   dataLoader.addRow(row) 
   row['sipm_id'] = 'sipm285'
   dataLoader.addRow(row)   # add multiple distinct rows to the database with one call

   retVal,code,text =  dataLoader.send()
   if retVal:
      print "DataLoader - Success!" 
      print text
   else:
       print "Failed!" 
       print code
       print text

   dataLoader.clearRows()  # clears the class instance for reuse

   ## addRow can also be used to update an existing record.
   ## to do this, the dictionary must contain all fields comprising
   ## the table's primary key and the field or fields to change.
   urow = {'sipm_id' : 'sipm-284',  'worker_id' : 'fn5873'}
   dataLoader.addRow(urow,'update')
   retVal,code,text = dataLoader.send()

   #Data query example.  See "QueryEngine":https://cdcvs.fnal.gov/redmine/projects/qengine for details.
   #Selects rows where sipm_id=sipm-284" 
   dataQuery = DataQuery(" https://dbdata0vm.fnal.gov:9443/QE/hw/app/SQ/query")
   rows = dataQuery.query('the_database_name', table, 'sipm_id,test_type,test_date,worker_id,bias_voltage,gain','sipm_id:eq:sipm-284',)
   for row in rows:
       print row

Image API

The Python API since version v1.4 allows to store the images as a part of the data row. The corresponding field has to be defined as text as it stores only the file name. The file data is stored by the server in the table not directly visible to the user. Here is an example.


from DataLoader import DataLoader
import sys, os

# Code snippet to show image API

row = {
        'sipm_sn': "sipm_789",                               # text
        'procedure_document_version': "v1.0",                # text
        'iv_curve': open("image1.png", "rb"),                # image
        'comments': "Some comment.......",                   # text
    }

password = os.environ.get("LOADER_PWD", "xxxxxxxxx")
url = "https://somenode.fnal.gov:9443/pdune/hdb/loader" 
group = "Detector Tables" 
table = "sipms" 

dataLoader = DataLoader(password, url, group, table)

dataLoader.addRow(row)                    # Create a new row in the table

(retVal, code, text) = dataLoader.send()

if retVal:
    print "DataLoader - Success!" 
    print text
else:
    print "Failed!" 
    print code
    print text
    sys.exit(1)

dataLoader.clearRows()  # clears the class instance for reuse

row['iv_curve'] = open("image2.png", "rb")      # Replace the image
row['comments'] = "Image has been replaced..."  # Update the comments...

dataLoader.addRow(row, mode='update')           # Update the row just stored with a new image and comments

(retVal, code, text) = dataLoader.send()

if retVal:
    print "DataLoader - Success!" 
    print text
else:
    print "Failed!" 
    print code
    print text
    sys.exit(1)

dataLoader.clearRows()  # clears the class instance for reuse

Using with HTTPS URLs

In order to use your client application over TLS connections you may need to provide the location of the CA certificate file in case if it's not included in the standard bundle.
Below is an example for the server having the certificate signed by InCommon CA.

Do this once on each node you want to work on.

$ wget http://crt.incommon-igtf.org/InCommonIGTFServerCA.crt
$ openssl x509 -inform der -in InCommonIGTFServerCA.crt -out InCommonIGTFServerCA.pem
$ wget 'https://support.comodo.com/index.php?/Knowledgebase/Article/GetAttachment/969/821026' -O ComodoRCA-CA.pem
$ cat InCommonIGTFServerCA.pem ComodoRCA-CA.pem > CA-bundle.pem

Save the resulting file in predefined place. It may vary depending on the OS version you are using.

Then every time you want to use it do:

$ export SSL_CERT_FILE=<full-path-to-file>/CA-bundle.pem
$ python .........

Developer Notes

IFBeam Data C API