BioChem Data Type Validation

[upsonp]

This issue is to dissect Biochem validation as part of issue #141

This issue is to dissect Biochem Data Type validation as part of issue #144

This is the validation code run by the biochem intake script on the Discrete Data Edits table (BCD). It can be found in the BioChem account on PTran under packages.

FUNCTION DATA_TYPE(data_type_seq_in_par IN BCDATATYPES.data_type_seq%type, method_in_par IN BCDATATYPES.method%type) RETURN VARCHAR2
IS
   count_recs_var   NUMBER:=0;
   return_var       VARCHAR2(1) := 'F';
 BEGIN
  -- Create the cursor (data_type_cur) passing the data_type_seq_in_par and method_in_par input parameter values at execution
  -- time. This cursor select the count of the number of records which meet the criteria (where the datatype_seq and the
  -- method both refer to the same record.
  SELECT count(*) INTO count_recs_var
      FROM BCDATATYPES
      WHERE METHOD = method_in_par
      AND DATA_TYPE_SEQ = data_type_seq_in_par;
   -- If the count_recs_var is greater than 0 then both column values refer to the same datatype. Assign 'T' to the
   -- return_var variable.
   IF count_recs_var > 0 THEN
      return_var := 'T';
   END IF;
   RETURN return_var;
 END DATA_TYPE;

[upsonp]

Pretty self explanatory. The function is querying the BCDataTypes lookup for a row with both the passed in method and data_type sequence. If the method and sequence are mismatched count(*) returns zero and false is returned from the function. If more than zero rows are returned the function returns true and it's good to go.

The issue here though is DART comes with a copy of the BcDataTypes table which is loaded from a fixture file. The fixture file has to be manually updated using functions in the bio_tables.sync_tables package. If someone adds a new data type or updates a data type in the official Biochem database and the biochem fixtures file isn't updated there could be a mismatch for the data type code.

What would be nice is to include a button that, if the user can connect to a biochem database, would update the fixture file, or update a copy of the biochem lookup tables that are kept in a separate local database. Right now the biochem lookup tables are written to the mission database. Which is good in that the mission database is completely self contained, but it does create the potential issue of out-of-date lookup tables.