#!/usr/bin/perl -w

###########################################################################
#
# Baracus build and boot management framework
#
# Copyright (C) 2010 Novell, Inc, 404 Wyman Street, Waltham, MA 02451, USA.
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the Artistic License 2.0, as published
# by the Perl Foundation, or the GNU General Public License 2.0
# as published by the Free Software Foundation; your choice.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  Both the Artistic
# Licesnse and the GPL License referenced have clauses with more details.
#
# You should have received a copy of the licenses mentioned
# along with this program; if not, write to:
#
# FSF, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110, USA.
# The Perl Foundation, 6832 Mulderstraat, Grand Ledge, MI 48837, USA.
#
###########################################################################

use strict;

use Getopt::Long qw( :config pass_through );
use Pod::Usage;

use lib "/usr/share/baracus/perl";

use BaracusDB;
use BaracusSql qw( :vars :subs );
use BaracusConfig qw( :vars :subs );
use BaracusCore qw( :subs );  # main, check_command, etc...
use BaracusAux  qw( :subs );  # aux check and db routines...
use BaracusSource qw ( :vars );

=pod

=head1 NAME

B<baconfig> - baracus tool to manage auxiliary files to describe builds

=head1 SYNOPSIS

B<baconfig> E<lt>commandE<gt> E<lt>typeE<gt> [options and arguments]

Where E<lt>commandE<gt> is

    list   <type> [match]  List entries in database.
    add    <type> <...>    Copy to database from specified file.
    update <type> <...>    Modify module, profile or file entry.
    export <type> <...>    Export data portion of entry to new file.
    detail <type> <name>   Display file entry in database table.
    remove <type> <name>   Delete file from database table.

    help      This 'Usage' summary message.
    man       Detailed man page.

And, where E<lt>typeE<gt> is

    hardware  Values related to the hardware of the build client.
    module    Postinstall scripts to run on build completion.
    profile   Network or other variable definitions.
    autobuild Build recipe templates with variables for customization.
    file      file database.

And, where <...> indicates additional required arguments.

Use 'man' or 'help <command> [<type>]' for more details.

=head1 DESCRIPTION

This tool allows files related to hardware, modules, profiles, autobuild to be
added to, removed from, detailed, fetched from database tables.  Additionally,
all the database entries can be listed by type.

=head1 OPTIONS

=over 4

=item -v --verbose  Be verbose with output

=item -q --quiet  Be as quiet as possible

=item -n --nolabels  Do not print headers/footers

=item -a --all  For versioned files, show disabled too

=back

=cut

my $man     = 0;
my $help    = 0;

my $cmds =
{
 'list'    => \&list,
 'add'     => \&add,
 'update'  => \&update,
 'export'  => \&export,
 'detail'  => \&detail,
 'remove'  => \&remove,

 'help'    => \&help,
 'man'     => \&man,
 };

my @types = qw ( hardware module profile autobuild file );

my $opts =
{
 verbose    => 0,
 quiet      => 0,
 all        => 0,
 nolabels   => 0,
 debug      => 0,
 execname   => "",
 LASTERROR  => "",
};

$opts->{execname} = $0;
$opts->{execname} = $1 if ( $0 =~ m|^.*/([^/].+)| );

GetOptions
    (
     'help|?'   => \$help,
     'man'      => \$man,
     'debug+'   => \$opts->{debug},
     'quiet'    => \$opts->{quiet},
     'all'      => \$opts->{all},
     'verbose+' => \$opts->{verbose},
     'nolabels' => \$opts->{nolabels},
     );

&man()  if $man;
&help( $opts, $cmds ) if $help;
&help( $opts, $cmds ) unless ( scalar @ARGV );
if ($ARGV[0] eq "man"  ) { &man(); }
if ($ARGV[0] eq "help" ) { shift @ARGV; &help( $opts, $cmds, @ARGV ); }

# what DBI schema and database are we using
my $dbname = "baracus";
my $dbrole = $dbname;

my $dbtftp = "sqltftp";

print "setting uid to $dbrole\n" if ($opts->{debug} > 2);

my $uid = BaracusDB::su_user( $dbrole );
die BaracusDB::errstr unless ( defined $uid );

my $dbh = BaracusDB::connect_db( $dbname, $dbrole );
die BaracusDB::errstr unless( $dbh );

my $tftph = BaracusDB::connect_db( $dbtftp, $dbrole );
die BaracusDB::errstr unless( $tftph );

my $status = &main( $opts, $cmds, @ARGV );

die BaracusDB::errstr unless BaracusDB::disconnect_db( $dbh );
die BaracusDB::errstr unless BaracusDB::disconnect_db( $tftph );

print $opts->{LASTERROR} if $status;

exit $status;

die "DOES NOT EXECUTE";


###########################################################################
##
## commands
##


=head1 COMMANDS

=head2 list <type> [--all] [--quiet] [match]

Takes <type> as an argument, (hardware, module, profile, autobuild, file).
Also takes optional string [ match ] to use for partial matching.

Lists enabled entries of type, matching match, in filesystem.
If --all is specified all versions (if <type> supports versions)
are listed.

=cut

sub list
{
    my $command = "list";
    my $type = shift;
    my $pattern = shift;

    if ( scalar @_ ) {
        printf "\nUnknown arg(s): %s\n", join(" ", @_);
        &help( $opts, $cmds, $command );
    }

    if ( defined $pattern ) {
        if ( $pattern =~ m{\*|\?} ) {
            print "handling wildcard to postgres expression\n" if $opts->{debug};
            $pattern =~ s|\*|%|g;
            $pattern =~ s|\?|_|g;
        }
    } else {
        $pattern = "%";
    }

    $type = lc $type if defined $type;
    my $tlist = &check_type( $opts, $command, $type );

    # recursive call to list for all types - one shot whammy
    if ( $type eq "all" ) {
        my @ltypes = split( /, /, $tlist);
        my $status;
        foreach my $ltype ( @ltypes ) {
            $status = &list( $ltype, $pattern );
            return $status if $status;
        }
        return 0;
    }

    my $sth = &list_start( $type, $pattern );

    unless( defined $sth ) {
        $opts->{LASTERROR} .= "Unable to create db stmt handle for search: $pattern\n";
        return 1;
    }

    unless ( $opts->{quiet} || $opts->{nolabels} ) {
        print "--------------------------------------------------------------------------------\n";
        if ( $type eq "hardware" ) {
            printf "%-32s ver sts description\n", $type;
        } elsif ( $type eq "profile" ) {
            printf "%-32s ver sts description\n", $type;
        } elsif ( $type eq "module") {
            printf "%-32s ver sts mty description\n", $type;
        } elsif ( $type eq "autobuild" ) {
            printf "%-32s ver sts description\n", $type;
        } elsif ( $type eq "file" ) {
            printf "%-32s cnt sts description\n", $type;
        } else {
            printf "%-32s description\n", $type;
        }
        print "--------------------------------------------------------------------------------\n";
    }
    while ( my $href = &list_next( $sth ) ) {
        if ( $type eq "hardware" ) {
            next unless ( $opts->{all} || $href->{'status'} );
            # name, version, status, description
            printf "%-32s ", $href->{'name'};
            unless ( $opts->{quiet} ) {
                printf "%-3s %-3s %s",
                    $href->{'version'}, $href->{'status'},
                        (defined $href->{'description'}) ?
                            $href->{'description'} : "";
            }
        } elsif ( $type eq "module" ) {
            next unless ( $opts->{all} || $href->{'status'} );
            # name, version, status, mandatory, description
            printf "%-32s ", $href->{'name'};
            my $mty = &check_mandatory ( $opts, $dbh, $href->{'name'});
            $mty = scalar @{$mty} if ( $mty != 0 );
            unless ( $opts->{quiet} ) {
                printf "%-3s %-3s %-3s %s",
                    $href->{'version'}, $href->{'status'}, $mty,
                        (defined $href->{'description'}) ?
                            $href->{'description'} : "";
            }
        } elsif ( $type eq "profile" ) {
            next unless ( $opts->{all} || $href->{'status'} );
            # name, version, status, description
            printf "%-32s ", $href->{'name'};
            unless ( $opts->{quiet} ) {
                printf "%-3s %-3s %s",
                    $href->{'version'}, $href->{'status'},
                        (defined $href->{'description'}) ?
                            $href->{'description'} : "";
            }
        } elsif ( $type eq "autobuild" ) {
            next unless ( $opts->{all} || $href->{'status'} );
            # name, version, status, description
            printf "%-32s ", $href->{'name'};
            unless ( $opts->{quiet} ) {
                printf "%-3s %-3s %s",
                    $href->{'version'}, $href->{'status'},
                        (defined $href->{'description'}) ?
                            $href->{'description'} : "";
            }
        } elsif ( $type eq "file" ) {
            next unless ( $opts->{all} || $href->{'enabled'} );
            # name, count, status, description
            printf "%-32s ", $href->{'name'};
            unless ( $opts->{quiet} ) {
                printf "%-3s %-3s %s",
                    $href->{'count'}, $href->{'enabled'},
                        (defined $href->{'description'}) ?
                            $href->{'description'} : "";
            }
        } else {
            # name, description
            printf "%-32s ", $href->{'name'};
            unless ( $opts->{quiet} ) {
                printf "%s",
                    (defined $href->{'description'}) ?
                        $href->{'description'} : "";
            }
        }
        print "\n";
    }
    &list_finish( $sth );

    return 0;
}

# add invoke proper add type

sub add
{
    my $command = "add";
    my $type = shift;
    my $status;

    $type = lc $type;
    &check_type( $opts, $command, $type );

    if    ( $type eq "hardware"  ) { $status = &add_hardware( @_ );  }
    elsif ( $type eq "module"    ) { $status = &add_module( @_ );    }
    elsif ( $type eq "profile"   ) { $status = &add_profile( @_ );   }
    elsif ( $type eq "autobuild" ) { $status = &add_autobuild( @_ ); }
    elsif ( $type eq "file"      ) { $status = &add_tftp( @_ );      }
    else {
        # should be redundant with &check_type above
        $opts->{LASTERROR} = "Invalid <type> '$type' passed for add.\n";
        return 1;
    }

    return $status;
}

=head2 add hardware <options and args>

Where <options and args> are

    --name id                        Required unique id, suggest make-model.
    --enable | --noenable            Enable hardware for use. [default enable]
    --description "note"             Quoted note describing this hardware.
    --bootArgs string                Quoted line of kernel boot arguments.
    --rootdisk disk                  Disk device to install. [default /dev/sda]
    --rootpart part                  Root partition. [ default <rootdisk> ]

    --driverupdate "dudname"         Additional pkgs on driver update disk
                                     Typically specific to one class of hardware
                                     and DUD provided by vendor to support distro.

    --cert "distro1 distro2 etc..."  A list of supported distros, for this
                                     hardware. May be spcified multiple times.

=cut

sub add_hardware
{
    my $command = "add";
    my $type = "hardware";

    my %entry =
        (
         'hardwareid'   => "",
         'bootargs'     => "",
         'rootdisk'     => "/dev/sda",
         'rootpart'     => "",
         'description'  => "",
         'driverupdate' => "",
         'cert'         => "",
         'status'       => "t",
         'version'      => 1,
         );

    @ARGV = @_;
    GetOptions
        (
         'name=s'         => \$entry{'hardwareid'},
         'description=s'  => \$entry{'description'},
         'bootargs=s'     => \$entry{'bootargs'},
         'rootdisk=s'     => \$entry{'rootdisk'},
         'rootpart=s'     => \$entry{'rootpart'},
         'enable!'        => \$entry{'status'},
         'disable'        => sub { $entry{'status'} = 0 },
         'driverupdate=s' => \$entry{'driverupdate'},
         'cert=s'         => \&multiarg_handler,
         );

    if ( scalar @ARGV ) {
        $opts->{LASTERROR} = sprintf "\nUnknown arg(s): %s\n", join(" ", @ARGV);
        return 1;
    }

    &help( $opts, $cmds, $command, $type) unless ( $entry{'hardwareid'} );

    # multiarg creates hash entry with a space seperated list of unique values

    $entry{'cert'}     =
        $multiarg{ 'cert'     } if (defined $multiarg{ 'cert'     });

    my $findcount = &find_helper( $opts, $dbh, $tftph, $type, $entry{'hardwareid'} );
    unless ( defined $findcount ) {
        # &find_helper call failed
        $opts->{LASTERROR} = "Failed in add $type.\n" . $opts->{LASTERROR};
        return 1;
    }
    if ( $findcount ) {
        $opts->{LASTERROR} = "Entry already exists:  $type $entry{'hardwareid'}.\n";
        return 1;
    }

    if ( $entry{'status'} ne "" ) {
        if ( $entry{'status'} ) {
            $entry{'status'} = "t";
        } else {
            $entry{'status'} = "f";
        }
    }

    $entry{'rootpart'} = $entry{'rootdisk'} unless ( $entry{'rootpart'} );

    unless ( $entry{'driverupdate'} eq "" ) {
        my $dist = { distro => "$entry{'driverupdate'}" };
        my $dref = &get_distro( $opts, $dbh, $dist );
        unless ( defined $dref ) {
            $opts->{LASTERROR} = "Unable to find driverupdate: $entry{'driverupdate'}\n";
            return 1;
        }
        if ( $dref->{type} != BA_SOURCE_DUD ) {
            $opts->{LASTERROR} = "Specified driverupdate not DUD type: $dref->{addon}\n";
            return 1;
        }
    }

    unless ( $entry{'cert'} eq "" ) {
        print "checking certs: $entry{'cert'}\n" if $opts->{debug};
        my $status = &check_distros ( $opts, $dbh, $tftph, $entry{'cert'} );
        return $status if $status; # LASTERROR set in call
    }

    my $sql_cols = lc get_cols( $baTbls{ $type } );
    $sql_cols =~ s/[ \t]*//g;
    my @cols = split( /,/, $sql_cols );
    my $sql_vals = "?," x scalar @cols; chop $sql_vals;

    my $sql = qq|INSERT INTO $baTbls{ $type }
                ( $sql_cols )
                VALUES ( $sql_vals )
                |;

    print $sql . "\n" if $opts->{debug};

    my $sth = $dbh->prepare( $sql );

    unless ( defined $sth ) {
        $opts->{LASTERROR} = "Unable to prepare 'add' $type statement\n" .
            $dbh->errstr;
        return 1;
    }

    my $paramidx = 0;
    foreach my $col (@cols) {
        $paramidx += 1;
        $sth->bind_param( $paramidx, $entry{ $col } );
    }

    unless( $sth->execute( ) ) {
        $opts->{LASTERROR} = "Unable to execute 'add' $type statement\n" .
            $sth->errstr;
        return 1;
    }

    $sth->finish;
    undef $sth;

    # foreign key references require that hardwareid exist before certs added

    unless ( $entry{'cert'} eq "" ) {
        my $status = &add_certs ( $type, $entry{'hardwareid'}, $entry{'cert'} );
        if ($status) {
            $opts->{LASTERROR} .= "\nAdd succeeded but certification failed.\nUse 'modify $type' next time to try adding certification again.\n";
            return $status;
        }
    }

    return 0;
}

