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
}

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};"
}


# 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%% *}"
		opts="${line/${column} /}"

		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
	echo "CREATE TABLE ${MCORE_SQL_DB}.${table} ( ${create} );"
	mysqldo "CREATE TABLE ${MCORE_SQL_DB}.${table} ( ${create} );" || die "creating table '${table}'"

	if [[ -n ${primary} ]]
	then
		echo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});"
		mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'"
	fi
	if [[ -n ${autoincrement} ]]
	then
		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}'"
	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
			echo "UPDATE ${MCORE_SQL_DB}.${table} SET ${column_value}='${value}', ${column_bool}='${bool}' WHERE ${column_id}='${id}';"
			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
			echo "INSERT INTO ${MCORE_SQL_DB}.${table} (${column_value}, ${column_bool}) VALUES ( '${value}', '${bool}' );"
			mysqldo "INSERT INTO ${MCORE_SQL_DB}.${table} (${column_value}, ${column_bool}) VALUES ( '${value}', '${bool}' );" || die "insert value into table ${table}"
		fi
	done
}

update_to_database_schema_version_2()
{
	return 0
}

update_to_database_schema_version_3()
{
	return 0
}

create_database()
{
	create_database_schema
}

update_database()
{
	local current_sql_db_version
	local i

	current_sql_db_version=$(mysqldo "SELECT MAX(version) from meta;")

	if [[ ${current_sql_db_version} = ${MCORE_SQL_DB_VERSION} ]]
	then
		echo "Database is already up to date"
		return 0
	fi

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

fill_database()
{
	create_database_values
}

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			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