Cheap unit tests with jOOQ

[digulla]

While writing the dynamic record code, I was wondering if this feature wouldn't be a valuable addition to jOOQ: A "junit" mode.

Basically, this mode should allow the developer to register SQL queries and the desired result (i.e. the records which jOOQ should return for this query).

In this mode, jOOQ wouldn't try to open a DB connection. It would allow to test SQL query building (by registering no results) and creating Record objects to test result processing and a mix of both.

Or is something like that already possible?

Regards,

A. Digulla

[Lukas Eder]

Hello Aaron,

Ideally, you would:

1. Write a simple JDBC mock layer. Your mock Connection /
PreparedStatements would look up mock ResultSets from a file
2. Hook these JDBC mock objects into jOOQ's ExecuteListener lifecycle

jOOQ wouldn't notice the difference between your mock objects and an
actual database.

DBUnit might be a good choice:
http://www.dbunit.org

Also, H2 has nice, text-based integration tests:
http://code.google.com/p/h2database/source/browse/trunk/h2/src/test/org/h2/test/test-1.3.txt

On the other hand, using an actual H2 (or Derby, HSQLDB) in-memory
database instance might be less work?

Cheers
Lukas

[digulla]

On Tuesday, February 12, 2013 3:14:40 PM UTC+1, Lukas Eder wrote:

I again fell into the trap of not explaining that I already spend several weeks of thinking about this and just posting the final results. Oh well ...

1. Write a simple JDBC mock layer. Your mock Connection /
PreparedStatements would look up mock ResultSets from a file
2. Hook these JDBC mock objects into jOOQ's ExecuteListener lifecycle

MockRunner can do that. The last release (0.4.2) was 2009. The API is ... strange. To mock a ResultSet, you have to give the MockResultSet the values for each column as an array (i.e. it's not row based). Internally, it will then compile the arrays with column values into rows arrays.

Actually, it takes a lot of work to prepare data in the form of a ResultSet (a class which has 187 methods and each of them has lots of intricate details). Writing a mock JDBC layer takes many, many lines of code and because of the broken API evolution in the VM, you will need a code generator step to build an instance per Java version.

If I'd do the same with jOOQ, I'd just have to add a few times `public` to a few classes and methods and I'd just have to write a unit test compatible implementation of Record and intercept the one single point where a query is executed.

Comparing the two, the first approach would take 100-500 times the effort of the second one.

 

Also, H2 has nice, text-based integration tests: 
http://code.google.com/p/h2database/source/browse/trunk/h2/src/test/org/h2/test/test-1.3.txt

That just executes queries against a real (in-memory) database so I gain exactly nothing from this. It's not even related to my question, really. It's like the documentation based tests in Python docutils.

 

On the other hand, using an actual H2 (or Derby, HSQLDB) in-memory
database instance might be less work?

Less work? Absolutely.

But in terms of test runtime, it would make a huge difference. Creating an H2 database takes a long time. Creating a list of object arrays, wrap them in a record and call a method that makes jOOQ return this wrapper form a certain SQL pattern is encountered would take only a few nanoseconds.

It would also cut many dependencies from my tests (database drivers), make the test much more memory efficient, it would make it much, much easier to put "illegal" test data into the "database" because I wouldn't have to fulfill all the foreign key relations, etc.

Regards,

A. Digulla

[digulla]

Let me elaborate on this a little bit.

When I write unit tests, I want to test my code efficiently.

I don't want to test:

- the JDBC API

- the database driver

- the database core with its caches, file system abstractions, ...

- creating a new database

- executing DDL code to create tables, indexes and other stuff

- loading test data into the new tables

- connecting to the test database

- copying test data around in memory

- converting the test data into a ResultSet

- parsing the ResultSet into a Record.

- erasing test data and replacing it with new test data for the next test

- ...

All these dependencies cost time, rarely give me additional value (like finding I'm using the wrong query or the query won't return the expected result, etc.) and they are hard to maintain. Instead of having everything in one place, I need to maintain a dozen different files, I need to find a way to make Spring lazy create the database when I need a different set of test data per test, etc.

Usually, I have these kinds of tests:

1. Is my query correct syntactically?
2. Does my query return the correct results?
   
3. Does the code processing the results work correctly?
4. Does my code update the database correctly?

The first kind of test is almost futile with jOOQ. I sometimes do it anyway for complex queries but I rarely run them versus the database. Instead, I let the test print the query, execute it once on an SQL tool and when it works, I copy the expected result into the test. There is no need to actually execute that query unless it changes.

The second test obviously needs a database but a static one. I can share same database among all the "test query results" tests since the queries don't change it. The cost to create the database is shared among all tests. If I run one individual test, then it's a bit expensive but I can bear with that. I can also make the database clever and persist it on disk, so I can omit many of the expensive steps above. So this isn't a problem or at a problem that I don't have a better solution for.

#3 only needs a database because ResultSet is such a horrible interface. It's usually more simple to just create a database than write 1000 lines of code just to be able to start writing my test.

But jOOQ doesn't really need ResultSet. It should be possible to pass it a mock connection that simply doesn't do anything (just a few lines of code to implement the few methods in java.sql.Connection).

And then I need a way to overwrite `org.jooq.impl.AbstractResultQuery.execute(ExecuteContext, ExecuteListener)` and I've cut a dozen unnecessary dependencies from my test cases.

A simple and API clean way to implement this would be to make the execute methods part of the configuration, maybe an Executioner interface which I can replace.

Regards,

A. Digulla

[Lukas Eder]

Hi Aaron,

The unit-vs-integration-test debate is a religious one... I can see
how you don't want to go down the integration-test path...

Nevertheless, I'm sorry, I'm a bit lost in this thread. You seem to
already know what you have to do? Essentially, you're about to modify
jOOQ source code at your site in order to make it easier to write unit
tests by patching / mocking jOOQ's internals, right? Is there anything
I can still help you with?

Cheers
Lukas

2013/2/13 digulla <adig...@gmail.com>:

> --
> You received this message because you are subscribed to the Google Groups
> "jOOQ User Group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to jooq-user+...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>

[digulla]

On Wednesday, February 13, 2013 4:37:09 PM UTC+1, Lukas Eder wrote:

The unit-vs-integration-test debate is a religious one... I can see
how you don't want to go down the integration-test path...

My opinion is that I know better what I need than some framework someone else designed and that frameworks should help me solve problems in the way I feel best rather than limiting my options without a good reason.

In this case here, I would like to hear "I'll support some form of dependency injection in jOOQ 3.0". You already pass Configuration almost everywhere, it wouldn't be a huge change to let it create all instances (instead of just a few of them).

The current situation is frustrating. jOOQ almost does what I need but often, there is just a final / package private that prevents me from solving my problem. Instead of investing 5-10 minutes to solve a trivial problem, I have to spend a couple of days.

I can see how this design makes your life easier :-) But for me, your API feels ... hostile. I hope you understand.

