Tracker Databases

Tracker Databases

There are a few tracker databases hosted at different sites. This wiki page currently focuses on the ones that interact with Fermilab-hosted mu2e_tracker_prd database and uses tools available in the TrackerHardwareDB git repository. If you have any questions or comments, contact Andy Edmonds.

Setting Up

To use the tools described on this wiki page, you need to clone the TrackerHardwareDB git repository. The following instructions are for running on the mu2egpvms but some tools (e.g. the panel QC viewer) could be run from any machine:

ssh -XY username@mu2egpvmXX.fnal.gov
cd /mu2e/app/users/username/
git clone git@github.com:Mu2e/TrackerHardwareDB

To get any code updates, you should periodically do

cd TrackerHardwareDB
git pull

FNAL Planes Database

The database for the plane construction at FNAL. The working version is not hosted on mu2e_tracker_prd but is copied to the imported.fnal_planes database table following the instructions below.

Importing

To copy to imported.fnal_planes, first go to the computer in the clean room and create a .zip file with the plane DB folder and copy to your machine.

Then you can do:

cd TrackerHardwareDB/python
python3 create_imported_fnal_planesdb.py path/to/fnal/planesdb YYYY_MM_DD

where YYYY_MM_DD is the date that the zip file was created (note: underscores!).

This will create two .sql files. You will need the mu2e_tracker_admin database role to then do

cd ../sql
psql -h ifdb11 -p 5459 mu2e_tracker_prd < create_imported_fnal_planesdb.sql
psql -h ifdb11 -p 5459 mu2e_tracker_prd < insert_imported_fnal_planesdb.sql

The plane construction DB can now be viewed using the panel QC viewer below.

Some details from behind the scenes:

• the first psql command drops the imported.fnal_planes_previous table, recreates it from the current imported.fnal_planes, and then drops the imported.fnal_planes table before creating a imported.fnal_planes_YYYY_MM_DD
• the second psql command insert all the new data into imported.fnal_planes_YYYY_MM_DD before copying it to create the new imported.fnal_planes table
• both the imported.fnal_planes_previous and imported.fnal_planes tables are needed to extract data for the extraction step (see next section)

Extracting

The information in imported.fnal_planes needs to be extracted so that it can be inserted into the qc.panels and qc.planes tables.

cd TrackerHardwareDB/python
python3 extract_updates_from_imported_fnal_planesdb.py --snapshot_date 2023-07-14

This script will compare the previous and current versions of imported.fnal_planes and look for any changes in the contents of the following:

• 'Missing_straws', 'Missing_wires', 'High_currents', 'Blocked_straws', 'Sparks', 'Brooken_wires', 'Loose_omegas', 'Missing_anode_pins', 'Missing_cathode_pins', 'Missing_omegas'

and create a run_create_update_qc_panels_table.sh file. This contains commands for the python script described in #Changing missing_straws, high_current_wires, etc., which you can run like so:

. ./run_create_update_qc_panels_table.sh

This creates the .sql files that should be checked and then run manually. Note that this script appends to any existing .sql files with the names ../sql/update_qc_panels.sql and ../sql/update_qc_planes.sql so delete those files if they already exist.

Notes:

• During the extraction script process, the script will prompt you for input e.g. if it cannot parse an integer when it thinks it should.
• If you make a mistake, you can press Ctrl+C to stop and your progress so far will still be in the run_create_update_qc_panels_table.sh
• You can start from a later panel with the --min_panel_id argument
• Be sure to look at the viewer to check what is already in the qc.panels database table

Panel QC Database

The panel QC database that is stored in mu2e_tracker_prd

Viewing Database

The panel QC database viewer can be run on any machine connected to the internet with Node.js installed.

Below are instructions to run the viewer on the mu2egpvm machines but in principle it is runnable from anywhere.

Installation

To install the panel QC viewer:

cd TrackerHardwareDB/panel-qc-viewer/
npm install

Running

To run the panel QC viewer, you need to forward a port when you ssh to the mu2egpvm machine:

ssh -L 3000:localhost:3000 -XY username@mu2egpvmXX.fnal.gov
cd TrackerHardwareDB/panel-qc-viewer
npm start

Then you can open a browser and go to localhost:3000 on your local machine

To run on a different port you can type:

 PORT=XXXX npm start

Updating Database

There are a few python scripts that can be used to update the qc.panels table. To set up to run the python scripts you need to do the following:

cd TrackerHardwareDB
source setup.sh

Then each scripts follows a similar pattern:

1. Run a python scripts to create a .sql file
2. Check the .sql file for any obvious errors
3. Run commands contained in the .sql file. This puts the data in the database

