scripts/sql-schema/mcore-sql-schema.sh

#!/bin/bash

# include everything from include
for inc in $(find ./include -type f -name \*.sh)
do
	source "${inc}"
done

die()
{
	echo "ERROR: $@"
	exit 1
}

mysql_command()
{
	local sql_user
	local sql_pass
	local sql_host
	local sql_db
	local sql_command

	sql_user=$1
	sql_pass=$2
	sql_host=$3
	sql_db=$4
	sql_command=$5

	#fallback (sql_opts not, they are optional )
	if [ -z "${sql_user}" \
		-o -z "${sql_pass}" \
		-o -z "${sql_host}" \
		-o -z "${sql_db}" \
		-o -z "${sql_command}" ]
	then
		echo
		echo "Usage:"
		echo -e "      mysql_command user pass host db \"sql-command\""
		echo
		return 1
	fi

	mysql \
		--user="${sql_user}" \
		--password="${sql_pass}" \
		--host="${sql_host}" \
		--database="${sql_db}" \
		--batch \
		--skip-column-names \
		--execute="${sql_command}" \
		|| return 1

	return 0
}

mysqldo()
{
	mysql_command \
		"${MCORE_SQL_USER}" \
		"${MCORE_SQL_PASS}" \
		"${MCORE_SQL_HOST}" \
		"${MCORE_SQL_DB}" \
		"$@" \
		&& return 0 || return 1
}

# creates a mysql table with help of a defined array TABLE_DEFINITION:
# array values are a struct like this:
# first array element is the table name, everything else are column descriptions
# TABLE_DEFINITION=(
#		"table_name"
#			"column1_name column1_argvs"
#			"column2_name column2_argvs"
#			"columnN_name columnN_argvs"
#	)
sql_create_table()
{
	local table
	local count
	local i
	local line
	local opts
	local column
	local autoincrement
	local primary
	local create

	table="${TABLE_DEFINITION[0]}"
	count="${#TABLE_DEFINITION[*]}"

	for ((i=1; i<count; i++))
	do
		line=( ${TABLE_DEFINITION[${i}]} )
		column="${line[0]}"
		opts="${line[*]:1}"

		case ${opts} in
			*"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;;
			*"primary key"*) opts="${opts//primary key}"; primary="${column}" ;;
		esac

		case ${opts} in
			*AUTO_INCREMENT*) opts="${opts//AUTO_INCREMENT}"; autoincrement="${column} ${opts}" ;;
			*auto_increment*) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;;
		esac

		if [[ -z ${create} ]]
		then
			create="${column} ${opts}"
		else
			create+=", ${column} ${opts}"
		fi
	done
	mysqldo "CREATE TABLE ${MCORE_SQL_DB}.${table} ( ${create} );" || die "creating table '${table}'"

	if [[ -n ${primary} ]]
	then
		mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'"
	fi
	if [[ -n ${autoincrement} ]]
	then
		mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" || die "adding auto_increment to table '${table}'"
	fi
}

