Repository: ariga/atlas
Pull request #2109 (2023-09-22 11:43:48)

sql: add support for materialized views in sqlspec
Adding another type to HCL, `materialized`. The reason we don't use `view` is that, in different databases (PostgreSQL, MSSQL, DB2, etc.), these two don't have the same set of attributes and we want our LSP to enforce type safety without executing custom logic.


Patch:
diff --git a/sql/internal/specutil/convert.go b/sql/internal/specutil/convert.go
index b3acfc2a..45b138e7 100644
--- a/sql/internal/specutil/convert.go
+++ b/sql/internal/specutil/convert.go
@@ -41,9 +41,10 @@ type (
 type (
 	// ScanDoc represents a scanned HCL document.
 	ScanDoc struct {
-		Schemas []*sqlspec.Schema
-		Tables  []*sqlspec.Table
-		Views   []*sqlspec.View
+		Schemas      []*sqlspec.Schema
+		Tables       []*sqlspec.Table
+		Views        []*sqlspec.View
+		Materialized []*sqlspec.View
 	}
 	// ScanFuncs represents a set of scan functions
 	// used to convert the HCL document to the Realm.
@@ -53,6 +54,14 @@ type (
 	}
 )
 
+const (
+	typeView         = "view"
+	typeTable        = "table"
+	typeColumn       = "column"
+	typeSchema       = "schema"
+	typeMaterialized = "materialized"
+)
+
 // Scan populates the Realm from the schemas and table specs.
 func Scan(r *schema.Realm, doc *ScanDoc, funcs *ScanFuncs) error {
 	byName := make(map[string]*schema.Schema)
@@ -110,36 +119,74 @@ func Scan(r *schema.Realm, doc *ScanDoc, funcs *ScanFuncs) error {
 			viewDeps[v] = refs
 		}
 	}
+	for _, m := range doc.Materialized {
+		name, err := SchemaName(m.Schema)
+		if err != nil {
+			return fmt.Errorf("specutil: cannot extract schema name for materialized %q: %w", m.Name, err)
+		}
+		s, ok := byName[name]
+		if !ok {
+			return fmt.Errorf("specutil: schema %q not found for materialized %q", name, m.Name)
+		}
+		v, err := funcs.View(m, s)
+		if err != nil {
+			return fmt.Errorf("specutil: cannot convert materialized %q: %w", m.Name, err)
+		}
+		s.AddViews(v.SetMaterialized(true))
+		if deps, ok := m.Attr("depends_on"); ok {
+			refs, err := deps.Refs()
+			if err != nil {
+				return fmt.Errorf("specutil: expect list of references for attribute materialized.%s.depends_on: %w", m.Name, err)
+			}
+			viewDeps[v] = refs
+		}
+	}
 	// Link views' dependencies.
 	for v, refs := range viewDeps {
+		srcT := typeView
+		if v.Materialized() {
+			srcT = typeMaterialized
+		}
 		for i, r := range refs {
 			switch p, err := r.Path(); {
 			case err != nil:
-				return fmt.Errorf("specutil: extract reference for view.%s: %w", v.Name, err)
+				return fmt.Errorf("specutil: extract reference for %s.%s: %w", srcT, v.Name, err)
 			case len(p) == 0:
-				return fmt.Errorf("specutil: empty reference for view.%s", v.Name)
-			case p[0].T == "view":
-				q, n, err := viewName(r)
+				return fmt.Errorf("specutil: empty reference for %s.%s", srcT, v.Name)
+			case p[0].T == typeView:
+				q, n, err := refName(r, typeView)
 				if err != nil {
-					return fmt.Errorf("specutil: extract view name from view.%s.depends_on[%d]: %w", v.Name, i, err)
+					return fmt.Errorf("specutil: extract view name from %s.%s.depends_on[%d]: %w", srcT, v.Name, i, err)
 				}
 				v1, err := findT(v.Schema, q, n, func(s *schema.Schema, name string) (*schema.View, bool) {
 					return s.View(name)
 				})
 				if err != nil {
-					return fmt.Errorf("specutil: find view refrence for view.%s.depends_on[%d]: %w", v.Name, i, err)
+					return fmt.Errorf("specutil: find view refrence for %s.%s.depends_on[%d]: %w", srcT, v.Name, i, err)
 				}
 				v.AddDeps(v1)
-			case p[0].T == "table":
-				q, n, err := tableName(r)
+			case p[0].T == typeMaterialized:
+				q, n, err := refName(r, typeMaterialized)
 				if err != nil {
-					return fmt.Errorf("specutil: extract view name from view.%s.depends_on[%d]: %w", v.Name, i, err)
+					return fmt.Errorf("specutil: extract materialized name from %s.%s.depends_on[%d]: %w", srcT, v.Name, i, err)
+				}
+				v1, err := findT(v.Schema, q, n, func(s *schema.Schema, name string) (*schema.View, bool) {
+					return s.Materialized(name)
+				})
+				if err != nil {
+					return fmt.Errorf("specutil: find materialized refrence for %s.%s.depends_on[%d]: %w", srcT, v.Name, i, err)
+				}
+				v.AddDeps(v1)
+			case p[0].T == typeTable:
+				q, n, err := refName(r, typeTable)
+				if err != nil {
+					return fmt.Errorf("specutil: extract table name from %s.%s.depends_on[%d]: %w", srcT, v.Name, i, err)
 				}
 				t1, err := findT(v.Schema, q, n, func(s *schema.Schema, name string) (*schema.Table, bool) {
 					return s.Table(name)
 				})
 				if err != nil {
-					return fmt.Errorf("specutil: find table refrence for view.%s.depends_on[%d]: %w", v.Name, i, err)
+					return fmt.Errorf("specutil: find table refrence for %s.%s.depends_on[%d]: %w", srcT, v.Name, i, err)
 				}
 				v.AddDeps(t1)
 			}
@@ -392,6 +439,7 @@ func FromSchema(s *schema.Schema, specT TableSpecFunc, specV ViewSpecFunc) (*Sch
 			Name: s.Name,
 		}
 		views  = make([]*sqlspec.View, 0, len(s.Views))
+		mviews = make([]*sqlspec.View, 0, len(s.Views))
 		tables = make([]*sqlspec.Table, 0, len(s.Tables))
 	)
 	for _, t := range s.Tables {
@@ -412,13 +460,18 @@ func FromSchema(s *schema.Schema, specT TableSpecFunc, specV ViewSpecFunc) (*Sch
 		if s.Name != "" {
 			view.Schema = SchemaRef(s.Name)
 		}
-		views = append(views, view)
+		if v.Materialized() {
+			mviews = append(mviews, view)
+		} else {
+			views = append(views, view)
+		}
 	}
 	convertCommentFromSchema(s.Attrs, &spec.Extra.Attrs)
 	return &SchemaSpec{
-		Schema: spec,
-		Tables: tables,
-		Views:  views,
+		Schema:       spec,
+		Tables:       tables,
+		Views:        views,
+		Materialized: mviews,
 	}, nil
 }
 
@@ -521,14 +574,18 @@ func FromView(v *schema.View, colFn ViewColumnSpecFunc) (*sqlspec.View, error) {
 				path = append(path, d.Schema.Name)
 			}
 			deps = append(deps, schemahcl.BuildRef([]schemahcl.PathIndex{
-				{T: "table", V: append(path, d.Name)},
+				{T: typeTable, V: append(path, d.Name)},
 			}))
 		case *schema.View:
+			vt := typeView
+			if d.Materialized() {
+				vt = typeMaterialized
+			}
 			if nameV[d.Name] > 1 {
 				path = append(path, d.Schema.Name)
 			}
 			deps = append(deps, schemahcl.BuildRef([]schemahcl.PathIndex{
-				{T: "view", V: append(path, d.Name)},
+				{T: vt, V: append(path, d.Name)},
 			}))
 		}
 	}
