def make_rec_sngls_table( connection, recovery_table ):
"""
Makes a temporary table containing events from the given recovery table
that could potentially be injections --- i.e., events from the "simulation"
datatype --- and the process id of the injection jobs that created them.
This allows for quick matching between injections and single events later
on.
"""
sqlquery = ''.join(['''
CREATE TEMP TABLE rec_sngls AS
SELECT
experiment_summary.sim_proc_id AS sim_proc_id,
''', recovery_table, '''.*
FROM
''', recovery_table, '''
''', sqlutils.join_experiment_tables_to_sngl_table( recovery_table ), '''
WHERE
experiment_summary.datatype == "simulation"''' ])
connection.cursor().execute(sqlquery)
def dbinjfind(connection,
simulation_table,
recovery_table,
match_criteria,
rough_match=None,
rejection_criteria=[],
rough_rejection=None,
verbose=False):
# validate simulation_table and recovery_table
simulation_table = sqlutils.validate_option(simulation_table)
recovery_table = sqlutils.validate_option(recovery_table)
# create DataRow classes to store data for each table
simColumns = sqlutils.get_column_names_from_table(connection,
simulation_table)
recColumns = sqlutils.get_column_names_from_table(connection,
recovery_table)
SimDataRow = dataUtils.createDataRowClass(simulation_table,
columns=simColumns)
RecDataRow = dataUtils.createDataRowClass(recovery_table,
columns=recColumns)
# create a temporary table to store the eligible foreground events that can be matched
if verbose:
print >> sys.stdout, "Getting eligible events..."
make_rec_sngls_table(connection, recovery_table)
# if using rough match, create an index for it
rough_match_test = ''
if rough_match is not None:
simRough, recRough, winRough = rough_match
simRough = sqlutils.validate_option(simRough)
recRough = sqlutils.validate_option(recRough)
sqlquery = "CREATE INDEX rs_rmtch_idx ON rec_sngls (%s)" % recRough
connection.cursor().execute(sqlquery)
rough_match_test = "rec_sngls.%s >= sim.%s - %f AND rec_sngls.%s <= sim.%s + %f AND\n" % (
recRough, simRough, winRough, recRough, simRough, winRough)
#
# Remove triggers that match all_data triggers
#
if rejection_criteria != []:
if verbose:
print >> sys.stdout, "Applying rejection test to eligible events..."
# If comparing to all_data for rejection, create a temp table of all data events
# This rejection test only uses the single-ifo triggers from coincident events
sqlquery = ''.join([
'''
CREATE TEMP TABLE all_data_sngls AS
SELECT
''', recovery_table, '''.*
FROM
''', recovery_table, '''
''',
sqlutils.join_experiment_tables_to_sngl_table(recovery_table), '''
WHERE
experiment_summary.datatype == "all_data"'''
])
connection.cursor().execute(sqlquery)
rough_test = ''
if rough_rejection is not None:
rejRough, rejRoughWin = rough_rejection
rejRough = sqlutils.validate_option(rejRough)
sqlquery = "CREATE INDEX ads_rmtch_idx ON all_data_sngls (%s)" % rejRough
connection.cursor().execute(sqlquery)
rough_test = "all_data_sngls.%s >= rec_sngls.%s - %f AND all_data_sngls.%s <= rec_sngls.%s + %f AND\n" % (
rejRough, rejRough, rejRoughWin, rejRough, rejRough,
rejRoughWin)
# cycle over the rejection criteria, creating a function in the database for each
rejection_tests = []
for n, (thisFunc, window) in enumerate(rejection_criteria):
compF = dataUtils.CompareDataRows(RecDataRow, RecDataRow)
funcName = 'matches_all_data%i' % n
if thisFunc == 'eThinca':
compF.create_dbCompF(connection, compF.eThincaSngl, funcName,
window, recColumns, recColumns)
else:
compF.set_matchCriteriaA(thisFunc)
compF.set_matchCriteriaB(thisFunc)
compF.create_dbCompF(connection, compF.diffRowARowB, funcName,
window, recColumns, recColumns)
simSnglCols = ','.join(
['rec_sngls.%s' % (col) for col in compF.neededColumnsA])
allSnglCols = ','.join(
['all_data_sngls.%s' % (col) for col in compF.neededColumnsB])
rejection_tests.append('%s(%s, %s)' %
(funcName, simSnglCols, allSnglCols))
# now remove triggers
sqlquery = ''.join([
'''
DELETE FROM
rec_sngls
WHERE EXISTS (
SELECT
*
FROM
all_data_sngls
WHERE
''', rough_test, '\nAND '.join(rejection_tests), ')'
])
connection.cursor().execute(sqlquery)
connection.commit()
#
# Determine Sim-Sngl matches
#
if verbose:
print >> sys.stdout, "Applying match criteria to find sim-sngl maps..."
# cycle over the match criteria, creating a function in the database for each
match_tests = []
for n, (simFunc, snglFunc, window) in enumerate(match_criteria):
compF = dataUtils.CompareDataRows(SimDataRow, RecDataRow)
# set the name of the compare function to use in the database
funcName = 'are_match%i' % n
# set the functions that get the match criteria from the tables
# and create the compare function in the database
if simFunc == 'eThinca':
compF.create_dbCompF(connection, compF.eThincaSim, funcName,
window, simColumns, recColumns)
else:
compF.set_matchCriteriaA(simFunc)
compF.set_matchCriteriaB(snglFunc)
compF.create_dbCompF(connection, compF.diffSimSngl, funcName,
window, simColumns, recColumns)
simCols = ','.join(['sim.%s' % (col) for col in compF.neededColumnsA])
snglCols = ','.join(
['rec_sngls.%s' % (col) for col in compF.neededColumnsB])
match_tests.append('%s(%s, %s)' % (funcName, simCols, snglCols))
# determine matches
sqlquery = ''.join([
"""
CREATE TEMP TABLE found_inj AS
SELECT
sim.simulation_id AS sim_id,
rec_sngls.event_id AS event_id
FROM
""", simulation_table, """ AS sim, rec_sngls
WHERE
sim.process_id == rec_sngls.sim_proc_id AND
""", rough_match_test, '\n\t\tAND'.join(match_tests)
])
connection.cursor().execute(sqlquery)
connection.commit()
def dbinjfind( connection, simulation_table, recovery_table, match_criteria, rough_match = None, rejection_criteria = [], rough_rejection = None, verbose = False ):
# validate simulation_table and recovery_table
simulation_table = sqlutils.validate_option( simulation_table )
recovery_table = sqlutils.validate_option( recovery_table )
# create DataRow classes to store data for each table
simColumns = sqlutils.get_column_names_from_table( connection, simulation_table )
recColumns = sqlutils.get_column_names_from_table( connection, recovery_table )
SimDataRow = dataUtils.createDataRowClass( simulation_table, columns = simColumns )
RecDataRow = dataUtils.createDataRowClass( recovery_table, columns = recColumns )
# create a temporary table to store the eligible foreground events that can be matched
if verbose:
print >> sys.stdout, "Getting eligible events..."
make_rec_sngls_table( connection, recovery_table )
# if using rough match, create an index for it
rough_match_test = ''
if rough_match is not None:
simRough, recRough, winRough = rough_match
simRough = sqlutils.validate_option( simRough )
recRough = sqlutils.validate_option( recRough )
sqlquery = "CREATE INDEX rs_rmtch_idx ON rec_sngls (%s)" % recRough
connection.cursor().execute( sqlquery )
rough_match_test = "rec_sngls.%s >= sim.%s - %f AND rec_sngls.%s <= sim.%s + %f AND\n" %( recRough, simRough, winRough, recRough, simRough, winRough )
#
# Remove triggers that match all_data triggers
#
if rejection_criteria != []:
if verbose:
print >> sys.stdout, "Applying rejection test to eligible events..."
# If comparing to all_data for rejection, create a temp table of all data events
# This rejection test only uses the single-ifo triggers from coincident events
sqlquery = ''.join(['''
CREATE TEMP TABLE all_data_sngls AS
SELECT
''', recovery_table, '''.*
FROM
''', recovery_table, '''
''', sqlutils.join_experiment_tables_to_sngl_table( recovery_table ), '''
WHERE
experiment_summary.datatype == "all_data"''' ])
connection.cursor().execute(sqlquery)
rough_test = ''
if rough_rejection is not None:
rejRough, rejRoughWin = rough_rejection
rejRough = sqlutils.validate_option( rejRough )
sqlquery = "CREATE INDEX ads_rmtch_idx ON all_data_sngls (%s)" % rejRough
connection.cursor().execute( sqlquery )
rough_test = "all_data_sngls.%s >= rec_sngls.%s - %f AND all_data_sngls.%s <= rec_sngls.%s + %f AND\n" % ( rejRough, rejRough, rejRoughWin, rejRough, rejRough, rejRoughWin )
# cycle over the rejection criteria, creating a function in the database for each
rejection_tests = []
for n,(thisFunc, window) in enumerate(rejection_criteria):
compF = dataUtils.CompareDataRows(RecDataRow, RecDataRow)
funcName = 'matches_all_data%i' % n
# Note: setting the match criteria also sets the needed columns
compF.set_matchCriteriaA(thisFunc)
compF.set_matchCriteriaB(thisFunc)
# need different diff function if using eThinca
if thisFunc == 'eThinca':
diffFunc = compF.eThincaSngl
else:
diffFunc = compF.diffRowARowB
compF.create_dbCompF(connection, diffFunc, funcName, window)
simSnglCols = ','.join(['rec_sngls.%s' %(col) for col in compF.neededColumnsA])
allSnglCols = ','.join(['all_data_sngls.%s' %(col) for col in compF.neededColumnsB])
rejection_tests.append( '%s(%s, %s)' %(funcName, simSnglCols, allSnglCols) )
# now remove triggers
sqlquery = ''.join([ '''
DELETE FROM
rec_sngls
WHERE EXISTS (
SELECT
*
FROM
all_data_sngls
WHERE
''', rough_test, '\nAND '.join( rejection_tests ), ')' ])
connection.cursor().execute(sqlquery)
connection.commit()
#
# Determine Sim-Sngl matches
#
if verbose:
print >> sys.stdout, "Applying match criteria to find sim-sngl maps..."
# cycle over the match criteria, creating a function in the database for each
match_tests = []
for n,(simFunc, snglFunc, window) in enumerate(match_criteria):
compF = dataUtils.CompareDataRows(SimDataRow, RecDataRow)
# set the name of the compare function to use in the database
funcName = 'are_match%i' % n
compF.set_matchCriteriaA(simFunc)
compF.set_matchCriteriaB(snglFunc)
# need different diff function if using eThinca
if simFunc == 'eThinca':
diffFunc = compF.eThincaSim
else:
diffFunc = compF.diffSimSngl
compF.create_dbCompF(connection, diffFunc, funcName, window)
simCols = ','.join(['sim.%s'%(col) for col in compF.neededColumnsA])
snglCols = ','.join(['rec_sngls.%s'%(col) for col in compF.neededColumnsB])
match_tests.append( '%s(%s, %s)' %(funcName, simCols, snglCols) )
# determine matches
sqlquery = ''.join(["""
CREATE TEMP TABLE found_inj AS
SELECT
sim.simulation_id AS sim_id,
rec_sngls.event_id AS event_id
FROM
""", simulation_table, """ AS sim, rec_sngls
WHERE
sim.process_id == rec_sngls.sim_proc_id AND
""", rough_match_test, '\n\t\tAND'.join( match_tests ) ])
connection.cursor().execute(sqlquery)
connection.commit()
