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 9641 aaronmk
alias ssh2local='declare prefix=ssh_; import_vars; unset prefix
8
declare $(prefix=ssh_ get_prefix_vars); unset $(prefix=ssh_ get_prefix_vars)'
9 9464 aaronmk
alias use_local='declare prefix=local_; import_vars; unset prefix'
10
alias use_remote='declare prefix=remote_; import_vars; unset prefix'
11 9167 aaronmk
alias use_local_remote='{ use_local; use_remote; }'
12
	# *must* be run inside a function
13 9574 aaronmk
alias use_root='declare prefix=root_; import_vars; unset prefix'
14 9014 aaronmk
15
quote='"'
16
17 9074 aaronmk
esc_name() { echo "$quote${1//$quote/$quote$quote}$quote"; }
18 9014 aaronmk
19 9423 aaronmk
mk_esc_name_alias() # usage: mk_esc_name_alias schema_esc
20 9426 aaronmk
{ alias mk_"$1"='declare '"$1"'="${'"$1"':-$(esc_name "$'"${1%_esc}"'")}"; '\
21
'echo_vars '"$1"; }
22 9014 aaronmk
23 9423 aaronmk
mk_esc_name_alias schema_esc
24
mk_esc_name_alias table_esc
25
26 9470 aaronmk
fi # load new aliases
27
if self_being_included; then
28
29 9623 aaronmk
skip_table() # usage: create_table || table=... skip_table || return 0
30
{ what="table $table" already_exists_msg; }
31
32 9817 aaronmk
: "${test=$(isset limit; exit2bool)}" # test mode when using limited # rows
33
34 9698 aaronmk
# usage: (set_large_table; to_file db_cmd...) || return
35
alias set_large_table='declare del="${del-$(test "$limit"; exit2bool)}"
36
echo_vars del'
37
38 9700 aaronmk
limit() # usage: "query... $([prefix=$'| |\n'] limit)"
39 9467 aaronmk
{
40
	echo_func; kw_params prefix; local prefix="${prefix-
41
}"
42
	echo -n "${limit:+${prefix}LIMIT $limit}"
