/*
ISC 561
In-class demo: AMCIS database (see handout)

Minimum and Maximum cardinality pp. 214 - 222

This sql script contains DDL and Transact-SQL to implement minimum and maximum cardinality

  Enforce minimum and maximum cardinality 
       Session --< Presentation (1,5)

DROP PROC InsertNewSession
DROP PROC InsertPresentation
DROP PROC DeletePresentation
DROP VIEW vwSessionPresentation
DROP TRIGGER trigMaxCardinality
DROP TRIGGER trigMinCardinality

Minimum cardinality
  Stored procedure (sproc: AddNewSession)
  Trigger (INSTEAD OF: vwSessionPresentation)
  Trigger (DELETE)
  
Maximum cardinality
  Stored procedure (sproc: AddPresentation, DeletePresentation)
  Trigger (INSERT, DELETE)

Enforce Sesssion Requires 1 to 5 presentations.
1. sproc and application logic
2. Trigger on child table (after first INSERT)
3. INSTEAD OF Trigger pp. 274,275-276,279-280,393,401,404 


  @@Identity
  SCOPE_IDENTITY()
  IDENT_CURRENT('tblTableName')

*/

--sprocs pp. 281-283, 384-393 (in SQL Server)
/*
I'll create three sprocs: 
   InsertNewSession :ensures minimum (1)
   InsertPresentation :ensures maximum (5) 
   DeletePresentation :ensure minimum (1)


InsertNewSession
  Start with transaction SQL written for test cases
  Create input/output parameters for local variables
  Steps in sproc
    1. If parent NOT EXISTS
         Insert the parent row
       Else
         Exit (RETURN)
    2. Retrieve surrogate key
    3. Insert the child row

InsertPresentation
  Steps in sproc
    1. If Presentation count <= 5
         Insert the child row
       Else
         Exit

DeletePresentation
  Steps in sproc
    1. If Presentation count = 1
         Delete the child row
       Else
         Exit
*/

/*
Output parameters
   @variable INT OUTPUT

Optional parameters
   @variable INT = NULL

*/

SET NOCOUNT ON
GO

/*
InsertNewSessionWithPresentation
  Start with transaction SQL written for test cases
  Create input/output parameters for local variables
  Steps in sproc
    1. If parent NOT EXISTS
         Insert the parent row
       Else
         Exit (RETURN)
    2. Retrieve surrogate key
    3. Insert the child row
*/

/*
SELECT * 
FROM tblSession, tblPresentation
WHERE tblSession.SessionID=tblPresentation.SessionID

SELECT * FROM tblSession
*/
--DROP PROC InsertNewSession
ALTER PROC InsertNewSession
 @SessionCode VARCHAR(6), --Input parameter
 @SessionDate DATETIME = NULL, --Optional parameter
 @LcdProjectorRequired BIT,
 @EditorID INT,
 @TrackChairID INT, 
 @RoomID INT, 
 @TimeSlotID INT, 
 @PaperID INT
/*
Created by Harold Pardue
Purpose of change: created sproc

*/
AS
BEGIN

  IF EXISTS(SELECT * FROM tblSession WHERE SessionCode = @SessionCode)
  BEGIN
     RAISERROR('The Session already exists...', 16, 1)
     RETURN --Exit sproc
  END

BEGIN TRY
  BEGIN TRANSACTION InsertSession

  INSERT INTO tblSession (SessionCode, LcdProjectorRequired, RoomID, TimeSlotID, TrackChairID, EditorID)
     VALUES (@SessionCode, @LcdProjectorRequired, @RoomID, @TimeSlotID, @TrackChairID, @EditorID);

  DECLARE @SessionID INT; SET @SessionID = SCOPE_IDENTITY();

  INSERT INTO tblPresentation (PresentationOrder, PaperID, SessionID)
    VALUES (1, @PaperID, @SessionID);

  COMMIT TRANSACTION  InsertSession;
  --PRINT 'Session successfully inserted...'

  END TRY

  BEGIN CATCH
    DECLARE @ErrorMessage VARCHAR(500);
    SET @ErrorMessage = ERROR_MESSAGE() + ' Rolledback transaction: Session insertions.';
    ROLLBACK TRANSACTION  InsertSession;
    RAISERROR (@ErrorMessage, 16,1);
  END CATCH
