Create procedure for adjusting database following user deletion.

[ChandlorRatcliffe]

We now have a function that deletes users. In the process, we cascade the ids above the id that was deleted to ensure that we always have a continuous series of user_ids in order to ensure that the next user id corresponds to count(user_id)+1.

This introduces four problems:

• First, any notes that the user had created need to be deleted.
• Second, any notes for user_ids greater than the id deleted need their user_id to be shifted back to accommodate the change.
• Third, courses exclusively associated with that user need to be deleted.
• Fourth, any courses associated with user_id greater than the deleted id need their user_id to be shifted back to accommodate the change.

To resolve this issue, develop a procedure for the database that can be called to create this cascade of adjustments.

I'm assigning it to everyone in Database. It will probably benefit us to break this issue into four separate issues and assign it accordingly.

[tedtdang]

First.txt

[ChandlorRatcliffe]

That has a very rough framework for deleting things, but it doesn't react to a detected deletion as a stored procedure should. Additionally, we don't want to delete courses in which other users may be enrolled. We'll need minimally a nested query to resolve that when we detect the start of a deletion in user.

Finally, the deletion on its own is one thing. The big issue is that we'll be rolling back all of the user_ids in User when we perform a deletion in order to ensure that we only ever have user_ids in the range 1...n for n users. When we do that, the users who have been affected by the rollback will no longer have access to the appropriate notes or courses, and we need to adjust for that.

[tedtdang]

First.txt Better?

[tedtdang]

First.txt Please check it

[ChandlorRatcliffe]

Our goal is to shift the user_id attribute in both the notes table and the course table where it is greater than the id of the user we intend to delete back by one after deleting that user's notes and course registrations.

You've shifted back the note_id instead. The note_id shouldn't change. The course_id shouldn't change either.

[tedtdang]

First, any notes that the user had created need to be deleted. Should the tuples having the specific note_id in table NOTE be deleted?

[ChandlorRatcliffe]

The only thing we'll do with the note_id is use it to eliminate invalid contains records, and that will be a separate issue. Our focus is on notes with the user_id of the deleted user, and on courses with the user_id associated with the deleted user.

[tedtdang]

First.txt Second.txt Do they work?

[tedtdang]

1 and 2.txt Please check if they work

[ChandlorRatcliffe]

Note: MySQL triggers are completely different from SQL triggers because the designers hate us all.

[tedtdang]

I refer from https://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html and come up with the attachment. 1.and.2.txt

[tedtdang]

1, 2, and 3.txt

[tedtdang]

1, 2, 3, and 4.txt

[ChandlorRatcliffe]

From our talk in class today, be sure to refactor the original problem with the current database in mind. Test it in your MySQL Wrokbench, and commit something to your branch when you have anything to show.

[ChandlorRatcliffe]

This is closed because the database schema changed so significantly that this problem no longer exists.