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 use_local='declare prefix=local_; import_vars'
8
alias use_remote='declare prefix=remote_; import_vars'
9
alias use_local_remote='{ use_local; use_remote; }'
10
	# *must* be run inside a function
11

    
12
quote='"'
13

    
14
esc_name() { echo "$quote${1//$quote/$quote$quote}$quote"; }
15

    
16
mk_esc_name() { set_var "$1"_esc "$(esc_name "${!1}")"; }
17

    
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
alias mk_select='mk_table_esc
22
declare query="${query:-SELECT * FROM $table_esc}${limit:+ LIMIT $limit}"'
23

    
24
fi # load new aliases
25
if self_being_included; then
26

    
27
log_sql() { test "$verbosity" -ge 2; }
28

    
29

    
30
### MySQL
31

    
32
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
# usage: mysql*() { ...; mysql_cmd "$@"; } (with alias)
39
function mysql_cmd() # usage: mysql*() { ...; mysql_cmd cmd "$@"; }
40
# auto-adds connection/login opts when specified
41
{
42
	echo_func
43
	local cmd="$1"; shift
44
	local ssh_server="$(localize_url "$ssh_server")"
45
	local server="$(localize_url "$server")"
46
	if test "$ssh_server"; then
47
		local ssh_dest="${ssh_dest-${ssh_user:+$ssh_user@}$ssh_server}"
48
	fi
49
	
50
	local var=ssh_dest; local_inv
51
	command ${ssh_dest:+ssh "$ssh_dest" }"$cmd" \
52
${server:+ --host="$server" }${user:+--user="$user" } --password\
53
${password+="$password"} "$@"
54
}
55
alias mysql_cmd='mysql_cmd "$FUNCNAME"'
56

    
57
fi # load new aliases
58
if self_being_included; then
59

    
60
mysql() # usage: [output_data=1] mysql ...
61
{
62
	echo_func; kw_params output_data
63
	set_database
64
	
65
	set -- ${database:+--database="$database" }--column-names "$@"
66
	if test "$output_data"; then echo_stdin|mysql_cmd --batch "$@"
67
	else cmd_log_fd=1 mysql_cmd --verbose "$@"
68
	fi
69
}
70

    
71
mysql_ANSI()
72
{
73
	echo_func
74
	(echo "SET sql_mode = 'ANSI';"; cat)|mysql "$@"
75
}
76

    
77
mysql_export() # does not support CSV
78
{
79
	echo_func
80
	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
	: "${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
	head="${head%
97
}"
98
	local tail="${query#$head}"
99
	mysql_load_data_format="${mysql_load_data_format%
100
}"
101
	mysql_ANSI "$@" <<EOF
102
$head
103
INTO OUTFILE '$file'
104
$mysql_load_data_format
105
$tail
106
EOF
107
}
108

    
109
mysqldump() # usage: [schema=1 | data=1] mysqldump db [table...]
110
{
111
	echo_func; kw_params schema data
112
	
113
	mysql_cmd ${database:+--databases "$database" --tables } \
114
--quick --lock-tables=false --set-charset \
115
${postgres_compat:+--compatible=postgresql --add-locks=false }\
116
${schema:+--no-data }${data:+--no-create-info }"$@"
117
}
118

    
119
mysqldump_diffable()
120
{
121
	echo_func
122
	mysqldump "$@"|{ pipe_delay; sed 's/^(-- Dump completed).*$/\1/'; }
123
}
124

    
125

    
126
### PostgreSQL
127

    
128
pg_export()
129
{
130
	echo_func
131
	mk_select
132
	local pg_copy_format="${pg_copy_format-CSV HEADER}"
133
	
134
	psql "$@" <<<"COPY ($query) TO STDOUT $pg_copy_format;"
135
}
136

    
137
pg_header()
138
{
139
	echo_func
140
	local pg_copy_format="CSV HEADER" limit=0
141
	pg_export "$@"|echo_stdout
142
}
143

    
144
pg_export_table_no_header()
145
{
146
	echo_func
147
	local pg_copy_format="CSV"
148
	pg_export "$@"
149
}
150

    
151
pg_export_table_to_dir_no_header()
152
{
153
	echo_func
154
	local table="$1"; shift; mk_table_esc
155
	local cols="$(pg_header)"
156
	stdout="$exports_dir/$table.no_header.cols=$cols.csv" to_file \
157
pg_export_table_no_header "$@"
158
}
159

    
160
fi
(2-2/5)