Repository: schemahero/schemahero
Pull request #244 (2020-08-06 04:31:43)

Auto_increment mysql


Patch:
diff --git a/config/crds/v1/schemas.schemahero.io_tables.yaml b/config/crds/v1/schemas.schemahero.io_tables.yaml
index 2ed69286..23a7b348 100644
--- a/config/crds/v1/schemas.schemahero.io_tables.yaml
+++ b/config/crds/v1/schemas.schemahero.io_tables.yaml
@@ -51,6 +51,11 @@ spec:
                       columns:
                         items:
                           properties:
+                            attributes:
+                              properties:
+                                autoIncrement:
+                                  type: boolean
+                              type: object
                             constraints:
                               properties:
                                 notNull:
@@ -124,6 +129,11 @@ spec:
                       columns:
                         items:
                           properties:
+                            attributes:
+                              properties:
+                                autoIncrement:
+                                  type: boolean
+                              type: object
                             constraints:
                               properties:
                                 notNull:
@@ -197,6 +207,11 @@ spec:
                       columns:
                         items:
                           properties:
+                            attributes:
+                              properties:
+                                autoIncrement:
+                                  type: boolean
+                              type: object
                             constraints:
                               properties:
                                 notNull:
diff --git a/config/crds/v1beta1/schemas.schemahero.io_tables.yaml b/config/crds/v1beta1/schemas.schemahero.io_tables.yaml
index b829e36d..6430cba4 100644
--- a/config/crds/v1beta1/schemas.schemahero.io_tables.yaml
+++ b/config/crds/v1beta1/schemas.schemahero.io_tables.yaml
@@ -49,6 +49,11 @@ spec:
                     columns:
                       items:
                         properties:
+                          attributes:
+                            properties:
+                              autoIncrement:
+                                type: boolean
+                            type: object
                           constraints:
                             properties:
                               notNull:
@@ -122,6 +127,11 @@ spec:
                     columns:
                       items:
                         properties:
+                          attributes:
+                            properties:
+                              autoIncrement:
+                                type: boolean
+                            type: object
                           constraints:
                             properties:
                               notNull:
@@ -195,6 +205,11 @@ spec:
                     columns:
                       items:
                         properties:
+                          attributes:
+                            properties:
+                              autoIncrement:
+                                type: boolean
+                            type: object
                           constraints:
                             properties:
                               notNull:
diff --git a/pkg/apis/schemas/v1alpha4/sql.go b/pkg/apis/schemas/v1alpha4/sql.go
index 1c7592d7..6f00a398 100644
--- a/pkg/apis/schemas/v1alpha4/sql.go
+++ b/pkg/apis/schemas/v1alpha4/sql.go
@@ -39,10 +39,15 @@ type SQLTableColumnConstraints struct {
 	NotNull *bool `json:"notNull,omitempty" yaml:"notNull,omitempty"`
 }
 
+type SQLTableColumnAttributes struct {
+	AutoIncrement *bool `json:"autoIncrement,omitempty" yaml:"autoIncrement,omitempty"`
+}
+
 type SQLTableColumn struct {
 	Name        string                     `json:"name" yaml:"name"`
 	Type        string                     `json:"type" yaml:"type"`
 	Constraints *SQLTableColumnConstraints `json:"constraints,omitempty" yaml:"constraints,omitempty"`
+	Attributes  *SQLTableColumnAttributes  `json:"attributes,omitempty" yaml:"attributes,omitempty"`
 	Default     *string                    `json:"default,omitempty" yaml:"default,omitempty"`
 }
 
diff --git a/pkg/apis/schemas/v1alpha4/zz_generated.deepcopy.go b/pkg/apis/schemas/v1alpha4/zz_generated.deepcopy.go
index a50808fd..9f2e69c5 100644
--- a/pkg/apis/schemas/v1alpha4/zz_generated.deepcopy.go
+++ b/pkg/apis/schemas/v1alpha4/zz_generated.deepcopy.go
@@ -121,6 +121,11 @@ func (in *SQLTableColumn) DeepCopyInto(out *SQLTableColumn) {
 		*out = new(SQLTableColumnConstraints)
 		(*in).DeepCopyInto(*out)
 	}