/*
test cases
DECLARE @EditorID INT; SET @EditorID = (SELECT ParticipantID FROM tblParticipant WHERE Email = 'hpardue@usouthal.edu')
DECLARE @SessionDate DATETIME; SET @SessionDate = GETDATE();
DECLARE @TrackChairID INT; SET @TrackChairID = (SELECT ParticipantID FROM tblParticipant WHERE Email = 'apatankar@ucb.edu')
DECLARE @RoomID INT; SET @RoomID = (SELECT RoomID FROM tblRoom WHERE RoomName = 'Salon C')
DECLARE @TimeSlotID INT; SET @TimeSlotID = 4
DECLARE @PaperID INT; SET @PaperID = (SELECT PaperID FROM tblPaper WHERE Title = 'READABLE: An Approach and an Environment for Developing Maintainable Web Software')

EXEC InsertNewSession 'AP134', @SessionDate, 1, @EditorID, @TrackChairID, @RoomID, @TimeSlotID, @PaperID

*/

END --end sproc

GO
PRINT 'Successfully created sproc InsertNewSession...'
GO
/*
 @SessionCode VARCHAR(6),
 @SessionDate DATETIME, 
 @LcdProjector BIT,
 @EditorID INT,
 @TrackChairID INT, 
 @RoomID INT, 
 @TimeSlotID INT, 
 @PaperID INT
*/

/*
Test cases
SELECT * FROM tblSession, tblpresentation WHERE tblSession.SessionID=tblPresentation.SessionID
SELECT * FROM tblPresentation 

Session already exists

DECLARE @EditorID INT; SET @EditorID = (SELECT ParticipantID FROM tblParticipant WHERE Email = 'hpardue@usouthal.edu')
DECLARE @SessionDate DATETIME; SET @SessionDate = GETDATE();
DECLARE @TrackChairID INT; SET @TrackChairID = (SELECT ParticipantID FROM tblParticipant WHERE Email = 'apatankar@ucb.edu')
DECLARE @RoomID INT; SET @RoomID = (SELECT RoomID FROM tblRoom WHERE RoomName = 'Salon C')
DECLARE @TimeSlotID INT; SET @TimeSlotID = 4
DECLARE @PaperID INT; SET @PaperID = (SELECT PaperID FROM tblPaper WHERE Title = 'READABLE: An Approach and an Environment for Developing Maintainable Web Software')

EXEC InsertNewSession 'AP134', @SessionDate, 1, @EditorID, @TrackChairID, @RoomID, @TimeSlotID, @PaperID

--DELETE tblSession WHERE SessionID = 1

SELECT * 
FROM tblSession, tblPresentation, tblPaper
WHERE tblSession.SessionID=tblPresentation.SessionID AND
      tblPresentation.PaperID=tblPaper.PaperID
SELECT * FROM tblSession
SELECT * FROM tblPaper
SELECT * FROM tblPresentation

DELETE tblSession WHERE SessionID = 14

SELECT * FROM tblRoom
*/


/*
InsertPresentation
  Steps in sproc
    1. If Presentation count < 5
         Insert the child row
       Else
         Exit

*/
--DROP PROC InsertPresentation
CREATE PROC InsertPresentation
 @SessionID INT,
 @PaperID INT
/*
Created by Harold Pardue
Purpose of change: created sproc

*/
AS
BEGIN

  IF (SELECT COUNT(*) FROM tblPresentation WHERE SessionID = @SessionID) >= 2
  BEGIN
     RAISERROR('Presentation cannot be inserted because limit of five (5) has been reached...', 16, 1)
     RETURN --Exit sproc
  END


  BEGIN TRY
    BEGIN TRANSACTION InsertPresentation

  --Increment presentationorder by 1
  DECLARE @PresentationOrder INT
  SET @PresentationOrder = (SELECT MAX(PresentationOrder) + 1 FROM tblPresentation WHERE SessionID = @SessionID)

    INSERT INTO tblPresentation (PresentationOrder, PaperID, SessionID)
      VALUES (@PresentationOrder, @PaperID, @SessionID);

    COMMIT TRANSACTION InsertPresentation;

 END TRY

 BEGIN CATCH
   DECLARE @ErrorMessage VARCHAR(500);
   SET @ErrorMessage = ERROR_MESSAGE() + ' Rolledback transaction: Presentation insertions.';
   ROLLBACK TRANSACTION InsertPresentation;
   RAISERROR (@ErrorMessage, 16,1);
 END CATCH

