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
alias use_local='declare prefix=local_; import_vars'
8
alias use_remote='declare prefix=remote_; import_vars'
9 9167 aaronmk
alias use_local_remote='{ use_local; use_remote; }'
10
	# *must* be run inside a function
11 9014 aaronmk
12
quote='"'
13
14 9074 aaronmk
esc_name() { echo "$quote${1//$quote/$quote$quote}$quote"; }
15 9014 aaronmk
16 9074 aaronmk
mk_esc_name() { set_var "$1"_esc "$(esc_name "${!1}")"; }
17 9014 aaronmk
18
alias mk_schema_esc='declare schema_esc; mk_esc_name schema'
19
alias mk_table_esc='declare table_esc; mk_esc_name table'
20
21 9088 aaronmk
alias mk_select='mk_table_esc
22
declare query="${query:-SELECT * FROM $table_esc}${limit:+ LIMIT $limit}"'
23
24 9014 aaronmk
fi # load new aliases
25
if self_being_included; then
26
27 9074 aaronmk
log_sql() { test "$verbosity" -ge 2; }
28 9014 aaronmk
29 9017 aaronmk
30 9014 aaronmk
### MySQL
31
32 9094 aaronmk
alias set_database=\
33
'if test "$schema"; then declare database="${database-$schema}"; fi'
34
35
fi # load new aliases
36
if self_being_included; then
37
38 9099 aaronmk
# usage: mysql*() { ...; mysql_cmd "$@"; } (with alias)
39
function mysql_cmd() # usage: mysql*() { ...; mysql_cmd cmd "$@"; }
40 9271 aaronmk
# auto-adds connection/login opts when specified
41 9014 aaronmk
{
42
	echo_func
43 9099 aaronmk
	local cmd="$1"; shift
44 9014 aaronmk
	local ssh_server="$(localize_url "$ssh_server")"
45
	local server="$(localize_url "$server")"
46 9050 aaronmk
	if test "$ssh_server"; then
47 9014 aaronmk
		local ssh_dest="${ssh_dest-${ssh_user:+$ssh_user@}$ssh_server}"
48
	fi
49
50
	local var=ssh_dest; local_inv
51 9099 aaronmk
	command ${ssh_dest:+ssh "$ssh_dest" }"$cmd" \
52 9014 aaronmk
${server:+ --host="$server" }${user:+--user="$user" } --password\
53 9095 aaronmk
${password+="$password"} "$@"
54 9014 aaronmk
}
55 9099 aaronmk
alias mysql_cmd='mysql_cmd "$FUNCNAME"'
56 9014 aaronmk
57 9099 aaronmk
fi # load new aliases
58
if self_being_included; then
59
60 9101 aaronmk
mysql() # usage: [output_data=1] mysql ...
61 9095 aaronmk
{
62 9235 aaronmk
	echo_func; kw_params output_data
63 9095 aaronmk
	set_database
64 9101 aaronmk
65 9151 aaronmk
	set -- ${database:+--database="$database" }--column-names "$@"
66 9150 aaronmk
	if test "$output_data"; then echo_stdin|mysql_cmd --batch "$@"
67 9214 aaronmk
	else cmd_log_fd=1 mysql_cmd --verbose "$@"
68 9101 aaronmk
	fi
69 9095 aaronmk
}
70 9014 aaronmk
71 9074 aaronmk
mysql_ANSI()
72 9014 aaronmk
{
73
	echo_func
74
	(echo "SET sql_mode = 'ANSI';"; cat)|mysql "$@"
75
}
76
77 9152 aaronmk
mysql_export() # does not support CSV
78 9102 aaronmk
{
79
	echo_func
80 9152 aaronmk
	mk_select
81
82
	output_data=1 mysql_ANSI "$@" <<<"$query"
83
}
84
85
mysql_export_outfile() # supports CSV, but requires the FILE privilege
86
{
87
	echo_func
88 9102 aaronmk
	: "${file:?}"
89
	mk_select
90
	local mysql_load_data_format="${mysql_load_data_format-\
91
FIELDS TERMINATED BY ','
92
OPTIONALLY ENCLOSED BY '\"'
93
}"
94
95
	local head="${query%%FROM*}" # includes trailing newline
96 9105 aaronmk
	head="${head%
97
}"
98 9102 aaronmk
	local tail="${query#$head}"
99 9106 aaronmk
	mysql_load_data_format="${mysql_load_data_format%
100
}"
101 9102 aaronmk
	mysql_ANSI "$@" <<EOF
102 9105 aaronmk
$head
103
INTO OUTFILE '$file'
104 9106 aaronmk
$mysql_load_data_format
105
$tail
106 9102 aaronmk
EOF
107
}
108
109 9074 aaronmk
mysqldump() # usage: [schema=1 | data=1] mysqldump db [table...]
110 9014 aaronmk
{
111 9235 aaronmk
	echo_func; kw_params schema data
112 9231 aaronmk
113 9095 aaronmk
	mysql_cmd ${database:+--databases "$database" --tables } \
114
--quick --lock-tables=false --set-charset \
115 9014 aaronmk
${postgres_compat:+--compatible=postgresql --add-locks=false }\
116
${schema:+--no-data }${data:+--no-create-info }"$@"
117
}
118
119 9074 aaronmk
mysqldump_diffable()
120 9014 aaronmk
{
121
	echo_func
122 9066 aaronmk
	mysqldump "$@"|{ pipe_delay; sed 's/^(-- Dump completed).*$/\1/'; }
123 9014 aaronmk
}
124
125 9017 aaronmk
126 9014 aaronmk
### PostgreSQL
127
128 9083 aaronmk
pg_export()
129 9014 aaronmk
{
130
	echo_func
131 9088 aaronmk
	mk_select
132 9014 aaronmk
	local pg_copy_format="${pg_copy_format-CSV HEADER}"
133
134 9084 aaronmk
	psql "$@" <<<"COPY ($query) TO STDOUT $pg_copy_format;"
135 9014 aaronmk
}
136
137 9074 aaronmk
pg_header()
138 9014 aaronmk
{
139
	echo_func
140
	local pg_copy_format="CSV HEADER" limit=0
141 9083 aaronmk
	pg_export "$@"|echo_stdout
142 9014 aaronmk
}
143
144 9074 aaronmk
pg_export_table_no_header()
145 9014 aaronmk
{
146
	echo_func
147
	local pg_copy_format="CSV"
148 9083 aaronmk
	pg_export "$@"
149 9014 aaronmk
}
150
151 9074 aaronmk
pg_export_table_to_dir_no_header()
152 9014 aaronmk
{
153
	echo_func
154
	local table="$1"; shift; mk_table_esc
155
	local cols="$(pg_header)"
156 9042 aaronmk
	stdout="$exports_dir/$table.no_header.cols=$cols.csv" to_file \
157
pg_export_table_no_header "$@"
158 9014 aaronmk
}
159
160
fi