hypersql and verifying a lower case schema name

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

hypersql and verifying a lower case schema name

Andy Stevens-2
Hi,

I'm using hypersql to run some unit tests of a class that will
actually connect to some other type of database at runtime.  Not
ideal, but there isn't a dedicated test database I can use (we're
reading data from a service another team is responsible for).  Also,
the class is just running fairly basic SQL queries and using hsql I
can do everything in-memory so the tests run a lot quicker (and it's
much simpler than trying to mock most of java.sql.* with jmock...)

However, the queries sent by the class include the schema (i.e.
they're of the form 'select "FOO" from "dbo"."BAR"'). The default
schema in hsql is named "PUBLIC" rather than "dbo", but I got around
that by executing
ALTER SCHEMA PUBLIC RENAME TO "dbo"
after starting the database and before creating my test table.
Querying the DatabaseMetaData and INFORMATION_SCHEMA.TABLES &
INFORMATION_SCHEMA.SCHEMATA contents, I get the schema & table names
that I expect.
However, when I try to set up dbunit on the connection with
IDatabaseConnection connection = new DatabaseConnection(conn, "dbo", true);
it gives the error
org.dbunit.DatabaseUnitException: The given schema 'DBO' does not exist.
Not sure why it's converted to upper case, but if I try to escape the
schema name with
IDatabaseConnection connection = new DatabaseConnection(conn, "\"dbo\"", true);
then it gives
org.dbunit.DatabaseUnitException: The given schema '"dbo"' does not exist.
instead.  If I pass false for the validation parameter, it gets
further, but connection.getSchema() return those same values in each
case and connection.createDataSet().getTableNames() returns an empty
list (while I can still see the table as expected via the same JDBC
queries as before on connection.getConnection())

After it's connected, I can set the case-sensitive & qualified table
names features on the DatabaseConfig, but that doesn't help with
verifying the schema name in the constructor.  Is there any way to get
this working correctly, or should I raise a bug report?

I can work around the problem by using the single-argument constructor
(i.e. with a null schema name); in that case
connection.createDataSet().getTableNames() returns "dbo.TEST_TABLE"
like I was expecting.  But it would be nice to be able to use the
other constructor, in case anyone happens to forget the "alter schema"
statement in the future...

Oh yes, something else that was a little frustrating - I noticed
there's a hsql-specific subclass
org.dbunit.ext.hsqldb.HsqldbConnection which sets up the data type
factory, but that only includes the two-arg constructor (connection,
schema) so can't validate the supplied schema.  It would be nice if it
also had equivalents for the other constructors - I could upload a
patch if you like...


Andy
--
http://pseudoq.sourceforge.net/  Open source java sudoku application

------------------------------------------------------------------------------
Xperia(TM) PLAY
It's a major breakthrough. An authentic gaming
smartphone on the nation's most reliable network.
And it wants your games.
http://p.sf.net/sfu/verizon-sfdev
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: hypersql and verifying a lower case schema name

Mike Kienenberger
I have switched from HSQLDB to H2Database.   There are likely to be
many similarities between the two since they were both originally
developed by Thomas Mueller (H2 stands for "Hypersonic 2"), and maybe
what I'm doing for H2 will also work for HSQLDB.  I didn't use HSQLDB
with more than one schema, and it's been long enough that I don't
remember all of the details of my test environment using it.  I know I
didn't use the public schema, nor did I try to rename it, though.  I
just ignored it.

For H2, I am actually using multiple schemas.   Our production
databases are on Oracle, and H2 is similar enough to Oracle that it's
almost a drop in replacement.   (There are a few minor things that
needed to be done -- rewriting the schema creation sql for complex
views involving system dates, defining a TRUNC() function)

CREATE ALIAS IF NOT EXISTS TRUNC  AS $$ java.util.Date
trunc(java.sql.Timestamp timeStamp) { return new
java.util.Date(timeStamp.getTime()); } $$;

To make multiple schemas accessible, all I needed to do was add it to
my jdbc url:

jdbc:h2:tcp://localhost/~/test/snapshot_database;MVCC=TRUE;SCHEMA_SEARCH_PATH=dbo,someotherschema,etc

You can also set them from sql.

SET SCHEMA dbo;
SET SCHEMA_SEARCH_PATH path;

We also set the qualifiedTableNames feature, but we don't specify a
specific schema.

        public IDatabaseConnection getConnection() throws Exception {
                if (null == jdbcDatabaseTester)
                {
                        jdbcDatabaseTester = new JdbcDatabaseTester(driver, url,
                                        userid, password);
                }
               
                IDatabaseConnection connection = jdbcDatabaseTester.getConnection();

                String qualifiedTableNamesFeature =
"http://www.dbunit.org/features/qualifiedTableNames";
                DatabaseConfig config = connection.getConfig();
                if (! config.getFeature(qualifiedTableNamesFeature))
                {
                        config.setFeature(qualifiedTableNamesFeature, true);
                }

                return connection;
        }

If the above doesn't help with hsqldb, maybe you should consider
switching to H2 since it sounds like you are just getting started.
Thomas Mueller actively develops H2, answers questions on the h2
google group when no one else can do so, and considers and applies
patches in a timely fashion.  Yes, I am now a fan :)


