--- alx-src/branches/alx-web-070/scripts/sql-schema/mcore-sql-schema.sh	2016/02/05 12:29:42	8488
+++ alx-src/branches/alx-web-070/scripts/sql-schema/mcore-sql-schema.sh	2016/02/05 14:34:11	8503
@@ -90,9 +90,9 @@
 
 	for ((i=1; i<count; i++))
 	do
-		line="${TABLE_DEFINITION[${i}]}"
-		column="${line%% *}"
-		opts="${line/${column} /}"
+		line=( ${TABLE_DEFINITION[${i}]} )
+		column="${line[0]}"
+		opts="${line[*]:1}"
 
 		case ${opts} in
 			*"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;;
@@ -111,17 +111,120 @@
 			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
+}
+
+# TABLE_DEFINITION=(
+#		"table_name"
+#			"column1_name after_column column1_argvs"
+#			"column2_name after_column column2_argvs"
+#			"columnN_name after_column columnN_argvs"
+#	)
+sql_add_column()
+{
+	local table
+	local count
+	local i
+	local line
+	local opts
+	local column
+	local autoincrement
+	local primary
+
+	table="${TABLE_DEFINITION[0]}"
+	count="${#TABLE_DEFINITION[*]}"
+
+	for ((i=1; i<count; i++))
+	do
+		line=( ${TABLE_DEFINITION[${i}]} )
+		column="${line[0]}"
+		after="${line[1]}"
+		opts="${line[*]:2}"
+
+		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} AFTER ${after_column};" || die "add column '${table}'"
+	done
+
+	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"
+#	)
+sql_modify_column()
+{
+	local table
+	local count
+	local i
+	local line
+	local opts
+	local column
+	local autoincrement
+	local primary
+	local current_primary
+
+	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
+
+	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 [[ ${current_primary} != ${primary} ]]
+		then
+			mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD PRIMARY KEY (${primary});" || die "adding primary key to table '${table}'"
+		fi
+	fi
+	if [[ -n ${autoincrement} ]]
+	then
 		mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} MODIFY ${autoincrement} AUTO_INCREMENT;" || die "adding auto_increment to table '${table}'"
 	fi
 }
@@ -171,58 +274,65 @@
 		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 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_version
+	local current_sql_db_schema_version
 	local i
 
-	current_sql_db_version=$(mysqldo "SELECT MAX(version) from meta;")
+	current_sql_db_schema_version=$(mysqldo "SELECT MAX(version) from meta_schema;")
 
-	if [[ ${current_sql_db_version} = ${MCORE_SQL_DB_VERSION} ]]
+	if [[ ${current_sql_db_schema_version} = ${MCORE_SQL_DB_SCHEMA_VERSION} ]]
 	then
-		echo "Database is already up to date"
+		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_VERSION
-	for i in $(seq $((${current_sql_db_version}+1)) ${MCORE_SQL_DB_VERSION})
+	# 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_version_${i}) ]]
+		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}
+			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