Repository: ariga/atlas
Pull request #883 (2022-06-20 19:58:24)

sql/postgres: support scanning and printing interval types


Patch:
diff --git a/doc/md/ddl/sql_types.md b/doc/md/ddl/sql_types.md
index 025a9dd8..534ab167 100644
--- a/doc/md/ddl/sql_types.md
+++ b/doc/md/ddl/sql_types.md
@@ -1052,6 +1052,78 @@ slug: /ddl/sql-types
             </td>
         </tr>
         
+        <tr>
+            <td>day</td>
+            <td>day</td>
+            <td>
+                <ul>
+                        <li>precision (int)</li>
+                </ul>
+            </td>
+            <td>
+                
+                <pre>
+                    type = day(10)
+                </pre>
+                
+                
+            </td>
+        </tr>
+        
+        <tr>
+            <td>day_to_hour</td>
+            <td>day_to_hour</td>
+            <td>
+                <ul>
+                        <li>precision (int)</li>
+                </ul>
+            </td>
+            <td>
+                
+                <pre>
+                    type = day_to_hour(10)
+                </pre>
+                
+                
+            </td>
+        </tr>
+        
+        <tr>
+            <td>day_to_minute</td>
+            <td>day_to_minute</td>
+            <td>
+                <ul>
+                        <li>precision (int)</li>
+                </ul>
+            </td>
+            <td>
+                
+                <pre>
+                    type = day_to_minute(10)
+                </pre>
+                
+                
+            </td>
+        </tr>
+        
+        <tr>
+            <td>day_to_second</td>
+            <td>day_to_second</td>
+            <td>
+                <ul>
+                        <li>precision (int)</li>
+                </ul>
+            </td>
+            <td>
+                
+                <pre>
+                    type = day_to_second(10)
+                </pre>
+                
+                
+            </td>
+        </tr>
+        
         <tr>
             <td>decimal</td>
             <td>decimal</td>
@@ -1143,6 +1215,60 @@ enum "my_enum" &#123;
             </td>
         </tr>
         
+        <tr>
+            <td>hour</td>
+            <td>hour</td>
+            <td>
+                <ul>
+                        <li>precision (int)</li>
+                </ul>
+            </td>
+            <td>
+                
+                <pre>
+                    type = hour(10)
+                </pre>
+                
+                
+            </td>
+        </tr>
+        
+        <tr>
+            <td>hour_to_minute</td>
+            <td>hour_to_minute</td>
+            <td>
+                <ul>
+                        <li>precision (int)</li>
+                </ul>
+            </td>
+            <td>
+                
+                <pre>
+                    type = hour_to_minute(10)
+                </pre>
+                
+                
+            </td>
+        </tr>
+        
+        <tr>
+            <td>hour_to_second</td>
+            <td>hour_to_second</td>
+            <td>
+                <ul>
+                        <li>precision (int)</li>
+                </ul>
+            </td>
+            <td>
+                
+                <pre>
+                    type = hour_to_second(10)
+                </pre>
+                
+                
+            </td>
+        </tr>
+        
         <tr>
             <td>hstore</td>
             <td>hstore</td>
@@ -1262,6 +1388,24 @@ enum "my_enum" &#123;
             </td>
         </tr>
         
+        <tr>
+            <td>interval</td>
+            <td>interval</td>
+            <td>
+                <ul>
+                        <li>precision (int)</li>
+                </ul>
+            </td>
+            <td>
+                
+                <pre>
+                    type = interval(10)
+                </pre>
+                
+                
+            </td>
+        </tr>
+        
         <tr>
             <td>json</td>
             <td>json</td>
@@ -1364,6 +1508,42 @@ enum "my_enum" &#123;
             </td>
         </tr>
         
+        <tr>
+            <td>minute</td>
+            <td>minute</td>
+            <td>
+                <ul>
+                        <li>precision (int)</li>
+                </ul>
+            </td>
+            <td>
+                
+                <pre>
+                    type = minute(10)
+                </pre>
+                
+                
+            </td>
+        </tr>
+        
+        <tr>
+            <td>minute_to_second</td>
+            <td>minute_to_second</td>
+            <td>
+                <ul>
+                        <li>precision (int)</li>
+                </ul>
+            </td>
+            <td>
+                
+                <pre>
+                    type = minute_to_second(10)
+                </pre>
+                
+                
+            </td>
+        </tr>
+        
         <tr>
             <td>money</td>
             <td>money</td>
@@ -1381,6 +1561,24 @@ enum "my_enum" &#123;
             </td>
         </tr>
         
+        <tr>
+            <td>month</td>
+            <td>month</td>
+            <td>
+                <ul>
+                        <li>precision (int)</li>
+                </ul>
+            </td>
+            <td>
+                
+                <pre>
+                    type = month(10)
+                </pre>
+                
+                
+            </td>
+        </tr>
+        
         <tr>
             <td>numeric</td>
             <td>numeric</td>
