FR/Q: Unit tests on MySQL

Are there any improvements planned regarding Unit Tests on certain types of databases (MySQL, in particular)? Not being able to use statement batches to initialize/test makes it rather inconvenient to utilize it easily.

The code from the initialize, test, and cleanup sections is sent to the database as entered, as a single anonymous SQL block. As far as I know, there is no support in MySQL for running anonymous code blocks and the only way to run such code is by implementing stored procedures.

One way to workaround that limitation trick is to create a schema in database named like test_cases, and then, in the unit test init section create a temporary procedure in that schema, in the test section execute it, and in the cleanup section drop it. Hope this works for you

	SysOp wrote:
	One way to workaround that limitation trick is to create a schema in database named like test_cases, and then, in the unit test init section create a temporary procedure in that schema, in the test section execute it, and in the cleanup section drop it. Hope this works for you

That's dead-on what I was referring to as "rather inconvenient" :)

EDIT: Besides, it asks for a file name every time I'm saving the unit testing project, even if it had been loaded from a file. It also closes the Unit Testing window upon closing the tab which was active when it was invoked. I'm not sure if that's by design or not, but there's nothing indicating which tab it is connected to and it might get closed unintentionally as a result. I guess that's because they share the connection. Still, it would be nice if that window would automatically reopen after eg. applying changes to SA Option, which re-initializes all connections open in SE.

	Quote:
	That's dead-on what I was referring to as "rather inconvenient" :)

Totally with you. But I would put the onus on MySQL. It's "rather inconvenient" that such a popular database server still doesn't support such basic function as executing multiple SQL statements as a single block and requires wrapping them in a stored procedure. But that's still a significant progress considering that stored procedures were introduced in MySQL 5.0, 20 years after Oracle started supporting PL/SQL programs, 17 years after similar things appeared in SQL Server, and 10 years after PosgtreSQL. But alas, 12 years have passed since introduction of stored procedures in MySQL and there is still no support for anonymous blocks, that's rather weird, isn't it? :-)

Seriously, the Unit Testing Framework implementation is database agnostic, given a block of code in the referenced sections it sends them to the database as is without additional interpretation. I'm sure that can be improved. perhaps an option can be added for the execution mode, e.g. execute all at once, or parse and execute statement by statement.

Quote:

EDIT: Besides, it asks for a file name every time I'm saving the unit testing project, even if it had been loaded from a file. It also closes the Unit Testing window upon closing the tab which was active when it was invoked. I'm not sure if that's by design or not, but there's nothing indicating which tab it is connected to and it might get closed unintentionally as a result. I guess that's because they share the connection. Still, it would be nice if that window would automatically reopen after eg. applying changes to SA Option, which re-initializes all connections open in SE.

Would you please describe step by step what you mean by "It also closes the Unit Testing window upon closing the tab which was active when it was invoked?" I don't think that behavior is intentional if I get correctly what you mean.

Yes, MySQL has some fairly displeasing qualities besides the ones you mentioned, like teen-aged open or won't fix cases for bugs that just seem to be there to make our lives miserable. I'm also aware that this one is definitely not on you but on them, yet they don't seem to be bothering or fixing it.

But I've seen you going out of your way to make things better for us in the past, hence I thought you might have some plan for this, since MySQL is pretty popular. Incidentally, it was exactly an option to execute statement by statement was what I had on my mind. It definitely would have some unwanted consequences and I don't think filtering them out would be an easy goal to achieve either. I'm not pushing a feature request for this one, I was just curious if you had something planned.

Regarding the Unit Testing window, I made a short video you watch here. I open 5 empty tabs in SE, then switch to 3rd one and open Unit Testing window from there. I see no connection properties in Unit Testing window, so I'm pretty sure it shares connection with tab #3. Closing 5th and then 2nd does nothing unexpected but when I close 3rd, it closes Unit Testing window as well.

Last edited by gemisigo on Thu Aug 17, 2017 7:57 am; edited 1 time in total

I have submitted an enhancement request to add SQL execution mode option to the unit tests which would enable executing them as blocks (current version) or as a group of individual statements executed one by one.

Thank you very much for the video. Now it makes sense. I'm glad I asked for it because it's not what I thought. The connection is shared with the 3rd tab indeed. And yet, I'm not sure why it closes the unit tests window. That doesn't happen to other tools. The behavior documented in your video was common in before 9.0 but not anymore. I'm going to log a ticket for this one.

Being afraid it might not have been noticed, I've moved this out of my previous post

FR #1: Shouldn't Cleanup part run regardless of Initialize and/or Execute parts succeeding or failing? I create a stored procedure in Initialize, run it in Execute, evaluate its results in Check, and destroy it in Cleanup. But it seems that if either Execute or Check fails, Cleanup isn't run: trying to run it again fails right away at Initialize because the stored procedure hasn't been dropped. After each failed case I have to manually clean up the mess that's left.

Last edited by gemisigo on Sun Aug 20, 2017 11:49 am; edited 1 time in total

After fidgeting with it for a while, I came to conclusion that since every tab can have its own Unit testing window, closing it with its corresponding tab is the proper solution after all.
FR #2: Reopening it when it's only changes that are applied would be good, though. The same goes for not having to select a file every time it's being saved and executing Cleanup phase regardless of the outcome of the previous phases.

