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
# auto-adds connection/login opts when specified
39
# usage: mysql*() { ...; mysql_cmd "$@"; } (with alias)
40
function mysql_cmd() # usage: mysql*() { ...; mysql_cmd cmd "$@"; }
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
63
	set_database
64
	
65
	set -- ${database:+--database="$database" }--column-names "$@"
66
	if test "$output_data"; then echo_stdin|mysql_cmd --batch "$@"
67
	else limit_stdout=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
112
	mysql_cmd ${database:+--databases "$database" --tables } \
113
--quick --lock-tables=false --set-charset \
114
${postgres_compat:+--compatible=postgresql --add-locks=false }\
115
${schema:+--no-data }${data:+--no-create-info }"$@"
116
}
117

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

    
124

    
125
### PostgreSQL
126

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

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

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

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

    
159
fi
(2-2/5)