Module ligolw_sqlutils

SQLite doesn't have a tuple type built-in. This can be frustrating if one
needs to compare values from multiple columns when doing queries. For example,
if one wanted to do something like:

connection.cursor().execute('''
    SELECT *
    FROM a 
    WHERE (a.val1, a.val2) IN (
        SELECT (b.val1, b.val2) 
        FROM b)
    ''')

an error would be raised.

This function tries to alleiviate the problem by giving the ability to concatenate
results from multiple columns into a single colon-seperated string. These strings can then be
compared directly. So, in the above example, one would do:

from pylal import ligolw_sqlutils as sqlutils
connection.create_function("concatenate", 2, sqlutils.concatenate)
connection.cursor().execute('''
    SELECT *
    FROM a 
    WHERE concatenate(a.val1, a.val2) IN (
        SELECT concatenate(b.val1, b.val2) 
        FROM b)
''')

Note that the create_function method must be called first with the number of 
values that will be passed to concatenate before using it in any query.

Strips and checks that there are no newlines, tabs, spaces, or semi-colons in the given option. This should be used for options that will be plugged into sqlite statements to protect against injection attacks. If lower is set on, will also make all letters lower-case in the option.

Get all index names and associated SQL CREATE statements associated with tables in table_names. Return a list of (idx_name, sql) tuples.

Converts durations stored in the experiment_summary_table from seconds 
to other units of time.

@duration: duration to convert; assumed to be a float or long in seconds
@convert_to: the unit to convert to. Options are:
    's': to seconds - will just divide by 1.
        This can be useful if need to convert
        the duration from a long int to a float.
    'min': to minutes - will divide by 60.
    'hr': to hours - will divide by 3600.
    'days': to days - will divide by 86400.
    'yr': to years - will divide by 31557600. 
        This is the Julian year, which is the
        accepted astronomical year

Attempts to get column type from lsctables.py for the given table name and column name. If the table doesn't exist in lsctables or the column doesn't exist in the lsctables definition of the table, returns the default type.

Creates a column in the given table if it doesn't exist. Note that table_name and column_name must be all lower-case.

Cleans metadata from tables that don't have process_ids in any of the tables
listed in the key_tables list.

@connection: connection to a sqlite database
@key_tables: list of tuples that must have the following order:
    (table, column, filter)
 where:
    table is the name of a table to get a save process id from,
    column is the name of the process_id column in that table
    (this doesn't have to be 'process_id', but it should be a
    process_id type),
    filter is a filter to apply to the table when selecting process_ids

Create a map between process_ids so duplicate entries in numerous ligolw_xml tables can be removed without a loss of information. The tables the _pidmap_ is used to simplify are:

-- process, process_params -- filter, summ_value, search_summary, search_summvars -- sim_inspiral, sim_ringdown -- time_slide -- update the sim_proc_ids in the experiment_summary table

Delete duplicate information in summary tables according to the process_ids. tbl_name: filter, summ_value, search_summary, search_summvars

Update process_ids for events in the sngl_inspiral &/or sngl_ringdown table. This function does *not* remove duplicate events that have different event_ids.

Delete duplicate rows in the process & process params table. The temp table _pidmap_ created by the get_process_info function is dropped.

Writes JOIN string to join the experiment, experiment_summary, and experiment_map tables to the specified table. This allows querying across any of these tables.

Writes JOIN string to join the experiment, experiment_summary, and experiment_map tables to the specified table. This allows querying across any of these tables.

Removes entries from the experiment, experiment_summary, and time_slide tables that have no events in them, i.e., that have no mapping to any coinc_event_ids via the experiment_map table. Entries are only removed if none of the experiment_summ_ids associated with an experiment_id have coinc_events. In other words, Even if only one of the experiment_summ_ids associated with an experiment_id has an event, all of the experiment_summ_ids and experiment_ids associated with that event are saved. This perserves the background time and slide set associated with an experiment.

