#!/nfs/acis/a1/perl/bin/perl
# later incantation needed on Suns to get Perl 5.10.0 for DBI
# -*- perl -*-
#-----------------------------------------------------------------------
# Module Name: $Source: /Users/goeke/programming/perl/crater/RCS/SData,v $
# Purpose: Receive CCSDS packets and strip out data
# Language: Perl 5
# Assumptions: Consistent with Data ICD, 32-02001
# Part Number:
# Author: Robert F. Goeke (goeke@space.mit.edu
# References:
#-----------------------------------------------------------------------
########################################################################
# Initial Constants
########################################################################
$Version = '$Id: SData,v 2.6 2009/06/16 18:42:57 goeke Exp goeke $';
# require 5.8.0; # need 5.8.0 or above for DBI.pm
use Time::Local; # do /not/ use Time::gmtime, it breaks gmtime ???
use POSIX; # needed for strftime()
use DBI;
########## A few of the more popular signals the program might receive
$SIG{'HUP'} = 'Cleanup';
$SIG{'INT'} = 'Cleanup';
$SIG{'QUIT'} = 'Cleanup';
$SIG{'KILL'} = 'Cleanup';
$SIG{'USR1'} = 'Cleanup';
$SIG{'USR2'} = 'Cleanup';
$Epoch = timegm(0,0,0,1,0,2001); # Epoch for LRO MET
$LeapSec = 2; # occured 1/1/06 and 1/1/09
# NB Perl time() function does not include leap seconds
$Epoch += $LeapSec;
$Orbit = "113"; # Nominal orbital time in minutes
########## SOC Data Base constants
$DBHOST = 'crater-science.bu.edu';
$DB = 'crater';
$DBUSER = 'crater';
$DBPASS = 'crater';
$PRIMARY = "L2_pri";
$SECONDARY = "L2_sec";
$HOUSE = "L2_hk";
########## SOC Primary Science field names
@Primary = ( "Seconds",
"Fract",
"Time",
"Ampl1",
"Ampl2",
"Ampl3",
"Ampl4",
"Ampl5",
"Ampl6",
"Energy1",
"Energy2",
"Energy3",
"Energy4",
"Energy5",
"Energy6",
"LET1",
"LET2",
"LET3",
"LET4",
"LET5",
"LET6",
"DQI",
"Flags1",
"Flags2",
"Flags3",
"Flags4",
"Flags5",
"Flags6",
"Flags7",
"Flags8",
"Flags9",
"Flags10",
"Flags11",
"Flags12",
"Flags13",
"Flags14",
"Flags15",
"Flags16",
"Flags17",
"Flags18",
"Flags19",
"Flags20",
"Flags21",
"Flags22",
"Flags23",
"Flags24",
"Flags25",
"Flags26",
"Flags27",
"Flags28",
"Flags29",
"Flags30",
"Flags31",
"Flags32"
);
# "Index",
########## SOC Secondary Science field names
@Secondary = ( "Seconds",
"Fract",
"Time",
"BiasCmd",
"CalLow",
"CalHigh",
"CalRate",
"ProcDFlag1",
"ProcDFlag2",
"ProcDFlag3",
"ProcDFlag4",
"ProcDFlag5",
"ProcDFlag6",
"LastCmd",
"LastValue",
"DiscThin",
"DiscThick",
"Mask1",
"Mask2",
"Single1",
"Single2",
"Single3",
"Single4",
"Single5",
"Single6",
"Good",
"Reject",
"Total",
"MoonVec1",
"MoonVec2",
"MoonVec3",
"SunVec1",
"SunVec2",
"SunVec3",
"CraterVec1",
"CraterVec2",
"CraterVec3",
"Altitude",
"Nadir",
"GSEVec1",
"GSEVec2",
"GSEVec3",
"GSMVec1",
"GSMVec2",
"GSMVec3",
"Latitude",
"Longitude"
);
# "BiasCntrl",
########## SOC Housekeeping Science field names
@House = ( "Seconds",
"Fract",
"Time",
"V5digital",
"V5plus",
"V5neg",
"BiasCurrent1",
"BiasCurrent2",
"BiasCurrent3",
"BiasCurrent4",
"BiasCurrent5",
"BiasCurrent6",
"BiasVoltThin",
"BiasVoltThick",
"CalAmp",
"LLDThin",
"LLDThick",
"Ttelescope",
"Tanalog",
"Tdigital",
"Tpower",
"Tref",
"RadHighSens",
"RadMedSens",
"RadLowSens",
"Radtotal",
"BiasEnergy1",
"BiasEnergy2",
"BiasEnergy3",
"BiasEnergy4",
"BiasEnergy5",
"BiasEnergy6"
);
# "I28bus", "P28bus", "V28bus", "VAnalog_Err", "FPGA_SN", "Tprt", "Purge",
########################################################################
# The actual program
########################################################################
&Setup;
&Connect;
&Fetch;
&Cleanup;
exit 0;
########################################################################
# Close the data pipe
# NB that all the external signals come here
########################################################################
sub Cleanup {
&Diag("Signal @_ received") if (defined(@_));
$STH->finish() if (defined($STH));
$DBH->disconnect() if (defined($DBH));
&Diag("MySQL Disconnect");
exit 0;
}
########################################################################
# Connect to the SOC database
# "$DBH" is the database file handle
########################################################################
sub Connect {
my $connectid = "dbi:mysql:${DB};${DBHOST}";
$DBH = DBI->connect($connectid,$DBUSER,$DBPASS);
if (defined($DBH)) {
&Diag("MySQL Connection made");
} else {
print STDERR "Connection to $DBHOST Failed $!\n";
exit 2;
}
}
########################################################################
# Diagnotic print statements
########################################################################
sub Diag {
print STDERR "# @_\n" if ($Verbose);
}
########################################################################
# Get the data from the database
# For CSV,TSV stuff, just do what the customer wants
# For RTLM formats, force acquisition of seconds
########################################################################
sub Fetch {
my ($query,$select);
if ($Rtlm) {
if ($FieldNames[0] !~ /Seconds/) {
unshift(@FieldNames,"Time");
unshift(@FieldNames,"Fract");
unshift(@FieldNames,"Seconds");
}
}
$select = $FieldNames[0];
for ($ii=1 ; $ii<=$#FieldNames ; $ii++) {
$select .= ", " . $FieldNames[$ii];
}
printf STDERR "
#############
# Field names requested from SOC data base:
# $select
#############
" if $Verbose;
# The syntax of MySQL requires that time be quoted, but not the modifiers
if (defined($Start)) {
if (defined($Stop)) { # defined both absolute start and stop times
$query = "SELECT $select FROM $Table WHERE time >= \"$Start\" AND time <= \"$Stop\" ";
&Diag("Start time: $Start Stop time: $Stop");
} else { # start defined, but no stop
$query = "SELECT $select FROM $Table WHERE time >= \"$Start\" AND time <= \"$Start\" + $Delta";
&Diag("Start time: $Start Stop Time: $Start + $Delta");
}
} elsif (defined($Stop)) { # no start defined, but we do have stop
$query = "SELECT $select FROM $Table WHERE time >= \"$Stop\" - $Delta AND time <= \"$Stop\" ";
&Diag("Start time: $Stop - $Delta Stop Time: $Stop");
} else { # neither start nor stop defined
$query = "SELECT $select FROM $Table WHERE time >= \"NOW\" - $Delta AND time <= \"NOW\" ";
&Diag("Start time: NOW - $Delta Stop Time: NOW");
}
$STH = $DBH->prepare($query);
$STH->execute();
&Diag("Retrieving data ...");
if ($Rtlm) {
&Format_RTLM;
} else {
&Format_TSV;
}
}
########################################################################
# Format the output for RTLM
# Drop frac and SOC-time, rearrange time into rtlm form
# and then simply use the field names -> rtlm mnemonics
########################################################################
sub Format_RTLM {
my (@row,@time,@date,$pad);
my $last_time = 0;
while ( @row = $STH->fetchrow_array ) {
if ($Table eq $PRIMARY) {
if ($row[0] != $last_time) {
print "### Primary Science\n";
&Format_time($row[0]);
$last_time = $row[0];
}
print "EventAmp\t$row[3]\t$row[4]\t$row[5]\t$row[6]\t$row[7]\t$row[8]\n";
} else {
print "### Secondary Science\n" if ($Table eq $SECONDARY);
print "### Analog Housekeeping\n" if ($Table eq $HOUSE);
&Format_time($row[0]);
$pad = ($Table eq $SECONDARY) ? "\t" : "\t--\t--\t";
for ($ii=3; $ii<=$#FieldNames; $ii++) {
print "$FieldNames[$ii]$pad$row[$ii]\n" ;
}
}
}
}
########################################################################
# Format the output for RTLM
# If $LRO_Clock defined, use LRO seconds, else YYYY-MM-DD HH:MM:SS as input
# rtlm time format: HH:MM:SS MM/DD/YY
########################################################################
sub Format_time {
if (!defined($LRO_Clock)) {
print "Time\t$_[0]\n";
} else {
printf "Time\t%s\n",&Localtime($_[0]);
# @time = split(' ',$_[2]);
# @date = split(/-/,$time[0]);
# $date[0] =~ s/^20//; # 2009 -> 09
# print "Time\t$time[1] $date[1]/$date[2]/$date[0] UTC\n";
}
# print "SerialNumber\t2\n"; # a fudge while SOC doesn't report it
}
########################################################################
# Format the output for TSV, CSV
########################################################################
sub Format_TSV {
my @row;
############ put row titles in first output line
print "$FieldNames[0]";
for ($ii=1; $FieldNames[$ii]; $ii++) {
print "$Sep$FieldNames[$ii]" ;
}
print "\n";
while ( @row = $STH->fetchrow_array ) {
print "$row[0]";
for ($ii=1; $ii<=$#FieldNames; $ii++) {
print "$Sep$row[$ii]" ;
}
print "\n";
}
}
########################################################################
# Help facility
########################################################################
sub Help {
my $name = $0;
$name =~ s/.*\///; # just the basename, please
if (!$BigHelp) {
print "
usage: $name P|S|H [-c] [-h] [-H] [-o filename] [-r|R|Ru] \\
[-s start_time] [-S stop_time] [-v] \\
[-m name1 name2 ... namej]
";
return if (!$Verbose);
print "
Extracts data from the CRaTER SOC Level 2 data base.
By default, all fields are output, and in Tab-Separated-Value format
Mandatory argument: must be one or more of P, S, H
P primary science
S secondary science
H analog housekeeping
flag -c generates output in Comma-Separated-Value format
-h prints this help message
-H prints available database field names
-i interval over which data is to be extracted (in hours)
-I interval over which data is to be extracted (in orbits) [1]
-m extract only following mnemonics/field names
NB this must be the last argument provided
-o write data to the specified filename [STDOUT]
-r dumps data in \"rtlm -a \| calcurve\" format (LRO seconds)
-R dumps data in \"rtlm -a -t \| calcurve\" format (local)
-Ru dumps data in \"rtlm -a -t -u \| calcurve\" format (UTC)
-s start_time in form \"YYYY-MM-DD HH:MM\" UTC [now-interval]
-S stop_time in form \"YYYY-MM-DD HH:MM\" UTC [now]
-v verbose display
";
print "$Version\n";
} else {
for ($ii=0; $Primary[$ii]; $ii++) {
$Secondary[$ii] = "" if (!defined($Secondary[$ii]));
$House[$ii] = "" if (!defined($House[$ii]));
$MNE1 = $Primary[$ii];
$MNE2 = $Secondary[$ii];
$MNE3 = $House[$ii];
write;
}
exit 0;
}
}
########################################################################
# Form for mnemonic dump to STDOUT
format STDOUT_TOP =
Field Names: Primary Secondary Housekeeping
--------------- --------------- ---------------
.
format STDOUT =
@<<<<<<<<<<<<<< @<<<<<<<<<<<<<< @<<<<<<<<<<<<<<
$MNE1, $MNE2, $MNE3
.
########################################################################
########################################################################
# Convert LRO time to today's version
# $TIME is set in Setup() to be 'gmtime' or 'localtime'
# $TZ is set in Setup() to be 'UTC' or local dst
########################################################################
sub Localtime {
my ($sec,$min,$hour,$mday,$mon,$year) = eval "$TIME($Epoch + $_[0])";
return sprintf("%02d:%02d:%02d %d/%d/%02d %s",
$hour, $min, $sec, $mon+1, $mday, $year-100, $TZ );
}
########################################################################
# Process all the command line arguments
# Open input and output files
# NB that output must be closed and write protected before progam exit!
########################################################################
sub Setup {
my ($bar,$foo,$ii,$filename,$list);
$Verbose = 0;
$TIME = 'localtime'; # default is to use local wall clock
$TZ = 'UTC';
$Sep = "\t"; # default is TSV output
$Delta = "INTERVAL $Orbit MINUTE"; # default delta t for data output
while ( $foo = shift(@ARGV) ) {
if ( $foo =~ /^-[vV]/ ) {
$Verbose++;
next;
}
if ( $foo =~ /^-[cC]/ ) { # CSV output
$Sep = ",";
next;
}
if ( $foo =~ /^-[hH]/ ) {
$BigHelp++ if ( $foo =~ /H/);
$Verbose++; &Help;
exit 0;
}
if ( $foo =~ /^-[iI]/ ) {
if ( !defined($ARGV[0]) ) {
print STDERR "flag $foo requires an argument\n";
&Help;
exit 1;
}
$bar = shift(@ARGV);
if ( $bar =~ /[^0-9.]/ ) {
print STDERR "flag $foo requires a decimal numeric argument\n";
&Help;
exit 1;
}
$Delta = ($foo =~ /^-i/) ?
"INTERVAL $bar * 60 MINUTE" : "INTERVAL $bar * $Orbit MINUTE";
next;
}
if ( $foo =~ /^-[mM]/ ) { # must be the last flag!
if ( !defined($ARGV[0]) ) {
print STDERR "flag $foo requires at least one argument\n";
&Help;
exit 1;
}
if (!defined(@FieldNames)) { # we got here without table select!
print STDERR "Data table P, S, or H not defined\n";
&Help;
exit 3;
}
$ii = 0;
while ( $foo = shift(@ARGV) ) { # build @list
&Verify($list[$ii] = $foo); # exits on error
$ii++;
}
last; # must be last flag and args!
}
if ( $foo =~ /^-[oO]/ ) {
if ( !defined($ARGV[0]) ) {
print STDERR "flag $foo requires an argument\n";
&Help;
exit 1;
}
if (!defined($filename)) {
$filename = shift(@ARGV);
} else {
print STDERR "Only one $foo flag allowed\n";
&Help;
exit 1;
}
next;
}
if ( $foo =~ /^-[rR]/ ) {
$Rtlm++;
$LRO_Clock++ if ( $foo =~ /^-R/);
$LRO_UTC++ if ( $foo =~ /^-Ru/ );
$TIME = (defined($LRO_UTC)) ? 'gmtime' : 'localtime';
$TZ = (defined($LRO_UTC)) ? "UTC" : strftime("%Z",localtime());
next;
}
if ( $foo =~ /^-[sS]/ ) {
if ( !defined($ARGV[0]) ) {
print STDERR "flag $foo requires an argument\n";
&Help;
exit 1;
}
$bar = shift(@ARGV);
$Start = $bar if ($foo =~ /^-s/);
$Stop = $bar if ($foo =~ /^-S/);
if ($bar !~ /^20[0-9]{2}(-[0-9]{1,2}){2}( +[0-9]{1,2}:[0-9]{1,2})?$/) {
print STDERR "Time \"$bar\" provided with $foo flag not in standard format\n";
&Help;
exit 1;
}
next;
}
if ( $foo =~ /^-/ ) {
print STDERR "Unknown flag $foo\n";
&Help;
exit 1;
}
if ( $foo =~ /^P/ && !$Table ) {
$Table = $PRIMARY; @FieldNames = @Primary;
next;
}
if ( $foo =~ /^S/ && !$Table ) {
$Table = $SECONDARY; @FieldNames = @Secondary;
next;
}
if ( $foo =~ /^H/ && !$Table) {
$Table = $HOUSE; @FieldNames = @House;
next;
}
print STDERR "Unknown arguement: $foo\n";
&Help;
exit 3;
}
if (!defined($Table)) {
print STDERR "One of P S H must be chosen as data source\n";
&Help;
exit 4;
} elsif ($Verbose) {
&Diag("Retrieving data from $Table table");
}
# If -m flag was invoked, we have a list, otherwise use default
@FieldNames = @list if (defined(@list));
if ($filename) {
open(DUMP,">$filename") || die "Could not write to $filename\n";
select(DUMP);
&Diag("Using $filename for output");
}
}
########################################################################
# Check given mnemonic/field name with approved list
########################################################################
sub Verify {
for ($ii=0; $ii<=$#FieldNames; $ii++) {
return if ($FieldNames[$ii] eq $_[0])
}
print STDERR "$_[0] not a valid field name; use -H flag for name list";
select(STDOUT);
&Help;
exit 5;
}
exit 0;
########################################################################
# Pod follows
########################################################################
=for html
CRaTER -- SData
=head2 NAME
SData -- Retrieves archived data from the CRaTER SOC L2 data base
=head2 USAGE
SData P|S|H [-c] [-h] [-H] [-o filename] [-r] \
[-s start_time] [-S stop_time] [-v] \
[-m name1 name2 ... namej]
=head2 FLAGS
P primary science
S secondary science
H analog housekeeping
-c generates output in Comma-Separated-Value format
-h prints this help message
-H prints available database field names
-i interval over which data is to be extracted (in decimal hours)
-I interval over which data is to be extracted (in decimal orbits) [1]
-m extract only following mnemonics/field names
NB this must be the last argument provided
-o write data to the specified filename [STDOUT]
-r dumps data in "rtlm -a | calcurve" format (LRO seconds)
-R dumps data in "rtlm -a -t | calcurve" format (local)
-Ru dumps data in "rtlm -a -t -u | calcurve" format (UTC)
-s start_time in form "YYYY-MM-DD HH:MM" UTC [now-interval]
-S stop_time in form "YYYY-MM-DD HH:MM" UTC [now]
-v verbose display
=head2 DESCRIPTION
The program connects to the SOC science MySQL Level 2 data base and extracts
data over the time period requested. The data table -- primary science,
secondary science, or analog housekeeping -- must be specified, but all
other flags are optional. By default all information contained in the
selected table is output in Tab-Separated-Value format. Note that in "data
base" format -- as opposed to the optional "rtlm" formats -- the first line
output contains the field names for the selected data; the values follow
on successive lines.
Absolute start and stop times may be specified. One may also specify only
one of these, together with a data interval given in units of either decimal
hours or decimal lunar orbits (nominally 113 minutes). The default data interval is
a single orbit. If neither start nor stop time is given, the default stop time
is NOW with the start time calculated from the data interval, either commanded or
itself the default value.
The HH:MM information in start/stop times optional, 00:00 is the default.
=head2 MySQL SYNTAX
The following command fragments are provided for those needing manual
interaction with the SQL data base.
Note that case is note significant in SQL
and that all SQL commands must end with a semicolon.
%unix mysql -h crater-science.bu.edu -u crater -pcrater
mysql> use crater;
mysql> show tables; # we have L2_hk, L2_sec, L2_pri
muysql> describe L2_pri
# Seconds, Fract, Time, Index, Ampl1, Ampl2, Ampl3, Ampl4, Ampl5, Ampl6
mysql> describe L2_sec # etc
mysql> describe L2_hk # etc
mysql> select time, ttelescope from L2_hk where month(time) = 21;
mysql> select * from L2_hk where time > "2009-12-16 21:47" - interval 1 hour
mysql> select * from L2_hk where time > "2008-0-0" and time < now
=head2 BUGS
This program requires Perl version 5.8.0 or above with the DBI.pm and DBD.pm
modules available to enable communication with the SOC MySQL.
Not B fields in the SOC tables are retrieved by this program; those
which the author deemed either obsolete or of limited utility have been omitted.
Formatting with the various -r flags is a close, but not exact,
analog to an B output. In particular,
additional information fields defined in the SOC level 2 products are
added to the output.
=head2 SEE ALSO
=head4 High Level programs
mysql
=head4 Low Level programs
rtlm, calcurve
=head2 AUTHOR
Bob Goeke
=head2 RCS Information
$Id: SData,v 2.6 2009/06/16 18:42:57 goeke Exp goeke $
Copyright: Massachusetts Institute of Technology 2009
=cut
########################################################################
# history follows
########################################################################
# $Log: SData,v $
# Revision 2.6 2009/06/16 18:42:57 goeke
# POD enhancements
#
# Revision 2.5 2009/05/08 13:13:00 goeke
# Bug fixes to get time reported correctly in different formats
#
# Revision 2.4 2009/05/06 13:30:44 goeke
# Minor POD edits
#
# Revision 2.3 2009/05/05 20:28:24 goeke
# Mostly documentation changes; note req. to use 5.8.0 or greater for DBI.pm
#
# Revision 2.2 2009/04/30 14:07:49 goeke
# Add -i/I flags
#
# Revision 2.1 2009/04/22 13:04:00 goeke
# Fixed bug in time print in rtlm mode
# Added serial number output as fudge (since SOC isn't reporting it)
# Changed name to SData from o_soc
#
# Revision 1.8 2009/04/21 15:27:47 goeke
# Deleted extra character in output of -r format
#
# Revision 1.7 2009/04/21 15:03:46 goeke
# Default start and stop times have been redone
# Bug fix to quote "now" in sql calls, ditto "time" - interval 1 hour
# Added check to assure P,S,H selected prior to -m checks
# Updated pod
#
# Revision 1.6 2009/04/17 20:05:06 goeke
# Added flags to get rtlm time in local/UTC/LRO_seconds
#
# Revision 1.5 2009/04/17 19:22:36 goeke
# Time, in rtlm format, can be specified in either LRO seconds or UTC
#
# Revision 1.4 2009/04/17 17:48:14 goeke
# rtlm formatting now works
#
# Revision 1.3 2009/04/17 14:43:03 goeke
# The -m flag now works properly; fleshed out pod
#
# Revision 1.2 2009/04/17 12:53:46 goeke
# Just an in-progress bookmark; adding -m flag and field name verification
#
# Revision 1.1 2009/04/16 20:06:58 goeke
# Initial revision
#