Hardware DB Project Wiki
- Table of contents
- Hardware DB Project
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 .........