The SCRIPT command sometimes exports views or tables before their dependencies

[mickroll]

Here is a simple Testcase.
When simply creating a table and then a related view, the sorting works: first table, then view.
But if the table is altered after creating the view (or even before doing that!), then the 'create table' sql is output at the end of the script.

H2BreakingViewTest.zip

I guess this is because a altered table is recreated by invalidating the old one (thereby losing its id), a new table is created and the old data is copied there. Now the table has a bigger id than the view and the sorting fails. Why this also fails for altering the table before even creating the view puzzles me.

This seems to be an old issue #172, the original author failed to produce a test case.

[famod]

This problem is even older than #172: https://code.google.com/archive/p/h2database/issues/420

[katzyn]

H2 can create a view before its dependencies are created, but ALTER VIEW TEST_VIEW RECOMPILE is required before its use.

CREATE TABLE TEST_TABLE ("ID" NUMBER(19, 0), "TEXT" VARCHAR(100));
CREATE VIEW TEST_VIEW("ID", "TEXT") AS SELECT ID, TEXT FROM TEST_TABLE;
ALTER TABLE TEST_TABLE ADD COLUMN ("NEW_COLUMN" VARCHAR(10));
SCRIPT;

->

CREATE FORCE VIEW "PUBLIC"."TEST_VIEW"("ID", "TEXT") AS
    SELECT "ID", "TEXT" FROM "PUBLIC"."TEST_TABLE";
CREATE MEMORY TABLE "PUBLIC"."TEST_TABLE"(
    "ID" NUMBER(19, 0),
    "TEXT" VARCHAR(100),
    "NEW_COLUMN" VARCHAR(10)
);

TABLE TEST_VIEW;
> View "PUBLIC.TEST_VIEW" is invalid: Table "TEST_TABLE" not found

ALTER VIEW TEST_VIEW RECOMPILE;
> ok

TABLE TEST_VIEW;
> ID TEXT
> -- ----

Views may also depend on each other, so it's not enough to export views after tables, views can be broken in very similar way when they aren't initialized in order.

[katzyn]

I guess in the most cases database is closed after its initialization with a RUNSCRIPT command. After reopening all invalid views are recompiled in a loop while there is a progress, so the reopened database should be fine. But if some queries are executed directly after execution of RUNSCRIPT, views may throw the error 90109.

[mickroll]

In our setup we do not close/reopen the database after every restore, we use h2 dump/restore for test isolation and to prevent costly reinitialisation.

From my point of view a generated dump should be consistent in itself, without the need to 'fiddle around' before the restored database works. Maybe a solution would be that ALTER VIEW TEST_VIEW RECOMPILE; is added to every dump that is generated by SCRIPT?

[katzyn]

It wouldn't in general case, because views may depend on each other too.

[mickroll]

At least in our case, views are created in the correct order. So dumping them in the same order as they are created would work for this scenario.
But you are right, if views depend on each other (we have that too...), it would be nice to have a more intelligent approach. I guess altering a view leads to a similar problem? (eg. old view is deleted, new one is created, with new id, so sorting by id for dump doesn't work here, either)

[mickroll]

The current sort-by-id-behaviour in ScriptCommand.query(int) has the following comment:

// sort by id, so that views are after tables and views on views
// after the base views

The goal of this issue is the same, only sorting by id is the wrong thing to do.

1. dependencies between tables are irrelevant, because constraints are added later. tables can be created unordered (no change needed here)
2. dependencies between views and tables are resolved by simply creating views after all tables have been created. This needs fixing and is the topic of this issue.
3. dependencies between views and other views (like you stated above) are an issue. This also needs fixing and is the bigger problem. I opened a new issue for that: 'script' view exporting order #2391