#<pre>
/**

	@author Professor Tom DeDonno
	@date 7/2013 and 8/2015

	function testFruitExistence( msg Text, fid smallint )
	returns TEXT string if fruit is or is not in table:fruit

	procedure testTransaction( ) does major deletes in table:fruit and table:orders 
	prints out initial fruit count, fruit count after delete, fruit count after rollback

	procedure testTransaction2( fid smallint )
	logs operations to table:myError
	deletes fruit fID, print fruit count, then rollback, print fruit count

*/

#if pma generates out of synce, just import twice or drop first

drop function if exists `testFruitExistence`;
drop Procedure if exists `testTransaction`;
drop procedure if exists `testTransaction2`;

# will use one delimiter for all three routines

delimiter $$

create function `testFruitExistence`( msg TEXT, fID smallint unsigned )
RETURNS TEXT
DETERMINISTIC READS SQL DATA
BEGIN 
	declare str TEXT;


	select concat( msg, " fruitID:", fID, " is ",
        if( fID=(select fruitID from fruit where fruitID=fID),
        " in ", " not in " ),
        " table fruit" ) into str;

	return str;
END $$

/* Essay 11 test testTransaction in phpMyAdmim,
   what does the UNION query do?
   what does the delete do?
   why are none of the deletes maintained after select testTransaction( )
*/

/** as of MySQL 5.5 you cannot have transactions inside functions or triggers */

CREATE Procedure  `testTransaction`()
DETERMINISTIC READS SQL DATA
BEGIN 
	declare str TEXT;
	start transaction;
	set str = concat( "Start: ", (select count( fruitID ) from fruit) );

	# does this delete orderLine entries and how?
	delete from `orders` where shipDate is null;

	delete from fruit where fruitID not in
	(select fruitID from `inventoryQuantity` UNION (select fruitID from orderLine) );

	set str = concat( str,  " After Delete: ",  (select count( fruitID ) from fruit) );

	rollback;
	set str = concat( str, " After rollback: ", ( select count( fruitID ) from fruit) );
    select str;
END $$


/** Essay 12 test the procedure testTransaction2 using phpMyAdmin,
    make the following calls  testTransaction2( 6 ), and testTransaction2( 2 )
    how is the field myDate being set in myError? why does 2 or 6 fail?
    if phpMyAdmin creates a new session on each submit, how does the calls
    to this procedure work as a single session? */


create procedure `testTransaction2`( fID smallint unsigned )
DETERMINISTIC READS SQL DATA
BEGIN

	insert into myError (location, msg ) VALUES
	( "testTransaction2",
           testFruitExistence( "before Start Transaction:", fID ) );

	start transaction;

	delete from `fruit` where fruitID = fID;

	insert into myError (location, msg ) VALUES
	( "testTransaction2 inside transaction",
	  testFruitExistence( concat( "fruitID ", fID, " deleted"), fID ) );

	rollback; #rollback, cancel all changes from the last transaction

	insert into myError (location, msg ) VALUES
	( "testTransaction2",
	  testFruitExistence( concat( "After Rollback fruitID ",  fID, " back in"), fID ) );

END $$

#</div>
delimiter ;

/** Essay13 list the signature of each function and procedure in your database,
    a signatures consists of the function/procedure name,
    its arguments and types, its return value (for function only)
    and a short comment on what it does */