43
}
44
45 10401 aaronmk
cols2list() # usage: cols2list col...
46
{ echo_func; cmd=esc_name foreach_arg; delim=', ' join "$@"; }
47
48 10827 aaronmk
mk_select() # usage: {query=... | table=... [cols=...] [filter=...] \
49
# [order_by=...]} mk_select
50 9468 aaronmk
{
51
	echo_func; kw_params query table cols filter; mk_table_esc
52 10827 aaronmk
	if is_array cols    ; then cols="$(    cols2list "${cols[@]}"    )"; fi
53
	if is_array order_by; then order_by="$(cols2list "${order_by[@]}")"; fi
54 9468 aaronmk
	echo "$(rtrim "${query:-SELECT ${cols:-*} ${cols:+
55 9335 aaronmk
}FROM $table_esc
56
${filter:+WHERE $filter
57 10827 aaronmk
}${order_by:+ORDER BY $order_by
58 9467 aaronmk
}}")\
59
$(limit)"
60 9468 aaronmk
}
61 9088 aaronmk
62 9468 aaronmk
# export func usage: export_func() { ...; mk_select_var; ... }
63
# caller usage: {query=... | table=... [cols=...] [filter=...]} export_func
64
# cmd line usage: [limit=...] caller
65
alias mk_select_var='declare query="$(mk_select)"'
66
67 9014 aaronmk
fi # load new aliases
68
if self_being_included; then
69
70 9482 aaronmk
mk_drop() # usage: table=... mk_drop|*sql_ANSI
71
{
72
	log++; echo_func; kw_params table; : "${table?}"; mk_table_esc
73
	echo "DROP TABLE IF EXISTS $table_esc"
74
}
75
76 9465 aaronmk
mk_truncate() # usage: table=... mk_truncate|*sql_ANSI
77 9432 aaronmk
{
78
	log++; echo_func; kw_params table; : "${table?}"; mk_table_esc
79
	echo "TRUNCATE $table_esc"
80
}
81 9014 aaronmk
82 9432 aaronmk
83 9014 aaronmk
### MySQL
84
85 9094 aaronmk
alias set_database=\
86
'if test "$schema"; then declare database="${database-$schema}"; fi'
87
88
fi # load new aliases
89
if self_being_included; then
90
91 9099 aaronmk
# usage: mysql*() { ...; mysql_cmd "$@"; } (with alias)
92 10888 aaronmk
# caller usage: [ssh_server=...] [server=...] [user=...] [password=...] mysql*()
93 9099 aaronmk
function mysql_cmd() # usage: mysql*() { ...; mysql_cmd cmd "$@"; }
94 9271 aaronmk
# auto-adds connection/login opts when specified
95 9014 aaronmk
{
96
	echo_func
97 9099 aaronmk
	local cmd="$1"; shift
98 9014 aaronmk
	local ssh_server="$(localize_url "$ssh_server")"
99
	local server="$(localize_url "$server")"
100 9050 aaronmk
	if test "$ssh_server"; then
101 9014 aaronmk
		local ssh_dest="${ssh_dest-${ssh_user:+$ssh_user@}$ssh_server}"
102
	fi
103
104
	local var=ssh_dest; local_inv
105 9701 aaronmk
	command "time" ${ssh_dest:+ssh "$ssh_dest" }"$cmd" \
106 9014 aaronmk
${server:+ --host="$server" }${user:+--user="$user" } --password\
107 9694 aaronmk
${password+="$password"} --quick "$@" # --quick: don't buffer entire result
108 9014 aaronmk
}
109 9099 aaronmk
alias mysql_cmd='mysql_cmd "$FUNCNAME"'
110 9014 aaronmk
111 9099 aaronmk
fi # load new aliases
112
if self_being_included; then
113
114 10887 aaronmk
function mysql() # usage: [database=...] [data_only=1] [log_queries=] mysql ...
115 9095 aaronmk
{
116 9578 aaronmk
	echo_func; kw_params output_data data_only query_verbosity
117 9557 aaronmk
	if test "$data_only"; then local output_data="${output_data-1}"; fi
118 9578 aaronmk
	local log_queries="${log_queries-1}"
119 9095 aaronmk
	set_database
120 9101 aaronmk
121 9557 aaronmk
	set -- ${database:+--database="$database" }--local-infile=1 \
122
--${data_only:+skip-}column-names "$@"
123 9150 aaronmk
	if test "$output_data"; then echo_stdin|mysql_cmd --batch "$@"
124 9591 aaronmk
	else cmd_log_fd=1 mysql_cmd ${log_queries:+--verbose --verbose --verbose }\
125
"$@" # --verbose*3: echo runtimes
126 9101 aaronmk
	fi
127 9095 aaronmk
}
128 9014 aaronmk
129 9074 aaronmk
mysql_ANSI()
130 9014 aaronmk
{
131
	echo_func
132
	(echo "SET sql_mode = 'ANSI';"; cat)|mysql "$@"
133
}
134
135 9706 aaronmk
# always use ANSI mode, to support "" identifiers. `` are *still* supported in
136
# this mode, so it also works with SHOW CREATE TABLE output and dumpfiles.
137
alias mysql='mysql_ANSI'
138
139 9707 aaronmk
fi # load new aliases
140
if self_being_included; then
141
142
mysql_root() { echo_func; use_root; mysql "$@"; }
143
144 9777 aaronmk
mysql_truncate() { echo_func; mk_truncate|mysql; }
145 9466 aaronmk
146 9456 aaronmk
mysql_import() # usage: table=... [cols=...] [append=1] mysql_import <file
147
# without append=1, first ensures the table is empty
148 9430 aaronmk
{
149
	echo_func
150
	mk_table_esc
151
	local mysql_load_data_format="${mysql_load_data_format-\
152
FIELDS TERMINATED BY ','
153
OPTIONALLY ENCLOSED BY '\"'
154
}"
155
156 9466 aaronmk
	if test ! "$append"; then mysql_truncate; fi
157 9430 aaronmk
	mysql_load_data_format="${mysql_load_data_format%
158
}"
159 9642 aaronmk
	ssh2local # ssh does not tunnel nonstandard fds
160 9777 aaronmk
	mysql "$@" 40<&0 <<EOF
161 9651 aaronmk
LOAD DATA LOCAL INFILE '/dev/fd/40'
162 9660 aaronmk
${append:+IGNORE }INTO TABLE $table_esc
163 9430 aaronmk
$mysql_load_data_format
164
IGNORE 1 LINES
165
EOF
166
}
167
168 9152 aaronmk
mysql_export() # does not support CSV
169 10887 aaronmk
# caller usage: [database=...] {query=... | table=... [cols=...] [filter=...]} \
170
# mysql_export
171 9348 aaronmk
# cmd line usage: [limit=...] caller
172 9102 aaronmk
{
173
	echo_func
174 9463 aaronmk
	mk_select_var
175 9152 aaronmk
176 9777 aaronmk
	output_data=1 mysql "$@" <<<"$query"
177 9152 aaronmk
}
178
179
mysql_export_outfile() # supports CSV, but requires the FILE privilege
180
{
181
	echo_func
182 9102 aaronmk
	: "${file:?}"
183 9463 aaronmk
	mk_select_var
184 9102 aaronmk
	local mysql_load_data_format="${mysql_load_data_format-\
185
FIELDS TERMINATED BY ','
186
OPTIONALLY ENCLOSED BY '\"'
187
}"
188
189
	local head="${query%%FROM*}" # includes trailing newline
