The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
MySQL-Util-0.10
River stage one • 2 direct dependents • 2 total dependents
1 ++
/ MySQL::Util

NAME

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

VERSION

Version 0.10

SYNOPSIS

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

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

 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_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 none were 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_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_SCHEMA
        CONSTRAINT_TYPE
        COLUMN_NAME
        ORDINAL_POSITION
        POSITION_IN_UNIQUE_CONSTRAINT
        REFERENCED_COLUMN_NAME
        REFERENCED_TABLE_SCHEMA
        REFERENCED_TABLE_NAME
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_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_tables( )

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

table_exists($table)

Returns true if table exists. Otherwise returns false.

use_dbh($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_dbh" and switched context along the way, the returned dbh will also be in that same context.

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:

ACKNOWLEDGEMENTS

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.

1 POD Error

The following errors were encountered while parsing the POD:

Around line 531:

Expected text after =item, not a bullet