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