END --end sproc

GO
PRINT 'Successfully created sproc InsertPresentation...'
GO


/*
SELECT * FROM tblPaper
SELECT * FROM tblSession
SELECT * FROM tblPresentation

EXEC InsertPresentation 1, 2
EXEC InsertPresentation 1, 3

SELECT * 
FROM tblSession, tblPresentation, tblPaper
WHERE tblSession.SessionID=tblPresentation.SessionID AND
      tblPresentation.PaperID=tblPaper.PaperID

SELECT @@TRANCOUNT

sp_help tblPresentation
*/
--DROP PROC DeletePresentation
CREATE PROC DeletePresentation
 @SessionID INT,
 @PaperID INT
/*
Created by Harold Pardue
Purpose of change: created sproc

*/
AS
BEGIN

  IF (SELECT COUNT(*) FROM tblPresentation WHERE SessionID = @SessionID) < 2
  BEGIN
     RAISERROR('Presentation cannot be deleted because the minimum limit of one (1) has been reached...', 16, 1)
     RETURN --Exit sproc
  END

   BEGIN TRY
    BEGIN TRANSACTION DeletePresentation

    DELETE tblPresentation 
    WHERE PaperID = @PaperID AND
          SessionID = @SessionID;

    COMMIT TRANSACTION DeletePresentation; 

 END TRY

 BEGIN CATCH
   DECLARE @ErrorMessage VARCHAR(500);
   SET @ErrorMessage = ERROR_MESSAGE() + ' Rolledback transaction: Presentation deletion.';
   ROLLBACK TRANSACTION DeletePresentation;
   RAISERROR (@ErrorMessage, 16,1);
 END CATCH

END --end sproc
GO

PRINT 'Successfully created sproc DeletePresentation...'
GO

/*
SELECT * FROM tblSession
SELECT * FROM tblPresentation
SELECT * FROM tblPaper

EXEC DeletePresentation 1, 2
EXEC DeletePresentation 1, 1


But we can still delete directly with SQL

DELETE tblPresentation WHERE PaperID = 1

SELECT * 
FROM tblSession, tblPresentation, tblPaper
WHERE tblSession.SessionID=tblPresentation.SessionID AND
      tblPresentation.PaperID=tblPaper.PaperID

*/

/*

Enforce Sesssion Requires 1 to 5 presentations.
2. Trigger on child table (after first INSERT)
  pp. 248-254, 388-391, 280, 198, 352-353, 354, 392-393
  INSERT trigger to prevent COUNT(*) > 5
  DELETE trigger to prevent COUNT(*) < 1

SELECT * FROM tblPresentation WHERE SessionID = 1
--SELECT * FROM tblSubmission

*/
--SET SHOWPLAN_ALL ON 
--DROP TRIGGER trigMaxCardinality

CREATE TRIGGER trigMaxCardinality ON tblPresentation
FOR INSERT
AS
BEGIN

   IF (SELECT COUNT(*) 
       FROM tblPresentation 
       WHERE SessionID = (SELECT DISTINCT SessionID FROM inserted)) > 2
   BEGIN
      DELETE tblPresentation WHERE PaperID = (SELECT PaperID FROM inserted)
      --ROLLBACK TRAN
      RAISERROR ('Limit of five presentations exceeded: New row deleted', 16, 1)
   END
END
GO
PRINT 'Successfully trigger trigMaxCardinality...'
GO

--SELECT * FROM tblPresentation
INSERT INTO tblPresentation (PresentationOrder, SessionID, PaperID)
   VALUES (1, 1, 1)
   
INSERT INTO tblPresentation (PresentationOrder, SessionID, PaperID)
   VALUES (3, 1, 3)

