NAME

MySQL::TableInfo - Perl extension for getting access into mysql's column information.

RATIONALE

The idea was taken from Paul DuBois' "MySQL and Perl for the Web" book. I searched the CPAN but failed to find any module that does the similar task and thought of putting one together and upload to CPAN. And here it is.

NOTE

The library has been tested on MySQL version 3.23.40

SYNOPSIS

use CGI;
use DBI;
use MySQL::TableInfo;

my $CGI = new CGI:
my $dbh = DBI->connect(....);
my $table = new MySQL::TableInfo($dbh, "bio");

print $CGI->header,
    $CGI->start_html("MySQL::TableInfo"),
    $CGI->start_form,
    $CGI->div("Do you have beard?"),
    $CGI->popup_menu(-name=>'has_beard',
                     -values=>[$table->enum('has_beard')],
                     -default=>$table->default('has_beard')),
$CGI->end_form,
$CGI->end_html;

DESCRIPTION

MySQL::TableInfo is a handy class for getting easy access to MySQL tables' descriptions which is available via

DESCRIBE table_name, SHOW COLUMNS FROM table_name

queries. It's also handy for constructing form based CGI applications to control HTML forms' attributes such as VALUE, SIZE, MAXLENGTH, TYPE and so forth. For example, if you have a ENUM('Yes', 'No') column in your mysql table, then you normally would present it either as a group of radio buttons, or as a <SELECT> menu. If you modify the column, and add one more option, ENUM('Yes', 'No', 'N/A'), then you will have to re-write your html code accordingly. By using MySQL::TableInfo, you can avoide this double troubles. Consider the following code:

use CGI;
use DBI;
use MySQL::TableInfo;

my $CGI = new CGI:
my $dbh = DBI->connect(....);
my $table = new MySQL::TableInfo($dbh, "bio");

print $CGI->header, $CGI->start_html("MySQL::TableInfo");

print $CGI->start_form,
    $CGI->div("Do you wear beard?"),
    $CGI->checkbox_group( -name=>'has_beard',
                          -values=>[$table->set('has_beard')],
                          -default=>$table->default('has_beard')),
$CGI->end_form;

print $CGI->end_html;

As you see, modifying 'has_beard' column, which is an enumeration column, whould reflect in your CGI too.

METHODS

new($dbh, 'table_name')

constructor method. The two reguired arguments are database handle ($dbh) returned from DBI->connect(), and the name of the mysql table to work with. Since you create the $dbh with the database name, it is not required to pass the database name to new(). If you really want to, you can prescribe the database name in front of the "table_name" delimited with a period. Example:

my $table = new MySQL::TableInfo($dbh, "database.table_name");

column([$column_name])

if invoked with a column name returns an array consisting of all the column's attributes. If the argument is missing returns an array consisting table's all the columns. For example, the following code prints all the column names:

foreach my $col ($table->column) {
    print "$col\n";
}

You can also print all the column names together with their attributes by slightly modifying the above code:

foreach my $col ($table->column) {
    print "$col => ", join (" : ", $table->column($col) ), "\n";
}

Of course the above example is pretty awkward if we want to gain access to each attribute of the columns (like size, default values, sets, enumeations and etc) seperately. But the cure is comming below, read on

size($column_name)

returns the size of the $column_name. If the column doesn't have any size attribute (such as TEXT?) it returns undef

type($column_name)

returns the type of the column. The possible values returned from this method include: varchar, char, text, int, set, enum and so forth.

default($column_name)

returns default values for the $column_name

is_null($column_name)

returns true if the $column_name can hold NULL value, false otherwise.

set($column_name)

returns an array consisting of all the possible options of the SET column type.

enum($column_name)

the same as set(), but implemented for enum type columns. Remember, the methods set() and enum() can be used interchangebly for either set or enum type columns. The class doesn't make any distinction over the two type. It's the programmer's responsibility instead

extra($column_name)

returns extra information about the $column_name. If no such information available, returns undef instead. As of MySql 3.23.x, the only value extra() returns is auto_increment

load_default($CGI)

loads defaults of the columns into the CGI.pm object. It is usefull if you are making extensive use of Lincoln Stein's CGI.pm module.

validate($CGI)

validates matching the value(s) of the paramaters with their respective columns (if exists) off the mysql table. This feature is not implemented as of MySQL::TableInfo version 0.03. Any modifications are welcome.

COPYRIGHT

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

AUTHOR

Sherzod B. Ruzmetov <sherzodr@cpan.org>

BUGS

No bugs have been detected thus far. Any bug reports should be sent to Sherzod Ruzmetov (sherzodR) <sherzodr@cpan.org>

SEE ALSO

DBI DBD::mysql CGI