pbnj 2.04-4 / usr / bin / outputpbnj

#!/usr/bin/perl

eval 'exec /usr/bin/perl  -S $0 ${1+"$@"}'
    if 0; # not running under some shell
#
# Copyright (C) 2005-2006 Joshua D. Abraham ( jabra@ccs.neu.edu )
#
# This program is released under the terms of the GNU General Public License
# (GPL), which is distributed with this software in the file "COPYING".
# The GPL specifies the terms under which users may copy and use this software.
#
# PBNJ 2.0
# (P)orts (B)anners N' (J)unk
#
# Author:   Joshua D. Abraham
# Date:     March 15, 2006
# Updated:  November 15, 2006
# Version:  2.04
# 
# 
# OutputPBNJ - a program to query a PBNJ 2.0 database.
#

use strict;
use warnings;

use Shell;
use YAML;
use DBI;
use Getopt::Long;
use Text::CSV_XS;
use FileHandle;
use File::HomeDir;
use vars qw( $PROG );
( $PROG = $0 ) =~ s/^.*[\/\\]//;    # Truncate calling path from the prog name

my $AUTH    = 'Joshua D. Abraham';  # author
my $VERSION = '2.04';               # version

my $type       = 'tab';             # default output format
my $output     = 'stdout';          # default output
my $bothOutput = 0;                 # use both output methods
my $query      = 'query.yaml';      # yaml configureation file
my $data;                           # store the contents of yaml config
my $result;                         # result of sql query
my %options;                        # getopts hash
my $header = 0;                     # print column headers
my $dbh;                            # database connection
my $dbconfig = 'config.yaml';       # database config
my $database;                       # database file
my $db;                             # db backend
my $hostname;                       # db host ip
my $port;                           # db port
my $user;                           # db username
my $passwd;                         # db password
my $dbdir = ".";                    # output database directory
my $dir   = ".";                    # output database directory
my $datadb;                         # db
$options{test}  = 0;                # testing flag
$options{debug} = 0;                # debug flag
my $configdir;

#chown() that reports errors
sub safe_chown {
    my $uid  = shift;
    my $gid  = shift;
    my $file = shift;

    if ( chown( $uid, $gid, $file ) != 1 ) {
        error(    'Unable to change the owner of the file ' . $file . '.'
                . "\n\n" );
    }
}

