Annotation of /alx-src/branches/alx-web-070/scripts/sql-schema/mcore-sql-schema.sh
Parent Directory | Revision Log
Revision 8485 -
(hide annotations)
(download)
(as text)
Fri Feb 5 12:06:33 2016 UTC (8 years, 3 months ago) by niro
File MIME type: application/x-sh
File size: 6148 byte(s)
Fri Feb 5 12:06:33 2016 UTC (8 years, 3 months ago) by niro
File MIME type: application/x-sh
File size: 6148 byte(s)
-added initial schema script
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 | mysql_insert() | ||
68 | { | ||
69 | local cmdline="$@" | ||
70 | #local table="$1" | ||
71 | local table="${cmdline%%,*}" | ||
72 | local argv | ||
73 | local insert_line | ||
74 | local update_line | ||
75 | local key | ||
76 | local value | ||
77 | local counter | ||
78 | |||
79 | OLD_IFS=${IFS} | ||
80 | IFS=, | ||
81 | declare -i counter=0 | ||
82 | #for argv in ${cmdline/$1/} # first value is the table_name | ||
83 | for argv in ${cmdline#*,} # first value is the table_name | ||
84 | do | ||
85 | key="${argv%=*}" | ||
86 | value="${argv#*=}" | ||
87 | |||
88 | if [ ${counter} -eq 0 ] | ||
89 | then | ||
90 | insert_keys="${key}" | ||
91 | insert_values="'${value}'" | ||
92 | update_line="${key}='${value}'" | ||
93 | else | ||
94 | insert_keys+=",${key}" | ||
95 | insert_values+=",'${value}'" | ||
96 | update_line+=",${key}='${value}'" | ||
97 | fi | ||
98 | (( counter ++ )) | ||
99 | done | ||
100 | IFS=${OLD_IFS} | ||
101 | |||
102 | echo "INSERT INTO ${table} (${insert_keys}) VALUES(${insert_values}) ON DUPLICATE KEY UPDATE ${update_line};" | ||
103 | #mysqldo "insert into ${table} (${insert_keys}) values(${insert_values}) on duplicate key update ${update_line};" | ||
104 | } | ||
105 | |||
106 | |||
107 | # creates a mysql table with help of a defined array TABLE_DEFINITION: | ||
108 | # array values are a struct like this: | ||
109 | # first array element is the table name, everything else are column descriptions | ||
110 | # TABLE_DEFINITION=( | ||
111 | # "table_name" | ||
112 | # "column1_name column1_argvs" | ||
113 | # "column2_name column2_argvs" | ||
114 | # "columnN_name columnN_argvs" | ||
115 | # ) | ||
116 | sql_create_table() | ||
117 | { | ||
118 | local table | ||
119 | local count | ||
120 | local i | ||
121 | local line | ||
122 | local opts | ||
123 | local column | ||
124 | local autoincrement | ||
125 | local primary | ||
126 | local create | ||
127 | |||
128 | table="${TABLE_DEFINITION[0]}" | ||
129 | count="${#TABLE_DEFINITION[*]}" | ||
130 | |||
131 | for ((i=1; i<count; i++)) | ||
132 | do | ||
133 | line="${TABLE_DEFINITION[${i}]}" | ||
134 | column="${line%% *}" | ||
135 | opts="${line/${column} /}" | ||
136 | |||
137 | case ${opts} in | ||
138 | *"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;; | ||
139 | *"primary key"*) opts="${opts//primary key}"; primary="${column}" ;; | ||
140 | esac | ||
141 | |||
142 | case ${opts} in | ||
143 | *AUTO_INCREMENT*) opts="${opts//AUTO_INCREMENT}"; autoincrement="${column} ${opts}" ;; | ||
144 | *auto_increment*) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;; | ||
145 | esac | ||
146 | |||
147 | if [[ -z ${create} ]] | ||
148 | then | ||
149 | create="${column} ${opts}" | ||
150 | else | ||
151 | create+=", ${column} ${opts}" | ||
152 | fi | ||
153 | done | ||
154 | echo "CREATE TABLE ${MCORE_SQL_DB}.${table} ( ${create} );" | ||
155 | mysqldo "CREATE TABLE ${MCORE_SQL_DB}.${table} ( ${create} );" || die "creating table '${table}'" | ||
156 | |||
157 | if [[ -n ${primary} ]] | ||
158 | then | ||
159 | echo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" | ||
160 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'" | ||
161 | fi | ||
162 | if [[ -n ${autoincrement} ]] | ||
163 | then | ||
164 | echo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" | ||
165 | mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" || die "adding auto_increment to table '${table}'" | ||
166 | fi | ||
167 | } | ||
168 | |||
169 | # VALUE_DEFINITION=( | ||
170 | # "table_name, value_column_name, bool_column_name, id_column_name" | ||
171 | # "value1, bool1" | ||
172 | # "value2, bool2" | ||
173 | # "valueN, boolN" | ||
174 | # ) | ||
175 | sql_set_value() | ||
176 | { | ||
177 | local line | ||
178 | local meta | ||
179 | local table | ||
180 | local column_value | ||
181 | local column_bool | ||
182 | local column_id | ||
183 | local value | ||
184 | local bool | ||
185 | local count | ||
186 | local i | ||
187 | local id | ||
188 | |||
189 | count="${#VALUE_DEFINITION[*]}" | ||
190 | line="${VALUE_DEFINITION[0]}" | ||
191 | for i in ${line//,/ } | ||
192 | do | ||
193 | meta+=( "${i}" ) | ||
194 | done | ||
195 | |||
196 | table="${meta[0]}" | ||
197 | column_value="${meta[1]}" | ||
198 | column_bool="${meta[2]}" | ||
199 | column_id="${meta[3]}" | ||
200 | |||
201 | for ((i=1; i<count; i++)) | ||
202 | do | ||
203 | line="${VALUE_DEFINITION[${i}]}" | ||
204 | value="${line%%,*}" | ||
205 | bool="${line##*,}" | ||
206 | # remove any leading whitespaces | ||
207 | bool="${bool// }" | ||
208 | |||
209 | # now check if the value already exist in the database | ||
210 | id=$(mysqldo "SELECT ${column_id} FROM ${MCORE_SQL_DB}.${table} WHERE ${column_value}='${value}';") | ||
211 | if [[ -n ${id} ]] | ||
212 | then | ||
213 | # update value in database | ||
214 | echo "UPDATE ${MCORE_SQL_DB}.${table} SET ${column_value}='${value}', ${column_bool}='${bool}' WHERE ${column_id}='${id}';" | ||
215 | mysqldo "UPDATE ${MCORE_SQL_DB}.${table} SET ${column_value}='${value}', ${column_bool}='${bool}' WHERE ${column_id}='${id}';" || die "update value in table ${table}" | ||
216 | else | ||
217 | # insert into the database | ||
218 | echo "INSERT INTO ${MCORE_SQL_DB}.${table} (${column_value}, ${column_bool}) VALUES ( '${value}', '${bool}' );" | ||
219 | mysqldo "INSERT INTO ${MCORE_SQL_DB}.${table} (${column_value}, ${column_bool}) VALUES ( '${value}', '${bool}' );" || die "insert value into table ${table}" | ||
220 | fi | ||
221 | done | ||
222 | } | ||
223 | |||
224 | update_to_database_schema_version_2() | ||
225 | { | ||
226 | return 0 | ||
227 | } | ||
228 | |||
229 | update_to_database_schema_version_3() | ||
230 | { | ||
231 | return 0 | ||
232 | } | ||
233 | |||
234 | create_database() | ||
235 | { | ||
236 | create_database_schema | ||
237 | } | ||
238 | |||
239 | update_database() | ||
240 | { | ||
241 | local current_sql_db_version | ||
242 | local i | ||
243 | |||
244 | current_sql_db_version=$(mysqldo "SELECT MAX(version) from meta;") | ||
245 | |||
246 | if [[ ${current_sql_db_version} = ${MCORE_SQL_DB_VERSION} ]] | ||
247 | then | ||
248 | echo "Database is already up to date" | ||
249 | return 0 | ||
250 | fi | ||
251 | |||
252 | # run every update_to_database_version_X script which exist up to version MCORE_SQL_DB_VERSION | ||
253 | for i in $(seq $((${current_sql_db_version}+1)) ${MCORE_SQL_DB_VERSION}) | ||
254 | do | ||
255 | if [[ -n $(typeset -f update_to_database_version_${i}) ]] | ||
256 | then | ||
257 | echo "Running update_to_database_schema_version_${i}()" | ||
258 | update_to_database_schema_version_${i} | ||
259 | fi | ||
260 | done | ||
261 | } | ||
262 | |||
263 | fill_database() | ||
264 | { | ||
265 | create_database_values | ||
266 | } | ||
267 | |||
268 | case $1 in | ||
269 | create) | ||
270 | create_database | ||
271 | fill_database | ||
272 | ;; | ||
273 | |||
274 | update) | ||
275 | update_database | ||
276 | fill_database | ||
277 | ;; | ||
278 | esac |