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"') )
		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

	echo "Updating database default values to version ${MCORE_SQL_DB_VALUES_VERSION}"
	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	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	niro	8509	# "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	niro	8493	# )
134	niro	8509	#
135	niro	8493	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	niro	8504	local current_primary
146			local current_autoincrement
147			local current_opts
148	niro	8493
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	niro	8509	opts="${line[*]:1}"
157	niro	8493
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	niro	8509	mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD ${column} ${opts};" \|\| die "add column '${table}'"
169	niro	8493	done
170
171	niro	8504	# 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	niro	8493	if [[ -n ${primary} ]]
189			then
190	niro	8504	# 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	niro	8493	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	niro	8501	local current_primary
222	niro	8504	local current_autoincrement
223			local current_opts
224	niro	8493
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	niro	8500	mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${column} ${opts};" \|\| die "modify table '${table}'"
245	niro	8493	done
246
247	niro	8504	# 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			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	niro	8493	if [[ -n ${primary} ]]
265			then
266	niro	8501	# only run this if the primary key is not already the same column
267	niro	8502	i=( $(mysqldo 'SHOW COLUMNS FROM '${MCORE_SQL_DB}.${table}' WHERE `Key`="PRI"') )
268	niro	8501	current_primary="${i[0]}"
269	niro	8504	if [[ -n ${current_primary} ]]
270	niro	8501	then
271	niro	8504	mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} DROP PRIMARY KEY;" \|\| die "adding primary key to table '${table}'"
272	niro	8501	fi
273	niro	8504	mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" \|\| die "adding primary key to table '${table}'"
274	niro	8493	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}'"
278			fi
279			}
280
281	niro	8485	# VALUE_DEFINITION=(
282			# "table_name, value_column_name, bool_column_name, id_column_name"
283			# "value1, bool1"
284			# "value2, bool2"
285			# "valueN, boolN"
286			# )
287			sql_set_value()
288			{
289			local line
290			local meta
291			local table
292			local column_value
293			local column_bool
294			local column_id
295			local value
296			local bool
297			local count
298			local i
299			local id
300
301			count="${#VALUE_DEFINITION[*]}"
302			line="${VALUE_DEFINITION[0]}"
303			for i in ${line//,/ }
304			do
305			meta+=( "${i}" )
306			done
307
308			table="${meta[0]}"
309			column_value="${meta[1]}"
310			column_bool="${meta[2]}"
311			column_id="${meta[3]}"
312
313			for ((i=1; i<count; i++))
314			do
315			line="${VALUE_DEFINITION[${i}]}"
316			value="${line%%,*}"
317			bool="${line##*,}"
318			# remove any leading whitespaces
319			bool="${bool// }"
320
321			# now check if the value already exist in the database
322			id=$(mysqldo "SELECT ${column_id} FROM ${MCORE_SQL_DB}.${table} WHERE ${column_value}='${value}';")
323			if [[ -n ${id} ]]
324			then
325			# update value in database
326			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
328			# insert into the database
329			mysqldo "INSERT INTO ${MCORE_SQL_DB}.${table} (${column_value}, ${column_bool}) VALUES ( '${value}', '${bool}' );" \|\| die "insert value into table ${table}"
330			fi
331			done
332			}
333
334			create_database()
335			{
336			create_database_schema
337	niro	8495
338	niro	8498	# 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	niro	8485	}
341
342			update_database()
343			{
344	niro	8497	local current_sql_db_schema_version
345	niro	8485	local i
346
347	niro	8498	current_sql_db_schema_version=$(mysqldo "SELECT MAX(version) from meta_schema;")
348	niro	8485
349	niro	8497	if [[ ${current_sql_db_schema_version} = ${MCORE_SQL_DB_SCHEMA_VERSION} ]]
350	niro	8485	then
351	niro	8503	echo "Database schema is already up to date"
352	niro	8485	return 0
353			fi
354
355	niro	8496	# run every update_to_database_version_X script which exist up to version MCORE_SQL_DB_SCHEMA_VERSION
356	niro	8497	for i in $(seq $((${current_sql_db_schema_version}+1)) ${MCORE_SQL_DB_SCHEMA_VERSION})
357	niro	8485	do
358	niro	8495	if [[ -n $(typeset -f update_to_database_schema_version_${i}) ]]
359	niro	8485	then
360			echo "Running update_to_database_schema_version_${i}()"
361	niro	8495	update_to_database_schema_version_${i} \|\| die
362	niro	8485	fi
363			done
364	niro	8495
365	niro	8498	# 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	niro	8485	}
368
369			fill_database()
370			{
371	niro	8498	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	niro	8519	echo "Updating database default values to version ${MCORE_SQL_DB_VALUES_VERSION}"
383	niro	8485	create_database_values
384	niro	8498	# 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	niro	8485	}
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