@@ -743,7 +800,7 @@ func FromCheck(s *schema.Check) *sqlspec.Check {
 
 // SchemaName returns the name from a ref to a schema.
 func SchemaName(ref *schemahcl.Ref) (string, error) {
-	vs, err := ref.ByType("schema")
+	vs, err := ref.ByType(typeSchema)
 	if err != nil {
 		return "", err
 	}
@@ -755,7 +812,7 @@ func SchemaName(ref *schemahcl.Ref) (string, error) {
 
 // ColumnByRef returns a column from the table by its reference.
 func ColumnByRef(t *schema.Table, ref *schemahcl.Ref) (*schema.Column, error) {
-	vs, err := ref.ByType("column")
+	vs, err := ref.ByType(typeColumn)
 	if err != nil {
 		return nil, err
 	}
@@ -822,24 +879,12 @@ func findT[T schema.View | schema.Table](sch *schema.Schema, qualifier, name str
 	}
 }
 
-func tableName(ref *schemahcl.Ref) (qualifier, name string, err error) {
-	vs, err := ref.ByType("table")
-	if err != nil {
-		return "", "", err
-	}
-	switch len(vs) {
-	case 1:
-		name = vs[0]
-	case 2:
-		qualifier, name = vs[0], vs[1]
-	default:
-		return "", "", fmt.Errorf("sqlspec: unexpected number of references in %q", vs)
-	}
-	return
+func tableName(ref *schemahcl.Ref) (string, string, error) {
+	return refName(ref, typeTable)
 }
 
-func viewName(ref *schemahcl.Ref) (qualifier, name string, err error) {
-	vs, err := ref.ByType("view")
+func refName(ref *schemahcl.Ref, typeName string) (qualifier, name string, err error) {
+	vs, err := ref.ByType(typeName)
 	if err != nil {
 		return "", "", err
 	}
@@ -861,28 +906,28 @@ func isLocalRef(r *schemahcl.Ref) bool {
 // ColumnRef returns the reference of a column by its name.
 func ColumnRef(cName string) *schemahcl.Ref {
 	return schemahcl.BuildRef([]schemahcl.PathIndex{
-		{T: "column", V: []string{cName}},
+		{T: typeColumn, V: []string{cName}},
 	})
 }
 
 func externalColRef(cName string, tName string) *schemahcl.Ref {
 	return schemahcl.BuildRef([]schemahcl.PathIndex{
-		{T: "table", V: []string{tName}},
-		{T: "column", V: []string{cName}},
+		{T: typeTable, V: []string{tName}},
+		{T: typeColumn, V: []string{cName}},
 	})
 }
 
 func qualifiedExternalColRef(cName, tName, sName string) *schemahcl.Ref {
 	return schemahcl.BuildRef([]schemahcl.PathIndex{
-		{T: "table", V: []string{sName, tName}},
-		{T: "column", V: []string{cName}},
+		{T: typeTable, V: []string{sName, tName}},
+		{T: typeColumn, V: []string{cName}},
 	})
 }
 
 // SchemaRef returns the schemahcl.Ref to the schema with the given name.
 func SchemaRef(name string) *schemahcl.Ref {
 	return schemahcl.BuildRef([]schemahcl.PathIndex{
-		{T: "schema", V: []string{name}},
+		{T: typeSchema, V: []string{name}},
 	})
 }
 
diff --git a/sql/internal/specutil/spec.go b/sql/internal/specutil/spec.go
index fdca87b7..8b5ccc3f 100644
--- a/sql/internal/specutil/spec.go
+++ b/sql/internal/specutil/spec.go
@@ -24,14 +24,16 @@ type (
 	// SchemaSpec is returned by driver convert functions to
 	// marshal a *schema.Schema into top-level spec objects.
 	SchemaSpec struct {
-		Schema *sqlspec.Schema
-		Tables []*sqlspec.Table
-		Views  []*sqlspec.View
+		Schema       *sqlspec.Schema
+		Tables       []*sqlspec.Table
+		Views        []*sqlspec.View
+		Materialized []*sqlspec.View
 	}
 	doc struct {
-		Tables  []*sqlspec.Table  `spec:"table"`
-		Views   []*sqlspec.View   `spec:"view"`
-		Schemas []*sqlspec.Schema `spec:"schema"`
+		Tables       []*sqlspec.Table  `spec:"table"`
+		Views        []*sqlspec.View   `spec:"view"`
+		Materialized []*sqlspec.View   `spec:"materialized"`
+		Schemas      []*sqlspec.Schema `spec:"schema"`
 	}
 )
 
@@ -47,6 +49,7 @@ func Marshal(v any, marshaler schemahcl.Marshaler, convertFunc func(*schema.Sche
 		}
 		d.Tables = spec.Tables
 		d.Views = spec.Views
+		d.Materialized = spec.Materialized
 		d.Schemas = []*sqlspec.Schema{spec.Schema}
 	case *schema.Realm:
 		for _, s := range s.Schemas {
@@ -56,6 +59,7 @@ func Marshal(v any, marshaler schemahcl.Marshaler, convertFunc func(*schema.Sche
 			}
 			d.Tables = append(d.Tables, spec.Tables...)
 			d.Views = append(d.Views, spec.Views...)
+			d.Materialized = spec.Materialized
 			d.Schemas = append(d.Schemas, spec.Schema)
 		}
 		if err := QualifyObjects(d.Tables); err != nil {
@@ -64,6 +68,9 @@ func Marshal(v any, marshaler schemahcl.Marshaler, convertFunc func(*schema.Sche
 		if err := QualifyObjects(d.Views); err != nil {
 			return nil, err
 		}
+		if err := QualifyObjects(d.Materialized); err != nil {
+			return nil, err
+		}
 		if err := QualifyReferences(d.Tables, s); err != nil {
 			return nil, err
 		}
diff --git a/sql/postgres/sqlspec.go b/sql/postgres/sqlspec.go
index a57590f1..7775123c 100644
--- a/sql/postgres/sqlspec.go
+++ b/sql/postgres/sqlspec.go
@@ -23,10 +23,11 @@ import (
 
 type (
 	doc struct {
-		Tables  []*sqlspec.Table  `spec:"table"`
-		Views   []*sqlspec.View   `spec:"view"`
-		Enums   []*Enum           `spec:"enum"`
-		Schemas []*sqlspec.Schema `spec:"schema"`
+		Tables       []*sqlspec.Table  `spec:"table"`
+		Views        []*sqlspec.View   `spec:"view"`
+		Materialized []*sqlspec.View   `spec:"materialized"`
+		Enums        []*Enum           `spec:"enum"`
+		Schemas      []*sqlspec.Schema `spec:"schema"`
 	}
 	// Enum holds a specification for an enum, that can be referenced as a column type.
 	Enum struct {
@@ -38,6 +39,15 @@ type (
 	}
 )
 
+// merge merges the doc d1 into d.
+func (d *doc) merge(d1 *doc) {
+	d.Tables = append(d.Tables, d1.Tables...)
+	d.Views = append(d.Views, d1.Views...)
+	d.Materialized = append(d.Materialized, d1.Materialized...)
+	d.Enums = append(d.Enums, d1.Enums...)
+	d.Schemas = append(d.Schemas, d1.Schemas...)
+}
+
 // Label returns the defaults label used for the enum resource.
 func (e *Enum) Label() string { return e.Name }
 
@@ -63,7 +73,7 @@ func evalSpec(p *hclparse.Parser, v any, input map[string]cty.Value) error {
 			return err
 		}
 		if err := specutil.Scan(v,
-			&specutil.ScanDoc{Schemas: d.Schemas, Tables: d.Tables, Views: d.Views},
+			&specutil.ScanDoc{Schemas: d.Schemas, Tables: d.Tables, Views: d.Views, Materialized: d.Materialized},
 			&specutil.ScanFuncs{Table: convertTable, View: convertView},
 		); err != nil {
 			return fmt.Errorf("specutil: failed converting to *schema.Realm: %w", err)
@@ -83,7 +93,7 @@ func evalSpec(p *hclparse.Parser, v any, input map[string]cty.Value) error {
 		}
 		r := &schema.Realm{}
 		if err := specutil.Scan(r,
-			&specutil.ScanDoc{Schemas: d.Schemas, Tables: d.Tables, Views: d.Views},
+			&specutil.ScanDoc{Schemas: d.Schemas, Tables: d.Tables, Views: d.Views, Materialized: d.Materialized},
 			&specutil.ScanFuncs{Table: convertTable, View: convertView},
 		); err != nil {
 			return err
@@ -105,25 +115,18 @@ func MarshalSpec(v any, marshaler schemahcl.Marshaler) ([]byte, error) {
 	var d doc
 	switch s := v.(type) {
 	case *schema.Schema:
-		var err error
-		doc, err := schemaSpec(s)
+		d1, err := schemaSpec(s)
 		if err != nil {
 			return nil, fmt.Errorf("specutil: failed converting schema to spec: %w", err)
 		}
-		d.Tables = doc.Tables
-		d.Views = doc.Views
-		d.Schemas = doc.Schemas
-		d.Enums = doc.Enums
+		d.merge(d1)
 	case *schema.Realm:
 		for _, s := range s.Schemas {
-			doc, err := schemaSpec(s)
+			d1, err := schemaSpec(s)
 			if err != nil {
 				return nil, fmt.Errorf("specutil: failed converting schema to spec: %w", err)
 			}
-			d.Tables = append(d.Tables, doc.Tables...)
-			d.Views = append(d.Views, doc.Views...)
-			d.Schemas = append(d.Schemas, doc.Schemas...)
-			d.Enums = append(d.Enums, doc.Enums...)
+			d.merge(d1)
 		}
 		if err := specutil.QualifyObjects(d.Tables); err != nil {
 			return nil, err
@@ -131,6 +134,9 @@ func MarshalSpec(v any, marshaler schemahcl.Marshaler) ([]byte, error) {
 		if err := specutil.QualifyObjects(d.Views); err != nil {
 			return nil, err
 		}
+		if err := specutil.QualifyObjects(d.Materialized); err != nil {
+			return nil, err
+		}
 		if err := specutil.QualifyObjects(d.Enums); err != nil {
 			return nil, err
 		}
@@ -147,6 +153,7 @@ var (
 	hclState = schemahcl.New(append(specOptions,
 		schemahcl.WithTypes("table.column.type", TypeRegistry.Specs()),
 		schemahcl.WithTypes("view.column.type", TypeRegistry.Specs()),
+		schemahcl.WithTypes("materialized.column.type", TypeRegistry.Specs()),
 		schemahcl.WithScopedEnums("view.check_option", schema.ViewCheckOptionLocal, schema.ViewCheckOptionCascaded),
 		schemahcl.WithScopedEnums("table.index.type", IndexTypeBTree, IndexTypeBRIN, IndexTypeHash, IndexTypeGIN, IndexTypeGiST, "GiST", IndexTypeSPGiST, "SPGiST"),
 		schemahcl.WithScopedEnums("table.partition.type", PartitionTypeRange, PartitionTypeList, PartitionTypeHash),
@@ -560,10 +567,11 @@ func schemaSpec(s *schema.Schema) (*doc, error) {
 		return nil, err
 	}
 	d := &doc{
-		Tables:  spec.Tables,
-		Views:   spec.Views,
-		Schemas: []*sqlspec.Schema{spec.Schema},
-		Enums:   make([]*Enum, 0, len(s.Objects)),
+		Tables:       spec.Tables,
+		Views:        spec.Views,
+		Materialized: spec.Materialized,
+		Schemas:      []*sqlspec.Schema{spec.Schema},
+		Enums:        make([]*Enum, 0, len(s.Objects)),
 	}
 	for _, o := range s.Objects {
 		if e, ok := o.(*schema.EnumType); ok {
diff --git a/sql/schema/dsl.go b/sql/schema/dsl.go
index 5178f900..440ab308 100644
--- a/sql/schema/dsl.go
+++ b/sql/schema/dsl.go
@@ -231,6 +231,12 @@ func NewView(name, def string) *View {
 	return &View{Name: name, Def: def}
 }
 
+// NewMaterializedView creates a new materialized View.
+func NewMaterializedView(name, def string) *View {
+	return NewView(name, def).
+		SetMaterialized(true)
+}
+
 // SetSchema sets the schema (named-database) of the view.
 func (v *View) SetSchema(s *Schema) *View {
 	v.Schema = s
diff --git a/sql/schema/schema.go b/sql/schema/schema.go
index 0ca1cfaf..d4971630 100644
--- a/sql/schema/schema.go
+++ b/sql/schema/schema.go
@@ -126,9 +126,19 @@ func (s *Schema) Table(name string) (*Table, bool) {
 
 // View returns the first view that matched the given name.
 func (s *Schema) View(name string) (*View, bool) {
-	for _, t := range s.Views {
-		if t.Name == name {
-			return t, true
+	for _, v := range s.Views {
+		if v.Name == name && !v.Materialized() {
+			return v, true
+		}
+	}
+	return nil, false
+}
+
+// Materialized returns the first materialized view that matched the given name.
+func (s *Schema) Materialized(name string) (*View, bool) {
+	for _, v := range s.Views {
+		if v.Name == name && v.Materialized() {
+			return v, true
 		}
 	}
 	return nil, false
@@ -174,6 +184,26 @@ func (t *Table) ForeignKey(symbol string) (*ForeignKey, bool) {
 	return nil, false
 }
 
+// Materialized reports if the view is materialized.
+func (v *View) Materialized() bool {
+	for _, a := range v.Attrs {
+		if _, ok := a.(*Materialized); ok {
+			return true
+		}
+	}
+	return false
+}
+
+// SetMaterialized reports if the view is materialized.
+func (v *View) SetMaterialized(b bool) *View {
+	if b {
+		ReplaceOrAppend(&v.Attrs, &Materialized{})
+	} else {
+		v.Attrs = RemoveAttr[*Materialized](v.Attrs)
+	}
+	return v
+}
+
 // Column returns the first column that matched the given name.
 func (v *View) Column(name string) (*Column, bool) {
 	for _, c := range v.Columns {
@@ -377,6 +407,12 @@ type (
 	ViewCheckOption struct {
 		V string // LOCAL, CASCADED, NONE, or driver specific.
 	}
+
+	// Materialized is a schema attribute that attached to views to indicates
+	// they are MATERIALIZED VIEWs.
+	Materialized struct {
+		Attr
+	}
 )
 
 // A list of known view check options.
diff --git a/sql/sqlspec/sqlspec.go b/sql/sqlspec/sqlspec.go
index 16ecc4a9..520bdabf 100644
--- a/sql/sqlspec/sqlspec.go
+++ b/sql/sqlspec/sqlspec.go
@@ -120,6 +120,7 @@ func (v *View) SchemaRef() *schemahcl.Ref { return v.Schema }
 
 func init() {
 	schemahcl.Register("view", &View{})
+	schemahcl.Register("materialized", &View{})
 	schemahcl.Register("table", &Table{})
 	schemahcl.Register("schema", &Schema{})
 }


Test patch:
diff --git a/sql/postgres/sqlspec_test.go b/sql/postgres/sqlspec_test.go
index d876eb5c..6bffdbfd 100644
--- a/sql/postgres/sqlspec_test.go
+++ b/sql/postgres/sqlspec_test.go
@@ -321,6 +321,7 @@ func TestMarshalViews(t *testing.T) {
 					schema.NewIntColumn("id", "id"),
 				).
 				SetComment("view comment"),
+			schema.NewMaterializedView("m1", "SELECT * FROM t1"),
 		)
 	s.AddViews(
 		schema.NewView("v4", "SELECT * FROM v2 JOIN t1 USING (id)").
@@ -328,6 +329,12 @@ func TestMarshalViews(t *testing.T) {
 				s.Views[1],
 				s.Tables[0],
 			),
+		schema.NewMaterializedView("m2", "SELECT * FROM t1").
+			AddDeps(
+				s.Views[1],
+				s.Views[3],
+				s.Tables[0],
+			),
 	)
 	buf, err := MarshalHCL(s)
 	require.NoError(t, err)
@@ -368,6 +375,15 @@ view "v4" {
   as         = "SELECT * FROM v2 JOIN t1 USING (id)"
   depends_on = [view.v2, table.t1]
 }
+materialized "m1" {
+  schema = schema.public
+  as     = "SELECT * FROM t1"
+}
+materialized "m2" {
+  schema     = schema.public
+  as         = "SELECT * FROM t1"
+  depends_on = [view.v2, materialized.m1, table.t1]
+}
 schema "public" {
 }
 `
@@ -386,6 +402,15 @@ view "v1" {
   schema = schema.public
   as     = "SELECT * FROM t2 WHERE id IS NOT NULL"
 }
+materialized "m1" {
+  schema = schema.public
+  as     = "SELECT * FROM t2 WHERE id IS NOT NULL"
+}
+materialized "m2" {
+  schema     = schema.public
+  as         = "SELECT * FROM multi"
+  depends_on = [view.v1, materialized.m1, table.t1]
+}
 view "v2" {
  schema = schema.public
  column "id" {
@@ -467,6 +492,12 @@ schema "other" {}
 			AddDeps(public.Views[0], public.Tables[0]),
 		schema.NewView("v4", "SELECT * FROM public.t2").
 			AddDeps(public.Tables[1]),
+		schema.NewMaterializedView("m1", "SELECT * FROM t2 WHERE id IS NOT NULL"),
+	)
+	m1, _ := public.Materialized("m1")
+	public.AddViews(
+		schema.NewMaterializedView("m2", "SELECT * FROM multi").
+			AddDeps(public.Views[0], m1, public.Tables[0]),
 	)
 	other.AddViews(
 		schema.NewView("v4", "SELECT * FROM other.t2").
@@ -2125,12 +2156,20 @@ func TestMarshalQualifiers(t *testing.T) {
 		s2 = schema.New("s2").
 			AddTables(
 				schema.NewTable("t1").AddColumns(schema.NewIntColumn("id", "int")),
+			).
+			AddViews(
+				schema.NewMaterializedView("m1", "SELECT id FROM s2.t1"),
 			)
 		s3 = schema.New("s3").
 			AddTables(
 				schema.NewTable("s1").AddColumns(schema.NewIntColumn("id", "int")),
+			).
+			AddViews(
+				schema.NewMaterializedView("m1", "SELECT id FROM s3.t1"),
 			)
 	)
+	s2.Views[0].AddDeps(s2.Tables[0])
+	s3.Views[0].AddDeps(s3.Tables[0])
 	buf, err := MarshalHCL.MarshalSpec(schema.NewRealm(s1, s2, s3))
 	require.NoError(t, err)
 	require.Equal(t, `table "s1" "t1" {
@@ -2154,6 +2193,16 @@ table "s3" "s1" {
     type = int
   }
 }
+materialized "s2" "m1" {
+  schema     = schema.s2
+  as         = "SELECT id FROM s2.t1"
+  depends_on = [table.s2.t1]
+}
+materialized "s3" "m1" {
+  schema     = schema.s3
+  as         = "SELECT id FROM s3.t1"
+  depends_on = [table.s1]
+}
 schema "s1" {
 }
 schema "s2" {