Difference between revisions of "G2pmysql"
From Hall A Wiki
(→Organization of AnaInfo) |
(→Organization of AnaInfo) |
||
| Line 317: | Line 317: | ||
Currently (8/13/12) four fields in the AnaInfo tables are expert determined and by expert determined I mean they do not directly tie back to an EPICS variable. These are, SeptaStatus,RunStatus,RunQuality and ExpertC (or Expert Comment). I will briefly describe the function of each one and how it is determined. | Currently (8/13/12) four fields in the AnaInfo tables are expert determined and by expert determined I mean they do not directly tie back to an EPICS variable. These are, SeptaStatus,RunStatus,RunQuality and ExpertC (or Expert Comment). I will briefly describe the function of each one and how it is determined. | ||
| − | ======SeptaStatus | + | ======SeptaStatus====== |
During the running of the experiment the RHRS Septa magnet experienced two separate issues which damaged the coil packages. This field exists for both LHRS and RHRS. The SeptaStatus field takes the following values: | During the running of the experiment the RHRS Septa magnet experienced two separate issues which damaged the coil packages. This field exists for both LHRS and RHRS. The SeptaStatus field takes the following values: | ||
* 0 - RHRS septa operating with all coil packages (48-48-16) | * 0 - RHRS septa operating with all coil packages (48-48-16) | ||
| Line 326: | Line 326: | ||
| − | ======RunStatus | + | ======RunStatus====== |
This is similar to the RunType field in RunInfo except here it is Expert determined instead of Shift Worker controlled. RunStatus takes the following values: | This is similar to the RunType field in RunInfo except here it is Expert determined instead of Shift Worker controlled. RunStatus takes the following values: | ||
* 1 - g2p Production | * 1 - g2p Production | ||
Revision as of 14:54, 13 August 2012
Below is a description of the g2p mysql database. It will be continuously updated with new features as they're requested and as I think of them. So if you think of something you would like the database to do, just let me know.
-Ryan
List of good mysql tutorial websites.
http://www.tutorialspoint.com/mysql/index.htm http://www.tizag.com/mysqlTutorial/ http://www.dreamincode.net/forums/topic/42301-beginners-guide-to-creating-simple-mysql-tables/
Installing a MYSQL Server on your own machine
http://dev.mysql.com/doc/refman/5.0/en/macosx-installation.html http://dev.mysql.com/doc/refman/5.0/en/windows-installation.html http://dev.mysql.com/doc/refman/5.0/en/linux-installation-rpm.html Note: Default ROOT password for mysql user is blank
Useful Files
g2p mysql DB backups
- Backup_List gives some information on the backups
g2p mysql Table Descriptions
There are two main categories of tables in the g2p mysql database. The first category includes tables that were filled with data online. The fields were filled either at the start or end of run with a snapshot of the EPICS information associated with the table variable or with summary information from the end of run script. Some of this information was provided by the shift worker so one should be careful with these tables. These tables are the RunInfo Tables and there is one for each DAQ arm (Left, Right, Third). As of 8/13/2012 these tables will no longer be updated!
The second category of tables are AnaInfo, analysis tables. The data in these tables is either filled from the EPICS information from the accumulated data files or via a student "expert". In this regard, these tables should be used be used for the analysis. They will be continually updated with more data fields as needed in the analysis process and errors will be corrected as they are discovered.
The AnaInfo were created from the RunInfo tables. This means that all the runs that exist in RunInfo exist in AnaInfo.
RunInfo Tables
Inside the g2p database are the following RunInfo tables: mysql> show Tables; +---------------+ | Tables_in_g2p | +---------------+ | RunInfoL | | RunInfoR | | RunInfoT | +---------------+ 3 rows in set (0.00 sec)
Table Descriptions (for the last column I've described how that particular field is filled):
mysql> describe RunInfoL; +--------------------+------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+------------+------+-----+-------------------+-------+ | RunNumber | int(11) | NO | PRI | 0 | | Start of Run | RunStartTime | datetime | YES | | NULL | | Start of Run | EntryTime | timestamp | NO | | CURRENT_TIMESTAMP | | | RunTime | float | YES | | NULL | | End of Run | TargetPosition | float | YES | | NULL | | Start of Run | TargetPolarization | float | YES | | NULL | | Start of Run | TargetField | float | YES | | NULL | | Start of Run | LeftMomentum | float | YES | | NULL | | Start of Run | LeftAngle | float | YES | | NULL | | Start of Run | LeftSeptum | float | YES | | NULL | | Start of Run | Energy | float | YES | | NULL | | Start of Run | Current | float | YES | | NULL | | Start of Run | Events | int(11) | YES | | NULL | | End of Run | Deadtime | float | YES | | NULL | | End of Run | BCM_Current | float | YES | | NULL | | End of Run | u1_Charge | float | YES | | NULL | | End of Run | d1_Charge | float | YES | | NULL | | End of Run | HalfWavePlate | longtext | YES | | NULL | | Start of Run | RunType | longtext | YES | | NULL | | End of Run (shift worker controlled!) | TargetType | longtext | YES | | NULL | | End of Run (shift worker controlled!) | sw_comment | longtext | YES | | NULL | | End of Run (shift worker controlled!) | GoodRun | tinyint(1) | YES | | NULL | | | T3prescaled | int(11) | YES | | NULL | | End of Run | T3real | int(11) | YES | | NULL | | End of Run | T4prescaled | int(11) | YES | | NULL | | End of Run | T4real | int(11) | YES | | NULL | | End of Run | T7prescaled | int(11) | YES | | NULL | | End of Run | T7real | int(11) | YES | | NULL | | End of Run | T8prescaled | int(11) | YES | | NULL | | End of Run | T8real | int(11) | YES | | NULL | | End of Run | NMRArea | float | YES | | NULL | | Start of Run | muFreq | float | YES | | NULL | | Start of Run | T3realRate | float | YES | | NULL | | End of Run | T4realRate | float | YES | | NULL | | End of Run | T3preRate | float | YES | | NULL | | End of Run | T4preRate | float | YES | | NULL | | End of Run | ps3 | float | YES | | NULL | | End of Run | ps4 | float | YES | | NULL | | End of Run | ps7 | float | YES | | NULL | | End of Run | ps8 | float | YES | | NULL | | End of Run | FastRasterIx | float | YES | | NULL | | Start of Run | FastRasterIy | float | YES | | NULL | | Start of Run | SlowRasterIy | float | YES | | NULL | | Start of Run | SlowRasterIx | float | YES | | NULL | | Start of Run | TargetEncoder | float | YES | | NULL | | Start of Run | SeptaStatus | int(11) | YES | | NULL | | | RunStatus | int(11) | YES | | NULL | | +--------------------+------------+------+-----+-------------------+-------+ 47 rows in set (0.01 sec)
mysql> describe RunInfoR; +--------------------+------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+------------+------+-----+-------------------+-------+ | RunNumber | int(11) | NO | PRI | 0 | | | RunStartTime | datetime | YES | | NULL | | | EntryTime | timestamp | NO | | CURRENT_TIMESTAMP | | | RunTime | float | YES | | NULL | | | TargetPosition | float | YES | | NULL | | | TargetPolarization | float | YES | | NULL | | | TargetField | float | YES | | NULL | | | RightMomentum | float | YES | | NULL | | | RightAngle | float | YES | | NULL | | | RightSeptum | float | YES | | NULL | | | Energy | float | YES | | NULL | | | Current | float | YES | | NULL | | | Events | int(11) | YES | | NULL | | | Deadtime | float | YES | | NULL | | | BCM_Current | float | YES | | NULL | | | u1_Charge | float | YES | | NULL | | | d1_Charge | float | YES | | NULL | | | HalfWavePlate | longtext | YES | | NULL | | | RunType | longtext | YES | | NULL | | | TargetType | longtext | YES | | NULL | | | sw_comment | longtext | YES | | NULL | | | GoodRun | tinyint(1) | YES | | NULL | | | T1prescaled | int(11) | YES | | NULL | | | T1real | int(11) | YES | | NULL | | | T2prescaled | int(11) | YES | | NULL | | | T2real | int(11) | YES | | NULL | | | T7prescaled | int(11) | YES | | NULL | | | T7real | int(11) | YES | | NULL | | | T8prescaled | int(11) | YES | | NULL | | | T8real | int(11) | YES | | NULL | | | NMRArea | float | YES | | NULL | | | muFreq | float | YES | | NULL | | | T1realRate | float | YES | | NULL | | | T2realRate | float | YES | | NULL | | | T1preRate | float | YES | | NULL | | | T2preRate | float | YES | | NULL | | | ps1 | float | YES | | NULL | | | ps2 | float | YES | | NULL | | | ps7 | float | YES | | NULL | | | ps8 | float | YES | | NULL | | | SlowRasterIx | float | YES | | NULL | | | SlowRasterIy | float | YES | | NULL | | | FastRasterIy | float | YES | | NULL | | | FastRasterIx | float | YES | | NULL | | | TargetEncoder | float | YES | | NULL | | | SeptaStatus | int(11) | YES | | NULL | | | RunStatus | int(11) | YES | | NULL | | +--------------------+------------+------+-----+-------------------+-------+ 47 rows in set (0.00 sec)
mysql> describe RunInfoT; +--------------------+------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+------------+------+-----+-------------------+-------+ | RunNumber | int(11) | NO | PRI | 0 | | | RunStartTime | datetime | YES | | NULL | | | EntryTime | timestamp | NO | | CURRENT_TIMESTAMP | | | RunTime | float | YES | | NULL | | | LeftMomentum | float | YES | | NULL | | | RightMomentum | float | YES | | NULL | | | Energy | float | YES | | NULL | | | Current | float | YES | | NULL | | | Events | int(11) | YES | | NULL | | | Deadtime | float | YES | | NULL | | | BCM_Current | float | YES | | NULL | | | u1_Charge | float | YES | | NULL | | | d1_Charge | float | YES | | NULL | | | HalfWavePlate | longtext | YES | | NULL | | | RunType | longtext | YES | | NULL | | | TargetType | longtext | YES | | NULL | | | sw_comment | longtext | YES | | NULL | | | GoodRun | tinyint(1) | YES | | NULL | | | T1prescaled | int(11) | YES | | NULL | | | T1real | int(11) | YES | | NULL | | | T8prescaled | int(11) | YES | | NULL | | | T8real | int(11) | YES | | NULL | | | T1realRate | float | YES | | NULL | | | T1preRate | float | YES | | NULL | | | ps1 | int(11) | YES | | NULL | | | TargetPolarization | float | YES | | NULL | | | TargetPosition | float | YES | | NULL | | | TargetField | float | YES | | NULL | | | NMRArea | float | YES | | NULL | | | muFreq | float | YES | | NULL | | | TargetEncoder | float | YES | | NULL | | +--------------------+------------+------+-----+-------------------+-------+ 31 rows in set (0.00 sec)
Modifications made to RunInfo Tables after Run Period
After the run period, a few minor issues were corrected with the RunInfo tables. The changes are listed below. It's listed as a snapshot of what I was working on during the period right after the end of the experiment, so it might not read very well. My apologies.
- Backed up DB - 6/4/12
- /adaqfs/home/adaq/g2p/g2pbackup.sql - This is a backup of the original DB prior to any modifications
- I also have a copy of this file on my own computer
- Updated Target Encoder Position for both L/R HRS - 6/4/12
- The entry is filled from the start of run so you want to check with the end of run and make sure this updated correctly. Most problems occur around dilution runs due to the relatively quick moving of the target.
- /adaqfs/home/a-onl/scripts/mysql/FixTargetPosition_g2p(R).pl accesses end of run files and updates the DB
- /adaqfs/home/a-onl/scripts/mysql/Target.sh loops through however many runs you want to using FixTargetPosition_g2p(R).pl
- Updated Target Number for both L/R HRS - 6/7/12
- Updated FixTargetPosition_g2p.pl to do this
- Fixed a bug so perl script now correctly finds negative encoder values
- Renamed Target.sh to mysql_g2p.sh 6/12/12
- Updated HWP Status using FixHWP_g2p(R).pl 6/12/12
- HWP Status. Because the entry is filled at the start of run, what to make sure that it updated before a new run was started.
- Updated PS Status using FixPS_g2p(R).pl 6/12/12
- Up until a certain point there was a bug in the code so that the DB scripts only recorded the first digit of the prescale factor. So a PS of 10 would only record as a PS of 1. This was eventually fixed but the runs need to be checked
- Create a script to parse EPICS files from run period epics.pl 6/13/12
- Located on ifarm /w/halla-1/g2p/ryan/mysql
- EPICS files located at w/halla-1/g2p/epics
- Currently set up to loop through all the lines of EPICS file and compute the average of an EPICS variable between a start and stop time. This will eventually be tied to run times!
- Epics variables and descriptions can be found here https://hallaweb.jlab.org/wiki/index.php/G2p_epics
Things to check for/To Do List:
- Based off of Encoder position should be able to correct Target Type and Target Number in beam. Encoder positions are in ().
- Decided to only update the Target Number because this was determined via EPICS so it is definitely not right. The Target Type could be useful because it was shift worker controlled, so if the Target Encoder was not reporting to EPICS correctly the Target Type may be correct. I stress 'may' because you have to trust the shift worker.
- Carbon - (10,000 , 15,000) 6 (at some point Carbon target became thicker but not sure when this should be checked)
- Carbon Hole - (41,000 , 45,000) 2
- CH2 - ( 37,000 , 41,000 ) 3
- NH3 (Top/Bottom determined by Target Number) - ( 28,000 , 35,000) 4 (45,000 , 51,000) 1
- Dummy - (20,000 , 25,000) 5
- Home - < 1000 7
- If 999 or 0 Target Encoder is not reporting to EPICS - Record Target Number as 0
- Important Notes on Target Variable Epics reporting: Going through the HALOG I don't see Target Encoder Variables accurately reporting until Runs 3220/22305 on 3/16/12. See http://hallaweb.jlab.org/halog/log/html/1203_archive/120316153201.html
- Should base my mysql stuff off of this: https://userweb.jlab.org/~singhj/runsummary/
AnaInfo Tables
Below is a snapshot of the AnaInfo tables on 8/13/12. More fields will be added as time goes on.
mysql> show Tables; +---------------+ | Tables_in_g2p | +---------------+ | AnaInfoL | | AnaInfoR | | RunInfoL | | RunInfoR | | RunInfoT | +---------------+ 5 rows in set (0.00 sec)
mysql> describe AnaInfoL; +---------------+-----------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +---------------+-----------+------+-----+-------------------+-----------------------------+ | RunNumber | int(11) | NO | PRI | 0 | | | RunStartTime | datetime | YES | | NULL | | | EntryTime | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | ps3 | float | YES | | NULL | | Copied directly from RunInfo | ps4 | float | YES | | NULL | | Copied directly from RunInfo | ps7 | float | YES | | NULL | | Copied directly from RunInfo | ps8 | float | YES | | NULL | | Copied directly from RunInfo | TargetEncoder | float | YES | | NULL | | Avg value from EPICS/raw data | SeptaStatus | int(11) | YES | | NULL | | Expert Determined | RunStatus | int(11) | YES | | NULL | | Expert Determined | RunQuality | int(11) | YES | | NULL | | Expert Determined | Q1p | float | YES | | NULL | | Avg value from EPICS/raw data | Q1pSTD | float | YES | | NULL | | Avg value from EPICS/raw data | Q2p | float | YES | | NULL | | Avg value from EPICS/raw data | Q2pSTD | float | YES | | NULL | | Avg value from EPICS/raw data | D1p | float | YES | | NULL | | Avg value from EPICS/raw data | D1pSTD | float | YES | | NULL | | Avg value from EPICS/raw data | Q3p | float | YES | | NULL | | Avg value from EPICS/raw data | Q3pSTD | float | YES | | NULL | | Avg value from EPICS/raw data | SeptaI | float | YES | | NULL | | Avg value from EPICS/raw data | SeptaSTD | float | YES | | NULL | | Avg value from EPICS/raw data | TargetSTD | float | YES | | NULL | | Avg value from EPICS/raw data | Ihwp | int(11) | YES | | NULL | | Avg value from EPICS/raw data | IhwpSTD | float | YES | | NULL | | Avg value from EPICS/raw data | Energy | float | YES | | NULL | | Avg value from EPICS/raw data (HALLA:p) | EnergySTD | float | YES | | NULL | | Avg value from EPICS/raw data (HALLA:p) | ExpertC | longtext | YES | | NULL | | Expert Determined +---------------+-----------+------+-----+-------------------+-----------------------------+ 27 rows in set (0.00 sec)
mysql> describe AnaInfoR; +---------------+-----------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +---------------+-----------+------+-----+-------------------+-----------------------------+ | RunNumber | int(11) | NO | PRI | 0 | | | RunStartTime | datetime | YES | | NULL | | | EntryTime | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | ps1 | float | YES | | NULL | | | ps2 | float | YES | | NULL | | | ps7 | float | YES | | NULL | | | ps8 | float | YES | | NULL | | | TargetEncoder | float | YES | | NULL | | | SeptaStatus | int(11) | YES | | NULL | | | RunStatus | int(11) | YES | | NULL | | | RunQuality | int(11) | YES | | NULL | | | Q1p | float | YES | | NULL | | | Q1pSTD | float | YES | | NULL | | | Q2p | float | YES | | NULL | | | Q2pSTD | float | YES | | NULL | | | D1p | float | YES | | NULL | | | D1pSTD | float | YES | | NULL | | | Q3p | float | YES | | NULL | | | Q3pSTD | float | YES | | NULL | | | SeptaI | float | YES | | NULL | | | SeptaSTD | float | YES | | NULL | | | TargetSTD | float | YES | | NULL | | | Ihwp | int(11) | YES | | NULL | | | IhwpSTD | float | YES | | NULL | | | Energy | float | YES | | NULL | | | EnergySTD | float | YES | | NULL | | | ExpertC | longtext | YES | | NULL | | +---------------+-----------+------+-----+-------------------+-----------------------------+ 27 rows in set (0.00 sec)
Organization of AnaInfo
Currently (8/13/12) four fields in the AnaInfo tables are expert determined and by expert determined I mean they do not directly tie back to an EPICS variable. These are, SeptaStatus,RunStatus,RunQuality and ExpertC (or Expert Comment). I will briefly describe the function of each one and how it is determined.
SeptaStatus
During the running of the experiment the RHRS Septa magnet experienced two separate issues which damaged the coil packages. This field exists for both LHRS and RHRS. The SeptaStatus field takes the following values:
- 0 - RHRS septa operating with all coil packages (48-48-16)
- 1 - RHRS septa after 1st issue but before 2nd (40-32-16)
- 2 - RHRS septa after the 2nd problem (40-00-16)
The field values were determined by reading the HALOG to determine when the problems occurred. During transition periods from one status to another it is possible that SeptaStatus value maybe incorrect but this does not affect Production or Optics running, only calibration runs taking with no beam in the Hall.
RunStatus
This is similar to the RunType field in RunInfo except here it is Expert determined instead of Shift Worker controlled. RunStatus takes the following values:
- 1 - g2p Production
- 2 - gep Production
- 3 - Dilution
- 4 - g2p Optics
- 5 - gep Optics
- 6 - Packing Fraction
- 7 - Cosmic
- 8 - BCM Calibration
- 9 - BPM Calibration
- 10 - Pedestal
- 11 - DAQ Test
- 12 - Large Charge Asymmetry
- 13 - Other
These were determined from a combination of the Student Run Sheets, Shift Worker Run Sheets and HALOG.
RunQuality
Misc. MYSQL Commands and Hints
Below are some specific MYSQL commands so I don't have to look them up every time I want to use them.
Checking Database size
SELECT SUM(ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024),2)) "Size in MB" FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = "g2p";
+------------+
| Size in MB |
+------------+
| 3.34 |
+------------+
1 row in set (0.11 sec)
Dumping and Restoring the DB
backup: # mysqldump -h adaql10 -u g2p -p[password] [database_name] > dumpfilename.sql restore:# mysql -h adaq10 -u g2p -p[password] [database_name] < dumpfilename.sql
