Construction Databases at CMS

List of CMS Construction databases and API services

Detector	DB instance	Read API	Write API	Description
OT	INT2R	GPN: http://dbloader-tracker:8113/ Web: https://cmsdca.cern.ch/trk_rhapi	Web: https://cmsdca.cern.ch/trk_loader/trker/int2r GPN: ssh://dbloader-tracker.cern.ch:/home/dbspool/spool/trk/int2r	Development DB
OT	CMSR	GPN: http://dbloader-tracker:8113/ Web: https://cmsdca.cern.ch/trk_rhapi	Web: https://cmsdca.cern.ch/trk_loader/trker/cmsr GPN: ssh://dbloader-tracker.cern.ch:/home/dbspool/spool/trk/cmsr	Production DB
HGCAL	INT2R	GPN: http://dbloader-hgcal:8113/ Web: https://cmsdca.cern.ch/hgc_rhapi	Web: https://cmsdca.cern.ch/hgc_loader/hgc/int2r GPN: ssh://dbloader-hgcal.cern.ch:/home/dbspool/spool/hgc/int2r	Development DB
HGCAL	CMSR	GPN: http://dbloader-hgcal:8113/ Web: https://cmsdca.cern.ch/hgc_rhapi	Web: https://cmsdca.cern.ch/hgc_loader/hgc/cmsr GPN: ssh://dbloader-hgcal.cern.ch:/home/dbspool/spool/hgc/cmsr	Production DB
MTD	INT2R	GPN: http://dbloader-mtd:8113/ Web: https://cmsdca.cern.ch/mtd_rhapi	Web: https://cmsdca.cern.ch/mtd_loader/mtd/int2r GPN: ssh://dbloader-mtd.cern.ch:/home/dbspool/spool/mtd/int2r	Development DB
GEM	INT2R	GPN: http://dbloader-gem:8113/ Web: https://cmsdca.cern.ch/gem_rhapi	Web: https://cmsdca.cern.ch/gem_loader/gem/int2r GPN: ssh://dbloader-gem.cern.ch:/home/dbspool/spool/gem/int2r	Development DB
GEM	OMDS	GPN: http://dbloader-gem:8113/ Web: https://cmsdca.cern.ch/gem_rhapi	Web: https://cmsdca.cern.ch/gem_loader/gem/omds GPN: ssh://dbloader-gem.cern.ch:/home/dbspool/spool/gem/omds	Production DB
PPS	INT2R	GPN: http://dbloader-pps:8113/ Web: https://cmsdca.cern.ch/pps_rhapi	Web: https://cmsdca.cern.ch/pps_loader/pps/int2r GPN: ssh://dbloader-pps.cern.ch:/home/dbspool/spool/pps/int2r	Development DB
ECAL	INT2R	GPN: http://dbloader-ecal:8113/ Web: https://cmsdca.cern.ch/ecal_rhapi	Web: https://cmsdca.cern.ch/ecal_loader/ecal/int2r GPN: ssh://dbloader-ecal.cern.ch:/home/dbspool/spool/ecal/int2r	Development DB

For Web access please follow instruction in Access from Outside CERN section.

E-Groups for data upload

Schemas and documentation links

Detector	DB instance	URL	Description
OT	INT2R	https://cmsdca.cern.ch/trk_loader/trker/int2r/doc/doc https://cmsdca.cern.ch/trk_loader/trker/int2r/doc/xsd	Development DB
OT	CMSR	https://cmsdca.cern.ch/trk_loader/trker/cmsr/doc/doc https://cmsdca.cern.ch/trk_loader/trker/cmsr/doc/xsd	Production DB
HGCAL	INT2R	https://cmsdca.cern.ch/hgc_loader/hgc/int2r/doc/doc https://cmsdca.cern.ch/hgc_loader/hgc/int2r/doc/xsd	Development DB
HGCAL	CMSR	https://cmsdca.cern.ch/hgc_loader/hgc/cmsr/doc/doc https://cmsdca.cern.ch/hgc_loader/hgc/cmsr/doc/xsd	Production DB
MTD	INT2R	https://cmsdca.cern.ch/mtd_loader/mtd/int2r/doc/doc https://cmsdca.cern.ch/mtd_loader/mtd/int2r/doc/xsd	Development DB
GEM	INT2R	https://cmsdca.cern.ch/gem_loader/gem/int2r/doc/doc https://cmsdca.cern.ch/gem_loader/gem/int2r/doc/xsd	Development DB
GEM	OMDS	https://cmsdca.cern.ch/gem_loader/gem/omds/doc/doc https://cmsdca.cern.ch/gem_loader/gem/omds/doc/xsd	Production DB
PPS	INT2R	https://cmsdca.cern.ch/pps_loader/pps/int2r/doc/doc https://cmsdca.cern.ch/pps_loader/pps/int2r/doc/xsd	Development DB
ECAL	INT2R	https://cmsdca.cern.ch/ecal_loader/ecal/int2r/doc/doc https://cmsdca.cern.ch/ecal_loader/ecal/int2r/doc/xsd	Development DB

