Can idempotent scripts files be written for H2?

[maxhearn]

When a new version of a product that uses an RDBMS is released, it
often requires updating the database schema. The "obvious" thing to
do is run a script that updates the schema if it is not current.

Doing that in a safe manner requires that the script be idempotent:

1) Running it a second time should be harmless.

2) If the first run was terminated before it completed, running it a
second time should perform the actions that didn't happen the first
time around.

It doesn't look to me like idempotent script files can actually be
written with H2. Some actions can be written in an idempotent
fashion. For example, the CREATE TABLE statement can be written with:

CREATE TABLE IF NOT EXISTS NewTable ....

But there's no safe safe way to add a column to a table, since ALTER
TABLE ADD doesn't support IF NOT EXISTS.

An alternative approach would be to SELECT against the
INFORMATION_SCHEMA.COLUMNS table to determine if a column already
exists. But I don't see any flow of control constructs I can use in
the script to avoid adding a column if it already exists.

Is the general approach to this, "Write the upgrade commands in Java",
or is there a way to do this in a script that I'm just not seeing?

[Thomas Mueller]

Hi,

There is an item 'Procedural language / script language (Javascript)'
on the roadmap (priority 2):
http://groups.google.com/group/h2-database/web/roadmap

I think the easiest way to solve your problem is using constructs like:

SET @VERSION=SELECT VALUE FROM SETTINGS WHERE KEY='version';
IF @VERSION<=1 THEN
... script to upgrage from version 1 to version 2 ...
END
IF @VERSION<=2 THEN
... script to upgrage from version 2 to version 3 ...
END

Otherwise you will have to repeat IF EXISTS for each statement. So far
I am not sure how this procedural language should look like. More like
PostgreSQL / Oracle, Microsoft SQL Server, or MySQL. Probably it will
be something simpler and less verbose (with the option to support
compatibility with other databases later on if required). A converter
from PL/SQL / T-SQL to Java would make sense as well, and the ability
to directly compile and execute Java code inside H2 (but there are
security risks).

Regards,
Thomas

[maxhearn]

I absolutely agree that constructs like this are the way to address
the problem:

>
> SET @VERSION=SELECT VALUE FROM SETTINGS WHERE KEY='version';
> IF @VERSION<=1 THEN

>   ... script to upgrade from version 1 to version 2 ...
> END

I've been able to successfully use this sort of thing with other
database systems. The only real requirement other than basic control
flow is to be able to query for the existence of indexes, tables and
columns. But I think you've already got that covered in the
INFORMATION_SCHEMA.

In the meantime, I guess I'll have to write Java methods to handle
schema upgrades.