I could maintain my own branch but it's effort I would like to avoid. Especially when we talk about a "little" patch that adds DI to jOOQ - that would be a huge beast and it would break for every release that you do.

Regards,

A. Digulla

[Lukas Eder]

>> The unit-vs-integration-test debate is a religious one... I can see
>> how you don't want to go down the integration-test path...
>
>
> My opinion is that I know better what I need than some framework someone
> else designed and that frameworks should help me solve problems in the way I
> feel best rather than limiting my options without a good reason.
>
> In this case here, I would like to hear "I'll support some form of
> dependency injection in jOOQ 3.0". You already pass Configuration almost
> everywhere, it wouldn't be a huge change to let it create all instances
> (instead of just a few of them).
>
> The current situation is frustrating. jOOQ almost does what I need but
> often, there is just a final / package private that prevents me from solving
> my problem. Instead of investing 5-10 minutes to solve a trivial problem, I
> have to spend a couple of days.

There's this point of view here... (1)

> I can see how this design makes your life easier :-) But for me, your API
> feels ... hostile. I hope you understand.
>
> I could maintain my own branch but it's effort I would like to avoid.
> Especially when we talk about a "little" patch that adds DI to jOOQ - that
> would be a huge beast and it would break for every release that you do.

... and then, there's this one. (2)

(1) seems to claim that things should open up and be accessible,
extendable, public, etc.
(2) seems to claim that things should remain stable

My mental Prolog processor goes mad at these claims. ;-)

jOOQ's model is quite clear. What's in org.jooq follows semantic
versioning (major / minor releases, API guarantees, stability,
support). If you touch org.jooq.impl, however, then you're on your
own. I think this model has made it possible to achieve anything at
all. If you have any more concrete suggestions than "adding some form"
of dependency injection", I'll be more than happy to discuss them.
From the information you have disclosed so far, I'm still not quite
sure where jOOQ goes wrong

[digulla]

For this feature, I'd need the following changes:

AbstractQuery.execute():

1. The method must work with a null connections

2. It must not create a statement

3. I must be able to override

 ExecuteContext ctx = new DefaultExecuteContext(c, this);

 so I can return a mock statement which does nothing for calls like

ctx.statement().setQueryTimeout(timeout);

and later ctx.resultSet()

