Importing a dump created by SCRIPT DROP fails if dependent views exist

[GoogleCodeExporter]

Importing a dump created by SCRIPT DROP is most likely to fail if you have 
views that depend on other views.

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1. Create two views, whereas one view depends on the other view.
2. Create a dump with SCRIP DROP.
3. Imort the dump.
Note: If you are lucky, the dependent view might be dropped first because it 
comes first in the dump/script created by SCRIPT DROP. However, this is not the 
case for us.

What is the expected output? What do you see instead?
I expect h2 to respect the dependency and delete the views in correct order or 
to simply drop them with CASCADE.

Instead, an Exception is thrown:

org.h2.jdbc.JdbcSQLException: Kann "VIEW_1" nicht löschen weil "VIEW_2" davon 
abhängt
Cannot drop "VIEW_1" because "VIEW_2" depends on it; SQL statement:
DROP VIEW VIEW_1 [90107-163]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
    at org.h2.message.DbException.get(DbException.java:169)
    at org.h2.command.ddl.DropView.update(DropView.java:65)
    at org.h2.command.CommandContainer.update(CommandContainer.java:73)
    at org.h2.command.Command.executeUpdate(Command.java:226)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:177)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:152)
    at liquibase.executor.jvm.JdbcExecutor$1ExecuteStatementCallback.doInStatement(JdbcExecutor.java:89)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)
    ... 101 more

What version of the product are you using? On what operating system, file
system, and virtual machine?
h2 1.3.163

Do you know a workaround?
While writing the dump file, add CASCADE to each DROP VIEW or just to the views 
that you know are needed by other views.

What is your use case, meaning why do you need this feature?
To speed up or integration tests, we use dumps to restore the test data. We 
import these dumps for many test methods. Doing so with h2 1.3.163 fails, 
1.2.147 was fine!

How important/urgent is the problem for you?
medium, workaround is in place and "only" affects tests.

Please provide any additional information below.
Just adding CASCADE to each DROP VIEW should always be fine, I think. If not 
(for some scenarios), then maybe SCRIP DROP could take another parameter to 
control the inclusion of CASCADE.

Original issue reported on code.google.com by moldowan.android@googlemail.com on 20 Jan 2012 at 10:27

[GoogleCodeExporter]

Hi,

Thanks a lot for reporting this issue! You are right, "cascade" needs to be 
added to the "drop view" statement. I will do that.

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 24 Jan 2012 at 6:53

• Changed state: Accepted

[GoogleCodeExporter]

Should be fixed in version 1.3.164

Original comment by thomas.t...@gmail.com on 3 Feb 2012 at 2:15

• Changed state: Fixed

[GoogleCodeExporter]

Confirmed, thanks!

Original comment by moldowan.android@googlemail.com on 6 Feb 2012 at 4:37