column list of CHECK constraints

[Paigan Jadoth]

I found an issue similar to http://groups.google.com/group/h2-database/t/cdef82a168babed5
for the column list of CHECK constraints.

In the information schema, the column CONSTRAINTS.COLUMN_LIST is
always NULL for CHECK constraints.

Now as dropping a column checks if it any references from a CONSTRAINT
prevent that (which is very smart :-) ):
http://code.google.com/p/h2database/source/browse/trunk/h2/src/main/org/h2/table/Table.java?r=3879#500
... it would be desirable (if not even required) to have a means of
querying the I_S if/which constraints reference a certain column
before attempting to drop it to ensure the attempt will succeed or to
drop the hindering constraints beforehand.

For any other constraint, this can be easily done. But for CHECK
constraints, the column list is always null, sadly. :-(.

I did a little reasearch again and found that there already is a
method to determine the columns involved in the CHECK expression
(obviously, because the drop attempt checking in table must have a
means of finding them):
http://code.google.com/p/h2database/source/browse/trunk/h2/src/main/org/h2/constraint/ConstraintCheck.java?r=3879#104

So all that would be necessary to add the column list information to
the CHECK constraint row in I_S.CONSTRAINTS would be to call that
method and store the result in the row.

Could this be considered for a future version of H2? Otherwise,
programmatically dropping columns becomes a gamble as soon as CHECK
constraints are involved. :-(.
Is there an alternative (workaround) way to determine the involved
columns of a CHECK constraint? Maybe by mapping a H2 system function
as an alias or so?

Thanks for answers :)

[Paigan Jadoth]

Whoever might be interested in a workaround here:
I've written some h2acks ( :D ) providing the missing feature as a SQL
procedure.
https://jadoth.svn.sourceforge.net/svnroot/jadoth/H2acks/src/net/jadoth/h2acks/SQLFunctions.java
(sourceforge home: http://sourceforge.net/projects/jadoth/)

This solution is sufficient for me for the time being, of course I'd
prefer to have a proper fix for the problem in some future version of
H2.

On Dec 10, 2:02 pm, Paigan Jadoth <jadoth.pai...@googlemail.com>
wrote:
> I found an issue similar tohttp://groups.google.com/group/h2-database/t/cdef82a168babed5

> for the column list of CHECK constraints.
>
> In the information schema, the column CONSTRAINTS.COLUMN_LIST is
> always NULL for CHECK constraints.
>
> Now as dropping a column checks if it any references from a CONSTRAINT

> prevent that (which is very smart :-) ):http://code.google.com/p/h2database/source/browse/trunk/h2/src/main/o...

> ... it would be desirable (if not even required) to have a means of
> querying the I_S if/which constraints reference a certain column
> before attempting to drop it to ensure the attempt will succeed or to
> drop the hindering constraints beforehand.
>
> For any other constraint, this can be easily done. But for CHECK
> constraints, the column list is always null, sadly. :-(.
>
> I did a little reasearch again and found that there already is a
> method to determine the columns involved in the CHECK expression
> (obviously, because the drop attempt checking in table must have a

> means of finding them):http://code.google.com/p/h2database/source/browse/trunk/h2/src/main/o...

[Thomas Mueller]

Hi,

> Otherwise,
> programmatically dropping columns becomes a gamble as soon as CHECK
> constraints are involved. :-(.

Well, either it works or it doesn't (in which case you get an
exception)... Is it really a problem?

Regards,
Thomas

[Paigan Jadoth]

Hi

First of all: for my purposes, the hack works very well, so I
personally have no need anymore to "make a point" or something, it's
only in the best interest of H2 and its quality.

Yes, it is a problem.

Imagine the application issues a drop column command. And it fails.
What can the application logic do? Unsafely parse the exception
message? Surely not. Try parsing the arbitrarily complex CHECK
expression SQL string from the I_S? Absolutely not.
Manually handling such a problem every time is no option, either.
Manually tinkering around, repeatedly spending hours to research and
fix "all those little problems" by hand is no option for efficient
professional software development.
This requires clean architecture, layers, libraries that handle such
things programmatically, automatically, generically. Developing
quality application code is to define/generate structures once (on the
logic/control side, not statically on the model/db side) and then have
some layer that handles projection, generation, translation,
validation, interaction, etc.

Another point of view:
Do you iterate over arrays/collection with an unbound index and wait
for the AIOOBE to terminate the iteration? No of course not, but
instead you programmatically determine the correct bound of the
iteration index or use an iterator or a function with internal
iteration or so. Same applies to structure definitions, to working
with DDL. Proper (maintainable etc.) application code doesn't just
"wait for the exception", but it gets things right in the first place.
To obtain this when working with DDL, it's the DBMS' obligation to
provide a means of telling which of its structures are connected as
far as it's relevant to the application.
Which reason would an I_S have otherwise? And if there already is an
I_S, shouldn't it be complete?

Additionally, this issue is no big deal for H2. The information is
already perfectly available. All that's missing is the information
schema "view" to that detail of the database's internals.

If you want to safely and easily improve H2, you should have a desire
to put the fix for the hole on the roadmap (low, at the end, of
course, because it's not that serious and there exists an acceptable
workaround meanwhile).
If you say you don't care about such holes in your database, I
personally am perfectly fine with it either since I have a workaround.
It's just a little sad for H2 itself.

Thank you for your time :)
Regards

On Dec 16, 11:06 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Rami Ojares]

