Contents of /alx-src/branches/alx-web-070/scripts/sql-schema/mcore-sql-schema.sh
Parent Directory
| 
Revision Log
Revision 8552 -
(show annotations)
(download)
(as text)
Mon Feb 8 13:33:18 2016 UTC (8 years, 2 months ago) by niro
File MIME type: application/x-sh
File size: 10915 byte(s)
Mon Feb 8 13:33:18 2016 UTC (8 years, 2 months ago) by niro
File MIME type: application/x-sh
File size: 10915 byte(s)
-added mcore-sql-schema.conf
| 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 | case $1 in |
| 396 | create) |
| 397 | create_database |
| 398 | fill_database |
| 399 | ;; |
| 400 | |
| 401 | update) |
| 402 | update_database |
| 403 | fill_database |
| 404 | ;; |
| 405 | esac |
Properties
| Name | Value |
|---|---|
| svn:executable | * |