1.- New Requests: ================= 1.1.- Request #132 (A. Walter): ------------------------------- ----------------------------------------------------------------- --> Add new fileid column to UnassocAmp table. Fileid column is a number(15) datatype whose value is obtained from a new sequence named UNASSOCSEQ. The sequence value is assigned to all new rows parsed from a single GMP channel packet (1-6 amps). Unassocamp rows having the same fileid value are associated with same event origin, if any of them are found inside its predicted seismic energy window. ----------------------------------------------------------------- ----------------------------------------------------------------- ==> Approved. ----------------------------------------------------------------- 2.- Review of pending requests: =============================== 2.1.- Request #131 (E. Yu): --------------------------- --> Store categories of applications in an AppCategory and AssocAppCat table (similar to event types). ----------------------------------------------------------------- ==> Ellen to start using those tables and come back with some feedback and/or recommendations. ----------------------------------------------------------------- 2.2.- 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, Shakemap. - NC & SC will start writing an application to populate the main table (C_ChannelData) for their respective networks. ------------------------------------------------------------------------ ------------------------------------------------------------------------ ==> Stephane updated the tables with new values from the Cosmos documentation. Sent out new version of the schema. ------------------------------------------------------------------------ 2.3.- Request #4 (B. Worden): ----------------------------- --> Add new association table between amps and events. ----------------------------------------------------------------- ==> Proposal for amplitude sets: Table AMPSET Columns: PK ampsetid NUMBER Id for ampset PK ampid NUMBER Ampid of specific amp reading from amp table Table ASSOCEVAMPSET Columns: PK ampsetid NUMBER Sgampsetid from ampset PK ampsettype VARCHAR2(20) Type for ampset evid NUMBER Evid from event table subsource VARCHAR2(8) Subsource isvalid NUMBER Boolean flag to indicate if set is currently valid for this event lddate DATE Load date Table AMPSETTYPES Columns: PK ampsettype VARCHAR2(20) Short description of amplitude set type description VARCHAR2(80) Long description of amplitude set type ASSOCEVAMPSET.ampsetid has foreign key reference to AMPSET.ampsetid ASSOCEVAMPSET.ampsettype has foreign key reference to AMPSETTYPES.ampsettype Tables EVENT & ASSOCAMO remain as is. The AMPSET defines "sets" of strong ground motion observations. We currently have only 1 type of set (eg "shakemap-quality"), but the ampsettype 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 ampsettype. How this would be used: 1. When an application (ampgen, ampgenpp, gmp2db) inserts an amp row into the database for an event evid, they would query the ASSOCEVAMPSET table for all rows that contain: evid, the desired ampsettype, and isvalid flag == TRUE If there are no rows, it would get a new amesetid from an Oracle counter, and insert a row with: evid = eventid ampsetid = new counter value ampsettype = "shakemap_quality" isvalid = TRUE and will use this new ampsetid. If the query returns one (or more) rows, it selects ANY ONE of the ampsetids. It then enters an association row in the AMPSET with this ampsetid and ampid. If/when an event (origin and/or magnitude) is ever significantly changed so that either a program or a human decides that the current amps are no longer valid for the event and ampsettype (eg "shakemap_quality"), an application (eg jiggle) would set ISVALID=FALSE for all rows that match the evid, ampsettype, and where ISVALID==TRUE. We could then run applications (ampgenpp, gmp2db) to generate new amp readings and/or associate unassociated readings using the new event info. When an application such as shakemap wants to find all amps of a specific ampsettype (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 ampsets of "shakemap-quality" that are currently associated with the event (eg isvalid==TRUE). The process of creating a new ampset for an event consists of: query for all rows with valid ampset for this event and ampsettype; if (no_rows) { get new ampsetid from counter insert new row into ASSOCEVAMPSET. } If multiple programs run this code "simultaneously" 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 ampsettype. ==> Applications affected: * Shakemap harvester. * SGM import. * Ampgen. ==> Approved. DDL scripts are available in the SVN repository (PI schema). ==> Allan will start using those tables. ----------------------------------------------------------------- ----------------------------------------------------------------- ==> Pete will update the Shakemap harvester. Allan updated ampgen & SGM import. ----------------------------------------------------------------- 2.4.- 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. * Databases: - Remove NOT NULL constraint on Amp.datetime. - Add NOT NULL constraint to Amp.wstart. * 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. - NC will soon add datetime constraint. Wstart constraint cannot be added yet because some values are NULL. ------------------------------------------------------------------------ ------------------------------------------------------------------------ ==> NC added the datetime constraint. Pete checked/updated RT apps. Allan checked/updated PP apps. SGM import apps are waiting for the new version of the SGM packets. ------------------------------------------------------------------------ 2.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. Same operation needs to be performed in NC. - Allan updated Jiggle & the stored procedures. - NC will soon add this new field to its production databases. ------------------------------------------------------------------------ ------------------------------------------------------------------------ ==> NC added the new field to its production databases. Historic data need to be updated in the database. Pete started modifying trimag, EC & TMTS. Shang Lin needs to update STP. Stephane needs to update dbselect. ------------------------------------------------------------------------ 2.6.- 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 - 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. ------------------------------------------------------------------------ 2.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. ------------------------------------------------------------------------