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

NAME

App::MysqlUtils - CLI utilities related to MySQL

VERSION

This document describes version 0.013 of App::MysqlUtils (from Perl distribution App-MysqlUtils), released on 2018-12-05.

SYNOPSIS

This distribution includes the following CLI utilities:

FUNCTIONS

mysql_copy_rows_adjust_pk

Usage:

 mysql_copy_rows_adjust_pk(%args) -> [status, msg, payload, meta]

Copy rows from one table to another, adjust PK column if necessary.

This utility can be used when you have rows in one table that you want to insert to another table, but the PK might clash. When that happens, the value of the other columns are inspected. When all the values of the other columns match, the row is assumed to be a duplicate and skipped. If some values of the other column differ, then the row is assumed to be different and a new value of the PK column is chosen (there are several choices on how to select the new PK).

An example:

 % mysql-copy-rows-adjust-pk db1 --from t1 --to t2 --pk-column id --adjust "add 1000"

Suppose these are the rows in table t1:

 id    date                 description        user
 --    ----                 -----------        ----
  1    2018-12-03 12:01:01  Created user u1    admin1
  2    2018-12-03 12:44:33  Removed user u1    admin1

And here are the rows in table t2:

 id    date                 description        user
 --    ----                 -----------        ----
  1    2018-12-03 12:01:01  Created user u1    admin1
  2    2018-12-03 13:00:45  Rebooted machine1  admin1
  3    2018-12-03 13:05:00  Created user u2    admin2

You can see that row id=1 in both tables are identical. This will be skipped. On the other hand, row id=2 in t1 is different with row id=2 in t2. This row will be adjusted: id will be changed to 2+1000=1002. So the final rows in table t2 will be (sorted by date):

 id    date                 description        user
 --    ----                 -----------        ----
  1    2018-12-03 12:01:01  Created user u1    admin1
  1002 2018-12-03 12:44:33  Removed user u1    admin1
  2    2018-12-03 13:00:45  Rebooted machine1  admin1
  3    2018-12-03 13:05:00  Created user u2    admin2

So basically this utility is similar to MySQL's INSERT ... ON DUPLICATE KEY UPDATE, but will avoid inserting identical rows.

If the adjusted PK column clashes with another row in the target table, the row is skipped.

This function is not exported.

This function supports dry-run operation.

Arguments ('*' denotes required arguments):

  • adjust* => str

    How to adjust the value of the PK column.

    Currently the choices are:

    • "add N" add N to the original value.

    • "subtract N" subtract N from the original value.

  • database* => str

  • from* => str

    Name of source table.

  • host => str (default: "localhost")

  • password => str

    Will try to get default from ~/.my.cnf.

  • pk_column* => str

    Name of PK column.

  • port => int (default: 3306)

  • to* => str

    Name of target table.

  • username => str

    Will try to get default from ~/.my.cnf.

Special arguments:

  • -dry_run => bool

    Pass -dry_run=>1 to enable simulation mode.

Returns an enveloped result (an array).

First element (status) is an integer containing HTTP status code (200 means OK, 4xx caller error, 5xx function error). Second element (msg) is a string containing error message, or 'OK' if status is 200. Third element (payload) is optional, the actual result. Fourth element (meta) is called result metadata and is optional, a hash that contains extra information.

Return value: (any)

mysql_drop_all_tables

Usage:

 mysql_drop_all_tables(%args) -> [status, msg, payload, meta]

Drop all tables in a MySQL database.

For safety, the default is dry-run mode. To actually drop the tables, you must supply --no-dry-run or DRY_RUN=0.

This function is not exported.

This function supports dry-run operation.

Arguments ('*' denotes required arguments):

  • database* => str

  • host => str (default: "localhost")

  • password => str

    Will try to get default from ~/.my.cnf.

  • port => int (default: 3306)

  • username => str

    Will try to get default from ~/.my.cnf.

Special arguments:

  • -dry_run => bool

    Pass -dry_run=>1 to enable simulation mode.

Returns an enveloped result (an array).