if ( $^O eq 'MSWin32' || $^O =~ /cygwin/ ) {
    require Win32;
    import Win32;
    Win32->import(qw(CSIDL_APPDATA));
    my $dir = Win32::GetFolderPath( CSIDL_APPDATA() );
    $configdir = $dir . "\\" . "pbnj-2.0";

    if ( -e $configdir and -d $configdir ) {

        #print "$configdir exists\n";
    }
    else {
        mkdir $configdir;
        print "mkdir $configdir\n";
    }

    # check if config exists in the current directory
    if ( !-e $dbconfig ) {
        $dbconfig = "$configdir\\$dbconfig";
    }

    my ( $read_config, $read_query );

    # check if the config exists in ~/.pbnj-2.0/config.yaml
    if ( !-e $dbconfig ) {
        $read_config = 0;
    }
    else {

        #  print "config exists\n";
        $read_config = 2;
    }
    if ( !-e $query ) {
        $query = "$configdir\\$query";
    }
    if ( !-e $query ) {
        $read_query = 0;
    }
    else {

        #   print "query exists\n";
        $read_query = 2;
    }
    my @array;
    while (<DATA>) {
        push( @array, $_ );
    }

    #print "query is $read_query and config is $read_config\n";
    if ( $read_query != 2 or $read_config != 2 ) {
        if ( $read_config == 0 ) {
            open( CONFIG, ">$dbconfig" );
            foreach (@array) {
                last if (/Query.yaml/);
                if ( defined($_) ) {
                    print CONFIG $_;
                }
            }
            close(CONFIG);
            print "$dbconfig generated\n";
        }
        if ( $read_query == 0 ) {
            open( QUERY, ">$query" );
            my $config = 2;
            foreach (@array) {
                $config = 0 if (/Query.yaml/);
                if ( defined($_) and $config == 0 ) {
                    print QUERY $_;
                }
            }
            close(QUERY);
            print "$query generated\n";
        }
    }
}
else {
    $configdir = File::HomeDir->my_home;
    my $tmpuser = $configdir;
    $tmpuser =~ s/home//;
    $tmpuser =~ s/\///g;

    my $uid = getpwnam($tmpuser);
    my $gid = id("-g $tmpuser");
    if ( !defined($gid) ) {
        error("gid not defined\n");
    }
    if ( !defined($uid) ) {
        error("uid not defined\n");
    }

    $configdir .= "/.pbnj-2.0";

    if ( -e $configdir and -d $configdir ) {

        #print "$configdir exists\n";
    }
    else {
        umask 077;
        mkdir $configdir;
        print "mkdir $configdir\n";
        safe_chown( $uid, $gid, $configdir );
    }

    # check if config exists in the current directory
    if ( !-e $dbconfig ) {
        $dbconfig = "$configdir/$dbconfig";
    }

    my ( $read_config, $read_query );

    # check if the config exists in ~/.pbnj-2.0/config.yaml
    if ( !-e $dbconfig ) {
        $read_config = 0;
    }
    else {

        #  print "config exists\n";
        $read_config = 2;
    }
    if ( !-e $query ) {
        $query = "$configdir/$query";
    }
    if ( !-e $query ) {
        $read_query = 0;
    }
    else {

        #   print "query exists\n";
        $read_query = 2;
    }
    my @array;
    while (<DATA>) {
        push( @array, $_ );
    }

    #print "query is $read_query and config is $read_config\n";
    if ( $read_query != 2 or $read_config != 2 ) {
        if ( $read_config == 0 ) {
            umask 077;
            open( CONFIG, ">$dbconfig" );
            foreach (@array) {
                last if (/Query.yaml/);
                if ( defined($_) ) {
                    print CONFIG $_;
                }
            }
            close(CONFIG);
            safe_chown( $uid, $gid, $dbconfig );
            print "$dbconfig generated\n";
        }
        if ( $read_query == 0 ) {
            umask 077;
            open( QUERY, ">$query" );
            my $config = 2;
            foreach (@array) {
                $config = 0 if (/Query.yaml/);
                if ( defined($_) and $config == 0 ) {
                    print QUERY $_;
                }
            }
            close(QUERY);
            safe_chown( $uid, $gid, $query );
            print "$query generated\n";
        }
    }
}

##############################################################################
#
# help ->
# display help information
# side effect:  exits program
#
##############################################################################
sub help {
    print "Usage: $PROG [Query Options] [Config Options] [General Options] 
Query Options:
   -q  --query <name>       Perform sql query
   -t  --type <type>        Output Type [csv,tab,html]
   -f  --file <filename>    Store the result in file otherwise stdout
       --both               Print results and store them in a file
       --dir <dir>          Store the result in this directory [def .]
      
   -l  --lookup <name>      Lookup descrition based on name
       --list               List of names and descriptions
   -n  --name               Lookup all the names 
   -d  --desc               Lookup all the descriptions 
   -s  --sql                Lookup all the sql queries 
   
Config Options:
        --qconfig <file>    Config of sql queries [def query.yaml]
        --dbconfig <file>   Config for accessing database [def config.yaml]
        --configdir <dir>   Directory for the database config file
        
        --data <file>       SQLite Database override [def data.dbl]

General Options:      
       --test <level>       Testing information
       --debug <level>      Debug information
   -v  --version            Display version
   -h  --help               Display this information

Send Comments to Joshua D. Abraham ( jabra\@ccs.neu.edu )\n";
    exit;
}

##############################################################################
#
# print_version ->
# displays version
# side effect: exits program
#
##############################################################################
sub print_version {
    print "$PROG version $VERSION by $AUTH\n";
    exit;
}

##############################################################################
#
# printName: array ->
# print the names of the queries
# side effect: exits program
#
# data      yaml config contents
#
##############################################################################
sub printName {
    my $data = shift || die "printName data not defined";
    print "Name of the Queries\n";
    print "* * * * * * * * * * * * * * * * * * * * * * * * * * * \n";
    foreach (@$data) {
        my $tmp = $_;
        foreach my $key ( keys %$tmp ) {
            if ( $key eq 'name' ) {
                print "- " . $$tmp{$key} . "\n";
            }
        }
    }
    exit;
}

