/ 
MySQL-Util-0.41
River stage one • 2 direct dependents • 2 total dependents
/ MySQL::Util

NAME

MySQL::Util - Utility functions for working with MySQL.

VERSION

Version 0.41

SYNOPSIS

my $util = MySQL::Util->new( dsn  => $ENV{DBI_DSN}, 
                             user => $ENV{DBI_USER},
                             span => 1); 

my $util = MySQL::Util->new( dbh => $dbh );
                             
my $aref = $util->describe_table('mytable');
print "table: mytable\n";
foreach my $href (@$aref) {
    print "\t", $href->{FIELD}, "\n";
}

my $href = $util->get_ak_constraints('mytable');
my $href = $util->get_ak_indexes('mytable');
my $href = $util->get_constraints('mytable');

#
# drop foreign keys example 1 
# 

my $fks_aref = $util->drop_fks();

< do some work here - perhaps truncate tables >

$util->apply_ddl($fks_aref);   # this will clear the cache for us.  see 
                               # clear_cache() for more info.

# 
#  drop foreign keys example 2 
#

my $fks_aref = $util->drop_fks();

my $dbh = $util->clone_dbh;
foreach my $stmt (@$fks_aref) {
    $dbh->do($stmt); 
}

$util->clear_cache;  # we modified the database ddl outside of the object so 
                     # we need to clear the object's internal cache.  see 
                     # clear_cache() for more info.

METHODS

All methods croak in the event of failure unless otherwise noted.

new( dsn => $dsn, user => $user, [pass => $pass], [span => $span]);

constructor * dsn - standard DBI stuff * user - db username * pass - db password * span - follow references that span databases (default 0)

apply_ddl( [ ... ])

Runs arbitrary ddl commands passed in via an array ref.

The advantage of this is it allows you to make ddl changes to the db without having to worry about the object's internal cache (see clear_cache()).

describe_column(table => $table, column => $column)

Returns a hashref for the requested column.

Hash elements for each column:

DEFAULT
EXTRA
FIELD
KEY
NULL
TYPE

See MySQL documentation for more info on "describe <table>".

describe_table($table)

Returns an arrayref of column info for a given table.

The structure of the returned data is:

$arrayref->[ { col1 }, { col2 } ]

Hash elements for each column:

DEFAULT
EXTRA
FIELD
KEY
NULL
TYPE

See MySQL documentation for more info on "describe <table>".

drop_fks([$table])

Drops foreign keys for a given table or the entire database if no table is provided.

Returns an array ref of alter table statements to rebuild the dropped foreign keys on success. Returns an empty array ref if no foreign keys were found.

get_ak_constraints($table)

Returns a hashref of the alternate key constraints for a given table. Returns an empty hashref if none were found. The primary key is excluded from the returned data.

The structure of the returned data is:

$hashref->{constraint_name}->[ { col1 }, { col2 } ]

See "get_constraints" for a list of the hash elements in each column.

get_ak_indexes($table)

Returns a hashref of the alternate key indexes for a given table. Returns an empty hashref if one was not found.

The structure of the returned data is:

$href->{index_name}->[ { col1 }, { col2 } ]

See get_indexes for a list of hash elements in each column.

get_ak_names($table)

Returns an arrayref of alternate key constraints. Returns undef if none were found.

get_constraint(table => $table, name => $constraint_name)

Returns an arrayref for the requested constraints on a given table. Throws an error if the constraint is not found.

The structure of the returned data is:

$arrayref->[ { col1 }, { col2 } ]

Hash elements for each column:

see get_constraints()
get_constraints($table)

Returns a hashref of the constraints for a given table. Returns an empty hashref if none were found.

The structure of the returned data is:

$hashref->{constraint_name}->[ { col1 }, { col2 } ]

Hash elements for each column:

CONSTRAINT_NAME
TABLE_NAME
CONSTRAINT_SCHEMA
CONSTRAINT_TYPE
COLUMN_NAME
ORDINAL_POSITION
POSITION_IN_UNIQUE_CONSTRAINT
REFERENCED_COLUMN_NAME
REFERENCED_TABLE_SCHEMA
REFERENCED_TABLE_NAME
get_dbname()

Returns the name of the current schema/database.

get_depth($table)

Returns the table depth within the data model hierarchy. The depth is zero based.

For example:

-----------       -----------
| table A |------<| table B |
-----------       -----------

Table A has a depth of 0 and table B has a depth of 1. In other words, table B is one level down in the model hierarchy.

If a table has multiple parents, the parent with the highest depth wins.

