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

#!/usr/bin/env perl

use strict;
use Mysql;
use Getopt::Long;

sub getDataset;
sub dbUpdate;
sub addSlashes;

our $db;

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

printHelp () if $opt{"help"};
printHelp () if $argc != 2;
printHelp () if $ARGV[0] != "create" && $ARGV[0] != "update" && $ARGV[0] != "finish" && $ARGV[0] != "deprecate";
my $id = -1;
$id = getDataset ($ARGV[1]) if $ARGV[0] ne "create";
$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, $ARGV[1], $ENV{"USER"}, $opt{"format"}, $opt{"location"}, $opt{"remoteLocation"}, $status, $opt{"comment"}, $opt{"pyConfig"}, $opt{"crabCfg"}, $opt{"jsonFile"}, $opt{"lumiSummary"});

sub
addSlashes
{
  my $string = shift;

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

  return $string;
}

sub
getDataset
{
  my $dataset = shift;

  my $results;
  my $queryDataset = $dataset;
  $queryDataset =~ s/\*/%/g;
  $queryDataset =~ s/(.*)/%$1%/g;
  $db = Mysql->connect ("cmshead.mps.ohio-state.edu", "ntuple", "osuT3User");
  my $query = "select id,dataset,user,creationTime from ntuple where dataset like '$queryDataset' order by lastUpdateTime";
  $db->selectdb ("ntuple");
  $results = $db->query ($query);
  if ($results->numrows () == 1)
    {
      my @row = $results->fetchrow ();
      return $row[0];
    }
  if ($results->numrows () == 0)
    {
      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;
    }
  my %map;
  print "Found multiple database entries matching\n";
  print "\"$dataset\":\n";
  print "( 0) new\n";
  for (my $i = 1; $i <= $results->numrows (); $i++)
    {
      my @row = $results->fetchrow ();
      $map{"$i"} = $row[0];
      printf "(%2d) $row[1]\n", $i;
      print "     created by $row[2] on $row[3]\n";
    }
  print "\nWhich entry would you like to update? (Select 0 to create a new entry): ";
  my $response = <STDIN>;
  $response =~ s/[ \t\n]//g;
  return -1 if $response == 0;
  if (!(exists $map{$response}))
    {
      print "Your selection \"$response\" was not a valid option! Quitting.\n";
      exit;
    }

  return $map{$response};
}

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

  my $pset;
  my $crabCfg;
  my $jsonFile;
  my $lumiSummary;
  my $size = 0;
  my $nFiles = 0;
  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 ($location)
  {
    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;
  }

  $dataset = addSlashes ($dataset);
  $user = addSlashes ($user);
  $format = addSlashes ($format);
  $pset = addSlashes ($pset);
  $crabCfg = addSlashes ($crabCfg);
  $jsonFile = addSlashes ($jsonFile);
  $lumiSummary = addSlashes ($lumiSummary);
  $location = addSlashes ($location);
  $remoteLocation = addSlashes ($remoteLocation);
  $status = addSlashes ($status);
  $comment = addSlashes ($comment);

  my $results;
  my $query;
  if ($id < 0)
    {
      $db = Mysql->connect ("cmshead.mps.ohio-state.edu", "ntuple", "osuT3User");
      $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, remoteLocation, nFiles, sizeInGB, status, comment, pset, crabCfg, jsonFile, lumiSummary) values ($id, '$dataset', now(), now(), '$user', '$format', '$location', '$remoteLocation', $nFiles, $size, '$status', '$comment', '$pset', '$crabCfg', '$jsonFile', '$lumiSummary')";
    }
  if ($id > 0)
    {
      my $fields;
      my $values;

      $values .= ", dataset='$dataset'" if $dataset;
      $values .= ", lastUpdateTime=now()";
      $values .= ", format='$format'" if $format;
      $values .= ", location='$location'" if $location;
      $values .= ", remoteLocation='$remoteLocation'" if $remoteLocation;
      $values .= ", nFiles=$nFiles" if $location;
      $values .= ", sizeInGB=$size" if $location;
      $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 =~ s/^, //;
      $query = "update ntuple set $values where id=$id";
    }
  $results = $db->query ($query);

  return $results;
}

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

  print "Usage: $exeName [OPTION]... COMMAND DATASET\n";
  print "Manipulates entries in the OSU Tier 3 ntuple database.\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 file used to submit the ntuple jobs";
  printf "%-29s%s\n", "  -c, --comment COMMENT", "comment for this database entry";
  printf "%-29s%s\n", "  -f, --format FORMAT", "ntuple format (default: BEAN)";
  printf "%-29s%s\n", "  -j, --jsonFile FILE", "JSON file used to ntuplize the dataset";
  printf "%-29s%s\n", "  -h, --help", "print this help message";
  printf "%-29s%s\n", "  -l, --location DIRECTORY", "location on the Tier 3";
  printf "%-29s%s\n", "  -p, --pyConfig FILE", "python config used to ntuplize the dataset";
  printf "%-29s%s\n", "  -s, --lumiSummary FILE", "lumiSummary.json returned by CRAB";
  print "\n";
  print "COMMAND may be one of the following:\n";
  printf "%-29s%s\n", "  create", "creates the entry (assumes the dataset is present)";
  printf "%-29s%s\n", "  update", "updates the entry";
  printf "%-29s%s\n", "  finish", "finalizes the database entry";
  printf "%-29s%s\n", "  deprecate", "marks the dataset for deletion";

  exit;
}
Revision:	1.3
Committed:	Thu Jun 14 17:42:30 2012 UTC (12 years, 10 months ago) by ahart
Branch:	MAIN
CVS Tags:	V00-00-02
Changes since 1.2:	+7 -3 lines
Log Message:	Allow fuzzy dataset names for osudb and osusub. First commit of AnaTools directory.
#	User	Rev	Content
1	ahart	1.1	#!/usr/bin/env perl
2
3			use strict;
4			use Mysql;
5			use Getopt::Long;
6
7			sub getDataset;
8			sub dbUpdate;
9			sub addSlashes;
10
11			our $db;
12
13			my %opt;
14			Getopt::Long::Configure ("bundling");
15			GetOptions (\%opt, "comment\|c=s", "format\|f=s", "pyConfig\|p=s", "crabCfg\|b=s", "jsonFile\|j=s", "remoteLocation\|r=s", "lumiSummary\|s=s", "location\|l=s", "help\|h");
16			my $argc = @ARGV;
17
18			printHelp () if $opt{"help"};
19			printHelp () if $argc != 2;
20			printHelp () if $ARGV[0] != "create" && $ARGV[0] != "update" && $ARGV[0] != "finish" && $ARGV[0] != "deprecate";
21			my $id = -1;
22			$id = getDataset ($ARGV[1]) if $ARGV[0] ne "create";
23			$ARGV[0] = "create" if $id < 0;
24			my $status = "present";
25			$status = "" if $ARGV[0] eq "update";
26			$status = "deprecated" if $ARGV[0] eq "deprecate";
27			$opt{"format"} = "BEAN" if $ARGV[0] eq "create" && !$opt{"format"};
28			dbUpdate ($id, $ARGV[1], $ENV{"USER"}, $opt{"format"}, $opt{"location"}, $opt{"remoteLocation"}, $status, $opt{"comment"}, $opt{"pyConfig"}, $opt{"crabCfg"}, $opt{"jsonFile"}, $opt{"lumiSummary"});
29
30			sub
31			addSlashes
32	ahart	1.2	{
33	ahart	1.1	my $string = shift;
34
35			$string =~ s/\\/\\\\/g;
36			$string =~ s/'/\\'/g;
37			$string =~ s/"/\\"/g;
38			$string =~ s/\\0/\\\\0/g;
39
40			return $string;
41			}
42
43			sub
44			getDataset
45			{
46			my $dataset = shift;
47
48			my $results;
49	ahart	1.3	my $queryDataset = $dataset;
50			$queryDataset =~ s/\*/%/g;
51			$queryDataset =~ s/(.*)/%$1%/g;
52	ahart	1.1	$db = Mysql->connect ("cmshead.mps.ohio-state.edu", "ntuple", "osuT3User");
53	ahart	1.3	my $query = "select id,dataset,user,creationTime from ntuple where dataset like '$queryDataset' order by lastUpdateTime";
54	ahart	1.1	$db->selectdb ("ntuple");
55			$results = $db->query ($query);
56			if ($results->numrows () == 1)
57			{
58			my @row = $results->fetchrow ();
59			return $row[0];
60			}
61			if ($results->numrows () == 0)
62			{
63			print "Database entry does not exist. Create it? (Y/n): ";
64			my $response = <STDIN>;
65			$response =~ s/\n//g;
66			$response = "y" if !$response;
67			exit if substr (lc ($response), 0, 1) ne 'y';
68			return -1;
69			}
70			my %map;
71			print "Found multiple database entries matching\n";
72			print "\"$dataset\":\n";
73	ahart	1.3	print "( 0) new\n";
74	ahart	1.1	for (my $i = 1; $i <= $results->numrows (); $i++)
75			{
76			my @row = $results->fetchrow ();
77			$map{"$i"} = $row[0];
78	ahart	1.3	printf "(%2d) $row[1]\n", $i;
79			print " created by $row[2] on $row[3]\n";
80	ahart	1.1	}
81			print "\nWhich entry would you like to update? (Select 0 to create a new entry): ";
82			my $response = <STDIN>;
83			$response =~ s/[ \t\n]//g;
84			return -1 if $response == 0;
85			if (!(exists $map{$response}))
86			{
87			print "Your selection \"$response\" was not a valid option! Quitting.\n";
88			exit;
89			}
90
91			return $map{$response};
92			}
93
94			sub
95			dbUpdate
96			{
97			my $id = shift;
98			my $dataset = shift;
99			my $user = shift;
100			my $format = shift;
101			my $location = shift;
102			my $remoteLocation = shift;
103			my $status = shift;
104			my $comment = shift;
105			my $psetName = shift;
106			my $crabCfgName = shift;
107			my $jsonFileName = shift;
108			my $lumiSummaryName = shift;
109
110			my $pset;
111			my $crabCfg;
112			my $jsonFile;
113			my $lumiSummary;
114			my $size = 0;
115			my $nFiles = 0;
116			if ($psetName)
117			{
118			if (!(-e $psetName))
119			{
120			print "$psetName does not exist!\n";
121			exit;
122			}
123			open (PY_CONFIG, "<$psetName");
124			my @pset = <PY_CONFIG>;
125			close (PY_CONFIG);
126			$pset = join ("", @pset);
127			}
128			if ($crabCfgName)
129			{
130			if (!(-e $crabCfgName))
131			{
132			print "$crabCfgName does not exist!\n";
133			exit;
134			}
135			open (CRAB_CONFIG, "<$crabCfgName");
136			my @crabCfg = <CRAB_CONFIG>;
137			close (CRAB_CONFIG);
138			$crabCfg = join ("", @crabCfg);
139			}
140			if ($jsonFileName)
141			{
142			if (!(-e $jsonFileName))
143			{
144			print "$jsonFileName does not exist!\n";
145			exit;
146			}
147			open (JSON_FILE, "<$jsonFileName");
148			my @jsonFile = <JSON_FILE>;
149			close (JSON_FILE);
150			$jsonFile = join ("", @jsonFile);
151			}
152			if ($lumiSummaryName)
153			{
154			if (!(-e $lumiSummaryName))
155			{
156			print "$lumiSummaryName does not exist!\n";
157			exit;
158			}
159			open (LUMI_SUMMARY, "<$lumiSummaryName");
160			my @lumiSummary = <LUMI_SUMMARY>;
161			close (LUMI_SUMMARY);
162			$lumiSummary = join ("", @lumiSummary);
163			}
164			if ($location)
165			{
166			if (!(-e $location))
167			{
168			print "$location does not exist!\n";
169			exit;
170			}
171			$nFiles = `ls $location \| wc -l`;
172			$size = `du -s $location`;
173			$size =~ s/([^ ]) ./$1/;
174			$size /= 1024 * 1024;
175			$size = sprintf "%.2f", $size;
176			}
177
178			$dataset = addSlashes ($dataset);
179			$user = addSlashes ($user);
180			$format = addSlashes ($format);
181			$pset = addSlashes ($pset);
182			$crabCfg = addSlashes ($crabCfg);
183			$jsonFile = addSlashes ($jsonFile);
184			$lumiSummary = addSlashes ($lumiSummary);
185			$location = addSlashes ($location);
186			$remoteLocation = addSlashes ($remoteLocation);
187			$status = addSlashes ($status);
188			$comment = addSlashes ($comment);
189
190			my $results;
191			my $query;
192			if ($id < 0)
193			{
194			$db = Mysql->connect ("cmshead.mps.ohio-state.edu", "ntuple", "osuT3User");
195			$query = "select max(id) from ntuple";
196			$db->selectdb ("ntuple");
197			$results = $db->query ($query);
198			my @row = $results->fetchrow ();
199			my $id = 1;
200			$id = $row[0] + 1 if $results->numrows ();
201
202			$query = "insert into ntuple (id, dataset, creationTime, lastUpdateTime, user, format, location, remoteLocation, nFiles, sizeInGB, status, comment, pset, crabCfg, jsonFile, lumiSummary) values ($id, '$dataset', now(), now(), '$user', '$format', '$location', '$remoteLocation', $nFiles, $size, '$status', '$comment', '$pset', '$crabCfg', '$jsonFile', '$lumiSummary')";
203			}
204			if ($id > 0)
205			{
206			my $fields;
207			my $values;
208
209			$values .= ", dataset='$dataset'" if $dataset;
210			$values .= ", lastUpdateTime=now()";
211			$values .= ", format='$format'" if $format;
212			$values .= ", location='$location'" if $location;
213			$values .= ", remoteLocation='$remoteLocation'" if $remoteLocation;
214			$values .= ", nFiles=$nFiles" if $location;
215			$values .= ", sizeInGB=$size" if $location;
216			$values .= ", status='$status'" if $status;
217			$values .= ", comment='$comment'" if $comment;
218			$values .= ", pset='$pset'" if $pset;
219			$values .= ", crabCfg='$crabCfg'" if $crabCfg;
220			$values .= ", jsonFile='$jsonFile'" if $jsonFile;
221			$values .= ", lumiSummary='$lumiSummary'" if $lumiSummary;
222
223			$values =~ s/^, //;
224			$query = "update ntuple set $values where id=$id";
225			}
226			$results = $db->query ($query);
227
228			return $results;
229			}
230
231			sub
232			printHelp
233			{
234			my $exeName = $0;
235			$exeName =~ s/^.\/([^\/])$/$1/;
236
237			print "Usage: $exeName [OPTION]... COMMAND DATASET\n";
238			print "Manipulates entries in the OSU Tier 3 ntuple database.\n";
239			print "\n";
240			print "Mandatory arguments to long options are mandatory for short options too.\n";
241			printf "%-29s%s\n", " -b, --crabCfg FILE", "CRAB config file used to submit the ntuple jobs";
242			printf "%-29s%s\n", " -c, --comment COMMENT", "comment for this database entry";
243			printf "%-29s%s\n", " -f, --format FORMAT", "ntuple format (default: BEAN)";
244			printf "%-29s%s\n", " -j, --jsonFile FILE", "JSON file used to ntuplize the dataset";
245			printf "%-29s%s\n", " -h, --help", "print this help message";
246			printf "%-29s%s\n", " -l, --location DIRECTORY", "location on the Tier 3";
247			printf "%-29s%s\n", " -p, --pyConfig FILE", "python config used to ntuplize the dataset";
248			printf "%-29s%s\n", " -s, --lumiSummary FILE", "lumiSummary.json returned by CRAB";
249			print "\n";
250			print "COMMAND may be one of the following:\n";
251			printf "%-29s%s\n", " create", "creates the entry (assumes the dataset is present)";
252			printf "%-29s%s\n", " update", "updates the entry";
253			printf "%-29s%s\n", " finish", "finalizes the database entry";
254			printf "%-29s%s\n", " deprecate", "marks the dataset for deletion";
255
256			exit;
257			}