org.jooq.impl.AbstractResultQuery.execute(ExecuteContext, ExecuteListener):

1. Must work for null / mock connections as above

2. Instead of looping, it should return a predefined ResultImpl

Regards,

A. Digulla

[Lukas Eder]

Hi Aaron,

OK, these are concrete changes, but now I don't understand how this
has to do with dependency injection...?

I know you really hate implementing JDBC's Connection,
PreparedStatement and ResultSet. But frankly, this is the way to go
here. JDBC offers a clean, well-defined API, which is much more stable
than jOOQ's internals. Let's consider a couple of pro's / con's:

Your approach:
----------------------
1. Lots of work for me
2. Substantial probability of jOOQ evolution leading to breaking your
tests in the near future
3. Very difficult to document / configure
4. Little gain but potential, negative side-effects for the community
due to the added complexity

Implementing JDBC interfaces:
--------------------------------------------
1. No work for me
2. Little probability of breaking in the near future. JDBC doesn't
evolve *that* quickly. If it breaks, just add 3-4 more methods to your
implementation classes. Even if JDBC evolves backwards-incompatibly,
the evolution is very easy to control / maintain.
3. jOOQ evolution will hardly be able to break your tests
4. Community is not involved

Let's have a closer look at your requirements again:

--------------------------------------------------------------

> 1. The method must work with a null connections

Or better, with your mock connection! -> Transparent to jOOQ
... or, let's assume the null check is removed (*)

> 2. It must not create a statement

Or better, create your mock statement! -> Transparent to jOOQ
... or, assuming that the previous null check was removed (*), yes,
various prepare() methods would have to ignore the fact that they
cannot create a statement on a null connection (**)

> 3. I must be able to override
> ExecuteContext ctx = new DefaultExecuteContext(c, this);
> so I can return a mock statement which does nothing for calls like
> ctx.statement().setQueryTimeout(timeout);
> and later ctx.resultSet()

Or better, just let your mock statement (which you've already created
via your mock connection) ignore the setQueryTimeout call
... or, assuming that (*) and (**) were implemented, you could patch
the ctx.statement() with an ExecuteListener (***)

> org.jooq.impl.AbstractResultQuery.execute(ExecuteContext, ExecuteListener):
Of course, I will eventually add an AbstractSpecialQuery and an
AbstractHelloWorldQuery.execute(ABC, XYZ)... Your test might break

> 1. Must work for null / mock connections as above

It works for mock connections
... but it could work for null connections as (*), (**), and (***)
would hold true

> 2. Instead of looping, it should return a predefined ResultImpl

Or better, your mock statement returns the predefined mock ResultSet
--------------------------------------------------------------

I'm really having a hard time seeing the advantage of your projected
approach over just implementing 3-5 JDBC interfaces (depending on
whether you need Statement and CallableStatement as well)...

On the other hand, if you must insist on such heavy intervention into
jOOQ's internals (which I will not maintain), you can still maintain
things yourself. You have the choice:

1. Maintain your own, patched jOOQ code (frequent changes as I will
not make any guarantees to package-private implementation APIs. No one
sane in the open source world would do that)
2. Maintain your own JDBC implementation (infrequent changes that are
easy to maintain)

[digulla]

On Friday, February 15, 2013 1:25:07 PM UTC+1, Lukas Eder wrote:

2. Maintain your own JDBC implementation (infrequent changes that are 
easy to maintain)

How do I write a unit test that tests a piece of code with Java 5, 6 and 7?

Each of these versions uses a different, incompatible JDBC API and I'm pretty sure that Java 8 will include JDBC 4.2.

What's worse, I'm not at all interested in maintaining this code and these dependencies.

I don't want every project that uses my jOOQ unit test framework to add 100 lines to their POMs so the correct dependency is added to the classpath depending on the Java VM version. I think I could put that into a parent POM, though.

But in the end, it would be a lot more effort than hiding query execution behind an interface inside of jOOQ to cut any dependency on JDBC.

I feel that you simply say "JDBC mock is much easier" because you never actually tried to build one. I wrote 3 JDBC drivers, I wrote patches to MockRunner and I wrote unit tests using MockRunner. The JDBC API, frankly, sucks. From all the Java APIs, it's the most complex by far, it's the most brittle and the one where they broke every rule regarding API evolution.

JDBC is so complicated that it took Oracle more than 10 years to build a driver which doesn't have many major bugs like leaking Cursors when using BLOBs.

If JDBC was written by you, it would be an option. But it's not. There is no good way to implement Connection without using a code generator at build time.

