#<pre>
/**
  note browser cannot display sql file Types so we have to append .html 
  but this should be a sql file Type

<ul>Components
	<li>constraints orderLine and orders<br />
            inventory constraint must be set by student
	</li><li>Three triggers for orders,</br>
          insertOrder, updateOrder and deleteOrder
	</li><li>Creates table myError as trigger log file</li>
        <li>insert into orderLine 3, 23, 9999 for testing updateOrder</li>
</ul>

@author Professor Tom DeDonno 5/12/2013 
@modifed PTD 6/30/2013,  code throwing exception is not a comment
script was failing in batch mode, due to exception test
@modified PTD 7/23/2015 update orders set shipDate=now() is now a comment
it has become query 10 in pmaTrigger.sql, this entire script can now
be executed using import on PMA.

Whenever you write complex code, always make sure you identify
your self and also the date it was written, if you later make changes
you should include both modification date, and changes made.

Note I am sparse on comments in these files. Your report should include comments
it is customary to put the main parts of a file, in the comment header,
aka this file sets referential integrity for both orderLine, and customer.

Testing...

Nota Bene PHPMyAdmin will fail (stop) on first exception thrown,
However, Submit uses the command line interface, which continues
even after an exception. Therefore when testing on PHPMyAdmin,
you cannot test the entire script at once, but only part of it a
a time.

note all testing strings are inside of div blocks class testStrings

*/


/**

	Essay 1-5 are in
	<a href="http://cim.saddleback.edu/~premiere/fruitAlter.sql.html">
	fruitAlter.sql.html</a>

	You should review essay questions and answers before taking blackboard quizzes
	answers to Essay 6 to 9 are at http://cim.saddleback.edu/~premiere/essay6_9.html
	Essay 6: list and explain actions of
                 all referential integrity constraints in our fruit Database

	Essay 7: list and explain all triggers in our fruit database


	Essay 8: in table:orders which rows can you delete and which fields/columns can
        you change and why?

	Essay 9 & 10 are in 
	<a href="http://cim.saddleback.edu/~premiere/fruitRoutineCreate.sql.html">
	fruitRoutineCreate.sql.html</a>

*/


/* added constraint names 
   drop foreign key if exists doesn't exist */

# ALTER TABLE `orderLine` DROP FOREIGN KEY `orderLineFKorderID`;
# ALTER TABLE `orderLine` DROP FOREIGN KEY `orderLineFKfruitID`;

/*
ALTER TABLE orderLine MODIFY id INT NOT NULL;
ALTER TABLE orderLine DROP PRIMARY KEY; */

# some user may have already created the primary key for orderLine 
# procedure below will only add primary if is doesn't exist
# user may need to call fruitShared.purgeDatabase( database() );

# call fruitShared.addPrimaryKeyOrderLine( database() );
# getting commands out of sync, even when I do a prepare and execute
# pma may be executing batch all at once.


Alter table `orderLine` 
# drop primary key,
add primary key( orderID, fruitID ),
add constraint `orderLineFKorderID`
foreign key ( orderID ) references `orders`( orderID ) on DELETE CASCADE,
add constraint `orderLineFKfruitID` 
foreign key ( `fruitID` ) references `fruit`( `fruitID` ) on DELETE RESTRICT;

# ALTER TABLE `orders` DROP FOREIGN KEY `orderFKcustomerID`;

Alter table `orders` 
add constraint `orderFKcustomerID` 
foreign key (`customerID` ) references `customer`( `customerID` )
ON DELETE RESTRICT ON UPDATE NO ACTION;

#  note views are stored queries, you cannot add referential integrity to them
# note views with a 1:1 mapping allow updates from view back to original source 

# Syntax on trigger aka routines is a pain, this is typical for scripting languages

DROP TRIGGER if exists `deleteOrder`;

DELIMITER $$

CREATE TRIGGER deleteOrder
BEFORE DELETE on `orders`
FOR EACH ROW 
BEGIN 

	IF ( old.shipDate is not null )  THEN 

		#signal added to MySQL 5.5
		SIGNAL SQLSTATE  '90001'
		set message_text = 'Sorry, once we ship no returns.';
	END IF;
END $$
DELIMITER ;


# to test this trigger try inserting new order for both customer 120, and 130 see what happens
DROP TRIGGER if exists `insertOrder`;
DELIMITER EOT

CREATE TRIGGER insertOrder
BEFORE INSERT on `orders`
FOR EACH ROW
BEGIN
DECLARE oID int;
DECLARE msg TEXT;

/** what does this set and next if do? 
    we could use select orderID... into oID */

set oID=( select orderID from `orders` as o
	where o.customerID = NEW.customerID and o.shipDate is null
	limit 1);

	IF ( oID is not null ) THEN

		set msg = concat( "Order ", oID, " is still open for customerID:",
                                  NEW.customerID, " Cannot start new order" );

		SIGNAL SQLSTATE  '90001'
		set message_text = msg;
	END IF;

	# startDate is defined as default CURRENT_TIMESTAMP
	# guarantee if it user has not set a new startDate
	IF ( NEW.startDate is null ) THEN
		set NEW.startDate = now( );
	END IF;