##############################################################################
#
# printDesc
# print the descriptions of the queries
# side effect: exits program
#
# data      yaml config contents
#
##############################################################################
sub printDesc {
    my $data = shift || die "printDesc: data not defined";
    print "Description of the Queries\n";
    print "* * * * * * * * * * * * * * * * * * * * * * * * * * * \n";
    foreach (@$data) {
        my $tmp = $_;
        foreach my $key ( keys %$tmp ) {
            if ( $key eq 'desc' ) {
                print "- " . $$tmp{$key} . "\n";
            }
        }
    }
    exit;
}

##############################################################################
#
# printList
# print the list of the queries name followed by description
# side effect: exits program
#
# data      yaml config contents
#
##############################################################################
sub printList {
    my $data = shift || die "printList: data not defined";
    print "List of Name - Description of the Queries\n";
    print "* * * * * * * * * * * * * * * * * * * * * * * * * * * \n";
    my %line;
    my $i = 0;
    foreach (@$data) {
        my $tmp = $_;
        foreach my $key ( keys %$tmp ) {
            next if ( !defined( $$tmp{$key} ) );
            $line{ $i++ }{name} = $$tmp{$key} if ( $key eq "name" );
            $line{$i}{desc} = $$tmp{$key} if ( $key eq "desc" );
        }
    }
    $i--;
    foreach ( 0 .. $i ) {
        next
            if ( !defined( $line{$_}{name} or !defined( $line{$_}{desc} ) ) );
        print $line{$_}{name} . " - " . $line{$_}{desc} . "\n";
    }
    exit;
}

##############################################################################
#
# printSql
# print the Sql of the queries
# side effect: exits program
#
# data      yaml config contents
#
##############################################################################
sub printSql {
    my $data = shift || die "printSql: data not defined";
    print "Sql of the Queries\n";
    print "* * * * * * * * * * * * * * * * * * * * * * * * * * * \n";
    foreach (@$data) {
        my $tmp = $_;
        foreach my $key ( keys %$tmp ) {
            if ( $key eq 'sql' ) {
                print "- " . $$tmp{$key} . "\n";
            }
        }
    }
    exit;
}

##############################################################################
#
# lookupDescByName: anon hash
# lookup description of the query based on the name
# side effect: exits program
#
# data      yaml config contents
# name      name of the query to lookup
#
##############################################################################
sub lookupDescByName {
    my ($href) = @_;
    die "lookupByName: data not defined" unless defined $href->{data};
    die "lookupByName: name not defined" unless defined $href->{name};
    my $data = $href->{data};
    my $name = $href->{name};
    foreach (@$data) {
        my $tmp = $_;
        foreach my $key ( keys %$tmp ) {
            if ( $key eq 'name' and $$tmp{$key} eq $name ) {
                print "Description for $name\n";
                print "$$tmp{desc}\n";
                exit;
            }
        }
    }
    print "Didn't find any descrition with the name $name\n";
    exit 1;
}

##############################################################################
# lookupByDesc : anon hash ->
# lookup description of query based on the decription
# side effect: exits program
#
# data      yaml config contents
# desc      descrition of the query to lookup
#
##############################################################################
sub lookupNameByDesc {
    my ($href) = @_;
    die "lookupByDesc: data not defined" unless defined $href->{data};
    die "lookupByDesc: desc not defined" unless defined $href->{desc};
    my $data = $href->{data};
    my $desc = $href->{desc};
    foreach (@$data) {
        my $tmp = $_;
        foreach my $key ( keys %$tmp ) {
            if ( $key eq 'desc' and $$tmp{$key} eq $desc ) {
                print "Name is $$tmp{name}\n";
                exit;
            }
        }
    }
    print "didn't find any descrition with the name $desc\n";
    exit 1;
}