I would even try to create mocks using Mockito but even then, I still need to write about 200-500 lines of code to implement a mock ResultSet when I just need to mock Cursor.fetch() and you won't let me do that.

Just look at org.jooq.impl.FieldTypeHelper.getFromResultSet(ExecuteContext, Class<? extends T>, int) to get an idea how much of the ResultSet API I'd have to replicate.

And even when I could mock Cursor.fetch(), ResultImpl is package private so I'd have to cut&paste these 1931 lines to implement Result unless putting my code into org.jooq.impl which I don't like.

Regards,

A. Digulla

[Lukas Eder]

2013/2/15 digulla <adig...@gmail.com>:

> On Friday, February 15, 2013 1:25:07 PM UTC+1, Lukas Eder wrote:
>
>> 2. Maintain your own JDBC implementation (infrequent changes that are
>> easy to maintain)
>
>
> How do I write a unit test that tests a piece of code with Java 5, 6 and 7?

jOOQ doesn't officially support Java 5 ;-)
Also, jOOQ doesn't call JDBC methods from Java 7. To stay compliant
with JDBC 4.1, jOOQ offers org.jooq.tools.jdbc.JDBC41Connection and
similar methods, providing method stubs for the added methods in JDBC
4.1

So when using jOOQ, you only have to "support" JDBC 4.0

> Each of these versions uses a different, incompatible JDBC API and I'm
> pretty sure that Java 8 will include JDBC 4.2.

Yes, but with Java 8, extension methods are in place. Check out Statement:
http://hg.openjdk.java.net/lambda/lambda/jdk/file/ee1fce80ec31/src/share/classes/java/sql/Statement.java

It contains:

1091 default long getLargeUpdateCount() throws SQLException {
1092 throw new
UnsupportedOperationException("getLargeUpdateCount not implemented");
1093 }

Beautiful, eh? :-)

> I feel that you simply say "JDBC mock is much easier" because you never

> actually tried to build one. I wrote 3 JDBC drivers, [...]

jOOQ has a couple of JDBC implementations. That's not the same as
implementing a driver, of course.

> I wrote patches to
> MockRunner and I wrote unit tests using MockRunner. The JDBC API, frankly,
> sucks. From all the Java APIs, it's the most complex by far, it's the most
> brittle and the one where they broke every rule regarding API evolution.

It sucks, yes. But it's a well-defined API, unlike jOOQ's internals.

> JDBC is so complicated that it took Oracle more than 10 years to build a
> driver which doesn't have many major bugs like leaking Cursors when using
> BLOBs.

I doubt that this is because of JDBC. BLOBs are a nasty thing in a
database that doesn't support VARCHAR2(N) with N > 4000...

> Just look at org.jooq.impl.FieldTypeHelper.getFromResultSet(ExecuteContext,
> Class<? extends T>, int) to get an idea how much of the ResultSet API I'd
> have to replicate.

Here's another suggestion: Since I...

- really don't want to give any guarantee about jOOQ's internals to you
- understand your use-case
- want to add value for the community

I could implement MockConnection, MockStatement, etc. There could be
something like a MockDataProvider:

interface MockDataProvider {
// Callback for org.jooq.Query queries
int execute(String sql, Object[] bindValues);

// Callback for org.jooq.ResultQuery queries
// The actual signature may vary, in order to
// be able to implement ResultSetMetaData
Object[][] fetch(String sql, Object[] bindValues);
}

You as a user would only have to implement this MockDataProvider and
pass it to the jOOQ Factory like this:

new Factory(new MockConnection(new MyMockDataProvider()), dialect);

What about this approach? I'd be willing to maintain and integration
test such an API - without competing with DBUnit and other tools, of
course.

> And even when I could mock Cursor.fetch(), ResultImpl is package private so
> I'd have to cut&paste these 1931 lines to implement Result unless putting my
> code into org.jooq.impl which I don't like.

Well, jOOQ would hardly evolve if I didn't keep the impl parts package
private. Every release would be a major release, then...

[digulla]

On Friday, February 15, 2013 2:37:28 PM UTC+1, Lukas Eder wrote:

So when using jOOQ, you only have to "support" JDBC 4.0

This code would only compile with Java 6. Java 5 would complain about methods with @Override that don't exist in the interface and Java 7 would complain about unimplemented methods.

I'm not sure what happens when you try to run this on Java 7. Do you get a MethodNotImplementedException at runtime?

 

Yes, but with Java 8, extension methods are in place. Check out Statement:
http://hg.openjdk.java.net/lambda/lambda/jdk/file/ee1fce80ec31/src/share/classes/java/sql/Statement.java

