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 9468 aaronmk
mk_select() # usage: {query=... | table=... [cols=...] [filter=...]} mk_select
46
{
47
	echo_func; kw_params query table cols filter; mk_table_esc
48
	echo "$(rtrim "${query:-SELECT ${cols:-*} ${cols:+
49 9335 aaronmk
}FROM $table_esc
50
${filter:+WHERE $filter
51 9467 aaronmk
}}")\
52
$(limit)"
53 9468 aaronmk
}
54 9088 aaronmk
55 9468 aaronmk
# export func usage: export_func() { ...; mk_select_var; ... }
56
# caller usage: {query=... | table=... [cols=...] [filter=...]} export_func
57
# cmd line usage: [limit=...] caller
58
alias mk_select_var='declare query="$(mk_select)"'
59
60 9014 aaronmk
fi # load new aliases
61
if self_being_included; then
62
63 9482 aaronmk
mk_drop() # usage: table=... mk_drop|*sql_ANSI
64
{
65
	log++; echo_func; kw_params table; : "${table?}"; mk_table_esc
66
	echo "DROP TABLE IF EXISTS $table_esc"
67
}
68
69 9465 aaronmk
mk_truncate() # usage: table=... mk_truncate|*sql_ANSI
70 9432 aaronmk
{
71
	log++; echo_func; kw_params table; : "${table?}"; mk_table_esc
72
	echo "TRUNCATE $table_esc"
73
}
74 9014 aaronmk
75 9432 aaronmk
76 9014 aaronmk
### MySQL
77
78 9094 aaronmk
alias set_database=\
79
'if test "$schema"; then declare database="${database-$schema}"; fi'
80
81
fi # load new aliases
82
if self_being_included; then
83
84 9099 aaronmk
# usage: mysql*() { ...; mysql_cmd "$@"; } (with alias)
85
function mysql_cmd() # usage: mysql*() { ...; mysql_cmd cmd "$@"; }
86 9271 aaronmk
# auto-adds connection/login opts when specified
87 9014 aaronmk
{
88
	echo_func
89 9099 aaronmk
	local cmd="$1"; shift
90 9014 aaronmk
	local ssh_server="$(localize_url "$ssh_server")"
91
	local server="$(localize_url "$server")"
92 9050 aaronmk
	if test "$ssh_server"; then
93 9014 aaronmk
		local ssh_dest="${ssh_dest-${ssh_user:+$ssh_user@}$ssh_server}"
94
	fi
95
96
	local var=ssh_dest; local_inv
97 9701 aaronmk
	command "time" ${ssh_dest:+ssh "$ssh_dest" }"$cmd" \
98 9014 aaronmk
${server:+ --host="$server" }${user:+--user="$user" } --password\
99 9694 aaronmk
${password+="$password"} --quick "$@" # --quick: don't buffer entire result
100 9014 aaronmk
}
101 9099 aaronmk
alias mysql_cmd='mysql_cmd "$FUNCNAME"'
102 9014 aaronmk
103 9099 aaronmk
fi # load new aliases
104
if self_being_included; then
105
106 9706 aaronmk
function mysql() # usage: [output_data=1] [data_only=1] [log_queries=] mysql ...
107 9095 aaronmk
{
108 9578 aaronmk
	echo_func; kw_params output_data data_only query_verbosity
109 9557 aaronmk
	if test "$data_only"; then local output_data="${output_data-1}"; fi
110 9578 aaronmk
	local log_queries="${log_queries-1}"
111 9095 aaronmk
	set_database
112 9101 aaronmk
113 9557 aaronmk
	set -- ${database:+--database="$database" }--local-infile=1 \
114
--${data_only:+skip-}column-names "$@"
115 9150 aaronmk
	if test "$output_data"; then echo_stdin|mysql_cmd --batch "$@"
116 9591 aaronmk
	else cmd_log_fd=1 mysql_cmd ${log_queries:+--verbose --verbose --verbose }\
117
"$@" # --verbose*3: echo runtimes
118 9101 aaronmk
	fi
119 9095 aaronmk
}
120 9014 aaronmk
121 9074 aaronmk
mysql_ANSI()
122 9014 aaronmk
{
123
	echo_func
124
	(echo "SET sql_mode = 'ANSI';"; cat)|mysql "$@"
125
}
126
127 9706 aaronmk
# always use ANSI mode, to support "" identifiers. `` are *still* supported in
128
# this mode, so it also works with SHOW CREATE TABLE output and dumpfiles.
129
alias mysql='mysql_ANSI'
130
131 9707 aaronmk
fi # load new aliases
132
if self_being_included; then
133
134
mysql_root() { echo_func; use_root; mysql "$@"; }
135
136 9777 aaronmk
mysql_truncate() { echo_func; mk_truncate|mysql; }
137 9466 aaronmk
138 9456 aaronmk
mysql_import() # usage: table=... [cols=...] [append=1] mysql_import <file
139
# without append=1, first ensures the table is empty
140 9430 aaronmk
{
141
	echo_func
142
	mk_table_esc
143
	local mysql_load_data_format="${mysql_load_data_format-\
144
FIELDS TERMINATED BY ','
145
OPTIONALLY ENCLOSED BY '\"'
146
}"
147
148 9466 aaronmk
	if test ! "$append"; then mysql_truncate; fi