190 9105 aaronmk
	head="${head%
191
}"
192 9102 aaronmk
	local tail="${query#$head}"
193 9106 aaronmk
	mysql_load_data_format="${mysql_load_data_format%
194
}"
195 9777 aaronmk
	mysql "$@" <<EOF
196 9105 aaronmk
$head
197
INTO OUTFILE '$file'
198 9106 aaronmk
$mysql_load_data_format
199
$tail
200 9102 aaronmk
EOF
201
}
202
203 10649 aaronmk
mysqldump() # usage: [schema=1 | data=1] [create_db=1] mysqldump db [table...]
204 9014 aaronmk
{
205 10649 aaronmk
	echo_func
206
	echo_vars schema data create_db
207 9231 aaronmk
208 10649 aaronmk
	local var=create_db; local_inv
209
	mysql_cmd ${database:+--databases "$database" ${no_create_db:+--tables }} \
210 9694 aaronmk
--lock-tables=false --set-charset \
211 10446 aaronmk
${postgres_compat:+${data:+--compatible=postgresql }--add-locks=false }\
212 9014 aaronmk
${schema:+--no-data }${data:+--no-create-info }"$@"
213
}
214
215 9074 aaronmk
mysqldump_diffable()
216 9014 aaronmk
{
217
	echo_func
218 9550 aaronmk
	mysqldump "$@"|{ pipe_delay; echo_run sed 's/^(-- Dump completed).*$/\1/'; }
219 9014 aaronmk
}
220
221 9588 aaronmk
mysql_rm_privileged_statements()
222
{
223
	echo_func
224
	grep -vE -e '^/\*!40000 ALTER TABLE `.*` (DIS|EN)ABLE KEYS \*/;$' \
225
-e '^(UN)?LOCK TABLES( `.*` WRITE)?;$'
226
}
227 9017 aaronmk
228 9632 aaronmk
## permissions
229 9588 aaronmk
230 9632 aaronmk
mysql_seal_table() # usage: table=... user=... mysql_seal_table
231
# prevents further modifications to a table by a user
232
{
233 9634 aaronmk
	echo_func; kw_params table user; : "${table:?}" "${user:?}"; mk_table_esc
234 9632 aaronmk
235 9635 aaronmk
	benign_error=1 mysql_root <<<"REVOKE ALL PRIVILEGES ON $table FROM '$user'@'%'" || true
236 9636 aaronmk
	benign_error=1 mysql_root <<<"REVOKE GRANT OPTION   ON $table FROM '$user'@'%'" || true
237 9632 aaronmk
}
238
239
240 9014 aaronmk
### PostgreSQL
241
242 9648 aaronmk
alias use_pg='declare prefix=pg_; import_vars; unset prefix'
243
244 9649 aaronmk
fi # load new aliases
245
if self_being_included; then
246
247 10763 aaronmk
# usage: pg_*() { ...; pg_cmd "$@"; } (uses alias)
248
function pg_cmd() # usage for fn: pg_*() { ...; pg_cmd cmd "$@"; }
249
# auto-adds connection/login opts when specified
250 9646 aaronmk
{
251 10763 aaronmk
	echo_func
252 9646 aaronmk
253 9649 aaronmk
	use_pg
254 10154 aaronmk
	log- 2
255 11356 aaronmk
	if test "$server";   then local PGHOST="$server";       export PGHOST; fi
256
	if test "$user";     then local PGUSER="$user";         export PGUSER; fi
257
	if test "$password"; then local PGPASSWORD="$password"; export PGPASSWORD;fi
258
	if test "$database"; then local PGDATABASE="$database"; export PGDATABASE;fi
259 9649 aaronmk
	log+ 2
260
261 10763 aaronmk
	command "time" "$@"
262
}
263
alias pg_cmd='"pg_cmd" "${FUNCNAME%%__*}"'
264
265 11349 aaronmk
pg_as_root() # usage: pg_as_root {psql|pg_*} ... # only works inside runscript
266 11357 aaronmk
{ echo_func; : "${wrap_fn?}"; echo_run sudo -E -u postgres "$wrap_fn" "$@"; }
267 11349 aaronmk
268 10763 aaronmk
fi # load new aliases
269
if self_being_included; then
270
271
psql() # usage: [stdin=copy_pstdin_file] psql <<<"cmds, e.g. \copy from pstdin"
272 11360 aaronmk
# SUDO_USER: when set, avoids outputting to /dev/fd/#, because this causes a
273
# "Permission denied" error when running as sudo on Linux
274 10763 aaronmk
{
275
	echo_func; kw_params stdin
276 11360 aaronmk
	local verbose_="$(! isset SUDO_USER; exit2bool)"; echo_vars verbose_
277 10763 aaronmk
278 11360 aaronmk
	if test "$verbose_" && can_log; then set -- --echo-all --echo-hidden "$@";fi
279 9651 aaronmk
	local redirs=("${redirs[@]}" '40<&0' "0<${stdin:-&20}" '41>&1')
280 9646 aaronmk
	(
281 10295 aaronmk
		# hide stack traces/DETAIL sections of error messages at verbosity <2
282
		if ! clog++ can_log; then echo '\set VERBOSITY terse'; fi
283 11360 aaronmk
		if test "$verbose_" && can_log; then cat <<'EOF'
284 9646 aaronmk
\timing on
285
SET client_min_messages = NOTICE;
286
EOF
287
		fi
288
		cat
289 11360 aaronmk
	)|cmd_log_fd=${verbose_:+1} pg_cmd --file /dev/fd/40 \
290
${verbose_:+--output /dev/fd/41 }--set ON_ERROR_STOP=1 --quiet "$@"
291 9646 aaronmk
		# --output is for query *results*, not echoed statements
292
}
293
294 10755 aaronmk
pg_export() # usage: mk_select_opts... [pg_copy_format=CSV...] pg_export >out
295 9014 aaronmk
{
296
	echo_func
297 9463 aaronmk
	mk_select_var
298 9014 aaronmk
	local pg_copy_format="${pg_copy_format-CSV HEADER}"
299
300 9084 aaronmk
	psql "$@" <<<"COPY ($query) TO STDOUT $pg_copy_format;"
301 9014 aaronmk
}
302
303 9074 aaronmk
pg_header()
304 9014 aaronmk
{
305
	echo_func
306
	local pg_copy_format="CSV HEADER" limit=0
307 9083 aaronmk
	pg_export "$@"|echo_stdout
308 9014 aaronmk
}
309
310 9074 aaronmk
pg_export_table_no_header()
311 9014 aaronmk
{
312
	echo_func
313
	local pg_copy_format="CSV"
314 9083 aaronmk
	pg_export "$@"
315 9014 aaronmk
}
316
317 10825 aaronmk
pg_export_table_to_dir()
318
{
319
	echo_func
320
	local table="$1"; shift; mk_table_esc
321
	stdout="$exports_dir/$table.csv" to_file pg_export "$@"
322
}
323
324 9074 aaronmk
pg_export_table_to_dir_no_header()
325 9014 aaronmk
{
326
	echo_func
327
	local table="$1"; shift; mk_table_esc
328 9476 aaronmk
	stdout="$exports_dir/$table.no_header.cols=$(pg_header).csv" to_file \
329 9042 aaronmk
pg_export_table_no_header "$@"
330 9014 aaronmk
}
331
332 10764 aaronmk
pg_dump() # usage: [schema=...] [struct=1 | data=1] [compress=1] [owners=1] \
333 10774 aaronmk
# [create_schema=] pg_dump [opts...] >out
334 10764 aaronmk
{
335 10774 aaronmk
	echo_func; kw_params struct data plain owners create_schema
336 10775 aaronmk
	local create_schema="${create_schema-1}"
337 10764 aaronmk
338
	# subset
339
	if test "$schema"; then set -- --schema="\"$schema\"" "$@"; fi
340
341
	# format
342
	if test "$compress"; then set -- "$@" --format=c --compress=9
343
	                     else set -- "$@" --format=p --inserts # plain
344
	fi
345
	if test "$struct"  ; then set -- "$@" --schema-only; fi
346
	if test "$data"    ; then set -- "$@" --data-only  ; fi
347
	if test ! "$owners"; then set -- "$@" --no-owner   ; fi
348
349 10774 aaronmk
	(
350
		# filter output
351
		if test ! "$create_schema"; then
352
			fd=1 redirs= filter_fd sed 's/^CREATE SCHEMA/--&/'
353
		fi
354
355
		pg_cmd "$@"
356
	)
357 10764 aaronmk
}
358
359 9014 aaronmk
fi