 |
 |
Hi, Serguei. Apologies for not getting back to you sooner about this. My suggestion here is that we start with a minimal run database. I am working with the following table structure locally to test this ... mysql> describe run_log; +--------------+------------+------+-----+---------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +--------------+------------+------+-----+---------------------+-----------------------------+ | run | int(11) | NO | PRI | NULL | | | start_date | datetime | YES | | NULL | | | end_date | datetime | YES | | NULL | | | nevents | int(11) | YES | | NULL | | | nfiles | int(11) | YES | | NULL | | | end_ok | tinyint(1) | YES | | NULL | | | last_updated | timestamp | NO | | 0000-00-00 00:00:00 | on update CURRENT_TIMESTAMP | | created | timestamp | NO | | CURRENT_TIMESTAMP | | +--------------+------------+------+-----+---------------------+-----------------------------+ mysql> describe run_log_files; +-----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | run | int(11) | NO | | NULL | | | directory | varchar(255) | NO | | NULL | | | name | varchar(255) | NO | | NULL | | +-----------+--------------+------+-----+---------+----------------+ This ends up looking like the following (just used one file here so it is only an example) ... mysql> select * from run_log; +------+---------------------+---------------------+---------+--------+--------+---------------------+---------------------+ | run | start_date | end_date | nevents | nfiles | end_ok | last_updated | created | +------+---------------------+---------------------+---------+--------+--------+---------------------+---------------------+ | 5077 | 2015-04-26 14:44:02 | 2015-04-26 14:45:56 | 275828 | 1 | 0 | 2015-05-04 13:36:00 | 2015-05-04 13:36:00 | +------+---------------------+---------------------+---------+--------+--------+---------------------+---------------------+ 1 row in set (0.01 sec) mysql> select * from run_log_files; +----+------+-------------------+-------------------+ | id | run | directory | name | +----+------+-------------------+-------------------+ | 1 | 5077 | /u1/data/hps/test | hps_005077.evio.0 | +----+------+-------------------+-------------------+ 1 row in set (0.00 sec) This information can be recovered by crawling our EVIO data and running batch jobs to extract the information (it will take a long time for each job but each run can have a separate batch job). EPICS events and scalar data can also be read later to produce run log information for the appropriate parameters (beam energy, magnet settings, etc.) But these I would store in separate tables rather than take the approach here of having very large tables including all kinds of hall and detector conditions for the run. This will allow easily dropping and recreating the conditions type information independently from the primary run log info. To know exactly what should be stored per run outside the basic information (start date, end date, number of events, etc.) I will need more input from sub-system experts. For instance, we'd need to figure out how to calculate values from EPICS and scalar values, what time period sampling to use, and if it should be a simple mean of all the sampled values, etc. --Jeremy -----Original Message----- From: Serguei Boiarinov [mailto:[log in to unmask]] Sent: Friday, February 13, 2015 10:57 AM To: McCormick, Jeremy I. Cc: Nathan Baltzell; Serguei Boiarinov; Stepan Stepanyan Subject: Re: run database Hi Jeremy, Nathan, to start with runtime database, let me show what we used to have in CLAS. Four tables were filled on various stages of DAQ: run_log_begin (at Prestart), run_log_comment, run_log_end and run_log_files (at End). Several extra tables were used for specific devices (run_log_polar, run_log_rtpc, run_log_tpe). First 4 tables had following structure: mysql> describe run_log_begin; +--------------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+--------------+------+-----+---------+-------+ | session_name | varchar(20) | YES | | NULL | | | run | int(11) | YES | MUL | NULL | | | start_date | datetime | YES | | NULL | | | configuration | varchar(20) | YES | | NULL | | | trigger_config | varchar(80) | YES | | NULL | | | channel_config | varchar(80) | YES | | NULL | | | ts_file | varchar(80) | YES | | NULL | | | l1_program | varchar(120) | YES | | NULL | | | ts_csr | int(11) | YES | | NULL | | | ts_control | int(11) | YES | | NULL | | | ts_roc_enable | int(11) | YES | | NULL | | | ts_synch | int(11) | YES | | NULL | | | prescale_1 | int(11) | YES | | NULL | | | prescale_2 | int(11) | YES | | NULL | | | prescale_3 | int(11) | YES | | NULL | | | prescale_4 | int(11) | YES | | NULL | | | prescale_5 | int(11) | YES | | NULL | | | prescale_6 | int(11) | YES | | NULL | | | prescale_7 | int(11) | YES | | NULL | | | prescale_8 | int(11) | YES | | NULL | | | ts_timer_1 | int(11) | YES | | NULL | | | ts_timer_2 | int(11) | YES | | NULL | | | ts_timer_3 | int(11) | YES | | NULL | | | ts_timer_4 | int(11) | YES | | NULL | | | ts_timer_5 | int(11) | YES | | NULL | | | sc_spar | varchar(50) | YES | | NULL | | | cc_spar | varchar(50) | YES | | NULL | | | ec1_spar | varchar(50) | YES | | NULL | | | ec2_spar | varchar(50) | YES | | NULL | | | lac_spar | varchar(50) | YES | | NULL | | | beam_energy | float | YES | | NULL | | | thermionic_gun | float | YES | | NULL | | | polarized_gun | float | YES | | NULL | | | a_slit_position | float | YES | | NULL | | | b_slit_position | float | YES | | NULL | | | c_slit_position | float | YES | | NULL | | | radiator_position | float | YES | | NULL | | | torus_current | float | YES | | NULL | | | mini_current | float | YES | | NULL | | | mini_voltage | float | YES | | NULL | | | tagger_current | float | YES | | NULL | | | tagger_voltage | float | YES | | NULL | | | cryo_pressure | float | YES | | NULL | | | cryo_temperature | float | YES | | NULL | | | cryo_status | float | YES | | NULL | | | upstream_beam_vac | float | YES | | NULL | | | target_vac | float | YES | | NULL | | | halo_up_up | float | YES | | NULL | | | halo_up_down | float | YES | | NULL | | | halo_up_left | float | YES | | NULL | | | halo_up_right | float | YES | | NULL | | | halo_down_up | float | YES | | NULL | | | halo_down_down | float | YES | | NULL | | | halo_down_left | float | YES | | NULL | | | halo_down_right | float | YES | | NULL | | | bpm_1_x | float | YES | | NULL | | | bpm_1_y | float | YES | | NULL | | | bpm_1_i | float | YES | | NULL | | | bpm_2_x | float | YES | | NULL | | | bpm_2_y | float | YES | | NULL | | | bpm_2_i | float | YES | | NULL | | | bpm_3_x | float | YES | | NULL | | | bpm_3_y | float | YES | | NULL | | | bpm_3_i | float | YES | | NULL | | | ec_inner_lo | int(11) | YES | | NULL | | | ec_inner_hi | int(11) | YES | | NULL | | | ec_outer_lo | int(11) | YES | | NULL | | | ec_outer_hi | int(11) | YES | | NULL | | | ec_total_lo | int(11) | YES | | NULL | | | ec_total_hi | int(11) | YES | | NULL | | | cc_hi | int(11) | YES | | NULL | | | cc_lo | int(11) | YES | | NULL | | | sc_thresh | int(11) | YES | | NULL | | | sc_width | int(11) | YES | | NULL | | | helicity_clock | float | YES | | NULL | | | helicity_t_settle | float | YES | | NULL | | | helicity_t_stable | float | YES | | NULL | | | helicity_delay | float | YES | | NULL | | | helicity_pattern | float | YES | | NULL | | | helicity_frequency | float | YES | | NULL | | | helicity_wien_angle_zy | float | YES | | NULL | | | helicity_wien_angle_yx | float | YES | | NULL | | | helicity_wien_angle_zx | float | YES | | NULL | | | helicity_half_wave_plate | float | YES | | NULL | | | nmr_polarization_lf | float | YES | | NULL | | | magnet_current_lf | float | YES | | NULL | | | cohbrems_edge | float | YES | | NULL | | | cohbrems_plane | float | YES | | NULL | | | cohbrems_radiator | float | YES | | NULL | | +--------------------------+--------------+------+-----+---------+-------+ 89 rows in set (0.00 sec) mysql> describe run_log_end; +------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+-------------+------+-----+---------+-------+ | session_name | varchar(20) | YES | | NULL | | | run | int(11) | YES | MUL | NULL | | | end_date | datetime | YES | | NULL | | | end_ok | char(1) | YES | | NULL | | | filebase | varchar(50) | YES | | NULL | | | nfile | int(11) | YES | | NULL | | | nevent | int(11) | YES | | NULL | | | nerror | int(11) | YES | | NULL | | | nlong | int(11) | YES | | NULL | | | fcup | int(11) | YES | | NULL | | | fcup_active | int(11) | YES | | NULL | | | fcup_live | int(11) | YES | | NULL | | | clock | int(11) | YES | | NULL | | | clock_active | int(11) | YES | | NULL | | | clock_live | int(11) | YES | | NULL | | | trig_presc_bit1 | int(11) | YES | | NULL | | | trig_presc_bit2 | int(11) | YES | | NULL | | | trig_presc_bit3 | int(11) | YES | | NULL | | | trig_presc_bit4 | int(11) | YES | | NULL | | | trig_presc_bit5 | int(11) | YES | | NULL | | | trig_presc_bit6 | int(11) | YES | | NULL | | | trig_presc_bit7 | int(11) | YES | | NULL | | | trig_presc_bit8 | int(11) | YES | | NULL | | | trig_presc_bit9 | int(11) | YES | | NULL | | | trig_presc_bit10 | int(11) | YES | | NULL | | | trig_presc_bit11 | int(11) | YES | | NULL | | | trig_presc_bit12 | int(11) | YES | | NULL | | | trig_presc_all | int(11) | YES | | NULL | | | trig_event_bit1 | int(11) | YES | | NULL | | | trig_event_bit2 | int(11) | YES | | NULL | | | trig_event_bit3 | int(11) | YES | | NULL | | | trig_event_bit4 | int(11) | YES | | NULL | | | trig_event_bit5 | int(11) | YES | | NULL | | | trig_event_bit6 | int(11) | YES | | NULL | | | trig_event_bit7 | int(11) | YES | | NULL | | | trig_event_bit8 | int(11) | YES | | NULL | | | trig_event_bit9 | int(11) | YES | | NULL | | | trig_event_bit10 | int(11) | YES | | NULL | | | trig_event_bit11 | int(11) | YES | | NULL | | | trig_event_bit12 | int(11) | YES | | NULL | | | trig_event_all | int(11) | YES | | NULL | | | trig_file_bit1 | int(11) | YES | | NULL | | | trig_file_bit2 | int(11) | YES | | NULL | | | trig_file_bit3 | int(11) | YES | | NULL | | | trig_file_bit4 | int(11) | YES | | NULL | | | trig_file_bit5 | int(11) | YES | | NULL | | | trig_file_bit6 | int(11) | YES | | NULL | | | trig_file_bit7 | int(11) | YES | | NULL | | | trig_file_bit8 | int(11) | YES | | NULL | | | trig_file_bit9 | int(11) | YES | | NULL | | | trig_file_bit10 | int(11) | YES | | NULL | | | trig_file_bit11 | int(11) | YES | | NULL | | | trig_file_bit12 | int(11) | YES | | NULL | | | trig_file_all | int(11) | YES | | NULL | | +------------------+-------------+------+-----+---------+-------+ 54 rows in set (0.00 sec) mysql> describe run_log_comment; +----------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+--------------+------+-----+---------+-------+ | session_name | varchar(20) | YES | | NULL | | | run | int(11) | YES | MUL | NULL | | | entry_date | datetime | YES | | NULL | | | run_type | varchar(20) | YES | | NULL | | | target | varchar(25) | YES | | NULL | | | beam_current_request | float | YES | | NULL | | | mini_raster | char(3) | YES | | NULL | | | operators | varchar(100) | YES | | NULL | | | comment | text | YES | | NULL | | | ignore_run | char(1) | YES | | NULL | | | logbook_book | int(11) | YES | | NULL | | | logbook_page | int(11) | YES | | NULL | | +----------------------+--------------+------+-----+---------+-------+ 12 rows in set (0.00 sec) mysql> describe run_log_files; +--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | session_name | varchar(20) | | | | | | run | int(11) | | MUL | 0 | | | location | varchar(200) | | | | | | filename | varchar(100) | | | | | | nlong | int(11) | | | 0 | | | nevent | int(11) | | | 0 | | | nerror | int(11) | | | 0 | | +--------------+--------------+------+-----+---------+-------+ 7 rows in set (0.00 sec) As you can see different entries were coming from different sources: epics_server collecting various epics channels, daq components, scripts with popup guis to let user type operator names, etc. I'd suggest to define HPS-specific table(s), and then we can decide how to fill it in. Sergey ----- Original Message ----- From: "Jeremy I. McCormick" <[log in to unmask]> To: "Serguei Boiarinov" <[log in to unmask]> Sent: Tuesday, January 27, 2015 11:46:59 AM Subject: run database Hi, I can try and help with this… Can you point me to any documentation? Thanks. —Jeremy ######################################################################## Use REPLY-ALL to reply to list To unsubscribe from the HPS-SOFTWARE list, click the following link: https://listserv.slac.stanford.edu/cgi-bin/wa?SUBED1=HPS-SOFTWARE&A=1