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
limit() # usage: query... $([prefix=$'| |\n'] limit)
33
{
34
	echo_func; kw_params prefix; local prefix="${prefix-
35
}"
36
	echo -n "${limit:+${prefix}LIMIT $limit}"
37
}
38

    
39
mk_select() # usage: {query=... | table=... [cols=...] [filter=...]} mk_select
40
{
41
	echo_func; kw_params query table cols filter; mk_table_esc
42
	echo "$(rtrim "${query:-SELECT ${cols:-*} ${cols:+
43
}FROM $table_esc
44
${filter:+WHERE $filter
45
}}")\
46
$(limit)"
47
}
48

    
49
# export func usage: export_func() { ...; mk_select_var; ... }
50
# caller usage: {query=... | table=... [cols=...] [filter=...]} export_func
51
# cmd line usage: [limit=...] caller
52
alias mk_select_var='declare query="$(mk_select)"'
53

    
54
fi # load new aliases
55
if self_being_included; then
56

    
57
mk_drop() # usage: table=... mk_drop|*sql_ANSI
58
{
59
	log++; echo_func; kw_params table; : "${table?}"; mk_table_esc
60
	echo "DROP TABLE IF EXISTS $table_esc"
61
}
62

    
63
mk_truncate() # usage: table=... mk_truncate|*sql_ANSI
64
{
65
	log++; echo_func; kw_params table; : "${table?}"; mk_table_esc
66
	echo "TRUNCATE $table_esc"
67
}
68

    
69

    
70
### MySQL
71

    
72
alias set_database=\
73
'if test "$schema"; then declare database="${database-$schema}"; fi'
74

    
75
fi # load new aliases
76
if self_being_included; then
77

    
78
# usage: mysql*() { ...; mysql_cmd "$@"; } (with alias)
79
function mysql_cmd() # usage: mysql*() { ...; mysql_cmd cmd "$@"; }
80
# auto-adds connection/login opts when specified
81
{
82
	echo_func
83
	local cmd="$1"; shift
84
	local ssh_server="$(localize_url "$ssh_server")"
85
	local server="$(localize_url "$server")"
86
	if test "$ssh_server"; then
87
		local ssh_dest="${ssh_dest-${ssh_user:+$ssh_user@}$ssh_server}"
88
	fi
89
	
90
	local var=ssh_dest; local_inv
91
	command ${ssh_dest:+ssh "$ssh_dest" }"$cmd" \
92
${server:+ --host="$server" }${user:+--user="$user" } --password\
93
${password+="$password"} "$@"
94
}
95
alias mysql_cmd='mysql_cmd "$FUNCNAME"'
96

    
97
fi # load new aliases
98
if self_being_included; then
99

    
100
mysql() # usage: [output_data=1] [data_only=1] [log_queries=] mysql ...
101
{
102
	echo_func; kw_params output_data data_only query_verbosity
103
	if test "$data_only"; then local output_data="${output_data-1}"; fi
104
	local log_queries="${log_queries-1}"
105
	set_database
106
	
107
	set -- ${database:+--database="$database" }--local-infile=1 \
108
--${data_only:+skip-}column-names "$@"
109
	if test "$output_data"; then echo_stdin|mysql_cmd --batch "$@"
110
	else cmd_log_fd=1 mysql_cmd ${log_queries:+--verbose --verbose --verbose }\
111
"$@" # --verbose*3: echo runtimes
112
	fi
113
}
114

    
115
mysql_root() { echo_func; use_root; mysql "$@"; }
116

    
117
mysql_ANSI()
118
{
119
	echo_func
120
	(echo "SET sql_mode = 'ANSI';"; cat)|mysql "$@"
121
}
122

    
123
mysql_truncate() { echo_func; mk_truncate|mysql_ANSI; }
124

    
125
mysql_import() # usage: table=... [cols=...] [append=1] mysql_import <file
126
# without append=1, first ensures the table is empty
127
{
128
	echo_func
129
	mk_table_esc
130
	local mysql_load_data_format="${mysql_load_data_format-\
131
FIELDS TERMINATED BY ','
132
OPTIONALLY ENCLOSED BY '\"'
133
}"
134
	
135
	if test ! "$append"; then mysql_truncate; fi
136
	mysql_load_data_format="${mysql_load_data_format%
137
}"
138
	ssh2local # ssh does not tunnel nonstandard fds
139
	mysql_ANSI "$@" 40<&0 <<EOF
