1.- New 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 to send a proposal for the new table. ------------------------------------------------------------------------ 1.2.- Request #127 (E. Yu): --------------------------- ----------------------------------------------------------------- --> Make CODA.datetime not null. Just as waveform, arrival, and amp have datetime/datetime_on not null,could we have coda.datetime not null? ----------------------------------------------------------------- ------------------------------------------------------------------------ ==> Approved. Time should be of the P arrival associated with the coda. Definition should be "UTC epoch "start time" from which the relative seconds time values stored in the columns time1 ... time6 columns are measured". ------------------------------------------------------------------------ 2.- Review of pending requests: =============================== 2.1.- 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. ------------------------------------------------------------------------ 2.2.- 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. ------------------------------------------------------------------------ 2.3.- 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? -----------------------------------------------------------------