First element (status) is an integer containing HTTP status code (200 means OK, 4xx caller error, 5xx function error). Second element (msg) is a string containing error message, or 'OK' if status is 200. Third element (payload) is optional, the actual result. Fourth element (meta) is called result metadata and is optional, a hash that contains extra information.

Return value: (any)

mysql_drop_tables

Usage:

 mysql_drop_tables(%args) -> [status, msg, payload, meta]

Drop tables in a MySQL database.

For safety, the default is dry-run mode. To actually drop the tables, you must supply --no-dry-run or DRY_RUN=0.

Examples:

 # Drop table T1, T2, T3 (dry-run mode)
 % mysql-drop-tables DB T1 T2 T3
 
 # Drop all tables with names matching /foo/ (dry-run mode)
 % mysql-drop-tables DB --table-pattern foo
 
 # Actually drop all tables with names matching /foo/, don't delete more than 5 tables
 % mysql-drop-tables DB --table-pattern foo --limit 5 --no-dry-run

This function is not exported.

This function supports dry-run operation.

Arguments ('*' denotes required arguments):

  • database* => str

  • host => str (default: "localhost")

  • limit => posint

    Don't delete more than this number of tables.

  • password => str

    Will try to get default from ~/.my.cnf.

  • port => int (default: 3306)

  • table_pattern => re

  • tables => array[str]

  • username => str

    Will try to get default from ~/.my.cnf.

Special arguments:

  • -dry_run => bool

    Pass -dry_run=>1 to enable simulation mode.

Returns an enveloped result (an array).

First element (status) is an integer containing HTTP status code (200 means OK, 4xx caller error, 5xx function error). Second element (msg) is a string containing error message, or 'OK' if status is 200. Third element (payload) is optional, the actual result. Fourth element (meta) is called result metadata and is optional, a hash that contains extra information.

Return value: (any)

mysql_find_identical_rows

Usage:

 mysql_find_identical_rows(%args) -> [status, msg, payload, meta]

List rows on one table that are identical on another table.

This function is not exported.

Arguments ('*' denotes required arguments):

  • database* => str

  • exclude_columns => array[str]

    What column(s) to exclude from comparison.

  • host => str (default: "localhost")

  • password => str

    Will try to get default from ~/.my.cnf.

  • port => int (default: 3306)

  • return_column* => str

    What column to return.

  • t1* => str

    Name of the first table.

  • t2* => str

    Name of the second table.

  • username => str

    Will try to get default from ~/.my.cnf.

Returns an enveloped result (an array).

First element (status) is an integer containing HTTP status code (200 means OK, 4xx caller error, 5xx function error). Second element (msg) is a string containing error message, or 'OK' if status is 200. Third element (payload) is optional, the actual result. Fourth element (meta) is called result metadata and is optional, a hash that contains extra information.

Return value: (any)

mysql_query

Usage:

 mysql_query(%args) -> [status, msg, payload, meta]

Run query and return table result.

This is like just regular querying, but the result will be returned as table data (formattable using different backends). Or, you can output as JSON.

Examples:

 # by default, show as pretty text table, like in interactive mysql client
 % mysql-query DBNAME "SELECT * FROM t1"
 
 # show as JSON (array of hashes)
 % mysql-query DBNAME "QUERY..." --json ;# or, --format json
 
 # show as CSV
 % mysql-query DBNAME "QUERY..." --format csv
 
 # show as CSV table using Text::Table::CSV
 % FORMAT_PRETTY_TABLE_BACKEND=Text::Table::Org mysql-query DBNAME "QUERY..."

This function is not exported.

Arguments ('*' denotes required arguments):

  • add_row_numbers => bool

    Add first field containing number from 1, 2, ...

  • database* => str

  • host => str (default: "localhost")

  • password => str

    Will try to get default from ~/.my.cnf.

  • port => int (default: 3306)

  • query* => str

  • username => str

    Will try to get default from ~/.my.cnf.

Returns an enveloped result (an array).