INSERT INTO tblPresentation (PresentationOrder, SessionID, PaperID)
   VALUES (2, 1, 2)



SELECT * FROM tblPresentation

DELETE tblPresentation WHERE PresentationID = 3

CREATE TRIGGER trigMinCardinality ON tblPresentation
FOR DELETE
AS
BEGIN

   IF (SELECT COUNT(*) 
       FROM tblPresentation 
       WHERE SessionID = (SELECT DISTINCT SessionID FROM deleted)) < 1
   BEGIN
      ROLLBACK TRAN
      RAISERROR ('A session must have at least one Presentation', 16, 1)
   END
END
GO
PRINT 'Successfully created trigger trigMinCardinality...'
GO

/*
SELECT * FROM tblPaper
SELECT * FROM tblSession
SELECT * FROM tblPresentation

DELETE tblPresentation WHERE PresentationID = 6

*/

--DROP TRIGGER trigMinCardinality

/*
VIEW and INSTEAD OF trigger to implement the minimum cardinality.

Write the join of tblSession and tblPresentation
*/
/*
SELECT SessionCode, SessionDate, LCDProjectorRequired, EditorID, 
       TrackChairID, RoomID, TimeSlotID, PresentationOrder, PaperID 
FROM tblSession, tblpresentation 
WHERE tblSession.SessionID=tblPresentation.SessionID
*/

--Create the view
--DROP VIEW vwSessionPresentation
CREATE VIEW vwSessionPresentation
AS
  SELECT SessionCode, SessionDate, LCDProjectorRequired, EditorID, 
         TrackChairID, RoomID, TimeSlotID, PresentationOrder, PaperID 
  FROM tblSession, tblpresentation 
  WHERE tblSession.SessionID=tblPresentation.SessionID 
GO
PRINT 'Successfully created view vwSessionPresentation...'
GO



/*
SELECT * FROM vwSessionPresentation

SELECT * FROM tblPaper
SELECT * FROM tblSession
SELECT * FROM tblPresentation

SELECT * 
FROM tblSession, tblPresentation, tblPaper
WHERE tblSession.SessionID=tblPresentation.SessionID AND
      tblPresentation.PaperID=tblPaper.PaperID

--DROP TRIGGER trigMinCardinality
--We have to drop our trigger

DELETE tblPresentation WHERE PresentationID = 1
DELETE tblPresentation WHERE PresentationID = 3
DELETE tblSession WHERE SessionID = 1
DELETE tblSession WHERE SessionID = 2



*/
UPDATE vwSessionPresentation SET SessionDate = GETDATE() WHERE PaperID = 1

UPDATE vwSessionPresentation SET LCDProjectorRequired = 0 
WHERE PaperID=1

SELECT * FROM vwSessionPresentation

DELETE tblPresentation where PresentationID = 6
DELETE tblSession WHERE SessionID = 1
/*
DECLARE @EditorID INT; SET @EditorID = (SELECT ParticipantID FROM tblParticipant WHERE Email = 'hpardue@usouthal.edu')
DECLARE @SessionDate DATETIME; SET @SessionDate = GETDATE();
DECLARE @TrackChairID INT; SET @TrackChairID = (SELECT ParticipantID FROM tblParticipant WHERE Email = 'apatankar@ucb.edu')
DECLARE @RoomID INT; SET @RoomID = (SELECT RoomID FROM tblRoom WHERE RoomName = 'Salon C')
DECLARE @TimeSlotID INT; SET @TimeSlotID = 4
DECLARE @PaperID INT; SET @PaperID = (SELECT PaperID FROM tblPaper WHERE Title = 'READABLE: An Approach and an Environment for Developing Maintainable Web Software')

INSERT INTO vwSessionPresentation (SessionCode, SessionDate, LCDProjectorRequired, EditorID, TrackChairID, RoomID, TimeSlotID, PaperID)
   VALUES ('AP134', @SessionDate, 1, @EditorID, @TrackChairID, @RoomID, @TimeSlotID, @PaperID)

*/

