--- alx-src/branches/alx-web-070/scripts/sql-schema/mcore-sql-schema.sh	2016/02/05 14:34:11	8503
+++ alx-src/branches/alx-web-070/scripts/sql-schema/mcore-sql-schema.sh	2016/02/05 15:14:21	8504
@@ -139,6 +139,9 @@
 	local column
 	local autoincrement
 	local primary
+	local current_primary
+	local current_autoincrement
+	local current_opts
 
 	table="${TABLE_DEFINITION[0]}"
 	count="${#TABLE_DEFINITION[*]}"
@@ -163,8 +166,33 @@
 		mysqldo "ALTER TABLE ${MCORE_SQL_DB}.${table} ADD ${column} ${opts} AFTER ${after_column};" || 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"') )
+		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} ]]
@@ -190,6 +218,8 @@
 	local autoincrement
 	local primary
 	local current_primary
+	local current_autoincrement
+	local current_opts
 
 	table="${TABLE_DEFINITION[0]}"
 	count="${#TABLE_DEFINITION[*]}"
@@ -213,15 +243,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