# TABLE_DEFINITION=(
#		"table_name"
#			"column1_name column1_argvs"
#			"column2_name column2_argvs"
#			"columnN_name columnN_argvs"
#
# eg.    "description varchar(255) DEFAULT NULL AFTER value"
#	)
#
sql_add_column()
{
	local table
	local count
	local i
	local line
	local opts
	local column
	local autoincrement
	local primary
	local current_primary
	local current_autoincrement
	local current_opts

	table="${TABLE_DEFINITION[0]}"
	count="${#TABLE_DEFINITION[*]}"

	for ((i=1; i<count; i++))
	do
		line=( ${TABLE_DEFINITION[${i}]} )
		column="${line[0]}"
		opts="${line[*]:1}"

		case ${opts} in
			*"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;;
			*"primary key"*) opts="${opts//primary key}"; primary="${column}" ;;
		esac

		case ${opts} in
			*AUTO_INCREMENT*) opts="${opts//AUTO_INCREMENT}"; autoincrement="${column} ${opts}" ;;
			*auto_increment*) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;;
		esac

		mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD ${column} ${opts};" || die "add column '${table}'"
	done

	# delete an existing auto_increment first
	if [[ -n ${autoincrement} ]]
	then
		i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Extra`="auto_increment"') )
		current_autoincrement="${i[0]}"
		if [[ ${current_autoincrement} != ${column} ]] && [[ -n ${current_autoincrement} ]]
		then
			current_opts=( "${i[1]}" ) # type eg int(11)
			# null or not
			case "${i[2]}" in
				NO|no) current_opts+=( "NOT NULL" ) ;;
				YES|yes) current_opts+=( "DEFAULT ${i[4]}" ) ;; # should never match, but to be save
			esac
			mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} CHANGE ${current_autoincrement} ${current_autoincrement} ${current_opts};" || die "removing auto_increment to table '${table}'"
		fi
	fi

	if [[ -n ${primary} ]]
	then
		# only run this if the primary key is not already the same column
		i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Key`="PRI"') )
		current_primary="${i[0]}"
		if [[ -n ${current_primary} ]]
		then
			mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} DROP PRIMARY KEY;" || die "adding primary key to table '${table}'"
		fi
		mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'"
	fi
	if [[ -n ${autoincrement} ]]
	then
		mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" || die "adding auto_increment to table '${table}'"
	fi
}

# TABLE_DEFINITION=(
#		"table_name"
#			"column1_name column1_argvs"
#			"column2_name column2_argvs"
#			"columnN_name columnN_argvs"
#	)
sql_modify_column()
{
	local table
	local count
	local i
	local line
	local opts
	local column
	local autoincrement
	local primary
	local current_primary
	local current_autoincrement
	local current_opts

	table="${TABLE_DEFINITION[0]}"
	count="${#TABLE_DEFINITION[*]}"

	for ((i=1; i<count; i++))
	do
		line=( ${TABLE_DEFINITION[${i}]} )
		column="${line[0]}"
		opts="${line[*]:1}"

		case ${opts} in
			*"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;;
			*"primary key"*) opts="${opts//primary key}"; primary="${column}" ;;
		esac

		case ${opts} in
			*AUTO_INCREMENT*) opts="${opts//AUTO_INCREMENT}"; autoincrement="${column} ${opts}" ;;
			*auto_increment*) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;;
		esac

		mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${column} ${opts};" || die "modify table '${table}'"
	done

	# delete an existing auto_increment first
	if [[ -n ${autoincrement} ]]
	then
		i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Extra`="auto_increment"') )
		echo "DEBUG: i='${i[*]}'"
		current_autoincrement="${i[0]}"
		if [[ ${current_autoincrement} != ${column} ]] && [[ -n ${current_autoincrement} ]]
		then
			current_opts=( "${i[1]}" ) # type eg int(11)
			# null or not
			case "${i[2]}" in
				NO|no) current_opts+=( "NOT NULL" ) ;;
				YES|yes) current_opts+=( "DEFAULT ${i[4]}" ) ;; # should never match, but to be save
			esac
			mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} CHANGE ${current_autoincrement} ${current_autoincrement} ${current_opts};" || die "removing auto_increment to table '${table}'"
		fi
	fi

	if [[ -n ${primary} ]]
	then
		# only run this if the primary key is not already the same column
		i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Key`="PRI"') )
		current_primary="${i[0]}"
		if [[ -n ${current_primary} ]]
		then
			mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} DROP PRIMARY KEY;" || die "adding primary key to table '${table}'"
		fi
		mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'"
	fi
	if [[ -n ${autoincrement} ]]
	then
		mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" || die "adding auto_increment to table '${table}'"
	fi
}