Documentation of XML schema

DB Loader automatically generates XML schema documentation from the database. It generates documentation from all tables dynamically. Not only from static tables.

First of all, user can see all tables in all schemas. Simply can iterate them.

Secondly, after selecting a specific table - users are able to see which fields are possible in this tag. As well as relations (1 or many, etc).

Read API

Resthub API is a restful service for database data access from CLI and Python applications. More about Resthub: https://github.com/valdasraps/resthub

Usage Examples

Client script/module is available from these locations:

GPN: /afs/cern.ch/user/v/valdo/public/rhapi.py
Web: wget https://raw.githubusercontent.com/valdasraps/resthub/master/clients/python/src/main/python/rhapi.py

Usage scenarios:

cp /afs/cern.ch/user/v/valdo/public/rhapi.py .

python3 rhapi.py --help

# List folders (format: detector_database)
python3 rhapi.py --url=http://dbloader-tracker:8113

# List folder tables
python3 rhapi.py --url=http://dbloader-tracker:8113 trker_int2r

# List table metadata
python3 rhapi.py --url=http://dbloader-tracker:8113 trker_int2r.kinds_of_part

# Execute query (default format: csv)
python3 rhapi.py --url=http://dbloader-tracker:8113 "select * from trker_int2r.kinds_of_part p"

# Count query result size
python3 rhapi.py --url=http://dbloader-tracker:8113 "select * from trker_int2r.kinds_of_part p" -c

# Data in JSON format
python3 rhapi.py --url=http://dbloader-tracker:8113 "select * from trker_int2r.kinds_of_part p" -f json

# Data in XML format
python3 rhapi.py --url=http://dbloader-tracker:8113 "select * from trker_int2r.kinds_of_part p" -f xml

# Paged output
python3 rhapi.py --url=http://dbloader-tracker:8113 "select * from trker_int2r.parts p" -s 20 -g 1
python3 rhapi.py --url=http://dbloader-tracker:8113 "select * from trker_int2r.parts p" -s 20 -g 2
python3 rhapi.py --url=http://dbloader-tracker:8113 "select * from trker_int2r.parts p" -s 20 -g 3

# Count number of parts by type
python3 /afs/cern.ch/user/v/valdo/public/rhapi.py --krb --url=https://cmsdca.cern.ch/hgc_rhapi "select p.KIND_OF_PART, count(p.ID) as count from hgc_int2r.parts p group by p.KIND_OF_PART"

# Join tables in a single query
python3 rhapi.py --url=http://dbloader-tracker:8113 "select d.* from trker_int2r.parts p, trker_int2r.datasets d where p.id = d.part_id order by d.insertion_time desc" -s 20

# Use query parameter
python3 rhapi.py --url=http://dbloader-tracker:8113 "select d.* from trker_int2r.parts p, trker_int2r.datasets d, trker_int2r.runs r where p.id = d.part_id and r.id = d.run_id and r.run_number = :run order by d.insertion_time desc" -s 20 -prun=1

# Grouping
python3 rhapi.py --url=http://dbloader-tracker:8113 "select p.serial_number as part, d.kind_of_condition as condition, count(d.id) as count from trker_int2r.parts p, trker_int2r.datasets d where p.id = d.part_id group by p.serial_number, d.kind_of_condition" -s 20

API Tables

Model of main tables

Main tables:

Get data for specific condition

Condition tables named c prefix and a unique number, e.g. c7860.

In order to find which condition table the user need he just need to know the actual condition table name.

It could be done by 2 different ways:

