Project

General

Profile

1
#!/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 ssh2local='declare prefix=ssh_; import_vars; unset prefix
8
declare $(prefix=ssh_ get_prefix_vars); unset $(prefix=ssh_ get_prefix_vars)'
9
alias use_local='declare prefix=local_; import_vars; unset prefix'
10
alias use_remote='declare prefix=remote_; import_vars; unset prefix'
11
alias use_local_remote='{ use_remote; use_local; }'
12
	# *must* be run inside a function
13
alias use_root='declare prefix=root_; import_vars; unset prefix'
14

    
15
quote='"'
16

    
17
esc_name() { echo "$quote${1//$quote/$quote$quote}$quote"; }
18

    
19
mk_esc_name_alias() # usage: mk_esc_name_alias schema_esc
20
{ alias mk_"$1"='declare '"$1"'="${'"$1"':-$(esc_name "$'"${1%_esc}"'")}"; '\
21
'echo_vars '"$1"; }
22

    
23
mk_esc_name_alias schema_esc
24
mk_esc_name_alias table_esc
25

    
26
fi # load new aliases
27
if self_being_included; then
28

    
29
skip_table() # usage: create_table || table=... skip_table || return 0
30
{ what="table $table" already_exists_msg; }
31

    
32
: "${test=$(isset limit; exit2bool)}" # test mode when using limited # rows
33

    
34
# 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
limit() # usage: "query... $([prefix=$'| |\n'] [limit|n=#] limit)"
39
{
40
	echo_func; kw_params prefix; local prefix="${prefix-
41
}"
42
	if isset n; then local limit="${limit-$n}"; fi
43
	echo -n "${limit:+${prefix}LIMIT $limit}"
44
}
45

    
46
cols2list() # usage: cols2list col...
47
{ echo_func; cmd=esc_name foreach_arg; delim=', ' join "$@"; }
48

    
49
mk_select() # usage: {query=... | table=... [cols=...] [filter=...] \
50
# [order_by=...]} [limit|n=#] mk_select
51
{
52
	echo_func; kw_params query table cols filter; mk_table_esc
53
	if is_array cols    ; then cols="$(    cols2list "${cols[@]}"    )"; fi
54
	if is_array order_by; then order_by="$(cols2list "${order_by[@]}")"; fi
55
	echo "$(rtrim "${query:-SELECT ${cols:-*} ${cols:+
56
}FROM $table_esc
57
${filter:+WHERE $filter
58
}${order_by:+ORDER BY $order_by
59
}}")\
60
$(limit)"
61
}
62

    
63
# export func usage: export_func() { ...; mk_select_var; ... }
64
# caller usage: {query=... | table=... [cols=...] [filter=...]} export_func
65
# cmd line usage: [limit=...] caller
66
alias mk_select_var='declare query="$(mk_select)"'
67

    
68
fi # load new aliases
69
if self_being_included; then
70

    
71
mk_drop() # usage: table=... mk_drop|*sql_ANSI
72
{
73
	log++; echo_func; kw_params table; : "${table?}"; mk_table_esc
74
	echo "DROP TABLE IF EXISTS $table_esc"
75
}
76

    
77
mk_truncate() # usage: table=... mk_truncate|*sql_ANSI
78
{
79
	log++; echo_func; kw_params table; : "${table?}"; mk_table_esc
80
	echo "TRUNCATE $table_esc"
81
}
82

    
83

    
84
### MySQL
85

    
86
alias set_database=\
87
'if test "$schema"; then declare database="${database-$schema}"; fi'
88

    
89
fi # load new aliases
90
if self_being_included; then
91

    
92
# usage: mysql*() { ...; mysql_cmd "$@"; } (with alias)
93
# caller usage: [ssh_server=...] [server=...] [user=...] [password=...] mysql*()
94
function mysql_cmd() # usage: mysql*() { ...; mysql_cmd cmd "$@"; }
95
# auto-adds connection/login opts when specified
96
{
97
	echo_func
98
	local cmd="$1"; shift
99
	local ssh_server="$(localize_url "$ssh_server")"
100
	local server="$(localize_url "$server")"
101
	if test "$ssh_server"; then
102
		local ssh_dest="${ssh_dest-${ssh_user:+$ssh_user@}$ssh_server}"
103
	fi
104
	
105
	local var=ssh_dest; local_inv
106
	command "time" ${ssh_dest:+ssh "$ssh_dest" }"$cmd" \
107
${server:+ --host="$server" }${user:+--user="$user" } --password\
108
${password+="$password"} --quick "$@" # --quick: don't buffer entire result
109
}
110
alias mysql_cmd='mysql_cmd "$FUNCNAME"'
111

    
112
fi # load new aliases
113
if self_being_included; then
114

    
115
function mysql() # usage: [database=...] [data_only=1] [log_queries=] mysql ...
116
{
117
	echo_func; kw_params output_data data_only query_verbosity
118
	if test "$data_only"; then local output_data="${output_data-1}"; fi
119
	local log_queries="${log_queries-1}"
120
	set_database
121
	
122
	set -- ${database:+--database="$database" }--local-infile=1 \
123
--${data_only:+skip-}column-names "$@"
124
	if test "$output_data"; then echo_stdin|mysql_cmd --batch "$@"
125
	else cmd_log_fd=1 mysql_cmd ${log_queries:+--verbose --verbose --verbose }\
126
"$@" # --verbose*3: echo runtimes
127
	fi
128
}
129

    
130
mysql_ANSI()
131
{
132
	echo_func
133
	(echo "SET sql_mode = 'ANSI';"; cat)|mysql "$@"
134
}
135

    
136
# always use ANSI mode, to support "" identifiers. `` are *still* supported in
137
# this mode, so it also works with SHOW CREATE TABLE output and dumpfiles.
138
alias mysql='mysql_ANSI'
139

    
140
fi # load new aliases
141
if self_being_included; then
142

    
143
mysql_root() { echo_func; use_root; mysql "$@"; }
144

    
145
mysql_truncate() { echo_func; mk_truncate|mysql; }
146

    
147
mysql_import() # usage: table=... [cols=...] [append=1] mysql_import <file
148
# without append=1, first ensures the table is empty
149
{
150
	echo_func
151
	mk_table_esc
152
	local mysql_load_data_format="${mysql_load_data_format-\
153
FIELDS TERMINATED BY ','
154
OPTIONALLY ENCLOSED BY '\"'
155
}"
156
	
