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