@@ -1449,6 +1647,24 @@ enum "my_enum" &#123;
             </td>
         </tr>
         
+        <tr>
+            <td>second</td>
+            <td>second</td>
+            <td>
+                <ul>
+                        <li>precision (int)</li>
+                </ul>
+            </td>
+            <td>
+                
+                <pre>
+                    type = second(10)
+                </pre>
+                
+                
+            </td>
+        </tr>
+        
         <tr>
             <td>serial</td>
             <td>serial</td>
@@ -1710,6 +1926,42 @@ enum "my_enum" &#123;
             </td>
         </tr>
         
+        <tr>
+            <td>year</td>
+            <td>year</td>
+            <td>
+                <ul>
+                        <li>precision (int)</li>
+                </ul>
+            </td>
+            <td>
+                
+                <pre>
+                    type = year(10)
+                </pre>
+                
+                
+            </td>
+        </tr>
+        
+        <tr>
+            <td>year_to_month</td>
+            <td>year_to_month</td>
+            <td>
+                <ul>
+                        <li>precision (int)</li>
+                </ul>
+            </td>
+            <td>
+                
+                <pre>
+                    type = year_to_month(10)
+                </pre>
+                
+                
+            </td>
+        </tr>
+        
     </tbody>
 </table>
 
diff --git a/schema/schemaspec/schemahcl/types.go b/schema/schemaspec/schemahcl/types.go
index a63769ab..326ce69e 100644
--- a/schema/schemaspec/schemahcl/types.go
+++ b/schema/schemaspec/schemahcl/types.go
@@ -204,6 +204,9 @@ func (r *TypeRegistry) Convert(typ schema.Type) (*schemaspec.Type, error) {
 	if !ok {
 		return r.spec(typ)
 	}
+	if typeSpec.ToSpec != nil {
+		return typeSpec.ToSpec(typ)
+	}
 	s := &schemaspec.Type{T: typeSpec.T}
 	// Iterate the attributes in reverse order, so we can skip zero value and optional attrs.
 	for i := len(typeSpec.Attributes) - 1; i >= 0; i-- {
@@ -289,6 +292,9 @@ func (r *TypeRegistry) Type(typ *schemaspec.Type, extra []*schemaspec.Attr) (sch
 		T: typ.T,
 	}
 	cp.Attrs = appendIfNotExist(typ.Attrs, picked)
+	if typeSpec.FromSpec != nil {
+		return typeSpec.FromSpec(cp)
+	}
 	printType, err := r.PrintType(cp)
 	if err != nil {
 		return nil, err
@@ -313,6 +319,20 @@ func WithTypeFormatter(f func(*schemaspec.Type) (string, error)) TypeSpecOption
 	}
 }
 
+// WithFromSpec allows configuring the FromSpec convert function using functional options.
+func WithFromSpec(f func(*schemaspec.Type) (schema.Type, error)) TypeSpecOption {
+	return func(spec *schemaspec.TypeSpec) {
+		spec.FromSpec = f
+	}
+}
+
+// WithToSpec allows configuring the ToSpec convert function using functional options.
+func WithToSpec(f func(schema.Type) (*schemaspec.Type, error)) TypeSpecOption {
+	return func(spec *schemaspec.TypeSpec) {
+		spec.ToSpec = f
+	}
+}
+
 // TypeSpec returns a TypeSpec with the provided name.
 func TypeSpec(name string, opts ...TypeSpecOption) *schemaspec.TypeSpec {
 	return AliasTypeSpec(name, name, opts...)
diff --git a/schema/schemaspec/spec.go b/schema/schemaspec/spec.go
index 43a7273d..c3e491e3 100644
--- a/schema/schemaspec/spec.go
+++ b/schema/schemaspec/spec.go
@@ -8,6 +8,8 @@ import (
 	"fmt"
 	"reflect"
 	"strconv"
+
+	"ariga.io/atlas/sql/schema"
 )
 
 type (
@@ -92,6 +94,16 @@ type (
 		// Format is an optional formatting function.
 		// If exists, it will be used instead the registry one.
 		Format func(*Type) (string, error)
+
+		// FromSpec is an optional function that can be attached
+		// to the type spec and allows converting the schema spec
+		// type to a schema type (from document to databse).
+		FromSpec func(*Type) (schema.Type, error)
+
+		// ToSpec is an optional function that can be attached
+		// to the type spec and allows converting the schema type
+		// to a schema spec type (from database to document).
+		ToSpec func(schema.Type) (*Type, error)
 	}
 
 	// TypeAttr describes an attribute of a TypeSpec, for example `varchar` fields
diff --git a/sql/postgres/diff.go b/sql/postgres/diff.go
index f4307735..ee98f593 100644
--- a/sql/postgres/diff.go
+++ b/sql/postgres/diff.go
@@ -221,7 +221,7 @@ func (d *diff) typeChanged(from, to *schema.Column) (bool, error) {
 	switch fromT := fromT.(type) {
 	case *schema.BinaryType, *schema.BoolType, *schema.DecimalType, *schema.FloatType,
 		*schema.IntegerType, *schema.JSONType, *schema.SpatialType, *schema.StringType,
-		*schema.TimeType, *BitType, *NetworkType, *UserDefinedType:
+		*schema.TimeType, *BitType, *IntervalType, *NetworkType, *UserDefinedType:
 		t1, err := FormatType(toT)
 		if err != nil {
 			return false, err
diff --git a/sql/postgres/sqlspec.go b/sql/postgres/sqlspec.go
index ed0596c8..e567c7c0 100644
--- a/sql/postgres/sqlspec.go
+++ b/sql/postgres/sqlspec.go
@@ -569,6 +569,48 @@ var TypeRegistry = schemahcl.NewRegistry(
 		schemahcl.TypeSpec("hstore"),
 		schemahcl.TypeSpec("sql", schemahcl.WithAttributes(&schemaspec.TypeAttr{Name: "def", Required: true, Kind: reflect.String})),
 	),
+	schemahcl.WithSpecs(func() (specs []*schemaspec.TypeSpec) {
+		for _, f := range []string{"interval", "year", "month", "day", "hour", "minute", "second", "year to month", "day to hour", "day to minute", "day to second", "hour to minute", "hour to second", "minute to second"} {
+			specs = append(
+				specs,
+				schemahcl.TypeSpec(
+					specutil.Var(f),
+					schemahcl.WithAttributes(precisionTypeAttr()),
+					schemahcl.WithToSpec(func(t schema.Type) (*schemaspec.Type, error) {
+						i, ok := t.(*IntervalType)
+						if !ok {
+							return nil, fmt.Errorf("postgres: unexpected interval type %T", t)
+						}
+						spec := &schemaspec.Type{T: TypeInterval}
+						if i.F != "" {
+							spec.T = specutil.Var(strings.ToLower(i.F))
+						}
+						if p := i.Precision; p != nil && *p != defaultTimePrecision {
+							spec.Attrs = []*schemaspec.Attr{specutil.IntAttr("precision", *p)}
+						}
+						return spec, nil
+					}),
+					schemahcl.WithFromSpec(func(t *schemaspec.Type) (schema.Type, error) {
+						i := &IntervalType{T: TypeInterval}
+						if t.T != TypeInterval {
+							i.F = specutil.FromVar(t.T)
+						}
+						if a, ok := attr(t, "precision"); ok {
+							p, err := a.Int()
+							if err != nil {
+								return nil, fmt.Errorf(`postgres: parsing attribute "precision": %w`, err)
+							}
+							if p != defaultTimePrecision {
+								i.Precision = &p
+							}
+						}
+						return i, nil
+					}),
+				),
+			)
+		}
+		return specs
+	}()...),
 )
 
 func precisionTypeAttr() *schemaspec.TypeAttr {


Test patch:
diff --git a/sql/postgres/sqlspec_test.go b/sql/postgres/sqlspec_test.go
index b56b75b0..7f386a70 100644
--- a/sql/postgres/sqlspec_test.go
+++ b/sql/postgres/sqlspec_test.go
@@ -765,7 +765,7 @@ schema "test" {
 }
 
 func TestTypes(t *testing.T) {
-	// TODO(rotemtam) interval
+	p := func(i int) *int { return &i }
 	for _, tt := range []struct {
 		typeExpr string
 		expected schema.Type
@@ -910,6 +910,26 @@ func TestTypes(t *testing.T) {
 			typeExpr: "timestamptz(4)",
 			expected: typeTime(TypeTimestampTZ, 4),
 		},
+		{
+			typeExpr: "interval",
+			expected: &IntervalType{T: "interval"},
+		},
+		{
+			typeExpr: "interval(1)",
+			expected: &IntervalType{T: "interval", Precision: p(1)},
+		},
+		{
+			typeExpr: "second",
+			expected: &IntervalType{T: "interval", F: "second"},
+		},
+		{
+			typeExpr: "minute_to_second",
+			expected: &IntervalType{T: "interval", F: "minute to second"},
+		},
+		{
+			typeExpr: "minute_to_second(2)",
+			expected: &IntervalType{T: "interval", F: "minute to second", Precision: p(2)},
+		},
 		{
 			typeExpr: "real",
 			expected: &schema.FloatType{T: TypeReal, Precision: 24},