It contains:

     1091     default long getLargeUpdateCount() throws SQLException {
     1092         throw new
UnsupportedOperationException("getLargeUpdateCount not implemented");
     1093     }

Beautiful, eh? :-)

:-P

 

> Just look at org.jooq.impl.FieldTypeHelper.getFromResultSet(ExecuteContext,
> Class<? extends T>, int) to get an idea how much of the ResultSet API I'd
> have to replicate.

Here's another suggestion: Since I...

- really don't want to give any guarantee about jOOQ's internals to you
- understand your use-case

 interface MockDataProvider { ... }

Example. I have this code in my DAO:

        UserRecord row = jooq.create()

            .selectFrom( ADM_USER )

            .where( ADM_USER.ADM_USER_ID.equal( pk ) )

            .fetchOne()

            ;

To write a test for this, I need to create 8 tables and fill them with data because of foreign key relations.

What I'd like:

    UserRecord testData = new UserRecord():

    ... set fields ...

    factory = new JooqTestSetup(dialect, settings)

        .query( "select A, LOT, OF, COLUMNS from ADM_USER where ADM_USER.ADM_USER_ID = 1" )

        .shouldReturn( testData );

query() takes the SQL string that you get from factory.renderInline() 1:1 (i.e. when I have formatting enabled, I need to pass the multi-line string)

Or there could be query(string, Object[] bindValues) and queryInline().

shouldReturn() takes one or more Record types to make it easier to write correct test data.

If it should return no results, I'd like returnsNothing().

I could live with the Object[][] solution as well but it will make tests more brittle/less readable. If you use record types, you get all the advantages of the existing API (type safety, compile errors when the DB schema changes, ...) And you can use test data factories to quickly many records. We could even wire them correctly to the factory by using the newRecord() method of the returned test factor.

I think queries are most important. But UPDATE/INSERT should be simple to add. I'm just not sure what the method names would be.

An additional feature would be throwsException(SQLException) to support testing error handling.

I could implement MockConnection, MockStatement, etc.

Okay, have fun :-)

 

> And even when I could mock Cursor.fetch(), ResultImpl is package private so
> I'd have to cut&paste these 1931 lines to implement Result unless putting my
> code into org.jooq.impl which I don't like.

Well, jOOQ would hardly evolve if I didn't keep the impl parts package
private. Every release would be a major release, then...

I don't doubt that. But I'm also 95% sure that doing what I want is much less effort by hiding it inside of jOOQ than by mocking JDBC. I don't know all the details how to do it and what the best solution would be and what parts of the API you can change, etc. But I'm sure this would be a great feature that will make testing DB related code more simple, more stable and much cheater to maintain than any other, existing approach.

People just never tried that because there was no abstraction layer between their code and JDBC. But when I had to write code using JDBC, I always used classes where the JDBC code was hidden in a few methods that I could easily overwrite from tests.

That way, I ended up with 5% JDBC tests (making sure the few JDBC methods do work) and 95% of the tests that don't notice when JDBC isn't there.

Regards,

A. Digulla

[Lukas Eder]

Hello,

>> So when using jOOQ, you only have to "support" JDBC 4.0
>
> This code would only compile with Java 6. Java 5 would complain about
> methods with @Override that don't exist in the interface and Java 7 would
> complain about unimplemented methods.
>
> I'm not sure what happens when you try to run this on Java 7. Do you get a
> MethodNotImplementedException at runtime?

It works perfectly. As I said, Java 5 is not supported by jOOQ, so the
@Override annotations aren't a problem. I don't see where Java 7
should cause problems, when my Connection implementations extend
JDBC41Connection.

Another option is to use proxies to implement JDBC interfaces. Then
you'll never run into problems and you can be compatible with Java 1.3
(whatever the JDBC version was back then)

> I could live with the Object[][] solution as well but it will make tests
> more brittle/less readable. If you use record types, you get all the
> advantages of the existing API (type safety, compile errors when the DB
> schema changes, ...) And you can use test data factories to quickly many
> records. We could even wire them correctly to the factory by using the
> newRecord() method of the returned test factor.

Yes, I've come to that conclusion, too. Assuming that we're talking
about the same: I'll probably introduce methods to the Factory that
allow for creating Tables with Fields, and then newRecord() can be
used to create records from such tables, and then newResult() should
be introduced to combine the whole thing.

Probably, MockDataProvider would have to look more like this:

------------------------------------------------------------
public interface MockDataProvider {
MockResult[] execute(String sql, Object[] bindings);
}
------------------------------------------------------------

... where MockResult holds some meta data. Still, this doesn't
correctly model batch statements

> I think queries are most important. But UPDATE/INSERT should be simple to
> add. I'm just not sure what the method names would be.

