src/include/mysqlfunctions.global.class.in

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