[ViewVC] Annotation of: cvsroot/UserCode/OSUT3Analysis/DBTools/scripts/osudb

#!/usr/bin/env perl

use strict;
use Mysql;
use Getopt::Long;
use File::Copy;

sub getDataset;
sub dbUpdate;
sub dbUpdateOther;
sub dbDelete;
sub dbDeleteOther;
sub addSlashes;
sub uploadRelease;
sub datasetExists;

our $db = Mysql->connect ("cmshead.mps.ohio-state.edu", "ntuple", "osuT3User") or die "Failed to connect to Tier 3, stopped";

my %opt;
Getopt::Long::Configure ("bundling");
GetOptions (\%opt, "comment|c=s", "format|f=s", "pyConfig|p=s", "crabCfg|b=s", "jsonFile|j=s", "lumiSummary|s=s", "location|l=s", "fileList|t=s", "release|r=s", "other|o", "help|h");
my $argc = @ARGV;

printHelp ($ARGV[0]) if $opt{"help"};
printHelp () if $argc != 2 && $ARGV[0] ne "createOther";
printHelp () if $ARGV[0] ne "create" && $ARGV[0] ne "createOther" && $ARGV[0] ne "update" && $ARGV[0] ne "finish" && $ARGV[0] ne "deprecate" && $ARGV[0] ne "uploadRelease" && $ARGV[0] ne "deleteEntry";
if ($ARGV[0] eq "create" && datasetExists ($ARGV[1]))
  {
    print "Dataset name already exists in the database!\n";
    exit;
  }
if (($ARGV[0] eq "create" || $ARGV[0] eq "finish") && !$opt{"location"})
  {
    print "The directory containing the ntuples must be given!\n";
    exit;
  }
if ($ARGV[0] eq "uploadRelease" && !$opt{"pyConfig"} && !$opt{"release"})
  {
    print "Both the Python and the CMSSW release must be given!\n";
    exit;
  }
if ($ARGV[0] eq "uploadRelease")
  {
    my $parentDir = "./$opt{'release'}";
    $parentDir =~ s/CMSSW_[^\/]*//g;
    $parentDir =~ s/\/\/*/\//g;
    my $cmsswRelease = $opt{'release'};
    $cmsswRelease =~ s/^.*CMSSW_([^\/]*).*$/CMSSW_$1/;
    `tar -C $parentDir -czf $ARGV[1].tar.gz $opt{"release"}`;
    $opt{"format"} = "BEAN" if !$opt{"format"};
    uploadRelease ($opt{"format"}, $cmsswRelease, "$ARGV[1].tar.gz", $opt{"pyConfig"}, $ARGV[1], $ENV{"USER"}, $opt{"comment"});
  }
elsif ($ARGV[0] eq "createOther")
  {
    my @listOfFiles = @ARGV;
    @listOfFiles = reverse (@listOfFiles);
    pop (@listOfFiles);
    @listOfFiles = reverse (@listOfFiles);
    dbOtherCreate ($ENV{"USER"}, \@listOfFiles, $opt{"comment"});
  }
else
  {
    my $id = -1;
    my $fullDataset;
    ($id, $fullDataset) = getDataset ($ARGV[0], $ARGV[1]) if $ARGV[0] ne "create" && ($ARGV[0] ne "update" || !$opt{"other"}) && ($ARGV[0] ne "deleteEntry" || !$opt{"other"});
    $id = $ARGV[1] if ($ARGV[0] eq "update" && $opt{"other"}) || ($ARGV[0] eq "deleteEntry" && $opt{"other"});
    $fullDataset = $ARGV[1] if $id < 0;
    $ARGV[0] = "create" if $id < 0;
    my $status = "present";
    $status = "" if $ARGV[0] eq "update";
    $status = "deprecated" if $ARGV[0] eq "deprecate";
    $opt{"format"} = "BEAN" if $ARGV[0] eq "create" && !$opt{"format"};
    dbUpdate ($id, $fullDataset, $ENV{"USER"}, $opt{"format"}, $opt{"location"}, $opt{"fileList"}, $status, $opt{"comment"}, $opt{"pyConfig"}, $opt{"crabCfg"}, $opt{"jsonFile"}, $opt{"lumiSummary"}, $opt{"release"}) if !$opt{"other"};
    dbUpdateOther ($id, $opt{"comment"}) if $opt{"other"};
    dbDelete ($id) if $ARGV[0] eq "deleteEntry" && !$opt{"other"};
    dbDeleteOther ($id) if $ARGV[0] eq "deleteEntry" && $opt{"other"};
  }

sub
addSlashes
{
  my $string = shift;

  $string =~ s/\\/\\\\/g;
  $string =~ s/'/\\'/g;
  $string =~ s/"/\\"/g;
  $string =~ s/\\0/\\\\0/g;

  return $string;
}

sub
getDataset
{
  my $command = shift;
  my $dataset = shift;

  my $results;
  my $queryDataset = $dataset;
  $queryDataset =~ s/\*/%/g;
  $queryDataset =~ s/(.*)/%$1%/g;
  my $query = "select id,dataset,user,creationTime from ntuple where dataset like '$queryDataset' order by creationTime";
  $db->selectdb ("ntuple");
  $results = $db->query ($query);
  if ($results->numrows () == 1)
    {
      my @row = $results->fetchrow ();
      return ($row[0], $row[1]);
    }
  if ($results->numrows () == 0)
    {
      if ($command ne "deleteEntry" && $command ne "deprecate")
        {
          print "Database entry does not exist. Create it? (Y/n): ";
          my $response = <STDIN>;
          $response =~ s/\n//g;
          $response = "y" if !$response;
          exit if substr (lc ($response), 0, 1) ne 'y';
          return (-1, "");
        }
      else
        {
          print "Database entry does not exist.\n";
          exit;
        }
    }
  my %id;
  my %fullDataset;
  print "Found multiple database entries matching\n";
  print "\"$dataset\":\n";
  print "( 0) new\n" if $command ne "deleteEntry" && $command ne "deprecate";
  for (my $i = 1; $i <= $results->numrows (); $i++)
    {
      my @row = $results->fetchrow ();
      $id{"$i"} = $row[0];
      $fullDataset{"$i"} = $row[1];
      printf "(%2d) $row[1]\n", $i;
      print "     created by $row[2] on $row[3]\n";
    }
  print "\nWhich entry would you like to modify?";
  if ($command ne "deleteEntry" && $command ne "deprecate")
    {
      print " (Select 0 to create a new entry): "
    }
  else
    {
      print ": ";
    }
  my $response = <STDIN>;
  $response =~ s/[ \t\n]//g;
  return (-1, "") if $response == 0 && $command ne "deleteEntry" && $command ne "deprecate";
  if (!(exists $id{$response}))
    {
      print "Your selection \"$response\" was not a valid option! Quitting.\n";
      exit;
    }

  return ($id{$response}, $fullDataset{$response});
}

