Annotation of /alx-src/branches/alxconf-060/functions/mysqlfunctions

# $Id$
# mysql functions for bash

mysql_command()
{
	
	local SQL_USER
	local SQL_PASS
	local SQL_HOST
	local SQL_DB
	local SQL_COMMAND

	SQL_USER="$1"
	SQL_PASS="$2"
	SQL_HOST="$3"
	SQL_DB="$4"
	SQL_COMMAND="$5"

	# fallback (SQL_OPTS not, they are optional )
	if [[ -z ${SQL_USER} ]] ||
		[[ -z ${SQL_PASS} ]] ||
		[[ -z ${SQL_HOST} ]] ||
		[[ -z ${SQL_DB} ]] ||
		[[ -z ${SQL_COMMAND} ]]
	then
		echo
		echo "Usage:"
		echo -e "      mysql_command user pass host db \"sql-command\""
		echo
		return 1
	fi

	mysql \
		--user="${SQL_USER}" \
		--password="${SQL_PASS}" \
		--host="${SQL_HOST}" \
		--database="${SQL_DB}" \
		--batch \
		--skip-column-names \
		--execute="${SQL_COMMAND}" \
		|| return 1

	return 0
}

mysql_enum_colums()
{
	local SQL_USER
	local SQL_PASS
	local SQL_HOST
	local SQL_DB
	local SQL_TABLE

	local SHOWTABLES
	local i
	local read
	local field
	local type
	local null
	local key
	local default
	local extra

	SQL_USER=$1
	SQL_PASS=$2
	SQL_HOST=$3
	SQL_DB=$4
	SQL_TABLE=$5

	# show the column names ?
	if [ -n "${6}" -a "${6}" == "show" ] 
	then
		SHOWTABLES=true
	else
		 SHOWTABLES=false
	fi

	# fallback (SQL_OPTS not, they are optional )
	if [[ -z ${SQL_USER} ]] ||
		[[ -z ${SQL_PASS} ]] ||
		[[ -z ${SQL_HOST} ]] ||
		[[ -z ${SQL_DB} ]] ||
		[[ -z ${SQL_TABLE} ]]
	then
		echo
		echo "Usage:"
		echo -e "      mysql_enum_columns user pass host db table [show]"
		echo
		return 1
	fi

	declare -i i=0
	while read field type null key default extra
	do
		i=${i}+1
		[ "${SHOWTABLES}" == true ] && echo "${field}"
	done << EOF
$(mysql_command ${SQL_USER} ${SQL_PASS} ${SQL_HOST} ${SQL_DB} "show columns from ${SQL_TABLE};")
EOF
	[ "${SHOWTABLES}" == true ] || echo "${i}"
}

beep3x()
{
	echo -en "\a"
	sleep 0.7
	echo -en "\a"
	sleep 0.7
	echo -en "\a"
}

reach_mysql_server()
{
	# set connection timeout to 20 secs
	mysql --connect_timeout=20 \
		--user="${SQL_USER}" \
		--password="${SQL_PASS}" \
		--host="${SQL_HOST}" \
		--database="${SQL_DB}" \
		--batch \
		--execute="" &> /dev/null && return 0 || \
		(
			echo -e "${COLRED}"
			echo "Cannot reach a mysql-server at '${SQL_HOST}'."
			echo "None of the current settings of this client will be touched."
			echo "Please inform your system-administrator."
			echo -e "${COLDEFAULT}"
			beep3x
			sleep 5
			return 1
		)
}

mysqldo()
{
	mysql_command \
		"${SQL_USER}" \
		"${SQL_PASS}" \
		"${SQL_HOST}" \
		"${SQL_DB}" \
		"$@" \
		&& return 0 || return 1
}