=head2 add module <options and args>

Where <options and args> are

    --name id                           Required unique id, suggest script name
    --enable | --noenable               Enable module for use. [default enable]
    --description "note"                Quoted note describing this script
    --cert    "distro1 distro2 etc..."  Certify module for listed distro
    --mancert "distro1 distro2 etc..."  Make module mandatory for listed distro
    --file <path/script>                Script to use for this module

=cut

# thought this was supported from old autoinit documentation
#
#  --interpreter value   where value is shell, perl, python  [default shell]

sub add_module
{
    my $command = "add";
    my $type = "module";
    my $file = "";
    my %entry =
        (
         'moduleid'      => "",
         'description'   => "",
         'interpreter'   => "shell",
         'data'          => "",
         'status'        => "t",
         'cert'          => "",
         'mancert'       => "",
         'version'       => 1,
         );

    @ARGV = @_;
    GetOptions
        (
         'name=s'         => \$entry{'moduleid'},
         'description=s'  => \$entry{'description'},
#         'interpreter=s'  => \$entry{'interpreter'},
         'file=s'         => \$file,
         'enable!'        => \$entry{'status'},
         'disable'        => sub { $entry{'status'} = 0 },
         'cert=s'         => \&multiarg_handler,
         'mancert=s'      => \&multiarg_handler,
         );

    if ( scalar @ARGV ) {
        $opts->{LASTERROR} = sprintf "\nUnknown arg(s): %s\n", join(" ", @ARGV);
        return 1;
    }

    &help( $opts, $cmds, $command, $type) unless ( $entry{'moduleid'} );

    # multiarg creates hash entry with a space seperated list of unique values

    $entry{'cert'}    =
        $multiarg{ 'cert'    } if (defined $multiarg{ 'cert'    });
    $entry{'mancert'} =
        $multiarg{ 'mancert' } if (defined $multiarg{ 'mancert' });

    my $findcount = &find_helper( $opts, $dbh, $tftph, $type, $entry{'moduleid'} );
    unless ( defined $findcount ) {
        # &find_helper call failed
        $opts->{LASTERROR} = "Failed in add $type.\n" . $opts->{LASTERROR};
        return 1;
    }
    if ( $findcount ) {
        $opts->{LASTERROR} = "Entry already exists:  $type $entry{'moduleid'}.\n";
        return 1;
    }

    if ( $entry{'status'} ne "" ) {
        if ( $entry{'status'} ) {
            $entry{'status'} = "t";
        } else {
            $entry{'status'} = "f";
        }
    }

    $entry{'interpreter'} = lc $entry{'interpreter'};
    $entry{'interpreter'} =~ s/[ \t]*//g;

    if ( ($entry{'interpreter'} ne "shell")   &&
         ($entry{'interpreter'} ne "perl")    &&
         ($entry{'interpreter'} ne "python" ) ) {
        $opts->{LASTERROR} = "invalid interpreter specified \'$entry{'interpreter'}\' - should be 'shell' 'perl' or 'python'\n";
        return 1;
    }

    unless ( $file ne "" ) {
        print "Need --file <file>.\n";
        &help( $opts, $cmds, $command, $type);
    }

    unless ( -f $file and -r $file ) {
        $opts->{LASTERROR} = "Unable to find or read file: $file.\n";
        return 1;
    }

    open (FILE, "<$file") || die "unable to open $file: $!\n";
    undef $/;
    $entry{'data'}=<FILE>;      # slurp mode
    $/ = "\n";
    close FILE;

    unless ( $entry{'cert'} eq "" ) {
        print "checking certs: $entry{'cert'}\n" if $opts->{debug};
        my $status = &check_distros ( $opts, $dbh, $tftph, $entry{'cert'} );
        return $status if $status; # LASTERROR set in call
    }

    unless ( $entry{'mancert'} eq "" ) {
        print "checking certs: $entry{'mancert'}\n" if $opts->{debug};
        my $status = &check_distros ( $opts, $dbh, $tftph, $entry{'mancert'} );
        return $status if $status; # LASTERROR set in call
    }

    my $sql_cols = lc get_cols( $baTbls{ $type } );
    $sql_cols =~ s/[ \t]*//g;
    my @cols = split( /,/, $sql_cols );
    my $sql_vals = "?," x scalar @cols; chop $sql_vals;

    my $sql = qq|INSERT INTO $baTbls{ $type }
                ( $sql_cols )
                VALUES ( $sql_vals )
                |;

    print $sql . "\n" if $opts->{debug};

    my $sth = $dbh->prepare( $sql );

    unless ( defined $sth ) {
        $opts->{LASTERROR} = "Unable to prepare 'add' $type statement\n" .
            $dbh->errstr;
        return 1;
    }

    my $paramidx = 0;
    foreach my $col (@cols) {
        $paramidx += 1;
        $sth->bind_param( $paramidx, $entry{ $col } );
    }

    unless( $sth->execute( ) ) {
        $opts->{LASTERROR} = "Unable to execute 'add' $type statement\n" .
            $sth->errstr;
        return 1;
    }

    $sth->finish;
    undef $sth;

    # foreign key references require that hardwareid exist before certs added

    unless ( $entry{'cert'} eq "" ) {
        my $status = &add_certs ( $type, $entry{'moduleid'}, $entry{'cert'} );
        return $status if $status; # LASTERROR set in update_cert call
    }

    unless ( $entry{'mancert'} eq "" ) {
        my $status = &man_certs($type, $entry{'moduleid'}, $entry{'mancert'} );
        return $status if $status; # LASTERROR set in update_cert call
    }

    return 0;
}

=head2 add profile <options and args>

Where <options and args> are

    --name id              Required unique id.
    --enable | --noenable  Enable profile for use. [ default enable ]
    --description "note"   Quoted note describing this profile.
    --file <path/prof>     File to use for this profile.

=cut

sub add_profile
{
    my $command = "add";
    my $type = "profile";
    my $file = "";
    my %entry =
        (
         'profileid'    => "",
         'description'  => "",
         'data'         => "",
         'status'       => "t",
         'version'      => 1,
         );

    @ARGV = @_;
    GetOptions
        (
         'name=s'         => \$entry{'profileid'},
         'description=s'  => \$entry{'description'},
         'file=s'         => \$file,
         'enable!'        => \$entry{'status'},
         'disable'        => sub { $entry{'status'} = 0 },
         );

    if ( scalar @ARGV ) {
        $opts->{LASTERROR} = sprintf "\nUnknown arg(s): %s\n", join(" ", @ARGV);
        return 1;
    }

    &help( $opts, $cmds, $command, $type) unless ( $entry{'profileid'} );

    my $findcount = &find_helper( $opts, $dbh, $tftph, $type, $entry{'profileid'} );
    unless ( defined $findcount ) {
        $opts->{LASTERROR} = "Failed in add $type.\n" . $opts->{LASTERROR};
        return 1;
    }
    if ( $findcount ) {
        $opts->{LASTERROR} = "Entry already exists:  $type $entry{'profileid'}.\n";
        return 1;
    }

    if ( $entry{'status'} ne "" ) {
        if ( $entry{'status'} ) {
            $entry{'status'} = "t";
        } else {
            $entry{'status'} = "f";
        }
    }

    unless ( $file ne "" ) {
        print "Need --file <file>.\n";
        &help( $opts, $cmds, $command, $type);
    }

    unless ( -f $file and -r $file ) {
        $opts->{LASTERROR} = "Unable to find or read file: $file.\n";
        return 1;
    }

    open (FILE, "<$file") || die "unable to open $file: $!\n";
    undef $/;
    $entry{'data'}=<FILE>;      # slurp mode
    $/ = "\n";
    close FILE;

    my $sql_cols = lc get_cols( $baTbls{ $type } );
    $sql_cols =~ s/[ \t]*//g;
    my @cols = split( /,/, $sql_cols );
    my $sql_vals = "?," x scalar @cols; chop $sql_vals;

    my $sql = qq|INSERT INTO $baTbls{ $type }
                ( $sql_cols )
                VALUES ( $sql_vals )
                |;

    print $sql . "\n" if $opts->{debug};

    my $sth = $dbh->prepare( $sql );

    unless ( defined $sth ) {
        $opts->{LASTERROR} = "Unable to prepare 'add' $type statement\n" .
            $dbh->errstr;
        return 1;
    }

    my $paramidx = 0;
    foreach my $col (@cols) {
        $paramidx += 1;
        $sth->bind_param( $paramidx, $entry{ $col } );
    }

    unless( $sth->execute( ) ) {
        $opts->{LASTERROR} = "Unable to execute 'add' $type statement\n" .
            $sth->err;
        return 1;
    }

    $sth->finish;
    undef $sth;

    return 0;
}

=head2 add autobuild <options and args>

Where <options and args> are

    --name id              Required unique id.
    --enable | --noenable  Enable profile for use. [ default enable ]
    --description "note"   Quoted note describing this autobuild template.
    --file <path/abuild>   File to import for this autobuild template.

    --cert "distro1,distro2,etc..."  A list of supported distros, for this
                                     hardware. May be spcified multiple times.

=cut

sub add_autobuild
{
    my $command = "add";
    my $type = "autobuild";
    my $file = "";
    my %entry =
        (
         'autobuildid'  => "",
         'description'  => "",
         'data'         => "",
         'cert'         => "",
         'status'       => "t",
         'version'      => 1,
         );

    @ARGV = @_;
    GetOptions
        (
         'name=s'         => \$entry{'autobuildid'},
         'description=s'  => \$entry{'description'},
         'file=s'         => \$file,
         'enable!'        => \$entry{'status'},
         'disable'        => sub { $entry{'status'} = 0 },
         'cert=s'         => \&multiarg_handler,
         );

    if ( scalar @ARGV ) {
        $opts->{LASTERROR} = sprintf "\nUnknown arg(s): %s\n", join(" ", @ARGV);
        return 1;
    }

    &help( $opts, $cmds, $command, $type) unless ( $entry{'autobuildid'} );

    $entry{'cert'}     =
        $multiarg{ 'cert'     } if (defined $multiarg{ 'cert'     });

    my $findcount = &find_helper( $opts, $dbh, $tftph, $type, $entry{'autobuildid'} );
    unless ( defined $findcount ) {
        $opts->{LASTERROR} = "Failed in add $type.\n" . $opts->{LASTERROR};
        return 1;
    }
    if ( $findcount ) {
        $opts->{LASTERROR} = "Entry already exists:  $type $entry{'autobuildid'}.\n";
        return 1;
    }

    if ( $entry{'status'} ne "" ) {
        if ( $entry{'status'} ) {
            $entry{'status'} = "t";
        } else {
            $entry{'status'} = "f";
        }
    }

    unless ( $file ne "" ) {
        print "Need --file <file>.\n";
        &help( $opts, $cmds, $command, $type);
    }

    unless ( -f $file and -r $file ) {
        $opts->{LASTERROR} = "Unable to find or read file: $file.\n";
        return 1;
    }

    open (FILE, "<$file") || die "unable to open $file: $!\n";
    undef $/;
    $entry{'data'}=<FILE>;      # slurp mode
    $/ = "\n";
    close FILE;

    unless ( $entry{'cert'} eq "" ) {
        print "checking certs: $entry{'cert'}\n" if $opts->{debug};
        my $status = &check_distros ( $opts, $dbh, $tftph, $entry{'cert'} );
        return $status if $status; # LASTERROR set in call
    }

    my $sql_cols = lc get_cols( $baTbls{ $type } );
    $sql_cols =~ s/[ \t]*//g;
    my @cols = split( /,/, $sql_cols );
    my $sql_vals = "?," x scalar @cols; chop $sql_vals;

    my $sql = qq|INSERT INTO $baTbls{ $type }
                ( $sql_cols )
                VALUES ( $sql_vals )
                |;

    print $sql . "\n" if $opts->{debug};

    my $sth = $dbh->prepare( $sql );

    unless ( defined $sth ) {
        $opts->{LASTERROR} = "Unable to prepare 'add' $type statement\n" .
            $dbh->errstr;
        return 1;
    }

    my $paramidx = 0;
    foreach my $col (@cols) {
        $paramidx += 1;
        $sth->bind_param( $paramidx, $entry{ $col } );
    }

    unless( $sth->execute( ) ) {
        $opts->{LASTERROR} = "Unable to execute 'add' $type statement\n" .
            $sth->err;
        return 1;
    }

    $sth->finish;
    undef $sth;

    # foreign key references require that hardwareid exist before certs added

    unless ( $entry{'cert'} eq "" ) {
        my $status = &add_certs ( $type, $entry{'autobuildid'}, $entry{'cert'} );
        if ($status) {
            $opts->{LASTERROR} .= "\nAdd succeeded but certification failed.\nUse 'modify $type' next time to try adding certification again.\n";
            return $status;
        }
    }

    return 0;
}