##############################################################################
#
# lookupSql: anon hash -> scalar
# lookup sql based on the name
#
# data   data from the yaml config file
# name   name of the query to lookup
#
##############################################################################
sub lookupSql {
    my ($href) = @_;
    die "lookupSqlByName: data not defined" unless defined $href->{data};
    die "lookupSqlByName: name not defined" unless defined $href->{name};
    my $data = $href->{data};
    my $name = $href->{name};
    foreach (@$data) {
        my $tmp = $_;
        foreach my $key ( keys %$tmp ) {
            if ( $key eq 'name' and $$tmp{$key} eq $name ) {
                my $sql = $$tmp{sql};
                return $sql;
            }
        }
    }
}

##############################################################################
#
# realName: anon hash -> scalar
# determines if the name exists
#
# data      data from the yaml config file
# name      name of the query to lookup
#
##############################################################################
sub realName {
    my ($href) = @_;
    die "realName: data not defined" unless defined $href->{data};
    die "realName: name not defined" unless defined $href->{data};
    my $data = $href->{data};
    my $name = $href->{name};
    foreach (@$data) {
        my $tmp = $_;
        foreach my $key ( keys %$tmp ) {
            if ( $key eq 'name' and $$tmp{$key} eq $name ) {
                return 1;
            }
        }
    }
    return 0;
}

##############################################################################
#
# runSql: anon hash -> array ref
# execute sql and return arrayref to the result
#
# data      data for output
# name      name of sql query
# db        sqlite3 database file
#
# ############################################################################
sub runSql {
    my ($href) = @_;
    die "runSql: data not defined" unless defined $href->{data};
    die "runSql: name not defined" unless defined $href->{name};
    die "runSql: db not defined"   unless defined $href->{db};
    my $data = $href->{data};
    my $name = $href->{name};
    my $dbh  = $href->{db};
    my ( $exist, $sql );
    $exist = realName( { data => $data, name => $name } );

    if ( $exist eq 1 ) {
        $sql = lookupSql( { data => $data, name => $name } );
        print "sql is $sql\n"
            if ( $options{debug} eq 1 or $options{test} eq 1 );
    }
    else {
        print "Query $name does not exist the Query Config File\n";
        exit 1;
    }
    my $sth = $dbh->prepare($sql);
    if (defined($sth)){
        my $tmp = $sth->execute;
        return $sth;
    }
    else {
        print "Broken SQL Query\n";
        exit 1;
    }
    exit;
}

# html output
sub htmlOutput {
    my $output = shift;
    foreach (@$output) {
        foreach (@$_) {
            print "$_   ";
        }
        print "\n";
    }
    print "\n";
}

# tab output
sub tabOutput {
    my $output = shift;
    foreach (@$output) {
        foreach (@$_) {
            print "$_\t";
        }
        print "\n";
    }
    print "\n";
}

# csv output
sub csvOutput {
    my $output = shift;
    foreach (@$output) {
        foreach (@$_) {
            print "$_,";
        }
        print "\n";
    }
    print "\n";
}

