CREATE ROLE globus WITH SUPERUSER LOGIN ENCRYPTED PASSWORD 'foo';

CREATE TABLE gram_audit_table (
    job_grid_id character varying(256) NOT NULL,
    local_job_id character varying(512),
    subject_name character varying(256) NOT NULL,
    username character varying(16) NOT NULL,
    idempotence_id character varying(128),
    creation_time timestamp without time zone NOT NULL,
    queued_time timestamp without time zone,
    stage_in_grid_id character varying(256),
    stage_out_grid_id character varying(256),
    clean_up_grid_id character varying(256),
    globus_toolkit_version character varying(16) NOT NULL,
    resource_manager_type character varying(16) NOT NULL,
    job_description text NOT NULL,
    success_flag boolean NOT NULL,
    finished_flag boolean NOT NULL,
    end_time timestamp without time zone,
    duration real,
    count smallint);

CREATE OR REPLACE FUNCTION update_stellaris() RETURNS trigger AS $update_stellaris$

### Perl: start ###
use strict;
use URI;
use Time::Local;
use Net::hostent;
use HTTP::Request;
use LWP::UserAgent;

my $STELLARIS_URL="http://stellaris.astrogrid-d.org";

if ($_TD->{new}{finished_flag} ne "t" && $_TD->{new}{success_flag} ne "t") {
  my $ua_stellaris = LWP::UserAgent->new();
  my $res_stellaris = $ua_stellaris->get("http://Kerr:Einstein\@mintaka.aip.de:8080/volist/vomembers?print_id=1");
  if ($res_stellaris->content !~ $_TD->{new}{subject_name}) {return;}}

my $t = time(); my @now = localtime($t);
my $now_ = sprintf("%.4d-%.2d-%.2dT%.2d:%.2d:%.2d",
   1900+@now[5],1+@now[4],@now[3],@now[2],@now[1],@now[0]);
my $job_grid_id = URI->new($_TD->{new}{job_grid_id});
my $id = unpack("H*", $job_grid_id->query()); 
my $host = gethost($job_grid_id->host())->name();
my $status = ""; my $count = ""; my $exe = ""; my $end_time = "", my $duration = "";

my $tc = $_TD->{new}{creation_time}; $tc =~ s/ /T/;
my $c_t = $_TD->{new}{creation_time}; $c_t =~ s/-/ /g; $c_t =~ s/:/ /g; my @ct = split(/ /,$c_t);
my $tq = $_TD->{new}{queued_time}; $tq =~ s/ /T/;
if ($_TD->{new}{finished_flag} eq "t" &&
    $_TD->{old}{finished_flag} ne "t" && $_TD->{new}{queued_time} ne "") {
  my $q_t = $_TD->{new}{queued_time};
  $q_t =~ s/-/ /g; $q_t =~ s/:/ /g; my @qt = split(/ /,$q_t);
  my $queued_time = timelocal(@qt[5],@qt[4],@qt[3],@qt[2],@qt[1]-1,@qt[0]-1900);
  $end_time = $now_;
  $duration = sprintf("%.4f",($t-$queued_time)/3600.);
  $_TD->{new}{end_time} = sprintf("%.4d-%.2d-%.2d %.2d:%.2d:%.2d",
    1900+@now[5],1+@now[4],@now[3],@now[2],@now[1],@now[0]);
  $_TD->{new}{duration} = $duration;}
if ( $_TD->{new}{finished_flag} eq "f") {
       if ( $_TD->{new}{queued_time} eq "" ) { $status = "queued"; }
       else { $status = "started"; }}
else { if ($_TD->{new}{success_flag} eq "f") { $status = "failed"; }
       else {$status = "completed"; } }
if (index($_TD->{new}{job_description}, "<ns1:count>") > 0) {
  $count = $_TD->{new}{job_description};
  $count =~ s/.*<ns1:count>//; $count =~ s/<\/ns1:count>.*//;
  $_TD->{new}{count} = $count;}
