Contents of /alx-src/tags/alxconf-0_6_0-rc4/functions/mysqlfunctions
Parent Directory | Revision Log
Revision 2413 -
(show annotations)
(download)
Wed Jun 15 13:02:44 2011 UTC (13 years, 3 months ago) by niro
File size: 3698 byte(s)
Wed Jun 15 13:02:44 2011 UTC (13 years, 3 months ago) by niro
File size: 3698 byte(s)
tagged 'alxconf-0_6_0-rc4'
1 | # $Id$ |
2 | # mysql functions for bash |
3 | |
4 | mysql_command() |
5 | { |
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 | 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 | |
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 0.7 |
107 | echo -en "\a" |
108 | sleep 0.7 |
109 | echo -en "\a" |
110 | } |
111 | |
112 | reach_mysql_server() |
113 | { |
114 | # set connection timeout to 20 secs |
115 | mysql --connect_timeout=20 \ |
116 | --user="${SQL_USER}" \ |
117 | --password="${SQL_PASS}" \ |
118 | --host="${SQL_HOST}" \ |
119 | --database="${SQL_DB}" \ |
120 | --batch \ |
121 | --execute="" &> /dev/null && return 0 || \ |
122 | ( |
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 | |
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 | |
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 | eval $(mysql \ |
161 | --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 | -v "concat('${table}_', @name, '=', '\"', self::field, '\"')" -n) |
169 | } |
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 | eval $(mysql \ |
188 | --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 | * |