src/include/mysqlfunctions.global.class.in

# $Id$

provide mysql sql

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

	mariadb \
		--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
	mariadb --connect_timeout=20 \
		--user="${MCORE_SQL_USER}" \
		--password="${MCORE_SQL_PASS}" \
		--host="${MCORE_SQL_HOST}" \
		--database="${MCORE_SQL_DB}" \
		--batch \
		--execute="" &> /dev/null && return 0 || \
		(
			echo -e ${COLRED}
			echo "Cannot reach a mysql-server at '${MCORE_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 \
		"${MCORE_SQL_USER}" \
		"${MCORE_SQL_PASS}" \
		"${MCORE_SQL_HOST}" \
		"${MCORE_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 ${MCORE_SQL_USER} ${MCORE_SQL_PASS} ${MCORE_SQL_HOST} ${MCORE_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

	eval $(mariadb \
		--user="${MCORE_SQL_USER}" \
		--password="${MCORE_SQL_PASS}" \
		--host="${MCORE_SQL_HOST}" \
		--database="${MCORE_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}

	decho "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	niro	2093	# $Id$
2
3	niro	2486	provide mysql sql
4
5	niro	2093	mysql_command()
6			{
7	niro	2481	local sql_user
8			local sql_pass
9			local sql_host
10			local sql_db
11			local sql_command
12	niro	2093
13	niro	2481	sql_user=$1
14			sql_pass=$2
15			sql_host=$3
16			sql_db=$4
17			sql_command=$5
18	niro	2093
19	niro	2481	#fallback (sql_opts not, they are optional )
20			if [ -z "${sql_user}" \
21			-o -z "${sql_pass}" \
22			-o -z "${sql_host}" \
23			-o -z "${sql_db}" \
24			-o -z "${sql_command}" ]
25	niro	2093	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	2481
33	niro	2907	mariadb \
34	niro	2481	--user="${sql_user}" \
35			--password="${sql_pass}" \
36			--host="${sql_host}" \
37			--database="${sql_db}" \
38	niro	2093	--batch \
39			--skip-column-names \
40	niro	2481	--execute="${sql_command}" \
41	niro	2093	\|\| return 1
42	niro	2481
43	niro	2093	return 0
44			}
45
46			mysql_enum_colums()
47			{
48	niro	2481	local sql_user
49			local sql_pass
50			local sql_host
51			local sql_db
52			local sql_table
53	niro	2093
54	niro	2481	local showtables
55	niro	2093	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	2481
64			sql_user=$1
65			sql_pass=$2
66			sql_host=$3
67			sql_db=$4
68			sql_table=$5
69
70			# show the column names ?
71	niro	2093	if [ -n "${6}" -a "${6}" == "show" ]
72			then
73	niro	2481	showtables=true
74	niro	2093	else
75	niro	2481	showtables=false
76	niro	2093	fi
77
78	niro	2481	# fallback (sql_opts not, they are optional )
79			if [ -z "${sql_user}" \
80			-o -z "${sql_pass}" \
81			-o -z "${sql_host}" \
82			-o -z "${sql_db}" \
83			-o -z "${sql_table}" ]
84	niro	2093	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	niro	2481	[ "${showtables}" == true ] && echo "${field}"
97	niro	2093	done << EOF
98	niro	2481	$(mysql_command ${sql_user} ${sql_pass} ${sql_host} ${sql_db} "show columns from ${sql_table};")
99	niro	2093	EOF
100	niro	2481	[ "${showtables}" == true ] \|\| echo "${i}"
101	niro	2093	}
102
103			beep3x()
104			{
105			echo -en "\a"
106			sleep 1
107			echo -en "\a"
108			sleep 1
109			echo -en "\a"
110			}
111
112			reach_mysql_server()
113			{
114			# set connection timeout to 20 secs
115	niro	2907	mariadb --connect_timeout=20 \
116	niro	2483	--user="${MCORE_SQL_USER}" \
117			--password="${MCORE_SQL_PASS}" \
118			--host="${MCORE_SQL_HOST}" \
119			--database="${MCORE_SQL_DB}" \
120	niro	2093	--batch \
121			--execute="" &> /dev/null && return 0 \|\| \
122			(
123			echo -e ${COLRED}
124	niro	2483	echo "Cannot reach a mysql-server at '${MCORE_SQL_HOST}'."
125	niro	2093	echo "None of the current settings of this client will be touched."
126			echo "Please inform your system-administrator."
127			echo -e ${COLDEFAULT}
128			beep3x
129			sleep 5
130			return 1
131			)
132			}
133
134			mysqldo()
135			{
136			mysql_command \
137	niro	2483	"${MCORE_SQL_USER}" \
138			"${MCORE_SQL_PASS}" \
139			"${MCORE_SQL_HOST}" \
140			"${MCORE_SQL_DB}" \
141	niro	2093	"$@" \
142			&& return 0 \|\| return 1
143			}
144
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 all_vars
151			local all_items
152			local i
153	niro	2481	local count
154	niro	2093	local where_statement
155
156			local table="$1"
157
158			if [[ -z $2 ]]
159			then
160			where_statement="where serial='${ALX_SERIAL}'"
161			else
162			where_statement="$2"
163			fi
164
165			# setup arrays
166	niro	2483	all_vars=( $(mysql_enum_colums ${MCORE_SQL_USER} ${MCORE_SQL_PASS} ${MCORE_SQL_HOST} ${MCORE_SQL_DB} ${table} show) )
167	niro	2093	all_items=( $(mysqldo "select * from ${table} ${where_statement}") )
168
169			# sanity check
170			if [[ ${#all_vars[]} -ne ${#all_items[]} ]]
171			then
172			echo "error: arrays does not match!"
173			fi
174
175	niro	2481	count=${#all_vars[*]}
176			for ((i=0;i < count; i++))
177	niro	2093	do
178			eval $(echo ${table}_${all_vars[${i}]}=\${all_items[${i}]})
179			export ${table}_${all_vars[${i}]}
180			done
181			}
182
183			# read tables and evaluate all variables
184			# cmd: evaluate_table cfg_network
185			# result: cfg_network_hostname=DUMMY_HOSTNAME
186			evaluate_table_xml()
187			{
188			local table="$1"
189			local where_statement
190
191			if [[ -z $2 ]]
192			then
193			where_statement="where serial='${ALX_SERIAL}'"
194			else
195			where_statement="$2"
196			fi
197
198	niro	2907	eval $(mariadb \
199	niro	2483	--user="${MCORE_SQL_USER}" \
200			--password="${MCORE_SQL_PASS}" \
201			--host="${MCORE_SQL_HOST}" \
202			--database="${MCORE_SQL_DB}" \
203	niro	2093	--xml \
204			--execute="select * from ${table} ${where_statement}" \
205			\| xml sel -T -t -m //row/field \
206	niro	2876	-v "concat('${table}_',@name,'=','\"',self::field,'\"')" -n)
207	niro	2093	}
208
209			mysql_insert()
210			{
211			local cmdline="$@"
212			#local table="$1"
213			local table="${cmdline%%,*}"
214			local argv
215			local insert_line
216			local update_line
217			local key
218			local value
219			local counter
220
221			OLD_IFS=${IFS}
222			IFS=,
223			declare -i counter=0
224			#for argv in ${cmdline/$1/} # first value is the table_name
225			for argv in ${cmdline#*,} # first value is the table_name
226			do
227			key="${argv%=*}"
228			value="${argv#*=}"
229
230			if [ ${counter} -eq 0 ]
231			then
232			insert_keys="${key}"
233			insert_values="'${value}'"
234			update_line="${key}='${value}'"
235			else
236			insert_keys+=",${key}"
237			insert_values+=",'${value}'"
238			update_line+=",${key}='${value}'"
239			fi
240			(( counter ++ ))
241			done
242			IFS=${OLD_IFS}
243
244	niro	2494	decho "INSERT INTO ${table} (${insert_keys}) VALUES(${insert_values}) ON DUPLICATE KEY UPDATE ${update_line};"
245	niro	2093	mysqldo "insert into ${table} (${insert_keys}) values(${insert_values}) on duplicate key update ${update_line};"
246			}