Skip to content

CREATE VIEW statements shouldn't be sorted by name #35

@apstndb

Description

@apstndb

📝 Problem Summary

Output views are sorted, but it is possible that the view depends on another view.
Sorted DDL can't be submitted without modifications.

1️⃣ Base DDL

The base schema.

-- empty

2️⃣ Target DDL

The target schema you intended to migrate to.

CREATE TABLE table1 (PK INT64 PRIMARY KEY);

CREATE OR REPLACE VIEW table1_view_preprocess SQL SECURITY INVOKER AS SELECT t.PK, 1 AS n FROM table1 AS t;

CREATE OR REPLACE VIEW table1_view SQL SECURITY INVOKER AS SELECT v.PK, v.n, "foo" AS s FROM table1_view_preprocess AS v;

3️⃣ Generated Diff

The actual diff produced by the tool.

CREATE TABLE table1 (
  PK INT64 PRIMARY KEY
);

CREATE OR REPLACE VIEW table1_view SQL SECURITY INVOKER AS SELECT v.PK, v.n, "foo" AS s FROM table1_view_preprocess AS v;

CREATE OR REPLACE VIEW table1_view_preprocess SQL SECURITY INVOKER AS SELECT t.PK, 1 AS n FROM table1 AS t;

This ddls fail on UpdateDatabaseDdl.

error details: name = LocalizedMessage locale = en-US msg = Error parsing the definition of view `table1_view`: Table not found: table1_view_preprocess [at 1:83]
...INVOKER AS SELECT v.PK, v.n, "foo" AS s FROM table1_view_preprocess AS v
                                                ^

4️⃣ Expected Diff

table1_view should be emitted after table1_view_preprocess as input.

CREATE TABLE table1 (
  PK INT64 PRIMARY KEY
);

CREATE OR REPLACE VIEW table1_view_preprocess SQL SECURITY INVOKER AS SELECT t.PK, 1 AS n FROM table1 AS t;

CREATE OR REPLACE VIEW table1_view SQL SECURITY INVOKER AS SELECT v.PK, v.n, "foo" AS s FROM table1_view_preprocess AS v;

📌 Suggested Fix (Optional)

I think there is a multiple strategy:

  • Know dependency between table and views
    • I have expected there are more complex situations. It may be needed to DROP VIEW and CREATE [OR REPLACE] VIEW, not simply CREATE OR REPLACE VIEW.
  • Preserve original order in target DDL(may be a workaround)

Metadata

Metadata

Assignees

Labels

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions