Digest for jooq-user@googlegroups.com - 7 updates in 2 topics

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Digest for jooq-user@googlegroups.com - 7 updates in 2 topics

http://groups.google.com/group/jooq-user/topics mailing list
[hidden email]: Nov 29 03:21AM -0800

Hi!
 
I've been trying to write a mocked test that "proves" the correctness of a
query.
 
The query looks like this:
 
dslContext.select(TBL_TASK.STATE) //
.from(TBL_TASK) //
.where(TBL_TASK.ID.eq(taskId)) //
.fetchOne(TBL_TASK.STATE);
 
 
And the test looks like this:
 
int taskState = 5;
int taskId = 8;
 
MockDataProvider provider = context -> {
DSLContext create = DSL.using(SQLDialect.MYSQL);
MockResult[] mock = new MockResult[1];
 
Result<Record1<Integer>> result = create.newResult(TBL_TASK.STATE);
result.add(create.newRecord(TBL_TASK.STATE).values(taskState));
mock[0] = new MockResult(1, result);
return mock;
};
 
Connection connection = new MockConnection(provider);
DSLContext create = DSL.using(connection, SQLDialect.MYSQL);
Assert.assertEquals(taskState, getTaskState(create, taskId));
 
 
 
This seems to work fine when you try for example try to
"fetchOne(TBL_TASK.ID)" or try to "select(TBL_TASK.ID)"
 
But this:
 
dslContext.select(TBL_TASK.ID) //
.from(TBL_TASK) //
.where(TBL_TASK.ID.eq(taskId)) //
.fetchOne(TBL_TASK.ID);
 
 
Also works fine for some reason, even though the mock returns a record
containing a state-field? Maybe I'm doing something wrong or have the wrong
expectations about how mocking works, but it looks like the
fetchOne-function doesn't care about the field name?
 
Like I said, the test can prove that the query itself is written wrong
(selecting state but fetching id), but it can't prove that you aren't
trying to correctly access a completely different column (selecting and
fetching id-column when mock returns state-column).
Lukas Eder <[hidden email]>: Nov 29 12:41PM +0100

Hello
 
> containing a state-field? Maybe I'm doing something wrong or have the wrong
> expectations about how mocking works, but it looks like the
> fetchOne-function doesn't care about the field name?
 
The fetchOne() method doesn't influence how the query is executed in any
way, it simply specifies how the result should be processed, once it is
available (i.e. it fetches at most one record and in your case, only one
column from that record).
 
So, the interesting part is the select() clause, which tells jOOQ that the
query expects exactly one column of a given type. Now that the row type is
known to jOOQ (and jOOQ expects JDBC drivers to produce something
reasonable, and this includes your MockDataProvider), jOOQ doesn't have to
rely on any column name but can access the result values by index - which
is much faster in most JDBC drivers.
 
If you didn't provide any columns in the select() clause and the from()
clause also didn't provide the entirety of all columns (e.g. because there
is plain SQL involved), then jOOQ would detect reported column names from
the JDBC driver through ResultSetMetaData.
 
 
> (selecting state but fetching id), but it can't prove that you aren't
> trying to correctly access a completely different column (selecting and
> fetching id-column when mock returns state-column).
 
You got the "wrong" part wrong. The query isn't written wrong. It's just
that your MockDataProvider will always return that particular 1x1 result
and since jOOQ already knows the resulting rowtype in advance (you provided
it), it doesn't check ResultSetMetaData for what the *actual* column names
and types might be.
 
I hope this helps,
Lukas
[hidden email]: Nov 29 06:30AM -0800

Thank you, that helps somewhat. You're basically saying that me specifying
a column (fetchOne(state)) just fetches the first index instead, yes? I
verified this behavior by mocking a bad result where two columns switched
places (select(id, state) returns result "state,id").
 
I am still a bit unsure about the correct approach to writing a test that
verifies that the query is selecting the correct column name (ignoring the
topic of whether there is any real point to doing this in the first place).
Maybe it's more effort than it's actually worth?
 
It's not a huge deal really, but as a jOOQ-rookie I figured I should try to
figure out if it is doable!
 
