cockroachdb / cockroachdb/cockroach
sql/schemachanger: implement CREATE VIEW in the declarative schema changer
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
## Summary
Implement support for regular (non-materialized, non-temporary) CREATE VIEW in the declarative schema changer. DROP VIEW is already implemented in the DSC. This adds CREATE VIEW support so that views can be created through the declarative path.
## Scope
- Regular views only (non-materialized, non-temporary).
- No CREATE OR REPLACE VIEW.
- Enabled by default (`on: true`) in `supportedStatements`.
## Implementation Plan
### New elements
- `ViewQuery` element in `elements.proto` to carry the view query text and forward references (parallels `FunctionBody`).
### New operations
- `CreateViewDescriptor` — creates a minimal table descriptor marked as a view.
- `SetViewQuery` — sets the `ViewQuery` on the view descriptor.
- `UpdateViewBackReferencesInRelations` / `UpdateViewBackReferencesInTypes` / `UpdateViewBackReferencesInRoutines` — manages back-references.
### Build phase
- New `create_view.go` build handler that resolves the target schema, builds a reference provider via the optimizer, validates cross-database references, and emits all needed elements (View, ViewQuery, Namespace, SchemaChild, Column, ColumnName, ColumnType, Owner, UserPrivileges).
### ReferenceProvider extension
- Extend `ReferenceProvider` to handle `*memo.CreateViewExpr` in `reference_provider.go`, extracting the view query text, result columns, and dependencies.
### OpGen rules
- Replace the `NotImplemented` stub in `opgen_view.go` with proper `CreateViewDescriptor` + `MarkDescriptorAsPublic` transitions.
- New `opgen_view_query.go` for `ViewQuery` element transitions.
### Dependency rules
- `View:DESCRIPTOR_ADDED` precedes `ViewQuery:PUBLIC`.
- `ViewQuery:PUBLIC` precedes `View:PUBLIC`.
### Execution layer
- New `view.go` in `scmutationexec` implementing `CreateViewDescriptor`, `SetViewQuery`, and the back-reference update ops.
### Decomposition
- Emit `ViewQuery` element during decomposition of existing views for round-trip correctness.
## Key Files
### New Files
- `pkg/sql/schemachanger/scbuild/internal/scbuildstmt/create_view.go`
- `pkg/sql/schemachanger/scplan/internal/opgen/opgen_view_query.go`
- `pkg/sql/schemachanger/scexec/scmutationexec/view.go`
### Modified Files
- `pkg/sql/schemachanger/scpb/elements.proto`
- `pkg/sql/schemachanger/scop/immediate_mutation.go`
- `pkg/sql/reference_provider.go`
- `pkg/sql/schemachanger/scbuild/internal/scbuildstmt/dependencies.go`
- `pkg/sql/schemachanger/scbuild/builder_state.go`
- `pkg/sql/schemachanger/scbuild/internal/scbuildstmt/process.go`
- `pkg/sql/schemachanger/scplan/internal/opgen/opgen_view.go`
- `pkg/sql/schemachanger/scdecomp/decomp.go`
Epic: CRDB-31466
Jira issue: CRDB-60703
Contributor guide
Assessment
This issue has not been assessed yet.