1 -- Copyright (C) 2011--2012,2014 Kipp Cannon, Chad Hanna, Melissa Frei
3 -- This program is free software; you can redistribute it and/or modify it
4 -- under the terms of the GNU General Public License as published by the
5 -- Free Software Foundation; either version 2 of the License, or (at your
6 -- option) any later version.
8 -- This program is distributed in the hope that it will be useful, but
9 -- WITHOUT ANY WARRANTY; without even the implied warranty of
10 -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General
11 -- Public License for more details.
13 -- You should have received a copy of the GNU General Public License along
14 -- with this program; if not, write to the Free Software Foundation, Inc.,
15 -- 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
18 DROP table if EXISTS sim_inspiral;
21 -- coinc_definer clean up
24 CREATE TEMPORARY TABLE _idmap_ AS
26 old.coinc_def_id AS old,
27 MIN(new.coinc_def_id) AS new
30 JOIN coinc_definer AS new ON (
31 new.search == old.search
32 AND new.search_coinc_type == old.search_coinc_type
36 CREATE INDEX tmpindex ON _idmap_ (old);
38 UPDATE coinc_event SET coinc_def_id = (SELECT new FROM _idmap_ WHERE old == coinc_def_id);
39 DELETE FROM coinc_definer WHERE coinc_def_id IN (SELECT old FROM _idmap_ WHERE old != new);
45 -- segment_definer clean up. NOTE; this assumes no meaningful information
46 -- is stored in the version and comment columns, and will scramble it if
47 -- there is. that is, two segment_definer rows are considered to be
48 -- equivalent even if their version and comment values differ, and the one
49 -- with the higher ID will be discarded.
52 CREATE TEMPORARY TABLE _idmap_ AS
54 old.segment_def_id AS old,
55 MIN(new.segment_def_id) AS new
57 segment_definer AS old
58 JOIN segment_definer AS new ON (
60 AND new.name == old.name
64 CREATE INDEX tmpindex ON _idmap_ (old);
66 UPDATE segment_summary SET segment_def_id = (SELECT new FROM _idmap_ WHERE old == segment_def_id);
67 UPDATE segment SET segment_def_id = (SELECT new FROM _idmap_ WHERE old == segment_def_id);
68 DELETE FROM segment_definer WHERE segment_def_id IN (SELECT old FROM _idmap_ WHERE old != new);
74 -- time_slide clean up
77 CREATE TEMPORARY TABLE _idmap_ AS
80 (SELECT group_concat(instrument || "=" || offset) FROM time_slide AS time_slide_a WHERE time_slide_a.time_slide_id == time_slide.time_slide_id ORDER BY instrument) AS repr,
86 CREATE INDEX tmpindex ON _idmap_ (repr, old);
88 UPDATE _idmap_ SET new = (SELECT MIN(old) FROM _idmap_ AS a WHERE a.repr == _idmap_.repr);
90 CREATE INDEX tmpindex ON _idmap_ (old);
92 UPDATE coinc_event SET time_slide_id = (SELECT _idmap_.new FROM _idmap_ WHERE _idmap_.old == time_slide_id);
93 DELETE FROM time_slide WHERE time_slide_id IN (SELECT old FROM _idmap_ WHERE old != new);
105 "Number of coincs before clustering: " || count(*)
110 -- create a look-up table of info required for clustering
113 CREATE TEMPORARY TABLE _cluster_info_ AS
115 coinc_event.coinc_event_id AS coinc_event_id,
116 coinc_event.time_slide_id AS category,
117 (coinc_inspiral.end_time - (SELECT MIN(end_time) FROM coinc_inspiral)) + 1e-9 * coinc_inspiral.end_time_ns AS end_time,
118 coinc_event.likelihood AS ranking_stat
121 JOIN coinc_inspiral ON (
122 coinc_inspiral.coinc_event_id == coinc_event.coinc_event_id
124 CREATE INDEX tmpindex1 ON _cluster_info_ (coinc_event_id);
125 CREATE INDEX tmpindex2 ON _cluster_info_ (category, end_time, ranking_stat);
128 -- delete coincs that are within 4 s of coincs with higher SNR in the same
139 _cluster_info_ AS _cluster_info_a_
140 JOIN _cluster_info_ AS _cluster_info_b_ ON (
141 _cluster_info_b_.category == _cluster_info_a_.category
142 AND (_cluster_info_b_.end_time BETWEEN _cluster_info_a_.end_time - 4.0 AND _cluster_info_a_.end_time + 4.0)
143 AND _cluster_info_b_.ranking_stat > _cluster_info_a_.ranking_stat
146 _cluster_info_a_.coinc_event_id == coinc_event.coinc_event_id
148 DROP INDEX tmpindex1;
149 DROP INDEX tmpindex2;
150 DROP TABLE _cluster_info_;
153 "Number of coincs after clustering: " || count(*)
158 -- delete unused coinc_inspiral rows
164 coinc_event_id NOT IN (
172 -- delete unused coinc_event_map rows
178 coinc_event_id NOT IN (
186 -- delete unused sngl_inspiral rows
198 table_name == 'sngl_inspiral'