/*
Sooo... one solution is to create an INSTEAD OF trigger
We take the SQL from our previous sproc InsertNewSession and place it
inside a trigger instead.
We know this transaction works.
But instead of inserting VALUES from input parameters, we'll reference the "inserted" buffer.
Once the TRIGGER is created, try the second INSERT above again...
*/

CREATE TRIGGER trigInsertSession ON vwSessionPresentation
INSTEAD OF INSERT
AS
BEGIN

  BEGIN TRY
    BEGIN TRANSACTION InsertSession

    INSERT INTO tblSession (SessionCode, LcdProjectorRequired, RoomID, TimeSlotID, TrackChairID, EditorID)
     --VALUES (@SessionCode, @LcdProjectorRequired, @RoomID, @TimeSlotID, @TrackChairID, @EditorID);
       SELECT SessionCode, LcdProjectorRequired, RoomID, TimeSlotID, TrackChairID, EditorID FROM inserted;

    DECLARE @SessionID INT; SET @SessionID = SCOPE_IDENTITY();

    INSERT INTO tblPresentation (PresentationOrder, PaperID, SessionID)
    --VALUES (1, @PaperID, @SessionID);
      SELECT 1, PaperID, @SessionID FROM inserted;
    COMMIT TRANSACTION  InsertSession;

    --Echo the contents of inserted for debugging
     SELECT @SessionID AS SessionID, SessionCode, LcdProjectorRequired, RoomID, TimeSlotID, TrackChairID, EditorID, PaperID FROM inserted;

  END TRY

  BEGIN CATCH
    DECLARE @ErrorMessage VARCHAR(500);
    SET @ErrorMessage = ERROR_MESSAGE() + ' Rolledback transaction: Session insertions.';
    ROLLBACK TRANSACTION  InsertSession;
    RAISERROR (@ErrorMessage, 16,1);
  END CATCH

END --INSTEAD OF trigger
GO
PRINT 'Successfully created INSTEAD OF trigger InsertSession...'
GO

/*
DECLARE @EditorID INT; SET @EditorID = (SELECT ParticipantID FROM tblParticipant WHERE Email = 'hpardue@usouthal.edu')
DECLARE @SessionDate DATETIME; SET @SessionDate = GETDATE();
DECLARE @TrackChairID INT; SET @TrackChairID = (SELECT ParticipantID FROM tblParticipant WHERE Email = 'apatankar@ucb.edu')
DECLARE @RoomID INT; SET @RoomID = (SELECT RoomID FROM tblRoom WHERE RoomName = 'Salon C')
DECLARE @TimeSlotID INT; SET @TimeSlotID = 4
DECLARE @PaperID INT; SET @PaperID = (SELECT PaperID FROM tblPaper WHERE Title = 'READABLE: An Approach and an Environment for Developing Maintainable Web Software')

INSERT INTO vwSessionPresentation (SessionCode, SessionDate, LCDProjectorRequired, EditorID, TrackChairID, RoomID, TimeSlotID, PaperID)
   VALUES ('AP134', @SessionDate, 1, @EditorID, @TrackChairID, @RoomID, @TimeSlotID, @PaperID)

To make this work for a second and third INSERT is a bit more complex. We have
to check for the existence of Session row and then insert only the child row.

*/

/*

SELECT * FROM tblSession
SELECT * FROM tblPresentation
SELECT * FROM vwSessionPresentation
DELETE TABLE tblPresentation
*/

/*
What about deleting a Session?
We could write an sproc that executes:

   DELETE tblPresentation WHERE SessionID = @SessionID
   DELETE tblSession WHERE SessionID = @SessionID

Or, we could cascade the delete into the child table

Cascading Deletes pp. 194
Adding as a constraint

Cascade delete Presentations if Session is deleted
We can alter the FK constraint to CASCADE DELETE
when a Presentation is deleted.

SELECT * FROM tblSession
SELECT * FROM tblPresentation

DELETE tblSession WHERE SessionID = 2

*/



  ALTER TABLE tblPresentation
  DROP CONSTRAINT Session_Presentation_fk
  GO
  ALTER TABLE tblPresentation
  ADD CONSTRAINT Session_Presentation_fk FOREIGN KEY (SessionID)
  REFERENCES tblSession(SessionID) ON DELETE CASCADE
  GO