1. By resthub query: e.g. we know that condition table called - "TEST_SENSOR_IV".

1.1. We have to execute query

 python3 rhapi.py --url=http://dbloader-tracker:8113 "select * from trker_int2r.conditions c where c.DATABASE_TABLE = 'TEST_SENSOR_IV'" 

2. Another name is using WEB just go to the condition table. In our example, it is with OT https://cmsdca.cern.ch/trk_rhapi/table/trker_int2r/conditions/data (For different subsystem, it will be a different link)

The same is with parts. Just in order to the parts table name. The only difference is that we have to check the kinds_of_part table (see below).

Get specific kind of part details

1. depending on kind of part, each part can have custom values coming from extension table and attributes;

2. because of above, there is a special table for each kind of part which starts with p{KIND_OF_PART_ID};

3. exact table names can be looked up from here (see PART_TABLE):

https://cmsdca.cern.ch/hgc_rhapi/table/hgc_cmsr/kinds_of_part/data

4. p* tables have similar structure which comes from PARTS table with additional columns from extension table and/or attributes, i.e.

https://cmsdca.cern.ch/hgc_rhapi/table/hgc_cmsr/p1800

5. for more info you can dive into table template here:

https://github.com/valdasraps/resthub-loader/blob/master/src/main/resources/tables/part.xml#L54

Write API

Write API driven by DB Loader application provides write access to CLI and other applications. More about DB Loader: https://github.com/valdasraps/cmsdbldr

Usage Examples

Client script/module is available from these locations:

GPN: /afs/cern.ch/user/v/valdo/public/cmsdbldr_client.py
Web: wget https://raw.githubusercontent.com/valdasraps/cmsdbldr/master/src/main/python/cmsdbldr_client.py

Usage scenarios:

# Simple secure copy upload without client
# host and path parts must match your detector
scp testas.xml dbloader-tracker:/home/dbspool/spool/trk/int2r

# With client from lxplus or other AFS based machine
python3 cmsdbldr_client.py --krb --url=https://dbloader-tracker/trker/int2r PHS_testas.xml

# From outside (see section below) with lightweight account login
python3 cmsdbldr_client.py  --login --url=https://cmsdca.cern.ch/trk_loader/trker/int2r PHS_testas.zip

Minimal runtime env for CentOS/RHEL x86_64

In order to execute scripts in the machine which you do not have root rights, the prepared virtual environment can be downloaded from

GPN: /afs/cern.ch/user/v/valdo/public/venv.tar.gz
Web: https://github.com/valdasraps/resthub/blob/master/clients/python/src/deploy/venv.tar.gz?raw=true

Installation and usage:

tar xfz venv.tar.gz 
source venv/bin/activate
python3 rhapi.py --url=https://cmsdca.cern.ch/trk_rhapi --login
deactivate

Note: this is a minimal runtime environment and does not contain various advanced features like ROOT support, etc.

Authorization for secure access (https)

Kerberos login

Make sure that auth-get-sso-cookie utility is installed, i.e. check

auth-get-sso-cookie -h

If this utility is available you can proceed in executing application, i.e. from lxplus

python3 cmsdbldr_client.py --krb --url=https://cmsdca.cern.ch/trk_loader/trker/int2r LoadTrackerUmJoSensorWafers.xml

python3 rhapi.py --krb --url=https://cmsdca.cern.ch/trk_rhapi

Lightweight user login

In case you do not have CERN account or intend to use it for application permanent access, please request CERN Lightweight account from https://account.cern.ch/account/Externals/RegisterAccount.aspx

Request account to be added to certain e-group if needed (see above). Make sure you have required dependencies installed, i.e.

sudo pip3 install requests ilock

Execute upload job. During the first execution the script the will ask username and password. Both will be cached (stored) in a file .session.cache for other consequent uses. Cookies once expired will be refreshed automatically:

python3 cmsdbldr_client.py --login --url=https://cmsdca.cern.ch/trk_loader/trker/int2r LoadTrackerUmJoSensorWafers.xml

python3 rhapi.py --login --url=https://cmsdca.cern.ch/trk_rhapi

• ValdasRapsevicius - 2019-10-25
• ValdasRapsevicius - 2020-08-19
• AivarasSilale - 2020-08-21
• AivarasSilale - 2020-09-29
• ValdasRapsevicius - 2021-01-14