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()