To put data in the database, you need to have the mu2e_tracker_writer database role

Note (2024-05-28): The setup script works on both SL7 and AL9 machines. On an AL9 machine your command prompt will be prefixed by (pytrkdb_temp) but that will not happen on an SL7 machine. Note that the SL7 machines will be retired at the end of June 2024.

Adding a new panel

To add a new panel to the qc.panels table:

cd TrackerHardwareDB/python
python3 create_insert_new_panels.py --panel_ids X Y Z
psql -h ifdb11 -p 5459 mu2e_tracker_prd < insert_new_panels.sql

This will create .sql files to insert new rows for panels X, Y, and Z. Note that X, Y, Z do not need to contain "mn" or "MN" (the script will remove these characters)

Adding a new plane

To add a new plane to the qc.planes table:

cd TrackerHardwareDB/python
python3 create_insert_new_planes.py --plane_ids X Y Z
psql -h ifdb11 -p 5459 mu2e_tracker_prd < insert_new_planes.sql

This will create .sql files to insert new rows for planes X, Y, and Z.

Changing missing_straws, high_current_wires, loose_preamp_connections etc.

In general, results of the panel QC tests are propagated into qc.panels from the FNAL planes database (see above). These instructions are if there are additional changes or corrections that need to be made, or for the results of the electronic installation tests

cd TrackerHardwareDB/python
python3 create_update_qc_panels_table.py --panel_id XX --{new/add/remove}_{column1} A B C --{new/add/remove}_{column2} D E F --comment "a useful message for the repairs table"
psql -h ifdb11 -p 5459 mu2e_tracker_prd < update_qc_panels_table.sql

will create .sql files to update panel XX. Note that old update_qc_panels_table.sql files must be deleted by hand

Some examples:

• This will replace the current contents of missing_straws column for panel 53 with straw numbers 1, 2, 3.

python3 create_update_qc_panels_table.py --panel_id 53 --new_missing_straws 1 2 3 --comment "straws 1, 2, 3 found missing on 07/14/2023"

• This will add the straw number 4 to the current list of high_current_wires for panel 76:

python3 create_update_qc_panels_table.py --panel_id 76 --add_high_current_wires 4 --comment "wire 4 high current from HV test in 07/14/2023"

• This will remove the straw number 5 from the current list of missing_wires and add the straw numbers 88 and 89 to the current list of missing_omega_pieces for panel 121:

python3 create_update_qc_panels_table.py --panel_id 121 --remove_missing_wires 5 --add_missing_omega_pieces 88 89 --comment "removed wire 5 and omega pieces on channels 88 89 fell off on 07/14/2023"

Changing panels in a plane

Starting from an empty plane, these are the instructions to change the panels in the qc.planes database.

cd TrackerHardwareDB/python
python3 create_update_qc_planes_table.py --plane_id XX --add_panels_in_order A B C D E F --comment "plane created on 12/02/2023"
psql -h ifdb11 -p 5459 mu2e_tracker_prd < update_qc_planes_table.sql

will create .sql files to update plane XX by adding panels A, B, C, D, E, and F in positions 0, 1, 2, 3, 4, and 5. Note that the order matters here.

To do a panel swap, follow these instructions:

cd TrackerHardwareDB/python
python3 create_update_qc_planes_table.py --plane_id XX --panel_swap_out B --panel_swap_in Z --panel_swap_pos 1 --comment "swapped panel B --> Z on 12/02/2023"
psql -h ifdb11 -p 5459 mu2e_tracker_prd < update_qc_planes_table.sql

Note that you need to know the position that the original panel was placed in, and that all three arguments (--panel_swap_in, --panel_swap_out, and --panel_swap_pos) are required.

Updating plane construction start/end dates

To update the construction start and end dates in the qc.planes database.

cd TrackerHardwareDB/python
python3 create_update_qc_planes_table.py --plane_id XX --construction_start_date 2023-12-28 --construction_end_date 2023-12-29 --comment "adding construction start and end dates"
psql -h ifdb11 -p 5459 mu2e_tracker_prd < update_qc_planes_table.sql

Adding earboard test results

There are two things to do:

1. Add the generated pdf file to the repository

cd TrackerHardwareDB/panel-qc-viewer/public/images/earboard/
cp /path/to/images/plot_*.pdf .
git add *.pdf
git commit -m "Adding ear board plots"
git push

2. Update the result in the database with true or false

cd TrackerHardwareDB/python
python3 create_update_qc_panels_table.py --panel_id 121 --earboard true (or false) --comment "passed earboard test on 07/14/2023"
psql -h ifdb11 -p 5459 mu2e_tracker_prd < update_qc_panels_table.sql

