Annotation of /alx-src/trunk/tinyalxconfig-ng/functions/mysqlfunctions

#!/bin/bash

#
# mysql functions for the bash
# Version 0.1
#
# Niels Rogalla <niro@magellan-linux.de>
#
# $Header: /home/cvsd/alx-cvs/alx-src/tinyalxconfig-ng/functions/mysqlfunctions,v 1.4 2008-06-05 18:04:41 niro Exp $

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}" \
		-o -z "${SQL_PASS}" \
		-o -z "${SQL_HOST}" \
		-o -z "${SQL_DB}" \
		-o -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}" \
		-o -z "${SQL_PASS}" \
		-o -z "${SQL_HOST}" \
		-o -z "${SQL_DB}" \
		-o -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 1
	echo -en "\a"
	sleep 1
	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_tables cfg_network
# result: cfg_network_hostname=DUMMY_HOSTNAME
evaluate_tables()
{
	local all_vars
	local all_items
	local i
	local COUNT
	local where_statement

	local table="$1"

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

	# setup arrays
	all_vars=( $(mysql_enum_colums ${SQL_USER} ${SQL_PASS} ${SQL_HOST} ${SQL_DB} ${table} show) )
	all_items=( $(mysqldo "select * from ${table} ${where_statement}") )

	# sanity check
	if [[ ${#all_vars[*]} = ${#all_items[*]} ]]
	then
		echo "error: arrays does not match!"
	fi

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