can I influence the dump/export order at the h2 SCRPT command

Question

I've a h2 schema with some tables and a view.

The view is defined by:

CREATE FORCE VIEW PUBLIC.VIEW_TEST(NAME_,STREET_) AS 
SELECT 
USER.NAME_,
ADDRESS.STREET_
FROM 
PUBLIC.USER 
LEFT OUTER JOIN PUBLIC.ADDRESS ON USER.ADDRESS_= ADDRESS.ID_

After dumping (via "SCRIPT TO ..."), within the dump file, the "CREATE FORCE VIEW PUBLIC.VIEW_TEST ..." is before the "CREATE TABLE ADDRESS ..." clause. This table is joined within the view.

The result is, that after restoring the schema (via "RUNSCRIPT FROM ...") the command "SELECT * FROM VIEW_TEST" returns a error that the referenced table "ADDRESS" is unknown:

View "PUBLIC.VIEW_TEST" is invalid: "Tabelle ""ADDRESS"" not found 
Table ""ADDRESS"" not found [42102-197]"; SQL statement:
SELECT * FROM VIEW_TEST [90109-197] 90109/90109

If I drop the view and recreating it, everthing works fine, but I want to automize the dumping and restoring process.

Is there a way to set the ordering of tables and views?

What is the best way to ensure, that the view definitions are at the end of the dump?

Many thanks

Answer 1

No, there is no such way.

Here is a related bugreport: https://github.com/h2database/h2database/issues/2390

If you use the persistent database you can close the connection after execution of RUNSCRIPT command (make sure that you don't use DB_CLOSE_DELAY or use the SHUTDOWN command) and re-open it. The views will be initialized properly on startup.

If you use the in-memory database, the only workaround is to recompile your views with

ALTER VIEW VIEW_TEST RECOMPILE;
ALTER VIEW otherView RECOMPILE;
.....