1.- Review of pending requests: =============================== 1.1.- Request #4 (B. Worden): ----------------------------- --> Add new association table between amps and events. ----------------------------------------------------------------- ==> Current proposal for strong motion sets: At the end of the last conf call, I think the proposal had evolved to 2 new tables, and NO addition to the EVENT table Table SGAMPSET Columns: sgampsetid PK id for sgampset (primary key) ampid PK ampid of specific amp reading from amp table Table ASSOCEVAMPSET Columns: evid PK evid from event table sgampsetid PK sgampsetid from sgmapset (primary key) sgampsettype PK type for sgampset (primary key) isvalid boolean flag to indicate if set is currently valid for this event. Table ASSOCAMO remains as is. The SGAMPSET defines "sets" of strong ground motion observations. We currently have only 1 type of set (eg "shakemap-quality"), but the sgampsettype provide us future expansion if we want additional type of sgm amps. Whenever we insert an amp row into the database, we would also add it to the (or a) valid set for this event and sgampsettype. How this would be used: 1. When an application (ampgen, ampgenpp, gmp2db) insert an amp row into the database for an event evid, they would query the ASSOCEVAMPSET table for all rows that contain evid, the desired sgampsettype, and isvalid flag == TRUE If there are no rows, it would get a new sgamesetid from an Oracle counter, and insert a row with: evid = eventid sgampsetid = new counter value sgampsettype = "shakemap_quality" isvalid = TRUE and will use this new sgampsetid. If the query returns one (or more) rows, it selects ANY ONE of the sgampsetids. It then enters the an association row in the STAMPSET with this sgampsetid and ampid. 2. When an event is "cloned" (by jiggle), all sgampsetids with that are associated with the original event and have isvalid=TRUE would also have new rows added for the new cloned event. Therefore, all amps that are CURRENTLY (validly) associated with the original event would be associated with the new event. IN ADDITION, NEW AMPS ADDED TO THE SET FOR EITHER THE ORIGINAL OR CLONED EVENT WOULD BE ASSOCIATED WITH BOTH EVENTS. 2. If/when an event (orgin and/or magnitude) is ever signficantly changed so that either a program or a humam decides that the current amps are no longer valid for the event and stampesettype (eg "shakemap_quality"), an application (eg jiggle) would set ISVALID=FALSE for all rows that match the evid, sgampsettype, and where ISVALID==TRUE. We could then run applications (ampgenpp, gmp2db) to generate new amp reading and/or associate unassociated readings using the new event info. 3. When an application such as shakemap wants to find all amps of a specific sgampsettype (eg "shakemap-quality") for an event, it would select all amps (or most recently inserted amp for each SNLC) where the amps are in ANY of the sgampsets of "shakemap-quality" that are currently associated with the event (eg isvalid==TRUE). NOTES AND CAVEATS: NOTE: If you want to prevent there from being multiple rows with the same evid and sgampesettype and isvalue==TRUE, we have to put a contraint on the ASSOCEVAMPSET table to prevent this. The process of creating a new sgampset for an event consists of: query for all rows with valid ampset for this event and sgampsettype; if (no_rows) { get new sgampsetid from counter insert new row into ASSOCEVAMPSET. } If multiple programs run this code "simultaneouly" for the same event, they could both get a no_rows result from the query, and then both try to insert a new row. Only the first would succeed. The code would have to be written TO DETECT THIS CONDITION, requery and retry the query (an possible insert possibly multiple times). Otherwise, we can allow there to possibly be multipe "valid" sets for an event and sgampsettype. SUGGESTIONS: - evid should be a foreign key if we don't want a set to be associated with multiple events. - Remove sgampsettype field. ----------------------------------------------------------------- 1.2.- Request #128 (S. Zuzlewski): ---------------------------------- --> Allow NULL for Amp.datetime. ------------------------------------------------------------------------ Amplitude times in the database are only approximations and therefore are not reliable. Affected readings are: - NC WA amplitudes readings from pre-CISN software. - ALL CISN SGM spectral acceleration times imported from CISN partners. ==> Approved. After seeking guidance from the Standards group, the following was approved: - Allow NULL values in the Amp.datetime field and make use of the existing fields 'wstart' (Window start) and 'duration' to represent respectively the approximate time and its associated error. * For each amplitude where we know the time and the window (ex: current RT): We can set all the fields (datetime, wstart, duration). * For each amplitude where we know the time but not the window: Amp.datetime = Amp.wstart Amp.duration = 0 * For each amplitude where we don't know the time nor the window: Amp.datetime = NULL Amp.wstart = Amp.duration = NULL - Action Items: * Caltech will use 'wstart' to partition the Amp table. * Pete will look at affected RT apps (trimag, ampgen). * Allan will look at PP (jiggle, ampgen_pp, hypomag) & SGM import apps. * Each DC will look at its historic data and update appropriately the Amp fields datetime, wstart & duration. * RT apps: - datetime: actual time of pick. - wstart: start of window. - duration: filling in. - Need to be changed to use the correct API's (nominal vs. true). * PP apps: - Need to update gmp2db & db2gmp. ------------------------------------------------------------------------ ------------------------------------------------------------------------ Ellen removed the constraint on a test database. Allan thinks that the constraint can be removed directly on the production databases. If we allow NULL values for the Amp.datetime field, it should not affect any applications. The group agreed to make the following changes on the productions DB's: * Remove NOT NULL constraint on Amp.datetime. * Add NOT NULL constraint to Amp.wstart. This might take some time at Caltech since Ellen needs to deal first with migrating the partitioning of the Amp table. ------------------------------------------------------------------------ 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. - In NC, Pete created a script to reconfigure channel list Id's and names. Also Config_Channel is now a view of AppChannels and Program a synonym of Applications. - Ellen mentioned that she is/was working on a better way to manage channel lists with a GUI interface. ------------------------------------------------------------------------ ------------------------------------------------------------------------ Caltech still has those two objects as separate tables. In NC, they are now a view/synonym of the AppChannels/ Applications tables. Ellen mentioned that they had some issues but soon should be able to provide NC with a testbed for her GUI interface to manage channel lists. ------------------------------------------------------------------------ 1.4.- Request #126 (E. Yu): --------------------------- --> New 'WaveRoots' table. ------------------------------------------------------------------------ ==> Request has been approved. - Table & packages are installed in production (NC & SC). - Applications migrated: - SC: * Jiggle: Yes * DHI: Yes * STP: No - NC: * DHI: Yes * bsdata: Yes but more testing needed ------------------------------------------------------------------------ ------------------------------------------------------------------------ Shang Lin updated STP to use the Waveroots table. It is now in production mode. Needs to check with Doug N. if he is done testing bsdata. This completes the requirements of this request and it can probably now be taken off the agenda. ------------------------------------------------------------------------ 1.5.- Request #10 (D. Given): ----------------------------- --> Add new column "nobs" to NETMAG table. ------------------------------------------------------------------------ ==> Request has been approved. - This change has eventual implications for the following applications: Trimag, EC, Jiggle, MT, dbselect, Stored Procedures, STP(SC), RTMd(SC). - Ellen tested and documented a procedure to perform a DDL operation in a replication environment. - Ellen used the procedure to add the new field in SC. ------------------------------------------------------------------------ ------------------------------------------------------------------------ Ellen added the new field Netmag.nobs to the Caltech production DB's. Same operation needs to be performed in NC. Allan mentioned that he would not start updating the codes until all RSN databases have been updated. This discussion brought up the larger issue of how to deal with different code revisions and database versions. Should all the RSN's always be on the same code revision and database version? If not, how do we deal with bug fixes? Do we have to implement backports for earlier versions or do we require RSN's to upgrade to the latest release. The group agreed that this issue should probably be brought up to the Standards group or possibly the Software group. ------------------------------------------------------------------------ 1.6.- Request #53 (D. Given): ----------------------------- --> Add integer attribute for each channel to hold COSMOS "Table 6" value. ------------------------------------------------------------------------ ==> Allan sent a new version of the Cosmos schema. - Stephane loaded the Cosmos schema on a test database and updated createv0_xml to use those tables. - Other applications could benefit from those tables. For example: v02mseed. ------------------------------------------------------------------------ 1.7.- 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. ==> D. Given pointed out that contact information can change often. NC to discuss some more about it. ------------------------------------------------------------------------