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

	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="${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

	export eval $(mysql \
		--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}

	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	# $Id$
2
3	provide mysql sql
4
5	mysql_command()
6	{
7	local sql_user
8	local sql_pass
9	local sql_host
10	local sql_db
11	local sql_command
12
13	sql_user=$1
14	sql_pass=$2
15	sql_host=$3
16	sql_db=$4
17	sql_command=$5
18
19	#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	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
33	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
43	return 0
44	}
45
46	mysql_enum_colums()
47	{
48	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
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	if [ -n "${6}" -a "${6}" == "show" ]
72	then
73	showtables=true
74	else
75	showtables=false
76	fi
77
78	# 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	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
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	mysql --connect_timeout=20 \
116	--user="${MCORE_SQL_USER}" \
117	--password="${MCORE_SQL_PASS}" \
118	--host="${MCORE_SQL_HOST}" \
119	--database="${MCORE_SQL_DB}" \
120	--batch \
121	--execute="" &> /dev/null && return 0 \|\| \
122	(
123	echo -e ${COLRED}
124	echo "Cannot reach a mysql-server at '${MCORE_SQL_HOST}'."
125	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	"${MCORE_SQL_USER}" \
138	"${MCORE_SQL_PASS}" \
139	"${MCORE_SQL_HOST}" \
140	"${MCORE_SQL_DB}" \
141	"$@" \
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	local count
154	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	all_vars=( $(mysql_enum_colums ${MCORE_SQL_USER} ${MCORE_SQL_PASS} ${MCORE_SQL_HOST} ${MCORE_SQL_DB} ${table} show) )
167	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	count=${#all_vars[*]}
176	for ((i=0;i < count; i++))
177	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	export eval $(mysql \
199	--user="${MCORE_SQL_USER}" \
200	--password="${MCORE_SQL_PASS}" \
201	--host="${MCORE_SQL_HOST}" \
202	--database="${MCORE_SQL_DB}" \
203	--xml \
204	--execute="select * from ${table} ${where_statement}" \
205	\| xml sel -T -t -m //row/field \
206	-v "concat('${table}_',@name,'=',self::field)" -n)
207	}
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	echo "INSERT INTO ${table} (${insert_keys}) VALUES(${insert_values}) ON DUPLICATE KEY UPDATE ${update_line};"
245	mysqldo "insert into ${table} (${insert_keys}) values(${insert_values}) on duplicate key update ${update_line};"
246	}