Adding final AMB-DMB leak check, earflooding trimming info, air test for straw blockage test, etc.

We update these results in the database with the same script as in step 2 of the earboard test results. Namely:

cd TrackerHardwareDB/python
python3 create_update_qc_panels_table.py --panel_id 121 --passes_final_amb_dmb_leak_check true --earflooding_trimming_done false --air_test_for_straw_blockage_done true --comment "passed final AMB-DMB leak check on 12/02/2023, earflooding trimming was done on 06/31/2023, determined air test done from FNAL Plane DP extracted on 12/12/2023"
psql -h ifdb11 -p 5459 mu2e_tracker_prd < update_qc_panels_table.sql

Adding Fe55 analyzed data

From the panel QC Fe55 analysis script, we get either csv files named like mn269_ch0_r3_delta.csv and mn269_ch1_r3_delta.csv (for cramp type 1) or a file named like mn269_r3_delta.csv (for cramp type 2). The data contained in either case can be put into the database like so:

For cramp type 1:

cd TrackerHardwareDB/python
python3 create_update_maxerf_risetime.py --datafiles ../data/mn271_ch0_r6_delta.csv ../data/mn271_ch1_r6_delta.csv --comment "after first drilling"
psql -h ifdb11 -p 5459 mu2e_tracker_prd < update_maxerf_risetime.sql

For cramp type 2:

cd TrackerHardwareDB/python
python3 create_update_maxerf_risetime.py --datafiles ../data/mn271_r6_delta.csv --comment "after first drilling"
psql -h ifdb11 -p 5459 mu2e_tracker_prd < update_maxerf_risetime.sql

The python script has some internal checks to make sure there are the correct number of channels and no duplicate channel numbers. The "comment" argument will be included when the change is recorded in the repairs.panels table and will also appear in the legend on the "Fe55 historical data" plot on the HV page so make it short but informative.

Adding Fe55 plots

These are just stored in the repository so to add these do:

cd TrackerHardwareDB/panel-qc-viewer/public/images/hv_data/
cp /path/to/images/mn*.png .
git add *.png
git commit -m "Adding hv data plots"
git push

Then for other people to see the new images, they will need to pull in the updates.

Backing up raw Fe55 data

We periodically backup the raw HV data csv files. The csv files will be stored alongside a README that contains comments in some directory on trackerpi3. These are too large to store in the database so instead we tarball them up and store them on dCache. Information about the files (both .csv and README files) are then stored in the database tables qc.panel_hv_data_files and qc.panel_hv_data_readmes, which is then available through the viewer.

First, to make some space on trackerpi3, copy the data to the mu2egpvm:

