Schema auto-creation in In-Memory mode?

[vanyatka]

Hey guys,

I need to create a H2 datasource like following
jdbc:h2:mem:;SCHEMA=DBO.
The problem is that Schema is not created automatically, while my
hibernate mappings are bound to specific value of the schema.

Is it possible to tell H2 to auto-create specified schema? Or what
would be the best way to post-initialize the datasource the way it
would contain missing schema?

Currently when trying to obtain connection from the above datasouce
and create the schema manually I get the following exception:
"org.h2.jdbc.JdbcSQLException: Schema DBO not found; SQL statement:
SET SCHEMA DBO [90079-116]"


Thanks,

[Thomas Mueller]

Hi,

You could use named in-memory databases and create the schema first in
your application:

jdbc:h2:mem:test

Regards,
Thomas

[vanyatka]

Thanks, Thomas.

Yes, I can do that. But:
a) Named in-memory connections are shared within JVM, which is not
very handy "unit-test-wise", as you need to take care of clearing up
the DB before each test.
b) The same datasource is passed at the same time to Hibernate and
Unit tests components, so will need to figure out the way to setup
schema right after datasouce instantiation.

To be honest with all that functionality that H2Database provides this
piece should be a piece of cake ;)

Do you think we could add this as a feature request?


On Dec 9, 9:27 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[vanyatka]

The ability to auto-create schema (based on connection string) for in-
memory mode would be very helpful..

On Dec 9, 9:27 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Thomas Mueller]

Hi,

> The ability to auto-create schema (based on connection string) for in-
> memory mode would be very helpful..

Just creating the schema may solve your specific problem, however I
don't want to add it, because it sounds like a hack. I will add a
feature request for the following feature:

If the database URL ends with ";INIT=<url>" then the SQL script from
the given file or URL is executed (the user name must have admin
rights). Example URL: jdbc:h2:mem:test;INIT=~/init.sql

That would allow to create schemas, tables, and data.

However, I will not have time to implement this feature currently.
Patches are always welcome of course!

Regards,
Thomas

[rossputin]

Hi.

I had thought about doing this also, but am unsure how to, I use
Spring to do all this and am using a 'LocalSessionFactoryBean' to
create the schema, but this does not allow me to name the schema.
Does anyone have any ideas for an easy alternative where I could name
the schema ?

Thanks in advance,

-- Ross

On Dec 9 2009, 6:27 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Thomas Mueller]

Hi,

Why can't you use the default schema?

Regards,
Thomas

[rossputin]

Hi.

Unfortunately the codebase requires two schema's, both specifically
named.

-- Ross

On Jan 23, 6:43 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Thomas Mueller]

Hi,

> Unfortunately the codebase requires two schema's, both specifically
> named.

I still don't understand. Why does the codebase require two specially
named schemas, but doesn't create them?

Regards,
Thomas

[Kerry Sainsbury]

Hi Thomas,

I've been quietly implementing the "INIT=" functionality and have come to a decision point that I'm SURE you'll want to contribute to.

The "INIT=<url>" description below is very similar to the RUNSCRIPT command, so I came to the conclusion that I might as well just use it directly, and rather than "INIT=<url>" I'd support "INIT=<command>", which would in turn let me support the requirement with:

";INIT=RUNSCRIPT FROM <url>" (lets call this Option 1)

This also fixes problems with CHARSET issues, because RUNSCRIPT handles that too.

If you'd prefer I could just use "RUNSCRIPT" behind the scenes, and use the originally suggested notation, plus support for RUNSCRIPT options:

";INIT=<url> [with support for all the optional RUNSCRIPT parameters too]" (lets call this Option 2)

or perhaps, if it's clearer:

";RUNSCRIPT=<url> [with support for all the optional RUNSCRIPT parameters too]" (lets call this Option 3)

So, which option do you like? Or have you a 4th suggestion?

Cheers
Kerry

Regards,
Thomas

--

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.

[Thomas Mueller]

Hi,

The most flexible solution is probably ";INIT=<list of SQL
statements>". The problem is that the ";" to delimit SQL statements
can't be used (unless there is some way to escape it; currently there
isn't).

There is a workaround I didn't think about first: to use the database
event listener. The problem is that there is currently no way to pass
the user and password to it, however it does work:

url += ";DATABASE_EVENT_LISTENER='"+ Init.class.getName() + "'";
Connection conn = DriverManager.getConnection(url, "sa", "sa");
Statement stat = conn.createStatement();
stat.execute("select * from test");
conn.close();

public class Init implements DatabaseEventListener {

private String databaseUrl;

public void init(String url) {
databaseUrl = url;
}

public void opened() {
try {
Connection conn =
DriverManager.getConnection(databaseUrl, "sa", "sa");
Statement stat = conn.createStatement();
stat.execute("create table if not exists test(id int)");
conn.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}

...

Regards,
Thomas