1.- New Requests: ================= 1.1.- Request #128 (S. Zuzlewski): ---------------------------------- ----------------------------------------------------------------- --> Allow NULL for Amp.datetime. In the hypoinverse format, the time of the maximum amplitude is never recorded on the archive line because it was never measured in the analog era and no data exists for it. In the context of importing the historic NCSN catalogs at the NCEDC, we would like to be able to set the amplitude times to NULL. ----------------------------------------------------------------- ------------------------------------------------------------------------ ==> Discussed. Value could be set to: - NULL: problematic because Caltech is using this field for partitioning the Amp table. - Some bogus value. - Origin Time. Take issue to Standards Group: - Is it acceptable that the time is not reliable? - If not, can we remove the unknown values? ------------------------------------------------------------------------ 1.2.- Request #129 (S. Zuzlewski): ---------------------------------- ----------------------------------------------------------------- --> Add hardware ownership information. We want to add hardware ownership information to the HT schema in order to identify which piece of hardware belongs to whom (similar to the 'owners' table in SIS). It would require a new table: ownerid int (PK) name string contact string lddate date We would then add the field 'ownerid' as a foreign key in the Sensor, Filamp & Datalogger tables. ----------------------------------------------------------------- ------------------------------------------------------------------------ ==> Discussed. D. Given pointed out that contact information can change often. NC to discuss some more about it. ------------------------------------------------------------------------ 1.3.- Request #130 (S. Zuzlewski): ---------------------------------- ----------------------------------------------------------------- --> AppChannels vs Config_Channel. Issue of config_channel/program tables use by RT programs and applications/appchannels by post proc. We should ideally create one set of tables and both should use the same set. It will simplify the schema, and avoid the confusion of what tables to populate for program's channel lists. * Config_Channel (RT): * Program (RT): PROGID NUMBER(8,0) PROGID NUMBER(8,0) NET VARCHAR2(8) NAME VARCHAR2(16) STA VARCHAR2(6) LDDATE DATE SEEDCHAN VARCHAR2(3) LOCATION VARCHAR2(2) CONFIG VARCHAR2(64) LDDATE DATE * AppChannels (PP): * Applications (PP): PROGID NUMBER(8,0) PROGID NUMBER(8,0) NET VARCHAR2(8) NAME VARCHAR2(16) STA VARCHAR2(6) LDDATE DATE SEEDCHAN VARCHAR2(3) LOCATION VARCHAR2(2) CONFIG VARCHAR2(64) ONDATE DATE OFFDATE DATE LDDATE DATE * Ellen's Strawman: 1. One table is the source of channel lists (i.e. associating SNCL+ondate+offdate with an application), which shall be appchannels. You cannot have the same program/application name with different channel sets. 2. On the real time databases, there is a readonly snapshot of appchannels. i.e. "CREATE SNAPSHOT AppChannels AS SELECT * FROM AppChannels@archdb". This snapshot can be refreshed at regular intervals, or manually if desired by the RT operator. 3. Currently RT programs create config files by querying config_channel. Config_channel does not have ondate/offdate. If we cannot modify queries of the scripts that query config_channel in a timely manner, we can create a RT snapshot of appchannels called "config_channel" that only queries online entries of appchannels. i.e. "CREATE SNAPSHOT Config_Channel AS SELECT progid,net,sta, seedchan,config,lddate,location FROM AppChannels@archdb WHERE offdate = '3000/01/01'". Then the RT side would not need much changing. ----------------------------------------------------------------- ------------------------------------------------------------------------ ==> Discussed. Caltech pointed out that those tables are going to be deprecated at some point. ------------------------------------------------------------------------ 2.- Review of pending requests: =============================== 2.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. ==> Table to be named 'WaveRoots'. Field 'rank' to be renamed '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. TABLE: Waveroots 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 or (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 WCOPY not null number -- Copy number of the waveform file location. Used to distinguish between locations that contain a copy of the waveform file. The copy number should start from 1. The copy number does not indicate quality or any preference of one copy over another; it is used to just distinguish one copy from another. ==> Install table in production. Table should be in WF schema. Migrate applications: - QC merging. - Population programs. - Jiggle, STP, DHI, bsdata. ------------------------------------------------------------------------ 2.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. ==> Ellen will test and document her procedure. ------------------------------------------------------------------------ 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? ==> Doug N. to summarize discussion (flag for set used/not used). ----------------------------------------------------------------- 2.4.- 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. ------------------------------------------------------------------------