This section is a demonstration on how to use the database service to access to the run database of Muon g-2 experiment.
A general DB setup wiki by the DB team can be found here.
A talk by Liang and Dikai during the previous collaboration meeting can be found here:
Online/gm2 Local and Offline/FNAL Central Database¶
In general, we store two categories of database for Muon g-2 experiment. The first one is an online database hosted by a local machine (our database server) at MC1, and the second one is an offline database hosted by FNAL under Central DB together with other experiments. Both databases are based on PostgreSQL and our online database is duplicated and stored in the Central DB as well (the frequency is still in discussion).
A PostgreSQL client (psql) is required to perform complete sets of transaction on these databases. A web infrastructure is setup by the FNAL Central DB team to enable users to perform web-url-based queries but it is mainly limited to transactions like reading the tables.
PostgreSQL-query-based and web-url-based database transaction¶
Important: Make sure that you are on FNAL's fgz network (for the web url-based) else the following exercises won't work.
To use postgres query to talk to a postgres server, there are many API clients available on the internet. Below are what we currently use in the collaboration.
- pqxx (C++ API for art::Database_service and MIDAS slow control frontend for SiPM, uTCA crate)
- psycopg2 (python API for SiPM slow control - sipmometer)
The use of APIs to perform transaction directly to the DB server is allowed only for limited parties as it is not scalable.
Most users or the grid jobs will use the web url query discussed below.
Another important thing to keep in mind is that the version of psql client in our gm2 virtual machines is 8.4.20 while the one of the Central DB psql server is 9.5.4.
So far we have not seen any conflicts for our transactions but it is always better to double check if the results are what you would expect.
Below is the warning message for your reference, where
gm2_conditions_dev is the name of the test database in the Central DB server.
<gm2gpvm03.fnal.gov> psql -U USERNAME -d gm2_conditions_dev -h ifdb04 -p 5445 psql (8.4.20, server 9.5.4) WARNING: psql version 8.4, server version 9.5. Some psql features might not work.
Web url-based transaction¶
The core part of the web url-based query is given by the following (let's give it a name,
Next you need to specify the database name (
DB_NAME) and the table name (
TABLE_NAME). The syntax is given by the following
where we specify
Do not forget the pound sign
& in between them. The
QUERY_CONDITION argument is a bit tricky here.
- If you want to query for
run = 1000, then
- if you want
run < 1000, then
- Similarly, for
run > 1000, then
By putting the whole url-query on your web browser, you will be able to look into the unformatted values of the table.
Currently we have a test database at FNAL Central DB named postgres_dev, and several tables for various purposes listed below:
- g2sc_values (for all the slow control info like temperatures of the SiPMs and uTCA crates)
- calorimeter_calibration_correction_constants_slac2016 (for the calibration constants extracted from SLAC data)
Now let's try to look at the temperature of the SiPMs in calorimeter 21 by copy and paste the following url to your browser
Here I limited the number of entries by using
You can also use curl, a command line tool in unix, to fetch the temperature data.
curl "http://dbdata0vm.fnal.gov:8210/QE/gm2/dev/app/SQ/query?dbname=postgres_dev&t=g2sc_values&w=channel:calo21temps&w=id:lt:100" > tempCalo21.txt
Web-based PostgreSQL management page¶
Even though command line tools are enough at the moment to management our database, using a nice web-based administration tool is often helpful.
The one I have been using so far is phpPgAdmin:
Accessing DB using gm2util::Database_service¶
It is still working in progress and will be pushed to the development branch once it is ready.