WARNING: This should only be used for purposes of scaling down a temporary database in prep. for xml extraction. In general, all experiment and time_slide entries should be left in the experiment tables even if they don't have events in them.

Updates the number of events in the num_events column of the experiment_summary table. This should be used whenever coincs are deleted from the experiment_map table or when new files are added to a database.

Clears experiment_map, coinc_event, coinc_event_map, and all tables pointing to the coinc_event_map of triggers that are no longer in the specified table. Note that the experiment_summary, experiment, and time_slide_tables are left alone. This is because even if no events are left in an experiment, we still want info. about the experiment that was performed.

@connection to a sqlite database

Clears the given table of coinc triggers falling outside of the
desired ranges, as specified by parse_param_ranges and parse_coinc_opts.

@connection: connection to a SQLite database with lsctables
@coinc_table: name of the coinc_table to delete the triggers from.
 Can be any table with a coinc_event_id.
@param_filters: output of parse_param_ranges(...).get_param_filters()
@include_coincs: output of parse_coinc_opts(...).get_coinc_filters().
   The coincs that are specified in this list will be SAVED.
@exclude_coincs: output of parse_coinc_opts(...).get_coinc_filters().
    The coincs that are specified in this list will be DELETED.
Note: exclude_coincs is applied first, so anything falling in it will 
be deleted, regardless of wether or not the same falls in include_coincs.
To avoid confusion, it is best to only specify one or the other, not both.

Adds a new entry to the coinc_definer_table. The only thing used to discriminate different coinc_definer entries is the description column. Search and search_coinc_type can also be optionally specified.

Retrieves all of the distinct map-label and coinc-type pairs in the database. A dictionary is returned in which they keys are the map-labels and the values are lists of tuples. Each tuple gives all of the tables mapped to a coinc_event_id.

Gets all the tables that are directly mapped to a list of coinc_event_ids. Returns a dictionary mapping the tables their matching coinc_event_ids.

Writes JOIN string to join the experiment, experiment_summary, and experiment_map table to the coinc_inspiral table. NOTE: Should only use when querying the coinc_inspiral table (i.e., the only table listed in the FROM statement is the coinc_inspiral).

Clears coinc_inspiral table of coinc triggers falling outside of the desired ranges, as specified by parse_param_ranges and parse_coinc_opts.

See apply_inclusion_rules_to_coinc_table for more info.

Clears experiment_map, coinc_event, coinc_event_map, and all tables pointing to the coinc_event_map of triggers that are no longer in the coinc_inspiral table. Note that the experiment_summary, experiment, and time_slide_tables are left alone. This is because even if no events are left in an experiment, we still want info. about the experiment that was performed.

Creates a temporary table in the sqlite database called sim_rec_map.
This table creates a direct mapping between simulation_ids in the simulation table
and coinc_event_ids from the recovery_table, along with a ranking stat from the 
recovery_table.
The columns in the sim_rec_map table are:
    * rec_id: coinc_event_ids of matching events from the recovery table
    * sim_id: the simulation_id from the sim_inspiral table
    * ranking_stat: any stat from the recovery table by which to rank
In addition, indices on the sim and rec ids are put on the table.

Note that because this is a temporary table, as soon as the connection is
closed, it will be deleted.

@connection: connection to a sqlite database
@simulation_table: any lsctable with a simulation_id column; e.g., sim_inspiral
@recovery_table: any lsctable with a coinc_event_id column; e.g., coinc_inspiral
@map_label: the label applied to the mapping between the injections and recovered
@ranking_stat: the name of the ranking stat in the recovery table to use.
 If set to None, ranking_stat column won't be populated.

Remove duplicates from simulation tables (sim_inspiral & sim_ringdown) if those tables exist in the database. Also update the sim_proc_id column in the experiment_summary table.

Gets all instrument sets available in the time slide table and gets all time-slide ids associated with that instrument set. Since this only uses the time-slide table, will get everything even if there were no coincident events during a time-slide.