Contents of /alx-src/branches/alx-web-070/scripts/sql-schema/mcore-sql-schema.sh
Parent Directory | Revision Log
Revision 8485 -
(show 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 | #!/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 |