##############################################################################
#
# delimOutput: anon hash ->
# output the results of a  sql query using a delminatator
# side effect:
#
# results   the data for output
# fileName  contains the filename of the output or stdout
# delim     Delminator for the file (csv, tab, space)
#
# ##############################################################################
sub delimOutput {
    my ($href) = @_;
    my ( $fh, @fields, $elements );
    die "output: results not defined"  unless defined $href->{results};
    die "output: fileName not defined" unless defined $href->{fileName};
    die "output: delim not defined"    unless defined $href->{delim};

    my $sth      = $href->{results};
    my $fileName = $href->{fileName};
    my $delim    = $href->{delim};
    my $csv      = Text::CSV_XS->new( { binary => 1 } );

    my $row = $sth->fetchall_arrayref;
    if ( $fileName ne 'stdout' ) {
        $fh = new FileHandle(">>$fileName");
        die "'$fileName': $!" unless defined $fh;
    }
    my $num_fields = $sth->{NUM_OF_FIELDS};
    if (scalar(@$row) eq 0){ 
        print "No Results from Query\n";
        exit;
    }
    print "num of fields is $num_fields\n" if ( $options{test} eq 1 );
    if ( ( $type eq 'csv' or $type eq 'html' ) and ( $header eq 1 ) ) {
        for ( 0 .. $num_fields ) {
            print "before column extraction\n" if ( $options{test} eq 1 );
            my $column = $sth->{NAME_lc}->[$_];
            print "after column extraction\n" if ( $options{test} eq 1 );

            if ( defined($column) ) {
                print "column is $column\n"
                    if ( $options{debug} eq 1 or $options{debug} eq 1 );
                print $fh "$column" if ( $fileName ne 'stdout' );
                print $fh "$delim"
                    if ( $fileName ne 'stdout'
                    && $_ < $num_fields - 1 );
                print "$column"
                    if ( $fileName eq 'stdout' or $bothOutput eq 1 );
                print "$delim"
                    if ( ( $fileName eq 'stdout' or $bothOutput eq 1 )
                    && $_ < ( $num_fields - 1 ) );
            }
            else {
                print "column not defined" if ( $options{test} eq 1 );
            }

        }
        print "\n" if ( $fileName eq 'stdout' );
        print $fh "\n" if ( $fileName ne 'stdout' );
    }
    my $rownum = 0;
    foreach (@$row) {
        my $result;
        foreach my $line (@$_) {
            if ( defined $result ) {
                $result = join( ',', $result, $line );
            }
            else {
                $result = join( ',', $line );
            }
        }
        if ( $csv->parse($result) ) {
            @fields   = $csv->fields();
            $elements = @fields;
        }
        if ( $type eq "tab" and $rownum eq 0 and $header eq 1 ) {
            for ( my $x = 0; $x < $elements; $x++ ) {
                print "before column extraction\n" if ( $options{test} eq 1 );
                my $col = $sth->{NAME_lc}->[$x];
                print "after column extraction\n" if ( $options{test} eq 1 );
                if ( defined($col) ) {
                    print "column is $col\n" if ( $options{test} eq 1 );
                    my $len = "%" . length( $fields[$x] );
                    my $tmp;
                    if ( $fields[$x] =~ /\w/ ) {
                        $tmp = 's';
                    }
                    elsif ( $fields[$x] =~ /\d/ ) {
                        $tmp = 'd';
                    }
                    else {
                        print "field is $fields[$x]"
                            if ( $options{test} eq 1 );
                    }
                    printf "$len$tmp", $col
                        if ( $type eq 'tab'
                        and ( $fileName eq 'stdout' or $bothOutput eq 1 ) );
                    printf "\t"
                        if ( $type eq 'tab'
                        and ( $fileName eq 'stdout' or $bothOutput eq 1 ) );
                    printf $fh "$len$tmp", $col
                        if ( $type eq 'tab' && $fileName ne 'stdout' );
                    printf $fh "\t"
                        if ( $type eq 'tab' && $fileName ne 'stdout' );

                }
            }
            $rownum++;
        }

        for ( my $x = 0; $x < $elements; $x++ ) {
            my $len;
            my $tmp;

            #print "x is $x  \t\n";
            #print "element is $elements \t\n";
            $len = "%" . length( $fields[$x] );
            if ( $fields[$x] =~ /\w/ ) {
                $tmp = 's';
            }
            elsif ( $fields[$x] =~ /\d/ ) {
                $tmp = 'd';
            }
            else {
                $tmp = 's';
            }
            printf $fh "$fields[$x]" if ( $fileName ne 'stdout' );
            printf $fh "$delim"
                if ( $fileName ne 'stdout' && $x < $elements - 1 );
            printf "$len$tmp", $fields[$x]
                if ( $fileName eq 'stdout' or $bothOutput eq 1 );
            printf "$delim"
                if ( ( $fileName eq 'stdout' or $bothOutput eq 1 )
                && $x < $elements - 1 );
        }
        printf "\n" if ( $fileName eq 'stdout' or $bothOutput eq 1 );
        printf $fh "\n" if ( $fileName ne 'stdout' );
    }
    print "\n" if ( $fileName eq 'stdout' or $bothOutput eq 1 );
    print "\n" if ( $fileName ne 'stdout' );
    printf $fh "\n" if ( $fileName ne 'stdout' );
}
#############################################################################
#
# GetOpts
#
#############################################################################
if ( @ARGV == 0 ) {
    help;
    exit;
}

