LIGO database table modification/addition questionnaire This questionnaire is intended to provide a starting point for discussing a proposed change to the LIGO database tables, and to bring forth all of the issues of coordination, etc. Please fill in the answers and circulate it for comment. If you are not sure about any of the answers, please try to find an appropriate person to ask. 1. Who is making this proposal? /(Indiate individual(s) and/or working group(s))/ Erik Katsavounidis for the Bursts Working Group 2. What do you propose to do? /(Check one or more)/ [X] Modify one or more existing database table(s) [ ] Add one or more new database table(s) [ ] Other 3. Briefly describe your proposal. /(Examples: "Add two columns to the sngl_burst table"; "Create a table called sngl_pings to store triggers from the 'pings' DSO")/ Add 32 new columns to the single bursts table 4. Explain why the proposed change is necessary. /(Examples: "Need to be able to store the time of the peak filter output"; "The 'pings' DSO calculates several interesting event parameters which do not fit into the sngl_burst table")/ Present table fields are insufficient to describe the burst event parameters; 7 new fields are introduced in order to facilitate this. These new fields are generally method-dependent. A set of additional 8 fields are needed in order to describe the burst event parameters in a uniquely (and thus commonly among the methods) defined way: these are the event time, frequency and strength together with their respective errors. Another set of 11 fields are introduced in order to allow a method- dependent description of the parameters of the most significant time-frequency pixel involved in a trigger. Finally, 6 more generic, undefined fields are proposed in order to allow each method to store the names and values of 3 additional quantities not currently identifiable. 5. Does the proposal require doing some administrative operation on the DB2 database servers? /(Hint: the answer is 'yes' unless the relevant table is never intended to be stored in an LDAS database.)/ [X] Yes [ ] No 6. If the proposal is to modify an existing LDAS database table, does the necessary DB2 administrative operation preserve existing data in the table? [X] Yes, because the proposal is to add one or more columns [ ] Yes, because of some other reason: _________________ [ ] No, but the table has no data in it yet, so it can safely be deleted and recreated [ ] No, there is existing data which will need special handling [ ] Not applicable 7. Does the proposal require a synchronized change to SQL schema files used by LDAS? /(Hint: the answer is 'yes' if the proposal is to modify or add a table in the LDAS database, except for certain administrative operations such as creating or deleting an index)/ [X] Yes [ ] No 8. Does the proposal require changing LDAS C++ and/or Tcl source code? /(Hint: the answer is 'no' if the proposal just modifies or adds a table in the LDAS database, but could conceivably be 'yes' if an architectural change to the general table design is being proposed.)/ [ ] Yes [X] No 9. Does the proposal require changing LAL? /(Certain tables are represented as data structures in LAL)/ [ ] Yes, and the change must be released synchronously [X] Yes, but the change does not need to be synchronized [ ] No 10. Does the proposal require changing guild? /(Guild can *display* arbitrary table contents, but must be modified if you want to be able to query new tables or select rows from existing tables based on values in newly-added columns)/ [X] Yes [ ] No 11. What is the anticipated data volume (/e.g./, number of rows per interferometer per month of LIGO running time) for the new or modified table(s)? Hard if not impossible to comment. Tentative first-pass trigger rates are O(1Hz) per burst search method (event trigger generator). 12. Add comments or additional information (/e.g./, exact names(s) or columns to be added, exact definition of the proposed new table, etc.) here as appropriate: * * The original sngl_burst.sql is modified below accordingly. In order to facilitate your scanning of the following information, lines starting with "--" are existing lines, lines starting with "++" are new lines inserted and lines starting with "xx" are old lines that were modified. CREATE TABLE sngl_burst ( -- Event table for single-interferometer burst-event search. -- Database which created this entry creator_db INTEGER NOT NULL WITH DEFAULT 1, -- INFORMATION ABOUT THE PROCESS WHICH GENERATED THIS EVENT -- Process which generated this event process_id CHAR(13) FOR BIT DATA NOT NULL, -- Filter identifier (indicates type of filter, plus parameters). May be null filter_id CHAR(13) FOR BIT DATA, -- Interferometer ifo CHAR(2) NOT NULL, -- Brief keyword to identify the search technique, e.g. "template" or "FCT" search VARCHAR(24) NOT NULL, -- Channel that was analyzed channel VARCHAR(64), xx TIME OF THE EVENT - THESE QUANTITIES ARE METHOD-DEFINED -- The start time of this burst event (in GPS seconds and nanoseconds) start_time INTEGER NOT NULL, start_time_ns INTEGER NOT NULL, ++ The stop time of this burst event (in GPS seconds and nanoseconds) ++ stop_time INTEGER NOT NULL, ++ stop_time_ns INTEGER NOT NULL, xx The time duration of this burst event (seconds), by definition this is the stop time minus the start time duration REAL NOT NULL, xx PROPERTIES OF THE EVENT - THESE QUANTITIES ARE METHOD-DEFINED ++ Low frequency (start frequency) of event (Hz) ++ flow REAL, ++ High frequency (end frequency) of event (Hz) ++ fhigh REAL, -- Center of frequency band in which observation is made (Hz) central_freq REAL, xx Range of frequency observed (Hz), by definition fhigh minus flow bandwidth REAL, -- Absolute signal amplitude (fractional strain) amplitude REAL NOT NULL, -- Signal to noise ratio snr REAL, -- Confidence variable confidence REAL, ++ time-frequency volume of the event, i.e, the number of pixels ++ tfvolume REAL, ++ strength of the event in calibrated strain root-sum-square (in-band) ++ h_rss REAL, ++ time shift of the time series ++ time_lag REAL NOT NULL, ++ Properties of the event filled in by a GENERAL BURST PARAMETER ++ estimation code - these quantities are NOT METHOD-DEPENDED ++ PRECISE DEFINITION IS PENDING ++ The peak time of this burst event (in GPS seconds and nanoseconds) ++ peak_time INTEGER, ++ peal_time_ns INTEGER, ++ Peak frequency of the event (Hz) ++ peak_frequency REAL, ++ Peak calibrated strain of the event ++ peak_strain REAL, ++ Error in peak time (in GPS seconds and nanoseconds) ++ peak_time_error INTEGER, ++ peal_time_ns_error INTEGER, ++ Error in peak frequency of the event (Hz) ++ peak_frequency_error REAL, ++ Error in peak calibrated strain of the event ++ peak_strain_error REAL, ++ Properties of the (M)ost (S)ignificant pixel of the event. ++ These quantities are method-depended and they are useful for ++ trigger generators that want to capture more information about ++ the distribution of power in the event cluster. ++ Quantities are the same as in the TIME/PROPERTIES OF THE EVENT ++ sections above ++ ms_start_time INTEGER, ++ ms_start_time_ns INTEGER, ++ ms_stop_time INTEGER, ++ ms_stop_time_ns INTEGER, ++ ms_duration REAL, ++ ms_flow REAL, ++ ms_fhigh REAL, ++ ms_bandwidth REAL, ++ ms_hrss REAL, ++ ms_snr REAL, ++ ms_confidence REAL, ++ This is a set of three name value pairs that get populated by particular ++ trigger generators when they don't fit into the general structure ++ outlined above ++ param_one_name VARCHAR(64), ++ param_one_value DOUBLE, ++ param_two_name VARCHAR(64), ++ param_two_value DOUBLE, ++ param_three_name VARCHAR(64), ++ param_three_value DOUBLE, -- Unique identifier for this event event_id CHAR(13) FOR BIT DATA NOT NULL, -- Insertion time (automatically assigned by the database) insertion_time TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP, CONSTRAINT s_burst_pk PRIMARY KEY (event_id, creator_db), CONSTRAINT s_burst_fk_pid FOREIGN KEY (process_id, creator_db) REFERENCES process(process_id, creator_db), -- Note that filter_id is allowed to be null, in which case no check is made. CONSTRAINT s_burst_fk_filt FOREIGN KEY (filter_id, creator_db) REFERENCES filter(filter_id, creator_db) ) -- The following line is needed for this table to be replicated to other sites DATA CAPTURE CHANGES ; -- Create an index based on time CREATE INDEX s_burst_ind_tim ON sngl_burst(start_time, snr) ; -- Create an index based on process_id CREATE INDEX s_burst_ind_pid ON sngl_burst(process_id, start_time) ; -- Create an index based on filter_id CREATE INDEX s_burst_ind_fid ON sngl_burst(filter_id, start_time) ; -- Create an SQL trigger so that if a sngl_burst entry is deleted, any -- associated sngl_datasource, sngl_transdata, and/or sngl_mime entries -- are deleted too. -- Must be done this way because there is no foreign-key relationship. -- Run script sngl_burst_tr_del.sql to create delete trigger for -- sngl_datasource, sngl_transdata, and sngl_mime records.