src/include/mysqlfunctions.global.class

#!/bin/bash

#
# mysql functions for the bash
# Version 0.1
#
# Niels Rogalla <niro@magellan-linux.de>
#
# $Id$

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_table cfg_network
# result: cfg_network_hostname=DUMMY_HOSTNAME
evaluate_table()
{
	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[*]} -ne ${#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
}

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

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

	export 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)
}

mysql_insert()
{
	local cmdline="$@"
	#local table="$1"
	local table="${cmdline%%,*}"
	local argv
	local insert_line
	local update_line
	local key
	local value
	local counter

	OLD_IFS=${IFS}
	IFS=,
	declare -i counter=0
	#for argv in ${cmdline/$1/} # first value is the table_name
	for argv in ${cmdline#*,} # first value is the table_name
	do
		key="${argv%=*}"
		value="${argv#*=}"

		if [ ${counter} -eq 0 ]
		then
			insert_keys="${key}"
			insert_values="'${value}'"
			update_line="${key}='${value}'"
		else
			insert_keys+=",${key}"
			insert_values+=",'${value}'"
			update_line+=",${key}='${value}'"
		fi
		(( counter ++ ))
	done
	IFS=${OLD_IFS}

	echo "INSERT INTO ${table} (${insert_keys}) VALUES(${insert_values}) ON DUPLICATE KEY UPDATE ${update_line};"
	mysqldo "insert into ${table} (${insert_keys}) values(${insert_values}) on duplicate key update ${update_line};"
}
1	#!/bin/bash
2
3	#
4	# mysql functions for the bash
5	# Version 0.1
6	#
7	# Niels Rogalla <niro@magellan-linux.de>
8	#
9	# $Id$
10
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	sleep 1
114	echo -en "\a"
115	sleep 1
116	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
152	# read tables and evaluate all variables
153	# cmd: evaluate_table cfg_network
154	# result: cfg_network_hostname=DUMMY_HOSTNAME
155	evaluate_table()
156	{
157	local all_vars
158	local all_items
159	local i
160	local COUNT
161	local where_statement
162
163	local table="$1"
164
165	if [[ -z $2 ]]
166	then
167	where_statement="where serial='${ALX_SERIAL}'"
168	else
169	where_statement="$2"
170	fi
171
172	# setup arrays
173	all_vars=( $(mysql_enum_colums ${SQL_USER} ${SQL_PASS} ${SQL_HOST} ${SQL_DB} ${table} show) )
174	all_items=( $(mysqldo "select * from ${table} ${where_statement}") )
175
176	# sanity check
177	if [[ ${#all_vars[]} -ne ${#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	}
189
190	# read tables and evaluate all variables
191	# cmd: evaluate_table cfg_network
192	# result: cfg_network_hostname=DUMMY_HOSTNAME
193	evaluate_table_xml()
194	{
195	local table="$1"
196	local where_statement
197
198	if [[ -z $2 ]]
199	then
200	where_statement="where serial='${ALX_SERIAL}'"
201	else
202	where_statement="$2"
203	fi
204
205	export eval $(mysql \
206	--user="${SQL_USER}" \
207	--password="${SQL_PASS}" \
208	--host="${SQL_HOST}" \
209	--database="${SQL_DB}" \
210	--xml \
211	--execute="select * from ${table} ${where_statement}" \
212	\| xml sel -T -t -m //row/field \
213	-v "concat('${table}_',@name,'=',self::field)" -n)
214	}
215
216	mysql_insert()
217	{
218	local cmdline="$@"
219	#local table="$1"
220	local table="${cmdline%%,*}"
221	local argv
222	local insert_line
223	local update_line
224	local key
225	local value
226	local counter
227
228	OLD_IFS=${IFS}
229	IFS=,
230	declare -i counter=0
231	#for argv in ${cmdline/$1/} # first value is the table_name
232	for argv in ${cmdline#*,} # first value is the table_name
233	do
234	key="${argv%=*}"
235	value="${argv#*=}"
236
237	if [ ${counter} -eq 0 ]
238	then
239	insert_keys="${key}"
240	insert_values="'${value}'"
241	update_line="${key}='${value}'"
242	else
243	insert_keys+=",${key}"
244	insert_values+=",'${value}'"
245	update_line+=",${key}='${value}'"
246	fi
247	(( counter ++ ))
248	done
249	IFS=${OLD_IFS}
250
251	echo "INSERT INTO ${table} (${insert_keys}) VALUES(${insert_values}) ON DUPLICATE KEY UPDATE ${update_line};"
252	mysqldo "insert into ${table} (${insert_keys}) values(${insert_values}) on duplicate key update ${update_line};"
253	}