=head2 add file <options and args>

Where <options and args> are

    --name id              Required unique id.
    --enable | --noenable  Enable file for use. [ default enable ]
    --description "note"   Quoted note describing this file.
    --file <path/prof>     File to use for this file entry.

=cut

sub add_tftp
{
    my $command = "add";
    my $type = "file";
    my $file = "";

    my %entry =
        (
         'name'         => "",
         'description'  => "",
         'bin'          => "",
         'size'         => "",
         'enabled'      => "t",
         'insertion'    => "CURRENT_TIMESTAMP",
         'change'       => "NULL",
         );

    @ARGV = @_;
    GetOptions
        (
         'name=s'         => \$entry{'name'},
         'description=s'  => \$entry{'description'},
         'file=s'         => \$file,
         'enable!'        => \$entry{'enabled'},
         'disable'        => sub { $entry{'enabled'} = 0 },
         );

    if ( scalar @ARGV ) {
        $opts->{LASTERROR} = sprintf "\nUnknown arg(s): %s\n", join(" ", @ARGV);
        return 1;
    }

    &help( $opts, $cmds, $command, $type) unless ( $entry{'name'} );

    my $findcount = &find_helper( $opts, $dbh, $tftph, $type, $entry{'name'} );
    unless ( defined $findcount ) {
        $opts->{LASTERROR} = "Failed in add $type.\n" . $opts->{LASTERROR};
        return 1;
    }
    if ( $findcount ) {
        $opts->{LASTERROR} = "Entry already exists:  $type $entry{'name'}.\n";
        return 1;
    }

    if ( $entry{'enabled'} ne "" ) {
        if ( $entry{'enabled'} ) {
            $entry{'enabled'} = "1";
        } else {
            $entry{'enabled'} = "0";
        }
    }

    unless ( $file ne "" ) {
        print "Need --file <file>.\n";
        &help( $opts, $cmds, $command, $type);
    }

    unless ( -f $file and -r $file ) {
        $opts->{LASTERROR} = "Unable to find or read file: $file.\n";
        return 1;
    }

    my $fh;
    open ($fh, "<", $file) || die "unable to open $file: $!\n";
    undef $/;
    my $content_tmp = <$fh>;    # slurp mode
    $/ = "\n";
    close $fh;

    $entry{'size'} = length( $content_tmp );

    my $sql_cols = lc get_cols( $baTbls{ $type });
    $sql_cols =~ s/[ \t]*//g;
    my @cols = split( /,/, $sql_cols );

    $sql_cols = "";
    my $sql_vals = "";
    # for the cols we are setting get rid of the autoincrement key
    foreach my $col ( @cols ) {
        next if ( $col eq "id" );
        if ( $col eq "bin" ) {
            $sql_cols .= "$col,";
            $sql_vals .= "?,";
            next;
        }
        if ( defined $entry{ $col } ) {
            $sql_cols .= "$col,";
            if ( $col eq "insertion" or $col eq "change" ) {
                $sql_vals .= qq|$entry{ $col },|;
            } else {
                $sql_vals .= qq|'$entry{ $col }',|;
            }
        }
    }
    chop $sql_cols;
    chop $sql_vals;

    my $sql = qq|INSERT INTO $baTbls{ $type }
                ( $sql_cols )
                VALUES ( $sql_vals )
                |;
    print $sql . "\n" if $opts->{debug};

    my $sth;
    unless ( $sth = $tftph->prepare( $sql ) ) {
        $opts->{LASTERROR} = "Unable to prepare 'add' $type statement\n" .
            $dbh->errstr;
        return 1;
    }

    # name in cache list or size > bfsize then
    # create a cache file in ~baracus/bfdir
    &add_bigfile( $entry{name}, $content_tmp )
        if ( is_should_bigfile( $entry{name}, $entry{size} ) );

    open ($fh, "<", $file) || die "unable to open $file: $!\n";

    my $bytes = 1;
    while ( $bytes ) {
        read $fh, $bytes, BA_DBMAXLEN;
        $entry{ 'bin' } = &bytea_encode( $bytes );
        if ( $bytes ) {
            unless ( $sth->execute( $entry{ 'bin' } ) ) {
                $opts->{LASTERROR} = "Unable to execute 'add' $type statement\n" .
                    $sth->err;
                return 1;
            }
        }
    }

    close $fh;

    $sth->finish;
    undef $sth;

    return 0;
}

# update - invoke proper update type

# this is not exactly straight forward - things to consider
#   - do we really need the trigger to convert the 'update' to an 'add'?
#   - highest version
#   - enabled version
#   - highest + 1 for new file content ( not enabled by default - disable former )
#   - specified version ( if enabled former enabled version must be disabled )

sub update
{
    my $command = "update";
    my $type = shift;
    my $status;

    $type = lc $type;
    &check_type( $opts, $command, $type );

    if    ( $type eq "hardware"  ) { $status = &update_hardware( @_ );  }
    elsif ( $type eq "module"    ) { $status = &update_module( @_ );    }
    elsif ( $type eq "profile"   ) { $status = &update_profile( @_ );   }
    elsif ( $type eq "autobuild" ) { $status = &update_autobuild( @_ ); }
    elsif ( $type eq "file"      ) { $status = &update_tftp( @_ );      }
    else {
        # should be redundant with &check_type above
        $opts->{LASTERROR} = "Invalid <type> '$type' passed for update.\n";
        return 1;
    }

    return $status;
}


=head2 update hardware <options and args>

Where <options and args> are

    --name id              Required unique id.
    --enable | --noenable  Enable module for use. [ no default ]
    --description "note"   Quoted note describing this profile.

    --addcert <distro>     Supported distros for this hardware
    --rmcert  <distro>     List of distros no longer supported

The cert options may be specified multiple times.

You may only specify the version to modify with the above params

    --version <version>    Modify parameters of an existing version.

or any of the below params to create a new version ( highest + 1 )
based on the enabled version, if found, else based on highest version.

    --bootArgs string      Quoted line of kernel boot arguments.
    --rootdisk disk        Disk device to install.
    --rootpart part        Root partition.
    --driverupdate dud     Driver update disk as added using basource.

=cut

sub update_hardware
{
    my $command = "update";
    my $type = 'hardware';

    my %entry =
        (
         'hardwareid'  => "",
         'description' => "",
         'addcert'     => "",
         'rmcert'      => "",
         'status'      => "",
         'version'     => 0,
         'bootargs'    => "",
         'rootdisk'    => "",
         'rootpart'    => "",
         'driverupdate'   => "",
         );

    @ARGV = @_;
    GetOptions
        (
         'name=s'         => \$entry{'hardwareid'},
         'description=s'  => \$entry{'description'},
         'addcert=s'      => \&multiarg_handler,
         'rmcert=s'       => \&multiarg_handler,
         'enable!'        => \$entry{'status'},
         'disable'        => sub { $entry{'status'} = 0 },
         'driverupdate=s' => \$entry{'driverupdate'},
         'bootargs=s'     => \$entry{'bootargs'},
         'rootdisk=s'     => \$entry{'rootdisk'},
         'rootpart=s'     => \$entry{'rootpart'},
         'version=i'      => \$entry{'version'},
         );

    if ( scalar @ARGV ) {
        $opts->{LASTERROR} = sprintf "\nUnknown arg(s): %s\n", join(" ", @ARGV);
        return 1;
    }

    &help( $opts, $cmds, $command, $type) unless ( $entry{'hardwareid'} );

    if ( $entry{'status'} ne "" ) {
        print "enable or noenable passed: $entry{'status'}\n" if $opts->{debug};
        if ( $entry{'status'} ) {
            $entry{'status'} = "t";
        } else {
            $entry{'status'} = "f";
        }
    }

    # fetch needed entries from db
    my ( $version_href, $highest_href, $enabled_href ) =
        &get_versions( $opts, $dbh, $type, $entry{'hardwareid'}, $entry{'version'});

    unless (defined $highest_href) {
        unless ( $opts->{LASTERROR} ) {
            $opts->{LASTERROR} =
                "Unable to update $type, entry '$entry{'hardwareid'}' not found\n";
        }
        return 1;
    }

    # multiarg creates hash entry with a space seperated list of unique values

    $entry{'addcert'} =
        $multiarg{ 'addcert' } if (defined $multiarg{ 'addcert' });
    $entry{'rmcert'}  =
        $multiarg{ 'rmcert'  } if (defined $multiarg{ 'rmcert'  });

    if ( $entry{'version'} != 0 ) {
        if ( $entry{'driverupdate'} or
             $entry{'bootargs'} or
             $entry{'rootdisk'} or
             $entry{'rootpart'}
            ) {
            $opts->{LASTERROR} = "Change of params for a particular version unsupported.\n";
            return 1;
        }
        unless (defined $version_href) {
            $opts->{LASTERROR} = "Specified version was not found.\n";
            return 1;
        }
    } elsif ( $entry{'driverupdate'} eq "" and
              $entry{'bootargs'} eq "" and
              $entry{'rootdisk'} eq "" and
              $entry{'rootpart'} eq ""
             ) {
        if ( defined $enabled_href && $enabled_href->{version} != 0) {
            print "working with enabled by default\n" if $opts->{debug};
            $entry{'version'} = $enabled_href->{version};
        } else {
            $opts->{LASTERROR} = "Need param(s) or specified --version for update.\n";
            return 1;
        }
    }

    unless ( $entry{'driverupdate'} eq "" ) {
        my $dist = { distro => "$entry{'driverupdate'}" };
        my $dref = &get_distro( $opts, $dbh, $dist );
        unless ( defined $dref ) {
            $opts->{LASTERROR} = "Unable to find driverupdate: $entry{'driverupdate'}\n";
            return 1;
        }
        if ( $dref->{type} != BA_SOURCE_DUD ) {
            $opts->{LASTERROR} = "Specified driverupdate not DUD type: $dref->{addon}\n";
            return 1;
        }
    }

    # finished processing command line

    unless ( $entry{'addcert'} eq "" ) {
        print "checking certs: $entry{'addcert'}\n" if $opts->{debug};
        my $status = &check_distros ( $opts, $dbh, $tftph, $entry{'addcert'} );
        return $status if $status; # LASTERROR set in call
    }

    unless ( $entry{'addcert'} eq "" ) {
        my $status = &add_certs("hardware", $entry{'hardwareid'}, $entry{'addcert'});
        return $status if $status; # LASTERROR set in call
    }

    unless ( $entry{'rmcert'} eq "" ) {
        my $status = &rm_certs("hardware", $entry{'hardwareid'}, $entry{'rmcert'});
        return $status if $status; # LASTERROR set in call
    }

    if ( $entry{'version'} != 0 ) {

        # true UPDATE for specified version
        # possibly two UPDATEs this 'status' changes to enable

        if ( defined $enabled_href->{'version'} &&
             $entry{'version'} != $enabled_href->{'version'} &&
             $entry{'status'} eq "t" ) {

            # disable enabled version - in preperation to enable specified version
            return 1 if &change_status( $type, "f", $enabled_href );
        }
        if ( $entry{'status'} ne "" ) {
            return 1 if &change_status( $type, $entry{'status'}, $version_href );
        }

        # the only other param to modify is description
        # because we don't support version updates for
        # other data modification is not allowed.

        if ( $entry{'description'} ne "" ) {
            return 1
                if &change_description( $type,
                                        $entry{'description'},
                                        $version_href );
        }

        # done with the genuine UPDATE of the version specified

    } else {

        # not UPDATE but instead INSERT a new version of <file> (highest ver + 1)
        # if --enable passed then may need UPDATE to disable earlier entry

        my $new_vers = $highest_href->{'version'} + 1;

        if ( defined $enabled_href->{'version'} &&
             $entry{'status'} eq "t" ) {

            # disable enabled version - in preperation to enable new version
            return 1 if &change_status( $type, "f", $enabled_href );
        }

        # use information from enabled version if found
        # and if not then highest version for all the fields
        # not provided with the new version of this file

        my $use_href;

        if ( defined $enabled_href->{'version'} ) {
            $use_href = $enabled_href;
        } else {
            $use_href = $highest_href;
        }

        while ( my ($key, $value) = each %entry ) {
            # change only what was passed
            if ( $value ) {
                $use_href->{ $key } = $value;
            }
        }

        return 1 if (&redundant_data( $opts, $dbh, $type, $entry{'hardwareid'}, $use_href ));

        # if not explicitly enabled we default to disabled
        if ( $entry{'status'} ne "t" ) {
            $use_href->{ 'status' } = "f";
        }

        # and assign the new version
        $use_href->{'version'} = $new_vers;

        # now add it

        my $sql_cols = lc get_cols( $baTbls{ $type } );
        $sql_cols =~ s/[ \t]*//g;
        my @cols = split( /,/, $sql_cols );
        my $sql_vals = "?," x scalar @cols; chop $sql_vals;

        my $sql = qq|INSERT INTO $baTbls{ $type }
                ( $sql_cols )
                VALUES ( $sql_vals )
                |;
        print $sql . "\n" if $opts->{debug};

        my $sth;
        unless ( $sth = $dbh->prepare( $sql ) ) {
            $opts->{LASTERROR} = "Unable to prepare 'update' $type statement\n" .
                $dbh->errstr;
            return 1;
        }

        my $paramidx = 0;
        foreach my $col (@cols) {
            $paramidx += 1;
            $sth->bind_param( $paramidx, $use_href->{ $col } );
        }

        # finished with last entry
        unless( $sth->execute( ) ) {
            $opts->{LASTERROR} = "Unable to execute 'update' $type statement\n" .
                $sth->err;
            return 1;
        }

        $sth->finish;
        undef $sth;
    }

    return 0;
}

