#<pre><code>
/**

	@author Professor Tom DeDonno
	@date 7/2013, 8/2015, 5/2020

	tests scripts for transactions and event (which is a comment)
*/


#<div class="testStrings">


/** 

Essay 11-13 are in
<a href="http://cim.saddleback.edu/~premiere/fruitTransactionCreate.sql.html">
fruitTransactionCreate.sql.html</a>

Essay 14 in the code below why does delete fruitID=1 throw an exception */

/* phpMyAdmin is a safe system,  it creates a new session
   at each query submit, therefore you  need to paste entire
   block of code into one sql dialog panel,
   a block begins with:  start transaction; .... ends with a commit or rollback;  */
# select "This should throw an exception 1 is inventory";
# delete from `fruit` where fruitID = '1';
# select testFruitExistence( "Tried to delete 1", 1 );

/** for pmaTrigger #10 review the transactions, what is commited 
    that changes the key fruit tables: fruit, inventory, orders, orderLine, customer;
    myError is a error/log file is not a key database table
 */

start transaction;

#pma will usually display only 1 line or worse yet empty set

select testFruitExistence( "Start of Transaction", 6 );
delete from `fruit` where fruitID = '6';
select testFruitExistence( "fruitID 6 deleted", 6 );

#remember cascade delete in orderLine
select count( * ) from orderLine;
delete from `orders` where shipDate is null;
select * from orderLine;

rollback;

#rollback, cancel all changes from the last transaction

select testFruitExistence( "After Rollback fruitID 6 back in", 6 );

start transaction;
select testFruitExistence( "Start of Transaction", 6 );
delete from `fruit` where fruitID = '6';
select testFruitExistence( "fruitID 6 deleted", 6 );
commit;

select testFruitExistence( "After Commit fruitID 6 Exists? ", 6 );

#insert into fruit ( fruitID, name, price ) 
#values ( 6, "plum", 1.25 );

select count( * ) from orderLine;
call testTransaction( );
select count( * ) from orderLine;

/** Essay 15 for the oneTime event, explain how
   we determine that this complex event took place,
   refer to the deskcheck identify the before and
   after database state */

/***
# in PHPMyAdmin More events, don't create recurring events

drop event if exists `oneTime`;

delete from myError where 1;

insert into orders (orderID, customerID) values (26, 100 );

insert into orderLine (fruitID, orderID, quantity ) values
(1, 26, 200 );

select * from orders; select * from orderLine;
select * from inventoryQuantity; select * from myError;

insert into myError ( location, msg ) 
select "Before create event", concat( "Open order count: ", count( * ) )
from orders where shipDate is null;

use delimiter $$

Create event if not exists `oneTime`
ON SCHEDULE at now() + interval 1 minute
DO BEGIN 
update `orders` set shipDate = now() where shipDate is null;
insert into myError ( location, msg )
select "Inside Event", concat( "1 Minute Later Open order count: ", count( * ) )
from orders where shipDate is null;

END $$

delimiter ;

#note myError insert would be commented out in production code (we have too many)


select "time is ", now( );
select * from myError;
show events;
#</code></pre>
**/