by Joel Limardo

by Joel Limardo

Software Developer and IT Professional

Chicago, IL

joel@joellimardo.com

SIP Phone (researching...)

Workadventure (researching...)

Downstream Project Status

BixChange

33%

LIMSExpert.com

28%

Upstream Projects

Joellimardo.com Project Cleanup

Fri, 12 Aug 2022

GraphViz::DBI

This is a bit more experimental. I want to just show the database graphically as a big PNG file just for comparison I guess. Looking at the pod however I realize that this module is fairly old and when I try to run it I get weirdness:



perl -MGraphViz::DBI -MDBI -e 'my $dbh = DBI->connect(qq~DBI:Pg:dbname=test;port=5432;host=localhost~,qq~me~,qq~afairlystandardpass~); GraphViz::DBI->new($dbh)->graph_tables->as_png;

DBD::Pg::st execute failed: ERROR: permission denied for relation _pg_foreign_tables at /usr/local/share/perl/5.26.1/GraphViz/DBI.pm line 73.

DBD::Pg::st execute failed: ERROR: permission denied for relation _pg_user_mappings at /usr/local/share/perl/5.26.1/GraphViz/DBI.pm line 73.

DBD::Pg::st execute failed: ERROR: permission denied for relation transforms at /usr/local/share/perl/5.26.1/GraphViz/DBI.pm line 73. ...

Looks like some kind of special permissions are needed to view lots of these PostgreSQL tables/views. I am confronted with the decision to delve deeper into the problem or simply dump this module for something else.

Updates

Note: this uses Javascript to open these sections. I will eventually update this with just a POD or something. In the meantime you can just view this stuff using the Lynx browser with the permanent link below if you have Javascript turned off.

I wrote this in an earlier post but lost it due to server problems:

perl -MGraphViz::DBI -MDBI -e 'my $dbh = DBI->connect(qq~DBI:Pg:dbname=test;port=5432;host=localhost~,qq~usr~,qq~pss~); my $gv = GraphViz::DBI->new($dbh); $gv->{'tables'} = [qw~db_version customers~]; print $gv->graph_tables->as_png; ' > out.png

Return

/technical/upstream/perl/dbmanage Permanent Link Project Sitemap Top

IO::DB

Normally you would Install via:


   sudo cpan -i IO::DB

When I try to install this way on 5.26.1 I get all sorts of errors due to the module dependencies. Turns out that this module relies on another called Class::HPLOO::Base that will not install. Apparently that module has not been updated in a while since the compile operation says certain syntax was deprecated in earlier versions of Perl.

This does not really mean I should totally avoid using this module. I just need to manually install it (or use force) and disable the so-called 'nasty bits.'

Every once in a while I get a kick out of trying to install a module manually anyway to see its dependencies (the IO::DB archive is called SDP-0.1.tar.gz). I then scoop out the module after running Perl Makefile.PL from the /lib directory and copy it to where I want it.

Give the module a quick test,


perl -e 'use lib qq~./~; use IO::DB; print 1;'

Or if you use:

cpan get IO::DB
... (get this from your ~/.cpan directory)
tar -zxvf SDP-0.1.tar.gz
cd ./SDP*/
mkdir ./olib
perl Makefile.PL PREFIX=./olib
make
make install
mv ./t/pod-coverage.t ./pod-coverage.t.skip
prove -b t

This will pass the basic tests. The pod-coverage.t only checks to see if the module documentation is complete. If you can read Perl this should not stop you from using the module.

Updates

In order to extend tests why not just actually extend the tests? I copy the 00.load.t to 01.me.t and add the following to it:

use Test::More qq~no_plan~;
BEGIN {
use_ok( 'IO::DB' );
}
ok ( ( 1 == 1 ) , 'Basic test' );
my $db = new IO::DB( { db_dsn => 'dbi:Pg:dbname=test',
db_user => 'dynuser',
db_pass => 'pppaaassss' } );

my $rows = $db->sql_rows ('select count(*) as cnt from foo' );
ok ( ( $rows > 0 ) , 'Basic select count ' );
#warn $rows->[0]->{'cnt'};
ok ( ( $rows->[0]->{'cnt'} > 0 ) , 'Actually returned SQL count is correct' );

Most of this stuff is copied from the basic POD in the module. I just want to make sure it works with PostgreSQL. I think the author shows it using Sybase.

TBD

Return

/technical/upstream/perl/dbmanage Permanent Link Project Sitemap Top

Database Management

Here I am talking about modeling a DB and DML statements to control its formation. There seem to be lots of different ways to access your data (which is important as well if you are to work with your database) but utilities for managing the database is what I am looking for here.

Super-quick spec of things I want to do:

Create a table, view, and stored procedure all from the interface
Build some kind of utility that will allow for whatever structures to be identifiable to the program in a kind-of database agnostic way (I see various methods for accessing system tables being used which is not portable)
Control with field-level granularity a user's access to CRUD according to some kind of access control list (ACL),

Data Access/Manip: IO::DB, SQL::Load, SQL::Abstract
Structure Visualization: GraphViz-DBI

/technical/upstream/perl/dbmanage Permanent Link Project Sitemap Top