Test if insertion succeeded about mytap HOT 6 CLOSED

The example uses a user defined MySQL function, so something like this:

begin delimiter //

create function insert_stuff(url, string, domain, src)
returns boolean
begin
   insert into table values(url, string, domain, src);
  return -- some indication if all was well
end //

end delimiter //

-- Insert stuff.
SELECT tap.ok(
    insert_stuff( 'www.foo.com', '1,2,3', @domain_id, @src_id ),
    'insert_stuff() should return true'
);  

from mytap.

What if the insertion fails due to constraints? I wanted to test that an insertion would fail. I could do this inside a scripting language outside of SQL, but inside SQL?...

from mytap.

I usually solve this with a helper procedure: check in the procedure for the errors in the error handler and fill a temp table accordingly. Then I use tap.ok to test for the expected value in the table.

from mytap.

Can you give an example on how that would work?

from mytap.

Two thoughts here:

• if you want to check in a function if a certain statements fails or succeeds, that's not possible in MySQL. MySQL simply lacks that feature.
• if you want to check if a certain statement correctly inserts the provided data, that's more of an end-to-end test and outside the scope of MyTAP. You're better off testing that in the app that adds the data.
  The primary focus of MyTAP is providing a test framework for schema validation.

from mytap.

To @hepabolu's point

Unlike postgresql and the pgTAP project which spawned this project, MySQL doesn't allow you to pass arbitrary SQL to a stored function and then execute it, dynamic SQL only works in stored procedures. Your insert statement can't be the first argument to any of the tap emitting functions, neither can you CALL a stored procedure which could run it inline within a SELECT statement containing the tap emitting function. The example you quote is a function returning TRUE within a stored function - which is legal in MySQL. MyTAP will allow you to test whether the table and the columns you wish to insert into exist and a multitude of other properties on those schema objects but it wont run an arbitrary insert statement and tell you whether they succeeded or failed.

from mytap.