Contents of /mcore-src/trunk/mcore-tools/src/include/mysqlfunctions.global.class.in
Parent Directory | Revision Log
Revision 2483 -
(show annotations)
(download)
Thu Sep 10 10:55:29 2015 UTC (8 years, 8 months ago) by niro
File size: 4756 byte(s)
Thu Sep 10 10:55:29 2015 UTC (8 years, 8 months ago) by niro
File size: 4756 byte(s)
-use MCORE_ prefixed sql variables from control.con
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 | } |