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_local; use_remote; }'
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)"
39
{
40
	echo_func; kw_params prefix; local prefix="${prefix-
41
}"
42
	echo -n "${limit:+${prefix}LIMIT $limit}"
43
}
44

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

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

    
62
# 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
fi # load new aliases
68
if self_being_included; then
69

    
70
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
mk_truncate() # usage: table=... mk_truncate|*sql_ANSI
77
{
78
	log++; echo_func; kw_params table; : "${table?}"; mk_table_esc
79
	echo "TRUNCATE $table_esc"
80
}
81

    
82

    
83
### MySQL
84

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

    
110
fi # load new aliases
111
if self_being_included; then
112

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

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

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

    
138
fi # load new aliases
139
if self_being_included; then
140

    
141
mysql_root() { echo_func; use_root; mysql "$@"; }
142

    
143
mysql_truncate() { echo_func; mk_truncate|mysql; }
144

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

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

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

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

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

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

    
227
## permissions
228

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

    
238

    
239
### PostgreSQL
240

    
241
alias use_pg='declare prefix=pg_; import_vars; unset prefix'
242

    
243
fi # load new aliases
244
if self_being_included; then
245

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

    
264
fi # load new aliases
265
if self_being_included; then
266

    
267
psql() # usage: [stdin=copy_pstdin_file] psql <<<"cmds, e.g. \copy from pstdin"
268
{
269
	echo_func; kw_params stdin
270
	
271
	if can_log; then set -- --echo-all --echo-hidden "$@"; fi
272
	local redirs=("${redirs[@]}" '40<&0' "0<${stdin:-&20}" '41>&1')
273
	(
274
		# hide stack traces/DETAIL sections of error messages at verbosity <2
275
		if ! clog++ can_log; then echo '\set VERBOSITY terse'; fi
276
		if can_log; then cat <<'EOF'
277
\timing on
278
SET client_min_messages = NOTICE;
279
EOF
280
		fi
281
		cat
282
	)|cmd_log_fd=1 pg_cmd --file /dev/fd/40 --output /dev/fd/41 \
283
--set ON_ERROR_STOP=1 --quiet "$@"
284
		# --output is for query *results*, not echoed statements
285
}
286

    
287
pg_export() # usage: mk_select_opts... [pg_copy_format=CSV...] pg_export >out
288
{
289
	echo_func
290
	mk_select_var
291
	local pg_copy_format="${pg_copy_format-CSV HEADER}"
292
	
293
	psql "$@" <<<"COPY ($query) TO STDOUT $pg_copy_format;"
294
}
295

    
296
pg_header()
297
{
298
	echo_func
299
	local pg_copy_format="CSV HEADER" limit=0
300
	pg_export "$@"|echo_stdout
301
}
302

    
303
pg_export_table_no_header()
304
{
305
	echo_func
306
	local pg_copy_format="CSV"
307
	pg_export "$@"
308
}
309

    
310
pg_export_table_to_dir()
311
{
312
	echo_func
313
	local table="$1"; shift; mk_table_esc
314
	stdout="$exports_dir/$table.csv" to_file pg_export "$@"
315
}
316

    
317
pg_export_table_to_dir_no_header()
318
{
319
	echo_func
320
	local table="$1"; shift; mk_table_esc
321
	stdout="$exports_dir/$table.no_header.cols=$(pg_header).csv" to_file \
322
pg_export_table_no_header "$@"
323
}
324

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

    
352
fi
(4-4/11)