Here is a test program that I did with DBD::CSV (that was also hippo's plan). I've used /tmp as a 'folder'; you should of course eventually choose a better location.
(It would kind of make sense if CREATE DATABASE did create a directory (=folder) but that wasn't implemented (fortunately). So, directory management has to be done separately.)
#!/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->q
+uote ("%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 ("jagu
+ar") . ") ");
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;
}
# Hope this helps...
(I also made a version with postgres reading an underlying text-file, via postgres' file_fdw, but it depends on postgres and is read-only; it therefore seems less handy although the code is compact enough)
update: Changed to use DBI instead of the explicit use DBD::CSV; added conditional INSERT.
|