GetOptions(
    \%options,
    'type|t=s', 'file|f=s', 'lookup|l=s', 'both|b',
    'query|q=s',  'names|n',     'desc|d', 'sql|s',  'list',
    'dbconfig=s', 'configdir=s', 'dir=s',  'data=s', 'qconfig=s',
    'test|=s',    'debug|=s',
    'help|h'    => sub { help(); },
    'version|v' => sub { print_version(); },
    'both'      => sub { $bothOutput = 1 },
    )
    or exit 1;

if ( $options{'configdir'} ) {
    $configdir = $options{'configdir'};
}
if ( $options{'dir'} ) {
    $dir = $options{'dir'};
}

if ( $options{'dbconfig'} ) {
    my $tmpconfig;
    if ( $^O eq 'MSWin32' || $^O =~ /cygwin/ ) {
        $tmpconfig = $configdir . '\\' . $options{'dbconfig'};
    }
    else {
        $tmpconfig = $configdir . "/" . $options{'dbconfig'};
    }

    if ( -e $tmpconfig && -r $tmpconfig ) {
        $dbconfig = $tmpconfig;
    }

}

if ( $options{'qconfig'} ) {
    my $tmpconfig;
    if ( $^O eq 'MSWin32' || $^O =~ /cygwin/ ) {
        $tmpconfig = $configdir . '\\' . $options{'qconfig'};
    }
    else {
        $tmpconfig = $configdir . "/" . $options{'qconfig'};
    }

    if ( -e $tmpconfig && -r $tmpconfig ) {
        $query = $tmpconfig;
    }
}
if ( -e $query && -r $query ) {
    $data = YAML::LoadFile($query);
}
else {
    print "configuration file $query not readable\n";
    exit 1;
}

chdir($dir) or die "Couldn't change to directory $dir\n";

if ( $options{'query'} ) {
    if ( $options{data} ) {
        $db       = "SQLite";
        $database = $options{data};
    }
    else {
        if ( -e $dbconfig && -r $dbconfig ) {
            $datadb = YAML::LoadFile($dbconfig);

            foreach my $tmp ( keys %$datadb ) {
                $passwd   = $$datadb{$tmp} if ( $tmp eq 'passwd' );
                $user     = $$datadb{$tmp} if ( $tmp eq 'user' );
                $db       = $$datadb{$tmp} if ( $tmp eq 'db' );
                $database = $$datadb{$tmp} if ( $tmp eq 'database' );
                $hostname = $$datadb{$tmp} if ( $tmp eq 'host' );
                $port     = $$datadb{$tmp} if ( $tmp eq 'port' );
            }
        }
        else {
            print "Configuration file $dbconfig not readable\n";
            exit 1;
        }
    }

    # connection to database
    if ( $db eq 'SQLite' ) {
        if ( !-e $database ) {
            print "SQLite database $database doesn't exist\n";
            exit 1;
        }
        if ( !-r $database ) {
            print "SQLite database $database can't be read\n";
            print "Might need root privileges to access it\n";
            exit 1;
        }

        $dbh = DBI->connect(
            "dbi:$db:$database",
            $user, $passwd,
            {   PrintError => 0,
                RaiseError => 0,
                AutoCommit => 1
            }
            )
            || die "Cannot connect: $DBI::errstr";
    }
    elsif ( $db eq 'mysql' ) {
        my $dsn = "DBI:$db:database=$database;host=$hostname;port=$port";
        $dbh = DBI->connect(
            $dsn, $user, $passwd,
            {   PrintError => 0,
                RaiseError => 0,
                AutoCommit => 1
            }
            )
            || die "Cannot connect: $DBI::errstr";

    }
    elsif ( $db eq 'Pg' ) {
        my $dsn = "DBI:$db:database=$database;host=$hostname;port=$port";
        $dbh = DBI->connect(
            $dsn, $user, $passwd,
            {   PrintError => 0,
                RaiseError => 0,
                AutoCommit => 1,
                PrintWarn  => 0
            }
            )
            || die "Cannot connect: $DBI::errstr";

    #$dbh = DBI->connect("dbi:$db:dbname=$dbname", "", "", {AutoCommit => 0});
    }
    elsif ( $db eq 'CSV' ) {
        print "output queries not supported for CSV format\n";
        exit;
    }
    else {
        print "$db isn't supported\n";
    }
}

