TYPE_NAME

[H.Merijn Brand]

In creating database-agnostic analysers, I want to be unaware of what
is behind the scenes, and when I want to describe a table, I use my own
function:

$ perl -MDP -MPROCURA::DBD -we'DDumper describe ("usr")'
$VAR1 = {
    LINK             => undef,
    NAME             => 'c_usr',
    NAME_lc          => 'c_usr',
    NAME_uc          => 'C_USR',
    NULLABLE         => 2,
    PRECISION        => 9,
    SCALE            => 0,
    TYPE             => 2,
    TYPE_NAME        => 'NUMERIC'
    };
$VAR2 = {
    LINK             => undef,
    NAME             => 'usr',
    NAME_lc          => 'usr',
    NAME_uc          => 'USR',
    NULLABLE         => 2,
    PRECISION        => 40,
    SCALE            => 0,
    TYPE             => 1,
    TYPE_NAME        => 'CHAR'
    };
$VAR3 = {
    LINK             => undef,
    NAME             => 'c_lp',
    NAME_lc          => 'c_lp',
    NAME_uc          => 'C_LP',
    NULLABLE         => 2,
    PRECISION        => 4,
    SCALE            => 0,
    TYPE             => 5,
    TYPE_NAME        => 'SMALLINT'
    };

What you see in action is a function that translates the basic
attributes available from a statement handle into a list of attributes
per field
--8<---
SYNOPSIS

   my @desc = describe ("table");      # Get table description as an array of
                                       # hashes. $desc[0]{NAME} is the name of
                                       # the first field
   my @desc = describe ("table", "L"); # .. *with* link info in $desc[0]{LINK}

DESCRIPTION

   describe (table [, "L"])
       Tries to describe "table" and return a listref of hashes in the field
       order of the table, so "$r->[0]{NAME_lc}" is the lower case name of
       the first field of the table.

       The converted entries are "NAME", "NAME_lc", "NAME_uc", "NULLABLE",
       "PRECISION", "SCALE", and "TYPE". Additionaly, "TYPE_NAME" has been
       added with the "type_info ()" information for that "TYPE" if available.

       When an "L" is passed as second argument, an additional hash entry
       "LINK" is set to "schema.table.field" when the field references another
       field.  This additional entry is undef if no link could be found.
-->8---

Not that I suggest to add this to the DBI, but I wonder how useful the
addition of TYPE_NAME would be as a default attribute, as it is not the
easiest of all to decipher:

{   my %types;  # Cache for types

    # Convert numeric to readable
    sub _type_name
    {
        my ($dbh, $type) = @_;

        unless (exists $types{$dbh}{$type}) {
            my $tpi = $type =~ m/^-?[0-9]+$/ ? $dbh->type_info ($type) : undef;
            $types{$dbh}{$type} = $tpi ? $tpi->{TYPE_NAME} : $type // "?";
            }
        return $types{$dbh}{$type};
        } # type_name
    }

and later in describe ():

    $desc[$i]{TYPE_NAME} = _type_name ($dbh, $desc[$i]{TYPE});


-- 
H.Merijn Brand  http://tux.nl      Perl Monger  http://amsterdam.pm.org/
using & porting perl 5.6.2, 5.8.x, 5.10.x, 5.11.x on HP-UX 10.20, 11.00,
11.11, 11.23, and 11.31, OpenSuSE 10.3, 11.0, and 11.1, AIX 5.2 and 5.3.
http://mirrors.develooper.com/hpux/           http://www.test-smoke.org/
http://qa.perl.org      http://www.goldmark.org/jeff/stupid-disclaimers/