Repository: ariga/atlas
Pull request #976 (2022-07-22 18:08:31)

sqlite: miscellaneous changes (doc, migrate, sqlspec) 
Review commits separately.


Patch:
diff --git a/doc/md/ddl/sql-types.md b/doc/md/ddl/sql-types.md
index e61bae78..5eaec165 100644
--- a/doc/md/ddl/sql-types.md
+++ b/doc/md/ddl/sql-types.md
@@ -697,4 +697,102 @@ table "t" {
     type = xml
   }
 }
+```
+
+## SQLite
+
+Values in SQLite are stored in one of the four native types: `BLOB`, `INTEGER`, `NULL`, `TEXT` and `REAL`. Still, Atlas
+supports variety of data types that are commonly used by ORMs. These types are mapped to column affinities based on
+the rules described in [SQLite website](https://www.sqlite.org/datatype3.html#type_affinity).
+
+### Blob
+
+The `blob` data type allows creating columns with `BLOB` type affinity.
+
+```hcl
+table "t" {
+  schema = schema.main
+  column "c" {
+    type = blob
+  }
+}
+```
+
+### Integer
+
+The `int` and `integer` data types allow creating columns with `INTEGER` type affinity.
+
+```hcl
+table "t" {
+  schema = schema.main
+  column "c" {
+    type = int
+  }
+}
+```
+
+### Numeric
+
+The `numeric` and `decimal` data types allow creating columns with `NUMERIC` type affinity.
+
+```hcl
+table "t" {
+  schema = schema.main
+  column "c" {
+    type = decimal
+  }
+}
+```
+
+### Text
+
+The `text`, `varchar`, `clob`, `character` and `varying_character` data types allow creating columns with `text` type
+affinity. i.e. stored as text strings.
+
+```hcl
+table "t" {
+  schema = schema.main
+  column "c" {
+    type = text
+  }
+}
+```
+
+### Real
+
+The `real`, `double`, `double_precision`, and `float` data types allow creating columns with `real` type
+affinity.
+
+```hcl
+table "t" {
+  schema = schema.main
+  column "c" {
+    type = real
+  }
+}
+```
+
+### Additional Types
+
+As mentioned above, Atlas supports variety of data types that are commonly used by ORMs. e.g. [Ent](https://entgo.io).
+
+```hcl
+table "t" {
+  schema = schema.main
+  column "c1" {
+    type = bool
+  }
+  column "c2" {
+    type = date 
+  }
+  column "c3" {
+    type = datetime
+  }
+  column "c4" {
+    type = uuid
+  }
+  column "c5" {
+    type = json
+  }
+}
 ```
\ No newline at end of file
diff --git a/sql/sqlite/convert.go b/sql/sqlite/convert.go
index 8ddf0c5e..18720237 100644
--- a/sql/sqlite/convert.go
+++ b/sql/sqlite/convert.go
@@ -49,15 +49,6 @@ func FormatType(t schema.Type) (string, error) {
 	return f, nil
 }
 
-// mustFormat calls to FormatType and panics in case of error.
-func mustFormat(t schema.Type) string {
-	s, err := FormatType(t)
-	if err != nil {
-		panic(err)
-	}
-	return s
-}
-
 // ParseType returns the schema.Type value represented by the given raw type.
 // It is expected to be one of the types in https://www.sqlite.org/datatypes.html,
 // or some of the common types used by ORMs like Ent.
diff --git a/sql/sqlite/migrate.go b/sql/sqlite/migrate.go
index 6b9b8fa8..745b144a 100644
--- a/sql/sqlite/migrate.go
+++ b/sql/sqlite/migrate.go
@@ -166,14 +166,20 @@ func (s *state) modifyTable(ctx context.Context, modify *schema.ModifyTable) err
 	if err := s.addTable(ctx, &schema.AddTable{T: &newT}); err != nil {
 		return err
 	}
-	if err := s.copyRows(modify.T, &newT, modify.Changes); err != nil {
+	copied, err := s.copyRows(modify.T, &newT, modify.Changes)
+	if err != nil {
 		return err
 	}
 	// Drop the current table, and rename the new one to its real name.
 	s.append(&migrate.Change{
-		Cmd:     Build("DROP TABLE").Ident(modify.T.Name).String(),
-		Source:  modify,
-		Comment: fmt.Sprintf("drop %q table after copying rows", modify.T.Name),
+		Cmd:    Build("DROP TABLE").Ident(modify.T.Name).String(),
+		Source: modify,
+		Comment: fmt.Sprintf("drop %q table %s", modify.T.Name, func() string {
+			if copied {
+				return "after copying rows"
+			}
+			return "without copying rows (no columns)"
+		}()),
 	})
 	s.append(&migrate.Change{
 		Cmd:     Build("ALTER TABLE").Ident(newT.Name).P("RENAME TO").Ident(modify.T.Name).String(),
@@ -325,7 +331,7 @@ func (s *state) fks(b *sqlx.Builder, fks ...*schema.ForeignKey) {
 	})
 }
 
-func (s *state) copyRows(from *schema.Table, to *schema.Table, changes []schema.Change) error {
+func (s *state) copyRows(from *schema.Table, to *schema.Table, changes []schema.Change) (bool, error) {
 	var (
 		args       []interface{}
 		fromC, toC []string
@@ -344,7 +350,7 @@ func (s *state) copyRows(from *schema.Table, to *schema.Table, changes []schema.
 					break
 				}
 				if change != nil {
-					return fmt.Errorf("duplicate changes for column: %q: %T, %T", column.Name, change, c)
+					return false, fmt.Errorf("duplicate changes for column: %q: %T, %T", column.Name, change, c)
 				}
 				change = changes[i]
 			case *schema.ModifyColumn:
@@ -352,12 +358,12 @@ func (s *state) copyRows(from *schema.Table, to *schema.Table, changes []schema.
 					break
 				}
 				if change != nil {
-					return fmt.Errorf("duplicate changes for column: %q: %T, %T", column.Name, change, c)
+					return false, fmt.Errorf("duplicate changes for column: %q: %T, %T", column.Name, change, c)
 				}
 				change = changes[i]
 			case *schema.DropColumn:
 				if c.C.Name == column.Name {
-					return fmt.Errorf("unexpected drop column: %q", column.Name)
+					return false, fmt.Errorf("unexpected drop column: %q", column.Name)
 				}
 			}
 		}
@@ -373,7 +379,7 @@ func (s *state) copyRows(from *schema.Table, to *schema.Table, changes []schema.
 				fromC = append(fromC, fmt.Sprintf("IFNULL(`%[1]s`, ?) AS `%[1]s`", column.Name))
 				x, err := defaultValue(column)
 				if err != nil {
-					return err
+					return false, err
 				}
 				args = append(args, x)
 			} else {
@@ -385,13 +391,18 @@ func (s *state) copyRows(from *schema.Table, to *schema.Table, changes []schema.
 			fromC = append(fromC, column.Name)
 		}
 	}
-	stmt := fmt.Sprintf("INSERT INTO `%s` (%s) SELECT %s FROM `%s`", to.Name, identComma(toC), identComma(fromC), from.Name)
-	s.append(&migrate.Change{
-		Cmd:     stmt,
-		Args:    args,
-		Comment: fmt.Sprintf("copy rows from old table %q to new temporary table %q", from.Name, to.Name),
-	})
-	return nil
+	insert := len(toC) > 0
+	if insert {
+		s.append(&migrate.Change{
+			Cmd: fmt.Sprintf(
+				"INSERT INTO `%s` (%s) SELECT %s FROM `%s`",
+				to.Name, identComma(toC), identComma(fromC), from.Name,
+			),
+			Args:    args,
+			Comment: fmt.Sprintf("copy rows from old table %q to new temporary table %q", from.Name, to.Name),
+		})
+	}
+	return insert, nil
 }
 
 // alterTable alters the table with the given changes. Assuming the changes are "alterable".
diff --git a/sql/sqlite/sqlspec.go b/sql/sqlite/sqlspec.go
index 44891637..0987d3b3 100644
--- a/sql/sqlite/sqlspec.go
+++ b/sql/sqlite/sqlspec.go
@@ -181,6 +181,7 @@ var TypeRegistry = schemahcl.NewRegistry(
 		schemahcl.NewTypeSpec("clob", schemahcl.WithAttributes(schemahcl.SizeTypeAttr(false))),
 		schemahcl.NewTypeSpec("numeric", schemahcl.WithAttributes(&schemahcl.TypeAttr{Name: "precision", Kind: reflect.Int, Required: false}, &schemahcl.TypeAttr{Name: "scale", Kind: reflect.Int, Required: false})),
 		schemahcl.NewTypeSpec("decimal", schemahcl.WithAttributes(&schemahcl.TypeAttr{Name: "precision", Kind: reflect.Int, Required: false}, &schemahcl.TypeAttr{Name: "scale", Kind: reflect.Int, Required: false})),
+		schemahcl.NewTypeSpec("bool"),
 		schemahcl.NewTypeSpec("boolean"),
 		schemahcl.NewTypeSpec("date"),
 		schemahcl.NewTypeSpec("datetime"),


Test patch:
diff --git a/sql/sqlite/migrate_test.go b/sql/sqlite/migrate_test.go
index 6831de7e..86e60095 100644
--- a/sql/sqlite/migrate_test.go
+++ b/sql/sqlite/migrate_test.go
@@ -223,6 +223,41 @@ func TestPlanChanges(t *testing.T) {
 				},
 			},
 		},
+		// Nothing to INSERT.
+		{
+			changes: []schema.Change{
+				func() schema.Change {
+					users := &schema.Table{
+						Name: "users",
+						Columns: []*schema.Column{
+							{Name: "c2", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "bigint"}}},
+						},
+					}
+					return &schema.ModifyTable{
+						T: users,
+						Changes: []schema.Change{
+							&schema.DropColumn{
+								C: &schema.Column{Name: "c1", Type: &schema.ColumnType{Type: &schema.StringType{T: "varchar(255)"}}},
+							},
+							&schema.AddColumn{
+								C: &schema.Column{Name: "c2", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "bigint"}}},
+							},
+						},
+					}
+				}(),
+			},
+			plan: &migrate.Plan{
+				Transactional: true,
+				Changes: []*migrate.Change{
+					{Cmd: "PRAGMA foreign_keys = off"},
+					{Cmd: "CREATE TABLE `new_users` (`c2` bigint NOT NULL)", Reverse: "DROP TABLE `new_users`"},
+					/* Nothing to INSERT from `users` as `c1` was dropped. */
+					{Cmd: "DROP TABLE `users`"},
+					{Cmd: "ALTER TABLE `new_users` RENAME TO `users`"},
+					{Cmd: "PRAGMA foreign_keys = on"},
+				},
+			},
+		},
 		{
 			changes: []schema.Change{
 				&schema.RenameTable{