def get_glitch_times(glitch_xmlfiles): """ Returns list of (gps,gps_ns) tuples for all events contained in the glitch_xmlfiles. """ # load files in database connection, cursor = idq_tables_dbutils.load_xml_files_into_database( glitch_xmlfiles) # get table names from the database tablenames = dbtables.get_table_names(connection) if not tablenames: print "No tables were found in the database." return [] # check if glitch table is present if not idq_tables.IDQGlitchTable.tableName in tablenames: print "No glitch table is found in database." print "Can not perform requested query." return [] data = cursor.execute("""SELECT gps, gps_ns FROM """ + \ idq_tables.IDQGlitchTable.tableName).fetchall() # close database connection.close() return data
def group_files(filenames, verbose = False): # figure out which files are injection runs and which aren't injection_filenames = [] noninjection_filenames = [] for n, filename in enumerate(filenames): if verbose: print >>sys.stderr, "%d/%d: %s" % (n + 1, len(filenames), filename) connection = sqlite3.connect(filename) if "sim_burst" in dbtables.get_table_names(connection): if verbose: print >>sys.stderr, "\t--> injections" injection_filenames.append(filename) else: if verbose: print >>sys.stderr, "\t--> non-injections" noninjection_filenames.append(filename) connection.close() return injection_filenames, noninjection_filenames
def get_get_glitch_ovl_sngburst_data(connection, cursor, glitch_columns, ovl_columns, snglburst_columns): """ Connects glitch, ovl and snglburst tables, and returns requested data, as a list of tuples with values for requested columns in the order of [(glitch_columns, ovl_columns, snglburst_columns), ...]. @param connection UNDOCUMENTED @param cursor UNDOCUMENTED @param glitch_columns is the list of requested glitch table column names, @param ovl_columns is the list of requested ovl table column names, @param snglburst_columns is the list of requested snglburst table column names. """ # get table names from the database tablenames = dbtables.get_table_names(connection) if not tablenames: print "No tables were found in the database." return [] # check if glitch table, ovl and snglburst tables are present if not ( (table.StripTableName(idq_tables.IDQGlitchTable.tableName) in tablenames)\ and (table.StripTableName(idq_tables.OVLDataTable.tableName) in tablenames)\ and (table.StripTableName(lsctables.SnglBurstTable.tableName) in tablenames) ): print "Not all of glitch, ovl and snglburst tables are found in database." print "Need all three to perform requested query." return [] # construct feature string with requested columns for SELECT statement glitch_features = ', '.join(['g.' + name for name in glitch_columns]) ovl_features = ', '.join(['o.' + name for name in ovl_columns]) snglburst_features = ', '.join(['b.' + name for name in snglburst_columns]) features_string = glitch_features + ', ' + ovl_features + ', ' + snglburst_features # do the query data = cursor.execute('''SELECT ''' + features_string + ''' FROM ''' + \ table.StripTableName(idq_tables.IDQGlitchTable.tableName) + \ ''' AS g JOIN coinc_event_map AS c1 ON g.event_id == c1.event_id''' + \ ''' JOIN coinc_event_map AS c2 ON c1.coinc_event_id == c2.coinc_event_id''' + \ ''' JOIN ''' + table.StripTableName(idq_tables.OVLDataTable.tableName) + \ ''' AS o ON o.event_id == c2.event_id''' + \ ''' JOIN coinc_event_map AS c3 ON g.event_id == c3.event_id''' + \ ''' JOIN coinc_event_map AS c4 ON c4.coinc_event_id == c3.coinc_event_id''' + \ ''' JOIN ''' + table.StripTableName(lsctables.SnglBurstTable.tableName) + \ ''' AS b ON b.event_id == c4.event_id''').fetchall() return data
def get_get_glitch_ovl_sngburst_data(connection, cursor, glitch_columns, ovl_columns, snglburst_columns): """ Connects glitch, ovl and snglburst tables, and returns requested data, as a list of tuples with values for requested columns in the order of [(glitch_columns, ovl_columns, snglburst_columns), ...]. @param connection UNDOCUMENTED @param cursor UNDOCUMENTED @param glitch_columns is the list of requested glitch table column names, @param ovl_columns is the list of requested ovl table column names, @param snglburst_columns is the list of requested snglburst table column names. """ # get table names from the database tablenames = dbtables.get_table_names(connection) if not tablenames: print "No tables were found in the database." return [] # check if glitch table, ovl and snglburst tables are present if not ( (idq_tables.IDQGlitchTable.tableName in tablenames)\ and (idq_tables.OVLDataTable.tableName in tablenames)\ and (lsctables.SnglBurstTable.tableName in tablenames) ): print "Not all of glitch, ovl and snglburst tables are found in database." print "Need all three to perform requested query." return [] # construct feature string with requested columns for SELECT statement glitch_features = ', '.join(['g.' + name for name in glitch_columns]) ovl_features = ', '.join(['o.' + name for name in ovl_columns]) snglburst_features = ', '.join(['b.' + name for name in snglburst_columns]) features_string = glitch_features + ', ' + ovl_features + ', ' + snglburst_features # do the query data = cursor.execute('''SELECT ''' + features_string + ''' FROM ''' + \ idq_tables.IDQGlitchTable.tableName + \ ''' AS g JOIN coinc_event_map AS c1 ON g.event_id == c1.event_id''' + \ ''' JOIN coinc_event_map AS c2 ON c1.coinc_event_id == c2.coinc_event_id''' + \ ''' JOIN ''' + idq_tables.OVLDataTable.tableName + \ ''' AS o ON o.event_id == c2.event_id''' + \ ''' JOIN coinc_event_map AS c3 ON g.event_id == c3.event_id''' + \ ''' JOIN coinc_event_map AS c4 ON c4.coinc_event_id == c3.coinc_event_id''' + \ ''' JOIN ''' + lsctables.SnglBurstTable.tableName + \ ''' AS b ON b.event_id == c4.event_id''').fetchall() return data
def get_glitch_times(glitch_xmlfiles): """ Returns list of (gps,gps_ns) tuples for all events contained in the glitch_xmlfiles. """ # load files in database connection, cursor = idq_tables_dbutils.load_xml_files_into_database(glitch_xmlfiles) # get table names from the database tablenames = dbtables.get_table_names(connection) if not tablenames: print "No tables were found in the database." return [] # check if glitch table is present if not table.StripTableName(idq_tables.IDQGlitchTable.tableName) in tablenames: print "No glitch table is found in database." print "Can not perform requested query." return [] data = cursor.execute('''SELECT gps, gps_ns FROM ''' + \ table.StripTableName(idq_tables.IDQGlitchTable.tableName)).fetchall() # close database connection.close() return data
# for n, filename in enumerate( ligolw_utils.sort_files_by_size(filenames, options.verbose, reverse=True)): if options.verbose: print >> sys.stderr, "%d/%d: %s" % (n + 1, len(filenames), filename) working_filename = dbtables.get_connection_filename( filename, tmp_path=options.tmp_space, verbose=options.verbose) database = SnglBurstUtils.CoincDatabase(sqlite3.connect(working_filename), options.live_time_program, search="StringCusp") if options.verbose: SnglBurstUtils.summarize_coinc_database(database) is_injection_db = "sim_burst" in dbtables.get_table_names( database.connection) if is_injection_db: database.connection.cursor().execute( """ CREATE TEMPORARY TABLE sim_burst_map AS SELECT a.event_id AS simulation_id, a.coinc_event_id AS coinc_event_id, b.event_id AS event_id FROM coinc_event_map AS a JOIN coinc_event ON ( coinc_event.coinc_event_id == a.coinc_event_id )
# open the database # if options.verbose: print >>sys.stderr, "%d/%d: %s" % (n + 1, len(filenames), filename) working_filename = dbtables.get_connection_filename(filename, tmp_path = options.tmp_space, verbose = options.verbose) connection = sqlite3.connect(working_filename) # # if the database contains a sim_inspiral table then it is assumed # to represent an injection run. its rings must not added to the # livetime, and it cannot provide background coincs, so it is just # skipped altogether in this first pass. # if "sim_ringdown" in dbtables.get_table_names(connection): if options.verbose: print >>sys.stderr, "\tdatabase contains sim_ringdown table, skipping ..." # # close the database # connection.close() dbtables.discard_connection_filename(filename, working_filename, verbose = options.verbose) continue # # compute and record background livetime #
def remove_redundant_entries(connection, cursor, verbose = False): """ Clean up idq tables by removing duplicate or redundant rows. """ # get table names from the database tablenames = dbtables.get_table_names(connection) if not tablenames: print "No tables were found in the database. Nothing to update." return # clean up process and process params tables if (table.Table.TableName(lsctables.ProcessTable.tableName) in tablenames) \ and (table.Table.TableName(lsctables.ProcessParamsTable.tableName) in tablenames): if verbose: print "Removing redundant entries from process and process_params tables ..." # get the lowest process_id from process table min_process_id = cursor.execute('''SELECT MIN(process_id) FROM process''').fetchone()[0] # delete redundant rows from process and process_params table that corresponds to different process ids cursor.execute('''DELETE FROM ''' + table.Table.TableName(lsctables.ProcessParamsTable.tableName) + ''' WHERE process_id != ?''',(min_process_id,)) cursor.execute('''DELETE FROM ''' + table.Table.TableName(lsctables.ProcessTable.tableName) + ''' WHERE process_id != ?''',(min_process_id,)) # get all tables that use process_id prc_id_tables = cursor.execute("SELECT name FROM sqlite_master WHERE type == 'table' AND sql LIKE '%process_id%'").fetchall() if verbose: print "Reassigning process_ids in all tables in the database ..." # loop over these tables and set all process_ids to min_process_id for (table_name,) in prc_id_tables: cursor.execute('''UPDATE ''' + table_name + ''' SET process_id = ?''', (min_process_id,)) connection.commit() # clean up coinc_definer and coinc event tables if ( table.Table.TableName(lsctables.CoincDefTable.tableName) in tablenames ) and \ (table.Table.TableName(lsctables.CoincTable.tableName) in tablenames ): if verbose: print "Removing redundant entries from coinc_definer and coinc_event tables ..." # get lowest (coinc_def_ids, search_coinc_type, description ) from coinc_def table # corresponding to unique search_coinc_type and description min_coinc_def_ids = cursor.execute(''' SELECT MIN(coinc_def_id), search_coinc_type, description FROM coinc_definer GROUP BY search_coinc_type, description ''').fetchall() if verbose: print "Updating coinc_def_id(s) in coinc_event table ..." # loop over them and update coinc_event table replacing redundant coinc_def ids for (min_coinc_def_id, min_search_coinc_type, min_description) in min_coinc_def_ids: cursor.execute('''UPDATE ''' + table.Table.TableName(lsctables.CoincTable.tableName) + ''' SET coinc_def_id = ? WHERE coinc_def_id IN ( SELECT coinc_def_id FROM ''' + table.Table.TableName(lsctables.CoincDefTable.tableName) + ''' WHERE ( search_coinc_type = ? ) AND ( description = ? ) )''', (min_coinc_def_id, min_search_coinc_type, min_description, )) if verbose: print "Removing unused entries from coinc_definer table ..." # remove redundant and already unused records from coinc_def table cursor.execute('''DELETE FROM ''' + table.Table.TableName(lsctables.CoincDefTable.tableName) + ''' WHERE coinc_def_id NOT IN ( SELECT MIN(coinc_def_id) FROM ''' + table.Table.TableName(lsctables.CoincDefTable.tableName) + ''' GROUP BY search_coinc_type, description ) ''') connection.commit() if verbose: print "Finished updating tables"
def delete_glitch_events_in_segment(connection, cursor, segment): """ Removes glitch events whose gps times fall within segment. All rows from the other tables in database connected to these glitch events via coinc_map tables are also deteled. @param connection UNDOCUMENTED @param cursor UNDOCUMENTED @param segment is glue segment """ # get table names from the database tablenames = dbtables.get_table_names(connection) if not tablenames: print "No tables were found in the database. Nothing to do." return # check if glitch table is present if not table.Table.TableName(idq_tables.IDQGlitchTable.tableName) in tablenames: print "No glitch table is found in database. Nothing to do" return # create sqlite function for testing if glitch gos time is inside the segment("md5", 1, md5sum) connection.create_function('gpstime_inside_segment', 3, gpstime_inside_segment) # convert segment inot segment string segment_string = segmentsUtils.to_range_strings([segment])[0] # check if coinc_def table is present if table.Table.TableName(lsctables.CoincDefTable.tableName) in tablenames: # get all distinct coinc types from coinc_def table coinc_descriptions = cursor.execute('''SELECT DISTINCT description FROM ''' + table.Table.TableName(lsctables.CoincDefTable.tableName)).fetchall() # loop over coinc descriptions for (description,) in coinc_descriptions: tables_in_coinc = idq_tables.CoincDefToTableNames[description] # work only with coincs that involve glitch table if idq_tables.IDQGlitchTable.tableName in tables_in_coinc: # remove glitch table, we will work with it later connected_tables = [t for t in tables_in_coinc if t != idq_tables.IDQGlitchTable.tableName] # loop over other tables in the coinc for t in connected_tables: # delete entries connected to glitch events that should be removed cursor.execute('''DELETE FROM ''' + table.Table.TableName(t) + \ ''' WHERE event_id IN (SELECT t.event_id FROM ''' + table.Table.TableName(t) + \ ''' AS t JOIN coinc_event_map AS c1 ON t.event_id == c1.event_id''' + \ ''' JOIN coinc_event_map AS c2 ON c1.coinc_event_id == c2.coinc_event_id''' + \ ''' JOIN ''' + table.Table.TableName(idq_tables.IDQGlitchTable.tableName) + \ ''' AS g ON c2.event_id == g.event_id WHERE gpstime_inside_segment(g.gps, g.gps_ns, ?))''',\ (segment_string,)) # delete entries from coinc_event table corresponding to deleted events cursor.execute('''DELETE FROM coinc_event WHERE coinc_event_id IN''' + \ ''' (SELECT c1.coinc_event_id FROM coinc_event_map AS c1 JOIN ''' + \ table.Table.TableName(idq_tables.IDQGlitchTable.tableName) + \ ''' AS g ON c1.event_id == g.event_id WHERE gpstime_inside_segment(g.gps, g.gps_ns, ?))''',\ (segment_string,)) # delete entries from coinc_map table corresponding to deleted events cursor.execute('''DELETE FROM coinc_event_map WHERE event_id IN''' + \ ''' (SELECT c1.event_id FROM coinc_event_map AS c1''' + \ ''' JOIN coinc_event_map AS c2 ON c1.coinc_event_id == c2.coinc_event_id ''' + \ ''' JOIN ''' + table.Table.TableName(idq_tables.IDQGlitchTable.tableName) +\ ''' AS g ON c2.event_id == g.event_id WHERE gpstime_inside_segment(g.gps, g.gps_ns, ?))''',\ (segment_string,)) # delete entries in glitch table cursor.execute('''DELETE FROM ''' + table.Table.TableName(idq_tables.IDQGlitchTable.tableName) + ''' WHERE gpstime_inside_segment(gps, gps_ns, ?)''', (segment_string,)) # commit everythings connection.commit()
def remove_redundant_entries(connection, cursor, verbose = False): """ Clean up idq tables by removing duplicate or redundant rows. """ # get table names from the database tablenames = dbtables.get_table_names(connection) if not tablenames: print "No tables were found in the database. Nothing to update." return # clean up process and process params tables if (lsctables.ProcessTable.tableName in tablenames) \ and (lsctables.ProcessParamsTable.tableName in tablenames): if verbose: print "Removing redundant entries from process and process_params tables ..." # get the lowest process_id from process table min_process_id = cursor.execute('''SELECT MIN(process_id) FROM process''').fetchone()[0] # delete redundant rows from process and process_params table that corresponds to different process ids cursor.execute('''DELETE FROM ''' + lsctables.ProcessParamsTable.tableName + ''' WHERE process_id != ?''',(min_process_id,)) cursor.execute('''DELETE FROM ''' + lsctables.ProcessTable.tableName + ''' WHERE process_id != ?''',(min_process_id,)) # get all tables that use process_id prc_id_tables = cursor.execute("SELECT name FROM sqlite_master WHERE type == 'table' AND sql LIKE '%process_id%'").fetchall() if verbose: print "Reassigning process_ids in all tables in the database ..." # loop over these tables and set all process_ids to min_process_id for (table_name,) in prc_id_tables: cursor.execute('''UPDATE ''' + table_name + ''' SET process_id = ?''', (min_process_id,)) connection.commit() # clean up coinc_definer and coinc event tables if ( lsctables.CoincDefTable.tableName in tablenames ) and \ (lsctables.CoincTable.tableName in tablenames ): if verbose: print "Removing redundant entries from coinc_definer and coinc_event tables ..." # get lowest (coinc_def_ids, search_coinc_type, description ) from coinc_def table # corresponding to unique search_coinc_type and description min_coinc_def_ids = cursor.execute(''' SELECT MIN(coinc_def_id), search_coinc_type, description FROM coinc_definer GROUP BY search_coinc_type, description ''').fetchall() if verbose: print "Updating coinc_def_id(s) in coinc_event table ..." # loop over them and update coinc_event table replacing redundant coinc_def ids for (min_coinc_def_id, min_search_coinc_type, min_description) in min_coinc_def_ids: cursor.execute('''UPDATE ''' + lsctables.CoincTable.tableName + ''' SET coinc_def_id = ? WHERE coinc_def_id IN ( SELECT coinc_def_id FROM ''' + lsctables.CoincDefTable.tableName + ''' WHERE ( search_coinc_type = ? ) AND ( description = ? ) )''', (min_coinc_def_id, min_search_coinc_type, min_description, )) if verbose: print "Removing unused entries from coinc_definer table ..." # remove redundant and already unused records from coinc_def table cursor.execute('''DELETE FROM ''' + lsctables.CoincDefTable.tableName + ''' WHERE coinc_def_id NOT IN ( SELECT MIN(coinc_def_id) FROM ''' + lsctables.CoincDefTable.tableName + ''' GROUP BY search_coinc_type, description ) ''') connection.commit() if verbose: print "Finished updating tables"
def delete_glitch_events_in_segment(connection, cursor, segment): """ Removes glitch events whose gps times fall within segment. All rows from the other tables in database connected to these glitch events via coinc_map tables are also deteled. @param connection UNDOCUMENTED @param cursor UNDOCUMENTED @param segment is glue segment """ # get table names from the database tablenames = dbtables.get_table_names(connection) if not tablenames: print "No tables were found in the database. Nothing to do." return # check if glitch table is present if not idq_tables.IDQGlitchTable.tableName in tablenames: print "No glitch table is found in database. Nothing to do" return # create sqlite function for testing if glitch gos time is inside the segment("md5", 1, md5sum) connection.create_function('gpstime_inside_segment', 3, gpstime_inside_segment) # convert segment inot segment string segment_string = segmentsUtils.to_range_strings([segment])[0] # check if coinc_def table is present if lsctables.CoincDefTable.tableName in tablenames: # get all distinct coinc types from coinc_def table coinc_descriptions = cursor.execute('''SELECT DISTINCT description FROM ''' + lsctables.CoincDefTable.tableName).fetchall() # loop over coinc descriptions for (description,) in coinc_descriptions: tables_in_coinc = idq_tables.CoincDefToTableNames[description] # work only with coincs that involve glitch table if idq_tables.IDQGlitchTable.tableName in tables_in_coinc: # remove glitch table, we will work with it later connected_tables = [t for t in tables_in_coinc if t != idq_tables.IDQGlitchTable.tableName] # loop over other tables in the coinc for t in connected_tables: # delete entries connected to glitch events that should be removed cursor.execute('''DELETE FROM ''' + t + \ ''' WHERE event_id IN (SELECT t.event_id FROM ''' + t + \ ''' AS t JOIN coinc_event_map AS c1 ON t.event_id == c1.event_id''' + \ ''' JOIN coinc_event_map AS c2 ON c1.coinc_event_id == c2.coinc_event_id''' + \ ''' JOIN ''' + idq_tables.IDQGlitchTable.tableName + \ ''' AS g ON c2.event_id == g.event_id WHERE gpstime_inside_segment(g.gps, g.gps_ns, ?))''',\ (segment_string,)) # delete entries from coinc_event table corresponding to deleted events cursor.execute('''DELETE FROM coinc_event WHERE coinc_event_id IN''' + \ ''' (SELECT c1.coinc_event_id FROM coinc_event_map AS c1 JOIN ''' + \ idq_tables.IDQGlitchTable.tableName + \ ''' AS g ON c1.event_id == g.event_id WHERE gpstime_inside_segment(g.gps, g.gps_ns, ?))''',\ (segment_string,)) # delete entries from coinc_map table corresponding to deleted events cursor.execute('''DELETE FROM coinc_event_map WHERE event_id IN''' + \ ''' (SELECT c1.event_id FROM coinc_event_map AS c1''' + \ ''' JOIN coinc_event_map AS c2 ON c1.coinc_event_id == c2.coinc_event_id ''' + \ ''' JOIN ''' + idq_tables.IDQGlitchTable.tableName +\ ''' AS g ON c2.event_id == g.event_id WHERE gpstime_inside_segment(g.gps, g.gps_ns, ?))''',\ (segment_string,)) # delete entries in glitch table cursor.execute('''DELETE FROM ''' + idq_tables.IDQGlitchTable.tableName + ''' WHERE gpstime_inside_segment(gps, gps_ns, ?)''', (segment_string,)) # commit everythings connection.commit()
plots = [plots[i] for i in options.plot] # # Process files # for n, filename in enumerate(ligolw_utils.sort_files_by_size(filenames, options.verbose, reverse = True)): if options.verbose: print >>sys.stderr, "%d/%d: %s" % (n + 1, len(filenames), filename) working_filename = dbtables.get_connection_filename(filename, tmp_path = options.tmp_space, verbose = options.verbose) database = SnglBurstUtils.CoincDatabase(sqlite3.connect(working_filename), options.live_time_program, search = "StringCusp") if options.verbose: SnglBurstUtils.summarize_coinc_database(database) is_injection_db = "sim_burst" in dbtables.get_table_names(database.connection) if is_injection_db: database.connection.cursor().execute(""" CREATE TEMPORARY TABLE sim_burst_map AS SELECT a.event_id AS simulation_id, a.coinc_event_id AS coinc_event_id, b.event_id AS event_id FROM coinc_event_map AS a JOIN coinc_event ON ( coinc_event.coinc_event_id == a.coinc_event_id ) JOIN coinc_event_map AS b ON (