=head2 update module <options and args>

Where <options and args> are

    --name id                           Required unique id, suggest script name.
    --enable | --noenable               Enable module for use. [ no default ]
    --description "note"                Quoted note describing this script.
    --addcert "distro1 distro2 etc..."  Add distro this module supports
    --rmcert  "distro1 distro2 etc..."  Remove distro support for module
    --mancert "distro1 distro2 etc..."  Make module mandatory for listed distro
    --optcert "distro1 distro2 etc..."  Make mandatory module optional for dist

you may only specify one of the following

    --file <path/script>   Create a new version with this content.
or
    --version <version>    Modify parameters of an existing version.

=cut

# dhb: thought init-scripts would support this but they don't anymore:
#
#   --interpreter value   where value is shell, perl, python  [default shell]

sub update_module
{
    my $command = "update";
    my $type = 'module';
    my $file = "";
    my $status;

    my %entry =
        (
         'moduleid'     => "",
         'description'  => "",
         'interpreter'  => "",
         'data'         => "",
         'status'       => "",
         'mancert'      => "",
         'optcert'      => "",
         'addcert'      => "",
         'rmcert'       => "",
         'version'      => 0,
         );

    @ARGV = @_;
    GetOptions
        (
         'name=s'         => \$entry{'moduleid'},
         'description=s'  => \$entry{'description'},
#         'interpreter=s'  => \$entry{'interpreter'},
         'file=s'         => \$file,
         'enable!'        => \$entry{'status'},
         'disable'        => sub { $entry{'status'} = 0 },
         'mancert=s'      => \&multiarg_handler,
         'optcert=s'      => \&multiarg_handler,
         'addcert=s'      => \&multiarg_handler,
         'rmcert=s'       => \&multiarg_handler,
         'version=i'      => \$entry{'version'},
         );

    if ( scalar @ARGV ) {
        $opts->{LASTERROR} = sprintf "\nUnknown arg(s): %s\n", join(" ", @ARGV);
        return 1;
    }

    &help( $opts, $cmds, $command, $type) unless ( $entry{'moduleid'} );

    if ( $entry{'status'} ne "" ) {
        print "enable or noenable passed: $entry{'status'}\n" if $opts->{debug};
        if ( $entry{'status'} ) {
            $entry{'status'} = "t";
        } else {
            $entry{'status'} = "f";
        }
    }

    # fetch needed entries from db
    my ( $version_href, $highest_href, $enabled_href ) =
        &get_versions( $opts, $dbh, $type, $entry{'moduleid'}, $entry{'version'});

    unless (defined $highest_href) {
        unless ( $opts->{LASTERROR} ) {
            $opts->{LASTERROR} =
                "Unable to update $type, entry '$entry{'moduleid'}' not found\n";
        }
        return 1;
    }

    # multiarg creates hash entry with a space seperated list of unique values

    $entry{'addcert'} =
        $multiarg{ 'addcert' } if (defined $multiarg{ 'addcert' });
    $entry{'optcert'} =
        $multiarg{ 'optcert' } if (defined $multiarg{ 'optcert' });
    $entry{'mancert'} =
        $multiarg{ 'mancert' } if (defined $multiarg{ 'mancert' });
    $entry{'rmcert'}  =
        $multiarg{ 'rmcert'  } if (defined $multiarg{ 'rmcert'  });

    if ( $entry{'version'} != 0 ) {
        if ( $file ) {
            $opts->{LASTERROR} = "Change of --file content for a version unsupported.\n";
            return 1;
        }
        unless (defined $version_href) {
            $opts->{LASTERROR} = "Specified version was not found.\n";
            return 1;
        }
    } elsif ( $file eq "" ) {
        if ( defined $enabled_href && $enabled_href->{version} != 0) {
            print "working with enabled by default\n" if $opts->{debug};
            $entry{'version'} = $enabled_href->{version};
        } else {
            $opts->{LASTERROR} = "Need --file <file> or specified --version for update.\n";
            return 1;
        }
    } elsif ( ! ( -f $file and -r $file ) ) {
        $opts->{LASTERROR} = "Unable to find or read: $file.\n";
        return 1;
    } else {
        open (FILE, "<$file") || die "unable to open $file: $!\n";
        undef $/;
        $entry{'data'}=<FILE>;  # slurp mode
        $/ = "\n";
        close FILE;
    }

    # finished processing command line

    unless ( $entry{'addcert'} eq "" ) {
        print "checking certs: $entry{'addcert'}\n" if $opts->{debug};
        my $status = &check_distros ( $opts, $dbh, $tftph, $entry{'addcert'} );
        return $status if $status; # LASTERROR set in call
    }

    unless ( $entry{'mancert'} eq "" ) {
        print "checking certs: $entry{'mancert'}\n" if $opts->{debug};
        my $status = &check_distros ( $opts, $dbh, $tftph, $entry{'mancert'} );
        return $status if $status; # LASTERROR set in call
    }

    unless ($entry{'addcert'} eq "" ) {
        $status = &add_certs("module", $entry{'moduleid'}, $entry{'addcert'});
        return $status if $status; # LASTERROR set in call
    }

    unless ($entry{'mancert'} eq "" ) {
        $status = &man_certs("module", $entry{'moduleid'}, $entry{'mancert'});
        return $status if $status; # LASTERROR set in call
    }

    unless ($entry{'optcert'} eq "" ) {
        $status = &opt_certs("module", $entry{'moduleid'}, $entry{'optcert'});
        return $status if $status; # LASTERROR set in call
    }

    unless ($entry{'rmcert'} eq "" ) {
        $status = &rm_certs("module", $entry{'moduleid'}, $entry{'rmcert'});
        return $status if $status; # LASTERROR set in update_cert call
    }

    if ( $entry{'version'} != 0 ) {

        # true UPDATE for specified version
        # possibly two UPDATEs this 'status' changes to enable

        if ( defined $enabled_href->{'version'} &&
             $entry{'version'} != $enabled_href->{'version'} &&
             $entry{'status'} eq "t" ) {

            # disable enabled version - in preperation to enable specified version
            return 1 if &change_status( $type, "f", $enabled_href, 1 );
        }
        if ( $entry{'status'} ne "" ) {
            return 1 if &change_status( $type, $entry{'status'}, $version_href );
        }

        # the only other param to modify is description
        # because we don't support intreperter now
        # and data modification is not allowed.

        if ( $entry{'description'} ne "" ) {
            return 1
                if &change_description( $type,
                                        $entry{'description'},
                                        $version_href );
        }

        # done with the genuine UPDATE of the version specified

    } else {

        # not UPDATE but instead INSERT a new version of <file> (highest ver + 1)
        # if --enable passed then may need UPDATE to disable earlier entry

        my $new_vers = $highest_href->{'version'} + 1;

        return 1 if (&redundant_data( $opts, $dbh, $type, $entry{'moduleid'}, $entry{'data'}));

        if ( defined $enabled_href->{'version'} &&
             $entry{'status'} eq "t" ) {

            # disable enabled version - in preperation to enable new version
            return 1 if &change_status( $type, "f", $enabled_href );
        }

        # use information from enabled version if found
        # and if not then highest version for all the fields
        # not provided with the new version of this file

        my $use_href;

        if ( defined $enabled_href->{'version'} ) {
            $use_href = $enabled_href;
        } else {
            $use_href = $highest_href;
        }

        while ( my ($key, $value) = each %entry ) {
            # change only what was passed
            if ( $value ) {
                $use_href->{ $key } = $value;
            }
        }

        # if not explicitly enabled we default to disabled
        if ( $entry{'status'} ne "t" ) {
            $use_href->{ 'status' } = "f";
        }

        # and assign the new version
        $use_href->{'version'} = $new_vers;

        # now add it

        my $sql_cols = lc get_cols( $baTbls{ $type } );
        $sql_cols =~ s/[ \t]*//g;
        my @cols = split( /,/, $sql_cols );
        my $sql_vals = "?," x scalar @cols; chop $sql_vals;

        my $sql = qq|INSERT INTO $baTbls{ $type }
                ( $sql_cols )
                VALUES ( $sql_vals )
                |;
        print $sql . "\n" if $opts->{debug};

        my $sth;
        unless ( $sth = $dbh->prepare( $sql ) ) {
            $opts->{LASTERROR} = "Unable to prepare 'update' $type statement\n" .
                $dbh->errstr;
            return 1;
        }

        my $paramidx = 0;
        foreach my $col (@cols) {
            $paramidx += 1;
            $sth->bind_param( $paramidx, $use_href->{ $col } );
        }

        # finished with last entry
        unless( $sth->execute( ) ) {
            $opts->{LASTERROR} = "Unable to execute 'update' $type statement\n" .
                $sth->err;
            return 1;
        }

        $sth->finish;
        undef $sth;
    }

    return 0;
}

=head2 update profile <options and args>

Where <options and args> are

    --name id              Required unique id.
    --enable | --noenable  Enable module for use. [ no default ]
    --description "note"   Quoted note describing this profile.

you may only specify one of the following

    --file <path/profile>  Create a new version with this content.
or
    --version <version>    Modify parameters of an existing version.

=cut

sub update_profile
{
    my $command = "update";
    my $type = 'profile';
    my $file = "";

    my %entry =
        (
         'profileid'    => "",
         'description'  => "",
         'data'         => "",
         'status'       => "",
         'version'      => 0,
         );

    @ARGV = @_;
    GetOptions
        (
         'name=s'         => \$entry{'profileid'},
         'description=s'  => \$entry{'description'},
         'file=s'         => \$file,
         'enable!'        => \$entry{'status'},
         'disable'        => sub { $entry{'status'} = 0 },
         'version=i'      => \$entry{'version'},
         );

    if ( scalar @ARGV ) {
        $opts->{LASTERROR} = sprintf "\nUnknown arg(s): %s\n", join(" ", @ARGV);
        return 1;
    }

    &help( $opts, $cmds, $command, $type) unless ( $entry{'profileid'} );

    if ( $entry{'status'} ne "" ) {
        print "enable or noenable passed: $entry{'status'}\n" if $opts->{debug};
        if ( $entry{'status'} ) {
            $entry{'status'} = "t";
        } else {
            $entry{'status'} = "f";
        }
    }

    # fetch needed entries from db
    my ( $version_href, $highest_href, $enabled_href ) =
        &get_versions( $opts, $dbh, $type, $entry{'profileid'}, $entry{'version'});

    unless (defined $highest_href) {
        unless ( $opts->{LASTERROR} ) {
            $opts->{LASTERROR} =
                "Unable to update $type, entry '$entry{'profileid'}' not found\n";
        }
        return 1;
    }

    if ( $entry{'version'} != 0 ) {
        if ( $file ) {
            $opts->{LASTERROR} = "Change of --file content for a version unsupported.\n";
            return 1;
        }
        unless (defined $version_href) {
            $opts->{LASTERROR} = "Specified version was not found.\n";
            return 1;
        }
    } elsif ( $file eq "" ) {
        if ( defined $enabled_href && $enabled_href->{version} != 0) {
            print "working with enabled by default\n" if $opts->{debug};
            $entry{'version'} = $enabled_href->{version};
        } else {
            $opts->{LASTERROR} = "Need --file <file> or specified --version for update.\n";
            return 1;
        }
    } elsif ( ! ( -f $file and -r $file ) ) {
        $opts->{LASTERROR} = "Unable to find or read: $file.\n";
        return 1;
    } else {
        open (FILE, "<$file") || die "unable to open $file: $!\n";
        undef $/;
        $entry{'data'}=<FILE>;  # slurp mode
        $/ = "\n";
        close FILE;
    }

    # finished processing command line

    if ( $entry{'version'} != 0 ) {

        # true UPDATE for specified version
        # possibly two UPDATEs this 'status' changes to enable

        if ( defined $enabled_href->{'version'} &&
             $entry{'version'} != $enabled_href->{'version'} &&
             $entry{'status'} eq "t" ) {

            # disable enabled version - in preperation to enable specified version
            return 1 if &change_status( $type, "f", $enabled_href );
        }
        if ( $entry{'status'} ne "" ) {
            return 1 if &change_status( $type, $entry{'status'}, $version_href );
        }

        # the only other param to modify is description
        # because we don't support intreperter now
        # and data modification is not allowed.

        if ( $entry{'description'} ne "" ) {
            return 1
                if &change_description( $type,
                                        $entry{'description'},
                                        $version_href );
        }

        # done with the genuine UPDATE of the version specified

    } else {

        # not UPDATE but instead INSERT a new version of <file> (highest ver + 1)
        # if --enable passed then may need UPDATE to disable earlier entry

        my $new_vers = $highest_href->{'version'} + 1;

        return 1 if (&redundant_data( $opts, $dbh, $type, $entry{'profileid'}, $entry{'data'}));

        if ( defined $enabled_href->{'version'} &&
             $entry{'status'} eq "t" ) {

            # disable enabled version - in preperation to enable new version
            return 1 if &change_status( $type, "f", $enabled_href );
        }

        # use information from enabled version if found
        # and if not then highest version for all the fields
        # not provided with the new version of this file

        my $use_href;

        if ( defined $enabled_href->{'version'} ) {
            $use_href = $enabled_href;
        } else {
            $use_href = $highest_href;
        }

        while ( my ($key, $value) = each %entry ) {
            # change only what was passed
            if ( $value ) {
                $use_href->{ $key } = $value;
            }
        }

        # if not explicitly enabled we default to disabled
        if ( $entry{'status'} ne "t" ) {
            $use_href->{ 'status' } = "f";
        }

        # and assign the new version
        $use_href->{'version'} = $new_vers;

        # now add it

        my $sql_cols = lc get_cols( $baTbls{ $type } );
        $sql_cols =~ s/[ \t]*//g;
        my @cols = split( /,/, $sql_cols );
        my $sql_vals = "?," x scalar @cols; chop $sql_vals;

        my $sql = qq|INSERT INTO $baTbls{ $type }
                ( $sql_cols )
                VALUES ( $sql_vals )
                |;
        print $sql . "\n" if $opts->{debug};

        my $sth;
        unless ( $sth = $dbh->prepare( $sql ) ) {
            $opts->{LASTERROR} = "Unable to prepare 'update' $type statement\n" .
                $dbh->errstr;
            return 1;
        }

        my $paramidx = 0;
        foreach my $col (@cols) {
            $paramidx += 1;
            $sth->bind_param( $paramidx, $use_href->{ $col } );
        }

        # finished with last entry
        unless( $sth->execute( ) ) {
            $opts->{LASTERROR} = "Unable to execute 'update' $type statement\n" .
                $sth->err;
            return 1;
        }

        $sth->finish;
        undef $sth;

    }

    return 0;
}

