Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

Re^3: perl create database with folder system

by erix (Parson)
on Dec 03, 2017 at 11:23 UTC ( #1204792=note: print w/replies, xml ) Need Help??


in reply to Re^2: perl create database with folder system
in thread perl create database with folder system

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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1204792]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (10)
As of 2019-10-17 19:10 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?