1.- Review of pending requests: =============================== 1.1.- Request #126 (E. Yu): --------------------------- --> Add "net" to wavefileroots table and redefine meaning of wavefileroots.archive column. ------------------------------------------------------------------------ ==> For both NC and SC to use wavefileroots table to locate waveforms in archive, I propose we add the column "net" (same as station_data.net) so waveforms can be organized by network. The "Archive" column is defined to mean the data center archive for which this rule applies. I am submitting an example of a populated table by email to schema change wg participants. Also add a "rank" (number) column. This column would allow multiple copies of waveforms to exist in different places. Also add column "format" (same as waveform.wave_fmt). ==> Approved. Ellen Yu sent a proposal for the new table: TABLE: Wavefileroots DEFINITION: This table contains the possible locations of waveforms based common columns it shares with the WAVEFORM table. COLUMNS: ARCHIVE NOT NULL VARCHAR2(8) -- This field specifies the place where the seismogram is archived - such as NCEDC/SCEDC WAVETYPE NOT NULL VARCHAR2(2) -- This attribute denotes the type of waveform (T)riggered or (C)ontinuous STATUS NOT NULL VARCHAR2(2) -- This attribute denotes the status of the waveform in the file: (A)rchived, (T)emporary NET VARCHAR2(8) -- Unique network identifier. This character string is the name of a seismic network. A null value means waveforms from any network could reside in this location. WAVE_FMT NOT NULL NUMBER(2) -- Waveform type - (numeric code for format -mSEED, SAC, SEGY etc) FILEROOT NOT NULL VARCHAR2(255) -- The top file directory of the waveform file. Subdirectories are allowed (i.e. /top_directory/subdirectory1/subdirectory2) but the top level must be included and it must be consistent with entries in SUBDIR. DATETIME_ON NOT NULL NUMBER(25,10) -- The datetime_on of the waveform row must be >= to this value. DATETIME_OFF NOT NULL NUMBER(25,10) -- The datetime_off of the waveform row must be < to this value RANK NOT NULL NUMBER -- The order which this location will be used to retrieve a waveform file. A value 1 means this location will be used first. ------------------------------------------------------------------------ ------------------------------------------------------------------------ ==> Table to be named 'WaveRoots'. Field 'rank' renamed to 'wcopy'. Ellen will come up with the DDL script. Allan will update the stored procedures. Make inventory of applications that will be affected by this change. ------------------------------------------------------------------------ 1.2.- Request #10 (D. Given): ----------------------------- --> Add new column "nobs" to NETMAG table. ------------------------------------------------------------------------ ==> Request has been approved but the implementation is on hold until: 1.- NC is done migrating to the new CISN software. 2.- SC is done migrating to the leap seconds compliant code. ==> This change has eventual implications for the following applications: Trimag, EC, Jiggle, MT, dbselect, Stored Procedures, STP(SC), RTMd(SC). ==> Ellen started testing the replication issues associated with adding a new field to an existing table. She came to the conclusion that during the update to the replication environment, there is a small time window where transactions can be lost. There does not seem to be a way around this issue. ==> Stephane came up with a possible scenario involving both the master and slave RT systems. In theory, no transaction should be lost on a master system by following this scenario. Ellen to look at scenario. Involve Paul later on. ------------------------------------------------------------------------ ------------------------------------------------------------------------ ==> Ellen tested scenario and came up to the conclusion that transactions could still be lost. Ellen proposed new scenario with bypass table. ------------------------------------------------------------------------ 1.3.- Request #53 (D. Given): ----------------------------- --> Add integer attribute for each channel to hold COSMOS "Table 6" value. ------------------------------------------------------------------------ ==> A. Walter is currently involved in NSMP project. We should wait until he has more information concerning this matter. ==> A. Walter sent out a proposal. Ellen & Stephane to look at it and provide feedback. ==> Allan to send new version of the schema. Ellen & I to look at it. ------------------------------------------------------------------------ ------------------------------------------------------------------------ ==> Stephane to start implementing code to populate and query those tables. ------------------------------------------------------------------------ 1.4.- Request #4 (B. Worden): ----------------------------- --> Add new association table between amps and events. ----------------------------------------------------------------- Need to associate amplitudes with events. (The schema originally had this association, and it was removed at Caltech's request, due to performance implications in the real-time system when events were merged). ==> D. Neuhauser sent out a strawman for implementing sets. Strong Ground Motion Sets. Strawman: 1. Create a new table SGMAMPSET with fields: sgmampsetid (integer) composite primary key ampid (integer) composite primary key 2. Add to ORIGIN table: sgmampsetid (integer) Before inserting strong ground motion info into the database for an event, if the sgmampsetid in the ORIGIN table is NULL, get a new counter for the sgmampsetid, and update the ORIGIN table with is value. When inserting strong ground motion into the database for an event, associated with the set specified by the sgmampsetid in the preferred origin on the event. When a new origin is computed that "near" the previous preferred origin, the new origin can be assigned the same sgmapampsetid as the previous origin. If the new origin is significantly different, the sgmampsetid can be left NULL to indicate that there is no current set of strong ground motions to be associated with this event (really with this origin). Programs that want to harvest strong ground motion records for an event would only have to get all amplitudes associated with the sgmampsetid of the preferred origin. ==> Ellen sent a new proposal: 1. Add column "prefsgmampset" (number) to table EVENT. fk reference to SGAMPSET.sgmampsetid 2. New table: SGMAMPSET columns: sgmampsetid (number) ampid (number) - fk reference to AMP.ampid lddate (date) 3. New table: ASSOCEVAMPSET columns: evid (number) - fk reference to EVENT.evid sgmampsetid (number) - fk refernece to SGMAMPSET.sgmampsetid lddate (date) 4. Table ASSOCAMO remains as is. Proposed functionality: 1. EVENT.prefsgmampsetid gives information about the preferred amp set for a given amplitude. 2. Any amplitude can be a member of an amp set, even if one or more have different origin associations. This should address ShakeMap issues. 3. History of past amp sets for an event can be tracked through SGAMPSET. Question - is this sufficient to track through lddate? 4. The origin used for an amplitude is tracked through ASSOCAMO (current practice). Question - is this sufficient? -----------------------------------------------------------------