#### databases

# using prefixed connection vars

alias use_local='declare prefix=local_; import_vars'

alias use_remote='declare prefix=remote_; import_vars'

alias use_local_remote='use_local; use_remote'

quote='"'

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

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

alias mk_schema_esc='declare schema_esc; mk_esc_name schema'

alias mk_table_esc='declare table_esc; mk_esc_name table'

fi # load new aliases

if self_being_included; then

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

### MySQL

# auto-adds connection/login opts when specified

mysql_cmd () # usage: mysql* () { ...; mysql_cmd "$@"; }

{

	echo_func

	local ssh_server="$(localize_url "$ssh_server")"

	local server="$(localize_url "$server")"

	if test -n "$ssh_server"; then

		local ssh_dest="${ssh_dest-${ssh_user:+$ssh_user@}$ssh_server}"

	fi

	if test -n "$schema"; then local database="${database-$schema}"; fi

	local var=ssh_dest; local_inv

	extern ${ssh_dest:+ssh "$ssh_dest" }"${FUNCNAME[1]}" \

${server:+ --host="$server" }${user:+--user="$user" } --password\

${password+="$password"} ${database:+--databases "$database" --tables } "$@"

}

mysql () { echo_func; mysql_cmd --verbose "$@"; }

mysql_ANSI ()

{

	echo_func

	(echo "SET sql_mode = 'ANSI';"; cat)|mysql "$@"

}

mysqldump () # usage: [schema=1 | data=1] mysqldump db [table...]

{

	echo_func

	mysql_cmd --quick --lock-tables=false --set-charset \

${postgres_compat:+--compatible=postgresql --add-locks=false }\

${schema:+--no-data }${data:+--no-create-info }"$@"

}

mysqldump_diffable ()

{

	echo_func

	mysqldump "$@"|sed 's/^(-- Dump completed).*$/\1/'

}

### PostgreSQL

pg_copy_to ()

{

	echo_func

	if test -z "$source"; then

		: "${table:?}"; mk_table_esc

		if test -z "$limit"; then local source="$table_esc"

		else local source="(SELECT * FROM $table_esc LIMIT $limit)"

		fi

	fi

	local pg_copy_format="${pg_copy_format-CSV HEADER}"

	psql "$@" <<<"COPY $source TO STDOUT $pg_copy_format;"

}

pg_header ()

{

	echo_func

	local pg_copy_format="CSV HEADER" limit=0

	pg_copy_to "$@"|echo_stdin

}

pg_export_table_no_header ()

{

	echo_func

	local pg_copy_format="CSV"

	pg_copy_to "$@"

}

pg_export_table_to_dir_no_header ()

{

	echo_func

	local table="$1"; shift; mk_table_esc

	local cols="$(pg_header)"

	pg_export_table_no_header "$@" >"$exports_dir/$table.no_header.cols=$cols.csv"

}