149 9430 aaronmk
	mysql_load_data_format="${mysql_load_data_format%
150
}"
151 9642 aaronmk
	ssh2local # ssh does not tunnel nonstandard fds
152 9777 aaronmk
	mysql "$@" 40<&0 <<EOF
153 9651 aaronmk
LOAD DATA LOCAL INFILE '/dev/fd/40'
154 9660 aaronmk
${append:+IGNORE }INTO TABLE $table_esc
155 9430 aaronmk
$mysql_load_data_format
156
IGNORE 1 LINES
157
EOF
158
}
159
160 9152 aaronmk
mysql_export() # does not support CSV
161 9348 aaronmk
# caller usage: {query=... | table=... [cols=...] [filter=...]} mysql_export
162
# cmd line usage: [limit=...] caller
163 9102 aaronmk
{
164
	echo_func
165 9463 aaronmk
	mk_select_var
166 9152 aaronmk
167 9777 aaronmk
	output_data=1 mysql "$@" <<<"$query"
168 9152 aaronmk
}
169
170
mysql_export_outfile() # supports CSV, but requires the FILE privilege
171
{
172
	echo_func
173 9102 aaronmk
	: "${file:?}"
174 9463 aaronmk
	mk_select_var
175 9102 aaronmk
	local mysql_load_data_format="${mysql_load_data_format-\
176
FIELDS TERMINATED BY ','
177
OPTIONALLY ENCLOSED BY '\"'
178
}"
179
180
	local head="${query%%FROM*}" # includes trailing newline
181 9105 aaronmk
	head="${head%
182
}"
183 9102 aaronmk
	local tail="${query#$head}"
184 9106 aaronmk
	mysql_load_data_format="${mysql_load_data_format%
185
}"
186 9777 aaronmk
	mysql "$@" <<EOF
