DAO Unit Test

[Debapriya Patra]

Hello,

I was writing unit test for a DAO function where I have the sorting applied in data while pulling it from DB.

I have added the mock data randomly(no sorting) in the .txt file and when I ran the unit test, it seems like the result is however I have put the mock data in the file not sorted way. 

Is that the expected behavior ?

I was in an impression that Mock data is basically data stored in DB not the DB returned by the query. But it seems like the mock data is the returned data by the query. It does not apply sorting.

Example: Following is my mock data and data returned in the same way its there in the mock file not applying sorting for the title column.

#testGetEduContentsByContentIdsSortByTitle
select `content_management`.`edu_content`.`id`, `content_management`.`edu_content`.`title`, `content_management`.`edu_content`.`description`, `content_management`.`edu_content`.`media_type`, `content_management`.`edu_content`.`edu_document_type`, `content_management`.`edu_content`.`content_status`, `content_management`.`edu_content`.`custom_document_type`, `content_management`.`edu_content`.`original_file_s3_url`, `content_management`.`edu_content`.`preview_file_s3_url`, `content_management`.`edu_content`.`thumbnail_file_s3_url`, `content_management`.`edu_content`.`created_ts`, `content_management`.`edu_content`.`updated_ts`, `content_management`.`edu_content`.`exam_name`, `content_management`.`edu_content`.`year`, `content_management`.`edu_content`.`term`, `content_management`.`edu_content`.`comment`, `content_management`.`edu_content`.`deleted`, `content_management`.`edu_content`.`num_pages` from `content_management`.`edu_content` where `content_management`.`edu_content`.`id` in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) order by `content_management`.`edu_content`.`updated_ts` desc limit ?;
> id title description media_type edu_document_type content_status custom_document_type original_file_s3_url preview_file_s3_url thumbnail_file_s3_url created_ts updated_ts exam_name year term comment deleted num_pages
> ------------------------------------- ----------- ------------- ----------------- -------------------- ------------------------- ---------------------- ----------------------- ----------------------- ------------------------- ---------------------- --------------------- ----------- ------- -------- ------- ---------- -----------
> a32ba478-90d9-4c7c-85e9-2899bddab41f Zunit test test application/pdf SYLLABUS SYLLABUS_REQUIRED null original/unittest.pdf preview/unittest.pdf thumbnail/unittest.pdf 2021-04-16 11:27:46 2021-04-21 11:27:46 midterm 2021 summer test false 1
> b32ba478-90d9-4c7c-85e9-2899bddab41e Nunit test test application/pdf SYLLABUS SYLLABUS_REQUIRED null original/unittest.pdf preview/unittest.pdf thumbnail/unittest.pdf 2021-04-06 11:27:46 2021-04-27 11:27:46 midterm 2021 summer test false 1
> c32ba478-90d9-4c7c-85e9-2899bddab41f Funit test test application/pdf QUIZ PENDING_VERIFICATION null original/unittest.pdf preview/unittest.pdf thumbnail/unittest.pdf 2021-04-26 11:27:46 2021-04-10 11:27:46 midterm 2021 summer test false 1

Thanks,

Deba

[Rob Sargent]

Who/what would do the sorting?  Not jOOQ for certain.
Is testing against a RDBMS completely out of the question?

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/a7238fca-ee95-4b05-b46f-2a4dd1c35313n%40googlegroups.com.

[Lukas Eder]

Hi Deba,

You're observing the expected behaviour. The MockFileDatabase is an extremely dumb "database product". It matches input "SQL" strings (it doesn't even have to be actual SQL, it's just a random string to the MockFileDatabase) with static output result sets and/or update counts. It's completely static. It has no knowledge of the SQL language or storage or operations such as "sorting" or "grouping" or whatever.

You're expecting it to sort things. Based on what? The fact that you used the English words "SortByTitle" in a comment? How would that work? Using GPT-3? Where would it stop? Sorting? Grouping? Calculating window functions? Tracking state? Transactions? Locks? Concurrency? You know what a thing is called that does all of that? An actual database product :)

The MockFileDatabase is useful for a limited number of things, like:

- Unit testing stuff that's relatively query agnostic, such as mapping logic

- Quick and dirty prototyping (with an emphasis on "quick and dirty" and on "prototyping")

- Luring folks who google "mock jdbc" or "mock database" into using jOOQ. They came for the mocking (and I'll talk them out of it immediately again), but they're staying for how awesome jOOQ is :)

I'll repeat my suggestion to start using https://www.testcontainers.org or any other means of integration testing instead of mocking. It is also mentioned here:

https://www.jooq.org/doc/latest/manual/sql-execution/mocking-connection/

I'll make sure that disclaimer is copied to the MockFileDatabase's Javadoc and relevant section of the manual. For the reference, it is here:

Disclaimer: The general idea of mocking a JDBC connection with this jOOQ API is to provide quick workarounds, injection points, etc. using a very simple JDBC abstraction. It is NOT RECOMMENDED to emulate an entire database (including complex state transitions, transactions, locking, etc.) using this mock API. Once you have this requirement, please consider using an actual database instead for integration testing, rather than implementing your test database inside of a MockDataProvider.

You shouldn't build an entire database product based on this. It will never work. You'll never get it "right". Use an *actual* database product and run your queries against it. If you must: Use H2 or similar (which has its own set of issues, only recommended if you're also using H2 in production, or if you're supporting 10 different dialects anyway). If you can, use https://www.testcontainers.org, and run everything against your actual production database product with actual sample data.

In case I still couldn't convince you to integration test your application instead, well, you'll have to provide the result sets for each of your possible queries, including:

- select .. from ... order by column

- select .. from ... order by column asc

- select .. from ... order by column desc

I hope this helps

Lukas

--

[Lukas Eder]

For the record, here's the issue improving the Javadocs to add this disclaimer more prominently:

https://github.com/jOOQ/jOOQ/issues/12053

I don't know if it will really help. People seem to have such strong opinions about mocking everything :)