It should all be contained in a single method, as there is no real
restriction to whether DML methods can generate results. Take the
Postgres INSERT .. RETURNING statement. JDBC takes these facts into
account, even if the API is awkward.

> An additional feature would be throwsException(SQLException) to support
> testing error handling.

Good idea. So obviously, MockDataProvider.execute() should have a
throws SQLException clause

>> I could implement MockConnection, MockStatement, etc.
>
> Okay, have fun :-)

I'll send you a bill! ;-)

>> > And even when I could mock Cursor.fetch(), ResultImpl is package private
>> > so
>> > I'd have to cut&paste these 1931 lines to implement Result unless
>> > putting my
>> > code into org.jooq.impl which I don't like.
>>
>> Well, jOOQ would hardly evolve if I didn't keep the impl parts package
>> private. Every release would be a major release, then...
>
> I don't doubt that. But I'm also 95% sure that doing what I want is much
> less effort by hiding it inside of jOOQ than by mocking JDBC. I don't know
> all the details how to do it and what the best solution would be and what
> parts of the API you can change, etc. But I'm sure this would be a great
> feature that will make testing DB related code more simple, more stable and
> much cheater to maintain than any other, existing approach.

And I'm very sure that you underestimate the complexity of patching
jOOQ. jOOQ also supports:

- fetchMany(), i.e. fetching several result sets
- fetchLazy(), i.e. fetching records one-by-one
- keepStatement(), i.e. sharing PreparedStatements among executions
- batch processing,
- fetchUpdatable(), i.e. fetching with ResultSet.CONCUR_UPDATABLE.
This is not yet implemented in jOOQ, though.
- etc.

So in the end, I'm not sure if it's really simpler to mock / patch
jOOQ rather than implementing those parts of JDBC that are used by
jOOQ. Given that jOOQ itself could use those MockConnection objects
itself for various unit / integration tests, I think we'll get much
more traction out of that approach, rather than doing something that
solves your immediate problems (which do not yet include
fetchMany/fetchLazy, batch/DML/etc.).

Cheers
Lukas

[Lukas Eder]

Good news, these artefacts are implemented on Github master for jOOQ 3.0:

JDBC implementations:
- MockConnection: https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/tools/jdbc/MockConnection.java

- MockStatement: https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/tools/jdbc/MockStatement.java
- MockResultSet: https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/tools/jdbc/MockResultSet.java

- MockResultSetMetaData: https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/tools/jdbc/MockResultSetMetaData.java

(ResultSet and ResultSetMetaData already existed. They were previously known as org.jooq.impl.ResultSetImpl)

Testing interface:
- MockDataProvider: https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/tools/jdbc/MockDataProvider.java
- MockExecuteContext: https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/tools/jdbc/MockExecuteContext.java
- MockResult: https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/tools/jdbc/MockResult.java

The interface looks like this:

------------------------------------------------------------

public interface MockDataProvider {

    MockResult[] execute(MockExecuteContext ctx) throws SQLException;

}

------------------------------------------------------------

MockResult wraps Statement#getUpdateCount() and Statement#getResultSet(). The above API allows for mocking:

- Statements without results

- Statements without results but with generated keys

- Statements with results

- Statements with several results

- Batch statements with single queries and multiple bind value sets

- Batch statements with multiple queries and no bind values

Usage examples can be seen here:

https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/test/java/org/jooq/test/MockTest.java

More documentation will be contained in the manual

[digulla]

On Saturday, February 16, 2013 10:36:06 AM UTC+1, Lukas Eder wrote:

Good news, these artefacts are implemented on Github master for jOOQ 3.0:

That was fast :-)

JDBC implementations:
- MockConnection: https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/tools/jdbc/MockConnection.java

- MockStatement: https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/tools/jdbc/MockStatement.java
- MockResultSet: https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/tools/jdbc/MockResultSet.java

- MockResultSetMetaData: https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/tools/jdbc/MockResultSetMetaData.java

(ResultSet and ResultSetMetaData already existed. They were previously known as org.jooq.impl.ResultSetImpl)

The code doesn't compile with Java 7. This is intentional, I guess?

Usage examples can be seen here:

https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/test/java/org/jooq/test/MockTest.java

I looked through the code. There is no fluent API to build test data. Is that planned?

Regards,

A. Digulla

[Lukas Eder]

> The code doesn't compile with Java 7. This is intentional, I guess?

It works for me both in Eclipse and with Maven. Can you be specific?

>> Usage examples can be seen here:
>>
>> https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/test/java/org/jooq/test/MockTest.java
>
> I looked through the code. There is no fluent API to build test data. Is
> that planned?

