Contents of /mcore-src/trunk/mcore-tools/src/include/mysqlfunctions.global.class.in
Parent Directory | Revision Log
Revision 2230 -
(show annotations)
(download)
Fri Jan 10 23:53:29 2014 UTC (10 years, 8 months ago) by niro
File size: 4763 byte(s)
Fri Jan 10 23:53:29 2014 UTC (10 years, 8 months ago) by niro
File size: 4763 byte(s)
-renamed mysqlfunctions.global.class -> mysqlfunctions.global.class.in
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 | } |