157
	if test ! "$append"; then mysql_truncate; fi
158
	mysql_load_data_format="${mysql_load_data_format%
159
}"
160
	ssh2local # ssh does not tunnel nonstandard fds
161
	mysql "$@" 40<&0 <<EOF
162
LOAD DATA LOCAL INFILE '/dev/fd/40'
163
${append:+IGNORE }INTO TABLE $table_esc
164
$mysql_load_data_format
165
IGNORE 1 LINES
166
EOF
167
}
168

    
169
mysql_export() # does not support CSV
170
# caller usage: [database=...] {query=... | table=... [cols=...] [filter=...]} \
171
# mysql_export
172
# cmd line usage: [limit=...] caller
173
{
174
	echo_func
175
	mk_select_var
176
	
177
	output_data=1 mysql "$@" <<<"$query"
178
}
179

    
180
mysql_export_outfile() # supports CSV, but requires the FILE privilege
181
{
182
	echo_func
183
	: "${file:?}"
184
	mk_select_var
185
	local mysql_load_data_format="${mysql_load_data_format-\
186
FIELDS TERMINATED BY ','
187
OPTIONALLY ENCLOSED BY '\"'
188
}"
189
	
190
	local head="${query%%FROM*}" # includes trailing newline
191
	head="${head%
192
}"
193
	local tail="${query#$head}"
194
	mysql_load_data_format="${mysql_load_data_format%
195
}"
196
	mysql "$@" <<EOF
