# #denotes a comment line
#
# be sure to have the ".my.cnf" option file in your home directory
# for security purposes make sure that the permissions on ".my.cnf" are set correctly -> to be sure execute "chmod 600 .my.cnf"
# be sure that the 'host' variable is set to the correct IP, when in doubt ask jaideep
# get the current password from jaideep
#
# be sure to run mysql from ifarml1.jlab.org only! mysql server will *not* accept connections from other IPs!
#
# usage: mysql < sample_query.sql        (for tab-separated data with column headings)
# 	 mysql -N < sample_query.sql     (for tab-separated data without column headings)
#
# variables (see below for the appropriate commeand to get the most up-to-date list of variables)
#
#	run		run number			integer from 999 to 4218
#	flag		run flag			-1 = doesn't exist , 0 = junk , 1 = secondary run , 2 = primary run with problems , 3 = primary run
#	num_file	number of raw data files	integer from 0 to 8
#	runtype		run type			"acc" , "bcm" , "bs" , "bull" , "cos" , "det" , "ela" , "harp" , "opt" , "oth" , "pc" , "ped" , "pro"
#	start_time	run start time			in unix time, seconds from Jan 1970
#	end_time	run end time			in unix time, seconds from Jan 1970
#	period		data period			1 , 2
#	nom_angle	nominal angle in degrees	6, 9, NULL
#	cell		cell name			"proteus" , "penelope" , "priapus"
#	nom_ebeam	nominal beam energy in GeV	1.1 , 1.5 , 2.1 , 2.2 , 2.8 , 3.3 , 3.8 , 4.2 , 4.4 , NULL
#	ebeam		average run beam energy in MeV	from 1095.94 to 4406.63 and NULL
#	ebeam_sigma	std dev run beam energy in MeV	numeric includes NULL
#	nom_pmom	nominal p0 (right arm) in MeV	upto 3161.2 includes  NULL
#	pmom		average run p0 in MeV		upto 3161.202 includes NULL
#	pmom_sigma	std dev run p0 in MeV		numeric includes NULL
#	septum		average run septum read in Amps upto 420.25 includes NULL 
#	septum_sigma	std dev run septum read in Amps numeric includes NULL
#	septum_set	septum set point in Amps	numeric includes NULL
#	nu		average energy loss in GeV	numeric includes NULL
#	q_2		average q^2 in GeV^2		numeric includes NULL
#	W_mass		average W in GeV		numeric includes NULL
#	a_beam_curr	average hall a current in uA	numeric includes NULL
#	a_beam_curr_sig	std dev hall a current in uA	numeric includes NULL
#	c_beam_curr	average hall c current in uA	numeric includes NULL
#	c_beam_curr_sig	std dev hall c current in uA	numeric includes NULL
#	polbeam		beam polarization		between 0.0 and 1.0 includes NULL
#	bhwp		beam half waveplate		"IN" , "OUT" , NULL
#	target1		general target in beam		"carbon" , "none" , "pol3he" , "refcell"
#	target2		specific target in beam		carbon = "all" , "some" ; none = NULL ; pol3he = "0" , "180" , "270" ; refcell = "3he" , "empty" , "n2"
#	taraux1		first refcell pressure in psig	from -18 to 147 includes NULL
#	taraux2		second pressure in psig		from -18 to 147 includes NULL
#	tarang		HH coils field orientation	"0" , "180" , "270" , NULL
#	raster		raster status			0 for off , 1 for on , NULL
#	ps1		trigger 1 prescale		integer includes NULL
#	ps2		trigger 2 prescale		integer includes NULL
#	ps3		trigger 3 prescale		integer includes NULL
#	ps4		trigger 4 prescale		integer includes NULL
#	ps5		trigger 5 prescale		integer includes NULL
#	ps6		trigger 6 prescale		integer includes NULL
#	ps7		trigger 7 prescale		integer includes NULL
#	ps8		trigger 8 prescale		integer includes NULL
#	comments	run comments from tcl script	string , not always reliable
#
# note that undefined values are NULL and not "NULL" , boolean expressions with NULL are undefined (neither true nor false) and therefore also NULL
#
#
# show all data tables in a database:
# SHOW TABLES;
#
# show all columns (variables) from the "summary" table:
# SHOW COLUMNS FROM summary;
# 
#
# general structure of a query: SELECT [column names separated by commas] FROM [table name, usually summary] WHERE [boolean expressions];
#
# examples:
#
# find prescale factors for T1 and T2 events for all non-junk runs:
# SELECT run , ps1 , ps2 FROM summary WHERE flag > 1;
#
# find all good pressure curves runs for the second period labeled by gas and pressure:
# SELECT run , target2 , taraux1 , taraux2 FROM summary WHERE (period = 2) AND (runtype = "pc") AND (flag > 1);
#
# find all pedestal runs:
# SELECT run FROM summary WHERE (runtype = "ped");
#
# find all run numbers that don't exist:
# SELECT run FROM summary WHERE (flag = -1);
#
# find all production (pol3he) runs for penelope in descending spectormeter p0 order with septum read current and target angle
# SELECT run , pmom , septum , tarang WHERE (cell = penelope) AND (flag > 1) AND (target1 = pol3he) ORDER BY pmom DESC;
#
# find counts for all target types for non-junk runs
# SELECT target1 , target2 , COUNT(*) FROM summary WHERE (flag > 1) GROUP BY target1 , target2;
#
# find all the runs for which the bhwp is not known right now along with start and end run timestamps in human readable format
# SELECT run , FROM_UNIXTIME(start_time) AS start_timestamp , FROM_UNIXTIME(end_time) AS end_timestamp FROM summary WHERE bhwp IS NULL;
#
# find all non-junk primary runs for second period with highest split file number and beam polarizations:
SELECT run , num_file-1 AS max_split_number , polbeam FROM summary WHERE (flag > 1) AND (period = 2);
