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