Contents of /alx-src/branches/alx-web-070/scripts/sql-schema/mcore-sql-schema.sh
Parent Directory | Revision Log
Revision 8504 -
(show annotations)
(download)
(as text)
Fri Feb 5 15:14:21 2016 UTC (8 years, 4 months ago) by niro
File MIME type: application/x-sh
File size: 10704 byte(s)
Fri Feb 5 15:14:21 2016 UTC (8 years, 4 months ago) by niro
File MIME type: application/x-sh
File size: 10704 byte(s)
-serveral primary key and auto_increment handling fixes
1 | #!/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 | line=( ${TABLE_DEFINITION[${i}]} ) |
94 | column="${line[0]}" |
95 | opts="${line[*]:1}" |
96 | |
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 | # TABLE_DEFINITION=( |
127 | # "table_name" |
128 | # "column1_name after_column column1_argvs" |
129 | # "column2_name after_column column2_argvs" |
130 | # "columnN_name after_column columnN_argvs" |
131 | # ) |
132 | sql_add_column() |
133 | { |
134 | local table |
135 | local count |
136 | local i |
137 | local line |
138 | local opts |
139 | local column |
140 | local autoincrement |
141 | local primary |
142 | local current_primary |
143 | local current_autoincrement |
144 | local current_opts |
145 | |
146 | table="${TABLE_DEFINITION[0]}" |
147 | count="${#TABLE_DEFINITION[*]}" |
148 | |
149 | for ((i=1; i<count; i++)) |
150 | do |
151 | line=( ${TABLE_DEFINITION[${i}]} ) |
152 | column="${line[0]}" |
153 | after="${line[1]}" |
154 | opts="${line[*]:2}" |
155 | |
156 | case ${opts} in |
157 | *"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;; |
158 | *"primary key"*) opts="${opts//primary key}"; primary="${column}" ;; |
159 | esac |
160 | |
161 | case ${opts} in |
162 | *AUTO_INCREMENT*) opts="${opts//AUTO_INCREMENT}"; autoincrement="${column} ${opts}" ;; |
163 | *auto_increment*) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;; |
164 | esac |
165 | |
166 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD ${column} ${opts} AFTER ${after_column};" || die "add column '${table}'" |
167 | done |
168 | |
169 | # delete an existing auto_increment first |
170 | if [[ -n ${autoincrement} ]] |
171 | then |
172 | i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Extra`="auto_increment"') ) |
173 | echo "DEBUG: i='${i[*]}'" |
174 | current_autoincrement="${i[0]}" |
175 | if [[ ${current_autoincrement} != ${column} ]] && [[ -n ${current_autoincrement} ]] |
176 | then |
177 | current_opts=( "${i[1]}" ) # type eg int(11) |
178 | # null or not |
179 | case "${i[2]}" in |
180 | NO|no) current_opts+=( "NOT NULL" ) ;; |
181 | YES|yes) current_opts+=( "DEFAULT ${i[4]}" ) ;; # should never match, but to be save |
182 | esac |
183 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} CHANGE ${current_autoincrement} ${current_autoincrement} ${current_opts};" || die "removing auto_increment to table '${table}'" |
184 | fi |
185 | fi |
186 | |
187 | if [[ -n ${primary} ]] |
188 | then |
189 | # only run this if the primary key is not already the same column |
190 | i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Key`="PRI"') ) |
191 | current_primary="${i[0]}" |
192 | if [[ -n ${current_primary} ]] |
193 | then |
194 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} DROP PRIMARY KEY;" || die "adding primary key to table '${table}'" |
195 | fi |
196 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'" |
197 | fi |
198 | if [[ -n ${autoincrement} ]] |
199 | then |
200 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" || die "adding auto_increment to table '${table}'" |
201 | fi |
202 | } |
203 | |
204 | # TABLE_DEFINITION=( |
205 | # "table_name" |
206 | # "column1_name column1_argvs" |
207 | # "column2_name column2_argvs" |
208 | # "columnN_name columnN_argvs" |
209 | # ) |
210 | sql_modify_column() |
211 | { |
212 | local table |
213 | local count |
214 | local i |
215 | local line |
216 | local opts |
217 | local column |
218 | local autoincrement |
219 | local primary |
220 | local current_primary |
221 | local current_autoincrement |
222 | local current_opts |
223 | |
224 | table="${TABLE_DEFINITION[0]}" |
225 | count="${#TABLE_DEFINITION[*]}" |
226 | |
227 | for ((i=1; i<count; i++)) |
228 | do |
229 | line=( ${TABLE_DEFINITION[${i}]} ) |
230 | column="${line[0]}" |
231 | opts="${line[*]:1}" |
232 | |
233 | case ${opts} in |
234 | *"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;; |
235 | *"primary key"*) opts="${opts//primary key}"; primary="${column}" ;; |
236 | esac |
237 | |
238 | case ${opts} in |
239 | *AUTO_INCREMENT*) opts="${opts//AUTO_INCREMENT}"; autoincrement="${column} ${opts}" ;; |
240 | *auto_increment*) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;; |
241 | esac |
242 | |
243 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${column} ${opts};" || die "modify table '${table}'" |
244 | done |
245 | |
246 | # delete an existing auto_increment first |
247 | if [[ -n ${autoincrement} ]] |
248 | then |
249 | i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Extra`="auto_increment"') ) |
250 | echo "DEBUG: i='${i[*]}'" |
251 | current_autoincrement="${i[0]}" |
252 | if [[ ${current_autoincrement} != ${column} ]] && [[ -n ${current_autoincrement} ]] |
253 | then |
254 | current_opts=( "${i[1]}" ) # type eg int(11) |
255 | # null or not |
256 | case "${i[2]}" in |
257 | NO|no) current_opts+=( "NOT NULL" ) ;; |
258 | YES|yes) current_opts+=( "DEFAULT ${i[4]}" ) ;; # should never match, but to be save |
259 | esac |
260 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} CHANGE ${current_autoincrement} ${current_autoincrement} ${current_opts};" || die "removing auto_increment to table '${table}'" |
261 | fi |
262 | fi |
263 | |
264 | if [[ -n ${primary} ]] |
265 | then |
266 | # only run this if the primary key is not already the same column |
267 | i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Key`="PRI"') ) |
268 | current_primary="${i[0]}" |
269 | if [[ -n ${current_primary} ]] |
270 | then |
271 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} DROP PRIMARY KEY;" || die "adding primary key to table '${table}'" |
272 | fi |
273 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'" |
274 | fi |
275 | if [[ -n ${autoincrement} ]] |
276 | then |
277 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" || die "adding auto_increment to table '${table}'" |
278 | fi |
279 | } |
280 | |
281 | # VALUE_DEFINITION=( |
282 | # "table_name, value_column_name, bool_column_name, id_column_name" |
283 | # "value1, bool1" |
284 | # "value2, bool2" |
285 | # "valueN, boolN" |
286 | # ) |
287 | sql_set_value() |
288 | { |
289 | local line |
290 | local meta |
291 | local table |
292 | local column_value |
293 | local column_bool |
294 | local column_id |
295 | local value |
296 | local bool |
297 | local count |
298 | local i |
299 | local id |
300 | |
301 | count="${#VALUE_DEFINITION[*]}" |
302 | line="${VALUE_DEFINITION[0]}" |
303 | for i in ${line//,/ } |
304 | do |
305 | meta+=( "${i}" ) |
306 | done |
307 | |
308 | table="${meta[0]}" |
309 | column_value="${meta[1]}" |
310 | column_bool="${meta[2]}" |
311 | column_id="${meta[3]}" |
312 | |
313 | for ((i=1; i<count; i++)) |
314 | do |
315 | line="${VALUE_DEFINITION[${i}]}" |
316 | value="${line%%,*}" |
317 | bool="${line##*,}" |
318 | # remove any leading whitespaces |
319 | bool="${bool// }" |
320 | |
321 | # now check if the value already exist in the database |
322 | id=$(mysqldo "SELECT ${column_id} FROM ${MCORE_SQL_DB}.${table} WHERE ${column_value}='${value}';") |
323 | if [[ -n ${id} ]] |
324 | then |
325 | # update value in database |
326 | mysqldo "UPDATE ${MCORE_SQL_DB}.${table} SET ${column_value}='${value}', ${column_bool}='${bool}' WHERE ${column_id}='${id}';" || die "update value in table ${table}" |
327 | else |
328 | # insert into the database |
329 | mysqldo "INSERT INTO ${MCORE_SQL_DB}.${table} (${column_value}, ${column_bool}) VALUES ( '${value}', '${bool}' );" || die "insert value into table ${table}" |
330 | fi |
331 | done |
332 | } |
333 | |
334 | create_database() |
335 | { |
336 | create_database_schema |
337 | |
338 | # update database schema version |
339 | 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" |
340 | } |
341 | |
342 | update_database() |
343 | { |
344 | local current_sql_db_schema_version |
345 | local i |
346 | |
347 | current_sql_db_schema_version=$(mysqldo "SELECT MAX(version) from meta_schema;") |
348 | |
349 | if [[ ${current_sql_db_schema_version} = ${MCORE_SQL_DB_SCHEMA_VERSION} ]] |
350 | then |
351 | echo "Database schema is already up to date" |
352 | return 0 |
353 | fi |
354 | |
355 | # run every update_to_database_version_X script which exist up to version MCORE_SQL_DB_SCHEMA_VERSION |
356 | for i in $(seq $((${current_sql_db_schema_version}+1)) ${MCORE_SQL_DB_SCHEMA_VERSION}) |
357 | do |
358 | if [[ -n $(typeset -f update_to_database_schema_version_${i}) ]] |
359 | then |
360 | echo "Running update_to_database_schema_version_${i}()" |
361 | update_to_database_schema_version_${i} || die |
362 | fi |
363 | done |
364 | |
365 | # update database schema version |
366 | 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" |
367 | } |
368 | |
369 | fill_database() |
370 | { |
371 | local current_sql_db_values_version |
372 | local i |
373 | |
374 | current_sql_db_values_version=$(mysqldo "SELECT MAX(version) from meta_values;") |
375 | |
376 | if [[ ${current_sql_db_values_version} = ${MCORE_SQL_DB_VALUES_VERSION} ]] |
377 | then |
378 | echo "Database values are already up to date" |
379 | return 0 |
380 | fi |
381 | |
382 | create_database_values |
383 | # update database values version |
384 | 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" |
385 | } |
386 | |
387 | case $1 in |
388 | create) |
389 | create_database |
390 | fill_database |
391 | ;; |
392 | |
393 | update) |
394 | update_database |
395 | fill_database |
396 | ;; |
397 | esac |