Project

General

Profile

Database

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.
https://cdcvs.fnal.gov/redmine/projects/g-2/wiki/Muon_g-2_Database

A talk by Liang and Dikai during the previous collaboration meeting can be found here:
http://gm2-docdb.fnal.gov:8080/cgi-bin/ShowDocument?docid=4691

Online/gm2 Local and Offline/FNAL Central Database

DBOverview.png

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.

PostgreSQL-query-based

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, CORE_URL)

CORE_URL=http://dbdata0vm.fnal.gov:8210/QE/gm2/dev/app/SQ

Next you need to specify the database name (DB_NAME) and the table name (TABLE_NAME). The syntax is given by the following

CORE_URL/query?dbname=DB_NAME&t=TABLE_NAME&c=COLUMN_NAME&w=QUERY_CONDITION

where we specify

  • the DB_NAME after dbname=,
  • the TABLE_NAME after t=,
  • the COLUMN_NAME after c= and
  • the QUERY_CONDITION (WHERE clause) after w=

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 QUERY_CONDITION=run:1000,
  • if you want run < 1000, then QUERY_CONDITION=run:lt:1000
  • Similarly, for run > 1000, then QUERY_CONDITION=run:gt:1000.

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

http://dbdata0vm.fnal.gov:8210/QE/gm2/dev/app/SQ/query?dbname=postgres_dev&t=g2sc_values&w=channel:calo21temps&w=id:lt:1000

Here I limited the number of entries by using id<1000.

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:

https://github.com/phppgadmin/phppgadmin

phpPgAdminExample.png

Accessing DB using gm2util::Database_service

http://gm2-docdb.fnal.gov:8080/cgi-bin/ShowDocument?docid=4915

It is still working in progress and will be pushed to the development branch once it is ready.