Contents of /alx-src/branches/alx-web-070/scripts/sql-schema/mcore-sql-schema.sh
Parent Directory | Revision Log
Revision 9465 -
(show 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 | #!/bin/bash |
2 | |
3 | # 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 | # 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 | line=( ${TABLE_DEFINITION[${i}]} ) |
100 | column="${line[0]}" |
101 | opts="${line[*]:1}" |
102 | |
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 | # TABLE_DEFINITION=( |
133 | # "table_name" |
134 | # "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 | # ) |
140 | # |
141 | 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 | local current_primary |
152 | local current_autoincrement |
153 | local current_opts |
154 | |
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 | opts="${line[*]:1}" |
163 | |
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 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD ${column} ${opts};" || die "add column '${table}'" |
175 | done |
176 | |
177 | # 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 | if [[ -n ${primary} ]] |
195 | then |
196 | # 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 | 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 | local current_primary |
228 | local current_autoincrement |
229 | local current_opts |
230 | |
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 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${column} ${opts};" || die "modify table '${table}'" |
251 | done |
252 | |
253 | # 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 | if [[ -n ${primary} ]] |
271 | then |
272 | # only run this if the primary key is not already the same column |
273 | i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Key`="PRI"') ) |
274 | current_primary="${i[0]}" |
275 | if [[ -n ${current_primary} ]] |
276 | then |
277 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} DROP PRIMARY KEY;" || die "adding primary key to table '${table}'" |
278 | fi |
279 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'" |
280 | 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 | # 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 | echo "Creating database schema version ${MCORE_SQL_DB_SCHEMA_VERSION}" |
343 | create_database_schema |
344 | |
345 | # 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 | } |
348 | |
349 | update_database() |
350 | { |
351 | local current_sql_db_schema_version |
352 | local i |
353 | |
354 | current_sql_db_schema_version=$(mysqldo "SELECT MAX(version) from meta_schema;") |
355 | |
356 | if [[ ${current_sql_db_schema_version} = ${MCORE_SQL_DB_SCHEMA_VERSION} ]] |
357 | then |
358 | echo "Database schema is already up to date" |
359 | return 0 |
360 | fi |
361 | |
362 | # run every update_to_database_version_X script which exist up to version MCORE_SQL_DB_SCHEMA_VERSION |
363 | for i in $(seq $((${current_sql_db_schema_version}+1)) ${MCORE_SQL_DB_SCHEMA_VERSION}) |
364 | do |
365 | if [[ -n $(typeset -f update_to_database_schema_version_${i}) ]] |
366 | then |
367 | echo "Running update_to_database_schema_version_${i}()" |
368 | update_to_database_schema_version_${i} || die |
369 | fi |
370 | done |
371 | |
372 | # 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 | } |
375 | |
376 | fill_database() |
377 | { |
378 | 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 | echo "Updating database default values to version ${MCORE_SQL_DB_VALUES_VERSION}" |
390 | create_database_values |
391 | # 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 | } |
394 | |
395 | 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 | case $1 in |
404 | create) |
405 | create_database |
406 | fill_database |
407 | ;; |
408 | |
409 | update) |
410 | update_database |
411 | fill_database |
412 | ;; |
413 | |
414 | *) usage ;; |
415 | esac |
Properties
Name | Value |
---|---|
svn:executable | * |