onsdag 29. november 2017 12.41.29 UTC+1 skrev Lukas Eder følgende:
Lukas Eder <[hidden email]>: Nov 29 03:53PM +0100

> a column (fetchOne(state)) just fetches the first index instead, yes? I
> verified this behavior by mocking a bad result where two columns switched
> places (select(id, state) returns result "state,id").
 
No, be careful, that's not what I was saying. YOU specified that STATE was
the column at the first index by *putting it there* in your select()
clause. Again, fetchOne() has nothing to do with this. Here's the workflow:
 
1. select(STATE).fetchXYZ() runs a query expecting a STATE column at index
1 ... 1-based ;)
2. Because all the resulting rowtype information is known at compile time
(because you put that column there), there's no need to look up whatever
columns are returned by the database. We *expect* STATE to be the column at
index 1.
3. Your MockDataProvider happens to produce a row with exactly 1 column.
That might as well be a coincidence.
4. fetchLazy() (called internally by fetchOne()) now ignores column names
and fetches only one column from index 1, again, because YOU said so.
5. fetchOne(STATE) is simply convenience API to map the
Result<Record1<Integer>> result to a more convenient List<Integer> type by
looking up the STATE column from the result of 4.
 
Step 3 is important! MockDataProvider mocks your entire database for *ALL*
the queries that are sent through the MockConnection. Try it with JDBC
directly:
 
try (Connection c = new MockConnection(...);
Statement s = c.createStatement();
ResultSet rs = s.executeQuery("blah")) {
while (rs.next())
System.out.println(rs.getString(1));
}
 
 
Now, it really doesn't matter if you replace "blah" by "SELECT STATE" .. or
by "SELECT ID". Since you're selecting only one column (index 1), whatever
is returned at index 1 will be placed in that column.
 
Why don't you just step through the library with a debugger (specifically
the MockDataProvider part). You'll see it will all make sense.
 
I am still a bit unsure about the correct approach to writing a test that
> verifies that the query is selecting the correct column name (ignoring the
> topic of whether there is any real point to doing this in the first place).
> Maybe it's more effort than it's actually worth?
 
I don't know what your use-case is. In general: I doubt that mocking is the
correct approach to testing jOOQ and/or the database. An integration test
with an actual database and an actual table might be simpler...
 
It's not a huge deal really, but as a jOOQ-rookie I figured I should try to
> figure out if it is doable
 
Well, using one of the more advanced lower level SPIs might be a bit more
challenging a task for a jOOQ-rookie, so you might have simply not chosen
the easiest challenge to start working with jOOQ with :)
[hidden email]: Nov 29 07:24AM -0800

Right, thank you! Your clarification is basically what I meant, but I
didn't express it too well I guess :D
 
As for my use-case, I suppose it was just an idea to implement an extra
check on the SQL query. jOOQ already generates classes based on the actual
tables, but when writing code there is nothing really stopping me from
writing a query that selects a set of columns but then tries to fetch the
result from a completely different column that was never queried for in the
first place.
 
Example:
 
dslContext.select(TBL_TASK.ID) //
.from(TBL_TASK) //
.where(TBL_TASK.ID.eq(taskId)) //
.fetchOne(TBL_TASK.STATE); // Not part of select!
 
Results in
IllegalArgumentException: Field ("db"."tbl_task"."state") is not contained
in Row ("db"."tbl_task"."id")
 
The mock-tests catches stuff like this, but I noticed that it didn't catch
me using the wrong column name altogether. It's a minor thing, but I was
curious to see if it would be possible to catch this scenario as well.
 
Anyway, thanks for the help so far, I've learned about some possible
pitfalls at least!
 
onsdag 29. november 2017 15.53.20 UTC+1 skrev Lukas Eder følgende:
Lukas Eder <[hidden email]>: Nov 29 04:57PM +0100

Sorry for the confusion. Just wanted to be sure there wasn't a
misunderstanding.
 
> writing a query that selects a set of columns but then tries to fetch the
> result from a completely different column that was never queried for in the
> first place.
 
