Annotation of /alx-src/branches/alx-web-070/scripts/sql-schema/mcore-sql-schema.sh
Parent Directory | Revision Log
Revision 8509 -
(hide annotations)
(download)
(as text)
Mon Feb 8 08:52:47 2016 UTC (8 years, 3 months ago) by niro
File MIME type: application/x-sh
File size: 10659 byte(s)
Mon Feb 8 08:52:47 2016 UTC (8 years, 3 months ago) by niro
File MIME type: application/x-sh
File size: 10659 byte(s)
-get position from opts, no weird extra handling anymore just use standard sql queries
1 | niro | 8485 | #!/bin/bash |
2 | |||
3 | # include everything from include | ||
4 | for inc in $(find ./include -type f -name \*.sh) | ||
5 | do | ||
6 | source "${inc}" | ||
7 | done | ||
8 | |||
9 | die() | ||
10 | { | ||
11 | echo "ERROR: $@" | ||
12 | exit 1 | ||
13 | } | ||
14 | |||
15 | mysql_command() | ||
16 | { | ||
17 | local sql_user | ||
18 | local sql_pass | ||
19 | local sql_host | ||
20 | local sql_db | ||
21 | local sql_command | ||
22 | |||
23 | sql_user=$1 | ||
24 | sql_pass=$2 | ||
25 | sql_host=$3 | ||
26 | sql_db=$4 | ||
27 | sql_command=$5 | ||
28 | |||
29 | #fallback (sql_opts not, they are optional ) | ||
30 | if [ -z "${sql_user}" \ | ||
31 | -o -z "${sql_pass}" \ | ||
32 | -o -z "${sql_host}" \ | ||
33 | -o -z "${sql_db}" \ | ||
34 | -o -z "${sql_command}" ] | ||
35 | then | ||
36 | echo | ||
37 | echo "Usage:" | ||
38 | echo -e " mysql_command user pass host db \"sql-command\"" | ||
39 | echo | ||
40 | return 1 | ||
41 | fi | ||
42 | |||
43 | mysql \ | ||
44 | --user="${sql_user}" \ | ||
45 | --password="${sql_pass}" \ | ||
46 | --host="${sql_host}" \ | ||
47 | --database="${sql_db}" \ | ||
48 | --batch \ | ||
49 | --skip-column-names \ | ||
50 | --execute="${sql_command}" \ | ||
51 | || return 1 | ||
52 | |||
53 | return 0 | ||
54 | } | ||
55 | |||
56 | mysqldo() | ||
57 | { | ||
58 | mysql_command \ | ||
59 | "${MCORE_SQL_USER}" \ | ||
60 | "${MCORE_SQL_PASS}" \ | ||
61 | "${MCORE_SQL_HOST}" \ | ||
62 | "${MCORE_SQL_DB}" \ | ||
63 | "$@" \ | ||
64 | && return 0 || return 1 | ||
65 | } | ||
66 | |||
67 | # creates a mysql table with help of a defined array TABLE_DEFINITION: | ||
68 | # array values are a struct like this: | ||
69 | # first array element is the table name, everything else are column descriptions | ||
70 | # TABLE_DEFINITION=( | ||
71 | # "table_name" | ||
72 | # "column1_name column1_argvs" | ||
73 | # "column2_name column2_argvs" | ||
74 | # "columnN_name columnN_argvs" | ||
75 | # ) | ||
76 | sql_create_table() | ||
77 | { | ||
78 | local table | ||
79 | local count | ||
80 | local i | ||
81 | local line | ||
82 | local opts | ||
83 | local column | ||
84 | local autoincrement | ||
85 | local primary | ||
86 | local create | ||
87 | |||
88 | table="${TABLE_DEFINITION[0]}" | ||
89 | count="${#TABLE_DEFINITION[*]}" | ||
90 | |||
91 | for ((i=1; i<count; i++)) | ||
92 | do | ||
93 | niro | 8493 | line=( ${TABLE_DEFINITION[${i}]} ) |
94 | column="${line[0]}" | ||
95 | opts="${line[*]:1}" | ||
96 | niro | 8485 | |
97 | case ${opts} in | ||
98 | *"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;; | ||
99 | *"primary key"*) opts="${opts//primary key}"; primary="${column}" ;; | ||
100 | esac | ||
101 | |||
102 | case ${opts} in | ||
103 | *AUTO_INCREMENT*) opts="${opts//AUTO_INCREMENT}"; autoincrement="${column} ${opts}" ;; | ||
104 | *auto_increment*) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;; | ||
105 | esac | ||
106 | |||
107 | if [[ -z ${create} ]] | ||
108 | then | ||
109 | create="${column} ${opts}" | ||
110 | else | ||
111 | create+=", ${column} ${opts}" | ||
112 | fi | ||
113 | done | ||
114 | mysqldo "CREATE TABLE ${MCORE_SQL_DB}.${table} ( ${create} );" || die "creating table '${table}'" | ||
115 | |||
116 | if [[ -n ${primary} ]] | ||
117 | then | ||
118 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'" | ||
119 | fi | ||
120 | if [[ -n ${autoincrement} ]] | ||
121 | then | ||
122 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" || die "adding auto_increment to table '${table}'" | ||
123 | fi | ||
124 | } | ||
125 | |||
126 | niro | 8493 | # TABLE_DEFINITION=( |
127 | # "table_name" | ||
128 | niro | 8509 | # "column1_name column1_argvs" |
129 | # "column2_name column2_argvs" | ||
130 | # "columnN_name columnN_argvs" | ||
131 | # | ||
132 | # eg. "description varchar(255) DEFAULT NULL AFTER value" | ||
133 | niro | 8493 | # ) |
134 | niro | 8509 | # |
135 | niro | 8493 | sql_add_column() |
136 | { | ||
137 | local table | ||
138 | local count | ||
139 | local i | ||
140 | local line | ||
141 | local opts | ||
142 | local column | ||
143 | local autoincrement | ||
144 | local primary | ||
145 | niro | 8504 | local current_primary |
146 | local current_autoincrement | ||
147 | local current_opts | ||
148 | niro | 8493 | |
149 | table="${TABLE_DEFINITION[0]}" | ||
150 | count="${#TABLE_DEFINITION[*]}" | ||
151 | |||
152 | for ((i=1; i<count; i++)) | ||
153 | do | ||
154 | line=( ${TABLE_DEFINITION[${i}]} ) | ||
155 | column="${line[0]}" | ||
156 | niro | 8509 | opts="${line[*]:1}" |
157 | niro | 8493 | |
158 | case ${opts} in | ||
159 | *"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;; | ||
160 | *"primary key"*) opts="${opts//primary key}"; primary="${column}" ;; | ||
161 | esac | ||
162 | |||
163 | case ${opts} in | ||
164 | *AUTO_INCREMENT*) opts="${opts//AUTO_INCREMENT}"; autoincrement="${column} ${opts}" ;; | ||
165 | *auto_increment*) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;; | ||
166 | esac | ||
167 | |||
168 | niro | 8509 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD ${column} ${opts};" || die "add column '${table}'" |
169 | niro | 8493 | done |
170 | |||
171 | niro | 8504 | # delete an existing auto_increment first |
172 | if [[ -n ${autoincrement} ]] | ||
173 | then | ||
174 | i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Extra`="auto_increment"') ) | ||
175 | current_autoincrement="${i[0]}" | ||
176 | if [[ ${current_autoincrement} != ${column} ]] && [[ -n ${current_autoincrement} ]] | ||
177 | then | ||
178 | current_opts=( "${i[1]}" ) # type eg int(11) | ||
179 | # null or not | ||
180 | case "${i[2]}" in | ||
181 | NO|no) current_opts+=( "NOT NULL" ) ;; | ||
182 | YES|yes) current_opts+=( "DEFAULT ${i[4]}" ) ;; # should never match, but to be save | ||
183 | esac | ||
184 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} CHANGE ${current_autoincrement} ${current_autoincrement} ${current_opts};" || die "removing auto_increment to table '${table}'" | ||
185 | fi | ||
186 | fi | ||
187 | |||
188 | niro | 8493 | if [[ -n ${primary} ]] |
189 | then | ||
190 | niro | 8504 | # only run this if the primary key is not already the same column |
191 | i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Key`="PRI"') ) | ||
192 | current_primary="${i[0]}" | ||
193 | if [[ -n ${current_primary} ]] | ||
194 | then | ||
195 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} DROP PRIMARY KEY;" || die "adding primary key to table '${table}'" | ||
196 | fi | ||
197 | niro | 8493 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'" |
198 | fi | ||
199 | if [[ -n ${autoincrement} ]] | ||
200 | then | ||
201 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" || die "adding auto_increment to table '${table}'" | ||
202 | fi | ||
203 | } | ||
204 | |||
205 | # TABLE_DEFINITION=( | ||
206 | # "table_name" | ||
207 | # "column1_name column1_argvs" | ||
208 | # "column2_name column2_argvs" | ||
209 | # "columnN_name columnN_argvs" | ||
210 | # ) | ||
211 | sql_modify_column() | ||
212 | { | ||
213 | local table | ||
214 | local count | ||
215 | local i | ||
216 | local line | ||
217 | local opts | ||
218 | local column | ||
219 | local autoincrement | ||
220 | local primary | ||
221 | niro | 8501 | local current_primary |
222 | niro | 8504 | local current_autoincrement |
223 | local current_opts | ||
224 | niro | 8493 | |
225 | table="${TABLE_DEFINITION[0]}" | ||
226 | count="${#TABLE_DEFINITION[*]}" | ||
227 | |||
228 | for ((i=1; i<count; i++)) | ||
229 | do | ||
230 | line=( ${TABLE_DEFINITION[${i}]} ) | ||
231 | column="${line[0]}" | ||
232 | opts="${line[*]:1}" | ||
233 | |||
234 | case ${opts} in | ||
235 | *"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;; | ||
236 | *"primary key"*) opts="${opts//primary key}"; primary="${column}" ;; | ||
237 | esac | ||
238 | |||
239 | case ${opts} in | ||
240 | *AUTO_INCREMENT*) opts="${opts//AUTO_INCREMENT}"; autoincrement="${column} ${opts}" ;; | ||
241 | *auto_increment*) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;; | ||
242 | esac | ||
243 | |||
244 | niro | 8500 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${column} ${opts};" || die "modify table '${table}'" |
245 | niro | 8493 | done |
246 | |||
247 | niro | 8504 | # delete an existing auto_increment first |
248 | if [[ -n ${autoincrement} ]] | ||
249 | then | ||
250 | i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Extra`="auto_increment"') ) | ||
251 | echo "DEBUG: i='${i[*]}'" | ||
252 | current_autoincrement="${i[0]}" | ||
253 | if [[ ${current_autoincrement} != ${column} ]] && [[ -n ${current_autoincrement} ]] | ||
254 | then | ||
255 | current_opts=( "${i[1]}" ) # type eg int(11) | ||
256 | # null or not | ||
257 | case "${i[2]}" in | ||
258 | NO|no) current_opts+=( "NOT NULL" ) ;; | ||
259 | YES|yes) current_opts+=( "DEFAULT ${i[4]}" ) ;; # should never match, but to be save | ||
260 | esac | ||
261 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} CHANGE ${current_autoincrement} ${current_autoincrement} ${current_opts};" || die "removing auto_increment to table '${table}'" | ||
262 | fi | ||
263 | fi | ||
264 | |||
265 | niro | 8493 | if [[ -n ${primary} ]] |
266 | then | ||
267 | niro | 8501 | # only run this if the primary key is not already the same column |
268 | niro | 8502 | i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Key`="PRI"') ) |
269 | niro | 8501 | current_primary="${i[0]}" |
270 | niro | 8504 | if [[ -n ${current_primary} ]] |
271 | niro | 8501 | then |
272 | niro | 8504 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} DROP PRIMARY KEY;" || die "adding primary key to table '${table}'" |
273 | niro | 8501 | fi |
274 | niro | 8504 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'" |
275 | niro | 8493 | fi |
276 | if [[ -n ${autoincrement} ]] | ||
277 | then | ||
278 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" || die "adding auto_increment to table '${table}'" | ||
279 | fi | ||
280 | } | ||
281 | |||
282 | niro | 8485 | # VALUE_DEFINITION=( |
283 | # "table_name, value_column_name, bool_column_name, id_column_name" | ||
284 | # "value1, bool1" | ||
285 | # "value2, bool2" | ||
286 | # "valueN, boolN" | ||
287 | # ) | ||
288 | sql_set_value() | ||
289 | { | ||
290 | local line | ||
291 | local meta | ||
292 | local table | ||
293 | local column_value | ||
294 | local column_bool | ||
295 | local column_id | ||
296 | local value | ||
297 | local bool | ||
298 | local count | ||
299 | local i | ||
300 | local id | ||
301 | |||
302 | count="${#VALUE_DEFINITION[*]}" | ||
303 | line="${VALUE_DEFINITION[0]}" | ||
304 | for i in ${line//,/ } | ||
305 | do | ||
306 | meta+=( "${i}" ) | ||
307 | done | ||
308 | |||
309 | table="${meta[0]}" | ||
310 | column_value="${meta[1]}" | ||
311 | column_bool="${meta[2]}" | ||
312 | column_id="${meta[3]}" | ||
313 | |||
314 | for ((i=1; i<count; i++)) | ||
315 | do | ||
316 | line="${VALUE_DEFINITION[${i}]}" | ||
317 | value="${line%%,*}" | ||
318 | bool="${line##*,}" | ||
319 | # remove any leading whitespaces | ||
320 | bool="${bool// }" | ||
321 | |||
322 | # now check if the value already exist in the database | ||
323 | id=$(mysqldo "SELECT ${column_id} FROM ${MCORE_SQL_DB}.${table} WHERE ${column_value}='${value}';") | ||
324 | if [[ -n ${id} ]] | ||
325 | then | ||
326 | # update value in database | ||
327 | mysqldo "UPDATE ${MCORE_SQL_DB}.${table} SET ${column_value}='${value}', ${column_bool}='${bool}' WHERE ${column_id}='${id}';" || die "update value in table ${table}" | ||
328 | else | ||
329 | # insert into the database | ||
330 | mysqldo "INSERT INTO ${MCORE_SQL_DB}.${table} (${column_value}, ${column_bool}) VALUES ( '${value}', '${bool}' );" || die "insert value into table ${table}" | ||
331 | fi | ||
332 | done | ||
333 | } | ||
334 | |||
335 | create_database() | ||
336 | { | ||
337 | create_database_schema | ||
338 | niro | 8495 | |
339 | niro | 8498 | # update database schema version |
340 | mysqldo "INSERT INTO ${MCORE_SQL_DB}.meta_schema(version) VALUES ('${MCORE_SQL_DB_SCHEMA_VERSION}') ON DUPLICATE KEY UPDATE version='${MCORE_SQL_DB_SCHEMA_VERSION}';" || die "updating schema version" | ||
341 | niro | 8485 | } |
342 | |||
343 | update_database() | ||
344 | { | ||
345 | niro | 8497 | local current_sql_db_schema_version |
346 | niro | 8485 | local i |
347 | |||
348 | niro | 8498 | current_sql_db_schema_version=$(mysqldo "SELECT MAX(version) from meta_schema;") |
349 | niro | 8485 | |
350 | niro | 8497 | if [[ ${current_sql_db_schema_version} = ${MCORE_SQL_DB_SCHEMA_VERSION} ]] |
351 | niro | 8485 | then |
352 | niro | 8503 | echo "Database schema is already up to date" |
353 | niro | 8485 | return 0 |
354 | fi | ||
355 | |||
356 | niro | 8496 | # run every update_to_database_version_X script which exist up to version MCORE_SQL_DB_SCHEMA_VERSION |
357 | niro | 8497 | for i in $(seq $((${current_sql_db_schema_version}+1)) ${MCORE_SQL_DB_SCHEMA_VERSION}) |
358 | niro | 8485 | do |
359 | niro | 8495 | if [[ -n $(typeset -f update_to_database_schema_version_${i}) ]] |
360 | niro | 8485 | then |
361 | echo "Running update_to_database_schema_version_${i}()" | ||
362 | niro | 8495 | update_to_database_schema_version_${i} || die |
363 | niro | 8485 | fi |
364 | done | ||
365 | niro | 8495 | |
366 | niro | 8498 | # update database schema version |
367 | mysqldo "INSERT INTO ${MCORE_SQL_DB}.meta_schema(version) VALUES ('${MCORE_SQL_DB_SCHEMA_VERSION}') ON DUPLICATE KEY UPDATE version='${MCORE_SQL_DB_SCHEMA_VERSION}';" || die "updating schema version" | ||
368 | niro | 8485 | } |
369 | |||
370 | fill_database() | ||
371 | { | ||
372 | niro | 8498 | local current_sql_db_values_version |
373 | local i | ||
374 | |||
375 | current_sql_db_values_version=$(mysqldo "SELECT MAX(version) from meta_values;") | ||
376 | |||
377 | if [[ ${current_sql_db_values_version} = ${MCORE_SQL_DB_VALUES_VERSION} ]] | ||
378 | then | ||
379 | echo "Database values are already up to date" | ||
380 | return 0 | ||
381 | fi | ||
382 | |||
383 | niro | 8485 | create_database_values |
384 | niro | 8498 | # update database values version |
385 | mysqldo "INSERT INTO ${MCORE_SQL_DB}.meta_values(version) VALUES ('${MCORE_SQL_DB_VALUES_VERSION}') ON DUPLICATE KEY UPDATE version='${MCORE_SQL_DB_VALUES_VERSION}';" || die "updating values version" | ||
386 | niro | 8485 | } |
387 | |||
388 | case $1 in | ||
389 | create) | ||
390 | create_database | ||
391 | fill_database | ||
392 | ;; | ||
393 | |||
394 | update) | ||
395 | update_database | ||
396 | fill_database | ||
397 | ;; | ||
398 | esac |