/ 
DBIx-Admin-TableInfo-1.03
River stage one • 2 direct dependents • 2 total dependents
/ DBIx::Admin::TableInfo

NAME

DBIx::Admin::TableInfo - A wrapper around DBI's table_info() and column_info()

Synopsis

use DBIx::Admin::TableInfo;

my($dbh) = DBI -> connect
(
    'DBI:mysql:mids:127.0.0.1', 'root', 'pass',
    {
        AutoCommit         => 1,
        PrintError         => 0,
        RaiseError         => 1,
        ShowErrorStatement => 1,
    }
);
my($admin) = DBIx::Admin::TableInfo -> new(dbh => $dbh);
my($info)  = $admin -> info();

for my $table_name (@{$admin -> tables()})
{
    print "Table: $table_name\n";
    print "Table attributes\n";

    for (sort keys %{$$info{$table_name}{'attributes'} })
    {
        print "$_: $$info{$table_name}{'attributes'}{$_}\n";
    }

    print "\n";

    for my $column_name (@{$admin -> columns($table_name)})
    {
        print "Column: $column_name\n";
        print "Column attributes\n";

        for (sort keys %{$$info{$table_name}{'columns'}{$column_name} })
        {
            print "$_: $$info{$table_name}{'columns'}{$column_name}{$_}\n";
        }

        print "\n";
    }

    print "\n";
}

Description

DBIx::Admin::TableInfo is a pure Perl module.

It is a wrapper around the DBI methods table_info() and column_info().

Distributions

This module is available both as a Unix-style distro (*.tgz) and an ActiveState-style distro (*.ppd). The latter is shipped in a *.zip file.

See http://savage.net.au/Perl-modules.html for details.

See http://savage.net.au/Perl-modules/html/installing-a-module.html for help on unpacking and installing each type of distro.

Constructor and initialization

new(...) returns a DBIx::Admin::TableInfo object.

This is the class's contructor.

Usage: DBIx::Admin::TableInfo -> new().

This method takes a set of parameters. Only the dbh parameter is mandatory.

For each parameter you wish to use, call new as new(param_1 => value_1, ...).

column_catalog

This is the value passed in as the catalog parameter to column_info(catalog, schema...).

The default value is undef.

undef was chosen because it given the best results with MySQL. The MySQL driver DBD::mysql V 2.9002 has a bug in it, in that it aborts if an empty string is used here, even though an empty string is used for the catalog parameter to table_info().

This parameter is optional.

column_schema

This is the value passed in as the schema parameter to column_info(catalog, schema...).

The default value is undef.

See above for comments about undef.

This parameter is optional.

dbh

This is a database handle.

This parameter is mandatory.

table_catalog

This is the value passed in as the catalog parameter to table_info(catalog, schema...).

The default value is '' (the empty string).

See above for comments about empty strings.

This parameter is optional.

table_schema

This is the value passed in as the schema parameter to table_info(catalog, schema...).

The default value is '' (the empty string).

See above for comments about empty strings.

This parameter is optional.

Method: columns($table_name, $by_position)

Returns an array ref of column names.

By default they are sorted by name.

However, if you pass in a true value for $by_position, they are sorted by the column attribute ORDINAL_POSITION.

Method: info()

Returns a hash ref of all available data.

The structure of this hash is described next:

First level: The keys are the names of the tables
my($info)       = $obj -> info();
my(@table_name) = sort keys %$info;

I use singular names for my arrays, hence @table_name rather than @table_names.

Second level: The keys are 'attributes' and 'columns'
my($table_attributes) = $$info{$table_name}{'attributes'};

This is a hash ref of the table's attributes.

my($columns) = $$info{$table_name}{'columns'};

This is a hash ref of the table's columns.

Third level: Table attributes
while ( ($name, $value) = each(%$table_attributes) )
{
	Use...
}

For the attributes of the tables, there are no more levels in the hash ref.

Third level: The keys are the names of the columns.
my(@column_name) = sort keys %$columns;
Fourth level: Column attributes
for $column_name (@column_name)
{
    while ( ($name, $value) = each(%{$columns{$column_name} }) )
    {
	    Use...
    }
}

Method: tables()

Returns an array ref of table names.

They are sorted by name.

Example code

See the examples/ directory in the distro.

There are 2 demo programs:

test-admin-info.cgi

It outputs all possible info in HTML.

test-admin-info.pl

It outputs all possible info in text.

Tested Database Formats

I have used the program in the synopsis to read databases in these formats:

MySQL V 4.0
MS Access V 2

Yes, some businesses are still running V 2 as of July, 2004.

MS Access V 2002

It's important to note that in each case the only parameter that needed to be passed to new() was dbh.

Related Modules

I have written a set of modules - which are still being tested - under the DBIx::Admin::* namespace.

They will form the core of myadmin.cgi V 2. See http://savage.net.au/Perl-tutorials.html#tut_41

Required Modules

Carp.

Changes

See Changes.txt.

Author

DBIx::Admin::TableInfo was written by Ron Savage <ron@savage.net.au> in 2004.

Home page: http://savage.net.au/index.html

Copyright

Australian copyright (c) 2004, Ron Savage. All rights reserved.

All Programs of mine are 'OSI Certified Open Source Software';
you can redistribute them and/or modify them under the terms of
The Artistic License, a copy of which is available at:
http://www.opensource.org/licenses/index.html