get_fk_column_names(table => $table, [name => $constraint_name])

If name is specified, returns an array of columns that participate in the foreign key constraint. If name is not specified, returns an array of columns that participate an any foreign key constraint on the table.

get_fk_constraints([$table])

Returns the foreign keys for a table or the entire database.

Returns a hashref of the foreign key constraints on success. Returns an empty hashref if none were found.

The structure of the returned data is:

$hashref->{constraint_name}->[ { col1 }, { col2 } ]

See "get_constraints" for a list of the hash elements in each column.

get_fk_indexes($table)

Returns a hashref of the foreign key indexes for a given table. Returns an empty hashref if none were found. In order to qualify as a fk index, it must have a corresponding fk constraint.

The structure of the returned data is:

$hashref->{index_name}->[ { col1 }, { col2 } ]

See "get_indexes" for a list of the hash elements in each column.

get_indexes($table)

Returns a hashref of the indexes for a given table. Returns an empty hashref if none were found.

The structure of the returned data is:

$href->{index_name}->[ { col1 }, { col2 } ]

Hash elements for each column:

CARDINALITY
COLLATION
COLUMN_NAME
COMMENT
INDEX_TYPE
KEY_NAME
NON_UNIQUE
NULL
PACKED
SEQ_IN_INDEX
SUB_PART
TABLE
get_max_depth()

Returns the max table depth for all tables in the database.

See "get_depth" for additional info.

get_other_constraints($table)

Returns a hashref of the constraints that are not pk, ak, or fk for a given table. Returns an empty hashref if none were found.

The structure of the returned data is:

$hashref->{constraint_name}->[ { col1 }, { col2 } ]

See "get_constraints" for a list of the hash elements in each column.

get_other_indexes($table)

Returns a hashref of the indexes that are not pk, ak, or fk for a given table. Returns an empty hashref if none were found.

The structure of the returned data is:

$hashref->{index_name}->[ { col1 }, { col2 } ]

See "get_indexes" for a list of the hash elements in each column.

get_pk_constraint($table)

Returns an arrayref of the primary key constraint for a given table. Returns an empty arrayref if none were found.

The structure of the returned data is:

$aref->[ { col1 }, { col2 }, ... ]

See "get_constraints" for a list of hash elements in each column.

get_pk_index($table)

Returns an arrayref of the primary key index for a given table. Returns an empty arrayref if none were found.

The structure of the returned data is:

$aref->[ { col1 }, { col2 }, ... ]

See "get_indexes" for a list of the hash elements in each column.

get_pk_name($table)

Returns the primary key constraint name for a given table. Returns undef if one does not exist.

get_tables( )

Returns an arrayref of tables in the current database. Returns undef if no tables were found.

has_ak($table)

Returns true if the table has an alternate key or false if not.

has_fks($table)

Returns true if the table has foreign keys or false if not.

has_pk($table)

Returns true if the table has a primary key or false if it does not.

is_pk_auto_inc($table)

Returns true if the primary key is using the auto-increment feature or false if it does not.

is_column_nullable(table => $table, column => $column)

Returns true if column is nullable or false if it is not.

is_fk_column(table => $table, column => $column)

Returns true if column participates in a foreign key or false if it does not.

is_self_referencing($table, [$name => $constraint_name])

Returns true if the specified table has a self-referencing foreign key or false if it does not. If a constraint name is passed, it will only check the constraint provided.

table_exists($table)

Returns true if table exists. Otherwise returns false.

use_db($dbname)

Used for switching database context. Returns true on success.

ADDITIONAL METHODS

clear_cache()

Clears the object's internal cache.

If you modify the database ddl without going through the object, then you need to clear the internal cache so any future object calls don't return stale information.

clone_dbh()

Returns a cloned copy of the internal database handle per the DBI::clone method. Beware that the database context will be the same as the object's. For example, if you called "use_db" and switched context along the way, the returned dbh will also be in that same context.

SEE ALSO

MySQL::Util::Data::Create

AUTHOR

John Gravatt, <gravattj at cpan.org>

BUGS

Please report any bugs or feature requests to bug-mysql-util at rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=MySQL-Util. I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.

SUPPORT

You can find documentation for this module with the perldoc command.

perldoc MySQL::Util

You can also look for information at:

LICENSE AND COPYRIGHT

Copyright 2011 John Gravatt.

This program is free software; you can redistribute it and/or modify it under the terms of either: the GNU General Public License as published by the Free Software Foundation; or the Artistic License.

See http://dev.perl.org/licenses/ for more information.