Difference between revisions of "G2pmysql"

From Hall A Wiki
Jump to: navigation, search
(→‎RunInfo Tables)
(→‎AnaInfo Tables)
Line 31: Line 31:
 
====AnaInfo Tables====
 
====AnaInfo Tables====
  
−
 
+
[https://hallaweb.jlab.org/wiki/index.php/G2p_AnaInfo AnaInfo]
−
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=====
+
−
+
−
The RunQuality field distinguishes runs based upon 5 categories. This categories are listed below:
+
−
* -1 - Production Run with wrong Septa or Q1
+
−
* 0  - Junk/Garbage Run
+
−
* 1  - Calibration Run
+
−
* 2  - Production/Dilution Run with some minor problems (eg. One BCM not working)
+
−
* 3  - Production/Dilution Run with no obvious problems
+
−
 
+
−
RunQuality was determined from a combination of Student Run Sheets (SRS), Shift Worker Run Sheets (SWRS) and the HaLog. For the determination of production run quality, it was assumed that any run on the SRS was either a 2 or 3. Runs during a production run period which were not on the SRS were then checked on the SWRS, if they appeared on this list and were flagged as a good run they were a 2 or 3. If a run during a production period was not on either the SRS or SWRS it was considered on a case by case basis.
+
  
 
====ExpertC====
 
====ExpertC====

Revision as of 15:21, 14 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

AnaInfo Tables

AnaInfo

ExpertC

The ExpertC or Expert Comment field is where the student experts can make comments. These comments serve to explain decisions made in regards to RunQuality and RunStatus made by the student experts. For example, they should give reason for a production run's RunQuality flag if it is something other than 3. The expert comment is also used to provide further information on the RunStatus, such as what kind of optics run or BPM run, for example.

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
Retrieved from "https://hallaweb.jlab.org/wiki/index.php?title=G2pmysql&oldid=16212"