| 1 |
9014
|
aaronmk
|
#!/bin/bash -e
|
| 2 |
|
|
. "$(dirname "${BASH_SOURCE[0]}")"/util.sh
|
| 3 |
|
|
|
| 4 |
|
|
if self_not_included; then
|
| 5 |
|
|
|
| 6 |
|
|
# using prefixed connection vars
|
| 7 |
|
|
alias use_local='declare prefix=local_; import_vars'
|
| 8 |
|
|
alias use_remote='declare prefix=remote_; import_vars'
|
| 9 |
9093
|
aaronmk
|
alias use_local_remote='use_local; use_remote' # *must* be run inside a function
|
| 10 |
9014
|
aaronmk
|
|
| 11 |
|
|
quote='"'
|
| 12 |
|
|
|
| 13 |
9074
|
aaronmk
|
esc_name() { echo "$quote${1//$quote/$quote$quote}$quote"; }
|
| 14 |
9014
|
aaronmk
|
|
| 15 |
9074
|
aaronmk
|
mk_esc_name() { set_var "$1"_esc "$(esc_name "${!1}")"; }
|
| 16 |
9014
|
aaronmk
|
|
| 17 |
|
|
alias mk_schema_esc='declare schema_esc; mk_esc_name schema'
|
| 18 |
|
|
alias mk_table_esc='declare table_esc; mk_esc_name table'
|
| 19 |
|
|
|
| 20 |
9088
|
aaronmk
|
alias mk_select='mk_table_esc
|
| 21 |
|
|
declare query="${query:-SELECT * FROM $table_esc}${limit:+ LIMIT $limit}"'
|
| 22 |
|
|
|
| 23 |
9014
|
aaronmk
|
fi # load new aliases
|
| 24 |
|
|
if self_being_included; then
|
| 25 |
|
|
|
| 26 |
9074
|
aaronmk
|
log_sql() { test "$verbosity" -ge 2; }
|
| 27 |
9014
|
aaronmk
|
|
| 28 |
9017
|
aaronmk
|
|
| 29 |
9014
|
aaronmk
|
### MySQL
|
| 30 |
|
|
|
| 31 |
|
|
# auto-adds connection/login opts when specified
|
| 32 |
9074
|
aaronmk
|
mysql_cmd() # usage: mysql*() { ...; mysql_cmd "$@"; }
|
| 33 |
9014
|
aaronmk
|
{
|
| 34 |
|
|
echo_func
|
| 35 |
|
|
local ssh_server="$(localize_url "$ssh_server")"
|
| 36 |
|
|
local server="$(localize_url "$server")"
|
| 37 |
9050
|
aaronmk
|
if test "$ssh_server"; then
|
| 38 |
9014
|
aaronmk
|
local ssh_dest="${ssh_dest-${ssh_user:+$ssh_user@}$ssh_server}"
|
| 39 |
|
|
fi
|
| 40 |
9050
|
aaronmk
|
if test "$schema"; then local database="${database-$schema}"; fi
|
| 41 |
9014
|
aaronmk
|
|
| 42 |
|
|
local var=ssh_dest; local_inv
|
| 43 |
9075
|
aaronmk
|
command ${ssh_dest:+ssh "$ssh_dest" }"${FUNCNAME[1]}" \
|
| 44 |
9014
|
aaronmk
|
${server:+ --host="$server" }${user:+--user="$user" } --password\
|
| 45 |
|
|
${password+="$password"} ${database:+--databases "$database" --tables } "$@"
|
| 46 |
|
|
}
|
| 47 |
|
|
|
| 48 |
9074
|
aaronmk
|
mysql() { echo_func; mysql_cmd --verbose "$@"; }
|
| 49 |
9014
|
aaronmk
|
|
| 50 |
9074
|
aaronmk
|
mysql_ANSI()
|
| 51 |
9014
|
aaronmk
|
{
|
| 52 |
|
|
echo_func
|
| 53 |
|
|
(echo "SET sql_mode = 'ANSI';"; cat)|mysql "$@"
|
| 54 |
|
|
}
|
| 55 |
|
|
|
| 56 |
9074
|
aaronmk
|
mysqldump() # usage: [schema=1 | data=1] mysqldump db [table...]
|
| 57 |
9014
|
aaronmk
|
{
|
| 58 |
|
|
echo_func
|
| 59 |
|
|
mysql_cmd --quick --lock-tables=false --set-charset \
|
| 60 |
|
|
${postgres_compat:+--compatible=postgresql --add-locks=false }\
|
| 61 |
|
|
${schema:+--no-data }${data:+--no-create-info }"$@"
|
| 62 |
|
|
}
|
| 63 |
|
|
|
| 64 |
9074
|
aaronmk
|
mysqldump_diffable()
|
| 65 |
9014
|
aaronmk
|
{
|
| 66 |
|
|
echo_func
|
| 67 |
9066
|
aaronmk
|
mysqldump "$@"|{ pipe_delay; sed 's/^(-- Dump completed).*$/\1/'; }
|
| 68 |
9014
|
aaronmk
|
}
|
| 69 |
|
|
|
| 70 |
9017
|
aaronmk
|
|
| 71 |
9014
|
aaronmk
|
### PostgreSQL
|
| 72 |
|
|
|
| 73 |
9083
|
aaronmk
|
pg_export()
|
| 74 |
9014
|
aaronmk
|
{
|
| 75 |
|
|
echo_func
|
| 76 |
9088
|
aaronmk
|
mk_select
|
| 77 |
9014
|
aaronmk
|
local pg_copy_format="${pg_copy_format-CSV HEADER}"
|
| 78 |
|
|
|
| 79 |
9084
|
aaronmk
|
psql "$@" <<<"COPY ($query) TO STDOUT $pg_copy_format;"
|
| 80 |
9014
|
aaronmk
|
}
|
| 81 |
|
|
|
| 82 |
9074
|
aaronmk
|
pg_header()
|
| 83 |
9014
|
aaronmk
|
{
|
| 84 |
|
|
echo_func
|
| 85 |
|
|
local pg_copy_format="CSV HEADER" limit=0
|
| 86 |
9083
|
aaronmk
|
pg_export "$@"|echo_stdout
|
| 87 |
9014
|
aaronmk
|
}
|
| 88 |
|
|
|
| 89 |
9074
|
aaronmk
|
pg_export_table_no_header()
|
| 90 |
9014
|
aaronmk
|
{
|
| 91 |
|
|
echo_func
|
| 92 |
|
|
local pg_copy_format="CSV"
|
| 93 |
9083
|
aaronmk
|
pg_export "$@"
|
| 94 |
9014
|
aaronmk
|
}
|
| 95 |
|
|
|
| 96 |
9074
|
aaronmk
|
pg_export_table_to_dir_no_header()
|
| 97 |
9014
|
aaronmk
|
{
|
| 98 |
|
|
echo_func
|
| 99 |
|
|
local table="$1"; shift; mk_table_esc
|
| 100 |
|
|
local cols="$(pg_header)"
|
| 101 |
9042
|
aaronmk
|
stdout="$exports_dir/$table.no_header.cols=$cols.csv" to_file \
|
| 102 |
|
|
pg_export_table_no_header "$@"
|
| 103 |
9014
|
aaronmk
|
}
|
| 104 |
|
|
|
| 105 |
|
|
fi
|