=head2 update autobuild <options and args>

Where <options and args> are

    --name id              Required unique id.
    --enable | --noenable  Enable module for use. [ no default ]
    --description "note"   Quoted note describing this autobuild template.

you may only specify one of the following

    --file <path/abuild>   Create a new version with this content.
or
    --version <version>    Modify parameters of an existing version.

=cut

sub update_autobuild
{
    my $command = "update";
    my $type = 'autobuild';
    my $file = "";

    my %entry =
        (
         'autobuildid' => "",
         'description' => "",
         'addcert'     => "",
         'rmcert'      => "",
         'data'        => "",
         'status'      => "",
         'version'     => 0,
         );

    @ARGV = @_;
    GetOptions
        (
         'name=s'         => \$entry{'autobuildid'},
         'description=s'  => \$entry{'description'},
         'addcert=s'      => \&multiarg_handler,
         'rmcert=s'       => \&multiarg_handler,
         'enable!'        => \$entry{'status'},
         'disable'        => sub { $entry{'status'} = 0 },
         'file=s'         => \$file,
         'version=i'      => \$entry{'version'},
         );

    if ( scalar @ARGV ) {
        $opts->{LASTERROR} = sprintf "\nUnknown arg(s): %s\n", join(" ", @ARGV);
        return 1;
    }

    &help( $opts, $cmds, $command, $type) unless ( $entry{'autobuildid'} );

    if ( $entry{'status'} ne "" ) {
        print "enable or noenable passed: $entry{'status'}\n" if $opts->{debug};
        if ( $entry{'status'} ) {
            $entry{'status'} = "t";
        } else {
            $entry{'status'} = "f";
        }
    }

    # fetch needed entries from db
    my ( $version_href, $highest_href, $enabled_href ) =
        &get_versions( $opts, $dbh, $type, $entry{'autobuildid'}, $entry{'version'});

    unless (defined $highest_href) {
        unless ( $opts->{LASTERROR} ) {
            $opts->{LASTERROR} =
                "Unable to update $type, entry '$entry{'autobuildid'}' not found\n";
        }
        return 1;
    }

    # multiarg creates hash entry with a space seperated list of unique values

    $entry{'addcert'} =
        $multiarg{ 'addcert' } if (defined $multiarg{ 'addcert' });
    $entry{'rmcert'}  =
        $multiarg{ 'rmcert'  } if (defined $multiarg{ 'rmcert'  });

    if ( $entry{'version'} != 0 ) {
        if ( $file ) {
            $opts->{LASTERROR} = "Change of --file content for a version unsupported.\n";
            return 1;
        }
        unless (defined $version_href) {
            $opts->{LASTERROR} = "Specified version was not found.\n";
            return 1;
        }
    } elsif ( $file eq "" ) {
        if ( defined $enabled_href && $enabled_href->{version} != 0) {
            print "working with enabled by default\n" if $opts->{debug};
            $entry{'version'} = $enabled_href->{version};
        } else {
            $opts->{LASTERROR} = "Need --file <file> or specified --version for update.\n";
            return 1;
        }
    } elsif ( ! ( -f $file and -r $file ) ) {
        $opts->{LASTERROR} = "Unable to find or read: $file.\n";
        return 1;
    } else {
        open (FILE, "<$file") || die "unable to open $file: $!\n";
        undef $/;
        $entry{'data'}=<FILE>;  # slurp mode
        $/ = "\n";
        close FILE;
    }

    # finished processing command line

    unless ( $entry{'addcert'} eq "" ) {
        print "checking certs: $entry{'addcert'}\n" if $opts->{debug};
        my $status = &check_distros ( $opts, $dbh, $tftph, $entry{'addcert'} );
        return $status if $status; # LASTERROR set in call
    }

    unless ( $entry{'addcert'} eq "" ) {
        my $status = &add_certs("autobuild", $entry{'autobuildid'}, $entry{'addcert'});
        return $status if $status; # LASTERROR set in call
    }

    unless ( $entry{'rmcert'} eq "" ) {
        my $status = &rm_certs("autobuild", $entry{'autobuildid'}, $entry{'rmcert'});
        return $status if $status; # LASTERROR set in call
    }

    if ( $entry{'version'} != 0 ) {

        # true UPDATE for specified version
        # possibly two UPDATEs this 'status' changes to enable

        if ( defined $enabled_href->{'version'} &&
             $entry{'version'} != $enabled_href->{'version'} &&
             $entry{'status'} eq "t" ) {

            # disable enabled version - in preperation to enable specified version
            return 1 if &change_status( $type, "f", $enabled_href );
        }
        if ( $entry{'status'} ne "" ) {
            return 1 if &change_status( $type, $entry{'status'}, $version_href );
        }

        # the only other param to modify is description
        # because we don't support intreperter now
        # and data modification is not allowed.

        if ( $entry{'description'} ne "" ) {
            return 1
                if &change_description( $type,
                                        $entry{'description'},
                                        $version_href );
        }

        # done with the genuine UPDATE of the version specified

    } else {

        # not UPDATE but instead INSERT a new version of <file> (highest ver + 1)
        # if --enable passed then may need UPDATE to disable earlier entry

        my $new_vers = $highest_href->{'version'} + 1;

        return 1 if (&redundant_data( $opts, $dbh, $type, $entry{'autobuildid'}, $entry{'data'}));

        if ( defined $enabled_href->{'version'} &&
             $entry{'status'} eq "t" ) {

            # disable enabled version - in preperation to enable new version
            return 1 if &change_status( $type, "f", $enabled_href );
        }

        # use information from enabled version if found
        # and if not then highest version for all the fields
        # not provided with the new version of this file

        my $use_href;

        if ( defined $enabled_href->{'version'} ) {
            $use_href = $enabled_href;
        } else {
            $use_href = $highest_href;
        }

        while ( my ($key, $value) = each %entry ) {
            # change only what was passed
            if ( $value ) {
                $use_href->{ $key } = $value;
            }
        }

        # if not explicitly enabled we default to disabled
        if ( $entry{'status'} ne "t" ) {
            $use_href->{ 'status' } = "f";
        }

        # and assign the new version
        $use_href->{'version'} = $new_vers;

        # now add it

        my $sql_cols = lc get_cols( $baTbls{ $type } );
        $sql_cols =~ s/[ \t]*//g;
        my @cols = split( /,/, $sql_cols );
        my $sql_vals = "?," x scalar @cols; chop $sql_vals;

        my $sql = qq|INSERT INTO $baTbls{ $type }
                ( $sql_cols )
                VALUES ( $sql_vals )
                |;
        print $sql . "\n" if $opts->{debug};

        my $sth;
        unless ( $sth = $dbh->prepare( $sql ) ) {
            $opts->{LASTERROR} = "Unable to prepare 'update' $type statement\n" .
                $dbh->errstr;
            return 1;
        }

        my $paramidx = 0;
        foreach my $col (@cols) {
            $paramidx += 1;
            $sth->bind_param( $paramidx, $use_href->{ $col } );
        }

        # finished with last entry
        unless( $sth->execute( ) ) {
            $opts->{LASTERROR} = "Unable to execute 'update' $type statement\n" .
                $sth->err;
            return 1;
        }

        $sth->finish;
        undef $sth;

    }

    return 0;
}

=head2 update file <options and args>

Where <options and args> are

    --name id              Required unique id, suggest script name.
    --enable | --noenable  Enable module for use. [ no default ]
    --description "note"   Quoted note describing this script.
    --file <path/script>   Create a new version with this content.

There is no support for versioning of these files.

=cut