END EOT
delimiter ;


/* what does this table do? check where it is used */

create table if not exists myError (
myDate timestamp,
location TEXT,
msg TEXT
); 


drop trigger if exists `updateOrder`;

DELIMITER EOT

CREATE TRIGGER `updateOrder`
BEFORE UPDATE  ON `orders`
FOR EACH ROW BEGIN
	# PLSQL requires you to declare all variables first
 	Declare desired int;   # desired is what we want to send to customer
	Declare available int; # available is what is available in inventory
	Declare fID int;
	
	Declare msg TEXT;
	Declare rowCount int; # number of rows in orderLine for old.orderID 
	Declare  orderLineRows CURSOR 
	FOR select fruitID, quantity from orderLine where orderLine.orderID = old.orderID; 

	IF ( old.orderID != new.orderID or
	     old.customerID != new.customerID or
	     old.startDate != new.startDate or
	     old.shipDate is not null ) THEN
		 set msg = concat( "for orderID: ", old.orderID,
                 " We can only update null shipDate columns",
                 " old.customerID: ", old.customerID, 
		 " new.customerID: ", new.customerID );
		 SIGNAL SQLSTATE '90001' set message_text = msg;
	END IF;
	# note code to set shipDate from not null to null would required updating inventory

	OPEN orderLineRows;
	select FOUND_ROWS( ) into rowCount;
	while rowCount > 0 do

		FETCH orderLineRows into fID, desired;

		set available = (select ifnull(`quantity`,0) from `fruitView` where fruitID = fID);
		
		/* what does this code do? Why have it*/

		insert into myError
		(`location`, `msg` ) VALUES
		("update orders trigger after fetch",
		concat( "available:", available, " desired:", desired, " fruitID:", fID, " orderID:", new.orderID ) );

   		IF( desired > available ) THEN
	  		# remember orderID, fruitID is the primary key for orderLine
	  		update orderLine  set orderLine.quantity = available
	  		where orderLine.orderID = new.orderID and orderLine.fruitID = fID;
	  		set desired = available;
		END IF;  
		call updateInventory( fID, desired );
		set rowCount = rowCount - 1;
	end while;
	CLOSE orderLineRows;
		
END EOT


DELIMITER ;

/** note a trigger is a procedure */

drop procedure if exists `updateInventory`;

DELIMITER EOT
# procedures assumes desired is set to available quantity
create procedure `updateInventory`( IN fID int, IN desired int )
DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
BEGIN 
	Declare  q int;  # current quantity from one row of inventory
	Declare  rowCount int;  #number of inventory rows for fruitID fID
	Declare  errorMsg TEXT;
	Declare available int;
	Declare  inventoryRows CURSOR FOR select quantity from inventory where inventory.fruitID = fID;

	set available = (select ifnull(`quantity`,0) from `fruitView` where fruitID = fID);
	
	IF desired > available THEN 

		#signal added to MySQL 5.5
		set errorMsg = concat( "Sorry, fruitID:",  fID, " cannot ship:", desired,
		                       " we only have:", available );
		SIGNAL SQLSTATE  '90001'
		set message_text = errorMsg;
	END IF;

	#note could add special code for circumstances i
	#where desired=0; and available=0 or null

	OPEN inventoryRows;
	select FOUND_ROWS( ) into rowCount;

	/** again does this myError table serve a purpose in production system or just development */
	
	insert myError 
	(`location`, `msg` ) VALUES ( "update Inventory",
	concat( "available:", available, " desired:", desired, " fruitID:", fID, " rowCount:", rowCount) );


	while rowCount > 0 do 
		   
	   FETCH inventoryRows into q;  #get quantity of current inventory record

	   if( q > desired ) then
		   	update inventory set inventory.quantity = inventory.quantity - desired
		 	where inventory.quantity = q and inventory.fruitID = fID
		    limit 1;
			set rowCount = 0;
	   else  #situation where desired > q
	     	delete from inventory 
	        where inventory.quantity = q and inventory.fruitID = fID
	        limit 1;
	        set desired = desired - q;
	    end if; 
	    set rowCount = rowCount - 1;

	end while;
	CLOSE inventoryRows;

END EOT

Delimiter ;


# Tests strings, one should create routines for testing
# that automatically verify the routines anytime in the future, (aka unit Testing)


#<div class="testStrings">
#once again in phpMyAdmin it only prints out output from the last command

insert into `orderLine`
( `fruitID`, `orderID`, `quantity` ) VALUES ( 3, 23, 9999 );

select * from `fruitView`;

# make sure you identify all the changes this one line makes
#10pmaTrigger  it is 10 for pmaTrigger 
#update `orders` set shipDate = now() where `orderID` = 23;

/* &#92;G (back slash \G) print column wise,
   rows are printed down instead of across
   for displays with lots of columns  &92;G (back slash \G) is a better display
   each row record in show triggers is a long display */
#show triggers not working in mariaDB 10, its listed the creation date;
#select TRIGGER_NAME from information.schema.triggers where definer = "jstudent0"

#show triggers \G
#</div>