Repository: ariga/atlas
Pull request #342 (2021-12-26 12:56:04)

sql/sqlite: support literal expressions
In SQLite, almost any value is valid for DEFAULT. Use https://www.sqlite.org/syntax/literal-value.html to distinguish literals from raw expressions. 


Patch:
diff --git a/sql/mysql/inspect.go b/sql/mysql/inspect.go
index 8d6bc09c..4754d99e 100644
--- a/sql/mysql/inspect.go
+++ b/sql/mysql/inspect.go
@@ -587,7 +587,6 @@ func (i *inspect) myDefaultExpr(c *schema.Column, x, extra string) schema.Expr {
 	if i.supportsExprDefault() && strings.Contains(strings.ToLower(extra), defaultGen) {
 		return &schema.RawExpr{X: x}
 	}
-
 	switch c.Type.Type.(type) {
 	case *schema.BinaryType:
 		// MySQL v8 uses Hexadecimal representation.
diff --git a/sql/sqlite/diff.go b/sql/sqlite/diff.go
index 34455c17..7949cb1e 100644
--- a/sql/sqlite/diff.go
+++ b/sql/sqlite/diff.go
@@ -70,9 +70,10 @@ func (d *diff) typeChanged(from, to *schema.Column) (bool, error) {
 // defaultChanged reports if the a default value of a column
 // type was changed.
 func (d *diff) defaultChanged(from, to *schema.Column) bool {
-	d1, ok1 := from.Default.(*schema.RawExpr)
-	d2, ok2 := to.Default.(*schema.RawExpr)
-	return ok1 != ok2 || ok1 && d1.X != d2.X
+	if ok1, ok2 := from.Default != nil, to.Default != nil; !ok1 || !ok2 {
+		return ok1 != ok2
+	}
+	return defaultValue(from) != defaultValue(to)
 }
 
 // IndexAttrChanged reports if the index attributes were changed.
diff --git a/sql/sqlite/inspect.go b/sql/sqlite/inspect.go
index a8fd4bff..3b766d56 100644
--- a/sql/sqlite/inspect.go
+++ b/sql/sqlite/inspect.go
@@ -153,9 +153,7 @@ func (i *inspect) addColumn(t *schema.Table, rows *sql.Rows) error {
 		return err
 	}
 	if sqlx.ValidString(defaults) {
-		c.Default = &schema.RawExpr{
-			X: defaults.String,
-		}
+		c.Default = defaultExpr(defaults.String)
 	}
 	// TODO(a8m): extract collation from 'CREATE TABLE' statement.
 	t.Columns = append(t.Columns, c)
@@ -516,6 +514,35 @@ func columnParts(t string) []string {
 	return parts
 }
 
+func defaultExpr(x string) schema.Expr {
+	switch {
+	// Literals definition.
+	// https://www.sqlite.org/syntax/literal-value.html
+	case isBlob(x), isBool(x), isNumeric(x),
+		strings.HasPrefix(x, `"`) && strings.HasSuffix(x, `"`),
+		strings.HasPrefix(x, "'") && strings.HasSuffix(x, "'"):
+		return &schema.Literal{V: x}
+	default:
+		// We wrap the CURRENT_TIMESTAMP literals in raw-expressions
+		// as they are not parsable in most decoders.
+		return &schema.RawExpr{X: x}
+	}
+}
+
+// isNumeric reports if the given string is a valid numeric literal
+// according to https://www.sqlite.org/syntax/numeric-literal.html.
+func isNumeric(s string) bool {
+	// Hex digits.
+	if strings.HasPrefix(s, "0x") || strings.HasPrefix(s, "0X") {
+		// SQLite allows odd length hex string.
+		_, err := strconv.ParseUint(s[2:], 16, 64)
+		return err == nil
+	}
+	// Digits with optional exponent.
+	_, err := strconv.ParseFloat(s, 64)
+	return err == nil
+}
+
 // isNumber reports whether the string is a number (category N).
 func isNumber(s string) bool {
 	for _, r := range s {
@@ -526,6 +553,20 @@ func isNumber(s string) bool {
 	return true
 }
 
+// blob literals are hex strings preceded by 'x' (or 'X).
+func isBlob(s string) bool {
+	if (strings.HasPrefix(s, "x'") || strings.HasPrefix(s, "X'")) && strings.HasSuffix(s, "'") {
+		_, err := strconv.ParseUint(s[2:len(s)-1], 16, 64)
+		return err == nil
+	}
+	return false
+}
+
+func isBool(s string) bool {
+	_, err := strconv.ParseBool(s)
+	return err == nil
+}
+
 var reAutoinc = regexp.MustCompile(`(?i)PRIMARY\s+KEY\s+AUTOINCREMENT`)
 
 // autoinc checks if the table contains a "PRIMARY KEY AUTOINCREMENT" on its
diff --git a/sql/sqlite/migrate.go b/sql/sqlite/migrate.go
index 48a79695..551af287 100644
--- a/sql/sqlite/migrate.go
+++ b/sql/sqlite/migrate.go
@@ -170,15 +170,15 @@ func (s *state) column(b *sqlx.Builder, c *schema.Column) {
 		b.P("NOT")
 	}
 	b.P("NULL")
-	if x, ok := c.Default.(*schema.RawExpr); ok {
-		b.P("DEFAULT", x.X)
+	if c.Default != nil {
+		b.P("DEFAULT", defaultValue(c))
 	}
 	if sqlx.Has(c.Attrs, &AutoIncrement{}) {
 		b.P("PRIMARY KEY AUTOINCREMENT")
 	}
 }
 
-func (s *state) addIndexes(ctx context.Context, t *schema.Table, indexes ...*schema.Index) error {
+func (s *state) addIndexes(_ context.Context, t *schema.Table, indexes ...*schema.Index) error {
 	for _, idx := range indexes {
 		// PRIMARY KEY or UNIQUE columns automatically create indexes with the generated name.
 		// See: sqlite/build.c#sqlite3CreateIndex. Therefore, we ignore such PKs, but create
@@ -305,7 +305,7 @@ func (s *state) copyRows(from *schema.Table, to *schema.Table, changes []schema.
 			toC = append(toC, column.Name)
 			if !column.Type.Null && column.Default != nil && change.Change.Is(schema.ChangeNull|schema.ChangeDefault) {
 				fromC = append(fromC, fmt.Sprintf("IFNULL(`%[1]s`, ?) AS `%[1]s`", column.Name))
-				args = append(args, column.Default.(*schema.RawExpr).X)
+				args = append(args, defaultValue(column))
 			} else {
 				fromC = append(fromC, column.Name)
 			}
@@ -384,3 +384,14 @@ func Build(phrase string) *sqlx.Builder {
 	b := &sqlx.Builder{QuoteChar: '`'}
 	return b.P(phrase)
 }
+
+func defaultValue(c *schema.Column) string {
+	switch x := c.Default.(type) {
+	case *schema.Literal:
+		return x.V
+	case *schema.RawExpr:
+		return x.X
+	default:
+		panic(fmt.Sprintf("unexpected default value type: %T", x))
+	}
+}


Test patch:
diff --git a/internal/integration/sqlite_test.go b/internal/integration/sqlite_test.go
index 24c699c5..c939a75b 100644
--- a/internal/integration/sqlite_test.go
+++ b/internal/integration/sqlite_test.go
@@ -73,15 +73,15 @@ func TestSQLite_AddIndexedColumns(t *testing.T) {
 		usersT.Columns = append(usersT.Columns, &schema.Column{
 			Name:    "a",
 			Type:    &schema.ColumnType{Type: &schema.IntegerType{T: "integer"}, Null: true},
-			Default: &schema.RawExpr{X: "10"},
+			Default: &schema.Literal{V: "10"},
 		}, &schema.Column{
 			Name:    "b",
 			Type:    &schema.ColumnType{Type: &schema.IntegerType{T: "integer"}, Null: true},
-			Default: &schema.RawExpr{X: "20"},
+			Default: &schema.Literal{V: "20"},
 		}, &schema.Column{
 			Name:    "c",
 			Type:    &schema.ColumnType{Type: &schema.IntegerType{T: "integer"}, Null: true},
-			Default: &schema.RawExpr{X: "30"},
+			Default: &schema.Literal{V: "30"},
 		})
 		usersT.Indexes = append(usersT.Indexes, &schema.Index{
 			Unique: true,
@@ -172,14 +172,14 @@ func TestSQLite_AddColumns(t *testing.T) {
 		usersT.Columns = append(
 			usersT.Columns,
 			&schema.Column{Name: "null_int", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "integer"}, Null: true}},
-			&schema.Column{Name: "notnull_int", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "integer"}}, Default: &schema.RawExpr{X: "1"}},
+			&schema.Column{Name: "notnull_int", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "integer"}}, Default: &schema.Literal{V: "1"}},
 			&schema.Column{Name: "null_real", Type: &schema.ColumnType{Type: &schema.FloatType{T: "real"}, Null: true}},
-			&schema.Column{Name: "notnull_real", Type: &schema.ColumnType{Type: &schema.FloatType{T: "real"}}, Default: &schema.RawExpr{X: "1.0"}},
+			&schema.Column{Name: "notnull_real", Type: &schema.ColumnType{Type: &schema.FloatType{T: "real"}}, Default: &schema.Literal{V: "1.0"}},
 			&schema.Column{Name: "null_text", Type: &schema.ColumnType{Type: &schema.StringType{T: "text"}, Null: true}},
 			&schema.Column{Name: "notnull_text1", Type: &schema.ColumnType{Type: &schema.StringType{T: "text"}}, Default: &schema.RawExpr{X: "hello"}},
-			&schema.Column{Name: "notnull_text2", Type: &schema.ColumnType{Type: &schema.StringType{T: "text"}}, Default: &schema.RawExpr{X: "'hello'"}},
+			&schema.Column{Name: "notnull_text2", Type: &schema.ColumnType{Type: &schema.StringType{T: "text"}}, Default: &schema.Literal{V: "'hello'"}},
 			&schema.Column{Name: "null_blob", Type: &schema.ColumnType{Type: &schema.BinaryType{T: "blob"}, Null: true}},
-			&schema.Column{Name: "notnull_blob", Type: &schema.ColumnType{Type: &schema.BinaryType{T: "blob"}}, Default: &schema.RawExpr{X: "'blob'"}},
+			&schema.Column{Name: "notnull_blob", Type: &schema.ColumnType{Type: &schema.BinaryType{T: "blob"}}, Default: &schema.Literal{V: "'blob'"}},
 		)
 		changes := t.diff(t.loadUsers(), usersT)
 		require.Len(t, changes, 9)
@@ -225,13 +225,13 @@ func TestSQLite_ColumnInt(t *testing.T) {
 		liteRun(t, func(t *liteTest) {
 			usersT := &schema.Table{
 				Name:    "users",
-				Columns: []*schema.Column{{Name: "a", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "int"}}, Default: &schema.RawExpr{X: "1"}}},
+				Columns: []*schema.Column{{Name: "a", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "int"}}, Default: &schema.Literal{V: "1"}}},
 			}
 			t.migrate(&schema.AddTable{T: usersT})
 			t.dropTables(usersT.Name)
 			ensureNoChange(t, usersT)
 			for _, x := range []string{"2", "'3'", "10.1"} {
-				usersT.Columns[0].Default.(*schema.RawExpr).X = x
+				usersT.Columns[0].Default.(*schema.Literal).V = x
 				changes := t.diff(t.loadUsers(), usersT)
 				require.Len(t, changes, 1)
 				t.migrate(&schema.ModifyTable{T: usersT, Changes: changes})
@@ -446,190 +446,190 @@ create table atlas_types_sanity
 				{
 					Name: "tInteger",
 					Type: &schema.ColumnType{Type: &schema.IntegerType{T: "integer", Unsigned: false}, Raw: "integer(10)", Null: true},
-					Default: &schema.RawExpr{
-						X: "100",
+					Default: &schema.Literal{
+						V: "100",
 					},
 				},
 				{
 					Name: "tInt",
 					Type: &schema.ColumnType{Type: &schema.IntegerType{T: "int", Unsigned: false}, Raw: "int(10)", Null: true},
-					Default: &schema.RawExpr{
-						X: "100",
+					Default: &schema.Literal{
+						V: "100",
 					},
 				},
 				{
 					Name: "tTinyIny",
 					Type: &schema.ColumnType{Type: &schema.IntegerType{T: "tinyint", Unsigned: false}, Raw: "tinyint(10)", Null: true},
-					Default: &schema.RawExpr{
-						X: "100",
+					Default: &schema.Literal{
+						V: "100",
 					},
 				},
 				{
 					Name: "tSmallInt",
 					Type: &schema.ColumnType{Type: &schema.IntegerType{T: "smallint", Unsigned: false}, Raw: "smallint(10)", Null: true},
-					Default: &schema.RawExpr{
-						X: "100",
+					Default: &schema.Literal{
+						V: "100",
 					},
 				},
 				{
 					Name: "tMediumInt",
 					Type: &schema.ColumnType{Type: &schema.IntegerType{T: "mediumint", Unsigned: false}, Raw: "mediumint(10)", Null: true},
-					Default: &schema.RawExpr{
-						X: "100",
+					Default: &schema.Literal{
+						V: "100",
 					},
 				},
 				{
 					Name: "tIntegerBigInt",
 					Type: &schema.ColumnType{Type: &schema.IntegerType{T: "bigint", Unsigned: false}, Raw: "bigint(10)", Null: true},
-					Default: &schema.RawExpr{
-						X: "100",
+					Default: &schema.Literal{
+						V: "100",
 					},
 				},
 				{
 					Name: "tUnsignedBigInt",
 					Type: &schema.ColumnType{Type: &schema.IntegerType{T: "unsigned big int", Unsigned: false}, Raw: "unsigned big int(10)", Null: true},
-					Default: &schema.RawExpr{
-						X: "100",
+					Default: &schema.Literal{
+						V: "100",
 					},
 				},
 				{
 					Name: "tInt2",
 					Type: &schema.ColumnType{Type: &schema.IntegerType{T: "int2", Unsigned: false}, Raw: "int2(10)", Null: true},
-					Default: &schema.RawExpr{
-						X: "100",
+					Default: &schema.Literal{
+						V: "100",
 					},
 				},
 				{
 					Name: "tInt8",
 					Type: &schema.ColumnType{Type: &schema.IntegerType{T: "int8", Unsigned: false}, Raw: "int8(10)", Null: true},
-					Default: &schema.RawExpr{
-						X: "100",
+					Default: &schema.Literal{
+						V: "100",
 					},
 				},
 				{
 					Name: "tReal",
 					Type: &schema.ColumnType{Type: &schema.FloatType{T: "real", Precision: 0}, Raw: "real(10)", Null: true},
-					Default: &schema.RawExpr{
-						X: "100",
+					Default: &schema.Literal{
+						V: "100",
 					},
 				},
 				{
 					Name: "tDouble",
 					Type: &schema.ColumnType{Type: &schema.FloatType{T: "double", Precision: 0}, Raw: "double(10)", Null: true},
-					Default: &schema.RawExpr{
-						X: "100",
+					Default: &schema.Literal{
+						V: "100",
 					},
 				},
 				{
 					Name: "tDoublePrecision",
 					Type: &schema.ColumnType{Type: &schema.FloatType{T: "double precision", Precision: 0}, Raw: "double precision(10)", Null: true},
-					Default: &schema.RawExpr{
-						X: "100",
+					Default: &schema.Literal{
+						V: "100",
 					},
 				},
 				{
 					Name: "tFloat",
 					Type: &schema.ColumnType{Type: &schema.FloatType{T: "float", Precision: 0}, Raw: "float(10)", Null: true},
-					Default: &schema.RawExpr{
-						X: "100",
+					Default: &schema.Literal{
+						V: "100",
 					},
 				},
 				{
 					Name: "tText",
 					Type: &schema.ColumnType{Type: &schema.StringType{T: "text", Size: 10}, Raw: "text(10)", Null: false},
-					Default: &schema.RawExpr{
-						X: "'I am Text'",
+					Default: &schema.Literal{
+						V: "'I am Text'",
 					},
 				},
 				{
 					Name: "tCharacter",
 					Type: &schema.ColumnType{Type: &schema.StringType{T: "character", Size: 10}, Raw: "character(10)", Null: false},
-					Default: &schema.RawExpr{
-						X: "'I am Text'",
+					Default: &schema.Literal{
+						V: "'I am Text'",
 					},
 				},
 				{
 					Name: "tVarchar",
 					Type: &schema.ColumnType{Type: &schema.StringType{T: "varchar", Size: 10}, Raw: "varchar(10)", Null: false},
-					Default: &schema.RawExpr{
-						X: "'I am Text'",
+					Default: &schema.Literal{
+						V: "'I am Text'",
 					},
 				},
 				{
 					Name: "tVaryingCharacter",
 					Type: &schema.ColumnType{Type: &schema.StringType{T: "varying character", Size: 10}, Raw: "varying character(10)", Null: false},
-					Default: &schema.RawExpr{
-						X: "'I am Text'",
+					Default: &schema.Literal{
+						V: "'I am Text'",
 					},
 				},
 				{
 					Name: "tNchar",
 					Type: &schema.ColumnType{Type: &schema.StringType{T: "nchar", Size: 10}, Raw: "nchar(10)", Null: false},
-					Default: &schema.RawExpr{
-						X: "'I am Text'",
+					Default: &schema.Literal{
+						V: "'I am Text'",
 					},
 				},
 				{
 					Name: "tNativeCharacter",
 					Type: &schema.ColumnType{Type: &schema.StringType{T: "native character", Size: 10}, Raw: "native character(10)", Null: false},
-					Default: &schema.RawExpr{
-						X: "'I am Text'",
+					Default: &schema.Literal{
+						V: "'I am Text'",
 					},
 				},
 				{
 					Name: "tNVarChar",
 					Type: &schema.ColumnType{Type: &schema.StringType{T: "nvarchar", Size: 10}, Raw: "nvarchar(10)", Null: false},
-					Default: &schema.RawExpr{
-						X: "'I am Text'",
+					Default: &schema.Literal{
+						V: "'I am Text'",
 					},
 				},
 				{
 					Name: "tClob",
 					Type: &schema.ColumnType{Type: &schema.StringType{T: "clob", Size: 10}, Raw: "clob(10)", Null: false},
-					Default: &schema.RawExpr{
-						X: "'I am Text'",
+					Default: &schema.Literal{
+						V: "'I am Text'",
 					},
 				},
 				{
 					Name: "tBlob",
 					Type: &schema.ColumnType{Type: &schema.BinaryType{T: "blob", Size: 0}, Raw: "blob(10)", Null: false},
-					Default: &schema.RawExpr{
-						X: "'A'",
+					Default: &schema.Literal{
+						V: "'A'",
 					},
 				},
 				{
 					Name: "tNumeric",
 					Type: &schema.ColumnType{Type: &schema.DecimalType{T: "numeric", Precision: 10}, Raw: "numeric(10)", Null: false},
-					Default: &schema.RawExpr{
-						X: "100",
+					Default: &schema.Literal{
+						V: "100",
 					},
 				},
 				{
 					Name: "tDecimal",
 					Type: &schema.ColumnType{Type: &schema.DecimalType{T: "decimal", Precision: 10, Scale: 5}, Raw: "decimal(10,5)", Null: false},
-					Default: &schema.RawExpr{
-						X: "100",
+					Default: &schema.Literal{
+						V: "100",
 					},
 				},
 				{
 					Name: "tBoolean",
 					Type: &schema.ColumnType{Type: &schema.BoolType{T: "boolean"}, Raw: "boolean", Null: false},
-					Default: &schema.RawExpr{
-						X: "false",
+					Default: &schema.Literal{
+						V: "false",
 					},
 				},
 				{
 					Name: "tDate",
 					Type: &schema.ColumnType{Type: &schema.TimeType{T: "date"}, Raw: "date", Null: false},
-					Default: &schema.RawExpr{
-						X: "'now()'",
+					Default: &schema.Literal{
+						V: "'now()'",
 					},
 				},
 				{
 					Name: "tDatetime",
 					Type: &schema.ColumnType{Type: &schema.TimeType{T: "datetime"}, Raw: "datetime", Null: false},
-					Default: &schema.RawExpr{
-						X: "'now()'",
+					Default: &schema.Literal{
+						V: "'now()'",
 					},
 				},
 			},
diff --git a/sql/sqlite/inspect_test.go b/sql/sqlite/inspect_test.go
index a65843af..76a2ed34 100644
--- a/sql/sqlite/inspect_test.go
+++ b/sql/sqlite/inspect_test.go
@@ -44,17 +44,17 @@ func TestDriver_InspectTable(t *testing.T) {
 					WillReturnRows(sqltest.Rows(`
  name |   type       | nullable | dflt_value  | primary 
 ------+--------------+----------+ ------------+----------
- c1   | int           |  1      |             |  0
- c2   | integer       |  0      |             |  0
- c3   | varchar(100)  |  1      |             |  0
+ c1   | int           |  1      |     a       |  0
+ c2   | integer       |  0      |     97      |  0
+ c3   | varchar(100)  |  1      |    'A'      |  0
  c4   | boolean       |  0      |             |  0
  c5   | json          |  0      |             |  0
  c6   | datetime      |  0      |             |  0
- c7   | blob          |  0      |             |  0
+ c7   | blob          |  0      |    x'a'     |  0
  c8   | text          |  0      |             |  0
  c9   | numeric(10,2) |  0      |             |  0
  c10  | real          |  0      |             |  0
- id   | integer       |  0      |             |  1
+ id   | integer       |  0      |     0x1     |  1
 `))
 				m.noIndexes("users")
 				m.noFKs("users")
@@ -62,17 +62,17 @@ func TestDriver_InspectTable(t *testing.T) {
 			expect: func(require *require.Assertions, t *schema.Table, err error) {
 				require.NoError(err)
 				columns := []*schema.Column{
-					{Name: "c1", Type: &schema.ColumnType{Null: true, Type: &schema.IntegerType{T: "int"}, Raw: "int"}},
-					{Name: "c2", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "integer"}, Raw: "integer"}},
-					{Name: "c3", Type: &schema.ColumnType{Null: true, Type: &schema.StringType{T: "varchar", Size: 100}, Raw: "varchar(100)"}},
+					{Name: "c1", Type: &schema.ColumnType{Null: true, Type: &schema.IntegerType{T: "int"}, Raw: "int"}, Default: &schema.RawExpr{X: "a"}},
+					{Name: "c2", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "integer"}, Raw: "integer"}, Default: &schema.Literal{V: "97"}},
+					{Name: "c3", Type: &schema.ColumnType{Null: true, Type: &schema.StringType{T: "varchar", Size: 100}, Raw: "varchar(100)"}, Default: &schema.Literal{V: "'A'"}},
 					{Name: "c4", Type: &schema.ColumnType{Type: &schema.BoolType{T: "boolean"}, Raw: "boolean"}},
 					{Name: "c5", Type: &schema.ColumnType{Type: &schema.JSONType{T: "json"}, Raw: "json"}},
 					{Name: "c6", Type: &schema.ColumnType{Type: &schema.TimeType{T: "datetime"}, Raw: "datetime"}},
-					{Name: "c7", Type: &schema.ColumnType{Type: &schema.BinaryType{T: "blob"}, Raw: "blob"}},
+					{Name: "c7", Type: &schema.ColumnType{Type: &schema.BinaryType{T: "blob"}, Raw: "blob"}, Default: &schema.Literal{V: "x'a'"}},
 					{Name: "c8", Type: &schema.ColumnType{Type: &schema.StringType{T: "text"}, Raw: "text"}},
 					{Name: "c9", Type: &schema.ColumnType{Type: &schema.DecimalType{T: "numeric", Precision: 10, Scale: 2}, Raw: "numeric(10,2)"}},
 					{Name: "c10", Type: &schema.ColumnType{Type: &schema.FloatType{T: "real"}, Raw: "real"}},
-					{Name: "id", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "integer"}, Raw: "integer"}, Attrs: []schema.Attr{AutoIncrement{}}},
+					{Name: "id", Type: &schema.ColumnType{Type: &schema.IntegerType{T: "integer"}, Raw: "integer"}, Attrs: []schema.Attr{AutoIncrement{}}, Default: &schema.Literal{V: "0x1"}},
 				}
 				require.Equal(t.Columns, columns)
 				require.EqualValues(&schema.Index{