On Mon, Apr 11, 2011 at 8:45 AM, Andy Stevens
<[hidden email]> wrote:

> Hi,
>
> I'm using hypersql to run some unit tests of a class that will
> actually connect to some other type of database at runtime.  Not
> ideal, but there isn't a dedicated test database I can use (we're
> reading data from a service another team is responsible for).  Also,
> the class is just running fairly basic SQL queries and using hsql I
> can do everything in-memory so the tests run a lot quicker (and it's
> much simpler than trying to mock most of java.sql.* with jmock...)
>
> However, the queries sent by the class include the schema (i.e.
> they're of the form 'select "FOO" from "dbo"."BAR"'). The default
> schema in hsql is named "PUBLIC" rather than "dbo", but I got around
> that by executing
> ALTER SCHEMA PUBLIC RENAME TO "dbo"
> after starting the database and before creating my test table.
> Querying the DatabaseMetaData and INFORMATION_SCHEMA.TABLES &
> INFORMATION_SCHEMA.SCHEMATA contents, I get the schema & table names
> that I expect.
> However, when I try to set up dbunit on the connection with
> IDatabaseConnection connection = new DatabaseConnection(conn, "dbo", true);
> it gives the error
> org.dbunit.DatabaseUnitException: The given schema 'DBO' does not exist.
> Not sure why it's converted to upper case, but if I try to escape the
> schema name with
> IDatabaseConnection connection = new DatabaseConnection(conn, "\"dbo\"", true);
> then it gives
> org.dbunit.DatabaseUnitException: The given schema '"dbo"' does not exist.
> instead.  If I pass false for the validation parameter, it gets
> further, but connection.getSchema() return those same values in each
> case and connection.createDataSet().getTableNames() returns an empty
> list (while I can still see the table as expected via the same JDBC
> queries as before on connection.getConnection())
>
> After it's connected, I can set the case-sensitive & qualified table
> names features on the DatabaseConfig, but that doesn't help with
> verifying the schema name in the constructor.  Is there any way to get
> this working correctly, or should I raise a bug report?
>
> I can work around the problem by using the single-argument constructor
> (i.e. with a null schema name); in that case
> connection.createDataSet().getTableNames() returns "dbo.TEST_TABLE"
> like I was expecting.  But it would be nice to be able to use the
> other constructor, in case anyone happens to forget the "alter schema"
> statement in the future...
>
> Oh yes, something else that was a little frustrating - I noticed
> there's a hsql-specific subclass
> org.dbunit.ext.hsqldb.HsqldbConnection which sets up the data type
> factory, but that only includes the two-arg constructor (connection,
> schema) so can't validate the supplied schema.  It would be nice if it
> also had equivalents for the other constructors - I could upload a
> patch if you like...
>
>
> Andy
> --
> http://pseudoq.sourceforge.net/  Open source java sudoku application
>
> ------------------------------------------------------------------------------
> Xperia(TM) PLAY
> It's a major breakthrough. An authentic gaming
> smartphone on the nation's most reliable network.
> And it wants your games.
> http://p.sf.net/sfu/verizon-sfdev
> _______________________________________________
> dbunit-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>

------------------------------------------------------------------------------
Xperia(TM) PLAY
It's a major breakthrough. An authentic gaming
smartphone on the nation's most reliable network.
And it wants your games.
http://p.sf.net/sfu/verizon-sfdev
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user