197
$head
198
INTO OUTFILE '$file'
199
$mysql_load_data_format
200
$tail
201
EOF
202
}
203

    
204
mysqldump() # usage: [schema=1 | data=1] [create_db=1] mysqldump db [table...]
205
{
206
	echo_func
207
	echo_vars schema data create_db
208
	
209
	local var=create_db; local_inv
210
	mysql_cmd ${database:+--databases "$database" ${no_create_db:+--tables }} \
211
--lock-tables=false --set-charset \
212
${postgres_compat:+${data:+--compatible=postgresql }--add-locks=false }\
213
${schema:+--no-data }${data:+--no-create-info }"$@"
214
}
215

    
216
mysqldump_diffable()
217
{
218
	echo_func
219
	mysqldump "$@"|{ pipe_delay; echo_run sed 's/^(-- Dump completed).*$/\1/'; }
220
}
221

    
222
mysql_rm_privileged_statements()
223
{
224
	echo_func
225
	grep -vE -e '^/\*!40000 ALTER TABLE `.*` (DIS|EN)ABLE KEYS \*/;$' \
226
-e '^(UN)?LOCK TABLES( `.*` WRITE)?;$'
227
}
228

    
229
## permissions
230

    
231
mysql_seal_table() # usage: table=... user=... mysql_seal_table
232
# prevents further modifications to a table by a user
233
{
234
	echo_func; kw_params table user; : "${table:?}" "${user:?}"; mk_table_esc
235
	
236
	benign_error=1 mysql_root <<<"REVOKE ALL PRIVILEGES ON $table FROM '$user'@'%'" || true
237
	benign_error=1 mysql_root <<<"REVOKE GRANT OPTION   ON $table FROM '$user'@'%'" || true
238
}
239

    
240

    
241
### PostgreSQL
242

    
243
alias use_pg='declare prefix=pg_; import_vars; unset prefix'
244

    
245
fi # load new aliases
246
if self_being_included; then
247

    
248
# usage: pg_*() { ...; pg_cmd "$@"; } (uses alias)
249
function pg_cmd() # usage for fn: pg_*() { ...; pg_cmd cmd "$@"; }
250
# auto-adds connection/login opts when specified
251
{
252
	echo_func
253
	
254
	use_pg
255
	log- 2
256
	if test "$server";   then local PGHOST="$server";       export PGHOST; fi
257
	if test "$user";     then local PGUSER="$user";         export PGUSER; fi
258
	if test "$password"; then local PGPASSWORD="$password"; export PGPASSWORD;fi
259
	if test "$database"; then local PGDATABASE="$database"; export PGDATABASE;fi
260
	log+ 2
261
	
262
	command "time" "$@"
263
}
264
alias pg_cmd='"pg_cmd" "${FUNCNAME%%__*}"'
265

    
266
pg_as_root() # usage: pg_as_root {psql|pg_*} ... # only works inside runscript
267
{ echo_func; : "${wrap_fn?}"; echo_run sudo -E -u postgres "$wrap_fn" "$@"; }
268

    
269
fi # load new aliases
270
if self_being_included; then
271

    
272
psql() # usage: [stdin=copy_pstdin_file] psql <<<"cmds, e.g. \copy from pstdin"
273
# SUDO_USER: when set, avoids outputting to /dev/fd/#, because this causes a
274
# "Permission denied" error when running as sudo on Linux
275
{
276
	echo_func; kw_params stdin
277
	local verbose_="$(! isset SUDO_USER; exit2bool)"; echo_vars verbose_
278
	
279
	if test "$verbose_" && can_log; then set -- --echo-all --echo-hidden "$@";fi
280
	local redirs=("${redirs[@]}" '40<&0' "0<${stdin:-&20}" '41>&1')
281
	(
282
		# hide stack traces/DETAIL sections of error messages at verbosity <2
283
		if ! clog++ can_log; then echo '\set VERBOSITY terse'; fi
284
		if test "$verbose_" && can_log; then cat <<'EOF'
285
\timing on
286
SET client_min_messages = NOTICE;
287
EOF
288
		fi
289
		cat
290
	)|cmd_log_fd=${verbose_:+1} pg_cmd --file /dev/fd/40 \
291
${verbose_:+--output /dev/fd/41 }--set ON_ERROR_STOP=1 --quiet "$@"
292
		# --output is for query *results*, not echoed statements
293
}
294

    
295
pg_export() # usage: mk_select_opts... [pg_copy_format=CSV...] pg_export >out
296
{
297
	echo_func
298
	mk_select_var
299
	local pg_copy_format="${pg_copy_format-CSV HEADER}"
300
	
301
	psql "$@" <<<"COPY ($query) TO STDOUT $pg_copy_format;"
302
}
303

    
304
pg_header()
305
{
306
	echo_func
307
	local pg_copy_format="CSV HEADER" limit=0
308
	pg_export "$@"|echo_stdout
309
}
310

    
311
pg_export_table_no_header()
312
{
313
	echo_func
314
	local pg_copy_format="CSV"
315
	pg_export "$@"
316
}
317

    
318
pg_export_table_to_dir()
319
{
320
	echo_func
321
	local table="$1"; shift; mk_table_esc
322
	stdout="$exports_dir/$table.csv" to_file pg_export "$@"
323
}
324

    
325
pg_export_table_to_dir_no_header()
326
{
327
	echo_func
328
	local table="$1"; shift; mk_table_esc
329
	stdout="$exports_dir/$table.no_header.cols=$(pg_header).csv" to_file \
330
pg_export_table_no_header "$@"
331
}
332

    
333
pg_dump() # usage: [schema=...] [struct=1 | data=1] [compress=1] [owners=1] \
334
# [create_schema=] pg_dump [opts...] >out
335
{
336
	echo_func; kw_params struct data plain owners create_schema
337
	local create_schema="${create_schema-1}"
338
	
339
	# subset
340
	if test "$schema"; then set -- --schema="\"$schema\"" "$@"; fi
341
	
342
	# format
343
	if test "$compress"; then set -- "$@" --format=c --compress=9
344
	                     else set -- "$@" --format=p --inserts # plain
345
	fi
346
	if test "$struct"  ; then set -- "$@" --schema-only; fi
347
	if test "$data"    ; then set -- "$@" --data-only  ; fi
348
	if test ! "$owners"; then set -- "$@" --no-owner   ; fi
349
	
350
	(
351
		# filter output
352
		if test ! "$create_schema"; then
353
			fd=1 redirs= filter_fd sed 's/^CREATE SCHEMA/--&/'
354
		fi
355
		
356
		pg_cmd "$@"
357
	)
358
}
359

    
360
pg_schema_exists() # usage: schema=... pg_schema_exists
361
{
362
	echo_func; : "${schema:?}"; mk_schema_esc
363
	pattern='cannot create temporary relation in non-temporary schema' \
364
stderr_matches try psql <<<"CREATE TEMP TABLE $schema_esc.t ()"
365
}
366

    
367
pg_require_schema() # usage: schema=... pg_require_schema
368
{
369
	echo_func; : "${schema:?}"
370
	clog++ pg_schema_exists || die "schema $schema does not exist"
371
}
372

    
373
pg_table_exists() # usage: [schema=...] table=... pg_table_exists
374
{
375
	echo_func; : "${table:?}"; mk_table_esc
376
	psql <<<"SELECT NULL FROM $table_esc LIMIT 0"
377
}
378

    
379
fi
(4-4/11)