The following module was proposed for inclusion in the Module List:
modid: MySQL::Util
DSLIP: bdpOp
description: Utility functions for working with MySQL
userid: GRAVATTJ (John Gravatt)
chapterid: 7 (Database_Interfaces)
communities:
similar:
rationale:
This module is specifically tailored to MySQL. The functionality is
for general purpose things such as getting a list of indexes,
foreign keys, foreign key indexes, alternate keys, alternate key
indexes, constraints (fk, ak, and misc), misc indexes, and table
depth. I started out using DBI and DBD::mysql, but it was a bit
clunky and didn't provide all the functionality I needed. I am
currently using this module to perform database standards checking
at my place of work.
I am including the perldoc below.
=================
NAME MySQL::Util
DESCRIPTION Utility functions for working with MySQL.
Caching of some of the catalog data has been implemented for
performance.
SYNOPSIS my $util = Pearson::MySQL::Util->new( dsn =>
$ENV{DBI_DSN}, user => $ENV{DBI_USER} );
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'); ...
METHODS All methods croak in the event of failure unless otherwise
noted.
new(dsn => $dsn, user => $user, [pass => $pass]) constructor
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>".
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_TYPE COLUMN_NAME ORDINAL_POSITION
POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_NAME
REFERENCED_COLUMN_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 a hashref of the foreign key
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 } ]
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.
enteredby: GRAVATTJ (John Gravatt)
enteredon: Tue Nov 1 15:48:53 2011 GMT
The resulting entry would be:
MySQL::
::Util bdpOp Utility functions for working with MySQL GRAVATTJ
Thanks for registering,
--
The PAUSE
PS: The following links are only valid for module list maintainers:
Registration form with editing capabilities:
https://pause.perl.org/pause/authenquery?ACTION=add_mod&USERID=e8700000_930549452d4dc1e6&SUBMIT_pause99_add_mod_preview=1
Immediate (one click) registration:
https://pause.perl.org/pause/authenquery?ACTION=add_mod&USERID=e8700000_930549452d4dc1e6&SUBMIT_pause99_add_mod_insertit=1
Peek at the current permissions:
https://pause.perl.org/pause/authenquery?pause99_peek_perms_by=me&pause99_peek_perms_query=MySQL%3A%3AUtil
