Annotation of /alx-src/branches/alx-web-070/scripts/sql-schema/mcore-sql-schema.sh
Parent Directory
| 
Revision Log
Revision 8501 -
(hide annotations)
(download)
(as text)
Fri Feb 5 14:31:53 2016 UTC (8 years, 3 months ago) by niro
File MIME type: application/x-sh
File size: 8641 byte(s)
Fri Feb 5 14:31:53 2016 UTC (8 years, 3 months ago) by niro
File MIME type: application/x-sh
File size: 8641 byte(s)
-only modify the primary key if it is not the same column
| 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 | # "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 | |||
| 143 | table="${TABLE_DEFINITION[0]}" | ||
| 144 | count="${#TABLE_DEFINITION[*]}" | ||
| 145 | |||
| 146 | for ((i=1; i<count; i++)) | ||
| 147 | do | ||
| 148 | line=( ${TABLE_DEFINITION[${i}]} ) | ||
| 149 | column="${line[0]}" | ||
| 150 | after="${line[1]}" | ||
| 151 | opts="${line[*]:2}" | ||
| 152 | |||
| 153 | case ${opts} in | ||
| 154 | *"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;; | ||
| 155 | *"primary key"*) opts="${opts//primary key}"; primary="${column}" ;; | ||
| 156 | esac | ||
| 157 | |||
| 158 | case ${opts} in | ||
| 159 | *AUTO_INCREMENT*) opts="${opts//AUTO_INCREMENT}"; autoincrement="${column} ${opts}" ;; | ||
| 160 | *auto_increment*) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;; | ||
| 161 | esac | ||
| 162 | |||
| 163 | niro | 8500 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD ${column} ${opts} AFTER ${after_column};" || die "add column '${table}'" |
| 164 | niro | 8493 | done |
| 165 | |||
| 166 | if [[ -n ${primary} ]] | ||
| 167 | then | ||
| 168 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'" | ||
| 169 | fi | ||
| 170 | if [[ -n ${autoincrement} ]] | ||
| 171 | then | ||
| 172 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" || die "adding auto_increment to table '${table}'" | ||
| 173 | fi | ||
| 174 | } | ||
| 175 | |||
| 176 | # TABLE_DEFINITION=( | ||
| 177 | # "table_name" | ||
| 178 | # "column1_name column1_argvs" | ||
| 179 | # "column2_name column2_argvs" | ||
| 180 | # "columnN_name columnN_argvs" | ||
| 181 | # ) | ||
| 182 | sql_modify_column() | ||
| 183 | { | ||
| 184 | local table | ||
| 185 | local count | ||
| 186 | local i | ||
| 187 | local line | ||
| 188 | local opts | ||
| 189 | local column | ||
| 190 | local autoincrement | ||
| 191 | local primary | ||
| 192 | niro | 8501 | local current_primary |
| 193 | niro | 8493 | |
| 194 | table="${TABLE_DEFINITION[0]}" | ||
| 195 | count="${#TABLE_DEFINITION[*]}" | ||
| 196 | |||
| 197 | for ((i=1; i<count; i++)) | ||
| 198 | do | ||
| 199 | line=( ${TABLE_DEFINITION[${i}]} ) | ||
| 200 | column="${line[0]}" | ||
| 201 | opts="${line[*]:1}" | ||
| 202 | |||
| 203 | case ${opts} in | ||
| 204 | *"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;; | ||
| 205 | *"primary key"*) opts="${opts//primary key}"; primary="${column}" ;; | ||
| 206 | esac | ||
| 207 | |||
| 208 | case ${opts} in | ||
| 209 | *AUTO_INCREMENT*) opts="${opts//AUTO_INCREMENT}"; autoincrement="${column} ${opts}" ;; | ||
| 210 | *auto_increment*) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;; | ||
| 211 | esac | ||
| 212 | |||
| 213 | niro | 8500 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${column} ${opts};" || die "modify table '${table}'" |
| 214 | niro | 8493 | done |
| 215 | |||
| 216 | if [[ -n ${primary} ]] | ||
| 217 | then | ||
| 218 | niro | 8501 | # only run this if the primary key is not already the same column |
| 219 | i=( $(mysqldo 'SHOW COLUMNS FROM ${MCORE_SQL_DB}.${table} WHERE `Key`="PRI"') ) | ||
| 220 | current_primary="${i[0]}" | ||
| 221 | if [[ ${current_primary} != ${primary} ]] | ||
| 222 | then | ||
| 223 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'" | ||
| 224 | fi | ||
| 225 | niro | 8493 | fi |
| 226 | if [[ -n ${autoincrement} ]] | ||
| 227 | then | ||
| 228 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" || die "adding auto_increment to table '${table}'" | ||
| 229 | fi | ||
| 230 | } | ||
| 231 | |||
| 232 | niro | 8485 | # VALUE_DEFINITION=( |
| 233 | # "table_name, value_column_name, bool_column_name, id_column_name" | ||
| 234 | # "value1, bool1" | ||
| 235 | # "value2, bool2" | ||
| 236 | # "valueN, boolN" | ||
| 237 | # ) | ||
| 238 | sql_set_value() | ||
| 239 | { | ||
| 240 | local line | ||
| 241 | local meta | ||
| 242 | local table | ||
| 243 | local column_value | ||
| 244 | local column_bool | ||
| 245 | local column_id | ||
| 246 | local value | ||
| 247 | local bool | ||
| 248 | local count | ||
| 249 | local i | ||
| 250 | local id | ||
| 251 | |||
| 252 | count="${#VALUE_DEFINITION[*]}" | ||
| 253 | line="${VALUE_DEFINITION[0]}" | ||
| 254 | for i in ${line//,/ } | ||
| 255 | do | ||
| 256 | meta+=( "${i}" ) | ||
| 257 | done | ||
| 258 | |||
| 259 | table="${meta[0]}" | ||
| 260 | column_value="${meta[1]}" | ||
| 261 | column_bool="${meta[2]}" | ||
| 262 | column_id="${meta[3]}" | ||
| 263 | |||
| 264 | for ((i=1; i<count; i++)) | ||
| 265 | do | ||
| 266 | line="${VALUE_DEFINITION[${i}]}" | ||
| 267 | value="${line%%,*}" | ||
| 268 | bool="${line##*,}" | ||
| 269 | # remove any leading whitespaces | ||
| 270 | bool="${bool// }" | ||
| 271 | |||
| 272 | # now check if the value already exist in the database | ||
| 273 | id=$(mysqldo "SELECT ${column_id} FROM ${MCORE_SQL_DB}.${table} WHERE ${column_value}='${value}';") | ||
| 274 | if [[ -n ${id} ]] | ||
| 275 | then | ||
| 276 | # update value in database | ||
| 277 | mysqldo "UPDATE ${MCORE_SQL_DB}.${table} SET ${column_value}='${value}', ${column_bool}='${bool}' WHERE ${column_id}='${id}';" || die "update value in table ${table}" | ||
| 278 | else | ||
| 279 | # insert into the database | ||
| 280 | mysqldo "INSERT INTO ${MCORE_SQL_DB}.${table} (${column_value}, ${column_bool}) VALUES ( '${value}', '${bool}' );" || die "insert value into table ${table}" | ||
| 281 | fi | ||
| 282 | done | ||
| 283 | } | ||
| 284 | |||
| 285 | create_database() | ||
| 286 | { | ||
| 287 | create_database_schema | ||
| 288 | niro | 8495 | |
| 289 | niro | 8498 | # update database schema version |
| 290 | 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" | ||
| 291 | niro | 8485 | } |
| 292 | |||
| 293 | update_database() | ||
| 294 | { | ||
| 295 | niro | 8497 | local current_sql_db_schema_version |
| 296 | niro | 8485 | local i |
| 297 | |||
| 298 | niro | 8498 | current_sql_db_schema_version=$(mysqldo "SELECT MAX(version) from meta_schema;") |
| 299 | niro | 8485 | |
| 300 | niro | 8497 | if [[ ${current_sql_db_schema_version} = ${MCORE_SQL_DB_SCHEMA_VERSION} ]] |
| 301 | niro | 8485 | then |
| 302 | echo "Database is already up to date" | ||
| 303 | return 0 | ||
| 304 | fi | ||
| 305 | |||
| 306 | niro | 8496 | # run every update_to_database_version_X script which exist up to version MCORE_SQL_DB_SCHEMA_VERSION |
| 307 | niro | 8497 | for i in $(seq $((${current_sql_db_schema_version}+1)) ${MCORE_SQL_DB_SCHEMA_VERSION}) |
| 308 | niro | 8485 | do |
| 309 | niro | 8495 | if [[ -n $(typeset -f update_to_database_schema_version_${i}) ]] |
| 310 | niro | 8485 | then |
| 311 | echo "Running update_to_database_schema_version_${i}()" | ||
| 312 | niro | 8495 | update_to_database_schema_version_${i} || die |
| 313 | niro | 8485 | fi |
| 314 | done | ||
| 315 | niro | 8495 | |
| 316 | niro | 8498 | # update database schema version |
| 317 | 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" | ||
| 318 | niro | 8485 | } |
| 319 | |||
| 320 | fill_database() | ||
| 321 | { | ||
| 322 | niro | 8498 | local current_sql_db_values_version |
| 323 | local i | ||
| 324 | |||
| 325 | current_sql_db_values_version=$(mysqldo "SELECT MAX(version) from meta_values;") | ||
| 326 | |||
| 327 | if [[ ${current_sql_db_values_version} = ${MCORE_SQL_DB_VALUES_VERSION} ]] | ||
| 328 | then | ||
| 329 | echo "Database values are already up to date" | ||
| 330 | return 0 | ||
| 331 | fi | ||
| 332 | |||
| 333 | niro | 8485 | create_database_values |
| 334 | niro | 8498 | # update database values version |
| 335 | 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" | ||
| 336 | niro | 8485 | } |
| 337 | |||
| 338 | case $1 in | ||
| 339 | create) | ||
| 340 | create_database | ||
| 341 | fill_database | ||
| 342 | ;; | ||
| 343 | |||
| 344 | update) | ||
| 345 | update_database | ||
| 346 | fill_database | ||
| 347 | ;; | ||
| 348 | esac |