187 9105 aaronmk
$head
188
INTO OUTFILE '$file'
189 9106 aaronmk
$mysql_load_data_format
190
$tail
191 9102 aaronmk
EOF
192
}
193
194 9074 aaronmk
mysqldump() # usage: [schema=1 | data=1] mysqldump db [table...]
195 9014 aaronmk
{
196 9235 aaronmk
	echo_func; kw_params schema data
197 9231 aaronmk
198 9095 aaronmk
	mysql_cmd ${database:+--databases "$database" --tables } \
199 9694 aaronmk
--lock-tables=false --set-charset \
200 9014 aaronmk
${postgres_compat:+--compatible=postgresql --add-locks=false }\
201
${schema:+--no-data }${data:+--no-create-info }"$@"
202
}
203
204 9074 aaronmk
mysqldump_diffable()
205 9014 aaronmk
{
206
	echo_func
207 9550 aaronmk
	mysqldump "$@"|{ pipe_delay; echo_run sed 's/^(-- Dump completed).*$/\1/'; }
208 9014 aaronmk
}
209
210 9588 aaronmk
mysql_rm_privileged_statements()
211
{
212
	echo_func
213
	grep -vE -e '^/\*!40000 ALTER TABLE `.*` (DIS|EN)ABLE KEYS \*/;$' \
214
-e '^(UN)?LOCK TABLES( `.*` WRITE)?;$'
215
}
216 9017 aaronmk
217 9632 aaronmk
## permissions
218 9588 aaronmk
219 9632 aaronmk
mysql_seal_table() # usage: table=... user=... mysql_seal_table
220
# prevents further modifications to a table by a user
221
{
222 9634 aaronmk
	echo_func; kw_params table user; : "${table:?}" "${user:?}"; mk_table_esc
223 9632 aaronmk
224 9635 aaronmk
	benign_error=1 mysql_root <<<"REVOKE ALL PRIVILEGES ON $table FROM '$user'@'%'" || true
225 9636 aaronmk
	benign_error=1 mysql_root <<<"REVOKE GRANT OPTION   ON $table FROM '$user'@'%'" || true
226 9632 aaronmk
}
227
228
229 9014 aaronmk
### PostgreSQL
230
231 9648 aaronmk
alias use_pg='declare prefix=pg_; import_vars; unset prefix'
232
233 9649 aaronmk
fi # load new aliases
234
if self_being_included; then
235
236 9646 aaronmk
psql() # usage: [stdin=copy_pstdin_file] psql <<<"cmds, e.g. \copy from pstdin"
237
{
238
	echo_func; kw_params stdin
239
240 9649 aaronmk
	use_pg
241 10154 aaronmk
	log- 2
242 9649 aaronmk
	local PGHOST="$server";       export PGHOST
243
	local PGUSER="$user";         export PGUSER
244
	local PGPASSWORD="$password"; export PGPASSWORD
245
	local PGDATABASE="$database"; export PGDATABASE
246
	log+ 2
247
248 9646 aaronmk
	if can_log; then set -- --echo-all --echo-hidden "$@"; fi
249 9651 aaronmk
	local redirs=("${redirs[@]}" '40<&0' "0<${stdin:-&20}" '41>&1')
250 9646 aaronmk
	(
251 10295 aaronmk
		# hide stack traces/DETAIL sections of error messages at verbosity <2
252
		if ! clog++ can_log; then echo '\set VERBOSITY terse'; fi
253 9646 aaronmk
		if can_log; then cat <<'EOF'
254
\timing on
255
SET client_min_messages = NOTICE;
256
EOF
257
		fi
258
		cat
259 9651 aaronmk
	)|cmd_log_fd=1 command psql --file /dev/fd/40 --output /dev/fd/41 \
260 9647 aaronmk
--set ON_ERROR_STOP=1 --quiet "$@"
261 9646 aaronmk
		# --output is for query *results*, not echoed statements
262
}
263
264 9083 aaronmk
pg_export()
265 9014 aaronmk
{
266
	echo_func
267 9463 aaronmk
	mk_select_var
268 9014 aaronmk
	local pg_copy_format="${pg_copy_format-CSV HEADER}"
269
270 9084 aaronmk
	psql "$@" <<<"COPY ($query) TO STDOUT $pg_copy_format;"
271 9014 aaronmk
}
272
273 9074 aaronmk
pg_header()
274 9014 aaronmk
{
275
	echo_func
276
	local pg_copy_format="CSV HEADER" limit=0
277 9083 aaronmk
	pg_export "$@"|echo_stdout
278 9014 aaronmk
}
279
280 9074 aaronmk
pg_export_table_no_header()
281 9014 aaronmk
{
282
	echo_func
283
	local pg_copy_format="CSV"
284 9083 aaronmk
	pg_export "$@"
285 9014 aaronmk
}
286
287 9074 aaronmk
pg_export_table_to_dir_no_header()
288 9014 aaronmk
{
289
	echo_func
290
	local table="$1"; shift; mk_table_esc
291 9476 aaronmk
	stdout="$exports_dir/$table.no_header.cols=$(pg_header).csv" to_file \
292 9042 aaronmk
pg_export_table_no_header "$@"
293 9014 aaronmk
}
294
295
fi