64 |
&& return 0 || return 1 |
&& return 0 || return 1 |
65 |
} |
} |
66 |
|
|
|
mysql_insert() |
|
|
{ |
|
|
local cmdline="$@" |
|
|
#local table="$1" |
|
|
local table="${cmdline%%,*}" |
|
|
local argv |
|
|
local insert_line |
|
|
local update_line |
|
|
local key |
|
|
local value |
|
|
local counter |
|
|
|
|
|
OLD_IFS=${IFS} |
|
|
IFS=, |
|
|
declare -i counter=0 |
|
|
#for argv in ${cmdline/$1/} # first value is the table_name |
|
|
for argv in ${cmdline#*,} # first value is the table_name |
|
|
do |
|
|
key="${argv%=*}" |
|
|
value="${argv#*=}" |
|
|
|
|
|
if [ ${counter} -eq 0 ] |
|
|
then |
|
|
insert_keys="${key}" |
|
|
insert_values="'${value}'" |
|
|
update_line="${key}='${value}'" |
|
|
else |
|
|
insert_keys+=",${key}" |
|
|
insert_values+=",'${value}'" |
|
|
update_line+=",${key}='${value}'" |
|
|
fi |
|
|
(( counter ++ )) |
|
|
done |
|
|
IFS=${OLD_IFS} |
|
|
|
|
|
echo "INSERT INTO ${table} (${insert_keys}) VALUES(${insert_values}) ON DUPLICATE KEY UPDATE ${update_line};" |
|
|
#mysqldo "insert into ${table} (${insert_keys}) values(${insert_values}) on duplicate key update ${update_line};" |
|
|
} |
|
|
|
|
|
|
|
67 |
# creates a mysql table with help of a defined array TABLE_DEFINITION: |
# creates a mysql table with help of a defined array TABLE_DEFINITION: |
68 |
# array values are a struct like this: |
# array values are a struct like this: |
69 |
# first array element is the table name, everything else are column descriptions |
# first array element is the table name, everything else are column descriptions |
90 |
|
|
91 |
for ((i=1; i<count; i++)) |
for ((i=1; i<count; i++)) |
92 |
do |
do |
93 |
line="${TABLE_DEFINITION[${i}]}" |
line=( ${TABLE_DEFINITION[${i}]} ) |
94 |
column="${line%% *}" |
column="${line[0]}" |
95 |
opts="${line/${column} /}" |
opts="${line[*]:1}" |
96 |
|
|
97 |
case ${opts} in |
case ${opts} in |
98 |
*"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;; |
*"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;; |
111 |
create+=", ${column} ${opts}" |
create+=", ${column} ${opts}" |
112 |
fi |
fi |
113 |
done |
done |
|
echo "CREATE TABLE ${MCORE_SQL_DB}.${table} ( ${create} );" |
|
114 |
mysqldo "CREATE TABLE ${MCORE_SQL_DB}.${table} ( ${create} );" || die "creating table '${table}'" |
mysqldo "CREATE TABLE ${MCORE_SQL_DB}.${table} ( ${create} );" || die "creating table '${table}'" |
115 |
|
|
116 |
if [[ -n ${primary} ]] |
if [[ -n ${primary} ]] |
117 |
then |
then |
|
echo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" |
|
118 |
mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'" |
mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'" |
119 |
fi |
fi |
120 |
if [[ -n ${autoincrement} ]] |
if [[ -n ${autoincrement} ]] |
121 |
then |
then |
122 |
echo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" |
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}'" |
mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" || die "adding auto_increment to table '${table}'" |
278 |
fi |
fi |
279 |
} |
} |
323 |
if [[ -n ${id} ]] |
if [[ -n ${id} ]] |
324 |
then |
then |
325 |
# update value in database |
# update value in database |
|
echo "UPDATE ${MCORE_SQL_DB}.${table} SET ${column_value}='${value}', ${column_bool}='${bool}' WHERE ${column_id}='${id}';" |
|
326 |
mysqldo "UPDATE ${MCORE_SQL_DB}.${table} SET ${column_value}='${value}', ${column_bool}='${bool}' WHERE ${column_id}='${id}';" || die "update value in table ${table}" |
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 |
else |
328 |
# insert into the database |
# insert into the database |
|
echo "INSERT INTO ${MCORE_SQL_DB}.${table} (${column_value}, ${column_bool}) VALUES ( '${value}', '${bool}' );" |
|
329 |
mysqldo "INSERT INTO ${MCORE_SQL_DB}.${table} (${column_value}, ${column_bool}) VALUES ( '${value}', '${bool}' );" || die "insert value into table ${table}" |
mysqldo "INSERT INTO ${MCORE_SQL_DB}.${table} (${column_value}, ${column_bool}) VALUES ( '${value}', '${bool}' );" || die "insert value into table ${table}" |
330 |
fi |
fi |
331 |
done |
done |
332 |
} |
} |
333 |
|
|
|
update_to_database_schema_version_2() |
|
|
{ |
|
|
return 0 |
|
|
} |
|
|
|
|
|
update_to_database_schema_version_3() |
|
|
{ |
|
|
return 0 |
|
|
} |
|
|
|
|
334 |
create_database() |
create_database() |
335 |
{ |
{ |
336 |
create_database_schema |
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() |
update_database() |
343 |
{ |
{ |
344 |
local current_sql_db_version |
local current_sql_db_schema_version |
345 |
local i |
local i |
346 |
|
|
347 |
current_sql_db_version=$(mysqldo "SELECT MAX(version) from meta;") |
current_sql_db_schema_version=$(mysqldo "SELECT MAX(version) from meta_schema;") |
348 |
|
|
349 |
if [[ ${current_sql_db_version} = ${MCORE_SQL_DB_VERSION} ]] |
if [[ ${current_sql_db_schema_version} = ${MCORE_SQL_DB_SCHEMA_VERSION} ]] |
350 |
then |
then |
351 |
echo "Database is already up to date" |
echo "Database schema is already up to date" |
352 |
return 0 |
return 0 |
353 |
fi |
fi |
354 |
|
|
355 |
# run every update_to_database_version_X script which exist up to version MCORE_SQL_DB_VERSION |
# 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_version}+1)) ${MCORE_SQL_DB_VERSION}) |
for i in $(seq $((${current_sql_db_schema_version}+1)) ${MCORE_SQL_DB_SCHEMA_VERSION}) |
357 |
do |
do |
358 |
if [[ -n $(typeset -f update_to_database_version_${i}) ]] |
if [[ -n $(typeset -f update_to_database_schema_version_${i}) ]] |
359 |
then |
then |
360 |
echo "Running update_to_database_schema_version_${i}()" |
echo "Running update_to_database_schema_version_${i}()" |
361 |
update_to_database_schema_version_${i} |
update_to_database_schema_version_${i} || die |
362 |
fi |
fi |
363 |
done |
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() |
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 |
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 |
case $1 in |