140
LOAD DATA LOCAL INFILE '/dev/fd/40'
141
${append:+IGNORE }INTO TABLE $table_esc
142
$mysql_load_data_format
143
IGNORE 1 LINES
144
EOF
145
}
146

    
147
mysql_export() # does not support CSV
148
# caller usage: {query=... | table=... [cols=...] [filter=...]} mysql_export
149
# cmd line usage: [limit=...] caller
150
{
151
	echo_func
152
	mk_select_var
153
	
154
	output_data=1 mysql_ANSI "$@" <<<"$query"
155
}
156

    
157
mysql_export_outfile() # supports CSV, but requires the FILE privilege
158
{
159
	echo_func
160
	: "${file:?}"
161
	mk_select_var
162
	local mysql_load_data_format="${mysql_load_data_format-\
163
FIELDS TERMINATED BY ','
164
OPTIONALLY ENCLOSED BY '\"'
165
}"
166
	
167
	local head="${query%%FROM*}" # includes trailing newline
168
	head="${head%
169
}"
170
	local tail="${query#$head}"
171
	mysql_load_data_format="${mysql_load_data_format%
172
}"
173
	mysql_ANSI "$@" <<EOF
174
$head
175
INTO OUTFILE '$file'
176
$mysql_load_data_format
177
$tail
178
EOF
179
}
180

    
181
mysqldump() # usage: [schema=1 | data=1] mysqldump db [table...]
182
{
183
	echo_func; kw_params schema data
184
	
185
	mysql_cmd ${database:+--databases "$database" --tables } \
186
--quick --lock-tables=false --set-charset \
187
${postgres_compat:+--compatible=postgresql --add-locks=false }\
188
${schema:+--no-data }${data:+--no-create-info }"$@"
189
}
190

    
191
mysqldump_diffable()
192
{
193
	echo_func
194
	mysqldump "$@"|{ pipe_delay; echo_run sed 's/^(-- Dump completed).*$/\1/'; }
195
}
196

    
197
mysql_rm_privileged_statements()
198
{
199
	echo_func
200
	grep -vE -e '^/\*!40000 ALTER TABLE `.*` (DIS|EN)ABLE KEYS \*/;$' \
201
-e '^(UN)?LOCK TABLES( `.*` WRITE)?;$'
202
}
203

    
204
## permissions
205

    
206
mysql_seal_table() # usage: table=... user=... mysql_seal_table
207
# prevents further modifications to a table by a user
208
{
209
	echo_func; kw_params table user; : "${table:?}" "${user:?}"; mk_table_esc
210
	
211
	benign_error=1 mysql_root <<<"REVOKE ALL PRIVILEGES ON $table FROM '$user'@'%'" || true
212
	benign_error=1 mysql_root <<<"REVOKE GRANT OPTION   ON $table FROM '$user'@'%'" || true
213
}
214

    
215

    
216
### PostgreSQL
217

    
218
alias use_pg='declare prefix=pg_; import_vars; unset prefix'
219

    
220
fi # load new aliases
221
if self_being_included; then
222

    
223
psql() # usage: [stdin=copy_pstdin_file] psql <<<"cmds, e.g. \copy from pstdin"
224
{
225
	echo_func; kw_params stdin
226
	
227
	use_pg
228
	log+ -2
229
	local PGHOST="$server";       export PGHOST
230
	local PGUSER="$user";         export PGUSER
231
	local PGPASSWORD="$password"; export PGPASSWORD
232
	local PGDATABASE="$database"; export PGDATABASE
233
	log+ 2
234
	
235
	if can_log; then set -- --echo-all --echo-hidden "$@"; fi
236
	local redirs=("${redirs[@]}" '40<&0' "0<${stdin:-&20}" '41>&1')
237
	(
238
		if can_log; then cat <<'EOF'
239
\timing on
240
SET client_min_messages = NOTICE;
241
EOF
242
		fi
243
		cat
244
	)|cmd_log_fd=1 command psql --file /dev/fd/40 --output /dev/fd/41 \
245
--set ON_ERROR_STOP=1 --quiet "$@"
246
		# --output is for query *results*, not echoed statements
247
}
248

    
249
pg_export()
250
{
251
	echo_func
252
	mk_select_var
253
	local pg_copy_format="${pg_copy_format-CSV HEADER}"
254
	
255
	psql "$@" <<<"COPY ($query) TO STDOUT $pg_copy_format;"
256
}
257

    
258
pg_header()
259
{
260
	echo_func
261
	local pg_copy_format="CSV HEADER" limit=0
262
	pg_export "$@"|echo_stdout
263
}
264

    
265
pg_export_table_no_header()
266
{
267
	echo_func
268
	local pg_copy_format="CSV"
269
	pg_export "$@"
270
}
271

    
272
pg_export_table_to_dir_no_header()
273
{
274
	echo_func
275
	local table="$1"; shift; mk_table_esc
276
	stdout="$exports_dir/$table.no_header.cols=$(pg_header).csv" to_file \
277
pg_export_table_no_header "$@"
278
}
279

    
280
fi
(3-3/8)