Annotation of /alx-src/branches/alxconf-060/functions/mysqlfunctions
Parent Directory | Revision Log
Revision 2152 -
(hide annotations)
(download)
Tue May 17 21:33:57 2011 UTC (13 years, 4 months ago) by niro
File size: 3698 byte(s)
Tue May 17 21:33:57 2011 UTC (13 years, 4 months ago) by niro
File size: 3698 byte(s)
-eval already exports the variable - no need to do it twice, wich produces escaping issues
1 | niro | 2003 | # $Id$ |
2 | # mysql functions for bash | ||
3 | niro | 219 | |
4 | niro | 344 | mysql_command() |
5 | { | ||
6 | niro | 219 | |
7 | local SQL_USER | ||
8 | local SQL_PASS | ||
9 | local SQL_HOST | ||
10 | local SQL_DB | ||
11 | local SQL_COMMAND | ||
12 | |||
13 | niro | 2003 | SQL_USER="$1" |
14 | SQL_PASS="$2" | ||
15 | SQL_HOST="$3" | ||
16 | SQL_DB="$4" | ||
17 | SQL_COMMAND="$5" | ||
18 | niro | 219 | |
19 | niro | 2003 | # fallback (SQL_OPTS not, they are optional ) |
20 | niro | 219 | 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 | niro | 2003 | |
33 | niro | 219 | mysql \ |
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 | niro | 2003 | |
43 | niro | 219 | return 0 |
44 | } | ||
45 | |||
46 | niro | 344 | mysql_enum_colums() |
47 | { | ||
48 | niro | 219 | 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 | niro | 2003 | |
64 | niro | 219 | SQL_USER=$1 |
65 | SQL_PASS=$2 | ||
66 | SQL_HOST=$3 | ||
67 | SQL_DB=$4 | ||
68 | SQL_TABLE=$5 | ||
69 | niro | 2003 | |
70 | # show the column names ? | ||
71 | niro | 219 | if [ -n "${6}" -a "${6}" == "show" ] |
72 | then | ||
73 | SHOWTABLES=true | ||
74 | else | ||
75 | SHOWTABLES=false | ||
76 | fi | ||
77 | |||
78 | niro | 2003 | # fallback (SQL_OPTS not, they are optional ) |
79 | niro | 219 | 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 | niro | 284 | |
103 | niro | 344 | beep3x() |
104 | { | ||
105 | niro | 284 | echo -en "\a" |
106 | sleep 0.7 | ||
107 | echo -en "\a" | ||
108 | sleep 0.7 | ||
109 | echo -en "\a" | ||
110 | } | ||
111 | |||
112 | reach_mysql_server() | ||
113 | { | ||
114 | niro | 298 | # set connection timeout to 20 secs |
115 | mysql --connect_timeout=20 \ | ||
116 | niro | 286 | --user="${SQL_USER}" \ |
117 | --password="${SQL_PASS}" \ | ||
118 | --host="${SQL_HOST}" \ | ||
119 | --database="${SQL_DB}" \ | ||
120 | niro | 284 | --batch \ |
121 | niro | 293 | --execute="" &> /dev/null && return 0 || \ |
122 | niro | 284 | ( |
123 | echo -e ${COLRED} | ||
124 | echo "Cannot reach a mysql-server at '${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 | niro | 344 | |
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 | niro | 2143 | |
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 | niro | 2152 | eval $(mysql \ |
161 | niro | 2143 | --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 | niro | 2152 | -v "concat('${table}_', @name, '=', '\"', self::field, '\"')" -n) |
169 | niro | 2143 | } |
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 | niro | 2152 | eval $(mysql \ |
188 | niro | 2143 | --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 | } |
Properties
Name | Value |
---|---|
svn:executable | * |