#!/usr/local/bin/perl -w
########
#
# test DBD driver for CSV (comma-seperated-values) flat files 
# as DBI database (sql-like)
#  
#
#####

use strict;
use DBI;

my $csvdb = "test_csvdb";  # path to CSV Database directory

my $table_name = "test_people2";

# Note that the CSV driver actually ignores all the data characteristics.
# I just put 'em so this'd look like a typical SQL thingy.
my $table_layout = "
  (	Lastname   CHAR(24), 
	Firstname  CHAR(24), 
	ID         INTEGER NOT NULL,
	PostalCode INTEGER, 
	Age        INTEGER, 
	Sex        CHAR(1)
   )";

# @rows is an array of refs to arrays of data (all text strings, but 
# that doesn't really matter here) to put into the table.
#
# I put the comma into "Hamilton" to see what the resulting file
# would look like, and whether CSV would get confused - but it did OK,
# and changed the last line of the file to 
#    "Ham,ilton",William,247,10139,2,M
# Cool.  I even put in some carriage returns and quotes into the FirstName,
# and it still look like it's doing OK.  
#
my @rows = (
 [qw(  Gauss      Karl        119  19107        30   M )],
 [qw(  Smith      Mark        3    T2V3V4       53   M )],
 [qw(  Noether    Karl        118  19107        31   F )],
 [qw(  Smith      Jeff        28   K2G5J9       19   M )],
 [(  "Ham,ilton" ,  "William" ,   247 , 10139 ,  2,  "M" )],
 [(  "Carriage
      Returns and quotes \" 
      Here" ,  "Joe" ,   000 , 0 ,  22,  "M" )],
 [   "Spiffy",  "Thelma Louise", "222", "2 Gilmore Rd", 42, "F"]
);

# ---------- end data declarations ------------


# ------------------------
# connect to the database
my $dbh = DBI->connect("DBI:CSV:f_dir=$csvdb")
  or die "Couldn't connect to database: $DBI::errstr ";

# make the datebase (only need to do this once, or for testing)
my $make_data = 1;
print "=== MAKING TABLE === \n";
if ($make_data) {&create_table()}

print "=== LOOKING UP Karl === \n";
&lookup2("Karl");

#print "\n=== LOOKING UP MAHONEY === \n";
#&lookup("mahoney");
#print "\n=== LOOKING UP SANTA === \n";
#&lookup("santa");

$dbh->disconnect()
  or die "Couldn't disconnect: $DBI::errstr";


# ===========

sub lookup2 {
  my $field_name = "Firstname";
  my $table_name = "test_people";

  my ($username) = @_;
  my $query = $dbh->prepare( "SELECT * FROM $table_name WHERE $field_name = ?")
    or die "Couldn't prepare query: $DBI::errstr";

  $query->execute( $username )
    or die "Couldn't execute query $DBI::errstr";

  my @row=();

  while (@row = $query->fetchrow_array) {
    print "Found result row: @row \n";
  }
  $query->finish();

}



sub lookup {
  my $table_name = "authorization";
  my $field_name = "Username";

  my ($username) = @_;
#  my $query = $dbh->prepare( "SELECT * FROM $table_name WHERE $field_name = ?")
#    or die "Couldn't prepare query: $DBI::errstr";
  my ($query) = $dbh->prepare( "SELECT * FROM authorization WHERE Username = ?")
    or die "Couldn't prepare query: $DBI::errstr";

  $query->execute( $username )
    or die "Couldn't execute query $DBI::errstr";

  my ($name,$ac,$ip);
  $query->bind_columns(\$name,\$ac,\$ip);

  while ($query->fetch) {
    print "Found result row: name=$name ac=$ac ip=$ip \n";
  }
  $query->finish();

  #print " username = $username \n ";
  #print " number of fields found = $query->{NUM_OF_FIELDS} \n";
  #print "   field names: @{ $query->{NAME}} \n";
#  my @answer = $query->fetchrow_array;
#  print " answer = @answer \n";
#  while ($answer = $query->fetchrow_hashref) {
#    my @keys = keys %$answer;
#    my @vals = values %$answer;
#    print " next row that matches is ( @keys ) , ( @vals ) \n";
#  }
}

################################### subroutines


# create table (file) named $table_name 
# in directory $csvdb
# with fields $table_layout
# and rows @rows.
sub create_table {
  # ------------------------
  my $r;

# create a new table
  $dbh->do("CREATE TABLE $table_name $table_layout ")
    or die "Couldn't create table: $DBI::errstr ";

  foreach $r (@rows) {

    # See the CSV.html pod from the Perl stuff for this next syntax;
    # I'm following their suggested syntax.
    $dbh->do("INSERT INTO $table_name VALUES (?, ?, ?, ?, ?, ?)", undef, @$r)
      or die "Couldn't insert ' @$r ' into table : $DBI::errstr ";
  }
	 
}

##########################
# sample DBI statments:
#
#  $dbh->do( " any SQL comand" );
#
#  $query = $dbh->prepare( "SELECT * FROM people WHERE lastname = ?")
#    or die "Couldn't pepare statement: $DBI::errstr ";
#  $query->execute( $foo );   # the "$foo" replaces the ? in the prepare
#  while @data = $query->fetchrow_array()) { ... }  # gets one row at a time that matches.
#
#  $dbh->commit;     # make changes permanent
#  $dbh->rollback;   # undo changes
#  

################
# SQL commands samples:
#    INSERT INTO test_people VALUES ('Euler', 'Leonard', 248, NULL, 58, 'M')
#    DELETE FROM test_people WHERE id = 3
#    UPDATE people SET age = age+1 WHERE id = 247
#    SELECT firstname FROM people WHERE lastname = 'Smit'
#    CREATE TABLE foo (ownerid INTEGER NOT NULL, item CHAR(40) NOT NULL)
#    

##################
# example database "test_people" :
#                          LASTNAME   FIRSTNAME   ID   POSTAL_CODE   AGE  SEX
#                          Gauss      Karl        119  19107         30   M
#                          Smith      Mark        3    T2V 3V4       53   M
#                          Noether    Emmy        118  19107         31   F
#                          Smith      Jeff        28   K2G 5J9       19   M
#                          Hamilton   William     247  10139         2    M

##############
# A the actual file created looks like this:
#
# zonorus ~...po/csvdb> more test_people
#
#  Lastname,Firsname,ID,PostalCode,Age,Sex
#  Gauss,Karl,119,19107,30,M
#  Smith,Mark,3,T2V3V4,53,M
#  Noether,Emmy,118,19107,31,F
#  Smith,Jeff,28,K2G5J9,19,M
#  "Ham,ilton",William,247,10139,2,M
#  "Carriage
#      Returns and quotes "" 
#      Here",Joe,0,0,22,M