#!/usr/bin/env perl #======================================================================= # # NAME # # get_kywd_and_fld_vals.pl # # DESCRIPTION # # Subroutines and global values required to get a keyword list # from a fits file and table values from a database. Both # require information from the keyword database to process them. # # # Subroutines # # GetPrimeKeys - Gets each tables primary keys. # GetMappingTbls - Gets tables which map keyword to field and vv. # GetFileKywdLists - Fills hashes with keywords dredged from fits files. # FillKywdVerHash - Called by GetFileKywdLists to get keywords in # format the script can use. # GetDbFldVals - Get the field values from the database. # ConvertSybToMyDate - Helper routine for GetDbFldVals. # # # HISTORY # # 01/27/08 61112 L Gardner Moved here from catver.pl # 06/22/09 61985 L Gardner Handle multiple kywd tables + all kywds # #======================================================================= use strict; my $sybDatePattern = '^(\w{3})\s([\s\d]\d)\s(\d{4})\s([\s\d]\d):(\d\d):(\d\d):(\d\d\d)([AP]M)$'; # transmission source (last letter of dataset name) posibilities my $tsList = "JSQNPMTORX"; #----------------------------------------------------------------------- # # The keyword database contains the information on how to compare # the keywords to the fields. The hashes listed below must be filled # to process each of the datasets. The hashes are all filled by the # subroutines below and are stored based on mission and instrument at a minimum. # All the information for all missions and instruments are read in so that # we can process many different datasets during one run of this program. # # LIMITATION: Can only process one archive class during one run # of this program since many of the hashes are filled in based on # archive class and there is no way of determining the archive class # from the dataset name. # #----------------------------------------------------------------------- #----------------------------------------------------------------------- # # GetPrimeKeys # Using the keyFields hash create a hash of the table keys in # the correct order as well as a hash of acronyms and a hash # containing non standard fields that make the table unique. # Standard key fields are the dataset name and pso key. # #----------------------------------------------------------------------- sub GetPrimeKeys { my ( $dbh, $archiveClass, $primeKeys, $acronyms, $tableType, $unqFldList, $validInstr, $testFld ) = @_; # The tableType will be MEMBEXP, PRODEXP or ASNTBL. %$tableType = GetTableType( $dbh ); # GetKeyFields inputs the where clause. Process AST like CAL. my $acWhere = "='$archiveClass'"; $acWhere = "in ('CAL','AST')" if $archiveClass eq 'CAL'; my %keyFields = GetKeyFields( $dbh, $acWhere ); foreach my $mission ( sort keys %keyFields ) { foreach my $instr ( sort keys %{$keyFields{$mission}} ) { # validInstr is used in the next subroutine to determine what # goes into the skip list $validInstr->{$mission}{$instr} = 1; foreach my $tblName ( sort keys %{$keyFields{$mission}{$instr}} ) { # Getting a string back with keys sorted in key field order # The order is important for determining the unique field below. # The line below is just sorting on the value portion of # the keyFields hash. The value is the sort order. my $tblKeys = join(",", sort {$keyFields{$mission}{$instr}{$tblName}{$a} <=> $keyFields{$mission}{$instr}{$tblName}{$b}} keys %{$keyFields{$mission}{$instr}{$tblName}}); # The assoc_member should be queried on dataset name alone # since this is what maps to the file name. if ( $tblName eq 'assoc_member' ) { $tblKeys = 'asm_data_set_name'; # SCA: ASM table needs a unique key for stis if ( $mission eq 'HST' and $instr eq 'STIS' ) { $unqFldList->{$mission}{$instr}{asm} = 'asm_obsnum'; } } # Save the table's keys in a hash $primeKeys->{$mission}{$instr}{$tblName} = $tblKeys; # Save each tables acronym, used later in creating the where clause my $acronym = substr($tblKeys,0,3); $acronyms->{$mission}{$instr}{$tblName} = $acronym; # Save table type by acronym in addition to tablename. $tableType->{$mission}{$instr}{$acronym} = $tableType->{$mission}{$instr}{$tblName}; # This is a dads specific kludge. When we generate the where clause # it will be either dataset name, pso key or program_id. Here we # are trying to identify the part of the table key that is not # going to be in the where clause but is needed to treat each # row returned from the table as unique. We need to save this info # for our comparison with the keywords from the file. # # Note: We save this hash based on the acronym and not the table # name since when we do the comparison we only have the # fieldname at that point and can get the acronym from the # fieldname. my @tmp = split( /,/, $tblKeys ); my @unqKeys = (); foreach my $keyFld ( @tmp ) { if ( ( $keyFld !~ /obsnum$/ and $keyFld !~ /obset_id$/ and $keyFld !~ /program_id$/ and $keyFld !~ /data_set_name$/ ) or ( $keyFld =~ /obsnum$/ and $tblKeys =~ /data_set_name/ ) ) { push( @unqKeys, $keyFld ); } } $unqFldList->{$mission}{$instr}{$acronym} = join( ";", @unqKeys ); # SCA: Use sij_idb_obsnum as unique key for sij. if ( $mission eq 'HST' and $instr eq 'STIS' and $acronyms->{$mission}{$instr}{$tblName} eq 'sij') { $unqFldList->{$mission}{$instr}{$acronym} = 'sij_idb_obsnum' } print "GetPrimeKeys:1:$instr,$tblName*", "$acronyms->{$mission}{$instr}{$tblName}*", "$unqFldList->{$mission}{$instr}{$acronym}*", "$tableType->{$mission}{$instr}{$acronym}*", "$primeKeys->{$mission}{$instr}{$tblName}*\n" if $acronyms->{$mission}{$instr}{$tblName} eq substr($testFld,0,3); } } } } #----------------------------------------------------------------------- # # GetMappingTbls # Get the map file information and create two hashes, one based on kywd # and the other based on field name. Finally add to the skip list # any field that does not map to a keyword. # 1/26/09 - Added a keyword list to fill updMapper (also new) # 07/29/10 64432 Gardner/MSwam changed *= to left outer join # #----------------------------------------------------------------------- sub GetMappingTbls { my ( $dbh, $validInstr, $kywdList, $kywdMapper, $updMapper, $fldMapper, $multFldMap, $skipFld, $quoteIt, $testKywd, $testFld ) = @_; my %fldsProcessed = (); # Fields have been processed for some instrument my %multKywdTbls = (); # Tables have any fields populated by mult kywds # Get ALL the fields for tables that are populated by any keyword my $query = <<" EOQ"; select dkw_mission, dkw_real_instrument, dkw_keyword, hda_tablename, hda_fieldname, dkw_special_process, a.hda_datatype from hda_fields a left outer join dads_keywords b on a.hda_fieldname = b.dkw_fieldname where a.hda_dbname = 'catalog' and a.hda_tablename IN ( SELECT dkw_tablename FROM dads_keywords ) EOQ my @dat = GetDbValues( $dbh, $query ); foreach my $row ( @dat ) { my ( $mission, $instr, $kywd, $tblName, $fldName, $specProc, $datatype ) = @$row; # Determine whether datatype needs quoting. if ( $datatype =~ /char/ or $datatype =~ /datetime/ or $datatype =~ /text/ ) { $quoteIt->{$fldName} = 1; } # Field processed for current instrument. Below will use this hash # to populate skip list for any field other instruments do not fill. # Typically this will be fields in the "common" tables, ex: science. $fldsProcessed{$mission}{$fldName} = 1; # If there is no keyword that means this keyword has to go into # the skip list. If this is an instrument specific table then put # it in the skip list for that instrument. If it's not an instrument # specific table then put it in the skip list for all valid instruments. # The valid instrument list was determined in GetPrimeKeys. In any case # if there is no keyword don't continue saving the mapping info. if ( !$kywd ) { my $index = index $tblName, '_'; $instr = uc substr($tblName,0,$index); if ( $validInstr->{$instr} ) { $skipFld->{$mission}{$instr}{$fldName} = 1; } else { foreach my $instr ( %{$validInstr} ) { $skipFld->{$mission}{$instr}{$fldName} = 1; } } next; } print "GetMappingTbls:1:$kywd*$fldName*$specProc*$mission*$instr*", "$tblName*\n" if $kywd eq $testKywd and $fldName eq $testFld; # Save the mapping based on kywd $kywdMapper->{$mission}{$instr}{$kywd}{$fldName} = $specProc; # If a keyword list was supplied save the mapping info in updMapper # This will be used to update certain field in the database from # the fits file but will be ignored by catver. if ( $kywdList->{$kywd} or $kywdList->{"*"} ) { $updMapper->{$mission}{$instr}{$tblName}{$kywd}{$fldName} = 1; } # Fields that are populated by multiple keywords have spec proc = 'OR'. # Store the table acronym for fields should be put in special # hashes not processed by regular keywords. These fields/keywords will # be processed in a separate subroutine. If the spec proc doesn't = 'OR' # then store it in the regular mapper. # # Note: Every field with spec proc = IGNORE has at most one entry # without the ignore. Since we are storing one keyword per field # we will store only the non IGNORE keyword asssociated with field. if ( $specProc eq 'OR' ) { $multKywdTbls{substr($fldName,0,3)} = 1; $multFldMap->{$mission}{$instr}{$fldName}{$kywd} = 1; } else { $fldMapper->{$mission}{$instr}{$fldName} = $kywd if $specProc !~ /^IGNORE/; } } # If a table has any field filled by multiple keywords then all the # fields in that table should be moved to the multiple field mapper # and removed from the normal processing field mapper. The reason for # this is that typically the field filled in by multiple keywords # will be the unique value used below. Since that can change it was # decided that the whole table will be processed a different way. foreach my $mission ( sort keys %{$fldMapper} ) { foreach my $instr ( sort keys %{$fldMapper->{$mission}} ) { foreach my $fldName ( sort keys %{$fldMapper->{$mission}{$instr}} ) { if ( $multKywdTbls{substr($fldName,0,3)} ) { my $kywd = $fldMapper->{$mission}{$instr}{$fldName}; $multFldMap->{$mission}{$instr}{$fldName}{$kywd} = 1; delete $fldMapper->{$mission}{$instr}{$fldName}; } } } } # Sometimes a field is filled in for one instrument but not the others. # This will put the other instrument/fieldname combination in the skip # list so it won't show up as an error. foreach my $mission ( sort keys %fldsProcessed ) { foreach my $fldName ( sort keys %{$fldsProcessed{$mission}} ) { foreach my $instr ( sort keys %{$validInstr} ) { if ( ! $fldMapper->{$mission}{$instr}{$fldName} ) { $skipFld->{$mission}{$instr}{$fldName} = 1; } } } } } #----------------------------------------------------------------------- # # GetFileKywdLists # Open each extension in the file extension list, read it and extract # the keywords from the appropriate sections. # Note: wc = wavecal # #----------------------------------------------------------------------- sub GetFileKywdLists { my ( $mission, $instr, $fileDir, $datasetName, $suffixList, $imsetExtList, $kywdMapper, $extSource, $kywdVerVal, $wcExtSource, $wcKywdVerVal, $testKywd ) = @_; my $extNum = ''; # Extension number processing in file my %kywdExtSrc = (); # List of file extensions for a certain ext num my %kywdExtVal = (); # List of keywords in a certain extension number my %wcKywdExtSrc = (); # List of wc file extensions for a certain ext num my %wcKywdExtVal = (); # List of wc keywords in a certain ext num my $processedFiles = 0; # No files processed yet my $wavecal = 0; # Assume there are no wavecal files # associations and exposures have different sets of file extensions. # Determine the type of files to be opened. my $assoc = $datasetName =~ /[$tsList]$/ ? 'EXP' : 'ASN'; # Sort the extensions by the open order. foreach my $suffix ( sort {$suffixList->{$mission}{$instr}{$assoc}{$a} <=> $suffixList->{$mission}{$instr}{$assoc}{$b}} keys %{$suffixList->{$mission}{$instr}{$assoc}} ) { # Create the file name, skip it if it's not readable. my $inpFileName = "$fileDir/" . lc $datasetName . "_$suffix.fits"; print "GetFileKywdLists:0:$inpFileName*\n" if $testKywd; next if ! -e $inpFileName; # Could read at least one file $processedFiles = 1; # Set wavecal flag if have a wavecal extension $wavecal = 1 if $suffix eq 'wsp' or $suffix eq 'wav' or $suffix eq 'jwf' or $suffix eq 'jwt'; print "GetFileKywdLists:1:$inpFileName*\n" if $testKywd; # Use listhead to read the fits files open LISTHEAD, "-|" or exec 'listhead', $inpFileName, '-s'; while ( my $line = ) { chop $line; # Remove trailing newline # If the line has '---- extension 0 ----' in it then pull out the # extension number. This is done so don't loose valid keywords # that are repeated for each extension when putting in hash. if ( $line =~ /^-/ ) { ( $extNum = $line ) =~ s/\-//g; $extNum =~ s/^ extension (\d+) $/$1/; # SCA: For non 0 extensions we have to do the following # The flt, raw and wav extension files have DQ and ERR extensions # while the other file extensions don't. A keyword should be # saved only once per extension but for the flt, raw and wav the # extension number will be different than those in the other # files even though conceptually it is the same as in the other # files. This means for flt, raw and wav files use an algorithm # to translate the extension number to what it is in the other # files. # # After ext number number 0 the internal extensions # are SCI, DQ and then ERR. Only process the first one and # use -999 to skip the DQ and ERR section keywords. if ( $mission eq 'HST' and $instr eq 'STIS' and $datasetName =~ /0$/ and $extNum != 0 ) { if ( $suffix eq 'flt' or $suffix eq 'raw' or $suffix eq 'wav' ) { if ( ( $extNum % 3 ) == 1 ) { $extNum = ( $extNum + 2 ) / 3; } else { $extNum = -999; } } #print "GetFileKywdLists:1.5:$extNum*\n"; } # COS kludge: To fill the segment keywords you have to pull # the values for each entry from a different file (flt_a & flt_b). # They share the same extensions so flt_b keywords are not # extracted. To get around this I change their extension # number so that they will be saved and used in the compare. if ( $mission eq 'HST' and $instr eq 'COS' and $suffix =~ /_b$/ ) { $extNum += 1000; } next; } # SCA: Skip STIS asns DQ and ERR extensions for flt, raw & wav files next if $extNum == -999; # Skip lines that aren't in the form 'keyword = value' next if $line !~ /[\w\-]+\s*=/; next if $line =~ /^HISTORY\s+\w/; # Get the keyword and value, removing leading and trailing spaces my ( $kywd, $value ) = split( /= /, $line, 2 ); $kywd =~ s/\s+$//; # Strip off spaces and quotes $value =~ s/''/"/g; # Change '' to " (for next step) $value =~ s/'([^']+)'.*/$1/; # Get things between single quotes $value =~ s/"/'/g; # Put single quote back $value =~ s/ \/ .*$//; # Strip off comments starting with / $value =~ s/^\s+//; # Remove leading spaces $value =~ s/\s+$//; # Remove trailing spaces # Empty values should be replaces with ~ to avoid a perl false (''). $value = '~' if $value eq ''; # Save the upper cased value in a hash only the first time per # extension and save the source of the file. # # SCA: wavecals will be saved in it's own hash if ( $suffix eq 'wav' or $suffix eq 'wsp' or $suffix eq 'jwf' or $suffix eq 'jwt' ) { if ( not defined $wcKywdExtVal{$extNum}{$kywd} ) { $wcKywdExtSrc{$extNum}{$kywd} = $suffix; $wcKywdExtVal{$extNum}{$kywd} = uc $value; print "GetFileKywdLists:2a:$suffix*$extNum*$kywd*", "$wcKywdExtVal{$extNum}{$kywd}*\n" if $kywd =~ /$testKywd/; } } else { if ( not defined $kywdExtVal{$extNum}{$kywd} ) { $kywdExtSrc{$extNum}{$kywd} = $suffix; $kywdExtVal{$extNum}{$kywd} = uc $value; print "GetFileKywdLists:2b:$suffix*$extNum*", "$kywdExtVal{$extNum}{EXTNAME}*$kywd*", "$kywdExtVal{$extNum}{$kywd}*\n" if $kywd =~ /$testKywd/; } } } # Close the input file close LISTHEAD; # COS Kludge: an extension num can map to different ext names # Remove all extnums that map to extension names we don't care about. # Ignore the first extension since it doesn't have an ext name and we never # want to delete it. I also had to add another check that you can't # delete an extension if it came from a different file. if ( $mission eq 'HST' and $instr eq 'COS' ) { foreach my $extNum ( sort keys %kywdExtVal ) { if ( $extNum != 0 and $extNum != 1000 and !$imsetExtList->{$instr}{$suffix}{$kywdExtVal{$extNum}{EXTNAME}} and $kywdExtSrc{$extNum}{EXTNAME} eq $suffix ) { delete $kywdExtVal{$extNum}; print "GetFileKywdLists:3:$instr*$suffix*", "$kywdExtVal{$extNum}{EXTNAME}*$extNum*", "$imsetExtList->{$instr}{$suffix}{$kywdExtVal{$extNum}{EXTNAME}}\n" if $testKywd; } } } } # There is a hash based on the extension number. What is needed is a # hash based on the extension version. Create hashes corresponding # to the one's above but based on the extension version. FillKywdVerHash( $mission, $instr, $datasetName, $kywdMapper, $imsetExtList, \%kywdExtSrc, \%kywdExtVal, $extSource, $kywdVerVal, $testKywd ); # SCA: Same as above but for stis wavecals. if ( $wavecal ) { FillKywdVerHash( $mission, $instr, $datasetName, $kywdMapper, $imsetExtList, \%wcKywdExtSrc, \%wcKywdExtVal, $wcExtSource, $wcKywdVerVal, $testKywd ); } return ( $processedFiles, $wavecal ); } #----------------------------------------------------------------------- # # FillKywdVerHash # Open each extension in the file extension list and create a new # hash based on extension version which is what maps to to a database # record. # Note: In this subroutine we create a bunch of bogus pso keys # since the pso key is only defined in the primary header (extver=0). # pso key = program_id, obset_id and obsnum field combination. # #----------------------------------------------------------------------- sub FillKywdVerHash { my ( $mission, $instr, $datasetName, $kywdMapper, $imsetExtList, $kywdExtSrc, $kywdExtVal, $extSource, $kywdVerVal, $testKywd ) = @_; # The primary pso key should map to the dataset name. Sometimes # the 0 extension does not have to pso key so create a bogus one. if ( $mission eq 'HST' ) { $kywdExtVal->{0}{PROGRMID} = substr($datasetName,1,3); $kywdExtVal->{0}{OBSET_ID} = substr($datasetName,4,2); $kywdExtVal->{0}{OBSERVTN} = substr($datasetName,6,3); $kywdExtVal->{0}{OBSERVTN} =~ s/[$tsList]$//; } # Really want a hash based on extension version if it exists. Couldn't # get it before since the version keyword could be way down in the file. # Now have all the keywords so can get it. So... loop through # kywdExtVal based on a numerical sort of the extension numbers. foreach my $extNum ( sort { $a <=> $b } keys %{$kywdExtVal} ) { # Skip any extension number that is not a science keyword except the first # The first ext is the primary header and should always be processed. print "FillKywdVerHash:0:$extNum*$kywdExtVal->{$extNum}{EXTNAME}*", "$kywdExtSrc->{$extNum}{EXTNAME}*", "$imsetExtList->{$instr}{$kywdExtSrc->{$extNum}{EXTNAME}}{$kywdExtVal->{$extNum}{EXTNAME}}*", "$kywdExtVal->{$extNum}{$testKywd}*\n" if $testKywd; # The three keys into this hash is instr, suffix and extension name next if !$imsetExtList->{$instr}{$kywdExtSrc->{$extNum}{EXTNAME}}{$kywdExtVal->{$extNum}{EXTNAME}} and $extNum != 0 and $extNum != 1000; # Non zero extensions usually don't have a pso key so if it's not # there create it from the EXPNAME keyword if ( $mission eq 'HST' and !$kywdExtVal->{$extNum}{OBSERVTN} and $kywdExtVal->{$extNum}{EXPNAME} ) { $kywdExtVal->{$extNum}{PROGRMID} = substr($kywdExtVal->{$extNum}{EXPNAME},1,3); $kywdExtVal->{$extNum}{OBSET_ID} = substr($kywdExtVal->{$extNum}{EXPNAME},4,2); $kywdExtVal->{$extNum}{OBSERVTN} = substr($kywdExtVal->{$extNum}{EXPNAME},6,3); $kywdExtVal->{$extNum}{OBSERVTN} =~ s/[$tsList]$//; $kywdExtSrc->{$extNum}{PROGRMID} = $kywdExtSrc->{$extNum}{EXPNAME}; $kywdExtSrc->{$extNum}{OBSET_ID} = $kywdExtSrc->{$extNum}{EXPNAME}; $kywdExtSrc->{$extNum}{OBSERVTN} = $kywdExtSrc->{$extNum}{EXPNAME}; } # WFC3/IR does not have a CCDCHIP keyword and since it's part of # the key for wfc3_chip it causes the comparison between the table # and the file to fail for all keywords in the chip. Make it zero # for this config. if ( $mission eq 'HST' and $kywdExtVal->{0}{CONFIG} eq 'WFC3/IR' ) { $kywdExtVal->{$extNum}{CCDCHIP} = 0; } foreach my $kywd ( sort keys %{$kywdExtVal->{$extNum}} ) { # Loop through kywdExtVal and create new hash based on the # version number. If the EXTVER keyword does not exist then # default a zero to be the value. This should be outside this # foreach loop but for some reason it sometimes gets corrupted there. my $extVer = $kywdExtVal->{$extNum}{EXTVER} || '0'; # COS kludge: for data coming from *_b ext add 1000 to version num. $extVer += 1000 if $mission eq 'HST' and $instr eq 'COS' and $kywdExtSrc->{$extNum}{$kywd} =~ /_b$/; print "FillKywdVerHash:1:$extNum*$kywd*$extVer*", "$kywdExtVal->{$extNum}{$kywd}*", "$kywdExtSrc->{$extNum}{$kywd}*", "$kywdVerVal->{$extVer}{$kywd}*", "$kywdMapper->{$mission}{$instr}{$kywd}*\n" if $kywd eq $testKywd; # Don't save the keyword if it's been saved for this EXTVER. # or unless it's a keyword mapped to a field. # Note: need ORIGIN keyword for upd_flds_from_kywds.pl. next if defined $kywdVerVal->{$extVer}{$kywd} or ( not defined $kywdMapper->{$mission}{$instr}{$kywd} and $kywd ne 'ORIGIN' ); # Save the first occurance of this version's value and source file $kywdVerVal->{$extVer}{$kywd} = $kywdExtVal->{$extNum}{$kywd}; $extSource->{$extVer}{$kywd} = $kywdExtSrc->{$extNum}{$kywd}; print "FillKywdVerHash:2:saved\n" if $kywd eq $testKywd; } } } #----------------------------------------------------------------------- # # GetDbFldVals # Foreach table that this dataset should be in get the fieldnames and # values and store them in a hash. # #----------------------------------------------------------------------- sub GetDbFldVals { my ( $dbh, $mission, $instr, $datasetName, $archiveClass, $asnId, $acronyms, $primeKeys, $skipFld, $unqFldList, $tableType, $fldRowVal, $testFld ) = @_; foreach my $tblName ( sort keys %{$primeKeys->{$mission}{$instr}} ) { # If there are no table keys skip this table. next if !$primeKeys->{$mission}{$instr}{$tblName}; my $acronym = $acronyms->{$mission}{$instr}{$tblName}; # Generating the where clause. my $whereClause = GenWhereClause( $mission, $instr, $datasetName, $archiveClass, $asnId, $acronym, $tblName, $primeKeys->{$mission}{$instr}{$tblName}, $tableType->{$mission}{$instr}{$tblName}, $testFld ); # This contains the correct select and where clause my $query = "select * from $tblName " . $whereClause; print "GetDbFldVals:1:$query\n" if $acronym eq substr($testFld,0,3); # Run the query for the table. my $sth = $dbh->prepare($query) or die "Can't prepare query:" . $dbh->errstr; $sth->execute or die "Can't execute query:" . $dbh->errstr; # Loop through each row from the table and save fieldname/value pairs. while ( my $tblInfo = $sth->fetchrow_hashref ) { # Want the value of the field that makes this table unique so # can use this value as a hash key in saving the fieldname/value pair. # If it's null that is ok too. my $uniqueVal = $tblInfo->{$unqFldList->{$mission}{$instr}{$acronym}}; if ( $unqFldList->{$mission}{$instr}{$acronym} =~ /;/ ) { my @keyFlds = split(/;/,$unqFldList->{$mission}{$instr}{$acronym}); my @keyVals = (); foreach my $keyFld ( @keyFlds ) { push( @keyVals, $tblInfo->{$keyFld} ); } $uniqueVal = join( ";", @keyVals ); } $uniqueVal =~ s/\s+$//; print "GetDbFldList:2:$acronym*", "$unqFldList->{$mission}{$instr}{$acronym}*$uniqueVal*\n" if $acronym eq substr($testFld,0,3); # Process each field in the table foreach my $fldName ( sort keys %$tblInfo ) { print "GetDbFldVals:3:$fldName*", "$skipFld->{$mission}{$instr}{$fldName}*\n" if $fldName eq $testFld; # Don't process fields not filled by keywords next if $skipFld->{$mission}{$instr}{$fldName}; # Don't store idb fields for associations accept STIS next if $mission eq 'HST' and $instr ne 'STIS' and $datasetName =~ /[0-9A-I]$/ and $fldName =~ /^sij_idb/; # Don't store sdb fields for exposures that are members of assoc next if $datasetName =~ /[$tsList]$/ and $asnId and $fldName =~ /^sij_sdb/; # Get the field value and remove trailing spaces. We change # null fields to ~ since perl treats '' as false. my $fldVal = $tblInfo->{$fldName}; $fldVal =~ s/\s+$//; $fldVal = '~' if $fldVal eq ''; $fldVal = ConvertSybToMyDate($fldVal) if $fldVal =~ /$sybDatePattern/; # Save each field name and value pair. uniqueVal will be # different for each row returned from the database for mult rows. $fldRowVal->{$fldName}{$uniqueVal} = $fldVal; print "GetDbFldVals:4:$fldName*$uniqueVal*$fldVal*\n" if $fldName eq $testFld; } } } } #----------------------------------------------------------------------- # # GenWhereClause # Generates a where clause # #----------------------------------------------------------------------- sub GenWhereClause { my ( $mission, $instr, $datasetName, $archiveClass, $asnId, $acronym, $tblName, $tblKeys, $tblType, $testFld ) = @_; my $whereClause = ''; my @keyList = split( /,/, $tblKeys ); #print "GenWhereClause:1:$acronym:$tblName:$tblKeys:$tblType:\n" if $testFld; foreach my $fldName ( @keyList ) { # Don't use idb fields for associations. Have to exclude # STIS here because want to use sij_idb_data_set_name next if $mission eq 'HST' and $instr ne 'STIS' and $datasetName =~ /[0-9A-I]$/ and $fldName =~ /^sij_idb/; # Don't use sdb fields for exposures next if $datasetName =~ /[$tsList]$/ and $fldName =~ /^sij_sdb/; # SCA: Don't use the pso key at all for STIS or OMS - use dataset name next if $mission eq 'HST' and ( $instr eq 'STIS' or $instr eq 'OMS' ) and $tblType eq 'MEMBEXP' and $tblName ne 'sci_inst_db_join' and ( $fldName =~ /program_id$/ or $fldName =~ /obset_id$/ or $fldName =~ /obsnum$/ ); # SCA: Only use sdb part part for query next if $mission eq 'HST' and $instr eq 'STIS' and $tblName eq 'sci_inst_db_join' and $fldName =~ /^sij_idb/; #print "GenWhereClause:2:$tblName:$fldName:\n" if $testFld; # Build the where clause clause based on the keys if ( $fldName =~ /program_id/ ) { my $ppp = substr($datasetName,1,3); $whereClause .= " and $fldName = '$ppp'"; } elsif ( $fldName =~ /obset_id/ ) { my $ss = substr($datasetName,4,2); $whereClause .= " and $fldName = '$ss'"; } elsif ( $fldName =~ /obsnum/ ) { my $ooo = substr($datasetName,6,3); $ooo =~ s/[$tsList]$//; $whereClause .= " and $fldName = '$ooo'"; } elsif ( $fldName =~ /data_set_name/ ) { $whereClause .= " and $fldName like '$datasetName'"; } elsif ( $fldName =~ /asn_id/ ) { my $asnId = $datasetName; $asnId =~ s/\w$/0/; $whereClause .= " and $fldName like '$asnId'"; } elsif ( $fldName =~ /archive_class/ ) { $whereClause .= " and $fldName = '$archiveClass'"; } else { print "Unknown field type:$tblName..$fldName\n" if $testFld; } } # SCA: Hard code the where clause for sci_inst_db_join if ( $mission eq 'HST' and $instr eq 'STIS' and $tblName eq 'sci_inst_db_join' ) { $whereClause = " and sij_idb_data_set_name = '$datasetName'"; } #print "GenWhereClause:3:$tblName:$whereClause:\n" if $testFld; if ( $whereClause ) { # The first 'and' should be replaced with a 'where' $whereClause =~ s/^ and /where /; # Pull out of ADS best version for class of data we want if ( $tblName eq 'archive_data_set_all' ) { $whereClause .= " and ads_archive_class = '$archiveClass'" . " and ads_best_version = 'Y'"; } } $whereClause; } #----------------------------------------------------------------------- # # ConvertSybToMyDate # Takes a sybase formated date and converts it to the internal # date format. # #----------------------------------------------------------------------- sub ConvertSybToMyDate { my ( $date ) = @_; my $newDate = $date; # Convert Dates - If the field contains a sybase date then convert # it to a the programs date. Truncate the date to 100ths of seconds $newDate =~ /$sybDatePattern/; my $hour = $4; if ( $8 eq 'PM' ) { $hour += 12 if $hour < 12; } else { $hour = 0 if $hour == 12; } my $month_num = {qw/ Jan 1 Feb 2 Mar 3 Apr 4 May 5 Jun 6 Jul 7 Aug 8 Sep 9 Oct 10 Nov 11 Dec 12 /}->{$1}; return $newDate = sprintf("%02d/%02d/%4d %02d:%02d:%02d.%03d", $month_num, $2, $3, $hour, $5, $6, $7); } 1;