MySQL::Util - Utility functions for working with MySQL.
Version 0.41
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.
All methods croak in the event of failure unless otherwise noted.
constructor * dsn - standard DBI stuff * user - db username * pass - db password * span - follow references that span databases (default 0)
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()).
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>".
Returns an arrayref of column info for a given table.
The structure of the returned data is:
$arrayref->[ { col1 }, { col2 } ]
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.
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.
$hashref->{constraint_name}->[ { col1 }, { col2 } ]
See "get_constraints" for a list of the hash elements in each column.
Returns a hashref of the alternate key indexes for a given table. Returns an empty hashref if one was not found.
$href->{index_name}->[ { col1 }, { col2 } ]
See get_indexes for a list of hash elements in each column.
Returns an arrayref of alternate key constraints. Returns undef if none were found.
Returns an arrayref for the requested constraints on a given table. Throws an error if the constraint is not found.
see get_constraints()
Returns a hashref of the constraints for a given table. Returns an empty hashref if none were found.
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
Returns the name of the current schema/database.
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.
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.
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.
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.
$hashref->{index_name}->[ { col1 }, { col2 } ]
See "get_indexes" for a list of the hash elements in each column.
Returns a hashref of the indexes for a given table. Returns an empty hashref if none were found.
CARDINALITY COLLATION COLUMN_NAME COMMENT INDEX_TYPE KEY_NAME NON_UNIQUE NULL PACKED SEQ_IN_INDEX SUB_PART TABLE
Returns the max table depth for all tables in the database.
See "get_depth" for additional info.
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.
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.
Returns an arrayref of the primary key constraint for a given table. Returns an empty arrayref if none were found.
$aref->[ { col1 }, { col2 }, ... ]
See "get_constraints" for a list of hash elements in each column.
Returns an arrayref of the primary key index for a given table. Returns an empty arrayref if none were found.
Returns the primary key constraint name for a given table. Returns undef if one does not exist.
Returns an arrayref of tables in the current database. Returns undef if no tables were found.
Returns true if the table has an alternate key or false if not.
Returns true if the table has foreign keys or false if not.
Returns true if the table has a primary key or false if it does not.
Returns true if the primary key is using the auto-increment feature or false if it does not.
Returns true if column is nullable or false if it is not.
Returns true if column participates in a foreign key or false if it does not.
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.
Returns true if table exists. Otherwise returns false.
Used for switching database context. Returns true on success.
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.
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.
MySQL::Util::Data::Create
John Gravatt, <gravattj at cpan.org>
<gravattj at cpan.org>
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.
bug-mysql-util at rt.cpan.org
You can find documentation for this module with the perldoc command.
perldoc MySQL::Util
You can also look for information at:
RT: CPAN's request tracker
http://rt.cpan.org/NoAuth/Bugs.html?Dist=MySQL-Util
AnnoCPAN: Annotated CPAN documentation
http://annocpan.org/dist/MySQL-Util
CPAN Ratings
http://cpanratings.perl.org/d/MySQL-Util
Search CPAN
http://search.cpan.org/dist/MySQL-Util/
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.
To install MySQL::Util, copy and paste the appropriate command in to your terminal.
cpanm
cpanm MySQL::Util
CPAN shell
perl -MCPAN -e shell install MySQL::Util
For more information on module installation, please visit the detailed CPAN module installation guide.