Index: /trunk/ippToPsps/perl/cleanup.pl
===================================================================
--- /trunk/ippToPsps/perl/cleanup.pl	(revision 28889)
+++ /trunk/ippToPsps/perl/cleanup.pl	(revision 28889)
@@ -0,0 +1,168 @@
+#!/usr/bin/env perl
+
+use warnings;
+use strict;
+
+use LWP::UserAgent;
+use PS::IPP::Config 1.01 qw( :standard );
+use Getopt::Long qw( GetOptions :config auto_help auto_version gnu_getopt );
+use Pod::Usage qw( pod2usage );
+use XML::LibXML;
+use File::Temp qw(tempfile);
+use DBI;
+use constant DB_SOCKET => '/var/run/mysqld/mysqld.sock';
+
+
+my $save_temps = 0;
+# get user args
+#GetOptions(
+#        'batchid|d=s' => \$batchId,
+#        'odmurl|u=s' => \$odmUrl,
+#        ) or pod2usage( 2 );
+
+#pod2usage( -msg => "Unknown option: @ARGV", -exitval => 2 ) if @ARGV;
+
+# tell off user for not providing proper args
+#pod2usage(
+#        -msg => "\n   Required options:\n\n".
+#        "--batchid <batchid>\n".
+#        "\n   Optional:\n\n".
+#        "--odmurl <URL to ODM eg http://web01.psps.ifa.hawaii.edu/a01/OdmWebService/OdmWebService.asmx/GetBatchStatus>\n".
+#        -exitval => 3
+#        ) unless
+#defined $batchId and
+#defined $odmUrl;
+
+
+process();
+
+#######################################################################################
+# 
+# Loops through all processed exposures and checks against the ODM, then deletes if necessary 
+# 
+########################################################################################
+sub process {
+
+my $ippToPspsDb = connectToDb("ippToPsps", "ippdb01", "ipp", "ipp") or return 0;
+
+    my $query = $ippToPspsDb->prepare(<<SQL);
+
+    SELECT exp_id, batch_id, survey_id 
+        FROM batches 
+        WHERE processed = 1 
+        AND on_datastore = 1;
+SQL
+
+    $query->execute;
+my $batchFilter;
+my $statusFilter = "*";
+my $fromFilter = "2001-01-01";
+my $toFilter = "2099-12-31";
+
+my $odmUrl = "http://web01.psps.ifa.hawaii.edu/a01/OdmWebService/OdmWebService.asmx/GetBatchStatus";
+my $ua = LWP::UserAgent->new;
+my $loadedToOdm;
+my $mergeWorthy;
+my $mergeCompleted;
+$ua->timeout(15);
+$ua->env_proxy;
+
+# loop round exposures
+while (my @row = $query->fetchrow_array()) {
+    my ($expId, $batchId, $surveyType) = @row;
+
+    $batchFilter = sprintf("%s_P2_J%06d*", $surveyType, $batchId);
+
+    my $response = $ua->post($odmUrl,
+            [batchNameFilter => $batchFilter,
+            statusFilter => $statusFilter,
+            fromFilter => $fromFilter,
+            toFilter => $toFilter]
+            );
+
+    # '200' is the 'OK' response
+    if ($response->code != 200) {
+
+        print "Problem connecting to web service for $batchId\n"; 
+        print( "HTTP response status: ".$response->status_line."\n" );
+        next;
+    }
+
+    my ($tempFile, $tempName) = tempfile( "/tmp/ippToPsps_odmXml.XXXX", UNLINK => !$save_temps);
+    print $tempFile $response->content;
+    close($tempFile);
+
+    parseXml($tempName, \$loadedToOdm, \$mergeWorthy, \$mergeCompleted);
+    print "$batchId, $expId = " . $loadedToOdm . " " . $mergeWorthy . " " . $mergeCompleted . "\n";
+    updateDb($ippToPspsDb, $batchId, $expId, $loadedToOdm, $mergeWorthy, $mergeCompleted);
+
+    #if ( $response->content =~ m/MergeWorthy/i) {print "$batchId is MergeWorthy\n";}
+    #else {print "$batchId is NOT MergeWorthy\n";};
+
+}
+
+$ippToPspsDb->disconnect();
+}
+
+#######################################################################################
+#
+# Updates an existing database record 
+#
+#######################################################################################
+sub updateDb {
+    my ($db, $batchId, $expId, $loadedToOdm, $mergeWorthy, $mergeCompleted) = @_;
+
+    my $query = $db->prepare(<<SQL);
+
+    UPDATE batches 
+        SET loaded_to_ODM = $loadedToOdm, merge_worthy = $mergeWorthy, merged = $mergeCompleted 
+        WHERE batch_id = $batchId 
+        AND exp_id = $expId;
+SQL
+
+        $query->execute; # TODO check response of these
+}
+
+######################################################################################y
+# 
+# Connects to a db
+# 
+########################################################################################
+sub connectToDb {
+    my ($dbname,$dbserver,$dbuser,$dbpass) = @_;
+
+
+    my $db = DBI->connect("DBI:mysql:database=${dbname};host=${dbserver};" .
+            "mysql_socket=" . DB_SOCKET(),
+            ${dbuser},${dbpass},
+            { RaiseError => 1, AutoCommit => 1}
+            );
+
+    printf("* Connection to '$dbname' on '$dbserver': %s\n", $db ? "success" : "FAILED ($DBI::errstr)");
+
+    return $db;
+}
+
+######################################################################################y
+# 
+# Parses ODM XML
+# 
+########################################################################################
+sub parseXml {
+    my ($xmlFile, $loadedToOdm, $mergeWorthy, $mergeCompleted) = @_;
+
+    my $parser = XML::LibXML->new;
+    my $doc = $parser->parse_file($xmlFile);
+    my $xc = XML::LibXML::XPathContext->new($doc);
+    $xc->registerNs('ArrayOfOdmBatchState', 'PanSTARRS.Services.OdmWebService');
+    my $result = $xc->findvalue('//ArrayOfOdmBatchState:Message');
+
+    ${$loadedToOdm} = 0;
+    ${$mergeWorthy} = 0;
+    ${$mergeCompleted} = 0;
+
+    if ($result =~ m/LoadStarted/) { ${$loadedToOdm} = 1;}
+    if ($result =~ m/MergeWorthy/) { ${$loadedToOdm} = 1; ${$mergeWorthy} = 1;}
+    if ($result =~ m/Merge[1-9]Completed/) { ${$loadedToOdm} = 1; ${$mergeWorthy} = 1; ${$mergeCompleted} = 1;}
+}
+
Index: /trunk/ippToPsps/perl/convertPhotCodesToXml.pl
===================================================================
--- /trunk/ippToPsps/perl/convertPhotCodesToXml.pl	(revision 28889)
+++ /trunk/ippToPsps/perl/convertPhotCodesToXml.pl	(revision 28889)
@@ -0,0 +1,85 @@
+#!/usr/bin/perl -w
+
+use warnings;
+use strict;
+use IPC::Cmd 0.36 qw( can_run run );
+use XML::Writer;
+use Getopt::Long qw( GetOptions :config auto_help auto_version gnu_getopt );
+use Pod::Usage qw( pod2usage );
+
+my $inputPath = undef;
+my $outputPath = "photcodes.xml";
+
+# get user args
+GetOptions(
+        'input|i=s' => \$inputPath,
+        ) or pod2usage( 2 );
+
+pod2usage( -msg => "Unknown option: @ARGV", -exitval => 2 ) if @ARGV;
+
+# tell off user for not providing proper args
+pod2usage(
+        -msg => "\n   Required options:\n\n".
+        "--input <path to dvo.photcodes files>\n".
+        -exitval => 3
+        ) unless
+defined $inputPath;
+
+
+
+my $output = new IO::File(">$outputPath");
+my $writer = new XML::Writer(OUTPUT => $output, DATA_MODE => 1, DATA_INDENT=>2);
+$writer->xmlDecl('UTF-8');
+
+$writer->startTag('table', "name" => "PhotoCal");
+
+open (PHOTCODES, $inputPath);
+my $photCode;
+my $zeroPoint;
+my $filter;
+my $description;
+
+while (<PHOTCODES>) {
+
+    chomp;
+
+    if ($_ =~ m/\s+([0-9]+)\s+GPC1.*/) {
+
+        my @columns = split(/\s+/, $_);
+
+        my $filter = $columns[11];
+        my $description = $columns[2];
+        my $photCode = $columns[1];
+        my $zeroPoint = $columns[4];
+        my $extinction = $columns[5];
+
+        $writer->startTag('row',
+                "photoCalID" => $photCode,
+                "filterID" => $filter,
+                "photoCodeDesc" => $description,
+                "AB" => "0.0", # TODO
+                "zeropoint" => $zeroPoint,
+                "extinction" => $extinction,
+                "colorterm" => "0.0", # TODO
+                "colorExtn" => "0.0", # TODO
+                "orphanCalColor" => "0.0", # TODO
+                "orphanCalColorErr" => "0.0", # TODO
+                "startDate" => "54000.");
+
+        $writer->endTag();
+
+
+
+    }
+}
+
+$writer->endTag();
+
+
+close PHOTCODES;
+
+# finish up XML
+#$writer->endTag();
+$writer->end();
+
+
Index: /trunk/ippToPsps/perl/pspsSchema2xml.pl
===================================================================
--- /trunk/ippToPsps/perl/pspsSchema2xml.pl	(revision 28889)
+++ /trunk/ippToPsps/perl/pspsSchema2xml.pl	(revision 28889)
@@ -0,0 +1,337 @@
+#!/usr/bin/perl -w
+
+#######################################################################################
+#
+# Script that searches a dir containing PSPS schema files, finds those that comtain the 
+# tables on interest then parses them into an XML format. Also generates C-header files 
+# containing enums that detail table column names and numbers.
+#
+#######################################################################################
+
+use warnings;
+use strict;
+use IPC::Cmd 0.36 qw( can_run run );
+use XML::Writer;
+use Getopt::Long qw( GetOptions :config auto_help auto_version gnu_getopt );
+use Pod::Usage qw( pod2usage );
+
+my $schemaPath = undef;
+my $type = undef;
+
+# get user args
+GetOptions(
+        'schema|s=s' => \$schemaPath,
+        'type|t=s' => \$type,
+        ) or pod2usage( 2 );
+
+pod2usage( -msg => "Unknown option: @ARGV", -exitval => 2 ) if @ARGV;
+
+# tell off user for not providing proper args
+pod2usage(
+        -msg => "\n   Required options:\n\n".
+        "--schema <path to PSPS schema>\n".
+        "--type <init|det|diff|stack|object>\n".
+        -exitval => 3
+        ) unless
+defined $schemaPath and
+defined $type;
+
+if ($type ne "init" && $type ne "det" && $type ne "diff" && $type ne "stack" && $type ne "object" ) {
+
+    print "Don't understand type '$type'\n"; 
+    die;
+}
+
+my $enumsHeader = "ippToPsps".ucfirst($type)."Enums";
+open(OUT, ">".$enumsHeader.".h") or die("Error");
+
+print OUT "#ifndef ".uc($enumsHeader)."_H\n";
+print OUT "#define ".uc($enumsHeader)."_H\n\n";
+
+
+my $output = new IO::File(">tables.xml");
+my $writer = new XML::Writer(OUTPUT => $output, DATA_MODE => 1, DATA_INDENT=>2);
+$writer->xmlDecl('UTF-8');
+#    $writer->doctype('manifest', "", "psps-manifest.dtd");
+$writer->startTag('tableDescriptions', "type" => "$type");
+
+if ($type eq "init") {createInit();}
+elsif ($type eq "det") {createDetections();}
+elsif ($type eq "diff") {createDiffs();}
+elsif ($type eq "stack") {createStacks();}
+elsif ($type eq "object") {createObjects();}
+
+# finish up XML
+$writer->endTag();
+$writer->end();
+
+print OUT "\n#endif";
+close OUT;
+
+
+#######################################################################################
+#
+# Finds the schema file containing the table name passed in
+#
+#######################################################################################
+sub findSchemaFile {
+    my ($tableName) = @_;
+
+    opendir(DIR, $schemaPath) or print "Cannot open '$schemaPath'\n" and return "";
+    my @files= readdir(DIR);
+    closedir(DIR);
+
+    foreach my $f (@files) {
+
+        if ($f =~ m/.*\.sql$/) {
+            #print "....$f\n";
+
+            open FILE, "<$schemaPath/$f" or next;
+
+            while (<FILE>) {
+                if ($_ =~ m/.*CREATE TABLE\s+dbo\.$tableName\s*\(/i) {
+
+                    close (FILE);
+                    return "$schemaPath/$f";
+                }
+            }
+
+            close (FILE);
+
+        }
+    }
+
+    print "Could not find table '$tableName'\n";
+    return "";
+}
+
+#######################################################################################
+#
+# Creates detection batch tables
+#
+#######################################################################################
+sub createInit {
+
+    parseTable("Filter");
+    parseTable("FitModel");
+    parseTable("PhotozRecipe");
+    parseTable("Survey");
+    parseTable("CameraConfig");
+    parseTable("PhotoCal");
+    parseTable("SkyCell");
+    parseTable("ProjectionCell");
+    parseTable("Region");
+    parseTable("StackType");
+    parseTable("ImageFlags");
+    parseTable("DetectionFlags");
+}
+
+#######################################################################################
+#
+# Creates initialisation batch tables
+#
+#######################################################################################
+sub createDetections {
+
+    parseTable("FrameMeta");
+    parseTable("ImageMeta");
+    parseTable("Detection");
+    parseTable("SkinnyObject");
+    parseTable("ObjectCalColor");
+}
+
+#######################################################################################
+#
+# Creates difference batch tables
+#
+#######################################################################################
+sub createDiffs {
+
+    parseTable("StackMeta");
+    parseTable("StackToImage");
+    parseTable("StackLowSigDelta");
+    parseTable("StackHighSigDelta");
+    parseTable("ObjectCalColor");
+}
+
+#######################################################################################
+#
+# Creates stack batch tables
+#
+#######################################################################################
+sub createStacks {
+
+    parseTable("StackMeta");
+    parseTable("StackDetection");
+    parseTable("SkinnyObject");
+    parseTable("StackOrphan");
+    parseTable("ObjectCalColor");
+}
+
+#######################################################################################
+#
+# Creates object batch tables
+#
+#######################################################################################
+sub createObjects {
+
+    parseTable("Object");
+}
+
+#######################################################################################
+#
+# Parses a particular table from the SQL file and converts it to an XML description 
+#
+#######################################################################################
+sub parseTable {
+    my ($tableName, $newName) = @_;
+
+    my $path =  findSchemaFile($tableName);
+
+    if ($path eq "") {return;}
+
+    # sort out table name, either same as in schema or as defined
+    my $tableNameOut; 
+    if ($newName) { $tableNameOut = $newName;}
+    else {$tableNameOut = $tableName}
+
+    print OUT "\ntypedef enum {\n";
+    $writer->startTag('table', "name" => $tableNameOut);
+
+    open (SCHEMA, $path);
+
+    my $reading = 0;
+    my $found = 0;
+    my $colNum = 0;
+    my $table;
+
+    while (<SCHEMA>) {
+        chomp;
+
+        if ($_ =~ m/.*CREATE TABLE\s+dbo\.([a-zA-Z0-9.]+)\s*\(/i) {
+
+            $table = $1;
+
+            if ($table eq $tableName) {
+
+                $reading = 1;
+                $found = 1;
+                $colNum = 0;
+                next;
+            }
+        }
+
+        if($reading && $_ =~ m/^\s*\)\s*/) {$reading = 0;}
+
+        if(!$reading) {next;}
+
+        my $line = $_;
+        $line =~ s/[\s]*$//;
+
+        if (!$line) {next;}
+        if (length($line) < 5) {next;}
+        if ($line =~ m/^\s*--/) {next;}
+        if ($line =~ m/\/\*/) {next;}
+        if ($line =~ m/\*\//) {next;}
+
+        $colNum = processLine($line, $tableName, $colNum);
+
+    }
+
+    if (!$found) {print "Could not find table '$tableName'\n";}
+    $writer->endTag();
+    print OUT "} ".$tableNameOut.";\n";
+
+    close SCHEMA;
+}
+
+#######################################################################################
+#
+# Processes a line from the PSPS schema table description and converts to XML
+#
+#######################################################################################
+sub processLine {
+    my ($line, $tableName, $colNum) = @_;
+
+    my $name;
+    my $typeStr;
+    my $type;
+    my $comment;
+    my $default;
+
+    # parse line
+    if ($line =~ m/\s*([a-zA-Z0-9()-\[\]]+)\s+(.*)\s*--\/(.*)/) {
+
+        $name = $1;
+        $typeStr = $2;
+        $comment = $3;
+
+        # neaten up the comment
+        $comment =~ s/<[\/]{0,1}column>//g;
+        $comment =~ s/^[\s]*//;
+        $comment =~ s/[\s]*$//;
+        if ($comment =~ m/<column unit="(.*)">(.*)/ ) {$comment = "$2 (unit = $1)"}
+    }
+    # no comment case
+    elsif ($line =~ m/\s*([a-zA-Z0-9()-\[\]]+)\s+(.*)/) {
+
+        $name = $1;
+        $typeStr = $2;
+        $comment = "No comment";
+    }
+    else {
+
+        print "In '$tableName', can't process: '$line'\n";
+        return $colNum
+    }
+
+    my $ISBINARY = 0;
+
+    # remove [] from name
+    $name =~ s/[\[\]]//g;
+
+    # sort out type
+    if ($typeStr =~ m/([a-zA-Z0-9()-]*)[ \t]+.*/) {
+
+        $type = $1; 
+        $type =~ s/BIGINT/TLONGLONG/;
+        $type =~ s/SMALLINT/TSHORT/;
+        $type =~ s/TINYINT/TBYTE/;
+        $type =~ s/INT/TLONG/;
+        $type =~ s/FLOAT/TDOUBLE/;
+        $type =~ s/REAL/TFLOAT/;
+        $type =~ s/DATE/TSTRING/;
+        if ($type =~ m/^VARCHAR.*/) {$type = "TSTRING";}
+        if ($type =~ m/^VARBINARY.*/) {$type = "TSTRING"; $ISBINARY = 1;}
+    }
+
+    # get default value
+    if ($typeStr =~ m/.*DEFAULT[ \t]*(.*)/i) {
+
+        $default = $1;
+        $default =~ s/,//;
+            $default =~ s/[\(\)]//g;
+        $default =~ s/[\s]*$//;
+        $default =~ s/'//g;
+        if ($type ne "TSTRING" && !$default) {$default = "0";}
+        if ($ISBINARY && $default eq "0x") {$default = "";}
+    }
+    elsif ($type eq "TSTRING") {$default = " ";}
+    else {$default = 0;}
+
+    if ($type eq "TSTRING" && $default eq "") {$default = " ";}
+
+    $colNum++;
+
+    print OUT "  ".uc($tableName)."_".uc($name)." = ".$colNum.",\n";
+
+    $writer->startTag('column',
+            "name" => $name,
+            "type" => $type,
+            "default" => $default,
+            "comment" => $comment);
+
+    $writer->endTag();
+
+    return $colNum;
+}