Hi Paigan,

I have been reading your recent posts to this list and want to commend
the clarity and clear reasoning in your line of arguments.
Improving the Information Schema is never a bad thing.
The whole of jdbc metadata api can be done away with if the database has
a good system catalog.
It is in the spirit of relational theory and sql to represent all the
metadata of the database in the system catalog (Information schema in
h2's case).

This can be utilized especially by tools built on top of h2.
So while these improvements are not urgent they certainly improve h2.

One huge improvement (something that does not exist in other databases)
would be to add
the ability to refer to system catalog tables from foreign keys. This
would provide a clear and reliable mechanism
to make some data in the user space to rely on the existence of database
objects.

But I also understand Thomas' focus on priorities and I have always
admired his strong commitment
in fixing bugs from H2 that lead to corruption. And as the roadmap seems
to be Thomas' personal tasklist
I feel that he has the right to control it according to his preferences.

I know I am repeating myself but I think that simple improvements
(backward compatible improvements)
that are well grounded should always make it into h2, provided that they
adhere to basic guidelines like having proper tests etc.

Please, correct me if I am wrong or stepping out of line, Thomas.

- Rami Ojares

[Noel Grandin]

Hi

The correct solution is to look at the value in the CHECK_EXPRESSION
column to see if it contains the column that you are interested in.
That appears to be what other databases like ORACLE do.

Regards, Noel.

> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to
> h2-database...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.
>

[Paigan Jadoth]

Oh, hi!

I missed your post, as it seems :-(.

Thank you for the kind words.
I fully agree: improvements are nice, but critical stuff has to have
priority. Especially if the tasks are on one's personal work load. I
have a similar situation with some hobby projects that are
productively used in our company now, although they're not as "big" as
H2.

Despite all the agreement and understand (and respect) for Thomas'
priorities, every single topic so far pretty much received a kind of
"NO, go away!" reaction instead of a "I see the problem, but it's too
low at the moment" reaction.
Still I understand that as well, so I will continue to report problems
or potential improvements as I encounter them, even if they get
reppelled every time :D and maybe provide satisfying workarounds if
possible
(and add them to my personal "H2 known issues" collection ]:-> ).

So thanks again for the commendation.



Also a short reply to Noel's suggestion:
If by that a manual / application-side parsing of the CHECK_EXPRESSION
was meant, then: not, it's not the correct solution, as the explaining
should have made abundantly clear.
Nevertheless I have a sufficient workaround, so I'm fine.

[Noel Grandin]

Hi

Sorry if that was your experience, we are quite happy to have people
report problems.

We are quite short handed as far as developers go, so we welcome patches :-)

Regards, Noel.

[Thomas Mueller]

Hi,

> "NO, go away!"

Well, before adding a feature request, I usually would like to understand the real-world use case. In this case, I don't understand it so far. I know about the following two use cases for dropping columns:

- Manually at development time: in this case, the developer tries to drop a column manually. If this doesn't work, a nice exception message is enough in this case.

- Programmatically when installing a new version of an application: in this case, the program drops the column (for example at startup), but only if the schema is old. The program is expected to run automatically and without problems (if there is a problem, then the application is buggy, or there is something wrong with the database, both needs manual intervention).

For both cases, I don't see how additional metadata would help.

It seems you have a different use case... Could you please explain it?

Regards,

Thomas