sub update_tftp
{
    my $command = "update";
    my $type = 'file';
    my $file = "";

    my $sth;
    my $sql;
    my $href;
    my %save;

    my %entry =
        (
         'name'         => "",
         'description'  => "",
         'size'         => "",
         'enabled'      => "",
         'insertion'    => "",
         'change'       => "CURRENT_TIMESTAMP",
         );

    @ARGV = @_;
    GetOptions
        (
         'name=s'         => \$entry{'name'},
         'description=s'  => \$entry{'description'},
         'file=s'         => \$file,
         'enable!'        => \$entry{'enabled'},
         'disable'        => sub { $entry{'enabled'} = 0 },
         );

    if ( scalar @ARGV ) {
        $opts->{LASTERROR} = sprintf "\nUnknown arg(s): %s\n", join(" ", @ARGV);
        return 1;
    }

    &help( $opts, $cmds, $command, $type) unless ( $entry{'name'} );

    my $findcount = &find_helper( $opts, $dbh, $tftph, $type, $entry{'name'} );
    unless ( defined $findcount ) {
        $opts->{LASTERROR} = "Failed in update $type.\n" . $opts->{LASTERROR};
        return 1;
    }
    unless ( $findcount ) {
        $opts->{LASTERROR} = "Entry not found for update: $type $entry{'name'}.\n";
        return 1;
    }

    # get the current description and enabled states to see if we need to change
    my $sql_cols = lc get_cols( $baTbls{ $type } );
    $sql_cols =~ s/[ \t]*//g;

    $sql = qq|SELECT $sql_cols
              FROM $baTbls{ $type }
              WHERE name = '$entry{'name'}'
              ORDER BY id
              |;
    print "$sql\n" if $opts->{debug};

    unless ( $sth = $tftph->prepare( $sql ) ) {
        $opts->{LASTERROR} = "Unable to prepare 'update' $type query\n" .
            $tftph->errstr;
        return 1;
    }

    unless ( $sth->execute(  ) ) {
        $opts->{LASTERROR} = "Unable to execute 'update' $type query\n" .
            $sth->err;
        return 1;
    }

    my $entry_data;
    my $rowcount = 0;
    my %id;

    # store off first entry
    while ( $href = $sth->fetchrow_hashref( ) ) {
        while ( my ($key, $val) = each %{$href} ) {
            if ( $opts->{debug} ) {
                if ( defined $val ) {
                    print "update $rowcount - $href->{'id'} entry:  $key => $val\n";
                } else {
                    print "update $rowcount - $href->{'id'} entry:  $key => 'undef'\n";
                }
            }
        }
        unless ( $rowcount ) {
            while ( my ($key, $val) = each %{$href} ) {
                next if ( $key eq "bin" );
                    $save{ $key } = $val;
                if ( $opts->{debug} and defined $val ) {
                        print "1 entry $href->{'id'}:  $key => $val\n";
                    } elsif ( $opts->{debug} ) {
                        print "2 entry $href->{'id'}:  $key =>\n";
                    }
                }
            }
        $rowcount += 1;
        $id{ $href->{'id'} } = $href->{'id '};
        $entry_data .= &bytea_decode( $href->{'bin'} );
    }

    my $desc_needed = 0;
    my $stat_needed = 0;

    if ( defined $entry{'enabled'} && $entry{'enabled'} ne "" ) {
        if ( ! defined $save{'enabled'} or
             ( $save{'enabled'} ne $entry{'enabled'} ) ) {
            $save{'enabled'} = $entry{'enabled'};
            $stat_needed = 1;
        }
    }

    if ( defined $entry{'description'} && $entry{'description'} ne "" ) {
        if ( ! defined $save{'description'} or
             ( "$save{'description'}" ne "$entry{'description'}" ) ) {
            $save{'description'} = $entry{'description'};
            $desc_needed = 1;
        }
    }

    # if first change we don't have a value in entry.
    $save{'change'} = $entry{'change'};

    # determine if updating file now so can remove all entries -
    # then add data entries - after update status and desc if needed

    if ( $file ne "" ) {

        if ( ! ( -f $file and -r $file ) ) {
            $opts->{LASTERROR} = "Unable to find or read: $file.\n";
            return 1;
        }

        # grab file content for comparison with current entry 'data'

        # slurp carefully
        # the use of local() sets $/ to undef and when the scope exits
        # it will revert $/ back to its previous value (most likely ``\n'')
        open( my $fdh, "<$file" ) or die "Unable to open $file: $!\n";
        my $file_data = do { local( $/ ) ; <$fdh> } ;
        close $fdh;

        $save{'size'} = length ( $file_data );

        # must check first - for refusing to 'update' with duplicate data
        if ( $file_data eq $entry_data ) {
            $opts->{LASTERROR} = "Reject updating file entry with duplicate data\n";
            return 1;
        }

        # delete all old entries
        my $delete = qq|DELETE FROM $baTbls{$type} where name = '$entry{'name'}'|;
        unless ( $sth = $tftph->do( $delete ) ) {
            $opts->{LASTERROR} = "Unable to do 'update' $type removal\n" .
                $tftph->errstr;
            return 1;
        }

        my $sql_cols = lc get_cols( $baTbls{ $type });
        $sql_cols =~ s/[ \t]*//g;
        my @cols = split( /,/, $sql_cols );

        my $sql_vals = "";
        $sql_cols = "";

        foreach my $col ( @cols ) {
            next if ( $col eq "id" ); # do not include autoincr key in sql
            if ( $col eq "bin" ) {
                $sql_cols .= "$col,";
                $sql_vals .= "?,";
                next;
            }
            if ( defined $save{ $col } and $save{ $col } ne "" ) {
                $sql_cols .= "$col,";
                if ( $col eq "change" ) {
                    # do not quote CURRENT_TIMESTAMP or other sql expressions
                    $sql_vals .= qq|$save{ $col },|;
                } else {
                    $sql_vals .= qq|'$save{ $col }',|;
                }
            }
        }
        chop $sql_cols;
        chop $sql_vals;

        $sql = qq|INSERT INTO $baTbls{ $type }
                  ( $sql_cols )
                  VALUES ( $sql_vals )
                 |;
        print $sql . "\n" if $opts->{debug};

        unless ( $sth = $tftph->prepare( $sql ) ) {
            $opts->{LASTERROR} = "Unable to prepare 'update' $type statement\n" .
                $dbh->errstr;
            return 1;
        }

        # name in cache list or size > bfsize then
        # create a cache file in ~baracus/bfdir
        &add_bigfile( $entry{name}, $file_data )
            if ( is_should_bigfile( $entry{name}, $save{size} ) );

        # open the file again to be read in by chunks to store
        my $fh;
        open ($fh, "<", $file) || die "unable to open $file: $!\n";

        my $bytes = 1;
        while ( $bytes ) {
            read $fh, $bytes, BA_DBMAXLEN;
            $file_data = &bytea_encode( $bytes );
            if ( $bytes ) {
                unless ( $sth->execute( $file_data ) ) {
                    $opts->{LASTERROR} = "Unable to execute 'update' $type statement\n"
                        . $sth->err;
                    return 1;
                }
            }
        }

        close $fh;

    } elsif ( $desc_needed == 1 || $stat_needed == 1 ) {

        # no file to play with so only updating the status, description, or both
        my $sql_cols;
        my $sql_vals;

        if ( $desc_needed == 1 ) {
            $sql_cols .= "description";
            $sql_vals .= "'$save{'description'}'";
        }
        if ( $stat_needed == 1 ) {
            $sql_cols .= "," if ( $sql_cols );
            $sql_vals .= "," if ( $sql_vals );
            $sql_cols .= "enabled";
            $sql_vals .= "'$save{'enabled'}'";
        }
        $sql_cols .= ",insertion,change";
        $sql_vals .= ",'$save{'insertion'}', CURRENT_TIMESTAMP";

        $sql = qq|UPDATE $baTbls{ $type }
                  SET ( $sql_cols ) = ( $sql_vals )
                  WHERE id = ?|;

        unless ( $sth = $tftph->prepare( $sql ) ) {
            $opts->{LASTERROR} = "Unable to prepare 'update' statement\n" .
                $tftph->errstr;
            return 1;
        }

        foreach my $id ( sort keys %id ) {
            print "update id $id\n" if $opts->{debug};
            unless ( $sth->execute( $id ) ) {
                $opts->{LASTERROR} = "Unable to execute 'update' statement\n" .
                    $sth->err;
                return 1;
            }
        }
    }

    $sth->finish;
    undef $sth;

    return 0;
}


=head2 export E<lt>typeE<gt> <options and args>

Where <type> may be module, profile, autobuild, or file.  Not hardware.

For similar functionality with hardware type it is suggested to simply redirect the 'detail <type>' output.  With hardware there is no related '--file' argument used to add or update it.

Where <options and args> are

    --name <name>          Data of named entry used. [ default <file> ].
    --file <path/file>     Create this new flie with db data contents.
    --version <ver>        Required for module, profile, or autobuild types.

=cut

sub export
{
    my $command = "export";
    my $type = shift;
    my $file = "";

    my %entry =
        (
         'name'         => "",
         'version'      => "0",  # not used for tftp
         'description'  => "",
         );

    @ARGV = @_;
    GetOptions
        (
         'name=s'         => \$entry{'name'},
         'description=s'  => \$entry{'description'},
         'version=i'      => \$entry{'version'},
         'file=s'         => \$file,
         );

    if ( scalar @ARGV ) {
        $opts->{LASTERROR} = sprintf "\nUnknown arg(s): %s\n", join(" ", @ARGV);
        return 1;
    }

    unless ( $file ne "" ) {
        print "Required --file <path/file> not provided.\n";
        &help( $opts, $cmds, $command );
    }

    if ( $entry{'name'} eq "" ) {
        $entry{'name'} = $file;
        $entry{'name'} =~ s|.*/||; # only the short name for the lookup
    }

    if ( $type eq "file" ) {
        if ( $entry{'version'} ne "0" ) {
            print "Ignoring unsupported --version specifier for type $type\n";
        }
        my $tmpref = &find_tftpfile( $opts, $tftph, $entry{name} );
        unless ( defined $tmpref ) {
            unless ( $opts->{LASTERROR} ) {
                $opts->{LASTERROR} =
                    "Unable to find $type entry for $entry{name}\n";
            }
            return 1;
        }
    } else {
        # fetch needed entries from db
        my ( $version_href, $highest_href, $enabled_href ) =
            &get_versions( $opts, $dbh, $type, $entry{name}, $entry{version});

        unless (defined $highest_href) {
            unless ( $opts->{LASTERROR} ) {
                $opts->{LASTERROR} =
                    "Unable to find $type entry for $entry{name}\n";
            }
            return 1;
        }

        if ( $entry{'version'} ne "0" && ! defined $version_href ) {
            $opts->{LASTERROR} =
                "Specified version was not found.\n";
            return 1;
        }

        if ( $entry{'version'} eq "0" ) {
            if ( defined $enabled_href && $enabled_href->{version} != 0) {
                print "working with enabled by default, version $enabled_href->{version}\n" if $opts->{debug};
                $entry{'version'} = $enabled_href->{version};
            } else {
                $opts->{LASTERROR} = "Neither --version <ver> provided, nor 'enabled' entry found.\n";
                return 1;
            }
        }
    }

    if ( -e $file ) {
        $opts->{LASTERROR} = "Unwilling to overwrite existing <path/file>: $file\n";
        return 1;
    }

    my $sql;
    my $db2use = $dbh;

    if ( $type eq "module" ) {
        $sql = qq|SELECT data
                  FROM $baTbls{ $type }
                  WHERE moduleid = '| . $entry{'name'} . qq|'
                  AND version = '| . $entry{'version'} . qq|'|;
    } elsif ( $type eq "profile" ) {
        $sql = qq|SELECT data
                  FROM $baTbls{ $type }
                  WHERE profileid = '| . $entry{'name'} . qq|'
                  AND version = '| . $entry{'version'} . qq|'|;
    } elsif ( $type eq "autobuild" ) {
        $sql = qq|SELECT data
                  FROM $baTbls{ $type }
                  WHERE autobuildid = '| . $entry{'name'} . qq|'
                  AND version = '| . $entry{'version'} . qq|'|;
    } elsif ( $type eq "file" ) {
        $sql = qq|SELECT bin as data
                  FROM $baTbls{ $type }
                  WHERE name = '| . $entry{'name'} . qq|'
                  ORDER BY id
                 |;
        $db2use = $tftph;
    } else {
        $opts->{LASTERROR} = "Unsuported type $type for export command\n";
        return 1;
    }

    my $sth;
    my $href;

    unless ( $sth = $db2use->prepare( $sql ) ) {
        $opts->{LASTERROR} = "Unable to prepare 'export' statement\n" . $db2use->errstr;
        return 1;
    }

    unless( $sth->execute( ) ) {
        $opts->{LASTERROR} = "Unable to execute 'export' statement\n" . $sth->err;
        return 1;
    }

    open( my $fh, ">", $file ) or die "Unable to open $file: $!\n";

    my $rowcount = 0;
    my $row;
    while ( $href = $sth->fetchrow_hashref( ) ) {
        $rowcount += 1;
        if ( $type ne "file" ) {
            $row = $href->{'data'};
        } else {
            $row = &bytea_decode ( $href->{'data'} );
        }
        print $fh $row;
    }
    close $fh;

    $sth->finish;
    undef $sth;

    return 0
}

=head2 detail E<lt>typeE<gt> E<lt>nameE<gt> [ --all | --version <ver> ]

Takes <type> and <name> as arguments.

Fetch and display the details about the entry in the type database.

Only the 'enabled' version is shown unless --all or --version specified.

  --all            all module, profile, autobuild, hardware versions displayed.

or
  --version <ver>  show specified version (by default show enabled version).

=cut