+	if in.Attributes != nil {
+		in, out := &in.Attributes, &out.Attributes
+		*out = new(SQLTableColumnAttributes)
+		(*in).DeepCopyInto(*out)
+	}
 	if in.Default != nil {
 		in, out := &in.Default, &out.Default
 		*out = new(string)
@@ -138,6 +143,26 @@ func (in *SQLTableColumn) DeepCopy() *SQLTableColumn {
 	return out
 }
 
+// DeepCopyInto is an autogenerated deepcopy function, copying the receiver, writing into out. in must be non-nil.
+func (in *SQLTableColumnAttributes) DeepCopyInto(out *SQLTableColumnAttributes) {
+	*out = *in
+	if in.AutoIncrement != nil {
+		in, out := &in.AutoIncrement, &out.AutoIncrement
+		*out = new(bool)
+		**out = **in
+	}
+}
+
+// DeepCopy is an autogenerated deepcopy function, copying the receiver, creating a new SQLTableColumnAttributes.
+func (in *SQLTableColumnAttributes) DeepCopy() *SQLTableColumnAttributes {
+	if in == nil {
+		return nil
+	}
+	out := new(SQLTableColumnAttributes)
+	in.DeepCopyInto(out)
+	return out
+}
+
 // DeepCopyInto is an autogenerated deepcopy function, copying the receiver, writing into out. in must be non-nil.
 func (in *SQLTableColumnConstraints) DeepCopyInto(out *SQLTableColumnConstraints) {
 	*out = *in
diff --git a/pkg/database/mysql/alias.go b/pkg/database/mysql/alias.go
index 03a81056..04af6de6 100644
--- a/pkg/database/mysql/alias.go
+++ b/pkg/database/mysql/alias.go
@@ -24,6 +24,14 @@ func unaliasUnparameterizedColumnType(requestedType string) string {
 		requestedType = "mediumtext"
 	} else if requestedType == "longtext (4294967295)" {
 		requestedType = "longtext"
+	} else if requestedType == "blob (65535)" {
+		requestedType = "blob"
+	} else if requestedType == "tinyblob (255)" {
+		requestedType = "tinyblob"
+	} else if requestedType == "mediumblob (16777215)" {
+		requestedType = "mediumblob"
+	} else if requestedType == "longblob (4294967295)" {
+		requestedType = "longblob"
 	}
 
 	for _, unparameterizedColumnType := range unparameterizedColumnTypes {
diff --git a/pkg/database/mysql/alter.go b/pkg/database/mysql/alter.go
index c02d9e1c..f7515126 100644
--- a/pkg/database/mysql/alter.go
+++ b/pkg/database/mysql/alter.go
@@ -66,7 +66,23 @@ func columnsMatch(col1 *types.Column, col2 *types.Column) bool {
 		col2Constraints = &types.ColumnConstraints{}
 	}
 
-	return types.NotNullConstraintEquals(col1Constraints.NotNull, col2Constraints.NotNull)
+	if !types.BoolsEqual(col1Constraints.NotNull, col2Constraints.NotNull) {
+		return false
+	}
+
+	col1Attributes, col2Attributes := col1.Attributes, col2.Attributes
+	if col1Attributes == nil {
+		col1Attributes = &types.ColumnAttributes{}
+	}
+	if col2Attributes == nil {
+		col2Attributes = &types.ColumnAttributes{}
+	}
+
+	if !types.BoolsEqual(col1Attributes.AutoIncrement, col2Attributes.AutoIncrement) {
+		return false
+	}
+
+	return true
 }
 
 func ensureColumnConstraintsNotNullTrue(column *types.Column) {
diff --git a/pkg/database/mysql/alter_statements.go b/pkg/database/mysql/alter_statements.go
index e584119a..dcd840f2 100644
--- a/pkg/database/mysql/alter_statements.go
+++ b/pkg/database/mysql/alter_statements.go
@@ -59,9 +59,14 @@ func (s AlterModifyColumnStatement) ddl(useConstraintsFromExistingColumn bool) [
 		}
 	}
 
+	if s.Column.Attributes != nil && s.Column.Attributes.AutoIncrement != nil && *s.Column.Attributes.AutoIncrement {
+		stmts = append(stmts, "auto_increment")
+	}
+
 	if s.Column.ColumnDefault != nil {
 		stmts = append(stmts, fmt.Sprintf("default \"%s\"", *s.Column.ColumnDefault))
 	}
+
 	return []string{strings.Join(stmts, " ")}
 }
 
diff --git a/pkg/database/mysql/column.go b/pkg/database/mysql/column.go
index a23d7751..18b3c7c0 100644
--- a/pkg/database/mysql/column.go
+++ b/pkg/database/mysql/column.go
@@ -19,6 +19,12 @@ func schemaColumnToColumn(schemaColumn *schemasv1alpha4.SQLTableColumn) (*types.
 		}
 	}
 
+	if schemaColumn.Attributes != nil {
+		column.Attributes = &types.ColumnAttributes{
+			AutoIncrement: schemaColumn.Attributes.AutoIncrement,
+		}
+	}
+
 	requestedType := schemaColumn.Type
 	unaliasedColumnType := unaliasUnparameterizedColumnType(requestedType)
 	if unaliasedColumnType != "" {
@@ -64,6 +70,10 @@ func mysqlColumnAsInsert(column *schemasv1alpha4.SQLTableColumn) (string, error)
 		}
 	}
 
