Shell Programming and Scripting

use DBI qw(:sql_types);
my $dbfile = "schedule.sqlite";

my $dbh = DBI->connect("dbi:SQLite:$dbfile","","");
my $sth = $dbh->prepare("SELECT * FROM users ORDER BY id");
$sth->execute or die $sth->errstr;
my $row = $sth->fetch;
while (defined($row)) {
    print join("|", @$row), "\n";
    $row = $sth->fetch;
}

$dbh->disconnect;

CREATE TABLE users (id integer primary key, fname varchar(40), lname varchar(40));

CREATE TABLE parts (id integer primary key autoincrement,qty int8 not null,price int8 not null,item varchar(20) not null unique,desc varchar(255) not null);

#!/usr/bin/perl -w

use DBI qw(:sql_types);
my $dbfile = "parts_inventory.sqlite";

sub cmd_add {
    print "add:\n";
    my %record;
    foreach my $f ("item", "desc", "qty", "price") {
        my $aarg = shift(@ARGV);
        if ((! defined($aarg)) || ($aarg eq ":")) {
            # Error: some data is missing.
            printf("%%Error - Add command missing \"%s\".\n", $f);
            exit(1);
        } elsif ($aarg =~ /^-/) {
            # Option
        } else {
            # Data
            $record{$f} = $aarg;
        }
    }
    my $dbh = DBI->connect("dbi:SQLite:$dbfile","","");
    my $cmdh = $dbh->prepare("insert into parts " .
                             "values(null, ?, ?, ?, ?);");
    $cmdh->execute($record{"qty"}, $record{"price"} * 100, $record{"item"}, 
                   $record{"desc"}, ) or die "%Error - " . $cmdh->errstr();
    $dbh->disconnect();
    
}

sub cmd_report {
    print "report:\n";
    dbreport_by_id($dbfile);
}

sub dbcreate($) {
    printf("%%Warning - DBfile \"%s\" missing, creating new file.\n",
          $dbfile);
    # Touch file.
    my $now = time;
    utime($now, $now, $dbfile);

    my $dbh = DBI->connect("dbi:SQLite:$dbfile","","");
    my $cmdh = $dbh->prepare("create table parts (" .
                             "id integer primary key autoincrement," .
                             "qty int8 not null," .
                             "price int8 not null," .
                             "item varchar(20) not null unique," .
                             "desc varchar(255) not null);");
    $cmdh->execute() or die "%Error - " . $cmdh->errstr();
    $dbh->disconnect();
}

sub dbreport_by_id($) {
    my $dbh = DBI->connect("dbi:SQLite:$dbfile","","");
    my $sth = $dbh->prepare("SELECT * FROM parts ORDER BY id;");
    $sth->execute() or die "%Error - " . $sth->errstr();
    while (my $row = $sth->fetch()) {
        $row->[2] /= 100;
        print join("|", @$row), "\n";
    }
    $dbh->disconnect();
}

sub usage ($) {
    my $l = length("Usage - $0");

    print "\nUsage - $0 " . '[ SUBCMD [OPTIONS] DATA ... ] ' .
        '[ [ ":" SUBCMD [OPTIONS] DATA ... ] ... ]' . "\n";
    my $fmt = sprintf("%%%ds %%s\n", $l);
    printf($fmt, " ", "SUBCMD: report R_OPTIONS");
    printf($fmt, " ", "SUBCMD: add A_OPTIONS");
    print "\n";
    exit($_[0]);
}
if (! -e $dbfile) {
    dbcreate($dbfile);
}

# thiscmd [ subcmd [options] data ... ] [ ":" subcmd [options] data ... ] ...
# subcmd = report_cmd | add_cmd
# report_cmd = "report" report_opt [ report_opt ... ]
# report_opt = ( "-ascend" colname ) | ( "-desend" colname ) | 
#    ( "-limit" number )
# colname = "item" | "desc" | "qty" | "price"

# add_cmd = "add" add_data
# add_data = item_name description qty price

my $myprog = $0;
my $arg = shift(@ARGV);
my %cmds = ("add" => \&cmd_add, "report" => \&cmd_report, 
    "help" => \&usage);
if (! defined($arg)) {
    usage(0);
}

while ($arg) {
    # use hash for function call
    if ($arg ne ":") {
        if (exists($cmds{$arg})) {
            # Call subref
            &{$cmds{$arg}}
        } else {
            usage(1);
        }
    }
    $arg = shift(@ARGV);
}