sub detail
{
    my $command = "detail";
    my $type = shift;
    my $name = shift;
    my $href;
    my $version = 0;

    @ARGV=@_;
    if ( ( $type eq "module" )    ||
         ( $type eq "profile" )   ||
         ( $type eq "autobuild" ) ||
         ( $type eq "hardware" )
        ) {
        GetOptions(
                   'version=i'      => \$version,
                   );
    }
    if ( scalar @ARGV ) {
        $opts->{LASTERROR} = sprintf "\nUnknown arg(s): %s\n", join(" ", @ARGV);
        return 1;
    }

    if ( $version && $opts->{all} ) {
        $opts->{LASTERROR} = "Please use either --all _or_ --version <ver>. Not both.\n";
        return 1;
    }

    $type = lc $type;
    &check_type( $opts, $command, $type );

    unless ( defined $name) {
        &help( $opts, $cmds, $command );
    }
    $name =~ s|.*/||;           # only the short name for the lookup

    my $sql_cols = lc get_cols( $baTbls{ $type });
    my $sql = qq| SELECT $sql_cols FROM $baTbls{ $type } |;
    my $sql_cert;
    my $sql_man;
    my $sql_chunks;

    my $db2use = $dbh;

    if ( $type eq "hardware" ) {
        $sql .= "WHERE hardwareid = ?";
        $sql .= " AND version = $version" if ( $version );

        # $sql_cols = lc get_cols( $baTbls{ 'hwcert' } );
        $sql_cert = qq|SELECT distroid
                       FROM $baTbls{ 'hwcert' }
                       WHERE hardwareid = '| . $name . qq|'|;
    } elsif ( $type eq "module" ) {
        $sql .= "WHERE moduleid = ?";
        $sql .= " AND version = $version" if ( $version );

        $sql_cert = qq|SELECT distroid
                       FROM $baTbls{ 'modcert' }
                       WHERE moduleid = '| . $name . qq|'|;

        $sql_man = qq|SELECT distroid
                       FROM $baTbls{ 'modcert' }
                       WHERE moduleid = '| . $name . qq|'
                       AND mandatory = 't'|;
    } elsif ( $type eq "profile" ) {
        $sql .= "WHERE profileid = ?";
        $sql .= " AND version = $version" if ( $version );
    } elsif ( $type eq "autobuild" ) {
        $sql .= "WHERE autobuildid = ?";
        $sql .= " AND version = $version" if ( $version );

        # $sql_cols = lc get_cols( $baTbls{ 'abcert' } );
        $sql_cert = qq|SELECT distroid
                       FROM $baTbls{ 'abcert' }
                       WHERE autobuildid = '| . $name . qq|'|;
    } elsif ( $type eq "file" ) {
        $sql .= "WHERE name = ?";
        $sql .= " ORDER BY id";

        $sql_chunks = qq|SELECT bin
                         FROM $baTbls{ $type }
                         WHERE name =  '| . $name .
                             qq|' ORDER BY id|;

        $db2use = $tftph;
    }

    print $sql      . "\n" if $opts->{debug};
    print $sql_cert . "\n" if $opts->{debug};
    print $sql_man  . "\n" if $opts->{debug};

    my $sth;
    unless ( $sth = $db2use->prepare( $sql ) ) {
        $opts->{LASTERROR} = "Unable to prepare 'detail' statement\n" . $db2use->errstr;
        return 1;
    }

    unless( $sth->execute( $name ) ) {
        $opts->{LASTERROR} = "Unable to execute 'detail' statement\n" . $sth->err;
        return 1;
    }

    my $rowcount = 0;
    my $printcount = 0;
    my $tftpname = "";
    my $tftpdata;

    while ( $href = $sth->fetchrow_hashref( ) ) {
        $rowcount += 1;
        if ( $type eq "hardware" ) {
            if ( $opts->{all} ||
                 $href->{'status'} ||
                 $href->{'version'} == $version ) {
                print "# ba #####\n" if $opts->{all};
                unless ( $opts->{nolabels} ) {
                    print  "# ba BEGIN  Baracus Informational Header  BEGIN\n";
                    printf "# ba hardware=%s\n",    $href->{'hardwareid'};
                    printf "# ba description=%s\n", $href->{'description'};
                    printf "# ba enabled=%s\n",     $href->{'status'}
                        if ( $opts->{all} || $version );
                    printf "# ba version=%s\n",     $href->{'version'};
                }
                my $hwcert = "";
                my $certs = $db2use->selectall_arrayref( $sql_cert, { slice => {} } );
                if ( defined $certs ) {
                    foreach my $cert ( @{$certs} ) {
                        $hwcert .= "," if ( $hwcert ne "" );
                        $hwcert .= @{$cert}[0];
                    }
                    printf "# ba cert=%s\n", $hwcert if ( defined $hwcert );
                    print  "# ba END    Baracus Informational Header    END\n";
                }
                printf "bootArgs=%s\n",     $href->{'bootargs'};
                printf "rootDisk=%s\n",     $href->{'rootdisk'};
                printf "rootPart=%s\n",     $href->{'rootpart'};
                printf "driverupdate=%s\n", $href->{'driverupdate'};
                $printcount += 1;
            }
        } elsif ( $type eq "module" ) {
            if ( $opts->{all} ||
                 $href->{'status'} ||
                 $href->{'version'} == $version ) {
                print "# ba #####\n" if $opts->{all};
                unless ( $opts->{nolabels} ) {
                    print  "# ba BEGIN  Baracus Informational Header  BEGIN\n";
                    printf "# ba module=%s\n",      $href->{'moduleid'};
                    printf "# ba description=%s\n", $href->{'description'};
                    #printf "# ba interpreter=%s\n", $href->{'interpreter'};
                    printf "# ba enabled=%s\n",     $href->{'status'}
                        if ( $opts->{all} || $version );
                    printf "# ba version=%s\n",     $href->{'version'};
                }

                my $modcert = "";
                my $certs = $db2use->selectall_arrayref( $sql_cert, { slice => {} } );
                if ( defined $certs ) {
                    foreach my $cert ( @{$certs} ) {
                        $modcert .= "," if ( $modcert ne "" );
                        $modcert .= @{$cert}[0];
                    }
                }
                unless ( $opts->{nolabels} ) {
                    printf "# ba cert=%s\n", $modcert if ( defined $modcert );
                }
                my $modman = "";
                my $mancerts = $db2use->selectall_arrayref( $sql_man, { slice => {} } );
                if ( defined $mancerts ) {
                    foreach my $mancert ( @{$mancerts} ) {
                        $modman .= ":" if ( $modman ne "" );
                        $modman .= @{$mancert}[0];
                    }
                }
                unless ( $opts->{nolabels} ) {
                    printf "# ba mandatory=%s\n", $modman if ( defined $modman );
                    print  "# ba END    Baracus Informational Header    END\n";
                }
                print  $href->{'data'};
                $printcount += 1;
            }
        } elsif ( $type eq "profile" ) {
            if ( $opts->{all} ||
                 $href->{'status'} ||
                 $href->{'version'} == $version ) {
                print "# ba #####\n" if $opts->{all};
                unless ( $opts->{nolabels} ) {
                    print  "# ba BEGIN  Baracus Informational Header  BEGIN\n";
                    printf "# ba profile=%s\n",     $href->{'profileid'};
                    printf "# ba description=%s\n", $href->{'description'};
                    printf "# ba enabled=%s\n",     $href->{'status'}
                        if ( $opts->{all} || $version );
                    printf "# ba version=%s\n",     $href->{'version'};
                    print  "# ba END    Baracus Informational Header    END\n";
                }
                print  $href->{'data'};
                $printcount += 1;
            }
        } elsif ( $type eq "autobuild" ) {
            if ( $opts->{all} ||
                 $href->{'status'} ||
                 $href->{'version'} == $version ) {
                print "# ba #####\n" if $opts->{all};
                unless ( $opts->{nolabels} ) {
                    print  "# ba BEGIN  Baracus Informational Header  BEGIN\n";
                    print  "# ba Baracus Informational Header ('#ba ' prefix) - begin\n";
                    printf "# ba autobuild=%s\n",   $href->{'autobuildid'};
                    printf "# ba description=%s\n", $href->{'description'};
                    printf "# ba enabled=%s\n",     $href->{'status'}
                        if ( $opts->{all} || $version );
                    printf "# ba version=%s\n",     $href->{'version'};
                }

                my $abcert = "";
                my $certs = $db2use->selectall_arrayref( $sql_cert, { slice => {} } );
                if ( defined $certs ) {
                    foreach my $cert ( @{$certs} ) {
                        $abcert .= "," if ( $abcert ne "" );
                        $abcert .= @{$cert}[0];
                    }
                }
                unless ( $opts->{nolabels} ) {
                    printf "# ba cert=%s\n", $abcert if ( defined $abcert );
                    print  "# ba END    Baracus Informational Header    END\n";
                }

                print  $href->{'data'};
                $printcount += 1;
            }
        } elsif ( $type eq "file" ) {
            # because tftp file data is stored in chunks
            # need to collect the chunks before output
            if ( $tftpname ne $name ) {
                $tftpname = $name;
                # first time for 'name' encountered - get chunks
                my $chunks = $db2use->selectall_arrayref($sql_chunks,{slice=>{}});
                if ( defined $chunks and ( $opts->{all} or $href->{'enabled'} ) ) {
                    unless ( $opts->{nolabels} ) {
                        print  "# ba BEGIN Baracus Informational Header BEGIN\n";
                        printf "# ba name=%s\n",     $href->{'name'};
                        printf "# ba description=%s\n", $href->{'description'};
                        printf "# ba enabled=%s\n",     $href->{'enabled'};
                        printf "# ba inserted=%s\n",    $href->{'insertion'}
                            if $opts->{verbose};
                        printf "# ba modified=%s\n",    $href->{'change'}
                            if ( defined $href->{'change'} && $opts->{verbose} );
                        print  "# ba END    Baracus Informational Header    END\n";
                    }
                    foreach my $chunk ( @{$chunks} ) {
                        $tftpdata .= &bytea_decode( @{$chunk}[0] );
                    }
                    if ( $href->{'size'} < 512    or
                         $name =~ m/\.inventory$/ or
                         $name =~ m/\.diskwipe$/   or
                         $name =~ m/\.clone$/   or
                         $name =~ m/\.mcast$/   or
                         $name =~ m/\.image$/        ) {
                        print $tftpdata;
                    } else {
                        print "\n# File content will not be displayed.\n";
                        print "# file is large and may be binary.\n";
                        print "# please use the 'export' command.\n";
                    }
                    $printcount += 1;
                }
            }
        }
    }

    $sth->finish;
    undef $sth;

    if ( $printcount == 0 ) {
        $opts->{LASTERROR} = "No $type named $name";
        $opts->{LASTERROR} .= " *enabled* entry" if ( $rowcount && ! $version );
        $opts->{LASTERROR} .= " version $version" if ( $version );
        $opts->{LASTERROR} .= " found in db\n";
        return 1;
    }

    return 0;
}

=head2 remove E<lt>typeE<gt> E<lt>nameE<gt> [--version <ver>]

Takes <type> and <name> as arguments.

Removes all versions of <name> specified from <type> database.

Option --version <ver> can be provided to remove only the version specified.

=cut

sub remove
{
    my $command = "remove";
    my $type = shift;
    my $name = shift;
    my $version;

    my $optcert = "";
    my $rmcert = "";

    $type = lc $type;
    &check_type( $opts, $command, $type );

    unless ( defined $name) {
        &help( $opts, $cmds, $command );
    }
    $name =~ s|.*/||;           # only the short name for the lookup

    @ARGV = @_;
    if ( ( $type eq "hardware"  ) ||
         ( $type eq "module"    ) ||
         ( $type eq "profile"   ) ||
         ( $type eq "autobuild" )
        ) {
        GetOptions(
                   'version=i' => \$version,
                   );
    }
    if ( ( $type eq "hardware"  ) ||
         ( $type eq "module"    ) ||
         ( $type eq "autobuild" )
        ) {
        GetOptions(
                   'optcert=s' => \&multiarg_handler,
                   'rmcert=s'  => \&multiarg_handler,
                   );

        $optcert =
            $multiarg{ 'optcert' } if (defined $multiarg{ 'optcert' });
        $rmcert  =
            $multiarg{ 'rmcert'  } if (defined $multiarg{ 'rmcert'  });

        unless ( $optcert eq "" ) {
            $status = &opt_certs( $type, $name, $optcert );
            return $status if $status; # LASTERROR set in call
        }

        unless ($rmcert eq "" ) {
            $status = &rm_certs( $type, $name, $rmcert );
            return $status if $status; # LASTERROR set in update_cert call
        }
    }
    if ( scalar @ARGV ) {
        $opts->{LASTERROR} = sprintf "\nUnknown arg(s): %s\n", join(" ", @ARGV);
        return 1;
    }

    my @sql;

    my $db2use = $dbh;


    # hackish things always deserve comments
    # the SQL arrays for lumping in certs here
    # need special handling for removing a single version
    # and if only removing a single version we don't remove certs....

    if ( $type eq "hardware" ) {
        @sql =
            (
             qq|DELETE FROM $baTbls{ $type  } WHERE hardwareid = ?|,
             qq|DELETE FROM $baTbls{ hwcert } WHERE hardwareid = ?|
             );
    } elsif ( $type eq "module" ) {
        my $check = &check_mandatory( $opts, $dbh, $name );
        unless( defined $check ) {
            # call failed
            $opts->{LASTERROR} = "Failed in remove.\n" . $opts->{LASTERROR};
            return 1;
        }
        if ( $check ) {
            $opts->{LASTERROR} = "Cannot remove module marked as mandatory\n";
            return 1;
        }
        @sql =
            (
             qq|DELETE FROM $baTbls{ $type   } WHERE moduleid = ?|,
             qq|DELETE FROM $baTbls{ modcert } WHERE moduleid = ?|
             );
    } elsif ( $type eq "profile" ) {
        @sql =
            (
             qq|DELETE FROM $baTbls{ $type } WHERE profileid = ?|
             );
    } elsif ( $type eq "autobuild" ) {
        @sql =
            (
             qq|DELETE FROM $baTbls{ $type  } WHERE autobuildid = ?|,
             qq|DELETE FROM $baTbls{ abcert } WHERE autobuildid = ?|
             );
    } elsif ( $type eq "file" ) {
        @sql =
            (
             qq|DELETE FROM $baTbls{ $type } WHERE name = ?|
             );
        $db2use = $tftph;
    }

    my $findcount = &find_helper( $opts, $dbh, $tftph, $type, $name );

    unless ( defined $findcount ) {
        # &find call failed
        $opts->{LASTERROR} = "Failed in remove\n" . $opts->{LASTERROR};
        return 1;
    }

    if ( $findcount ) {

        my $count=0;
        foreach my $sql (@sql) {
            $count++;

            # do this if version specified
            if ( defined $version and $count == 1 ) {
                $sql .= " AND version = $version";
            }

            # and skip all but main tbl (first sql in array) if version
            # unless this is the last version in the table
            if ( defined $version and $count != 1 and $findcount != 1 ) {
                next;
            }

            my $sth;
            unless ( $sth = $db2use->prepare( $sql ) ) {
                $opts->{LASTERROR} = "Unable to prepare 'remove' statement\n" . $db2use->errstr;
                return 1;
            }

            # was is_should_bigfile then file in ~baracus/bfdir - remove it
            &remove_bigfile( $name ) if ( $type eq "file" );

            unless ( $sth->execute( $name ) ) {
                $opts->{LASTERROR} = "Unable to execute 'remove' statement\n" . $sth->err;
                return 1;
            }

            $sth->finish;
            undef $sth;
        }
    }

    return 0;
}

###########################################################################
#
# baconfig specific aux commands
#

# return 1 if ok to proceed with help on sub-type (perl doc for $cmd $type avail)
# return 0 if no sub-type help available so generic help presented

