my@row2= $sth_select_target->fetchrow_array or next;
print$ret, "\n";
}
[200, "OK"];
}
$SPEC{mysql_fill_csv_columns_from_query} = {
v=> 1.1,
summary=> 'Fill CSV columns with data from a query',
description=> <<'_',
This utility is handy if you have a partially filled table (in CSV format, which
you can convert from spreadsheet or Google Sheet or whatever), where you have
some unique key already specified in the table (e.g. customer_id) and you want
to fill up other columns (e.g. customer_name, customer_email, last_order_date) from a
query:
% mysql-fill-csv-columns-from-query DBNAME TABLE.csv 'SELECT c.NAME customer_name, c.email customer_email, (SELECT date FROM tblorders WHERE client_id=$customer_id ORDER BY date DESC LIMIT 1) last_order_time FROM tblclients WHERE id=$customer_id'
The `$NAME` in the query will be replaced by actual CSV column value. SELECT
fields must correspond to the CSV column names. For each row, a new query will
be executed and the first result row is used.
_
args=> {
%args_common,
%args_database0,
%App::CSVUtils::args_common,
%argscsv_filename1,
query=> {
schema=> 'str*',
req=> 1,
pos=> 2,
},
count=> {
summary=> 'Instead of returning the CSV rows, just return the count of rows that get filled',
You can see that row id=1 in both tables are identical. This will be skipped. On
the other hand, row id=2 in C<t1> is different with row id=2 in C<t2>. This row
will be adjusted: C<id> will be changed to 2+1000=1002. So the final rows in
table C<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):
=over 4
=item * B<adjust>* => I<str>
How to adjust the value of the PK column.
Currently the choices are:
=over
=item * "add N" add N to the original value.
=item * "subtract N" subtract N from the original value.
=back
=item * B<database>* => I<str>
(No description)
=item * B<from>* => I<str>
Name of source table.
=item * B<host> => I<str> (default: "localhost")
(No description)
=item * B<password> => I<str>
Will try to get default from C<~/.my.cnf>.
=item * B<pk_column>* => I<str>
Name of PK column.
=item * B<port> => I<int> (default: 3306)
(No description)
=item * B<to>* => I<str>
Name of target table.
=item * B<username> => I<str>
Will try to get default from C<~/.my.cnf>.
=back
Special arguments:
=over 4
=item * B<-dry_run> => I<bool>
Pass -dry_run=E<gt>1 to enable simulation mode.
=back
Returns an enveloped result (an array).
First element ($status_code) is an integer containing HTTP-like status code
(200 means OK, 4xx caller error, 5xx function error). Second element
($reason) is a string containing error message, or something like "OK" if status is
200. Third element ($payload) is the actual result, but usually not present when enveloped result is an error response ($status_code is not 2xx). Fourth
element (%result_meta) is called result metadata and is optional, a hash
that contains extra information, much like how HTTP response headers provide additional metadata.
For safety, the default is dry-run mode. To actually drop the tables, you must
supply C<--no-dry-run> or DRY_RUN=0.
This function is not exported.
This function supports dry-run operation.
Arguments ('*' denotes required arguments):
=over 4
=item * B<database>* => I<str>
(No description)
=item * B<host> => I<str> (default: "localhost")
(No description)
=item * B<password> => I<str>
Will try to get default from C<~/.my.cnf>.
=item * B<port> => I<int> (default: 3306)
(No description)
=item * B<username> => I<str>
Will try to get default from C<~/.my.cnf>.
=back
Special arguments:
=over 4
=item * B<-dry_run> => I<bool>
Pass -dry_run=E<gt>1 to enable simulation mode.
=back
Returns an enveloped result (an array).
First element ($status_code) is an integer containing HTTP-like status code
(200 means OK, 4xx caller error, 5xx function error). Second element
($reason) is a string containing error message, or something like "OK" if status is
200. Third element ($payload) is the actual result, but usually not present when enveloped result is an error response ($status_code is not 2xx). Fourth
element (%result_meta) is called result metadata and is optional, a hash
that contains extra information, much like how HTTP response headers provide additional metadata.
First element ($status_code) is an integer containing HTTP-like status code
(200 means OK, 4xx caller error, 5xx function error). Second element
($reason) is a string containing error message, or something like "OK" if status is
200. Third element ($payload) is the actual result, but usually not present when enveloped result is an error response ($status_code is not 2xx). Fourth
element (%result_meta) is called result metadata and is optional, a hash
that contains extra information, much like how HTTP response headers provide additional metadata.
For safety, the default is dry-run mode. To actually drop the tables, you must
supply C<--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):
=over 4
=item * B<database>* => I<str>
(No description)
=item * B<host> => I<str> (default: "localhost")
(No description)
=item * B<limit> => I<posint>
Don't delete more than this number of tables.
=item * B<password> => I<str>
Will try to get default from C<~/.my.cnf>.
=item * B<port> => I<int> (default: 3306)
(No description)
=item * B<table_pattern> => I<re>
(No description)
=item * B<tables> => I<array[str]>
(No description)
=item * B<username> => I<str>
Will try to get default from C<~/.my.cnf>.
=back
Special arguments:
=over 4
=item * B<-dry_run> => I<bool>
Pass -dry_run=E<gt>1 to enable simulation mode.
=back
Returns an enveloped result (an array).
First element ($status_code) is an integer containing HTTP-like status code
(200 means OK, 4xx caller error, 5xx function error). Second element
($reason) is a string containing error message, or something like "OK" if status is
200. Third element ($payload) is the actual result, but usually not present when enveloped result is an error response ($status_code is not 2xx). Fourth
element (%result_meta) is called result metadata and is optional, a hash
that contains extra information, much like how HTTP response headers provide additional metadata.
This utility is handy if you have a partially filled table (in CSV format, which
you can convert from spreadsheet or Google Sheet or whatever), where you have
some unique key already specified in the table (e.g. customer_id) and you want
to fill up other columns (e.g. customer_name, customer_email, last_order_date) from a
query:
% mysql-fill-csv-columns-from-query DBNAME TABLE.csv 'SELECT c.NAME customer_name, c.email customer_email, (SELECT date FROM tblorders WHERE client_id=$customer_id ORDER BY date DESC LIMIT 1) last_order_time FROM tblclients WHERE id=$customer_id'
The C<$NAME> in the query will be replaced by actual CSV column value. SELECT
fields must correspond to the CSV column names. For each row, a new query will
be executed and the first result row is used.
This function is not exported.
This function supports dry-run operation.
Arguments ('*' denotes required arguments):
=over 4
=item * B<count> => I<bool>
Instead of returning the CSV rows, just return the count of rows that get filled.
=item * B<database>* => I<str>
(No description)
=item * B<escape_char> => I<str>
Specify character to escape value in field in input CSV, will be passed to Text::CSV_XS.
Defaults to C<\\> (backslash). Overrides C<--tsv> option.
=item * B<filename>* => I<filename>
Input CSV file.
=item * B<header> => I<bool> (default: 1)
Whether input CSV has a header row.
By default (C<--header>), the first row of the CSV will be assumed to contain
field names (and the second row contains the first data row). When you declare
that CSV does not have header row (C<--no-header>), the first row of the CSV is
assumed to contain the first data row. Fields will be named C<field1>, C<field2>,
and so on.
=item * B<host> => I<str> (default: "localhost")
(No description)
=item * B<password> => I<str>
Will try to get default from C<~/.my.cnf>.
=item * B<port> => I<int> (default: 3306)
(No description)
=item * B<query>* => I<str>
(No description)
=item * B<quote_char> => I<str>
Specify field quote character in input CSV, will be passed to Text::CSV_XS.
Defaults to C<"> (double quote). Overrides C<--tsv> option.
=item * B<sep_char> => I<str>
Specify field separator character in input CSV, will be passed to Text::CSV_XS.
Defaults to C<,> (comma). Overrides C<--tsv> option.
=item * B<tsv> => I<bool>
Inform that input file is in TSV (tab-separated) format instead of CSV.
Overriden by C<--sep-char>, C<--quote-char>, C<--escape-char> options. If one of
those options is specified, then C<--tsv> will be ignored.
=item * B<username> => I<str>
Will try to get default from C<~/.my.cnf>.
=back
Special arguments:
=over 4
=item * B<-dry_run> => I<bool>
Pass -dry_run=E<gt>1 to enable simulation mode.
=back
Returns an enveloped result (an array).
First element ($status_code) is an integer containing HTTP-like status code
(200 means OK, 4xx caller error, 5xx function error). Second element
($reason) is a string containing error message, or something like "OK" if status is
200. Third element ($payload) is the actual result, but usually not present when enveloped result is an error response ($status_code is not 2xx). Fourth
element (%result_meta) is called result metadata and is optional, a hash
that contains extra information, much like how HTTP response headers provide additional metadata.
List rows on one table that are identical on another table.
This function is not exported.
Arguments ('*' denotes required arguments):
=over 4
=item * B<database>* => I<str>
(No description)
=item * B<exclude_columns> => I<array[str]>
What column(s) to exclude from comparison.
=item * B<host> => I<str> (default: "localhost")
(No description)
=item * B<password> => I<str>
Will try to get default from C<~/.my.cnf>.
=item * B<port> => I<int> (default: 3306)
(No description)
=item * B<return_column>* => I<str>
What column to return.
=item * B<t1>* => I<str>
Name of the first table.
=item * B<t2>* => I<str>
Name of the second table.
=item * B<username> => I<str>
Will try to get default from C<~/.my.cnf>.
=back
Returns an enveloped result (an array).
First element ($status_code) is an integer containing HTTP-like status code
(200 means OK, 4xx caller error, 5xx function error). Second element
($reason) is a string containing error message, or something like "OK" if status is
200. Third element ($payload) is the actual result, but usually not present when enveloped result is an error response ($status_code is not 2xx). Fourth
element (%result_meta) is called result metadata and is optional, a hash
that contains extra information, much like how HTTP response headers provide additional metadata.
First element ($status_code) is an integer containing HTTP-like status code
(200 means OK, 4xx caller error, 5xx function error). Second element
($reason) is a string containing error message, or something like "OK" if status is
200. Third element ($payload) is the actual result, but usually not present when enveloped result is an error response ($status_code is not 2xx). Fourth
element (%result_meta) is called result metadata and is optional, a hash
that contains extra information, much like how HTTP response headers provide additional metadata.
C<none> means to never overwrite existing .txt file. C<older> overwrites existing
.txt file if it's older than the corresponding .sql file. C<always> means to
always overwrite existing .txt file.
=item * B<pl_files>* => I<array[filename]>
(No description)
=back
Returns an enveloped result (an array).
First element ($status_code) is an integer containing HTTP-like status code
(200 means OK, 4xx caller error, 5xx function error). Second element
($reason) is a string containing error message, or something like "OK" if status is
200. Third element ($payload) is the actual result, but usually not present when enveloped result is an error response ($status_code is not 2xx). Fourth
element (%result_meta) is called result metadata and is optional, a hash
that contains extra information, much like how HTTP response headers provide additional metadata.
C<none> means to never overwrite existing .txt file. C<older> overwrites existing
.txt file if it's older than the corresponding .sql file. C<always> means to
always overwrite existing .txt file.
=item * B<sql_files>* => I<array[filename]>
(No description)
=back
Returns an enveloped result (an array).
First element ($status_code) is an integer containing HTTP-like status code
(200 means OK, 4xx caller error, 5xx function error). Second element
($reason) is a string containing error message, or something like "OK" if status is
200. Third element ($payload) is the actual result, but usually not present when enveloped result is an error response ($status_code is not 2xx). Fourth
element (%result_meta) is called result metadata and is optional, a hash
that contains extra information, much like how HTTP response headers provide additional metadata.
Parse SQL dump and spit out tables to separate files.
This function is not exported.
Arguments ('*' denotes required arguments):
=over 4
=item * B<dir> => I<dirname>
Directory to put the SQL files into.
=item * B<exclude_table_patterns> => I<array[re]>
(No description)
=item * B<exclude_tables> => I<array[str]>
(No description)
=item * B<include_table_patterns> => I<array[re]>
(No description)
=item * B<include_tables> => I<array[str]>
(No description)
=item * B<overwrite> => I<bool>
(No description)
=item * B<stop_after_table> => I<str>
(No description)
=item * B<stop_after_table_pattern> => I<re>
(No description)
=back
Returns an enveloped result (an array).
First element ($status_code) is an integer containing HTTP-like status code
(200 means OK, 4xx caller error, 5xx function error). Second element
($reason) is a string containing error message, or something like "OK" if status is
200. Third element ($payload) is the actual result, but usually not present when enveloped result is an error response ($status_code is not 2xx). Fourth
element (%result_meta) is called result metadata and is optional, a hash
that contains extra information, much like how HTTP response headers provide additional metadata.