sub
dbUpdate
{
  my $id = shift;
  my $dataset = shift;
  my $user = shift;
  my $format = shift;
  my $location = shift;
  my $fileListName = shift;
  my $status = shift;
  my $comment = shift;
  my $psetName = shift;
  my $crabCfgName = shift;
  my $jsonFileName = shift;
  my $lumiSummaryName = shift;
  my $release = shift;

  my $fileList;
  my $pset;
  my $crabCfg;
  my $jsonFile;
  my $lumiSummary;
  my $size = 0;
  my $nFiles = 0;
  if ($fileListName)
    {
      if (!(-e $fileListName))
        {
          print "$fileListName does not exist!\n";
          exit;
        }
      open (FILE_LIST, "<$fileListName");
      my @fileList = <FILE_LIST>;
      close (FILE_LIST);
      $fileList = join ("", @fileList);
    }
  if ($psetName)
    {
      if (!(-e $psetName))
        {
          print "$psetName does not exist!\n";
          exit;
        }
      open (PY_CONFIG, "<$psetName");
      my @pset = <PY_CONFIG>;
      close (PY_CONFIG);
      $pset = join ("", @pset);
    }
  if ($crabCfgName)
    {
      if (!(-e $crabCfgName))
        {
          print "$crabCfgName does not exist!\n";
          exit;
        }
      open (CRAB_CONFIG, "<$crabCfgName");
      my @crabCfg = <CRAB_CONFIG>;
      close (CRAB_CONFIG);
      $crabCfg = join ("", @crabCfg);
    }
  if ($jsonFileName)
    {
      if (!(-e $jsonFileName))
        {
          print "$jsonFileName does not exist!\n";
          exit;
        }
      open (JSON_FILE, "<$jsonFileName");
      my @jsonFile = <JSON_FILE>;
      close (JSON_FILE);
      $jsonFile = join ("", @jsonFile);
    }
  if ($lumiSummaryName)
    {
      if (!(-e $lumiSummaryName))
        {
          print "$lumiSummaryName does not exist!\n";
          exit;
        }
      open (LUMI_SUMMARY, "<$lumiSummaryName");
      my @lumiSummary = <LUMI_SUMMARY>;
      close (LUMI_SUMMARY);
      $lumiSummary = join ("", @lumiSummary);
    }
  if ($release)
    {
      my $query = "select id from ntupleRelease where name='$release'";
      $db->selectdb ("ntuple");
      my $results = $db->query ($query);
      if ($results->numrows () != 1)
        {
          print "Ntuple release \"$release\" not found!\n";
          exit;
        }
    }
  ($nFiles, $size) = sizeOfDataset ($dataset, $location, $fileList);
  my $fullLocation = $location;
  $fullLocation = "$ENV{'PWD'}/$location" if !($location =~ m/^\//);

  $dataset = addSlashes ($dataset);
  $user = addSlashes ($user);
  $format = addSlashes ($format);
  $pset = addSlashes ($pset);
  $crabCfg = addSlashes ($crabCfg);
  $jsonFile = addSlashes ($jsonFile);
  $lumiSummary = addSlashes ($lumiSummary);
  $fullLocation = addSlashes ($fullLocation);
  $fileList = addSlashes ($fileList);
  $status = addSlashes ($status);
  $comment = addSlashes ($comment);
  $release = addSlashes ($release);

  my $results;
  my $query;
  if ($id < 0)
    {
      $query = "select max(id) from ntuple";
      $db->selectdb ("ntuple");
      $results = $db->query ($query);
      my @row = $results->fetchrow ();
      my $id = 1;
      $id = $row[0] + 1 if $results->numrows ();

      $query = "insert into ntuple (id, dataset, creationTime, lastUpdateTime, user, format, location, fileList, nFiles, sizeInGB, status, comment, pset, crabCfg, jsonFile, lumiSummary, version) values ($id, '$dataset', now(), now(), '$user', '$format', '$fullLocation', '$fileList', $nFiles, $size, '$status', '$comment', '$pset', '$crabCfg', '$jsonFile', '$lumiSummary', '$release')";
    }
  if ($id > 0)
    {
      my $fields;
      my $values;

      $values .= ", dataset='$dataset'" if $dataset;
      $values .= ", lastUpdateTime=now()";
      $values .= ", format='$format'" if $format;
      $values .= ", location='$fullLocation'" if $location;
      $values .= ", fileList='$fileList'" if $fileList;
      $values .= ", nFiles=$nFiles" if $nFiles;
      $values .= ", sizeInGB=$size" if $size;
      $values .= ", status='$status'" if $status;
      $values .= ", comment='$comment'" if $comment;
      $values .= ", pset='$pset'" if $pset;
      $values .= ", crabCfg='$crabCfg'" if $crabCfg;
      $values .= ", jsonFile='$jsonFile'" if $jsonFile;
      $values .= ", lumiSummary='$lumiSummary'" if $lumiSummary;
      $values .= ", version='$release'" if $release;

      $values =~ s/^, //;
      $query = "update ntuple set $values where id=$id";
    }
  $results = $db->query ($query);

  return $results;
}

sub
dbUpdateOther
{
  my $id = shift;
  my $comment = shift;

  $comment = addSlashes ($comment);

  my $query = "update other set lastUpdateTime=now(), comment='$comment'  where id=$id";
  $db->selectdb ("ntuple");
  my $results = $db->query ($query);

  return $results;
}

sub
dbDelete
{
  my $id = shift;

  my $query = "delete from ntuple where id=$id";
  $db->selectdb ("ntuple");
  my $results = $db->query ($query);
}

sub
dbDeleteOther
{
  my $id = shift;

  my $query = "delete from other where id=$id";
  $db->selectdb ("ntuple");
  my $results = $db->query ($query);
}

sub
printHelp
{
  my $command = shift;

  my $exeName = $0;
  $exeName =~ s/^.*\/([^\/]*)$/$1/;

  if ($command eq "create")
    {
      print "Usage: $exeName -l DIRECTORY [OPTION]... create NAME\n";
      print "Creates an entry in the database for dataset NAME. The ntuples are assumed to\n";
      print "be present on the Tier 3, with the status being set accordingly.\n";
      print "\n";
      print "Mandatory arguments to long options are mandatory for short options too.\n";
      printf "%-29s%s\n", "  -b, --crabCfg FILE", "CRAB config used to submit ntuple jobs";
      printf "%-29s%s\n", "  -c, --comment COMMENT", "comment for the database entry";
      printf "%-29s%s\n", "  -f, --format FORMAT", "ntuple format (default: BEAN)";
      printf "%-29s%s\n", "  -j, --jsonFile FILE", "JSON file used for this dataset";
      printf "%-29s%s\n", "  -l, --location DIRECTORY", "directory containing the ntuples";
      printf "%-29s%s\n", "  -p, --pyConfig FILE", "Python config used to produce ntuples";
      printf "%-29s%s\n", "  -r, --release NAME", "ntuple release used to produce ntuples";
      printf "%-29s%s\n", "  -s, --lumiSummary FILE", "lumiSummary.json reported by CRAB";
    }
  elsif ($command eq "createOther")
    {
      print "Usage: $exeName [OPTION]... createOther DIRECTORIES_AND_FILES\n";
      print "Creates an entry in the database for non-ntuple data.\n";
      print "\n";
      print "Mandatory arguments to long options are mandatory for short options too.\n";
      printf "%-29s%s\n", "  -c, --comment COMMENT", "comment for the database entry";
    }
  elsif ($command eq "update")
    {
      print "Usage: $exeName [OPTION]... update NAME\n";
      print "Updates an existing database entry for dataset NAME.\n";
      print "\n";
      print "Mandatory arguments to long options are mandatory for short options too.\n";
      printf "%-29s%s\n", "  -b, --crabCfg FILE", "CRAB config used to submit ntuple jobs";
      printf "%-29s%s\n", "  -c, --comment COMMENT", "comment for the database entry";
      printf "%-29s%s\n", "  -f, --format FORMAT", "ntuple format";
      printf "%-29s%s\n", "  -j, --jsonFile FILE", "JSON file used for this dataset";
      printf "%-29s%s\n", "  -l, --location DIRECTORY", "directory containing the ntuples";
      printf "%-29s%s\n", "  -o, --other", "update an entry in the non-ntuple database";
      printf "%-29s%s\n", "  -p, --pyConfig FILE", "Python config used to produce ntuples";
      printf "%-29s%s\n", "  -r, --release NAME", "ntuple release used to produce ntuples";
      printf "%-29s%s\n", "  -s, --lumiSummary FILE", "lumiSummary.json reported by CRAB";
    }
  elsif ($command eq "finish")
    {
      print "Usage: $exeName -l DIRECTORY finish NAME\n";

      print "Finalizes the database entry for dataset NAME, changing its status to\n";
      print "\"present\".  This is intended to be the final step in command-line based ntuple\n";
      print "production.\n";
      print "\n";
      print "Mandatory arguments to long options are mandatory for short options too.\n";
      printf "%-29s%s\n", "  -l, --location DIRECTORY", "directory containing the ntuples";
    }
  elsif ($command eq "deleteEntry")
    {
      print "Usage: $exeName [OPTION]... deleteEntry NAME\n";
      print "Deletes the database entry for dataset NAME. This is intended primarily for\n";
      print "mistaken database entries. If you wish to actually delete a set of ntuples,\n";
      print "please use the \"deprecate\" command instead.\n";
      print "\n";
      printf "%-29s%s\n", "  -o, --other", "update an entry in the non-ntuple database";
    }
  elsif ($command eq "deprecate")
    {
      print "Usage: $exeName deprecate NAME\n";
      print "Marks the dataset NAME for deletion. WARNING: The dataset will be deleted from\n";
      print "the Tier 3 within one week of this action. If you wish to simply delete a\n";
      print "mistaken database entry, use the \"deleteEntry\" command instead.\n";
    }
  elsif ($command eq "uploadRelease")
    {
      print "Usage: $exeName -p FILE -r DIRECTORY [OPTION]... uploadRelease NAME\n";
      print "Copies an ntuple release to the appropriate area on the Tier 3, and creates a\n";
      print "database entry for it, with NAME being the name of the release. An ntuple\n";
      print "release is a CMSSW release with all the necessary packages added for creating\n";
      print "ntuples, along with a default Python config file.\n";
      print "\n";
      print "Mandatory arguments to long options are mandatory for short options too.\n";
      printf "%-29s%s\n", "  -c, --comment COMMENT", "comment for the database entry";
      printf "%-29s%s\n", "  -f, --format FORMAT", "ntuple format (default: BEAN)";
      printf "%-29s%s\n", "  -p, --pyConfig FILE", "default Python config for the release";
      printf "%-29s%s\n", "  -r, --release DIRECTORY", "CMSSW release containing ntuple packages";
    }
  else
    {
      print "Usage: $exeName [OPTION]... COMMAND NAME\n";
      print "Manipulates entry in the OSU Tier 3 ntuple database given by NAME.\n";
      print "\n";
      print "Mandatory arguments to long options are mandatory for short options too.\n";
      printf "%-29s%s\n", "  -h, --help", "print help. If COMMAND is present, print help";
      printf "%-29s%s\n", " ", "specific to COMMAND.";
      print "\n";
      print "COMMAND may be one of the following:\n";
      printf "%-29s%s\n", "  create", "creates the entry";
      printf "%-29s%s\n", "  create", "creates an entry for non-ntuple data";
      printf "%-29s%s\n", "  update", "updates the entry";
      printf "%-29s%s\n", "  finish", "finalizes the database entry";
      printf "%-29s%s\n", "  deleteEntry", "removes the database entry";
      printf "%-29s%s\n", "  deprecate", "marks the dataset for deletion";
      printf "%-29s%s\n", "  uploadRelease", "upload an ntuple release";
    }

  exit;
}

sub
sizeOfDataset
{
  my $dataset = shift;
  my $location = shift;
  my $fileList = shift;

  my $size = 0.0;
  my $nFiles = 0;
  if ($location && !$fileList)
    {
      if (!(-e $location))
        {
          print "$location does not exist!\n";
          exit;
        }
      $nFiles = `ls $location | wc -l`;
      $size = `du -s $location`;
      $size =~ s/([^ ]*) .*/$1/;
      $size /= 1024 * 1024;
      $size = sprintf "%.2f", $size;
    }
  elsif ($fileList)
    {
      foreach my $file (split (/\n/, $fileList))
        {
          $nFiles++;
          my $fileSize = `du -s $file`;
          $fileSize =~ s/([^ ]*) .*/$1/;
          $size += $fileSize;
        }
      $size /= 1024 * 1024;
      $size = sprintf "%.2f", $size;
    }

  return ($nFiles, $size);
}

sub
uploadRelease
{
  my $format = shift;
  my $cmsswRelease = shift;
  my $release = shift;
  my $psetName = shift;
  my $name = shift;
  my $user = shift;
  my $comment = shift;

  move ($release, "/home/hart/public_html/releases/$name.tar.gz") or die "Ntuple releases may only be uploaded on the Tier 3, stopped";
  if (!(-e $psetName))
    {
      print "$psetName does not exist!\n";
      exit;
    }
  open (PY_CONFIG, "<$psetName");
  my @pset = <PY_CONFIG>;
  close (PY_CONFIG);
  my $pset = join ("", @pset);

  my $query = "select max(id) from ntupleRelease";
  $db->selectdb ("ntuple");
  my $results = $db->query ($query);
  my @row = $results->fetchrow ();
  my $id = 1;
  $id = $row[0] + 1 if $results->numrows ();

  $name = addSlashes ($name);
  $pset = addSlashes ($pset);
  $user = addSlashes ($user);
  $format = addSlashes ($format);
  $cmsswRelease = addSlashes ($cmsswRelease);
  $comment = addSlashes ($comment);

  my $query = "insert into ntupleRelease (id, name, pset, user, pending, format, cmsswRelease, comment) values ($id, '$name', '$pset', '$user', 1, '$format', '$cmsswRelease', '$comment')";
  $db->selectdb ("ntuple");
  my $results = $db->query ($query);
}

sub
datasetExists
{
  my $dataset = shift;

  my $query = "select id from ntuple where dataset='$dataset'";
  $db->selectdb ("ntuple");
  my $results = $db->query ($query);

  return $results->numrows ();
}

sub
dbOtherCreate
{
  my $user = shift;
  my $listOfFiles = shift;
  my $comment = shift;

  my $size = 0.0;
  my $nFiles = 0.0;
  for (my $i = 0; $i < @$listOfFiles; $i++)
    {
      if (!(-e $$listOfFiles[$i]))
        {
          print "$$listOfFiles[$i] does not exist!\n";
          exit;
        }
      $nFiles += `ls -R $$listOfFiles[$i] | grep -v ':\$' | grep -v '^\$' | wc -l`;
      my $fileSize = `du -s $$listOfFiles[$i]`;
      $fileSize =~ s/([^ ]*) .*/$1/;
      $fileSize /= 1024 * 1024;
      $size += $fileSize;
      $$listOfFiles[$i] = "$ENV{'PWD'}/$$listOfFiles[$i]" if !($$listOfFiles[$i] =~ m/^\//);
    }
  $size = sprintf "%.2f", $size;
  my $location = join ("<br />", @$listOfFiles);

  my $query = "select max(id) from other";
  $db->selectdb ("ntuple");
  my $results = $db->query ($query);
  my @row = $results->fetchrow ();
  my $id = 1;
  $id = $row[0] + 1 if $results->numrows ();

  $user = addSlashes ($user);
  $location = addSlashes ($location);
  $comment = addSlashes ($comment);

  my $query = "insert into other (id, creationTime, lastUpdateTime, user, location, nFiles, sizeInGB, comment) values ($id, now(), now(), '$user', '$location', $nFiles, $size, '$comment')";
  $db->selectdb ("ntuple");
  my $results = $db->query ($query);

  print "ID $id\n";
}
Revision:	1.8
Committed:	Thu Aug 9 06:40:08 2012 UTC (12 years, 8 months ago) by ahart
Branch:	MAIN
Changes since 1.7:	+132 -21 lines
Log Message:	Check if dataset name exists before creating an entry, and add functionality for non-ntuple database entries.
#	User	Rev	Content
1	ahart	1.1	#!/usr/bin/env perl
2
3			use strict;
4			use Mysql;
5			use Getopt::Long;
6	ahart	1.4	use File::Copy;
7	ahart	1.1
8			sub getDataset;
9			sub dbUpdate;
10	ahart	1.8	sub dbUpdateOther;
11	ahart	1.4	sub dbDelete;
12	ahart	1.8	sub dbDeleteOther;
13	ahart	1.1	sub addSlashes;
14	ahart	1.4	sub uploadRelease;
15	ahart	1.8	sub datasetExists;
16	ahart	1.1
17	ahart	1.4	our $db = Mysql->connect ("cmshead.mps.ohio-state.edu", "ntuple", "osuT3User") or die "Failed to connect to Tier 3, stopped";
18	ahart	1.1
19			my %opt;
20			Getopt::Long::Configure ("bundling");
21	ahart	1.8	GetOptions (\%opt, "comment\|c=s", "format\|f=s", "pyConfig\|p=s", "crabCfg\|b=s", "jsonFile\|j=s", "lumiSummary\|s=s", "location\|l=s", "fileList\|t=s", "release\|r=s", "other\|o", "help\|h");
22	ahart	1.1	my $argc = @ARGV;
23
24	ahart	1.4	printHelp ($ARGV[0]) if $opt{"help"};
25	ahart	1.8	printHelp () if $argc != 2 && $ARGV[0] ne "createOther";
26			printHelp () if $ARGV[0] ne "create" && $ARGV[0] ne "createOther" && $ARGV[0] ne "update" && $ARGV[0] ne "finish" && $ARGV[0] ne "deprecate" && $ARGV[0] ne "uploadRelease" && $ARGV[0] ne "deleteEntry";
27			if ($ARGV[0] eq "create" && datasetExists ($ARGV[1]))
28			{
29			print "Dataset name already exists in the database!\n";
30			exit;
31			}
32	ahart	1.4	if (($ARGV[0] eq "create" \|\| $ARGV[0] eq "finish") && !$opt{"location"})
33			{
34			print "The directory containing the ntuples must be given!\n";
35			exit;
36			}
37			if ($ARGV[0] eq "uploadRelease" && !$opt{"pyConfig"} && !$opt{"release"})
38			{
39			print "Both the Python and the CMSSW release must be given!\n";
40			exit;
41			}
42	ahart	1.8	if ($ARGV[0] eq "uploadRelease")
43			{
44			my $parentDir = "./$opt{'release'}";
45			$parentDir =~ s/CMSSW_[^\/]*//g;
46			$parentDir =~ s/\/\/*/\//g;
47			my $cmsswRelease = $opt{'release'};
48			$cmsswRelease =~ s/^.CMSSW_([^\/]).*$/CMSSW_$1/;
49			`tar -C $parentDir -czf $ARGV[1].tar.gz $opt{"release"}`;
50			$opt{"format"} = "BEAN" if !$opt{"format"};
51			uploadRelease ($opt{"format"}, $cmsswRelease, "$ARGV[1].tar.gz", $opt{"pyConfig"}, $ARGV[1], $ENV{"USER"}, $opt{"comment"});
52			}
53			elsif ($ARGV[0] eq "createOther")
54			{
55			my @listOfFiles = @ARGV;
56			@listOfFiles = reverse (@listOfFiles);
57			pop (@listOfFiles);
58			@listOfFiles = reverse (@listOfFiles);
59			dbOtherCreate ($ENV{"USER"}, \@listOfFiles, $opt{"comment"});
60			}
61			else
62	ahart	1.4	{
63			my $id = -1;
64	ahart	1.5	my $fullDataset;
65	ahart	1.8	($id, $fullDataset) = getDataset ($ARGV[0], $ARGV[1]) if $ARGV[0] ne "create" && ($ARGV[0] ne "update" \|\| !$opt{"other"}) && ($ARGV[0] ne "deleteEntry" \|\| !$opt{"other"});
66			$id = $ARGV[1] if ($ARGV[0] eq "update" && $opt{"other"}) \|\| ($ARGV[0] eq "deleteEntry" && $opt{"other"});
67	ahart	1.5	$fullDataset = $ARGV[1] if $id < 0;
68	ahart	1.4	$ARGV[0] = "create" if $id < 0;
69			my $status = "present";
70			$status = "" if $ARGV[0] eq "update";
71			$status = "deprecated" if $ARGV[0] eq "deprecate";
72			$opt{"format"} = "BEAN" if $ARGV[0] eq "create" && !$opt{"format"};
73	ahart	1.8	dbUpdate ($id, $fullDataset, $ENV{"USER"}, $opt{"format"}, $opt{"location"}, $opt{"fileList"}, $status, $opt{"comment"}, $opt{"pyConfig"}, $opt{"crabCfg"}, $opt{"jsonFile"}, $opt{"lumiSummary"}, $opt{"release"}) if !$opt{"other"};
74			dbUpdateOther ($id, $opt{"comment"}) if $opt{"other"};
75			dbDelete ($id) if $ARGV[0] eq "deleteEntry" && !$opt{"other"};
76			dbDeleteOther ($id) if $ARGV[0] eq "deleteEntry" && $opt{"other"};
77	ahart	1.4	}
78	ahart	1.1
79			sub
80			addSlashes
81	ahart	1.2	{
82	ahart	1.1	my $string = shift;
83
84			$string =~ s/\\/\\\\/g;
85			$string =~ s/'/\\'/g;
86			$string =~ s/"/\\"/g;
87			$string =~ s/\\0/\\\\0/g;
88
89			return $string;
90			}
91
92			sub
93			getDataset
94			{
95	ahart	1.4	my $command = shift;
96	ahart	1.1	my $dataset = shift;
97
98			my $results;
99	ahart	1.3	my $queryDataset = $dataset;
100			$queryDataset =~ s/\*/%/g;
101			$queryDataset =~ s/(.*)/%$1%/g;
102	ahart	1.8	my $query = "select id,dataset,user,creationTime from ntuple where dataset like '$queryDataset' order by creationTime";
103	ahart	1.1	$db->selectdb ("ntuple");
104			$results = $db->query ($query);
105			if ($results->numrows () == 1)
106			{
107			my @row = $results->fetchrow ();
108	ahart	1.5	return ($row[0], $row[1]);
109	ahart	1.1	}
110			if ($results->numrows () == 0)
111			{
112	ahart	1.4	if ($command ne "deleteEntry" && $command ne "deprecate")
113			{
114			print "Database entry does not exist. Create it? (Y/n): ";
115			my $response = <STDIN>;
116			$response =~ s/\n//g;
117			$response = "y" if !$response;
118			exit if substr (lc ($response), 0, 1) ne 'y';
119	ahart	1.5	return (-1, "");
120	ahart	1.4	}
121			else
122			{
123			print "Database entry does not exist.\n";
124			exit;
125			}
126	ahart	1.1	}
127	ahart	1.5	my %id;
128			my %fullDataset;
129	ahart	1.1	print "Found multiple database entries matching\n";
130			print "\"$dataset\":\n";
131	ahart	1.4	print "( 0) new\n" if $command ne "deleteEntry" && $command ne "deprecate";
132	ahart	1.1	for (my $i = 1; $i <= $results->numrows (); $i++)
133			{
134			my @row = $results->fetchrow ();
135	ahart	1.5	$id{"$i"} = $row[0];
136			$fullDataset{"$i"} = $row[1];
137	ahart	1.3	printf "(%2d) $row[1]\n", $i;
138			print " created by $row[2] on $row[3]\n";
139	ahart	1.1	}
140	ahart	1.4	print "\nWhich entry would you like to modify?";
141			if ($command ne "deleteEntry" && $command ne "deprecate")
142			{
143			print " (Select 0 to create a new entry): "
144			}
145			else
146			{
147			print ": ";
148			}
149	ahart	1.1	my $response = <STDIN>;
150			$response =~ s/[ \t\n]//g;
151	ahart	1.5	return (-1, "") if $response == 0 && $command ne "deleteEntry" && $command ne "deprecate";
152			if (!(exists $id{$response}))
153	ahart	1.1	{
154			print "Your selection \"$response\" was not a valid option! Quitting.\n";
155			exit;
156			}
157
158	ahart	1.5	return ($id{$response}, $fullDataset{$response});
159	ahart	1.1	}
160
161			sub
162			dbUpdate
163			{
164			my $id = shift;
165			my $dataset = shift;
166			my $user = shift;
167			my $format = shift;
168			my $location = shift;
169	ahart	1.4	my $fileListName = shift;
170	ahart	1.1	my $status = shift;
171			my $comment = shift;
172			my $psetName = shift;
173			my $crabCfgName = shift;
174			my $jsonFileName = shift;
175			my $lumiSummaryName = shift;
176	ahart	1.5	my $release = shift;
177	ahart	1.1
178	ahart	1.4	my $fileList;
179	ahart	1.1	my $pset;
180			my $crabCfg;
181			my $jsonFile;
182			my $lumiSummary;
183			my $size = 0;
184			my $nFiles = 0;
185	ahart	1.4	if ($fileListName)
186			{
187			if (!(-e $fileListName))
188			{
189			print "$fileListName does not exist!\n";
190			exit;
191			}
192			open (FILE_LIST, "<$fileListName");
193			my @fileList = <FILE_LIST>;
194			close (FILE_LIST);
195			$fileList = join ("", @fileList);
196			}
197	ahart	1.1	if ($psetName)
198			{
199			if (!(-e $psetName))
200			{
201			print "$psetName does not exist!\n";
202			exit;
203			}
204			open (PY_CONFIG, "<$psetName");
205			my @pset = <PY_CONFIG>;
206			close (PY_CONFIG);
207			$pset = join ("", @pset);
208			}
209			if ($crabCfgName)
210			{
211			if (!(-e $crabCfgName))
212			{
213			print "$crabCfgName does not exist!\n";
214			exit;
215			}
216			open (CRAB_CONFIG, "<$crabCfgName");
217			my @crabCfg = <CRAB_CONFIG>;
218			close (CRAB_CONFIG);
219			$crabCfg = join ("", @crabCfg);
220			}
221			if ($jsonFileName)
222			{
223			if (!(-e $jsonFileName))
224			{
225			print "$jsonFileName does not exist!\n";
226			exit;
227			}
228			open (JSON_FILE, "<$jsonFileName");
229			my @jsonFile = <JSON_FILE>;
230			close (JSON_FILE);
231			$jsonFile = join ("", @jsonFile);
232			}
233			if ($lumiSummaryName)
234			{
235			if (!(-e $lumiSummaryName))
236			{
237			print "$lumiSummaryName does not exist!\n";
238			exit;
239			}
240			open (LUMI_SUMMARY, "<$lumiSummaryName");
241			my @lumiSummary = <LUMI_SUMMARY>;
242			close (LUMI_SUMMARY);
243			$lumiSummary = join ("", @lumiSummary);
244			}
245	ahart	1.5	if ($release)
246			{
247			my $query = "select id from ntupleRelease where name='$release'";
248			$db->selectdb ("ntuple");
249			my $results = $db->query ($query);
250			if ($results->numrows () != 1)
251			{
252			print "Ntuple release \"$release\" not found!\n";
253			exit;
254			}
255			}
256	ahart	1.4	($nFiles, $size) = sizeOfDataset ($dataset, $location, $fileList);
257	ahart	1.7	my $fullLocation = $location;
258			$fullLocation = "$ENV{'PWD'}/$location" if !($location =~ m/^\//);
259	ahart	1.1
260			$dataset = addSlashes ($dataset);
261			$user = addSlashes ($user);
262			$format = addSlashes ($format);
263			$pset = addSlashes ($pset);
264			$crabCfg = addSlashes ($crabCfg);
265			$jsonFile = addSlashes ($jsonFile);
266			$lumiSummary = addSlashes ($lumiSummary);
267	ahart	1.6	$fullLocation = addSlashes ($fullLocation);
268	ahart	1.4	$fileList = addSlashes ($fileList);
269	ahart	1.1	$status = addSlashes ($status);
270			$comment = addSlashes ($comment);
271	ahart	1.5	$release = addSlashes ($release);
272	ahart	1.1
273			my $results;
274			my $query;
275			if ($id < 0)
276			{
277			$query = "select max(id) from ntuple";
278			$db->selectdb ("ntuple");
279			$results = $db->query ($query);
280			my @row = $results->fetchrow ();
281			my $id = 1;
282			$id = $row[0] + 1 if $results->numrows ();
283
284	ahart	1.6	$query = "insert into ntuple (id, dataset, creationTime, lastUpdateTime, user, format, location, fileList, nFiles, sizeInGB, status, comment, pset, crabCfg, jsonFile, lumiSummary, version) values ($id, '$dataset', now(), now(), '$user', '$format', '$fullLocation', '$fileList', $nFiles, $size, '$status', '$comment', '$pset', '$crabCfg', '$jsonFile', '$lumiSummary', '$release')";
285	ahart	1.1	}
286			if ($id > 0)
287			{
288			my $fields;
289			my $values;
290
291			$values .= ", dataset='$dataset'" if $dataset;
292			$values .= ", lastUpdateTime=now()";
293			$values .= ", format='$format'" if $format;
294	ahart	1.6	$values .= ", location='$fullLocation'" if $location;
295	ahart	1.4	$values .= ", fileList='$fileList'" if $fileList;
296			$values .= ", nFiles=$nFiles" if $nFiles;
297			$values .= ", sizeInGB=$size" if $size;
298	ahart	1.1	$values .= ", status='$status'" if $status;
299			$values .= ", comment='$comment'" if $comment;
300			$values .= ", pset='$pset'" if $pset;
301			$values .= ", crabCfg='$crabCfg'" if $crabCfg;
302			$values .= ", jsonFile='$jsonFile'" if $jsonFile;
303			$values .= ", lumiSummary='$lumiSummary'" if $lumiSummary;
304	ahart	1.5	$values .= ", version='$release'" if $release;
305	ahart	1.1
306			$values =~ s/^, //;
307			$query = "update ntuple set $values where id=$id";
308			}
309			$results = $db->query ($query);
310
311			return $results;
312			}
313
314			sub
315	ahart	1.8	dbUpdateOther
316			{
317			my $id = shift;
318			my $comment = shift;
319
320			$comment = addSlashes ($comment);
321
322			my $query = "update other set lastUpdateTime=now(), comment='$comment' where id=$id";
323			$db->selectdb ("ntuple");
324			my $results = $db->query ($query);
325
326			return $results;
327			}
328
329			sub
330	ahart	1.4	dbDelete
331			{
332			my $id = shift;
333
334			my $query = "delete from ntuple where id=$id";
335			$db->selectdb ("ntuple");
336			my $results = $db->query ($query);
337			}
338
339			sub
340	ahart	1.8	dbDeleteOther
341			{
342			my $id = shift;
343
344			my $query = "delete from other where id=$id";
345			$db->selectdb ("ntuple");
346			my $results = $db->query ($query);
347			}
348
349			sub
350	ahart	1.1	printHelp
351			{
352	ahart	1.4	my $command = shift;
353
354	ahart	1.1	my $exeName = $0;
355			$exeName =~ s/^.\/([^\/])$/$1/;
356
357	ahart	1.4	if ($command eq "create")
358			{
359			print "Usage: $exeName -l DIRECTORY [OPTION]... create NAME\n";
360			print "Creates an entry in the database for dataset NAME. The ntuples are assumed to\n";
361			print "be present on the Tier 3, with the status being set accordingly.\n";
362			print "\n";
363			print "Mandatory arguments to long options are mandatory for short options too.\n";
364			printf "%-29s%s\n", " -b, --crabCfg FILE", "CRAB config used to submit ntuple jobs";
365			printf "%-29s%s\n", " -c, --comment COMMENT", "comment for the database entry";
366			printf "%-29s%s\n", " -f, --format FORMAT", "ntuple format (default: BEAN)";
367			printf "%-29s%s\n", " -j, --jsonFile FILE", "JSON file used for this dataset";
368			printf "%-29s%s\n", " -l, --location DIRECTORY", "directory containing the ntuples";
369			printf "%-29s%s\n", " -p, --pyConfig FILE", "Python config used to produce ntuples";
370	ahart	1.5	printf "%-29s%s\n", " -r, --release NAME", "ntuple release used to produce ntuples";
371	ahart	1.4	printf "%-29s%s\n", " -s, --lumiSummary FILE", "lumiSummary.json reported by CRAB";
372			}
373	ahart	1.8	elsif ($command eq "createOther")
374			{
375			print "Usage: $exeName [OPTION]... createOther DIRECTORIES_AND_FILES\n";
376			print "Creates an entry in the database for non-ntuple data.\n";
377			print "\n";
378			print "Mandatory arguments to long options are mandatory for short options too.\n";
379			printf "%-29s%s\n", " -c, --comment COMMENT", "comment for the database entry";
380			}
381	ahart	1.4	elsif ($command eq "update")
382			{
383			print "Usage: $exeName [OPTION]... update NAME\n";
384			print "Updates an existing database entry for dataset NAME.\n";
385			print "\n";
386			print "Mandatory arguments to long options are mandatory for short options too.\n";
387			printf "%-29s%s\n", " -b, --crabCfg FILE", "CRAB config used to submit ntuple jobs";
388			printf "%-29s%s\n", " -c, --comment COMMENT", "comment for the database entry";
389			printf "%-29s%s\n", " -f, --format FORMAT", "ntuple format";
390			printf "%-29s%s\n", " -j, --jsonFile FILE", "JSON file used for this dataset";
391			printf "%-29s%s\n", " -l, --location DIRECTORY", "directory containing the ntuples";
392	ahart	1.8	printf "%-29s%s\n", " -o, --other", "update an entry in the non-ntuple database";
393	ahart	1.4	printf "%-29s%s\n", " -p, --pyConfig FILE", "Python config used to produce ntuples";
394	ahart	1.5	printf "%-29s%s\n", " -r, --release NAME", "ntuple release used to produce ntuples";
395	ahart	1.4	printf "%-29s%s\n", " -s, --lumiSummary FILE", "lumiSummary.json reported by CRAB";
396			}
397			elsif ($command eq "finish")
398			{
399			print "Usage: $exeName -l DIRECTORY finish NAME\n";
400
401			print "Finalizes the database entry for dataset NAME, changing its status to\n";
402			print "\"present\". This is intended to be the final step in command-line based ntuple\n";
403			print "production.\n";
404			print "\n";
405			print "Mandatory arguments to long options are mandatory for short options too.\n";
406			printf "%-29s%s\n", " -l, --location DIRECTORY", "directory containing the ntuples";
407			}
408			elsif ($command eq "deleteEntry")
409			{
410	ahart	1.8	print "Usage: $exeName [OPTION]... deleteEntry NAME\n";
411	ahart	1.4	print "Deletes the database entry for dataset NAME. This is intended primarily for\n";
412			print "mistaken database entries. If you wish to actually delete a set of ntuples,\n";
413			print "please use the \"deprecate\" command instead.\n";
414	ahart	1.8	print "\n";
415			printf "%-29s%s\n", " -o, --other", "update an entry in the non-ntuple database";
416	ahart	1.4	}
417			elsif ($command eq "deprecate")
418			{
419			print "Usage: $exeName deprecate NAME\n";
420			print "Marks the dataset NAME for deletion. WARNING: The dataset will be deleted from\n";
421			print "the Tier 3 within one week of this action. If you wish to simply delete a\n";
422			print "mistaken database entry, use the \"deleteEntry\" command instead.\n";
423			}
424			elsif ($command eq "uploadRelease")
425			{
426			print "Usage: $exeName -p FILE -r DIRECTORY [OPTION]... uploadRelease NAME\n";
427			print "Copies an ntuple release to the appropriate area on the Tier 3, and creates a\n";
428			print "database entry for it, with NAME being the name of the release. An ntuple\n";
429			print "release is a CMSSW release with all the necessary packages added for creating\n";
430			print "ntuples, along with a default Python config file.\n";
431			print "\n";
432			print "Mandatory arguments to long options are mandatory for short options too.\n";
433			printf "%-29s%s\n", " -c, --comment COMMENT", "comment for the database entry";
434			printf "%-29s%s\n", " -f, --format FORMAT", "ntuple format (default: BEAN)";
435			printf "%-29s%s\n", " -p, --pyConfig FILE", "default Python config for the release";
436			printf "%-29s%s\n", " -r, --release DIRECTORY", "CMSSW release containing ntuple packages";
437			}
438			else
439			{
440			print "Usage: $exeName [OPTION]... COMMAND NAME\n";
441			print "Manipulates entry in the OSU Tier 3 ntuple database given by NAME.\n";
442			print "\n";
443			print "Mandatory arguments to long options are mandatory for short options too.\n";
444			printf "%-29s%s\n", " -h, --help", "print help. If COMMAND is present, print help";
445			printf "%-29s%s\n", " ", "specific to COMMAND.";
446			print "\n";
447			print "COMMAND may be one of the following:\n";
448			printf "%-29s%s\n", " create", "creates the entry";
449	ahart	1.8	printf "%-29s%s\n", " create", "creates an entry for non-ntuple data";
450	ahart	1.4	printf "%-29s%s\n", " update", "updates the entry";
451			printf "%-29s%s\n", " finish", "finalizes the database entry";
452			printf "%-29s%s\n", " deleteEntry", "removes the database entry";
453			printf "%-29s%s\n", " deprecate", "marks the dataset for deletion";
454			printf "%-29s%s\n", " uploadRelease", "upload an ntuple release";
455			}
456	ahart	1.1
457			exit;
458			}
459	ahart	1.4
460			sub
461			sizeOfDataset
462			{
463			my $dataset = shift;
464			my $location = shift;
465			my $fileList = shift;
466
467			my $size = 0.0;
468			my $nFiles = 0;
469	ahart	1.6	if ($location && !$fileList)
470	ahart	1.4	{
471			if (!(-e $location))
472			{
473			print "$location does not exist!\n";
474			exit;
475			}
476			$nFiles = `ls $location \| wc -l`;
477			$size = `du -s $location`;
478			$size =~ s/([^ ]) ./$1/;
479			$size /= 1024 * 1024;
480			$size = sprintf "%.2f", $size;
481			}
482			elsif ($fileList)
483			{
484			foreach my $file (split (/\n/, $fileList))
485			{
486			$nFiles++;
487			my $fileSize = `du -s $file`;
488			$fileSize =~ s/([^ ]) ./$1/;
489			$size += $fileSize;
490			}
491			$size /= 1024 * 1024;
492			$size = sprintf "%.2f", $size;
493			}
494
495			return ($nFiles, $size);
496			}
497
498			sub
499			uploadRelease
500			{
501			my $format = shift;
502			my $cmsswRelease = shift;
503			my $release = shift;
504			my $psetName = shift;
505			my $name = shift;
506			my $user = shift;
507			my $comment = shift;
508
509			move ($release, "/home/hart/public_html/releases/$name.tar.gz") or die "Ntuple releases may only be uploaded on the Tier 3, stopped";
510			if (!(-e $psetName))
511			{
512			print "$psetName does not exist!\n";
513			exit;
514			}
515			open (PY_CONFIG, "<$psetName");
516			my @pset = <PY_CONFIG>;
517			close (PY_CONFIG);
518			my $pset = join ("", @pset);
519
520			my $query = "select max(id) from ntupleRelease";
521			$db->selectdb ("ntuple");
522			my $results = $db->query ($query);
523			my @row = $results->fetchrow ();
524			my $id = 1;
525			$id = $row[0] + 1 if $results->numrows ();
526
527			$name = addSlashes ($name);
528			$pset = addSlashes ($pset);
529			$user = addSlashes ($user);
530			$format = addSlashes ($format);
531			$cmsswRelease = addSlashes ($cmsswRelease);
532			$comment = addSlashes ($comment);
533
534			my $query = "insert into ntupleRelease (id, name, pset, user, pending, format, cmsswRelease, comment) values ($id, '$name', '$pset', '$user', 1, '$format', '$cmsswRelease', '$comment')";
535			$db->selectdb ("ntuple");
536			my $results = $db->query ($query);
537			}
538	ahart	1.8
539			sub
540			datasetExists
541			{
542			my $dataset = shift;
543
544			my $query = "select id from ntuple where dataset='$dataset'";
545			$db->selectdb ("ntuple");
546			my $results = $db->query ($query);
547
548			return $results->numrows ();
549			}
550
551			sub
552			dbOtherCreate
553			{
554			my $user = shift;
555			my $listOfFiles = shift;
556			my $comment = shift;
557
558			my $size = 0.0;
559			my $nFiles = 0.0;
560			for (my $i = 0; $i < @$listOfFiles; $i++)
561			{
562			if (!(-e $$listOfFiles[$i]))
563			{
564			print "$$listOfFiles[$i] does not exist!\n";
565			exit;
566			}
567			$nFiles += `ls -R $$listOfFiles[$i] \| grep -v ':\$' \| grep -v '^\$' \| wc -l`;
568			my $fileSize = `du -s $$listOfFiles[$i]`;
569			$fileSize =~ s/([^ ]) ./$1/;
570			$fileSize /= 1024 * 1024;
571			$size += $fileSize;
572			$$listOfFiles[$i] = "$ENV{'PWD'}/$$listOfFiles[$i]" if !($$listOfFiles[$i] =~ m/^\//);
573			}
574			$size = sprintf "%.2f", $size;
575			my $location = join ("<br />", @$listOfFiles);
576
577			my $query = "select max(id) from other";
578			$db->selectdb ("ntuple");
579			my $results = $db->query ($query);
580			my @row = $results->fetchrow ();
581			my $id = 1;
582			$id = $row[0] + 1 if $results->numrows ();
583
584			$user = addSlashes ($user);
585			$location = addSlashes ($location);
586			$comment = addSlashes ($comment);
587
588			my $query = "insert into other (id, creationTime, lastUpdateTime, user, location, nFiles, sizeInGB, comment) values ($id, now(), now(), '$user', '$location', $nFiles, $size, '$comment')";
589			$db->selectdb ("ntuple");
590			my $results = $db->query ($query);
591
592			print "ID $id\n";
593			}