sub check_type_help
{
    my $opts    = shift;
    my $command = shift;
    my $type    = shift;

    if ( defined $type and ( $command eq "add" or $command eq "update" ) ) {
        return 1;
    } else {
        return 0;
    }
}
sub check_type
{
    my $opts = shift;
    my $command = shift;
    my $type = shift;

    my $cmd_list  = join(", ", keys %{$cmds} );
    my $type_list = join(", ", @types );

    return $type_list if ( defined $type and $type eq "all" );

    unless ( defined $command ) {
        print "requires <command> (e.g. $cmd_list )\n";
        exit 1;
    }
    unless ( defined $type ) {
        print "requires <type> (e.g. $type_list)\n";
        exit 1;
    }
    unless ( defined $baTbls{ $type } ) {
        print "invalid <type> '$type' please use: $type_list\n";
        exit 1;
    }

    return $type;
}

sub list_start
{
    my $type  = shift;
    my $name  = shift;

    my $sql;

    my $db2use = $dbh;

    if ( $type eq "hardware" ) {
        $sql = qq|SELECT hardwareid as name, description, version, status
                  FROM $baTbls{ $type }
                  WHERE hardwareid LIKE ?
                  ORDER BY name, version
                 |;
    } elsif ( $type eq "module" ) {
        $sql = qq|SELECT moduleid as name, description, version, status
                  FROM $baTbls{ $type }
                  WHERE moduleid LIKE ? ORDER BY moduleid, version
                 |;
    } elsif ( $type eq "profile" ) {
        $sql = qq|SELECT profileid as name, description, version, status
                  FROM $baTbls{ $type }
                  WHERE profileid LIKE ? ORDER BY profileid, version
                 |;
    } elsif ( $type eq "autobuild" ) {
        $sql = qq|SELECT autobuildid as name, description, version, status
                  FROM $baTbls{ $type }
                  WHERE autobuildid LIKE ?
                  ORDER BY autobuildid, version
                 |;
    } elsif ( $type eq "file" ) {
        $sql = qq|SELECT name, description, COUNT( id ) as count, enabled
                  FROM $baTbls{ $type }
                  WHERE name LIKE ?
                  GROUP BY name, description, enabled
                  ORDER BY name
                 |;
        $db2use = $tftph;
    }

    print $sql . "\n" if $opts->{debug};

    my $sth;
    unless ( $sth = $db2use->prepare( $sql ) ) {
        $opts->{LASTERROR} = "Unable to prepare 'list' query\n" . $db2use->errstr;
        return undef;
    }

    unless( $sth->execute( $name ) ) {
        $opts->{LASTERROR} = "Unable to execute 'list' query" . $sth->err;
        return undef;
    }

    return $sth;
}

sub list_next
{
    my $sth = shift;

    return $sth->fetchrow_hashref();
}

sub list_finish
{
    my $sth = shift;
    $sth->finish;
    undef $sth;
}

sub add_certs
{
    my $type   = shift;
    my $id     = shift;
    my $cert   = shift;

    my $sth;
    my $certtype;

    my $cert_href = &get_certs_hash( $opts, $dbh, $type, $id );

    my %entry;

    if ( $type eq "hardware") {
        $certtype = "hwcert";
        $entry{ hardwareid } = $id;
    } elsif ( $type eq "module" ) {
        $certtype = "modcert";
        $entry{ moduleid } = $id;
        $entry{ mandatory } = 'f';
    } elsif ( $type eq "autobuild") {
        $certtype = "abcert";
        $entry{ autobuildid } = $id;
    }

    my $sql_cols = lc get_cols( $baTbls{ $certtype } );
    $sql_cols =~ s/[ \t]*//g;
    my @cols = split( /,/, $sql_cols );
    my $sql_vals = "?," x scalar @cols; chop $sql_vals;

    my $sql = qq|INSERT INTO $baTbls{ $certtype }
                         ( $sql_cols )
                         VALUES ( $sql_vals )
                        |;

    print $sql . "\n" if $opts->{debug};

    unless ( $sth = $dbh->prepare( $sql ) ) {
        $opts->{LASTERROR} = "Unable to prepare 'add_certs' statement\n" .
            $dbh->errstr;
        return 1;
    }

    my @cert_add = split( /\s/, $cert );

    foreach my $cert_in (@cert_add) {

        # don't re-add - but don't fail just because it's already there
        if ( defined $cert_href->{ $cert_in } ) {
            print "$type $id already certified for $cert_in\n";
            next;
        }

        $entry{ distroid } = $cert_in;

        my $paramidx = 0;
        foreach my $col (@cols) {
            $paramidx += 1;
            $sth->bind_param( $paramidx, $entry{ $col } );
        }
        unless ( $sth->execute( ) ) {
            $opts->{LASTERROR} = "Unable to execute 'add_certs' statement\n" .
                $sth->errstr;
            return 1;
        }
    }

    $sth->finish;
    undef $sth;

    return 0;
}

sub man_certs
{
    my $type   = shift;
    my $id     = shift;
    my $cert   = shift;

    my $enabled_version = &check_enabled( $opts, $dbh, $type, $id );
    unless ( defined $enabled_version ) {
        # call failed
        $opts->{LASTERROR} = "Failed in check_enabled call.\n" . $opts->{LASTERROR};
        return 1;
    }
    unless ( $enabled_version ) {
        $opts->{LASTERROR} = "Unable to set mandatory for $type $id without enabled version.\n";
        return 1;
    }

    &optman_certs ( $type, $id, $cert, "mancert" );
}

sub opt_certs
{
    my $type   = shift;
    my $id     = shift;
    my $cert   = shift;

    &optman_certs ( $type, $id, $cert, "optcert" );
}

sub optman_certs
{
    my $type   = shift;
    my $id     = shift;
    my $cert   = shift;
    my $action = shift;

    unless ( $type eq "module" ) {
        $opts->{LASTERROR} = "Mandatory cert operations for modules only.\n";
        return 1;
    }

    my $cert_href = &get_certs_hash( $opts, $dbh, $type, $id );

    my @certs = split( /\s/, $cert );

    # do all the checking before any modifications... thus two loops
    #
    # is this entry already certified (we only modify here)
    # finally for mandatory we require an enabled version of the entry
    my $cert_status = 0;
    foreach my $cert_in ( @certs ) {
        # fail if it's not already there
        unless ( defined $cert_href->{ $cert_in } ) {
            unless ( $cert_status ) {
                print "Unable to set mandatory/optional flag for $type\n";
                $cert_status = 1;
            }
            print "$type $id not certified for distro: $cert_in\n";
        }
    }
    if ( $cert_status ) {
        $opts->{LASTERROR} = "Try --addcert cert";
        $opts->{LASTERROR} .= ( $action eq "mancert" ) ? " --$action cert\n" : "\n" ;
        return 1;
    }

    my $sql = qq| UPDATE $baTbls{ "modcert" }
                  SET ( mandatory ) = ( ? )
                  WHERE moduleid = '$id'
                  AND   distroid = ?
                |;

    print $sql . "\n" if $opts->{debug};

    my $sth;
    unless ( $sth = $dbh->prepare( $sql ) ) {
        $opts->{LASTERROR} = "Unable to prepare 'update_certs' $action statement\n" .
            $dbh->errstr;
        return 1;
    }

    foreach my $cert_in (@certs) {

        if ( $action eq "mancert" ) {
            # don't make it mandatory if it is already
            if ( $cert_href->{ $cert_in } ) {
                print "$type $id already mandatory for $cert_in\n";
                next;
            }
            $sth->bind_param( "1", 't' );
        } else {
            # don't make it optional if it is already
            unless ( $cert_href->{ $cert_in } ) {
                print "$type $id already optional for $cert_in\n";
                next;
            }
            $sth->bind_param( "1", 'f' );
        }

        $sth->bind_param( "2", $cert_in );
        unless ( $sth->execute( ) ) {
            $opts->{LASTERROR} = "Unable to execute 'create' $action statement\n" .
                $sth->errstr;
            return 1;
        }
    }

    $sth->finish;
    undef $sth;

    return 0;
}

sub rm_certs
{
    my $type   = shift;
    my $id     = shift;
    my $cert   = shift;

    my $certtype;

    my $cert_href = &get_certs_hash( $opts, $dbh, $type, $id );

    if    ( $type eq "hardware"  ) { $certtype = "hwcert";  }
    elsif ( $type eq "autobuild" ) { $certtype = "abcert";  }
    elsif ( $type eq "module"    ) { $certtype = "modcert"; }

    my @cert_rm = split( /\s/, $cert );

    my $continue = 1;
    foreach my $cert_out (@cert_rm) {
        unless ( defined $cert_href->{ $cert_out } ) {
            print "Ignoring rmcert: $type $id not certified for $cert_out\n";
            next;
        }
        if ( $cert_href->{ $cert_out } ) {
            $continue = 0;
            print "Unable to remove $type $id marked mandatory for $cert_out\n";
        }
    }
    unless ( $continue ) {
        $opts->{LASTERROR} = "Try --optcert cert --rmcert cert\n";
        return 1;
    }

    my $sql = qq| DELETE FROM $baTbls{ $certtype }
                  WHERE distroid = ?
                |;

    print $sql . "\n" if $opts->{debug};

    my $sth;
    unless ( $sth = $dbh->prepare( $sql ) ) {
        $opts->{LASTERROR} = "Unable to prepare 'rm_certs' statement\n" .
            $dbh->errstr;
        return 1;
    }

    my $success = 1;
    foreach my $cert_out (@cert_rm) {
        unless ( $sth->execute( $cert_out ) ) {
            $success = 0;
            print "--rmcert $cert_out failed\n";
        }
    }
    unless ($success) {
        $opts->{LASTERROR} = "Unable to execute 'remove' cert statement\n" .
            $sth->errstr;
        return 1;
    }

    $sth->finish;
    undef $sth;

    return 0;
}


sub change_status
{
    my $type = shift;
    my $stat = shift;
    my $href = shift;

    # used to knowingly bypass disable mandatory check
    # because we're about to set another version enabled
    my $special = shift;

    $special = 0 unless (defined $special);

    unless (defined $href && defined $href->{'status'} ) {
        $opts->{LASTERROR} = "Unable to modify status without valid entry\n";
        return 1;
    }

    # don't change to what we already are
    return 0 if ( $href->{'status'} eq $stat );

    my $id;

    if ( $type eq "hardware" ) {
        $id = 'hardwareid';
    } elsif ( $type eq "module" ) {
        $id = 'moduleid';
        if ( $stat eq "f") {
            ## Verify that the module is not mandatory
            my $mandatory_array = &check_mandatory( $opts, $dbh, $href->{ $id } );
            unless ( defined $mandatory_array ) {
                # call failed
                $opts->{LASTERROR} = "Failed in change_status.\n" . $opts->{LASTERROR};
                return 1;
            }
            unless ( ($mandatory_array == 0) || ($special == 1) ) {
                $opts->{LASTERROR} = "Unable to disable module set as mandatory: " .
                    join(' ', @{$mandatory_array}) . "\n";
                return 1;
            }
        }
    } elsif ( $type eq "profile" ) {
        $id = 'profileid';
    } elsif ( $type eq "autobuild" ) {
        $id = 'autobuildid';
    } else {
        print "Expected 'module', 'profile', 'autobuild' or 'hardware'\n";
        exit 1;
    }

    my $sql = qq|UPDATE $baTbls{ $type }
                 SET status = '$stat'
                 WHERE $id = '$href->{ $id }'
                 AND version = '$href->{'version'}'
                |;
    print $sql . "\n" if $opts->{debug};

    # update specified version
    my $sth;
    unless ( $sth = $dbh->prepare( $sql ) ) {
        $opts->{LASTERROR} = "Unable to prepare 'change_status' statement\n" .
            $dbh->errstr;
        return 1;
    }
    unless ( $sth->execute( ) ) {
        $opts->{LASTERROR} = "Unable to execute 'change_status' statement\n" .
            $sth->err;
        return 1;
    }
    return 0;
}

sub change_description
{
    my $type = shift;
    my $desc = shift;
    my $href = shift;

    unless (defined $href && defined $href->{'description'} ) {
        $opts->{LASTERROR} = "Unable to modify description without valid entry\n";
        return 1;
    }

    # don't change to what we already are
    return 0 if ( $href->{'description'} eq $desc );

    my $id;

    if    ( $type eq "hardware"  ) { $id = 'hardwareid';  }
    elsif ( $type eq "module"    ) { $id = 'moduleid';    }
    elsif ( $type eq "profile"   ) { $id = 'profileid';   }
    elsif ( $type eq "autobuild" ) { $id = 'autobuildid'; }
    else {
        print "Expected 'module', 'profile' or 'hardware'\n";
        exit 1;
    }

    my $sql = qq|UPDATE $baTbls{ $type }
                 SET description = '$desc'
                 WHERE $id = '$href->{ $id }'
                 AND version = '$href->{'version'}'
                |;
    print $sql . "\n" if $opts->{debug};

    # update specified version
    my $sth;
    unless ( $sth = $dbh->prepare( $sql ) ) {
        $opts->{LASTERROR} = "Unable to prepare 'change_status' statement\n" .
            $dbh->errstr;
        return 1;
    }
    unless ( $sth->execute( ) ) {
        $opts->{LASTERROR} = "Unable to execute 'change_status' statement\n" .
            $sth->err;
        return 1;
    }
    return 0;
}

###########################################################################

die "ABSOLUTELY DOES NOT EXECUTE";

__END__

