Annotation of /mcore-src/trunk/mcore-tools/daemon/include/mysqlfunctions.global.class
Parent Directory | Revision Log
Revision 2093 -
(hide annotations)
(download)
Tue May 21 09:09:51 2013 UTC (11 years ago) by niro
File size: 4763 byte(s)
Tue May 21 09:09:51 2013 UTC (11 years ago) by niro
File size: 4763 byte(s)
-add mysql basic functions
1 | niro | 2093 | #!/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 | } |