NAME

DBIx::MyDatabaseMunger - MariaDB/MySQL Database Management Utility

SYNOPSIS

Normal interface is through the mydbmunger command, but this class can also be used directly.

use DBIx::MyDatabaseMunger ();
$dbmunger = new DBIx::MyDatabaseMunger ({
    connect => {
        schema => 'database',
        host => 'mysql.example.com',
        user => 'username',
        password => 'p4ssw0rd',
    },
    colname => {
        ctime => 'create_datetime',
        mtime => 'tstmp',
    },
});
$dbmunger->pull();
$dbmunger->make_archive();
$dbmunger->push();

DESCRIPTION

A library and accompanying "mydbmunger" utility to simplify complex MySQL and MariaDB database management tasks.

CONSTRUCTOR

The constructor new DBIx::MyDatabaseMunger() takes a hash reference of options. These options include.

archive_name_pattern

Naming convention for archive tables. Takes a wildcard, '%' that will be the source table name.

Default: %Archive

colname

A hash of column names for special handling. Column names include:

action

Column used to record action in archive table. Column should be an enumeration type with values 'insert', 'update', or 'delete'.

ctime

Column used to record when a record is initially created. If not specified then this functionality will not be implemented.

dbuser

Column used to track dabase connection USER(), which indicates the user and host that is connected to the database.

mtime

Column used to record when a record was last changed. If not specified then this functionality will not be implemented.

revision

Revision count column. Must be an integer type.

stmt

The column used to track the SQL statement responsible for a table change.

updid

The column used to store the value of the variable indicated by updidvar.

dir

Directory in which to save table and trigger definitions.

Default: .

updidvar

Connection variable to be used by the calling application to track the reason for table updates, inserts, and deletes.

Default: @updid

METHODS

table_names ()

Return a list of all saved table names.

$o->parse_create_table_sql ( $sql )

Parse a CREATE TABLE statement generated by mysql "SHOW CREATE TABLE ..."

This function is very particular about the input format.

$o->read_table_sql ( $table_name )

Given a table name, retrieve the table definition SQL.

$o->get_table_desc ( $table_name )

Given a table name, retrieve the parsed table definition.

$o->find_data_tables_with_revision ()

Return table definitions that have a revision column.

$o->check_table_is_archive_capable ( $table )

Check that a table has bare minimum support required to have an archive table.

$o->check_table_updatable( $current, $desired )

Check that the current table could be updated to the desired state.

$o->make_archive_table_desc ( $table_desc )

Make a archive table description for the given source table description.

$o->write_table_sql( $name, $sql )

Save create table SQL for a table.

$o->remove_table_sql( $name )

Remove create table SQL for a table.

$o->write_table_definition( $table )

Write create table SQL for given table description.

$o->remove_trigger_fragment( $fragment )

Remove trigger fragment SQL.

$o->write_trigger_fragment_sql( $name, $time, $action, $table, $sql )

Write trigger fragement SQL to a file.

$o->write_archive_trigger_fragments( $table, $archive_table_desc )

Write trigger fragment sql for archive table management.

$o->query_table_sql ( $name )

$o->pull_table_definition ( $name )

$o->pull_table_definitions ()

$o->queue_create_table ( $table )

$o->create_table_sql ( $table )

$o->constraint_sql ( $constraint )

$o->queue_add_table_constraint ( $table, $constraint )

$o->queue_drop_table_constraint ( $table, $constraint )

$o->queue_table_updates( $current, $desired )

$o->push_table_definition( $table )

$o->push_table_definitions()

$o->queue_drop_table ( $name )

view_names ()

Return a list of all saved view names.

$o->read_view_sql ( $table_name )

Given a table name, retrieve the table definition SQL.

$o->write_view_sql( $name, $sql )

Save create view SQL for a view.

$o->remove_view_sql( $name )

Remove create view SQL for a view.

$o->query_table_names ()

$o->query_view_sql ( $name )

$o->pull_view_definition ( $name )

$o->query_view_names ()

$o->queue_create_view( $new_sql )

$o->push_view_definition( $view )

$o->push_view_definitions()

$o->queue_drop_view ( $name )

$o->pull_view_definitions ()

$o->trigger_fragments ()

Return list of trigger fragment names.

$o->assemble_triggers ()

Assemble trigger fragments into nested hash of triggers.

$o->read_trigger_fragment_sql ( \%fragment )

$o->queue_push_trigger_definitions()

$o->pull_trigger_definitions ()

$o->pull_trigger_fragments : method

$o->procedure_names()

$o->read_procedure_sql ( $name )

$o->queue_push_procedure ( $name )

$o->queue_drop_procedure ( $name )

$o->queue_create_procedure ( $name, $sql )

$o->queue_push_procedures ()

$o->query_procedure_names ()

$o->remove_procedure_sql( $name )

$o->pull_procedure_sql ( $name )

$o->pull_procedures ()

$o->write_procedure_sql( $name, $sql )

$o->pull ()

Handle the pull command.

$o->push ()

Handle the push command.

$o->run_queue()

Process any actions in todo queue. Returns number of actions executed.

$o->make_archive ()

Handle the make-archive command.

$o->set_dbh ()

Explicitly set the database handle.

AUTHOR

Johnathan Kupferer <jtk@uic.edu>

COPYRIGHT

Copyright (C) 2015 The University of Illinois at Chicago. All Rights Reserved.

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