SCRIPT DROP does not properly honor VIEW dependencies

[GoogleCodeExporter]

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1. take a complex dataset with a couple of views, whereas some views also 
depend on a "central helper view" which itself depends on some tables
2. create a database dump with SCRIPT DROP
3. import the dump with ScriptReader and Statement.execute in a loop (or just 
check the order of statements by looking at the dump file)

What is the expected output? What do you see instead?

Views should be created in correct order; AFTER the tables they depend on.
Instead - for our database -, a "central helper view" (a view other views 
depend on) is created BEFORE a table it depends on.
And because SCRIPT DROP produces "CREATE FORCE VIEW" statements, no error is 
raised when importing the dump.
The view and it's dependent views are then unusable.

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

Do you know a workaround?
When reading the statements from the dump created by SCRIPT DROP in a loop, do 
not execute the CREATE FORCE VIEW statements immediatly. Instead, execute them 
after everything else has been executed. This of course means nasty statement 
string parsing.

What is your use case, meaning why do you need this feature?
We use h2 instead oy mysql for our unit and integration tests. To speed up 

How important/urgent is the problem for you?
Low. The described workaround is ok for us for now.

Please provide any additional information below.
Unfortunately I cannot provide a dump of our database because of copyright 
reasons.

Apart from the dependency detection problem, I do think that SCRIPT DROP should 
not use FORCE for CREATE VIEW.

Original issue reported on code.google.com by moldowan.android@googlemail.com on 10 Sep 2012 at 9:08

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Sorry, I forgot to complete a sentence:
"To speed up" -> "To speed up the provisioning of test data, we use SCRIPT DROP 
and ScriptReader."

Original comment by moldowan.android@googlemail.com on 10 Sep 2012 at 9:20

[GoogleCodeExporter]

Hi,

Could you post a simple, reproducible test case (in the form of a script, for 
example)?

Original comment by thomas.t...@gmail.com on 29 Oct 2012 at 5:55

[GoogleCodeExporter]

Hi Thomas,

sorry for my late reply.
I will try my best to come up with an isolated test case but this will take 
some time.

Original comment by moldowan.android@googlemail.com on 11 Dec 2012 at 6:25

[GoogleCodeExporter]

I will re-open the issue if you have a test case.

Original comment by thomas.t...@gmail.com on 28 Jul 2013 at 9:38

• Changed state: Invalid