Investigate ways to keep data from old terms in DB

[ssciolla]

As part of work #113, we have discussed the possibility of only dropping records from active terms so that data from prior terms could be preserved temporarily. Whether this is necessary is still up in the air. Regardless, this would require a more sophisticated drop_records method, and in reality, the desired behavior for each table would have to be considered separately. Consider:

• course: We would have to join to the term table to filter out old terms before dropping any records.
• enrollment: This should be easy, since the dropped course records should trigger cascades.
• canvas_course_usage: This should be easy, since the dropped course records should trigger cascades.
• course_section: We would have to join to the term table; right now the only way to do that is through enrollment -> course -> term, so it would have to happen prior to any dropping of course records.
• user: users will likely persist from term to term, but we also can't really assume that some users won't ever disappear. Not exactly sure what the desired behavior here is.

I don't have a great idea of the best way to do this just yet. Seems like you could use separate DELETEs specifying primary keys, a DELETE with joins, or use an ORM (SQLAlchemy looks promising).

[ssciolla]

I think this could do the trick for a quick solution:

DELETE c, ccu, cs, e, u, t
FROM term t
INNER JOIN course c
	ON c.term_id=t.canvas_id
INNER JOIN canvas_course_usage ccu
	ON ccu.course_id=c.canvas_id
INNER JOIN enrollment e
	ON e.course_id=c.canvas_id
INNER JOIN course_section cs
	ON cs.canvas_id=e.course_section_id
INNER JOIN user u
	ON u.canvas_id=e.user_id
WHERE t.canvas_id IN (?);