1 |
#!/bin/bash |
# $Id$ |
2 |
|
# mysql functions for bash |
3 |
|
|
4 |
# |
mysql_command() |
5 |
# mysql functions for the bash |
{ |
|
# Version 0.1 |
|
|
# |
|
|
# Niels Rogalla <niro@magellan-linux.de> |
|
|
# |
|
|
# $Header: /home/cvsd/alx-cvs/alx-src/alxconfig-ng/functions/mysqlfunctions,v 1.5 2005-08-17 22:09:59 niro Exp $ |
|
|
|
|
|
mysql_command(){ |
|
6 |
|
|
7 |
local SQL_USER |
local SQL_USER |
8 |
local SQL_PASS |
local SQL_PASS |
10 |
local SQL_DB |
local SQL_DB |
11 |
local SQL_COMMAND |
local SQL_COMMAND |
12 |
|
|
13 |
SQL_USER=$1 |
SQL_USER="$1" |
14 |
SQL_PASS=$2 |
SQL_PASS="$2" |
15 |
SQL_HOST=$3 |
SQL_HOST="$3" |
16 |
SQL_DB=$4 |
SQL_DB="$4" |
17 |
SQL_COMMAND=$5 |
SQL_COMMAND="$5" |
18 |
|
|
19 |
#fallback (SQL_OPTS not, they are optional ) |
# fallback (SQL_OPTS not, they are optional ) |
20 |
if [ -z "${SQL_USER}" \ |
if [ -z "${SQL_USER}" \ |
21 |
-o -z "${SQL_PASS}" \ |
-o -z "${SQL_PASS}" \ |
22 |
-o -z "${SQL_HOST}" \ |
-o -z "${SQL_HOST}" \ |
29 |
echo |
echo |
30 |
return 1 |
return 1 |
31 |
fi |
fi |
32 |
|
|
33 |
mysql \ |
mysql \ |
34 |
--user="${SQL_USER}" \ |
--user="${SQL_USER}" \ |
35 |
--password="${SQL_PASS}" \ |
--password="${SQL_PASS}" \ |
39 |
--skip-column-names \ |
--skip-column-names \ |
40 |
--execute="${SQL_COMMAND}" \ |
--execute="${SQL_COMMAND}" \ |
41 |
|| return 1 |
|| return 1 |
42 |
|
|
43 |
return 0 |
return 0 |
44 |
} |
} |
45 |
|
|
46 |
mysql_enum_colums(){ |
mysql_enum_colums() |
47 |
|
{ |
48 |
local SQL_USER |
local SQL_USER |
49 |
local SQL_PASS |
local SQL_PASS |
50 |
local SQL_HOST |
local SQL_HOST |
60 |
local key |
local key |
61 |
local default |
local default |
62 |
local extra |
local extra |
63 |
|
|
64 |
SQL_USER=$1 |
SQL_USER=$1 |
65 |
SQL_PASS=$2 |
SQL_PASS=$2 |
66 |
SQL_HOST=$3 |
SQL_HOST=$3 |
67 |
SQL_DB=$4 |
SQL_DB=$4 |
68 |
SQL_TABLE=$5 |
SQL_TABLE=$5 |
69 |
|
|
70 |
#show the column names ? |
# show the column names ? |
71 |
if [ -n "${6}" -a "${6}" == "show" ] |
if [ -n "${6}" -a "${6}" == "show" ] |
72 |
then |
then |
73 |
SHOWTABLES=true |
SHOWTABLES=true |
75 |
SHOWTABLES=false |
SHOWTABLES=false |
76 |
fi |
fi |
77 |
|
|
78 |
#fallback (SQL_OPTS not, they are optional ) |
# fallback (SQL_OPTS not, they are optional ) |
79 |
if [ -z "${SQL_USER}" \ |
if [ -z "${SQL_USER}" \ |
80 |
-o -z "${SQL_PASS}" \ |
-o -z "${SQL_PASS}" \ |
81 |
-o -z "${SQL_HOST}" \ |
-o -z "${SQL_HOST}" \ |
100 |
[ "${SHOWTABLES}" == true ] || echo "${i}" |
[ "${SHOWTABLES}" == true ] || echo "${i}" |
101 |
} |
} |
102 |
|
|
103 |
beep3x() { |
beep3x() |
104 |
|
{ |
105 |
echo -en "\a" |
echo -en "\a" |
106 |
sleep 0.7 |
sleep 0.7 |
107 |
echo -en "\a" |
echo -en "\a" |
111 |
|
|
112 |
reach_mysql_server() |
reach_mysql_server() |
113 |
{ |
{ |
114 |
# set connection timeout to 10 secs |
# set connection timeout to 20 secs |
115 |
mysql --connect_timeout=10 \ |
mysql --connect_timeout=20 \ |
116 |
--user="${SQL_USER}" \ |
--user="${SQL_USER}" \ |
117 |
--password="${SQL_PASS}" \ |
--password="${SQL_PASS}" \ |
118 |
--host="${SQL_HOST}" \ |
--host="${SQL_HOST}" \ |
119 |
--database="${SQL_DB}" \ |
--database="${SQL_DB}" \ |
120 |
--batch \ |
--batch \ |
121 |
--execute="" $> /dev/null && return 0 || \ |
--execute="" &> /dev/null && return 0 || \ |
122 |
( |
( |
123 |
echo -e ${COLRED} |
echo -e ${COLRED} |
124 |
echo "Cannot reach a mysql-server at '${SQL_HOST}'." |
echo "Cannot reach a mysql-server at '${SQL_HOST}'." |
130 |
return 1 |
return 1 |
131 |
) |
) |
132 |
} |
} |
133 |
|
|
134 |
|
mysqldo() |
135 |
|
{ |
136 |
|
mysql_command \ |
137 |
|
"${SQL_USER}" \ |
138 |
|
"${SQL_PASS}" \ |
139 |
|
"${SQL_HOST}" \ |
140 |
|
"${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 table="$1" |
151 |
|
local where_statement |
152 |
|
|
153 |
|
if [[ -z $2 ]] |
154 |
|
then |
155 |
|
where_statement="where serial='${ALX_SERIAL}'" |
156 |
|
else |
157 |
|
where_statement="$2" |
158 |
|
fi |
159 |
|
|
160 |
|
export eval $(mysql \ |
161 |
|
--user="${SQL_USER}" \ |
162 |
|
--password="${SQL_PASS}" \ |
163 |
|
--host="${SQL_HOST}" \ |
164 |
|
--database="${SQL_DB}" \ |
165 |
|
--xml \ |
166 |
|
--execute="select * from ${table} ${where_statement}" \ |
167 |
|
| xml sel -T -t -m //row/field \ |
168 |
|
-v "concat('${table}_', @name, '=', '\"' ,self::field, '\"')" -n) |
169 |
|
} |
170 |
|
|
171 |
|
# runs a statement and evaluate the resulting columns |
172 |
|
# cmd: evaluate_command "select serial from cfg_serial where serial=10" |
173 |
|
# result: serial=10 |
174 |
|
# or with a prefix given |
175 |
|
# cmd: evaluate_command "select serial from cfg_serial where serial=10" "cfg_serial" |
176 |
|
# result: cfg_serial_serial=10 |
177 |
|
evaluate_statement() |
178 |
|
{ |
179 |
|
local statement="$1" |
180 |
|
local prefix="$2" |
181 |
|
|
182 |
|
if [[ ! -z ${prefix} ]] |
183 |
|
then |
184 |
|
prefix="${prefix}_" |
185 |
|
fi |
186 |
|
|
187 |
|
export eval $(mysql \ |
188 |
|
--user="${SQL_USER}" \ |
189 |
|
--password="${SQL_PASS}" \ |
190 |
|
--host="${SQL_HOST}" \ |
191 |
|
--database="${SQL_DB}" \ |
192 |
|
--xml \ |
193 |
|
--execute="${statement}" \ |
194 |
|
| xml sel -T -t -m //row/field \ |
195 |
|
-v "concat('${prefix}', @name, '=', '\"', self::field, '\"')" -n) |
196 |
|
} |