--- alx-src/branches/alx-web-070/scripts/sql-schema/mcore-sql-schema.sh	2016/02/05 14:32:57	8502
+++ alx-src/branches/alx-web-070/scripts/sql-schema/mcore-sql-schema.sh	2016/02/08 08:52:47	8509
@@ -125,10 +125,13 @@
 
 # TABLE_DEFINITION=(
 #		"table_name"
-#			"column1_name after_column column1_argvs"
-#			"column2_name after_column column2_argvs"
-#			"columnN_name after_column columnN_argvs"
+#			"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
@@ -139,6 +142,9 @@
 	local column
 	local autoincrement
 	local primary
+	local current_primary
+	local current_autoincrement
+	local current_opts
 
 	table="${TABLE_DEFINITION[0]}"
 	count="${#TABLE_DEFINITION[*]}"
@@ -147,8 +153,7 @@
 	do
 		line=( ${TABLE_DEFINITION[${i}]} )
 		column="${line[0]}"
-		after="${line[1]}"
-		opts="${line[*]:2}"
+		opts="${line[*]:1}"
 
 		case ${opts} in
 			*"PRIMARY KEY"*) opts="${opts//PRIMARY KEY}"; primary="${column}" ;;
@@ -160,11 +165,35 @@
 			*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}'"
+		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} ]]
@@ -190,6 +219,8 @@
 	local autoincrement
 	local primary
 	local current_primary
+	local current_autoincrement
+	local current_opts
 
 	table="${TABLE_DEFINITION[0]}"
 	count="${#TABLE_DEFINITION[*]}"
@@ -213,15 +244,34 @@
 		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 [[ ${current_primary} != ${primary} ]]
+		if [[ -n ${current_primary} ]]
 		then
-			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} 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
@@ -299,7 +349,7 @@
 
 	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