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