if ( $options{'list'} ) {
    printList($data);
}

if ( $options{'names'} ) {
    printName($data);
}

if ( $options{'desc'} ) {
    printDesc($data);
}

if ( $options{'sql'} ) {
    printSql($data);
}

if ( $options{'type'} ) {
    if ( $options{'type'} eq 'tab' ) {
        $type = 'tab';
    }
    elsif ( $options{'type'} eq 'html' ) {
        $type = 'html';
    }
    elsif ( $options{'type'} eq 'csv' ) {
        $type = 'csv';
    }
    else {
        print $options{'type'};
        die "type not supported\n";
    }
}

if ( $options{'file'} ) {
    chdir($dir) or die "Couldn't change to $dir directory\n";
    if ( -e $options{'file'} ) {
        if ( -w $options{'file'} ) {
            $output = $options{'file'};
        }
    }
    elsif ( !-w $options{'file'} ) {
        $output = $options{'file'};
    }
    else {
        print "file not writable\n";
        exit 1;
    }
}

if ( $options{'lookup'} ) {
    lookupDescByName( { data => $data, name => $options{'lookup'} } );
    exit;
}
if ( $options{'query'} ) {
    $result
        = runSql( { data => $data, name => $options{'query'}, db => $dbh } );
}
&help if ( not defined $result );

#############################################################################
#
# Main
#
#############################################################################

if ( $type eq 'html' ) {

    #htmlOutput( $result, $output );
    delimOutput( { results => $result, fileName => $output, delim => " " } );
}
elsif ( $type eq 'tab' ) {
    delimOutput( { results => $result, fileName => $output, delim => "\t" } );
}
elsif ( $type eq 'csv' ) {
    delimOutput( { results => $result, fileName => $output, delim => "," } );
}
else {
    print "output not defined\n";
}

if ( $db ne 'SQLite' ) {
    $dbh->disconnect;
}

__DATA__
# Config.yaml
#
# Copyright (C) 2005-2006 Joshua D. Abraham ( jabra@ccs.neu.edu )
# 
# This config file is released under the terms of the GNU General
# Public License (GPL), which is distributed with this software in the
# file "COPYING". The GPL specifies the terms under which users
# may copy and use this software.
# 
# PBNJ 2.0
# (P)orts (B)anners N' (J)unk
#
# Author:   Joshua D. Abraham
# Date:     March 15, 2006
# Updated:  November 15, 2006
# Version:  2.04
# 
# Configuration file for PBNJ 2.0
# YAML:1.0
#
# Config for connecting to a DBI database
# SQLite, mysql etc
db: SQLite
# for SQLite the name of the file. For mysql the name of the database
database: data.dbl
# Username for the database. For SQLite no username is needed.
user: ""
# Password for the database. For SQLite no password is needed.
passwd: ""
# Password for the database. For SQLite no host is needed.
host: ""
# Port for the database. For SQLite no port is needed.
port: ""

# Query.yaml
#
#
# Copyright (C) 2005-2006 Joshua D. Abraham ( jabra@ccs.neu.edu )
#
# This config file is released under the terms of the GNU General
# Public License (GPL), which is distributed with this software in the
# file "COPYING".  The GPL specifies the terms under which users
# may copy and use this software.
#
#
# PBNJ 2.0
# (P)orts (B)anners N' (J)unk
#
# Author:   Joshua D. Abraham
# Date:     March 15, 2006
# Updated:  November 15, 2006
# Version:  2.04
#
# Configuration file for PBNJ 2.0
# 
# Contains all the names, descriptions and queries for PBNJ 2.0
#
# If you would like to submit a new query, please submit it to the link
# below. The summary should start with QUERY: description
#
# http://sourceforge.net/tracker/?func=add&group_id=149390&atid=774490
#
#

--- #YAML:1.0

- name: possiblevuln
  desc: list all of the services that possibly, should not be running
  sql: select * from services where service!='ssh' and state='up'

