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=...]} mk_select
49
{
50
	echo_func; kw_params query table cols filter; mk_table_esc
51
	if is_array cols; then cols="$(cols2list "${cols[@]}")"; fi
52
	echo "$(rtrim "${query:-SELECT ${cols:-*} ${cols:+
53
}FROM $table_esc
54
${filter:+WHERE $filter
55
}}")\
56
$(limit)"
57
}
58

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

    
64
fi # load new aliases
65
if self_being_included; then
66

    
67
mk_drop() # usage: table=... mk_drop|*sql_ANSI
68
{
69
	log++; echo_func; kw_params table; : "${table?}"; mk_table_esc
70
	echo "DROP TABLE IF EXISTS $table_esc"
71
}
72

    
73
mk_truncate() # usage: table=... mk_truncate|*sql_ANSI
74
{
75
	log++; echo_func; kw_params table; : "${table?}"; mk_table_esc
76
	echo "TRUNCATE $table_esc"
77
}
78

    
79

    
80
### MySQL
81

    
82
alias set_database=\
83
'if test "$schema"; then declare database="${database-$schema}"; fi'
84

    
85
fi # load new aliases
86
if self_being_included; then
87

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

    
107
fi # load new aliases
108
if self_being_included; then
109

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

    
125
mysql_ANSI()
126
{
127
	echo_func
128
	(echo "SET sql_mode = 'ANSI';"; cat)|mysql "$@"
129
}
130

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

    
135
fi # load new aliases
136
if self_being_included; then
137

    
138
mysql_root() { echo_func; use_root; mysql "$@"; }
139

    
140
mysql_truncate() { echo_func; mk_truncate|mysql; }
141

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

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

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

    
198
mysqldump() # usage: [schema=1 | data=1] mysqldump db [table...]
199
{
200
	echo_func; kw_params schema data
201
	
202
	mysql_cmd ${database:+--databases "$database" --tables } \
203
--lock-tables=false --set-charset \
204
${postgres_compat:+${data:+--compatible=postgresql }--add-locks=false }\
205
${schema:+--no-data }${data:+--no-create-info }"$@"
206
}
207

    
208
mysqldump_diffable()
209
{
210
	echo_func
211
	mysqldump "$@"|{ pipe_delay; echo_run sed 's/^(-- Dump completed).*$/\1/'; }
212
}
213

    
214
mysql_rm_privileged_statements()
215
{
216
	echo_func
217
	grep -vE -e '^/\*!40000 ALTER TABLE `.*` (DIS|EN)ABLE KEYS \*/;$' \
218
-e '^(UN)?LOCK TABLES( `.*` WRITE)?;$'
219
}
220

    
221
## permissions
222

    
223
mysql_seal_table() # usage: table=... user=... mysql_seal_table
224
# prevents further modifications to a table by a user
225
{
226
	echo_func; kw_params table user; : "${table:?}" "${user:?}"; mk_table_esc
227
	
228
	benign_error=1 mysql_root <<<"REVOKE ALL PRIVILEGES ON $table FROM '$user'@'%'" || true
229
	benign_error=1 mysql_root <<<"REVOKE GRANT OPTION   ON $table FROM '$user'@'%'" || true
230
}
231

    
232

    
233
### PostgreSQL
234

    
235
alias use_pg='declare prefix=pg_; import_vars; unset prefix'
236

    
237
fi # load new aliases
238
if self_being_included; then
239

    
240
psql() # usage: [stdin=copy_pstdin_file] psql <<<"cmds, e.g. \copy from pstdin"
241
{
242
	echo_func; kw_params stdin
243
	
244
	use_pg
245
	log- 2
246
	local PGHOST="$server";       export PGHOST
247
	local PGUSER="$user";         export PGUSER
248
	local PGPASSWORD="$password"; export PGPASSWORD
249
	local PGDATABASE="$database"; export PGDATABASE
250
	log+ 2
251
	
252
	if can_log; then set -- --echo-all --echo-hidden "$@"; fi
253
	local redirs=("${redirs[@]}" '40<&0' "0<${stdin:-&20}" '41>&1')
254
	(
255
		# hide stack traces/DETAIL sections of error messages at verbosity <2
256
		if ! clog++ can_log; then echo '\set VERBOSITY terse'; fi
257
		if can_log; then cat <<'EOF'
258
\timing on
259
SET client_min_messages = NOTICE;
260
EOF
261
		fi
262
		cat
263
	)|cmd_log_fd=1 command psql --file /dev/fd/40 --output /dev/fd/41 \
264
--set ON_ERROR_STOP=1 --quiet "$@"
265
		# --output is for query *results*, not echoed statements
266
}
267

    
268
pg_export()
269
{
270
	echo_func
271
	mk_select_var
272
	local pg_copy_format="${pg_copy_format-CSV HEADER}"
273
	
274
	psql "$@" <<<"COPY ($query) TO STDOUT $pg_copy_format;"
275
}
276

    
277
pg_header()
278
{
279
	echo_func
280
	local pg_copy_format="CSV HEADER" limit=0
281
	pg_export "$@"|echo_stdout
282
}
283

    
284
pg_export_table_no_header()
285
{
286
	echo_func
287
	local pg_copy_format="CSV"
288
	pg_export "$@"
289
}
290

    
291
pg_export_table_to_dir_no_header()
292
{
293
	echo_func
294
	local table="$1"; shift; mk_table_esc
295
	stdout="$exports_dir/$table.no_header.cols=$(pg_header).csv" to_file \
296
pg_export_table_no_header "$@"
297
}
298

    
299
fi
(4-4/11)