Yeah, that sort of type safety can't really be guaranteed by the jOOQ API,
unfortunately, as Java doesn't support anonymous record types (yet). One
option would be to call Record1.value1(), in case of which you can be sure
of the field to exist, but you're back to referencing values by index,
which is certainly less readable.
 
 
> The mock-tests catches stuff like this, but I noticed that it didn't catch
> me using the wrong column name altogether. It's a minor thing, but I was
> curious to see if it would be possible to catch this scenario as well.
 
I don't think it is. You'd have to get deep into jOOQ and mock Records
themselves, which is currently not possible, although it might be possible
once we implement this:
https://github.com/jOOQ/jOOQ/issues/3313
 
Essentially, a jOOQ Record is a Map<Field<?>, ?>...
 
Thanks,
Lukas
Lukas Eder <[hidden email]>: Nov 29 05:28AM -0800

Version 3.10.2 - November 29, 2017
================================================================================
 
This is a 3.10 patch release with bug fixes
 
Features and Improvements
-------------------------
#6782 - Clarify DSL.currentDate() Javadoc: It creates ANSI SQL DATE values,
not Oracle DATE
#6784 - Add explicit unit of time specification to Query.queryTimeout()
#6810 - Provide maven-deploy script to install artifacts on remote
repository
 
Bug Fixes
---------
#6682 - Typo in ResultQuery Javadoc: thorws should be throws
#6685 - Avoid creating a new DefaultConfiguration in DSLContext.select()
and similar methods
#6687 - Avoid allocation of CursorImpl.intern array
#6692 - Prevent calling DSL.name() with empty arguments
#6697 - Remove the DATA_LOCALLY_SCOPED_DATA_MAP in SelectQueryImpl
#6700 - DSL.trueCondition() and DSL.falseCondition() should return constants
#6710 - Parser.parse() fails on trailing whitespace
#6711 - Avoid generating code with a redundant (Object) cast
#6713 - Redundant Javadoc generated when deprecation for unknown types is
generated
#6716 - PostgreSQL RETURNING <col> AS <alias> is not rendered correctly
#6731 - Typo in Cursor.fetchOne() Javadoc's deprecation hint
#6738 - jooq-runtime-3.10.0 lists wrong version in namespace
#6743 - Oracle OBJECT type declarations inside generated RECORD
declarations are not bound correctly, if NULL
#6748 - Avoid allocating a fresh "ExecuteListeners" instance in the absence
of actual ExecuteListeners
#6750 - Avoid adding the LoggerListener if the log level is more than DEBUG
#6753 - Avoid various array allocations due to unnecessary varargs
#6756 - Avoid unnecessary UnqualifiedName[] allocations in QualifiedField
etc.
#6759 - Avoid allocation of AbstractContext.visitListeners if unneeded
#6775 - Improve MockDataProvider Javadoc about null or empty return values
#6780 - Oracle's quoted string literals start with case-insensitive Q letter
#6786 - Result.format() breaks when there is a tabulator in the content
#6788 - Inefficient ProxyMapper allocates a new MutablePOJOMapper delegate
every time
#6791 - ConvertAll inefficiently unboxes and boxes primitive types
#6801 - Optimise internal reflection cache by avoiding cache key arrays
where possible
#6805 - Override equals() and hashCode() in org.jooq.impl.Fields
#6808 - Improve DefaultRecordMapper::init MappingException message
#6828 - Error when passing java.util.Date to DSL.year(), month(), day(),
hour(), minute(), second()
#6830 - DayToSecond generates nanosecond precision for DAY_MICROSECOND
interval
#6842 - MockResultSet.getTimestamp(int, Calendar) ignores Calendar
#6844 - SQLDataType.INTEGER.identity(true) not working for SQLLite
#6846 - <deprecationOnUnknownTypes/> has no effect
#6848 - Code generator doesn't work when Derby database uses non-standard
collation
#6858 - SQLiteTableDefinition.toString() prints "." after empty schema
#6869 - Code generator doesn't work in Java 6 build, which calls
java.lang.reflect.Method.getParameterCount()
You received this digest because you're subscribed to updates for this group. You can change your settings on the group membership page.
To unsubscribe from this group and stop receiving emails from it send an email to [hidden email].