Not right now. I'll first see how this is adopted. Feel free to
suggest / contribute one

Cheers
Lukas

[Lukas Eder]

> Not right now. I'll first see how this is adopted. Feel free to
> suggest / contribute one

Note that I'm going to implement a file-based API soon, as I think
that is easier to handle for end users:
https://github.com/jOOQ/jOOQ/issues/2208

A draft implementation is already included in 3.0-RC1

[digulla]

On Monday, February 18, 2013 11:51:30 AM UTC+1, Lukas Eder wrote:

> The code doesn't compile with Java 7. This is intentional, I guess?

It works for me both in Eclipse and with Maven. Can you be specific?

I get these errors with Java 7:

[INFO] --- maven-compiler-plugin:2.3.2:compile (default-compile) @ jooq ---

[INFO] Compiling 641 source files to /home/adigulla/packages/jOOQ/jOOQ/target/classes

[INFO] -------------------------------------------------------------

[ERROR] COMPILATION ERROR : 

[INFO] -------------------------------------------------------------

[ERROR] /home/adigulla/packages/jOOQ/jOOQ/src/main/java/org/jooq/impl/AbstractRecord.java:[546,38] error: incompatible types

[ERROR]     Object

/home/adigulla/packages/jOOQ/jOOQ/src/main/java/org/jooq/impl/AbstractRecord.java:[606,30] error: incompatible types

[ERROR]     Object

/home/adigulla/packages/jOOQ/jOOQ/src/main/java/org/jooq/impl/UpdatableRecordImpl.java:[90,36] error: incompatible types

[ERROR]     Object

/home/adigulla/packages/jOOQ/jOOQ/src/main/java/org/jooq/impl/UpdatableRecordImpl.java:[243,30] error: incompatible types

[ERROR]     Object

/home/adigulla/packages/jOOQ/jOOQ/src/main/java/org/jooq/impl/UpdatableRecordImpl.java:[387,30] error: incompatible types

[ERROR]     Object

/home/adigulla/packages/jOOQ/jOOQ/src/main/java/org/jooq/impl/UpdatableRecordImpl.java:[451,30] error: incompatible types

[INFO] 6 errors 

>> Usage examples can be seen here: 
>>
>> https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/test/java/org/jooq/test/MockTest.java
>
> I looked through the code. There is no fluent API to build test data. Is
> that planned?

Not right now. I'll first see how this is adopted. Feel free to
suggest / contribute one

Err ... contributing code for your project in the past was neither rewarding nor successful for me. So I'll just repeat myself:

    UserRecord testData = new UserRecord():

    ... set fields ...

    factory = new JooqTestSetup(dialect, settings)

        .query( "select A, LOT, OF, COLUMNS from ADM_USER where ADM_USER.ADM_USER_ID = 1" )

        .shouldReturn( testData );

https://groups.google.com/d/msg/jooq-user/OQzuxbqk-w8/Q5wV3cxc3DYJ

Regards,

A. Digulla

[digulla]

How about a parser that can read the output of Record.toString()? That would make it dead simple to produce the test data (just run the query once against a real database).

Regards,

A. Digulla

[Lukas Eder]

Hmm, try upgrading. These issues were fixed with
https://github.com/jOOQ/jOOQ/commit/047014fe811c0df2d5946bee13185530f5d78879

In this case, the issues had nothing to do with Java 7. From time to
time, I'm having issues with javac's different understanding of
generics compared to those of the Eclipse compiler

>> >> Usage examples can be seen here:
>> >>
>> >>
>> >> https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/test/java/org/jooq/test/MockTest.java
>> >
>> > I looked through the code. There is no fluent API to build test data. Is
>> > that planned?
>>
>> Not right now. I'll first see how this is adopted. Feel free to
>> suggest / contribute one
>
> Err ... contributing code for your project in the past was neither rewarding
> nor successful for me.

Here we go again.

