Question about H2 internals: DROP TABLE with VIEW
Pavel
I am trying to remove user defined table from user defined schema when
there is a view defined on the table. I tried to figure out how stock
H2 is doing it and I was lost. If you could clarify a few internal
aspects
it will be highly appreciated.
Database version 4-29
Let's consider the following program
File file = new File("target/db");
file.mkdirs();
Class.forName("org.h2.Driver");
ca = DriverManager.getConnection("jdbc:h2:file:target/db/
view");
Statement statement = ca.createStatement();
statement.execute("CREATE TABLE A(VAR CHAR(6))");
statement.execute("CREATE VIEW B AS (SELECT * FROM A)");
statement.execute("DROP TABLE A");
ca.close();
// the code below is optional just to verify that view was
dropped
/*
Connection cb =
DriverManager.getConnection("jdbc:h2:file:target/db/view");
statement = cb.createStatement();
try {
statement.execute("SELECT * FROM B");
fail("did not drop");
} catch (SQLException e) {
}
cb.close();
*/
Statement DROP TABLE A drops both table A and view B but using
strangely complicated logic.
Questions:
1. Is it expected behaviour : To drop view when table is dropped
without raising an error?
2. When I tried to trace this code I found that on "DROP TABLE A" H2
generates a lot of SQL exceptions, intercepts them and finally doing
everything all right. Why do you need all those exception needed. Also
there are repeated calls to findTableOrView. Why?
3. When I tried to drop the user defined table (modified H2) I got
the error message which was absent in the original H2.
org.h2.jdbc.JdbcSQLException: Cannot drop WORK.A because WORK.B
depends on it [90107-44]
at org.h2.message.Message.getSQLException(Message.java:65)
at org.h2.engine.Database.removeSchemaObject(Database.java:1135)
at org.h2.command.ddl.DropTable.executeDrop(DropTable.java:74)
at org.h2.command.ddl.DropTable.update(DropTable.java:86)
at org.h2.command.CommandContainer.update(CommandContainer.java:64)
at org.h2.command.Command.executeUpdate(Command.java:120)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:155)
Could you propose some solution to this problem. How did you avoid
this issue with the original H2 database?
Thanks,
Pavel
Thomas Mueller
Other databases support the option RESTRICT and CASCADE for DROP TABLE
statements. Currently H2 does not support it (and always cascades drop
table). This will need to be implemented at some point (it is already
on the feature request list, priority 2, Drop with restrict).
> 1. Is it expected behaviour : To drop view when table is dropped without raising an error?
Yes (because currently DROP cascades).
> 2. When I tried to trace this code I found that on "DROP TABLE A" H2 generates a lot of SQL exceptions, intercepts them and finally doing everything all right.
Currently H2 does no really 'know' if a view references a table
(directly or indirectly). So it tries to recompile each view and if it
fails, this view is affected as well. When implementing DROP RESTRICT
and CASCADE, the code should be cleaned up. It would be better if each
view 'knows' what tables are referenced.
Does this answer your question? Do you need the feature DROP RESTRICT?
Thomas
Pavel
The problem arise when I dropped the table but not the view, closed
the database and then restarted it. I was keeping getting errors.
It looks that I did not trigger SQLException on dropped table during
recompilation stage. I was not sure that I am doing something right
and tried to disable the code.
I will try to do it now
On Jun 8, 12:34 am, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
> Hi,
>
> Other databases support the option RESTRICT and CASCADE for DROP TABLE
> statements. Currently H2 does not support it (and always cascades drop
> table). This will need to be implemented at some point (it is already
> on the feature request list, priority 2, Drop with restrict).
>
> > 1. Is it expected behaviour : To drop view when table is dropped without raising an error?
>
> Yes (because currently DROP cascades).
>
> > 2. When I tried to trace this code I found that on "DROP TABLE A" H2 generates a lot of SQL exceptions, intercepts them and finally doing everything all right.
>
> Currently H2 does no really 'know' if a view references a table
> (directly or indirectly). So it tries to recompile each view and if it
> fails, this view is affected as well. When implementing DROP RESTRICT
> and CASCADE, the code should be cleaned up. It would be better if each
> view 'knows' what tables are referenced.
>
Yes, thanks. It clarified the logic.
> Does this answer your question? Do you need the feature DROP RESTRICT?
No, I do not need drop restrict.
>
> Thomas
Pavel
I noticed that you have rowCount variable in two classes: TableData
and ScanIndex. It looks like it contains the same value. Is it
correct?
Thomas Mueller
> I noticed that you have rowCount variable in two classes: TableData
> and ScanIndex. It looks like it contains the same value. Is it
> correct?
Yes. Actually, each index knows the row count. This is only used as a
consistency check.
Thomas