if (index($_TD->{new}{job_description}, "<ns1:executable>") > 0) {
  $exe = $_TD->{new}{job_description};
  $exe =~ s/.*<ns1:executable>//; $exe =~ s/<\/ns1:executable>.*//;}

my $usage_record = <<"EOF";
<?xml version="1.0"?>
<rdf:RDF xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#">
  <ns1:JobUsageRecord xmlns:ns1="http://www.gridforum.org/2003/ur-wg#" rdf:about="">
    <grddl:transformation xmlns:grddl="http://www.w3.org/2003/g/data-view#">http://www.astrogrid-d.org/xml2rdf.xsl</grddl:transformation>
    <ns1:RecordIdentity>
      <ns1:RecordIdentity rdf:about="#RecordIdentity">
        <urf:recordId xmlns:urf="http://schema.ogf.org/urf/2003/09/urf">$_TD->{new}{job_grid_id}</urf:recordId>
        <urf:createTime xmlns:urf="http://schema.ogf.org/urf/2003/09/urf">$now_</urf:createTime>
      </ns1:RecordIdentity>
    </ns1:RecordIdentity>
    <ns1:JobIdentity>
      <ns1:JobIdentity rdf:about="#JobIdentity">
        <ns1:LocalJobId>$_TD->{new}{local_job_id}</ns1:LocalJobId>
        <ns1:job_grid_id>$_TD->{new}{job_grid_id}</ns1:job_grid_id>
        <ns1:idempotence_id>$_TD->{new}{idempotence_id}</ns1:idempotence_id>
      </ns1:JobIdentity>
    </ns1:JobIdentity>
    <ns1:UserIdentity>
      <ns1:UserIdentity rdf:about="#UserIdentity">
        <ns1:LocalUserId>$_TD->{new}{username}</ns1:LocalUserId>
        <ns1:GlobalUserName>$_TD->{new}{subject_name}</ns1:GlobalUserName>
      </ns1:UserIdentity>
    </ns1:UserIdentity>
    <ns1:JobName>$exe</ns1:JobName>
    <ns1:Status>$status</ns1:Status>
    <ns1:creation_time>$tc</ns1:creation_time>
    <ns1:StartTime>$tq</ns1:StartTime>
    <ns1:EndTime>$end_time</ns1:EndTime>
    <ns1:WallDuration>
      <ns1:WallDuration rdf:about="#WallDuration">
        <ns1:unit>hours</ns1:unit>
        <x2r:value xmlns:x2r="http://www.astrogrid-d.org/2007/08/14-xml2rdf#">$duration</x2r:value>
      </ns1:WallDuration>
    </ns1:WallDuration>
    <ns1:Host>$host</ns1:Host>
    <ns1:Queue>$_TD->{new}{resource_manager_type}</ns1:Queue>
    <ns1:Count>$count</ns1:Count>
    <ns1:Resource>computer</ns1:Resource>
    <ns1:success_flag>$_TD->{new}{success_flag}</ns1:success_flag>
    <ns1:finished_flag>$_TD->{new}{finished_flag}</ns1:finished_flag>
  </ns1:JobUsageRecord>
</rdf:RDF>
EOF

if ($_TD->{new}{local_job_id} eq "") {$usage_record =~ s/<ns1:LocalJobId>.*<\/ns1:LocalJobId>//;}
if ($_TD->{new}{idempotence_id} eq "") {$usage_record =~ s/<na1:idempotence_id>.*<\/ns1:idempotence_id>//;}
if ($_TD->{new}{queued_time} eq "") {$usage_record =~ s/<ns1:StartTime>.*<\/ns1:StartTime>//;}
if ($exe eq "") {$usage_record =~ s/<ns1:JobName>.*<\/ns1:JobName>//;}
#if ($duration eq "") {$usage_record =~ s/<ns1:WallDuration unit="hours">.*<\/ns1:WallDuration>//;}
if ($count eq "") {$usage_record =~ s/<ns1:Count>.*<\/ns1:Count>//;}

my $req = HTTP::Request->new
    ("PUT", "$STELLARIS_URL/UsageRecords/".$host."/".@ct[0]."/".@ct[1]."/".$id."?modification=replace",
     HTTP::Headers->new(Content_Length=>length($usage_record),
                        Content_Type => "application/rdf+xml"),
     $usage_record);
my $ua = LWP::UserAgent->new(); my $res = $ua->request($req);

if ($res->is_error) {
    print STDERR "PUT ERROR: ".$res->status_line.", using POST instead.\n";
    my $req = HTTP::Request->new
        ("POST", "$STELLARIS_URL",
         HTTP::Headers->new(Content_Length=>length($usage_record),
                            Content_Type => "application/rdf+xml",
                            Slug=>"/UsageRecords/".$host."/".@ct[0]."/".@ct[1]."/".$id),
         $usage_record);
    my $ua = LWP::UserAgent->new(); my $res = $ua->request($req);
    if ($res->is_error) { print STDERR "POST ERROR: ".$res->status_line."\n"; }
}


#my $req = HTTP::Request->new("PUT",
# "http://stellaris.astrogrid-d.org/UsageRecords/".$host."/".@ct[0]."/".@ct[1]."/".$id,
#  HTTP::Headers->new(Content_Length => length($usage_record)), $usage_record);
#my $ua = LWP::UserAgent->new(); my $res = $ua->request($req);
#if ($res->is_error) {print STDERR "ERROR:  ".$res->status_line."\n";} 

return "MODIFY";
### Perl: end ###

$update_stellaris$ LANGUAGE plperlu;

CREATE TRIGGER update_stellaris_trig
    BEFORE INSERT OR UPDATE ON gram_audit_table
    FOR EACH ROW EXECUTE PROCEDURE update_stellaris();