# VALUE_DEFINITION=(
#		"table_name, value_column_name, bool_column_name, id_column_name"
#			"value1, bool1"
#			"value2, bool2"
#			"valueN, boolN"
#	)
sql_set_value()
{
	local line
	local meta
	local table
	local column_value
	local column_bool
	local column_id
	local value
	local bool
	local count
	local i
	local id

	count="${#VALUE_DEFINITION[*]}"
	line="${VALUE_DEFINITION[0]}"
	for i in ${line//,/ }
	do
		meta+=( "${i}" )
	done

	table="${meta[0]}"
	column_value="${meta[1]}"
	column_bool="${meta[2]}"
	column_id="${meta[3]}"

	for ((i=1; i<count; i++))
	do
		line="${VALUE_DEFINITION[${i}]}"
		value="${line%%,*}"
		bool="${line##*,}"
		# remove any leading whitespaces
		bool="${bool// }"

		# now check if the value already exist in the database
		id=$(mysqldo "SELECT ${column_id} FROM ${MCORE_SQL_DB}.${table} WHERE ${column_value}='${value}';")
		if [[ -n ${id} ]]
		then
			# update value in database
			mysqldo "UPDATE ${MCORE_SQL_DB}.${table} SET ${column_value}='${value}', ${column_bool}='${bool}' WHERE ${column_id}='${id}';" || die "update value in table ${table}"
		else
			# insert into the database
			mysqldo "INSERT INTO ${MCORE_SQL_DB}.${table} (${column_value}, ${column_bool}) VALUES ( '${value}', '${bool}' );" || die "insert value into table ${table}"
		fi
	done
}

create_database()
{
	create_database_schema

	# update database schema version
	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"
}

update_database()
{
	local current_sql_db_schema_version
	local i

	current_sql_db_schema_version=$(mysqldo "SELECT MAX(version) from meta_schema;")

	if [[ ${current_sql_db_schema_version} = ${MCORE_SQL_DB_SCHEMA_VERSION} ]]
	then
		echo "Database schema is already up to date"
		return 0
	fi

	# run every update_to_database_version_X script which exist up to version MCORE_SQL_DB_SCHEMA_VERSION
	for i in $(seq $((${current_sql_db_schema_version}+1)) ${MCORE_SQL_DB_SCHEMA_VERSION})
	do
		if [[ -n $(typeset -f update_to_database_schema_version_${i}) ]]
		then
			echo "Running update_to_database_schema_version_${i}()"
			update_to_database_schema_version_${i} || die
		fi
	done

	# update database schema version
	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"
}

fill_database()
{
	local current_sql_db_values_version
	local i

	current_sql_db_values_version=$(mysqldo "SELECT MAX(version) from meta_values;")

	if [[ ${current_sql_db_values_version} = ${MCORE_SQL_DB_VALUES_VERSION} ]]
	then
		echo "Database values are already up to date"
		return 0
	fi

	create_database_values
	# update database values version
	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"
}

case $1 in
	create)
		create_database
		fill_database
		;;

	update)
		update_database
		fill_database
		;;
