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

cpanm DBIx::Admin::TableInfo

perl -MCPAN -e shell
install DBIx::Admin::TableInfo