Difference between revisions of "G2pmysql"
From Hall A Wiki
(→Modifications made to RunInfo Tables after Run Period) |
(→C++/Root (and now Python) Mysql Library) |
||
| (74 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
| − | + | 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 | -Ryan | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
List of good mysql tutorial websites. | List of good mysql tutorial websites. | ||
| Line 46: | Line 8: | ||
http://www.dreamincode.net/forums/topic/42301-beginners-guide-to-creating-simple-mysql-tables/ | 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 | ||
| + | == Web Interface == | ||
| + | The g2p database can be accessed through the web at the following [https://hallaweb.jlab.org/experiment/g2p/mysql/ link]. | ||
| − | + | The web interface allows you to search the g2p mysql database through your web browser and also output your search results to a CSV file. The search form parses the AnaInfo tables and displays selected fields to the browser. The download CSV file contains ALL the fields. Also selecting an individual run from the search results will display information on all the fields of that run in a new tab/window from both the AnaInfo and RunInfo tables. | |
| − | There | + | <b>1/30/13</b> |
| + | There is now an interace to update the database through the web. I won't be making this information public for obvious reasons (even though the page is password protected!), so if you need access to it, email Ryan @ rbziel@jlab.org and we can work something out. Currently, this interface is limited to updating the RunQuality and ExpertComment flags for runs on both HRS. | ||
| − | == | + | == C++/Root (and now Python) Mysql Library == |
| − | + | There is a C++/root library for the g2p mysql database. The library interfaces root with the mysql database, and by including the library/header file in root scripts the user gains the ability to access run information. This will be particularly useful for normalization variables (e.g. deadtime, prescale, etc.) in cross-section analysis. These variables only need be calculated once for each run because once they're in the database they don't need to be calculated again - only called. A description of the library function calls can be found [https://hallaweb.jlab.org/wiki/index.php/G2p_mysql_lib here]. | |
| + | |||
| + | As of 5/27/14 there is also a python library. Located at /w/halla-sfs62/g2p/ryan/mysql_root/Python/g2p_mysql_lib.py. The function calls are the same as the C++ library. | ||
| + | |||
| + | == Useful Files & Notes== | ||
| + | |||
| + | g2p mysql [https://userweb.jlab.org/~rbziel/mysql_BKUP/ DB backups] | ||
| + | *Backup_List gives some information on the backups | ||
| + | |||
| + | Script used to initially [https://userweb.jlab.org/~rbziel/mysql_files/epics_db_example.pl fill] AnaInfo table from this [https://userweb.jlab.org/~rbziel/mysql_files/epics_csv.csv csv] file. | ||
| + | *Thanks to Chao for providing the EPICS information | ||
| + | |||
| + | Run start and stop times can be found [https://userweb.jlab.org/~rbziel/mysql_files/time.dat here] and the perl script used to edit the mysql db is [https://userweb.jlab.org/~rbziel/mysql_files/epics_time.pl this] | ||
| + | *Thanks to Chao for providing these times | ||
| + | *The times are in UNIX and I can't off the top of my head remember what the different columns are. | ||
| + | |||
| + | Trigger Efficiency and Deadtime calculations were made using the following scripts: On ifarm 'w/halla-sfs62/g2p/ryan/TEff/' has all the relevant files | ||
| + | |||
| + | Epics variables and descriptions can be found here https://hallaweb.jlab.org/wiki/index.php/G2p_epics | ||
| + | |||
| + | 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 | ||
| + | |||
| + | == Connecting to the Database== | ||
| + | |||
| + | The database is hosted on the Jlab mysql server, jmysql, which is accessible from ifarm. Below I'll describe how to connect to the database and give a brief how-to on how to navigate the database. I recommend using google or one of the above mysql tutorials to learn how to do things that I haven't discussed below. For now I'm only giving access to the read-only account. This could change in the future if I can limit what fields the read/write account can edit. | ||
| + | |||
| + | <b> Connecting to the DB </b> | ||
| + | Type the following command to connect from ifarm. You can either type the password immediately after "-p" with no space or leave it blank and then it will prompt you to enter the password. | ||
| + | ifarm1102> mysql -h jmysql -u g2pro -p[password] | ||
| + | |||
| + | After successfully connecting you will be greeted with a mysql prompt. From here you can show and select the available databases. We are using the g2p database. | ||
| + | mysql> show Databases; | ||
| + | +--------------------+ | ||
| + | | Database | | ||
| + | +--------------------+ | ||
| + | | information_schema | | ||
| + | | g2p | | ||
| + | | test | | ||
| + | +--------------------+ | ||
| + | 3 rows in set (0.00 sec) | ||
| + | |||
| + | mysql> use g2p; | ||
| + | |||
| + | <b> Navigating the DB </b> | ||
| + | Currently there are 5 tables in the database. The tables are described in another section, below. Also, you can see the contents of each table using the describe command. | ||
mysql> show Tables; | mysql> show Tables; | ||
+---------------+ | +---------------+ | ||
| Tables_in_g2p | | | Tables_in_g2p | | ||
+---------------+ | +---------------+ | ||
| + | | AnaInfoL | | ||
| + | | AnaInfoR | | ||
| RunInfoL | | | RunInfoL | | ||
| RunInfoR | | | RunInfoR | | ||
| RunInfoT | | | RunInfoT | | ||
+---------------+ | +---------------+ | ||
| − | + | 5 rows in set (0.00 sec) | |
| − | + | To sort and select data from the tables use the select command, which has the following syntax: | |
| + | mysql> select Field,Field,Field...etc from Table where Field >/</= value OR/AND Field >/</= value ...etc; | ||
| − | mysql> | + | For example to display SeptaStatus, Q1p, RunStartTime and RunStopTime information from the AnaInfoL table for run 3400 type the following: |
| − | +-------------------- | + | mysql> select SeptaStatus,Q1p,RunStartTime,RunStopTime from AnaInfoL where RunNumber = 3400; |
| − | | | + | +-------------+---------+---------------------+---------------------+ |
| − | +-------------------- | + | | SeptaStatus | Q1p | RunStartTime | RunStopTime | |
| − | | | + | +-------------+---------+---------------------+---------------------+ |
| − | + | | 1 | 2.25094 | 2012-03-28 15:05:49 | 2012-03-28 15:16:49 | | |
| − | + | +-------------+---------+---------------------+---------------------+ | |
| − | + | 1 row in set (0.00 sec) | |
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | +-------------------- | + | |
| − | + | ||
| + | <b> Exiting the database </b> | ||
| + | Exiting the database is easy! Just type exit when you're done. | ||
| − | + | == 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==== | |
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | [https://hallaweb.jlab.org/wiki/index.php/G2p_RunInfo RunInfo Tables] | |
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| + | ====AnaInfo Tables==== | ||
| + | [https://hallaweb.jlab.org/wiki/index.php/G2p_AnaInfo AnaInfo Tables] | ||
| − | == | + | == Feature Implementation== |
| − | + | Because this a wiki, everyone should be able to edit it. In that regard, if there are features you would like to see implemented in the DB please list them below. This also includes additional tables or additional fields in existing tables. Thanks! | |
| − | + | ||
| − | + | * [https://hallaweb.jlab.org/wiki/index.php/G2p_AnaInfo#Additions_to_the_AnaInfo_Tables AnaInfo Table Additions] | |
| − | + | * C++ library/analysis code interface | |
| − | + | ** Basic code exists now. It will be updated as needed | |
| + | * ? | ||
| + | |||
| + | == 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==== | ====Checking Database size==== | ||
Latest revision as of 14:15, 27 May 2014
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
Contents
Web Interface
The g2p database can be accessed through the web at the following link.
The web interface allows you to search the g2p mysql database through your web browser and also output your search results to a CSV file. The search form parses the AnaInfo tables and displays selected fields to the browser. The download CSV file contains ALL the fields. Also selecting an individual run from the search results will display information on all the fields of that run in a new tab/window from both the AnaInfo and RunInfo tables.
1/30/13 There is now an interace to update the database through the web. I won't be making this information public for obvious reasons (even though the page is password protected!), so if you need access to it, email Ryan @ rbziel@jlab.org and we can work something out. Currently, this interface is limited to updating the RunQuality and ExpertComment flags for runs on both HRS.
C++/Root (and now Python) Mysql Library
There is a C++/root library for the g2p mysql database. The library interfaces root with the mysql database, and by including the library/header file in root scripts the user gains the ability to access run information. This will be particularly useful for normalization variables (e.g. deadtime, prescale, etc.) in cross-section analysis. These variables only need be calculated once for each run because once they're in the database they don't need to be calculated again - only called. A description of the library function calls can be found here.
As of 5/27/14 there is also a python library. Located at /w/halla-sfs62/g2p/ryan/mysql_root/Python/g2p_mysql_lib.py. The function calls are the same as the C++ library.
Useful Files & Notes
g2p mysql DB backups
- Backup_List gives some information on the backups
Script used to initially fill AnaInfo table from this csv file.
- Thanks to Chao for providing the EPICS information
Run start and stop times can be found here and the perl script used to edit the mysql db is this
- Thanks to Chao for providing these times
- The times are in UNIX and I can't off the top of my head remember what the different columns are.
Trigger Efficiency and Deadtime calculations were made using the following scripts: On ifarm 'w/halla-sfs62/g2p/ryan/TEff/' has all the relevant files
Epics variables and descriptions can be found here https://hallaweb.jlab.org/wiki/index.php/G2p_epics
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
Connecting to the Database
The database is hosted on the Jlab mysql server, jmysql, which is accessible from ifarm. Below I'll describe how to connect to the database and give a brief how-to on how to navigate the database. I recommend using google or one of the above mysql tutorials to learn how to do things that I haven't discussed below. For now I'm only giving access to the read-only account. This could change in the future if I can limit what fields the read/write account can edit.
Connecting to the DB Type the following command to connect from ifarm. You can either type the password immediately after "-p" with no space or leave it blank and then it will prompt you to enter the password.
ifarm1102> mysql -h jmysql -u g2pro -p[password]
After successfully connecting you will be greeted with a mysql prompt. From here you can show and select the available databases. We are using the g2p database.
mysql> show Databases; +--------------------+ | Database | +--------------------+ | information_schema | | g2p | | test | +--------------------+ 3 rows in set (0.00 sec)
mysql> use g2p;
Navigating the DB Currently there are 5 tables in the database. The tables are described in another section, below. Also, you can see the contents of each table using the describe command.
mysql> show Tables; +---------------+ | Tables_in_g2p | +---------------+ | AnaInfoL | | AnaInfoR | | RunInfoL | | RunInfoR | | RunInfoT | +---------------+ 5 rows in set (0.00 sec)
To sort and select data from the tables use the select command, which has the following syntax:
mysql> select Field,Field,Field...etc from Table where Field >/</= value OR/AND Field >/</= value ...etc;
For example to display SeptaStatus, Q1p, RunStartTime and RunStopTime information from the AnaInfoL table for run 3400 type the following:
mysql> select SeptaStatus,Q1p,RunStartTime,RunStopTime from AnaInfoL where RunNumber = 3400; +-------------+---------+---------------------+---------------------+ | SeptaStatus | Q1p | RunStartTime | RunStopTime | +-------------+---------+---------------------+---------------------+ | 1 | 2.25094 | 2012-03-28 15:05:49 | 2012-03-28 15:16:49 | +-------------+---------+---------------------+---------------------+ 1 row in set (0.00 sec)
Exiting the database Exiting the database is easy! Just type exit when you're done.
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
Feature Implementation
Because this a wiki, everyone should be able to edit it. In that regard, if there are features you would like to see implemented in the DB please list them below. This also includes additional tables or additional fields in existing tables. Thanks!
- AnaInfo Table Additions
- C++ library/analysis code interface
- Basic code exists now. It will be updated as needed
- ?
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