esac
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	# 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	line=( ${TABLE_DEFINITION[${i}]} )
94	column="${line[0]}"
95	opts="${line[*]:1}"
96
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	# TABLE_DEFINITION=(
127	# "table_name"
128	# "column1_name column1_argvs"
129	# "column2_name column2_argvs"
130	# "columnN_name columnN_argvs"
131	#
132	# eg. "description varchar(255) DEFAULT NULL AFTER value"
133	# )
134	#
135	sql_add_column()
136	{
137	local table
138	local count
139	local i
140	local line
141	local opts
142	local column
143	local autoincrement
144	local primary
145	local current_primary
146	local current_autoincrement
147	local current_opts
148
149	table="${TABLE_DEFINITION[0]}"
150	count="${#TABLE_DEFINITION[*]}"
151
152	for ((i=1; i<count; i++))
153	do
154	line=( ${TABLE_DEFINITION[${i}]} )
155	column="${line[0]}"
156	opts="${line[*]:1}"
157
158	case ${opts} in
159	"PRIMARY KEY") opts="${opts//PRIMARY KEY}"; primary="${column}" ;;
160	"primary key") opts="${opts//primary key}"; primary="${column}" ;;
161	esac
162
163	case ${opts} in
164	AUTO_INCREMENT) opts="${opts//AUTO_INCREMENT}"; autoincrement="${column} ${opts}" ;;
165	auto_increment) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;;
166	esac
167
168	mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD ${column} ${opts};" \|\| die "add column '${table}'"
169	done
170
171	# delete an existing auto_increment first
172	if [[ -n ${autoincrement} ]]
173	then
174	i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Extra`="auto_increment"') )
175	current_autoincrement="${i[0]}"
176	if [[ ${current_autoincrement} != ${column} ]] && [[ -n ${current_autoincrement} ]]
177	then
178	current_opts=( "${i[1]}" ) # type eg int(11)
179	# null or not
180	case "${i[2]}" in
181	NO\|no) current_opts+=( "NOT NULL" ) ;;
182	YES\|yes) current_opts+=( "DEFAULT ${i[4]}" ) ;; # should never match, but to be save
183	esac
184	mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} CHANGE ${current_autoincrement} ${current_autoincrement} ${current_opts};" \|\| die "removing auto_increment to table '${table}'"
185	fi
186	fi
187
188	if [[ -n ${primary} ]]
189	then
190	# only run this if the primary key is not already the same column
191	i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Key`="PRI"') )
192	current_primary="${i[0]}"
193	if [[ -n ${current_primary} ]]
194	then
195	mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} DROP PRIMARY KEY;" \|\| die "adding primary key to table '${table}'"
196	fi
197	mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" \|\| die "adding primary key to table '${table}'"
198	fi
199	if [[ -n ${autoincrement} ]]
200	then
201	mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" \|\| die "adding auto_increment to table '${table}'"
202	fi
203	}
204
205	# TABLE_DEFINITION=(
206	# "table_name"
207	# "column1_name column1_argvs"
208	# "column2_name column2_argvs"
209	# "columnN_name columnN_argvs"
210	# )
211	sql_modify_column()
212	{
213	local table
214	local count
215	local i
216	local line
217	local opts
218	local column
219	local autoincrement
220	local primary
221	local current_primary
222	local current_autoincrement
223	local current_opts
224
225	table="${TABLE_DEFINITION[0]}"
226	count="${#TABLE_DEFINITION[*]}"
227
228	for ((i=1; i<count; i++))
229	do
230	line=( ${TABLE_DEFINITION[${i}]} )
231	column="${line[0]}"
232	opts="${line[*]:1}"
233
234	case ${opts} in
235	"PRIMARY KEY") opts="${opts//PRIMARY KEY}"; primary="${column}" ;;
236	"primary key") opts="${opts//primary key}"; primary="${column}" ;;
237	esac
238
239	case ${opts} in
240	AUTO_INCREMENT) opts="${opts//AUTO_INCREMENT}"; autoincrement="${column} ${opts}" ;;
241	auto_increment) opts="${opts//auto_increment}"; autoincrement="${column} ${opts}" ;;
242	esac
243
244	mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${column} ${opts};" \|\| die "modify table '${table}'"
245	done
246
247	# delete an existing auto_increment first
248	if [[ -n ${autoincrement} ]]
249	then
250	i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Extra`="auto_increment"') )
251	echo "DEBUG: i='${i[*]}'"
252	current_autoincrement="${i[0]}"
253	if [[ ${current_autoincrement} != ${column} ]] && [[ -n ${current_autoincrement} ]]
254	then
255	current_opts=( "${i[1]}" ) # type eg int(11)
256	# null or not
257	case "${i[2]}" in
258	NO\|no) current_opts+=( "NOT NULL" ) ;;
259	YES\|yes) current_opts+=( "DEFAULT ${i[4]}" ) ;; # should never match, but to be save
260	esac
261	mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} CHANGE ${current_autoincrement} ${current_autoincrement} ${current_opts};" \|\| die "removing auto_increment to table '${table}'"
262	fi
263	fi
264
265	if [[ -n ${primary} ]]
266	then
267	# only run this if the primary key is not already the same column
268	i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Key`="PRI"') )
269	current_primary="${i[0]}"
270	if [[ -n ${current_primary} ]]
271	then
272	mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} DROP PRIMARY KEY;" \|\| die "adding primary key to table '${table}'"
273	fi
274	mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" \|\| die "adding primary key to table '${table}'"
275	fi
276	if [[ -n ${autoincrement} ]]
277	then
278	mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" \|\| die "adding auto_increment to table '${table}'"
279	fi
280	}
281
282	# VALUE_DEFINITION=(
283	# "table_name, value_column_name, bool_column_name, id_column_name"
284	# "value1, bool1"
285	# "value2, bool2"
286	# "valueN, boolN"
287	# )
288	sql_set_value()
289	{
290	local line
291	local meta
292	local table
293	local column_value
294	local column_bool
295	local column_id
296	local value
297	local bool
298	local count
299	local i
300	local id
301
302	count="${#VALUE_DEFINITION[*]}"
303	line="${VALUE_DEFINITION[0]}"
304	for i in ${line//,/ }
305	do
306	meta+=( "${i}" )
307	done
308
309	table="${meta[0]}"
310	column_value="${meta[1]}"
311	column_bool="${meta[2]}"
312	column_id="${meta[3]}"
313
314	for ((i=1; i<count; i++))
315	do
316	line="${VALUE_DEFINITION[${i}]}"
317	value="${line%%,*}"
318	bool="${line##*,}"
319	# remove any leading whitespaces
320	bool="${bool// }"
321
322	# now check if the value already exist in the database
323	id=$(mysqldo "SELECT ${column_id} FROM ${MCORE_SQL_DB}.${table} WHERE ${column_value}='${value}';")
324	if [[ -n ${id} ]]
325	then
326	# update value in database
327	mysqldo "UPDATE ${MCORE_SQL_DB}.${table} SET ${column_value}='${value}', ${column_bool}='${bool}' WHERE ${column_id}='${id}';" \|\| die "update value in table ${table}"
328	else
329	# insert into the database
330	mysqldo "INSERT INTO ${MCORE_SQL_DB}.${table} (${column_value}, ${column_bool}) VALUES ( '${value}', '${bool}' );" \|\| die "insert value into table ${table}"
331	fi
332	done
333	}
334
335	create_database()
336	{
337	create_database_schema
338
339	# update database schema version
340	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"
341	}
342
343	update_database()
344	{
345	local current_sql_db_schema_version
346	local i
347
348	current_sql_db_schema_version=$(mysqldo "SELECT MAX(version) from meta_schema;")
349
350	if [[ ${current_sql_db_schema_version} = ${MCORE_SQL_DB_SCHEMA_VERSION} ]]
351	then
352	echo "Database schema is already up to date"
353	return 0
354	fi
355
356	# run every update_to_database_version_X script which exist up to version MCORE_SQL_DB_SCHEMA_VERSION
357	for i in $(seq $((${current_sql_db_schema_version}+1)) ${MCORE_SQL_DB_SCHEMA_VERSION})
358	do
359	if [[ -n $(typeset -f update_to_database_schema_version_${i}) ]]
360	then
361	echo "Running update_to_database_schema_version_${i}()"
362	update_to_database_schema_version_${i} \|\| die
363	fi
364	done
365
366	# update database schema version
367	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"
368	}
369
370	fill_database()
371	{
372	local current_sql_db_values_version
373	local i
374
375	current_sql_db_values_version=$(mysqldo "SELECT MAX(version) from meta_values;")
376
377	if [[ ${current_sql_db_values_version} = ${MCORE_SQL_DB_VALUES_VERSION} ]]
378	then
379	echo "Database values are already up to date"
380	return 0
381	fi
382
383	create_database_values
384	# update database values version
385	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"
386	}
387
388	case $1 in
389	create)
390	create_database
391	fill_database
392	;;
393
394	update)
395	update_database
396	fill_database
397	;;
398	esac