CDB Editor Scripts and There Usage

The data within the CDB may be edited but ONLY if you are AUTHORISED to do this and have taken precautions with the current database (i.e. back it up first) if in doubt don't use this program.

The edit-cdb program changes the CDB by applying a change set to it. The change set may be written manually but more often is auto generated from differences between the shifters run spreadsheet and the cdb using the cdb-comparison program.

CDB Comparison

cdb-comparison program compares the cdb to a spreadsheet and produce a list of changes on stdout. Usage:

  cdb-comparison <spreadsheet file> <cdb host> <start run> <end run> [change set id (defaults to date)]

  • spreadsheet file - the shifter excel spreadsheet (with corrected dates) [if this is a url the scipt will download the file first]
  • cdb host - the host of the CDB web API (e.g. if you visit <cdb_host>/cdb/beamline?wsdl you would see the wsdl)
  • start run - the first run to compare (inclusive)
  • end run - the last run to compare (inclusive)
  • change set id (defaults to the current date) - the change set id for the final change set file to have

Example: shifter_spreadsheet.xls 6000 7000 "7102 HOLT" 

The output of this process is a change set.

Only the following variables are compared in this program (although the edit-cdb program can change any variable):

  • daq_trigger
  • daq_gate_width
  • proton_absorber_thickness
  • start_pulse
  • end_pulse
  • magnets:X:set_current
  • magnets:X:polarity (compared with overall polarity)

These values are set at the top of the script and can be added to if required. They assume that the column order in the spreadsheet is fixed. The other thing that will be checked is the start and end date. If these are missing in the CDB then the program will generate them from the spreadsheet by looking at the previous time and next time.

Change Set

A change set is a csv file of changes to make to the cdb.

The file must contain a single change set id, usually at the top, which is set with the line:

 Change set, <change set id>

After this each line is a single change to a single value of a single run. The values appearing on each line are shown in the table:

Columns Required in edit-cdb Description
Run number Y Run number to change
Variable name Y name of Variable (see below)
CDB Value Y Current value in the cdb, blank for not in cdb
New Value (from spreadsheet) Y Value to change it to, often from the shifter's spreadsheet
Change to New Value (Y/N) Y Y to make this change, N to not make the change
ToF1 Triggers N Number of ToF1 Triggers
ToF2 Triggers N Number of ToF2 Triggers
optics N Optics for the run
Notes for run N Any notes on the run

The name of the variable is the name as it appears coming from the python CDB API. Some of these variables are in dictionaries, to set these separate the dictionary and variable name with a colon. For example to set the current of the DS magnet uses the variable name magnets:DS:set_current.

Edit CDB

The CDB editing program uses a change set file (often produced by CDB Comparison) and enacts it on the cdb.


  • SOURCE - the csv file
  • HOST - the webservice host (e.g. if you visit <HOST>/cdb/beamline?wsdl you would see the wsdl)
  • -d DESTINATION - Send the changes to this CDB host instead of HOST

The program uses the CDB python API to alter the runs in the database. It consolidates all the changes to a single run before calling the API; although the API is in 3 parts so the change is not atomic.
The only extra change it makes is to append the change id to the end notes along with the current program version number.

If you wish just to copy a run this is possible by changing the run number (set the variable to run_number).


If you want to install the utilities to work form the command line then:

cd <dir containing>
pip install .

Otherwise just install dependencies using pip. The dependencies are:

  • xlrd>=0.9.4
  • cdb>=1.1.3 - This is the python cdb access layer (see wiki to install)


There are a full set of tests for the cdb-edit program. These will alter the database so do not run them on production. Run the tests with:

  nosetests .

You will need pyhamcrest and psycopg2 packages installed to run them.

Updated by Holt, John over 8 years ago ยท 5 revisions