FR #3: Allow customization of all four sections (Initialize, Execute, Cleanup, and Check) in case templates. It might not add much to database types allowing batch statements but having eg. the following templates in MySQL procedure template sections:

Initialize

	Code:
	CREATE PROCEDURE unit_tests.utsp_$OBJECT$ ( ) BEGIN CALL $OBJECT$ ($ARGS_V$=?); . . . do some selects to set some session variables that could be used by this and subsequent test cases as Result, eg. SELECT @result_set2_count or SELECT @result_set3_sum_some_value, etc. . . . and finally SELECT @my_result value to be checked against END;

Execute

	Code:
	CALL unit_tests.utsp_$OBJECT$();

Cleanup

	Code:
	DROP PROCEDURE IF EXISTS unit_tests.utsp_$OBJECT$;

could take over a ridiculous amount of repetitious typing work needed to properly set up testing of stored procedures.

I also noticed that the help states that the Result

	Quote:
	value is obtained from the first column of the first row of the first result set returned by the database server after executing the main SQL code.

Luckily, that's not the case, for it would reduce its usability to ashes. If it returned the first result set you wouldn't ever be able to test anything beyond that first column most of the times not even interesting at all, not to mention stored procedures that return multiple result sets, or not returning any, for that matter.

As far as I can tell, it returns the the first column of the first row of the last result set, which makes much more sense. No matter how many result sets the procedure to be dissected returns, you can always add your SELECT @myresult as the last result set and rest assured it will be the thing compared against the Result value in Check section.

EDIT: How do I check Result for NULL? I couldn't find anything about it in the help and it seems NULL is converted to empty string, which in turn cannot be checked for, since leaving Result empty won't fail the test regardless of the value returned by Check. Currently I've put the stuff I select into a CASE WHEN to convert it into 'OK' or 'NOT OK' but there surely must be an easier way, isn't there?

gemisigo wrote:

FR #1: Shouldn't Cleanup part run regardless of Initialize and/or Execute parts succeeding or failing? I create a stored procedure in Initialize, run it in Execute, evaluate its results in Check, and destroy it in Cleanup. But it seems that if either Execute or Check fails, Cleanup isn't run: trying to run it again fails right away at Initialize because the stored procedure hasn't been dropped. After each failed case I have to manually clean up the mess that's left.

Enhancement request #SA0031987

	gemisigo wrote:
	FR #2: Reopening it when it's only changes that are applied would be good, though. The same goes for not having to select a file every time it's being saved and executing Cleanup phase regardless of the outcome of the previous phases.

Enhancement request #SA0031960 - make Unit Tests framework independent from the originating editor window. It should get its own database connection, cache, and other internal things that it needs to "divorce" from the editor.

Last edited by SysOp on Mon Aug 21, 2017 8:14 am; edited 1 time in total

Quote:

Luckily, that's not the case, for it would reduce its usability to ashes. If it returned the first result set you wouldn't ever be able to test anything beyond that first column most of the times not even interesting at all, not to mention stored procedures that return multiple result sets, or not returning any, for that matter.

As far as I can tell, it returns the the first column of the first row of the last result set, which makes much more sense. No matter how many result sets the procedure to be dissected returns, you can always add your SELECT @myresult as the last result set and rest assured it will be the thing compared against the Result value in Check section.

This feature I believe was designed after SQL Server and other database servers supporting SQL blocks. where you can perform some activity, and then at the end check the status using something like

DECLARE @count INT = 0
EXEC some_procedure
... check some condition and update @count value...
SELECT @count As MyCheckResult

So that's where the first column of the first row makes sense. With MySQL I'm afraid what you can do with that is much more limited

	Quote:
	FR #3: Allow customization of all four sections (Initialize, Execute, Cleanup, and Check) in case templates. It might not add much to database types allowing batch statements but having eg. the following templates in MySQL procedure template sections:

Enhancement request #SA0031987 - allow templates for Init, Cleanup, and Check sections, in addition to already existing templates for Test section.

Last edited by SysOp on Mon Aug 21, 2017 8:18 am; edited 2 times in total

	SysOp wrote:
	This feature I believe was designed after SQL Server and other database servers supporting SQL blocks. where you can perform some activity, and then the end check the status using something like DECLARE @count INT EXEC some_procedure SELECT @count As MyCheckResult So that's where the first column of the first row makes sense. With MySQL I'm afraid what you can do with that is much more limited

First column of the first row of the last result set is fine and it's fine for both SQL Server and MySQL. It's first column of the first row of the first result set that's in the help and what I say is both not true and wouldn't work anyway. The last result set is where this thing shines. You see, in your example above, you expect SELECT @count As MyCheckResult to be the first result set, which might not be the case if some_procedure returns result set(s). If it does, it will always check the first column of the first row of the first result set that would be returned by the stored procedure and your SELECT @count... would go to waste.

It's pretty bad that while SQL Server supports statements like INSERT INTO my_table EXECUTE some_procedure as long as my_table exists, you have to run unnecessary rounds in MySQL by creating temporary tables in the stored procedure and select those (instead of selecting directly) to check stuff. But even these work with last result set.