ssh username@mu2egpvm0X.fnal.gov
cd /exp/mu2e/app/users/username/
mkdir -p tracker-data/backup_2023-11-17
cd tracker-data/backup_2023-11-17/
scp mu2e@trackerpi3.dhcp.fnal.gov:~/hvcurrmeas/outputs/* .

This may take a while but once it's done, the data can be deleteed from the trackerpi3.

Then we need to create the tarballs, update the database, and copy the tarballs back to trackerpi3 so that the RawDataMover (RDM) knows to copy them to tape.

cd TrackerHardwareDB
source setup.sh
cd python/
rm *.tbz # remove any .tbz files that were left over from previous backups
python3 create_insert_panel_hv_data_files.py /exp/mu2e/app/users/username/tracker-data/backup_2023-11-17/
psql -h ifdb11 -p 5459 mu2e_tracker_prd < ../sql/insert_panel_hv_data_files.sql
scp *.tbz mu2e@trackerpi3.dhcp.fnal.gov:~/dropbox/output/
ssh mu2e@trackerpi3.dhcp.fnal.gov
cd ~/dropbox/output/
# check things look OK
mv *.tbz ../upload/

The python script does the following:

1. Walks through the /path/to/directoryX directories and creates a list of files with the name "README" and a list of files with the suffix ".csv"
2. Reads each README file and creates a map of "csv filename" --> "README comment"
   1. it also parses the README comment to find the name of the input and log files
3. Loops through the csv files and creates a map of "tar filename" --> "list of csv files it will contain"
   1. files (csv, input, and log) with the same panel number are put in the same tarball
   2. if there are two files with the name (i.e. in different directories), then the script will check if the files are identical. If the files are identical, only one will be tarballed; if they are different, then both will be tarballed
4. Loops through the tar filename map and creates the tarballs
   1. the tarball names have the form mcs.mu2e.PanelQC_HV.Fe55.mnNNN_YYYYMMDD_YYYYMMDD.tbz where NNN is the panel number and the two dates are the earliest and latest date covered by the contained data
5. Writes an sql file for uploading the file information to the database

The last set of steps of copying the tarballs to trackerpi3 lets the RawDataMover (RDM) know to copy them to tape. Be careful not to fill up trackerpi3. Once the tarballs can be safely deleted, the RDM moves the files to the delete directory, where we need to manually delete them.

Retrieving raw Fe55 data

We do not store the original raw HV files in the database. Instead, the files are stored in tarballs (.tbz files) on Dcache.

To know which tarball to retrieve, you can look on the HV data page of the QC viewer, where there is a list of .csv files along with the tarball file that each .csv file is in. The tarball file will have a name of the form bck.mu2e.PanelQC_HV.Fe55.XXXXXX.tbz where XXXXXX will contain information like the panel number and dates over which the data were taken.

The location of the tarball can then be retrieved from SAM by running the following on one of the mu2egpvms:

setup dhtools
sam locate-file bck.mu2e.PanelQC_HV.Fe55.XXXXXX.tbz

This will give you the location of the file. You will probably need to prestage the file. Once the file is on disk you can do:

tar -xvf bck.mu2e.PanelQC_HV.Fe55.XXXXXX.tbz

to unzip the tarball in your current directory. The raw .csv files will then be in that directory.

The original README files can be viewed on the hv_readmes page of the viewer (can be accessed from a link on the HV data page). The README files are stored in the database in the qc.panel_hv_readme_files table

Adding problems with survey holes

We update these results in the database with the same script as in step 2 of the earboard test results. There is one flag for each survey hole labelled 'hv', 'mid', and 'cal' forNamely:

cd TrackerHardwareDB/python
python3 create_update_qc_panels_table.py --panel_id -1 --survey_hole_problem_hv false --survey_hole_problem_mid false --survey_hole_problem_cal false --comment "no issues with survey holes seen on 2024-02-26"
psql -h ifdb11 -p 5459 mu2e_tracker_prd < update_qc_panels_table.sql

Adding panel measurements

To add panel measurements (e.g. leak rates), we use the following script:

cd TrackerHardwareDB/python
python3 create_insert_panel_measurements.py --measurement_type AAAA --datafile data.csv --date_taken YYYY-MM-DD
psql -h ifdb11 -p 5459 mu2e_tracker_prd < insert_panel_measurement.sql

where the measurement type has a limited number of options (use python3 create_insert_panel_measurements.py --help to see the list), the data file contains the data for one plane, and the date taken argument has the format given.

Some notes:

• the data file should have a header line with the column names, which should match what the --help option defines (e.g. for leak measurements the columns should be panel_id, leak_sccm and comment)
• the viewer will measurements in appropriate places

Adding plane measurements

To add plane measurements, we use the following script:

cd TrackerHardwareDB/python
python3 create_insert_plane_measurements.py --plane_id XX --measurement_type AAAA --datafile data.csv --date_taken YYYY-MM-DD
psql -h ifdb11 -p 5459 mu2e_tracker_prd < insert_plane_measurement.sql

where XX is the plane ID number, the measurement type has a limited number of options (use python3 create_insert_plane_measurements.py --help to see the list), the data file contains the data for one plane, and the date taken argument has the format given.

Some notes:

• the data file should have a header line with the column names, which should match what the --help option defines (e.g. for height measurements the columns should be phi_location_deg and height_inches)
• the viewer will show a histogram or table of the most recent measurements (i.e. all the measurements from the most recent date_taken)
  • previous measurements are stored in the database

Adding DRAC test results

The DRAC test results are in a .json file with the name

Panel_X_ROC_[hex]/DRACTEST_2023-12-29-17-38.json

where X is the panel ID (which can be 0 for when the test was done outside of a panel) and [hex] is the DRAC ID which is unique to each DRAC.

To add a DRAC test to the database you need to do the following:

cd TrackerHardwareDB/python
python3 create_insert_drac_tests.py --datafile path/to/Panel_X_ROC_[hex]/DRACTEST_2023-12-29-17-38.json
psql -h ifdb11 -p 5459 mu2e_tracker_prd < ../sql/insert_drac_tests.sql

where the data file must contain the directory name with the panel ID and DRAC ID

Adding DRAC ID to qc.panels

Once a DRAC has been installed in a panel, it must be documented in the qc.panels database table. This can be done with the following scripts:

cd TrackerHardwareDB/python
python3 create_update_qc_panels_table.py --panel_id 4 --set_drac_id ca980 --comment 'DRAC ca980 installed into panel 4 on 2024/01/28'
psql -h ifdb11 -p 5459 mu2e_tracker_prd < ../sql/update_qc_panels_table.sql