#!/bin/env perl
use strict;
use warnings;
use DBI;  

my $db       = "animals";
my $table    = "cat";
my $db_dir   = "/tmp/$db";
my $filename = "$db_dir/$table.txt";

if (! -d $db_dir ) { mkdir $db_dir; }
if (! -d $db_dir ) { die "error: no such directory $db_dir "; }

# copied from the DBD::CSV docs, almost unchanged
my $dbh = DBI->connect (
        "dbi:CSV:f_dir=${db_dir};f_ext=.txt;f_lock=2;" .
        "f_encoding=utf8;csv_eol=\n;csv_sep_char=\t;" .
        "csv_quote_char=\";csv_escape_char=\\;csv_class=Text::CSV_XS;" .
        "csv_null=1") or die $DBI::errstr;

my $sep = "\t";
my $eol = "\n";

if ( ! -e $filename ) {
# $dbh->do ("drop table $table");
  $dbh->do ("create table $table (id integer, name char(64))");
  my $rc = $dbh->do("
    insert into $table values 
        (1, " . $dbh->quote ("lion"         ) . ")
      , (2, " . $dbh->quote ("tiger"        ) . ")
      , (3, " . $dbh->quote ("lynx"         ) . ")
      , (4, " . $dbh->quote ("puma"         ) . ")
      , (5, " . $dbh->quote ("leopard"      ) . ")
      , (6, " . $dbh->quote ("mountain lion") . ")"
  );
}

print "-- retrieving all:\n";
my $sth = $dbh->prepare("select * from $table");
my $rc  = $sth->execute;
while (my $rrow = $sth->fetchrow_arrayref) {
  print $rrow->[0], $sep, $rrow->[1], $eol;
}

print "\n-- retrieving '%lion%':\n";
$sth = $dbh->prepare("select * from $table where name like " . $dbh->quote ("%lion%") . "");
$rc  = $sth->execute;
while (my $rrow = $sth->fetchrow_arrayref) {
  print $rrow->[0], $sep, $rrow->[1], $eol;
}

#my $rowcount = $dbh->selectrow_arrayref("select count(*) from $table where name = " . $dbh->quote ("jaguar") )->[0];
#if ($rowcount == 0)  {
if ($dbh->selectrow_arrayref("select count(*) from $table where name = " . $dbh->quote ("jaguar") )->[0] == 0) {
  print "\n-- inserting 'jaguar':\n";
  $rc = $dbh->do("insert into $table values (7, " . $dbh->quote ("jaguar") . ") ");
  print "   returned [$rc]\n\n";
}
else {
  print "\n-- record 'jaguar' exists, NOT inserting; \n\n";
}

print "-- retrieving again:\n";
$sth = $dbh->prepare("select * from $table");
$rc  = $sth->execute;
while (my $rrow = $sth->fetchrow_arrayref) {
  print $rrow->[0], $sep, $rrow->[1], $eol;
}

print "\n-- deleting 'puma':\n";
$rc = $dbh->do("delete from $table where name = " . $dbh->quote ("puma") . "");
print "   returned [$rc]\n\n";

print "-- and retrieving once more:\n";
$sth = $dbh->prepare("select * from $table");
$rc  = $sth->execute;
while (my $rrow = $sth->fetchrow_arrayref) {
  print $rrow->[0], $sep, $rrow->[1], $eol;
}