7 =head1 NAME $RCSfile: cqquery.pl,v $
11 This command line tool allows for a simplified access to Clearquest database
12 and supports an SQL like syntax to allow you to select and update data quickly.
13 It has the ability to talk to a running Clearquest::Server process so you can
14 use it on systems that do not have Clearques installed.
16 Currently the command langauge is limited - no joins or multiple tables, only
17 very simple where conditions, etc. This may improve over time.
19 All actions are logged to cqquery.log.
21 Note that CmdLine is in use so you have a fully command history stack (subject,
22 of course, to whether or not you have Term::ReadLine::Gnu installed. For cqperl
23 that's a no go. For Cygwin's Perl or Linux based Perl's you do or can install it
24 from CPAN) as well as CmdLine builtins like history and help.
26 Control-C handling is also supported.
34 Andrew DeFaria <Andrew@ClearSCM.com>
42 Mon Oct 24 16:19:15 PDT 2011
46 $Date: 2012/12/18 19:44:10 $
52 Usage: cqquery [-u|sage] [-v|erbose] [-d|ebug]
53 [-username <username>] [-password <password>]
54 [-database <database>] [-dbset <dbset>]
55 [-histfile <histfile>]
59 -usa|ge: Displays usage
61 -de|bug: Output debug messages
63 -h|istfile <histfile>: History file to use
65 -use|rname <username>: Username name to use
66 -p|assword <password>: Password to use
67 -da|tabase <database>: Database to use
68 -db|set <dbset>: DB Set to use
69 -[no]c|qd: If set then look for a Clearquest::Server
73 Configuration data is stored in ../etc/cqdservice.conf which defines the
74 defaults for things like username/password/db, etc. These are overridden by the
75 environent (-username is CQD_USERNAME, -password is CQDPASSWORD, etc. for
76 server based connections, CQ_USERNAME, CQPASSWORD, etc. for direct connections).
77 Command line options (e.i. -username) override both the environment and the
82 # TODO: This needs major revision...
88 use lib "$FindBin::Bin/../lib";
95 my $VERSION = '$Revision: 1.3 $';
96 ($VERSION) = ($VERSION =~ /\$Revision: (.*) /);
100 help => 'select <fields> from <table> [where <condition>]',
101 description => 'Selects fields from a table with an optional condiiton.
102 Currently conditions are limited.
107 help => 'update <table> set <field> = <expr> [where <condition>]',
108 description => 'Update a field in a table based on an optional condition',
112 help => 'insert [into] <table> <fields> values <values>',
113 description => 'Insert a new record into table',
117 help => 'delete [from] <table> [where <condition>]',
118 description => 'Delete records from table based on condition (not implemented)',
122 my (%opts, $cq, $log, $pipe);
131 . "Abort current operation (y/N)?"
134 my $response = <STDIN>;
137 die "Operation aborted\n" if $response =~ /^\s*(y|yes)/i;
139 display color ('cyan') . 'Continuing...' . color ('reset');
142 sub pipeInterrupt () {
148 sub findRecords ($$;@) {
149 my ($table, $condition, @fields) = @_;
151 my ($result, $nbrRecs) = $cq->find ($table, $condition, @fields);
155 my $msg = "$nbrRecs records qualified";
157 $SIG{PIPE} = \&pipeInterrupt;
159 $pipe = StartPipe $ENV{PAGER};
161 PipeOutput $msg, $pipe;
165 return ($result, $nbrRecs);
169 my ($table, $condition, @fields) = @_;
171 my ($result, $nbrRecs) = findRecords ($table, $condition, @fields);
179 while (my %record = $cq->getNext ($result)) {
185 my $line = $record{$_} ? "$_: $record{$_}" : "$_ <undef>";
189 PipeOutput $line, $pipe;
199 my ($table, $condition, %update) = @_;
201 my ($result, $nbrRecs) = findRecords ($table, $condition);
211 $log->disp ("$nbrRecs records qualified");
213 my ($processed, $updated) = (0, 0);
215 while (my %record = $cq->getNext ($result)) {
218 my $key = $cq->key ($table, $record{dbid});
220 $log->disp ("Updating $key", 1);
222 my $errmsg = $cq->updateRec ($table, $record{dbid}, %update);
225 $log->disp (color ('red') . ' failed!!' . color ('reset'));
229 $log->disp (color ('green' ). ' succeeded.' . color ('reset'));
235 my $errors = $log->errors;
237 return unless $processed;
243 if ($processed == 1) {
244 $log->disp ('One record processed');
246 $log->disp ("$processed records processed");
250 $log->disp ('One record updated');
252 $log->disp ("$updated records updated");
256 $log->disp ('One error (Check ' . $log->fullname . ' for more info)');
257 } elsif ($errors > 1) {
258 $log->disp ("$errors errors (Check " . $log->fullname . ' for more info)');
260 $log->disp ("$errors errors");
265 my ($table, %values) = @_;
267 my $errmsg = $cq->insert ($table, %values);
270 $log->err ("Unable to insert record:\n$errmsg");
272 $log->disp ("Inserted record");
281 # Mimic simple SQL statements...
282 if ($line =~ /^\s*select\s+([\w, ]+)\s+from\s+(\S+)(.*)\;*/i) {
283 my ($table, $condition, $rest);
285 @fields = split (/\s*,\s*/, $1);
289 # Trim any trailing ';' from table in case the person didn't enter a where
293 if ($rest =~ /\s*where\s+(.*?)\;*$/i) {
295 } elsif ($rest !~ /^\s*$/) {
296 error "Syntax error in select statement\n\n\t$line";
301 return ::select ($table, $condition, @fields);
302 } elsif ($line =~ /^\s*update\s+(\S+)\s+set\s+(\S+)\s*=\s*(.*)/i) {
303 my ($table, $condition, %update, $rest);
312 if ($rest =~ /(.*)\s+where\s+(.*)/) {
320 $value =~ s/^\s*["'](.*)/$1/;
321 $value =~ s/(.*)["']\s*$/$1/;
323 $update{$fieldName} = $value;
325 return update ($table, $condition, %update);
326 } elsif ($line =~ /^\s*insert\s+(into)*\s+(\S+)\s+([\w, ]+)\s+values*\s+([\w, ]+)\;*/i) {
327 my ($table, @values);
330 @fields = split /\s*,\s*/, $3;
331 @values = split /\s*,\s*/, $4;
335 $values{$_} = shift @values foreach (@fields);
337 return ::insert ($table, %values);
338 } elsif ($line =~/^\s*shutdown\s*$/) {
342 } elsif ($line =~ /^\s*$/) {
345 $log->err ("Unknown command: $line");
354 # Use test database for now...
355 $opts{database} = 'mobct';
356 $opts{histfile} = $ENV{CQQUERY_HISTFILE} || "./${FindBin::Script}_hist";
361 usage => sub { Usage },
362 verbose => sub { set_verbose },
363 debug => sub { set_debug },
372 display "$FindBin::Script v$VERSION";
374 $SIG{INT} = \&interrupt;
377 require Clearquest::Client;
378 $cq = Clearquest::Client->new (%opts);
381 $cq = Clearquest->new (\%opts);
386 my $me = $FindBin::Script;
389 my $prompt = color ('bold green') . "$me:" . color ('reset');
392 $CmdLine::cmdline->set_histfile ($opts{histfile});
393 $CmdLine::cmdline->set_prompt ($prompt);
394 $CmdLine::cmdline->set_cmds (%cmds);
395 $CmdLine::cmdline->set_eval (\&evaluate);
399 my $dbconnection = $cq->username . '@' . $cq->database . '/' . $cq->dbset;
400 $dbconnection .= ' (Server: ' . $cq->host . ':' . $cq->port . ')'
401 if ref $cq eq 'Clearquest::Client';
403 my $msg = "Opening database $dbconnection";
405 verbose_nolf color ('dark white') . "$msg..." . color ('reset');
408 unless ($cq->connect) {
409 $log->msg (color ('red') . ' Failed!' . color ('reset'));
411 $log->err ("Unable to connect to database $dbconnection", 1);
413 verbose color ('dark white') . ' connected' . color ('reset');
414 $log->log (' connected');
417 # Single execution from command line
419 my $result = evaluate join ' ', @ARGV;
426 while (($line, $result) = $CmdLine::cmdline->get ()) {
427 last unless defined $line;
429 $log->log ("$me: $line");
431 last if $line =~ /exit|quit/i;
433 my $result = evaluate ($line);