# read tables and evaluate all variables
# cmd:    evaluate_table cfg_network
# result: cfg_network_hostname=DUMMY_HOSTNAME
evaluate_table()
{
	local table="$1"
	local where_statement

	if [[ -z $2 ]]
	then
		where_statement="where serial='${ALX_SERIAL}'"
	else
		where_statement="$2"
	fi

	eval $(mysql \
		--user="${SQL_USER}" \
		--password="${SQL_PASS}" \
		--host="${SQL_HOST}" \
		--database="${SQL_DB}" \
		--xml \
		--execute="select * from ${table} ${where_statement}" \
		| xml sel -T -t -m //row/field \
			-v "concat('${table}_', @name, '=', '\"', self::field, '\"')" -n)
}

# runs a statement and evaluate the resulting columns
# cmd:    evaluate_command "select serial from cfg_serial where serial=10"
# result: serial=10
# or with a prefix given
# cmd:    evaluate_command "select serial from cfg_serial where serial=10" "cfg_serial"
# result: cfg_serial_serial=10
evaluate_statement()
{
	local statement="$1"
	local prefix="$2"

	if [[ ! -z ${prefix} ]]
	then
		prefix="${prefix}_"
	fi

	eval $(mysql \
		--user="${SQL_USER}" \
		--password="${SQL_PASS}" \
		--host="${SQL_HOST}" \
		--database="${SQL_DB}" \
		--xml \
		--execute="${statement}" \
		| xml sel -T -t -m //row/field \
			-v "concat('${prefix}', @name, '=', '\"', self::field, '\"')" -n)
}
1	niro	2003	# $Id$
2			# mysql functions for bash
3	niro	219
4	niro	344	mysql_command()
5			{
6	niro	219
7			local SQL_USER
8			local SQL_PASS
9			local SQL_HOST
10			local SQL_DB
11			local SQL_COMMAND
12
13	niro	2003	SQL_USER="$1"
14			SQL_PASS="$2"
15			SQL_HOST="$3"
16			SQL_DB="$4"
17			SQL_COMMAND="$5"
18	niro	219
19	niro	2003	# fallback (SQL_OPTS not, they are optional )
20	niro	3472	if [[ -z ${SQL_USER} ]] \|\|
21			[[ -z ${SQL_PASS} ]] \|\|
22			[[ -z ${SQL_HOST} ]] \|\|
23			[[ -z ${SQL_DB} ]] \|\|
24			[[ -z ${SQL_COMMAND} ]]
25	niro	219	then
26			echo
27			echo "Usage:"
28			echo -e " mysql_command user pass host db \"sql-command\""
29			echo
30			return 1
31			fi
32	niro	2003
33	niro	219	mysql \
34			--user="${SQL_USER}" \
35			--password="${SQL_PASS}" \
36			--host="${SQL_HOST}" \
37			--database="${SQL_DB}" \
38			--batch \
39			--skip-column-names \
40			--execute="${SQL_COMMAND}" \
41			\|\| return 1
42	niro	2003
43	niro	219	return 0
44			}
45
46	niro	344	mysql_enum_colums()
47			{
48	niro	219	local SQL_USER
49			local SQL_PASS
50			local SQL_HOST
51			local SQL_DB
52			local SQL_TABLE
53
54			local SHOWTABLES
55			local i
56			local read
57			local field
58			local type
59			local null
60			local key
61			local default
62			local extra
63	niro	2003
64	niro	219	SQL_USER=$1
65			SQL_PASS=$2
66			SQL_HOST=$3
67			SQL_DB=$4
68			SQL_TABLE=$5
69	niro	2003
70			# show the column names ?
71	niro	219	if [ -n "${6}" -a "${6}" == "show" ]
72			then
73			SHOWTABLES=true
74			else
75			SHOWTABLES=false
76			fi
77
78	niro	2003	# fallback (SQL_OPTS not, they are optional )
79	niro	3472	if [[ -z ${SQL_USER} ]] \|\|
80			[[ -z ${SQL_PASS} ]] \|\|
81			[[ -z ${SQL_HOST} ]] \|\|
82			[[ -z ${SQL_DB} ]] \|\|
83			[[ -z ${SQL_TABLE} ]]
84	niro	219	then
85			echo
86			echo "Usage:"
87			echo -e " mysql_enum_columns user pass host db table [show]"
88			echo
89			return 1
90			fi
91
92			declare -i i=0
93			while read field type null key default extra
94			do
95			i=${i}+1
96			[ "${SHOWTABLES}" == true ] && echo "${field}"
97			done << EOF
98			$(mysql_command ${SQL_USER} ${SQL_PASS} ${SQL_HOST} ${SQL_DB} "show columns from ${SQL_TABLE};")
99			EOF
100			[ "${SHOWTABLES}" == true ] \|\| echo "${i}"
101			}
102	niro	284
103	niro	344	beep3x()
104			{
105	niro	284	echo -en "\a"
106			sleep 0.7
107			echo -en "\a"
108			sleep 0.7
109			echo -en "\a"
110			}
111
112			reach_mysql_server()
113			{
114	niro	298	# set connection timeout to 20 secs
115			mysql --connect_timeout=20 \
116	niro	286	--user="${SQL_USER}" \
117			--password="${SQL_PASS}" \
118			--host="${SQL_HOST}" \
119			--database="${SQL_DB}" \
120	niro	284	--batch \
121	niro	293	--execute="" &> /dev/null && return 0 \|\| \
122	niro	284	(
123	niro	3472	echo -e "${COLRED}"
124	niro	284	echo "Cannot reach a mysql-server at '${SQL_HOST}'."
125			echo "None of the current settings of this client will be touched."
126			echo "Please inform your system-administrator."
127	niro	3472	echo -e "${COLDEFAULT}"
128	niro	284	beep3x
129			sleep 5
130			return 1
131			)
132			}
133	niro	344
134			mysqldo()
135			{
136			mysql_command \
137			"${SQL_USER}" \
138			"${SQL_PASS}" \
139			"${SQL_HOST}" \
140			"${SQL_DB}" \
141			"$@" \
142			&& return 0 \|\| return 1
143			}
144	niro	2143
145			# read tables and evaluate all variables
146			# cmd: evaluate_table cfg_network
147			# result: cfg_network_hostname=DUMMY_HOSTNAME
148			evaluate_table()
149			{
150			local table="$1"
151			local where_statement
152
153			if [[ -z $2 ]]
154			then
155			where_statement="where serial='${ALX_SERIAL}'"
156			else
157			where_statement="$2"
158			fi
159
160	niro	2152	eval $(mysql \
161	niro	2143	--user="${SQL_USER}" \
162			--password="${SQL_PASS}" \
163			--host="${SQL_HOST}" \
164			--database="${SQL_DB}" \
165			--xml \
166			--execute="select * from ${table} ${where_statement}" \
167			\| xml sel -T -t -m //row/field \
168	niro	2152	-v "concat('${table}_', @name, '=', '\"', self::field, '\"')" -n)
169	niro	2143	}
170
171			# runs a statement and evaluate the resulting columns
172			# cmd: evaluate_command "select serial from cfg_serial where serial=10"
173			# result: serial=10
174			# or with a prefix given
175			# cmd: evaluate_command "select serial from cfg_serial where serial=10" "cfg_serial"
176			# result: cfg_serial_serial=10
177			evaluate_statement()
178			{
179			local statement="$1"
180			local prefix="$2"
181
182			if [[ ! -z ${prefix} ]]
183			then
184			prefix="${prefix}_"
185			fi
186
187	niro	2152	eval $(mysql \
188	niro	2143	--user="${SQL_USER}" \
189			--password="${SQL_PASS}" \
190			--host="${SQL_HOST}" \
191			--database="${SQL_DB}" \
192			--xml \
193			--execute="${statement}" \
194			\| xml sel -T -t -m //row/field \
195			-v "concat('${prefix}', @name, '=', '\"', self::field, '\"')" -n)
196			}