#!/usr/bin/perl -w
$ID = q$Id: afsdb-query,v 1.8 2005/02/04 05:07:56 eagle Exp $;
#
# afsdb-query -- Query the AFS database, usable via remctl.
#
# Written by Russ Allbery <rra@stanford.edu>
# Copyright 2004, 2005 Board of Trustees, Leland Stanford Jr. University
#
# This program is free software; you may redistribute it and/or modify it
# under the same terms as Perl itself.
#
# Run a SQL query against the AFS database.  Mostly used for generating lists
# of volumes for volume moves.

##############################################################################
# Modules and declarations
##############################################################################

require 5.005;

use strict;
use vars qw($ID);

use Getopt::Long qw(GetOptions);
use Stanford::LSDB::AFSDB ();

##############################################################################
# Implementation
##############################################################################

# Trim extraneous garbage from the path.
my $fullpath = $0;
$0 =~ s%.*/%%;

# Parse our options.
my ($help, $version);
Getopt::Long::config ('bundling', 'require_order');
GetOptions ('help|h'     => \$help,
            'version|v'  => \$version) or exit 1;
if ($help) {
    print "Feeding myself to perldoc, please wait....\n";
    exec ('perldoc', '-t', $fullpath);
} elsif ($version) {
    my $version = join (' ', (split (' ', $ID))[1..3]);
    $version =~ s/,v\b//;
    $version =~ s/(\S+)$/($1)/;
    $version =~ tr%/%-%;
    print $version, "\n";
    exit;
}

# Skip an initial "query" argument.
if ($ARGV[0] eq 'query' && @ARGV == 2) {
    shift;
} elsif (@ARGV != 1) {
    die "Usage: afsdb query <sql-query>\n";
}

# Get the query and add select onto it if it doesn't already start that way.
# We don't try to be really secure; the assumption is that the user is already
# authenticated.  Change double quotes to single quotes because we never
# really want to mean what double quotes mean.
my $query = shift or die "$0: no query specified\n";
$query = 'select volname from volumes where ' . $query
    unless $query =~ /^select /i;
$query =~ tr/""/''/;

# Open a database connection and run the query.
my $dbh = Stanford::LSDB::AFSDB->connect
    or die "$0: cannot connect to database\n";
my $results = $dbh->selectall_arrayref ($query);
for my $row (@$results) {
    print "@$row\n";
}
$dbh->disconnect;

##############################################################################
# Documentation
##############################################################################

=head1 NAME

afsdb-query - Query the AFS database, usable via remctl

=head1 SYNOPSIS

afsdb-query [B<-hv>] [query] I<query>

=head1 DESCRIPTION

B<afsdb-query> just runs a SQL query against the AFS database on lsdb and
returns the result.  It's mostly used to generate volume lists for mass
volume moves, but can be used for other purposes.  It is intended to be run
via remctl.

If I<query> does not begin with C<select>, C<select volname from volumes
where> will automatically be added to the beginning of the query.  This lets
one write queries like:

    type = 'RW' and server = 'afssvr1' and part = '/vicepa'

without having to include the rest of the logic, when the goal is a simple
list of volumes.

Any double quotes in the SQL query will be automatically changed to single
quotes.  The query generally will include whitespace, so this is done so
that you can either enclose the whole query in single quotes and use double
quotes for strings or vice versa, whichever makes the most sense to you.

For easy use under remctld(8), if the first argument of B<afsdb-query> is
the word C<query> and there is a second argument, the second argument will
be taken as the query to run.

=head1 EXAMPLES

List all volumes on afssvr6:

    afsdb-query 'server = "afssvr6"'

List all RW volumes on afssvr1 /vicepb:

    afsdb-query "server = 'afssvr1' and part = '/vicepb' and type = 'RW'"

List volume name and size for all volumes on afssvr17 larger than 100MB:

    afsdb-query 'select volname, used from volumes
        where server = "afssvr17" and used > 100000'

When actually running the previous command, you'll need to type the whole
command on one line without any newlines.

=head1 CAVEATS

This script does not protect against any sort of attacks or attempt to
limit what operations the user can perform; the automatic addition of
the select command is purely for convenience and can easily be worked
around.  Users who have access to this script will be able to perform
any operation that can be performed by the user this script authenticates
as.

=head1 AUTHOR

Russ Allbery <rra@stanford.edu>

=head1 COPYRIGHT AND LICENSE

Copyright 2004, 2005 Board of Trustees, Leland Stanford Jr. University.

This program is free software; you may redistribute it and/or modify it
under the same terms as Perl itself.

=head1 SEE ALSO

Stanford::LSDB::AFSDB

The current version of this program is available from the AFS reporting
database software page at L<http://www.eyrie.org/~eagle/software/afsdb/>.

=cut