Project

General

Profile

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 9094 aaronmk
alias set_database=\
32
'if test "$schema"; then declare database="${database-$schema}"; fi'
33
34
fi # load new aliases
35
if self_being_included; then
36
37 9014 aaronmk
# auto-adds connection/login opts when specified
38 9099 aaronmk
# usage: mysql*() { ...; mysql_cmd "$@"; } (with alias)
39
function mysql_cmd() # usage: mysql*() { ...; mysql_cmd cmd "$@"; }
40 9014 aaronmk
{
41
	echo_func
42 9099 aaronmk
	local cmd="$1"; shift
43 9014 aaronmk
	local ssh_server="$(localize_url "$ssh_server")"
44
	local server="$(localize_url "$server")"
45 9050 aaronmk
	if test "$ssh_server"; then
46 9014 aaronmk
		local ssh_dest="${ssh_dest-${ssh_user:+$ssh_user@}$ssh_server}"
47
	fi
48
49
	local var=ssh_dest; local_inv
50 9099 aaronmk
	command ${ssh_dest:+ssh "$ssh_dest" }"$cmd" \
51 9014 aaronmk
${server:+ --host="$server" }${user:+--user="$user" } --password\
52 9095 aaronmk
${password+="$password"} "$@"
53 9014 aaronmk
}
54 9099 aaronmk
alias mysql_cmd='mysql_cmd "$FUNCNAME"'
55 9014 aaronmk
56 9099 aaronmk
fi # load new aliases
57
if self_being_included; then
58
59 9101 aaronmk
mysql() # usage: [output_data=1] mysql ...
60 9095 aaronmk
{
61
	echo_func
62
	set_database
63 9101 aaronmk
64 9151 aaronmk
	set -- ${database:+--database="$database" }--column-names "$@"
65 9150 aaronmk
	if test "$output_data"; then echo_stdin|mysql_cmd --batch "$@"
66 9149 aaronmk
	else limit_stdout_cmd mysql_cmd --verbose "$@"
67 9101 aaronmk
	fi
68 9095 aaronmk
}
69 9014 aaronmk
70 9074 aaronmk
mysql_ANSI()
71 9014 aaronmk
{
72
	echo_func
73
	(echo "SET sql_mode = 'ANSI';"; cat)|mysql "$@"
74
}
75
76 9152 aaronmk
mysql_export() # does not support CSV
77 9102 aaronmk
{
78
	echo_func
79 9152 aaronmk
	mk_select
80
81
	output_data=1 mysql_ANSI "$@" <<<"$query"
82
}
83
84
mysql_export_outfile() # supports CSV, but requires the FILE privilege
85
{
86
	echo_func
87 9102 aaronmk
	: "${file:?}"
88
	mk_select
89
	local mysql_load_data_format="${mysql_load_data_format-\
90
FIELDS TERMINATED BY ','
91
OPTIONALLY ENCLOSED BY '\"'
92
}"
93
94
	local head="${query%%FROM*}" # includes trailing newline
95 9105 aaronmk
	head="${head%
96
}"
97 9102 aaronmk
	local tail="${query#$head}"
98 9106 aaronmk
	mysql_load_data_format="${mysql_load_data_format%
99
}"
100 9102 aaronmk
	mysql_ANSI "$@" <<EOF
101 9105 aaronmk
$head
102
INTO OUTFILE '$file'
103 9106 aaronmk
$mysql_load_data_format
104
$tail
105 9102 aaronmk
EOF
106
}
107
108 9074 aaronmk
mysqldump() # usage: [schema=1 | data=1] mysqldump db [table...]
109 9014 aaronmk
{
110
	echo_func
111 9095 aaronmk
	mysql_cmd ${database:+--databases "$database" --tables } \
112
--quick --lock-tables=false --set-charset \
113 9014 aaronmk
${postgres_compat:+--compatible=postgresql --add-locks=false }\
114
${schema:+--no-data }${data:+--no-create-info }"$@"
115
}
116
117 9074 aaronmk
mysqldump_diffable()
118 9014 aaronmk
{
119
	echo_func
120 9066 aaronmk
	mysqldump "$@"|{ pipe_delay; sed 's/^(-- Dump completed).*$/\1/'; }
121 9014 aaronmk
}
122
123 9017 aaronmk
124 9014 aaronmk
### PostgreSQL
125
126 9083 aaronmk
pg_export()
127 9014 aaronmk
{
128
	echo_func
129 9088 aaronmk
	mk_select
130 9014 aaronmk
	local pg_copy_format="${pg_copy_format-CSV HEADER}"
131
132 9084 aaronmk
	psql "$@" <<<"COPY ($query) TO STDOUT $pg_copy_format;"
133 9014 aaronmk
}
134
135 9074 aaronmk
pg_header()
136 9014 aaronmk
{
137
	echo_func
138
	local pg_copy_format="CSV HEADER" limit=0
139 9083 aaronmk
	pg_export "$@"|echo_stdout
140 9014 aaronmk
}
141
142 9074 aaronmk
pg_export_table_no_header()
143 9014 aaronmk
{
144
	echo_func
145
	local pg_copy_format="CSV"
146 9083 aaronmk
	pg_export "$@"
147 9014 aaronmk
}
148
149 9074 aaronmk
pg_export_table_to_dir_no_header()
150 9014 aaronmk
{
151
	echo_func
152
	local table="$1"; shift; mk_table_esc
153
	local cols="$(pg_header)"
154 9042 aaronmk
	stdout="$exports_dir/$table.no_header.cols=$cols.csv" to_file \
155
pg_export_table_no_header "$@"
156 9014 aaronmk
}
157
158
fi