Add full-stack test for MySQL

[jdegoes]

Depends on #149

We need a full-stack test for MySQL.

We need an automated ZIO Test that, when run, will execute a simple query, such as select 1, against a live-running MySQL, and verify the result set returned from the query.

This full-stack test will be the foundation for adding other more complicated tests.

This can follow the established pattern from PostgresModuleTest.

[andrewhj]

I'll give this one a shot

[andrewhj]

Looking at how PostgresqlModule is done, it appears would be duplicate logic (particularly utilities like TestContainer). Would it make sense to move TestContainer to a more common location? Would jdbc be the appropriate module?

[andrewhj]

I've been unable to figure out the scala/zio syntax to perform a "select 1" call. I think I'm missing something simple. The (still unworking) syntax I thought made sense was
select(constant(1)).
However, this yields an error: Cannot prove that query.ResultType <:< (Integer, Unit).

In my branch I have test container setup for mysql and enough code in place to get a table selection test working so if anyone can offer some guidance (here or on discord) I'd love to get this example put together and merged.

[andrewhj]

created: https://gist.github.com/andrewhj/84ed6c26afdcac56a690f0377a7fff79 for reference

[jdegoes]

@andrewhj I am not sure what problem you're running into here, but we will investigate and fix!

[andrewhj]

I managed to get a table select test working (just not the simple "select 1" case). I will clean up & submit PR for that case.

[robmwalsh]

@andrewhj at the moment we need to select from a table, which is probably not ideal, but is the way that the syntax is currently implemented ( from memory select returns a SelectBuilder which has a function from(table: Table) which is what actually returns a Read.Select, which is the type that we need to execute a query. It's possible to fix this but probably not high on the priority list given there's a simple workaround; use select(1) from customers instead of select 1. I'll create an issue for this anyway. created #323

[andrewhj]

makes sense. 1 thing to consider (at least on postgres and mysql), is that you'll get a set back equal in size to the table you select. If customer has 5 rows, you'll get back 5 rows of 1. However, if customer is empty, you will get back an empty set (instead of 1).

[robmwalsh]

Right, I didn't think of that! In that case it should probably be a higher priority. In the meantime you can follow the other tests and just take the first returned value (or you can have a shot at fixing the issue - happy to provide assistance if needed)

[jdegoes]

Actually this is correct as per SQL specification. SELECT 1 FROM table must return as many results as are in the table. Now some databases just support SELECT 1, i.e. a glorified calculator. But such queries are not realistic so I don't think it's a priority to support them. (Sorry I missed the fact the table was missing; we should have a better error message here, anyway.)

…

On Wed, Nov 25, 2020 at 2:23 AM Unclebob ***@***.***> wrote: Right, I didn't think of that! In that case it should probably be a higher priority. In the meantime you can follow the other tests and just take the first returned value (or you can have a shot at fixing the issue - happy to provide assistance if needed) — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#69 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AABGISMPFUR32SEMUEGFXTDSRSWFZANCNFSM4Q73YIJQ> .

[robmwalsh]

I meant higher priority only in that it'd mean simpler/faster tests for all the glorified calculator style tests we have to run. Once all the PRs are merged I think I'll go through and tidy them up, break into suites etc; I can take care of it then.