# example of verion checking 
#- name: vulnssh
#  desc: all of the services that have old ssh running
#  sql: |-
#   select S.updated_on,M.ip,S.service,S.port,S.version from services as S,
#   machines as M where service='ssh' and state='up' and version!='4.1p1'

- name: sshmachines
  desc: all the machines that have run ssh
  sql: |-
     select S.updated_on,M.host,S.service,S.state,S.version from
     services as S, machines as M where port='22' and M.mid = S.mid

- name: allservices
  desc: all services that have ever been up
  sql: select * from services where state='up'

- name: services
  desc: basic dump of the services table
  sql: select updated_on,service,version,banner,state from services

- name: unknown_version_up
  desc: services that have run a unknown version
  sql: |-
   select updated_on,service,banner from services where 
   version='unknown version' and state='up'

- name: unknown_banner_up
  desc: services that have run a unknown banner
  sql: |-
   select updated_on,service,version from services where 
   banner='unknown banner' and state='up'

- name: machines
  desc: basic dump of the machines table
  sql: select created_on,ip,host,localh,os from machines

- name: sdump
  desc: dump of the services table
  sql: select * from services
  
- name: mdump
  desc: dump of the machines table
  sql: select * from machines

- name: servicesup
  desc: services running on machines with a given ip or range
  sql:  |-
    select M.host, S.service,S.version,S.banner from 
    services as S,machines as M where state='up' and M.mid = S.mid 
   
- name: unknownversion
  desc: all instances of services running with an unknown version
  sql: select * from services where version='unknown version' 
    
- name: unknownbanner
  desc: all instances of services running with an unknown banner
  sql: select * from services where banner='unknown banner'

- name: machine_audit
  desc: machine audit query
  sql: select ip,host,os from machines

- name: service_audit
  desc: serice audit query
  sql: |-
    select s.port,s.protocol,s.service,s.banner,s.version from services s join
    machines m join (select mid,service,max(machine_updated)'muo' from
    services group by mid,service)r where s.mid=r.mid and
    s.service=r.service and s.state='up' 
    AND s.machine_updated=r.muo and m.mid=s.mid ORDER BY s.port;

- name: latestinfo_audit
  desc: latest host and services info
  sql:  |-
    select S.port,S.protocol,S.service,S.state,S.banner,S.version from
    services as S where updated_on = (select updated_on from
    services ORDER BY updated_on DESC limit 1) ORDER BY s.port;

- name: latestinfo
  desc: latest host and services info (by hostname)
  sql:  |-
    select S.updated_on,M.host, S.service,S.state,S.version,S.protocol from 
    services as S,machines as M where updated_on = (select updated_on from 
    services ORDER BY updated_on DESC limit 1) and M.mid = S.mid

- name: latestchange
  desc: latest host and services info (by ip)
  sql:  |-
    select S.updated_on,M.ip, S.service,S.state,S.version,S.protocol from
    services as S,machines as M where updated_on = (select updated_on from
    services ORDER BY updated_on DESC limit 1) and M.mid = S.mid

- name: servicestate
  desc: |-
    most recent state for all services whether they have or have not changed
  sql: |-
    select s.updated_on,m.host,r.service,state from services s join
    machines m join (select mid,service,max(machine_updated)'muo' from
    services group by mid,service)r where s.mid=r.mid and
    s.service=r.service and s.machine_updated=r.muo and m.mid=s.mid;

- name: uptimeratio
  desc: |-
    ratio for each service for each machine of the uptime of all the
    services in the database. This is based on our scans. Therefore,
    if you can scan once a day or once every hour the ratio is more or
    less accurate depending.
  sql: |-
   select a.mid,a.service,upCount,scanCount,1.0*upCount/scanCount'ratio' from
   (select mid,service,state,count(*)'upCount' from services where state='up'
   group by mid,service,state) a join (select mid,service,count(*)'scanCount'
   from services  group by mid,service) b where a.mid=b.mid and
   a.service=b.service ;

- name: monthlyreport
  desc: |- 
    report of services ip addresses and versions that are currntly running
  sql: |-
    select M.ip,  S.port,S.service,S.version,S.banner from services as
    S,machines as M where updated_on = (select updated_on from services ORDER
    BY updated_on DESC limit 1) and M.mid = S.mid