I can but express time and again that I'm sorry that my words /
decisions made you feel this way. Contrary to your beliefs and
insisting, I did take many of your suggestions seriously, including
the idea of mocking a database (what we're discussing right now), or
the idea of a ConnectionProvider (which is included in 3.0-RC1). I
could probably list 1-2 other items, but I don't know why taking
things personal on this list is so important to you. Maybe you might
consider eventually getting over it and try to be constructive? I'd
greatly apreciate that.

[Lukas Eder]

>> Note that I'm going to implement a file-based API soon, as I think
>> that is easier to handle for end users:
>> https://github.com/jOOQ/jOOQ/issues/2208
>>
>> A draft implementation is already included in 3.0-RC1
>
> How about a parser that can read the output of Record.toString()? That would
> make it dead simple to produce the test data (just run the query once
> against a real database).

Hmm, currently, Record.toString() and Result.toString() do some
formatting, which involve truncating of data to at most 50 characters
(indicating that fact with ellipses : ...)
But I can see your point of having a two-way serialisation /
deserialisation mechanism, that could be re-usable. At least to a
certain extent (data type information is missing from serialised
tables).

I'll think about this, and add a comment to #2208

[Lukas Eder]

Note, that your idea led to me thinking that it should be possible to
load all sorts of formats that are supported by jOOQ:
http://www.jooq.org/doc/3.0/manual/sql-execution/exporting/

Executor.fetchFromXML(): https://github.com/jOOQ/jOOQ/issues/2233
Executor.fetchFromJSON(): https://github.com/jOOQ/jOOQ/issues/2234
Executor.fetchFromCSV(): Exists already, since jOOQ 2.4
Executor.fetchFromTXT(): https://github.com/jOOQ/jOOQ/issues/2235

In particular, loading from TXT should be somewhat lenient. The reason
why I started implementing the MockFileDatabase in an H2-integration
test style is because it is just simpler to manually write that H2
result set format (without all the border lines: +---+-----+, etc)

[Lukas Eder]

Hi Adam,

> I sort of stumbled on this thread a little late but is the issue that you
> (digulla) can't assert the SQL that is generated by jOOQ?

Hmm, yes, looks like you're a bit late ;-)
The latest suggestion by Aaron here was to find a way that

String s = record.toString();
Record other = someParseMethod(s);
record.equals(other); // More or less

His idea here was that actual test data for this new MockFileDatabase
could be dumped from sample queries with jOOQ's DEBUG logger. The
logger would then produce both a relevant SQL statement, and the
result in a similar way that I had originally suggested (H2 test
format, as can be seen here:
http://code.google.com/p/h2database/source/browse/trunk/h2/src/test/org/h2/test/test-1.3.txt)

> It sounds like what your looking for an abstraction above JDBC to mock. I
> did this for my library:
> https://github.com/agentgt/jirm/tree/master/jirm-core/src/main/java/co/jirm/core/execute

Mocking jOOQ's API in this way would probably be worse than mocking
JDBC, as the API evolves quickly, and there are already hundreds of
methods in various org.jooq.* types. Aaron's original idea was to be
able to "inject" results to jOOQ's internals, preventing actual
execution.

> However I think the spirit of jOOQ is to embrace JDBC so JDBC is exposed
> much more all over.
> Thus I think Lukas's Mocking JDBC contributions should be a good starting
> point.

Yes. JDBC is the right place to inject mock data, as it has the least
impact on jOOQ's delicate internals

> Of course I think your insane for mocking JDBC when you can just execute to
> a real database... but I too used to drink the mocking cool-aid :)

Well, if the mocking is done for you transparently, in a library, then
you're not mad anymore (except me for actually doing it). But I share
your feelings about the "real database". There are lots of advantages
in running nightly integration tests, when it comes to large-scale
testing. It is extremely hard to mock things like:

- Concurrent transactions
- Pessimistic locking
- "advanced" CRUD (i.e. modifying more than one table in a
semantically correct sequence)
- etc.

Anyway, it's good that the user can choose, now. There are 1-2
use-cases for real unit tests that don't run against a DB

Cheers
Lukas

[Lukas Eder]

Hi,

These articles about mocking your database get a lot of traction and
generate many clicks, which is good:

http://blog.jooq.org/2013/02/20/easy-mocking-of-your-database
http://architects.dzone.com/articles/easy-mocking-your-database-0
http://www.javacodegeeks.com/2013/02/easy-mocking-of-your-database.html

I'm sure that there's room for follow-up articles, once best practices
have been established, and once the feature-set is more complete. So
let's list the features we'd want to have:

- More meaningful input formats for MockFileDatabase
- Pattern matching in MockFileDatabase
- A DSL API for setting up tests. Aaron's suggestion is this one:

> UserRecord testData = new UserRecord():
> ... set fields ...
> factory = new JooqTestSetup(dialect, settings)
> .query( "select A, LOT, OF, COLUMNS from ADM_USER where
> ADM_USER.ADM_USER_ID = 1" )
> .shouldReturn( testData );
>
> https://groups.google.com/d/msg/jooq-user/OQzuxbqk-w8/Q5wV3cxc3DYJ

The above DSL should have the same features as the MockFileDatabase,
just in a programmatic way. Also, it should be easy to define the
outcome of batch and multi-result queries

Are there any other features that would be immediately useful?

Cheers
Lukas