First element (status) is an integer containing HTTP status code (200 means OK, 4xx caller error, 5xx function error). Second element (msg) is a string containing error message, or 'OK' if status is 200. Third element (payload) is optional, the actual result. Fourth element (meta) is called result metadata and is optional, a hash that contains extra information.

Return value: (any)

mysql_run_pl_files

Usage:

 mysql_run_pl_files(%args) -> [status, msg, payload, meta]

Run each .pl file, feed the output to `mysql` command and write result to .txt file.

The .pl file is supposed to produce a SQL statement. For simpler cases, use mysql-run-sql-files.

This function is not exported.

Arguments ('*' denotes required arguments):

  • database* => str

  • directory => dirname (default: ".")

    Specify directory for the resulting *.txt files.

  • mkdir => true (default: 1)

    Create output directory if not exists.

  • overwrite_when => str (default: "none")

    Specify when to overwrite existing .txt file.

    none means to never overwrite existing .txt file. older overwrites existing .txt file if it's older than the corresponding .sql file. always means to always overwrite existing .txt file.

  • pl_files* => array[filename]

Returns an enveloped result (an array).

First element (status) is an integer containing HTTP status code (200 means OK, 4xx caller error, 5xx function error). Second element (msg) is a string containing error message, or 'OK' if status is 200. Third element (payload) is optional, the actual result. Fourth element (meta) is called result metadata and is optional, a hash that contains extra information.

Return value: (any)

mysql_run_sql_files

Usage:

 mysql_run_sql_files(%args) -> [status, msg, payload, meta]

Feed each .sql file to `mysql` command and write result to .txt file.

This function is not exported.

Arguments ('*' denotes required arguments):

  • database* => str

  • directory => dirname (default: ".")

    Specify directory for the resulting *.txt files.

  • mkdir => true (default: 1)

    Create output directory if not exists.

  • overwrite_when => str (default: "none")

    Specify when to overwrite existing .txt file.

    none means to never overwrite existing .txt file. older overwrites existing .txt file if it's older than the corresponding .sql file. always means to always overwrite existing .txt file.

  • sql_files* => array[filename]

Returns an enveloped result (an array).

First element (status) is an integer containing HTTP status code (200 means OK, 4xx caller error, 5xx function error). Second element (msg) is a string containing error message, or 'OK' if status is 200. Third element (payload) is optional, the actual result. Fourth element (meta) is called result metadata and is optional, a hash that contains extra information.

Return value: (any)

mysql_sql_dump_extract_tables

Usage:

 mysql_sql_dump_extract_tables(%args) -> [status, msg, payload, meta]

Parse SQL dump and spit out tables to separate files.

This function is not exported.

Arguments ('*' denotes required arguments):

  • dir => dirname

    Directory to put the SQL files into.

  • exclude_table_patterns => array[re]

  • exclude_tables => array[str]

  • include_table_patterns => array[re]

  • include_tables => array[str]

  • overwrite => bool

  • stop_after_table => str

  • stop_after_table_pattern => re

Returns an enveloped result (an array).

First element (status) is an integer containing HTTP status code (200 means OK, 4xx caller error, 5xx function error). Second element (msg) is a string containing error message, or 'OK' if status is 200. Third element (payload) is optional, the actual result. Fourth element (meta) is called result metadata and is optional, a hash that contains extra information.

Return value: (any)

HOMEPAGE

Please visit the project's homepage at https://metacpan.org/release/App-MysqlUtils.

SOURCE

Source repository is at https://github.com/perlancar/perl-App-MysqlUtils.

BUGS

Please report any bugs or feature requests on the bugtracker website https://rt.cpan.org/Public/Dist/Display.html?Name=App-MysqlUtils

When submitting a bug or request, please include a test-file or a patch to an existing test-file that illustrates the bug or desired feature.

SEE ALSO

AUTHOR

perlancar <perlancar@cpan.org>

COPYRIGHT AND LICENSE

This software is copyright (c) 2018, 2017, 2016 by perlancar@cpan.org.

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