+	if mysqlColumn.Attributes != nil && mysqlColumn.Attributes.AutoIncrement != nil && *mysqlColumn.Attributes.AutoIncrement {
+		formatted = fmt.Sprintf("%s auto_increment", formatted)
+	}
+
 	if mysqlColumn.ColumnDefault != nil {
 		quoteDefaultValue := true
 
diff --git a/pkg/database/mysql/deploy.go b/pkg/database/mysql/deploy.go
index 72aca845..56700061 100644
--- a/pkg/database/mysql/deploy.go
+++ b/pkg/database/mysql/deploy.go
@@ -4,6 +4,7 @@ import (
 	"context"
 	"database/sql"
 	"fmt"
+	"strings"
 
 	"github.com/pkg/errors"
 	schemasv1alpha4 "github.com/schemahero/schemahero/pkg/apis/schemas/v1alpha4"
@@ -107,7 +108,7 @@ func executeStatements(m *MysqlConnection, statements []string) error {
 
 func buildColumnStatements(m *MysqlConnection, tableName string, mysqlTableSchema *schemasv1alpha4.SQLTableSchema) ([]string, error) {
 	query := `select
-COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH
+COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, EXTRA, COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH
 from information_schema.COLUMNS
 where TABLE_NAME = ?`
 	rows, err := m.db.Query(query, tableName)
@@ -117,16 +118,18 @@ where TABLE_NAME = ?`
 	alterAndDropStatements := []string{}
 	foundColumnNames := []string{}
 	for rows.Next() {
-		var columnName, dataType, isNullable string
+		var columnName, dataType, isNullable, extra string
 		var columnDefault sql.NullString
 		var charMaxLength sql.NullInt64
 
-		if err := rows.Scan(&columnName, &columnDefault, &isNullable, &dataType, &charMaxLength); err != nil {
+		if err := rows.Scan(&columnName, &columnDefault, &isNullable, &extra, &dataType, &charMaxLength); err != nil {
 			return nil, errors.Wrap(err, "failed to scan")
 		}
 
 		ignoreMaxLength := false
-		if dataType == "text" || dataType == "tinytext" || dataType == "mediumtext" || dataType == "longtext" {
+		if dataType == "text" || dataType == "tinytext" || dataType == "mediumtext" || dataType == "longtext" ||
+			dataType == "blob" || dataType == "tinyblob" || dataType == "mediumblob" || dataType == "longblob" {
+
 			ignoreMaxLength = true
 		}
 
@@ -145,6 +148,7 @@ where TABLE_NAME = ?`
 			Name:        columnName,
 			DataType:    dataType,
 			Constraints: &types.ColumnConstraints{},
+			Attributes:  &types.ColumnAttributes{},
 		}
 
 		if isNullable == "NO" {
@@ -153,6 +157,12 @@ where TABLE_NAME = ?`
 			existingColumn.Constraints.NotNull = &falseValue
 		}
 
+		if strings.Contains(extra, "auto_increment") {
+			existingColumn.Attributes.AutoIncrement = &trueValue
+		} else {
+			existingColumn.Attributes.AutoIncrement = &falseValue
+		}
+
 		if columnDefault.Valid {
 			existingColumn.ColumnDefault = &columnDefault.String
 		}
diff --git a/pkg/database/mysql/parameterized.go b/pkg/database/mysql/parameterized.go
index 356f88c3..8086999a 100644
--- a/pkg/database/mysql/parameterized.go
+++ b/pkg/database/mysql/parameterized.go
@@ -17,6 +17,7 @@ var unparameterizedColumnTypes = []string{
 	"mediumtext",
 	"longblob",
 	"longtext",
+	"blob",
 	"text",
 }
 
diff --git a/pkg/database/mysql/tables.go b/pkg/database/mysql/tables.go
index 5c26b5a7..ff2475b9 100644
--- a/pkg/database/mysql/tables.go
+++ b/pkg/database/mysql/tables.go
@@ -165,7 +165,7 @@ order by c.ORDINAL_POSITION`
 }
 
 func (m *MysqlConnection) GetTableSchema(tableName string) ([]*types.Column, error) {
-	query := `select COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE 
+	query := `select COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, EXTRA, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
 from information_schema.COLUMNS 
 where TABLE_NAME = ? 
 order by ORDINAL_POSITION`
@@ -177,26 +177,31 @@ order by ORDINAL_POSITION`
 	columns := make([]*types.Column, 0, 0)
 
 	for rows.Next() {
-		column := types.Column{}
+		column := types.Column{
+			Constraints: &types.ColumnConstraints{},
+			Attributes:  &types.ColumnAttributes{},
+		}
 
 		var maxLength sql.NullInt64
-		var isNullable string
+		var isNullable, extra string
 		var columnDefault sql.NullString
 		var numericPrecision sql.NullInt64
 		var numericScale sql.NullInt64
 
-		if err := rows.Scan(&column.Name, &columnDefault, &isNullable, &column.DataType, &maxLength, &numericPrecision, &numericScale); err != nil {
+		if err := rows.Scan(&column.Name, &columnDefault, &isNullable, &extra, &column.DataType, &maxLength, &numericPrecision, &numericScale); err != nil {
 			return nil, err
 		}
 
 		if isNullable == "NO" {
-			column.Constraints = &types.ColumnConstraints{
-				NotNull: &trueValue,
-			}
+			column.Constraints.NotNull = &trueValue
 		} else {
-			column.Constraints = &types.ColumnConstraints{
-				NotNull: &falseValue,
-			}
+			column.Constraints.NotNull = &falseValue
+		}
+
+		if strings.Contains(extra, "auto_increment") {
+			column.Attributes.AutoIncrement = &trueValue
+		} else {
+			column.Attributes.AutoIncrement = &falseValue
 		}
 
 		if columnDefault.Valid {
@@ -205,13 +210,9 @@ order by ORDINAL_POSITION`
 
 		// max length should not be written for all types
 		ignoreMaxLength := false
-		if column.DataType == "text" {
-			ignoreMaxLength = true
-		} else if column.DataType == "tinytext" {
-			ignoreMaxLength = true
-		} else if column.DataType == "mediumtext" {
-			ignoreMaxLength = true
-		} else if column.DataType == "longtext" {
+		if column.DataType == "text" || column.DataType == "tinytext" || column.DataType == "mediumtext" || column.DataType == "longtext" ||
+			column.DataType == "blob" || column.DataType == "tinyblob" || column.DataType == "mediumblob" || column.DataType == "longblob" {
+
 			ignoreMaxLength = true
 		}
 
diff --git a/pkg/database/postgres/alter.go b/pkg/database/postgres/alter.go
index 2a063cf6..80da3d75 100644
--- a/pkg/database/postgres/alter.go
+++ b/pkg/database/postgres/alter.go
@@ -148,5 +148,5 @@ func columnsMatch(col1 *types.Column, col2 *types.Column) bool {
 		col2Constraints = &types.ColumnConstraints{}
 	}
 
-	return types.NotNullConstraintEquals(col1Constraints.NotNull, col2Constraints.NotNull)
+	return types.BoolsEqual(col1Constraints.NotNull, col2Constraints.NotNull)
 }
diff --git a/pkg/database/types/column.go b/pkg/database/types/column.go
index 050d70ff..d6c758c3 100644
--- a/pkg/database/types/column.go
+++ b/pkg/database/types/column.go
@@ -8,11 +8,15 @@ type ColumnConstraints struct {
 	NotNull *bool
 }
 
-func NotNullConstraintEquals(a, b *bool) bool {
-	if a == nil || bool(*a) == false {
-		return b == nil || bool(*b) == false
+type ColumnAttributes struct {
+	AutoIncrement *bool
+}
+
+func BoolsEqual(a, b *bool) bool {
+	if a == nil || *a == false {
+		return b == nil || *b == false
 	}
-	return b != nil && bool(*b) == true
+	return b != nil && *b == true
 }
 
 type Column struct {
@@ -20,6 +24,7 @@ type Column struct {
 	DataType      string
 	ColumnDefault *string
 	Constraints   *ColumnConstraints
+	Attributes    *ColumnAttributes
 	IsArray       bool
 }
 
@@ -35,6 +40,12 @@ func ColumnToSchemaColumn(column *Column) (*schemasv1alpha4.SQLTableColumn, erro
 		}
 	}
 
+	if column.Attributes != nil {
+		schemaColumn.Attributes = &schemasv1alpha4.SQLTableColumnAttributes{
+			AutoIncrement: column.Attributes.AutoIncrement,
+		}
+	}
+
 	schemaColumn.Default = column.ColumnDefault
 
 	return schemaColumn, nil
diff --git a/pkg/installer/table_objects.go b/pkg/installer/table_objects.go
index 53904755..7d0d024c 100644
--- a/pkg/installer/table_objects.go
+++ b/pkg/installer/table_objects.go
@@ -51,6 +51,11 @@ spec:
                     columns:
                       items:
                         properties:
+                          attributes:
+                            properties:
+                              autoIncrement:
+                                type: boolean
+                            type: object
                           constraints:
                             properties:
                               notNull:
@@ -124,6 +129,11 @@ spec:
                     columns:
                       items:
                         properties:
+                          attributes:
+                            properties:
+                              autoIncrement:
+                                type: boolean
+                            type: object
                           constraints:
                             properties:
                               notNull:
@@ -197,6 +207,11 @@ spec:
                     columns:
                       items:
                         properties:
+                          attributes:
+                            properties:
+                              autoIncrement:
+                                type: boolean
+                            type: object
                           constraints:
                             properties:
                               notNull:
@@ -347,6 +362,11 @@ spec:
                       columns:
                         items:
                           properties:
+                            attributes:
+                              properties:
+                                autoIncrement:
+                                  type: boolean
+                              type: object
                             constraints:
                               properties:
                                 notNull:
@@ -420,6 +440,11 @@ spec:
                       columns:
                         items:
                           properties:
+                            attributes:
+                              properties:
+                                autoIncrement:
+                                  type: boolean
+                              type: object
                             constraints:
                               properties:
                                 notNull:
@@ -493,6 +518,11 @@ spec:
                       columns:
                         items:
                           properties:
+                            attributes:
+                              properties:
+                                autoIncrement:
+                                  type: boolean
+                              type: object
                             constraints:
                               properties:
                                 notNull:


Test patch:
diff --git a/integration/tests/cockroach/common.mk b/integration/tests/cockroach/common.mk
index d7bdd260..a3cf2b74 100644
--- a/integration/tests/cockroach/common.mk
+++ b/integration/tests/cockroach/common.mk
@@ -24,6 +24,9 @@ run:
 	@echo Verifying results for $(TEST_NAME)
 	diff -B expect.sql out.sql
 
+	# Apply
+	../../../../bin/kubectl-schemahero apply --driver=$(DRIVER) --uri="$(URI)" --ddl out.sql
+
 	# Cleanup
 	@-sleep 5
 	rm ./out.sql
diff --git a/integration/tests/mysql/Makefile b/integration/tests/mysql/Makefile
index bf5c3981..fe78be99 100644
--- a/integration/tests/mysql/Makefile
+++ b/integration/tests/mysql/Makefile
@@ -12,6 +12,7 @@ run: 5.6 5.7 8.0
 5.6:
 	make -C decimal run
 	make -C medium-text run
+	make -C medium-blob run
 	make -C column-set-default run
 	make -C column-unset-default run
 	make -C create-table run
@@ -23,12 +24,16 @@ run: 5.6 5.7 8.0
 	make -C index-create run
 	make -C primary-key-add run
 	make -C primary-key-drop run
+	make -C auto-increment-create run
+	make -C auto-increment-add run
+	make -C auto-increment-drop run
 
 .PHONY: 5.7
 5.7: export MYSQL_VERSION = 5.7
 5.7:
 	make -C decimal run
 	make -C medium-text run
+	make -C medium-blob run
 	make -C column-set-default run
 	make -C column-unset-default run
 	make -C create-table run
@@ -40,12 +45,16 @@ run: 5.6 5.7 8.0
 	make -C index-create run
 	make -C primary-key-add run
 	make -C primary-key-drop run
+	make -C auto-increment-create run
+	make -C auto-increment-add run
+	make -C auto-increment-drop run
 
 .PHONY: 8.0
 8.0: export MYSQL_VERSION = 8.0
 8.0:
 	make -C decimal run
 	make -C medium-text run
+	make -C medium-blob run
 	make -C column-set-default run
 	make -C column-unset-default run
 	make -C create-table run
@@ -57,6 +66,9 @@ run: 5.6 5.7 8.0
 	make -C index-create run
 	make -C primary-key-add run
 	make -C primary-key-drop run
+	make -C auto-increment-create run
+	make -C auto-increment-add run
+	make -C auto-increment-drop run
 
 
 .PHONY: 8.0
@@ -64,6 +76,7 @@ run: 5.6 5.7 8.0
 8.0:
 	make -C decimal run
 	make -C medium-text run
+	make -C medium-blob run
 	make -C column-set-default run
 	make -C column-unset-default run
 	make -C create-table run
@@ -75,6 +88,9 @@ run: 5.6 5.7 8.0
 	make -C index-create run
 	make -C primary-key-add run
 	make -C primary-key-drop run
+	make -C auto-increment-create run
+	make -C auto-increment-add run
+	make -C auto-increment-drop run
 
 .PHONY: build
 build: docker-build
diff --git a/integration/tests/mysql/auto-increment-add/Dockerfile b/integration/tests/mysql/auto-increment-add/Dockerfile
new file mode 100644
index 00000000..367f3cf1
--- /dev/null
+++ b/integration/tests/mysql/auto-increment-add/Dockerfile
@@ -0,0 +1,9 @@
+FROM mysql:8.0
+
+ENV MYSQL_USER=schemahero
+ENV MYSQL_PASSWORD=password
+ENV MYSQL_DATABASE=schemahero
+ENV MYSQL_RANDOM_ROOT_PASSWORD=1
+
+## Insert fixtures
+COPY ./fixtures.sql /docker-entrypoint-initdb.d/
diff --git a/integration/tests/mysql/auto-increment-add/Makefile b/integration/tests/mysql/auto-increment-add/Makefile
new file mode 100644
index 00000000..c90fccf3
--- /dev/null
+++ b/integration/tests/mysql/auto-increment-add/Makefile
@@ -0,0 +1,4 @@
+include ../common.mk
+
+TEST_NAME := mysql-auto-increment-add
+SPEC_FILE := ./specs/users.yaml
diff --git a/integration/tests/mysql/auto-increment-add/expect.sql b/integration/tests/mysql/auto-increment-add/expect.sql
new file mode 100644
index 00000000..3ec75373
--- /dev/null
+++ b/integration/tests/mysql/auto-increment-add/expect.sql
@@ -0,0 +1,1 @@
+alter table `users` modify column `id` int (11) not null auto_increment;
diff --git a/integration/tests/mysql/auto-increment-add/fixtures.sql b/integration/tests/mysql/auto-increment-add/fixtures.sql
new file mode 100644
index 00000000..b5effac8
--- /dev/null
+++ b/integration/tests/mysql/auto-increment-add/fixtures.sql
@@ -0,0 +1,3 @@
+create table users (
+  id integer primary key not null
+);
diff --git a/integration/tests/mysql/auto-increment-add/specs/users.yaml b/integration/tests/mysql/auto-increment-add/specs/users.yaml
new file mode 100644
index 00000000..d528777d
--- /dev/null
+++ b/integration/tests/mysql/auto-increment-add/specs/users.yaml
@@ -0,0 +1,12 @@
+database: schemahero
+name: users
+schema:
+  mysql:
+    primaryKey: [id]
+    columns:
+      - name: id
+        type: integer
+        constraints:
+          notNull: true
+        attributes:
+          autoIncrement: true
diff --git a/integration/tests/mysql/auto-increment-create/Dockerfile b/integration/tests/mysql/auto-increment-create/Dockerfile
new file mode 100644
index 00000000..367f3cf1
--- /dev/null
+++ b/integration/tests/mysql/auto-increment-create/Dockerfile
@@ -0,0 +1,9 @@
+FROM mysql:8.0
+
+ENV MYSQL_USER=schemahero
+ENV MYSQL_PASSWORD=password
+ENV MYSQL_DATABASE=schemahero
+ENV MYSQL_RANDOM_ROOT_PASSWORD=1
+
+## Insert fixtures
+COPY ./fixtures.sql /docker-entrypoint-initdb.d/
diff --git a/integration/tests/mysql/auto-increment-create/Makefile b/integration/tests/mysql/auto-increment-create/Makefile
new file mode 100644
index 00000000..bfdded4c
--- /dev/null
+++ b/integration/tests/mysql/auto-increment-create/Makefile
@@ -0,0 +1,4 @@
+include ../common.mk
+
+TEST_NAME := mysql-auto-increment-create
+SPEC_FILE := ./specs/users.yaml
diff --git a/integration/tests/mysql/auto-increment-create/expect.sql b/integration/tests/mysql/auto-increment-create/expect.sql
new file mode 100644
index 00000000..31e6cffd
--- /dev/null
+++ b/integration/tests/mysql/auto-increment-create/expect.sql
@@ -0,0 +1,1 @@
+create table `users` (`id` int (11) not null auto_increment, primary key (`id`));
diff --git a/integration/tests/mysql/auto-increment-create/fixtures.sql b/integration/tests/mysql/auto-increment-create/fixtures.sql
new file mode 100644
index 00000000..e69de29b
diff --git a/integration/tests/mysql/auto-increment-create/specs/users.yaml b/integration/tests/mysql/auto-increment-create/specs/users.yaml
new file mode 100644
index 00000000..d528777d
--- /dev/null
+++ b/integration/tests/mysql/auto-increment-create/specs/users.yaml
@@ -0,0 +1,12 @@
+database: schemahero
+name: users
+schema:
+  mysql:
+    primaryKey: [id]
+    columns:
+      - name: id
+        type: integer
+        constraints:
+          notNull: true
+        attributes:
+          autoIncrement: true
diff --git a/integration/tests/mysql/auto-increment-drop/Dockerfile b/integration/tests/mysql/auto-increment-drop/Dockerfile
new file mode 100644
index 00000000..367f3cf1
--- /dev/null
+++ b/integration/tests/mysql/auto-increment-drop/Dockerfile
@@ -0,0 +1,9 @@
+FROM mysql:8.0
+
+ENV MYSQL_USER=schemahero
+ENV MYSQL_PASSWORD=password
+ENV MYSQL_DATABASE=schemahero
+ENV MYSQL_RANDOM_ROOT_PASSWORD=1
+
+## Insert fixtures
+COPY ./fixtures.sql /docker-entrypoint-initdb.d/
diff --git a/integration/tests/mysql/auto-increment-drop/Makefile b/integration/tests/mysql/auto-increment-drop/Makefile
new file mode 100644
index 00000000..1a885339
--- /dev/null
+++ b/integration/tests/mysql/auto-increment-drop/Makefile
@@ -0,0 +1,4 @@
+include ../common.mk
+
+TEST_NAME := mysql-auto-increment-drop
+SPEC_FILE := ./specs/users.yaml
diff --git a/integration/tests/mysql/auto-increment-drop/expect.sql b/integration/tests/mysql/auto-increment-drop/expect.sql
new file mode 100644
index 00000000..125a0ac6
--- /dev/null
+++ b/integration/tests/mysql/auto-increment-drop/expect.sql
@@ -0,0 +1,1 @@
+alter table `users` modify column `id` int (11) not null;
diff --git a/integration/tests/mysql/auto-increment-drop/fixtures.sql b/integration/tests/mysql/auto-increment-drop/fixtures.sql
new file mode 100644
index 00000000..b08b39d8
--- /dev/null
+++ b/integration/tests/mysql/auto-increment-drop/fixtures.sql
@@ -0,0 +1,3 @@
+create table users (
+  id integer primary key not null auto_increment
+);
diff --git a/integration/tests/mysql/auto-increment-drop/specs/users.yaml b/integration/tests/mysql/auto-increment-drop/specs/users.yaml
new file mode 100644
index 00000000..328cb215
--- /dev/null
+++ b/integration/tests/mysql/auto-increment-drop/specs/users.yaml
@@ -0,0 +1,10 @@
+database: schemahero
+name: users
+schema:
+  mysql:
+    primaryKey: [id]
+    columns:
+      - name: id
+        type: integer
+        constraints:
+          notNull: true
diff --git a/integration/tests/mysql/common.mk b/integration/tests/mysql/common.mk
index aa98d68f..dbb9a1cf 100644
--- a/integration/tests/mysql/common.mk
+++ b/integration/tests/mysql/common.mk
@@ -21,6 +21,9 @@ run:
 	@echo Verifying results for $(TEST_NAME)
 	diff -B expect.sql out.sql
 
+	# Apply
+	../../../../bin/kubectl-schemahero apply --driver=$(DRIVER) --uri="$(URI)" --ddl out.sql
+
 	# Cleanup
 	@-sleep 5
 	# rm ./out.sql
diff --git a/integration/tests/mysql/medium-blob/Dockerfile b/integration/tests/mysql/medium-blob/Dockerfile
new file mode 100644
index 00000000..367f3cf1
--- /dev/null
+++ b/integration/tests/mysql/medium-blob/Dockerfile
@@ -0,0 +1,9 @@
+FROM mysql:8.0
+
+ENV MYSQL_USER=schemahero
+ENV MYSQL_PASSWORD=password
+ENV MYSQL_DATABASE=schemahero
+ENV MYSQL_RANDOM_ROOT_PASSWORD=1
+
+## Insert fixtures
+COPY ./fixtures.sql /docker-entrypoint-initdb.d/
diff --git a/integration/tests/mysql/medium-blob/Makefile b/integration/tests/mysql/medium-blob/Makefile
new file mode 100644
index 00000000..35c18c20
--- /dev/null
+++ b/integration/tests/mysql/medium-blob/Makefile
@@ -0,0 +1,4 @@
+include ../common.mk
+
+TEST_NAME := mysql-mediumblob
+SPEC_FILE := ./specs/mediumblob.yaml
diff --git a/integration/tests/mysql/medium-blob/expect.sql b/integration/tests/mysql/medium-blob/expect.sql
new file mode 100644
index 00000000..e69de29b
diff --git a/integration/tests/mysql/medium-blob/fixtures.sql b/integration/tests/mysql/medium-blob/fixtures.sql
new file mode 100644
index 00000000..1ed97c63
--- /dev/null
+++ b/integration/tests/mysql/medium-blob/fixtures.sql
@@ -0,0 +1,4 @@
+create table medblob (
+  id integer primary key not null,
+  column_a mediumblob
+);
diff --git a/integration/tests/mysql/medium-blob/specs/mediumblob.yaml b/integration/tests/mysql/medium-blob/specs/mediumblob.yaml
new file mode 100644
index 00000000..9c845981
--- /dev/null
+++ b/integration/tests/mysql/medium-blob/specs/mediumblob.yaml
@@ -0,0 +1,11 @@
+database: schemahero
+name: medblob
+requires: []
+schema:
+  mysql:
+    primaryKey: [id]
+    columns:
+      - name: id
+        type: integer
+      - name: column_a
+        type: mediumblob
\ No newline at end of file
diff --git a/integration/tests/postgres/common.mk b/integration/tests/postgres/common.mk
index bc34064a..7c1b4274 100644
--- a/integration/tests/postgres/common.mk
+++ b/integration/tests/postgres/common.mk
@@ -21,6 +21,9 @@ run:
 	@echo Verifying results for $(TEST_NAME)
 	diff -B expect.sql out.sql
 
+	# Apply
+	../../../../bin/kubectl-schemahero apply --driver=$(DRIVER) --uri="$(URI)" --ddl out.sql
+
 	# Cleanup
 	@-sleep 5
 	rm ./out.sql
diff --git a/pkg/database/mysql/alias_test.go b/pkg/database/mysql/alias_test.go
index 799a0528..39078ffe 100644
--- a/pkg/database/mysql/alias_test.go
+++ b/pkg/database/mysql/alias_test.go
@@ -71,6 +71,36 @@ func Test_unaliasUnparameterizedColumnType(t *testing.T) {
 			requestedType:         "longtext (4294967295)",
 			expectedUnaliasedType: "longtext",
 		},
+		{
+			name:                  "tinyblob",
+			requestedType:         "tinyblob",
+			expectedUnaliasedType: "tinyblob",
+		},
+		{
+			name:                  "tinyblob",
+			requestedType:         "tinyblob (255)",
+			expectedUnaliasedType: "tinyblob",
+		},
+		{
+			name:                  "mediumblob",
+			requestedType:         "mediumblob",
+			expectedUnaliasedType: "mediumblob",
+		},
+		{
+			name:                  "mediumblob",
+			requestedType:         "mediumblob (16777215)",
+			expectedUnaliasedType: "mediumblob",
+		},
+		{
+			name:                  "longblob",
+			requestedType:         "longblob",
+			expectedUnaliasedType: "longblob",
+		},
+		{
+			name:                  "longblob (4294967295)",
+			requestedType:         "longblob (4294967295)",
+			expectedUnaliasedType: "longblob",
+		},
 	}
 
 	for _, test := range tests {
diff --git a/pkg/database/mysql/alter_test.go b/pkg/database/mysql/alter_test.go
index b4fb6ba0..924bf8de 100644
--- a/pkg/database/mysql/alter_test.go
+++ b/pkg/database/mysql/alter_test.go
@@ -278,6 +278,58 @@ func Test_AlterColumnStatment(t *testing.T) {
 				"alter table `t` modify column `a` varchar (255) not null default \"11\"",
 			},
 		},
+		{
+			name:      "add auto_increment attibute",
+			tableName: "t",
+			desiredColumns: []*schemasv1alpha4.SQLTableColumn{
+				{
+					Name: "c",
+					Type: "integer",
+					Constraints: &schemasv1alpha4.SQLTableColumnConstraints{
+						NotNull: &trueValue,
+					},
+					Attributes: &schemasv1alpha4.SQLTableColumnAttributes{
+						AutoIncrement: &trueValue,
+					},
+				},
+			},
+			existingColumn: &types.Column{
+				Name:     "c",
+				DataType: "integer",
+				Constraints: &types.ColumnConstraints{
+					NotNull: &trueValue,
+				},
+			},
+			expectedStatements: []string{
+				"alter table `t` modify column `c` int (11) not null auto_increment",
+			},
+		},
+		{
+			name:      "drop auto_increment attibute",
+			tableName: "t",
+			desiredColumns: []*schemasv1alpha4.SQLTableColumn{
+				{
+					Name: "c",
+					Type: "integer",
+					Constraints: &schemasv1alpha4.SQLTableColumnConstraints{
+						NotNull: &trueValue,
+					},
+				},
+			},
+			existingColumn: &types.Column{
+				Name:     "c",
+				DataType: "integer",
+				Constraints: &types.ColumnConstraints{
+					NotNull: &trueValue,
+				},
+				Attributes: &types.ColumnAttributes{
+					AutoIncrement: &trueValue,
+				},
+			},
+			expectedStatements: []string{
+				"alter table `t` modify column `c` int (11) not null",
+			},
+		},
 	}
 
 	for _, test := range tests {
diff --git a/pkg/database/mysql/column_test.go b/pkg/database/mysql/column_test.go
index a9b1c06e..421e1726 100644
--- a/pkg/database/mysql/column_test.go
+++ b/pkg/database/mysql/column_test.go
@@ -29,6 +29,20 @@ func Test_mysqlColumnAsInsert(t *testing.T) {
 			},
 			expectedStatement: "`c` int (11) not null default '11'",
 		},
+		{
+			name: "auto_increment",
+			column: &schemasv1alpha4.SQLTableColumn{
+				Name: "c",
+				Type: "integer",
+				Constraints: &schemasv1alpha4.SQLTableColumnConstraints{
+					NotNull: &trueValue,
+				},
+				Attributes: &schemasv1alpha4.SQLTableColumnAttributes{
+					AutoIncrement: &trueValue,
+				},
+			},
+			expectedStatement: "`c` int (11) not null auto_increment",
+		},
 	}
 
 	for _, test := range tests {
@@ -82,6 +96,18 @@ func Test_InsertColumnStatement(t *testing.T) {
 			},
 			expectedStatement: "alter table `t` add column `a` int (11) null",
 		},
+		{
+			name:      "add auto_increment column",
+			tableName: "t",
+			desiredColumn: &schemasv1alpha4.SQLTableColumn{
+				Name: "a",
+				Type: "integer",
+				Attributes: &schemasv1alpha4.SQLTableColumnAttributes{
+					AutoIncrement: &trueValue,
+				},
+			},
+			expectedStatement: "alter table `t` add column `a` int (11) auto_increment",
+		},
 	}
 
 	for _, test := range tests {
@@ -125,6 +151,23 @@ func Test_schemaColumnToMysqlColumn(t *testing.T) {
 				ColumnDefault: nil,
 			},
 		},
+		{
+			name: "auto_increment",
+			schemaColumn: &schemasv1alpha4.SQLTableColumn{
+				Name: "c",
+				Type: "integer (11)",
+				Attributes: &schemasv1alpha4.SQLTableColumnAttributes{
+					AutoIncrement: &trueValue,
+				},
+			},
+			expectedColumn: &types.Column{
+				Name:     "c",
+				DataType: "int (11)",
+				Attributes: &types.ColumnAttributes{
+					AutoIncrement: &trueValue,
+				},
+			},
+		},
 	}
 
 	for _, test := range tests {
diff --git a/pkg/database/types/column_test.go b/pkg/database/types/column_test.go
index afdf41a6..149f322b 100644
--- a/pkg/database/types/column_test.go
+++ b/pkg/database/types/column_test.go
@@ -2,7 +2,7 @@ package types
 
 import "testing"
 
-func TestNotNullConstraintEquals(t *testing.T) {
+func TestBoolsEqual(t *testing.T) {
 	falseValue := false
 	trueValue := true
 	tests := []struct {
@@ -68,8 +68,8 @@ func TestNotNullConstraintEquals(t *testing.T) {
 	}
 	for _, tt := range tests {
 		t.Run(tt.name, func(t *testing.T) {
-			if got := NotNullConstraintEquals(tt.a, tt.b); got != tt.want {
-				t.Errorf("NotNullConstraintEquals() = %v, want %v", got, tt.want)
+			if got := BoolsEqual(tt.a, tt.b); got != tt.want {
+				t.Errorf("BoolsEqual() = %v, want %v", got, tt.want)
 			}
 		})
 	}
diff --git a/pkg/generate/generate_test.go b/pkg/generate/generate_test.go
index 94e24786..934fb659 100644
--- a/pkg/generate/generate_test.go
+++ b/pkg/generate/generate_test.go
@@ -124,7 +124,7 @@ spec:
 			driver:      "postgres",
 			dbName:      "db",
 			tableName:   "simple",
-			primaryKey:  []string{"one"},
+			primaryKey:  []string{"id"},
 			foreignKeys: []*types.ForeignKey{},
 			indexes: []*types.Index{
 				&types.Index{
@@ -156,7 +156,7 @@ spec:
   schema:
     postgres:
       primaryKey:
-      - one
+      - id
       indexes:
       - columns:
         - other
@@ -169,6 +169,39 @@ spec:
         type: varchar (255)
         constraints:
           notNull: true
+`,
+		},
+		{
+			name:       "generating with auto_increment",
+			driver:     "mysql",
+			dbName:     "db",
+			tableName:  "simple",
+			primaryKey: []string{"id"},
+			columns: []*types.Column{
+				&types.Column{
+					Name:     "id",
+					DataType: "integer",
+					Attributes: &types.ColumnAttributes{
+						AutoIncrement: &trueValue,
+					},
+				},
+			},
+			expectedYAML: `apiVersion: schemas.schemahero.io/v1alpha4
+kind: Table
+metadata:
+  name: simple
+spec:
+  database: db
+  name: simple
+  schema:
+    mysql:
+      primaryKey:
+      - id
+      columns:
+      - name: id
+        type: integer
+        attributes:
+          autoIncrement: true
 `,
 		},
 	}