def changes_to_notify(self) -> [Course]:
changed_courses = []
conn = sqlite3.connect(self.db_file)
conn.row_factory = sqlite3.Row
cursor = conn.cursor()
cursor.execute("""SELECT course_id, course_fullname
FROM files WHERE notified = 0 GROUP BY course_id;""")
curse_rows = cursor.fetchall()
for course_row in curse_rows:
course = Course(course_row['course_id'],
course_row['course_fullname'], [])
cursor.execute(
"""SELECT *
FROM files WHERE notified = 0 AND course_id = ?;""",
(course.id, ),
)
file_rows = cursor.fetchall()
course.files = []
for file_row in file_rows:
notify_file = File.fromRow(file_row)
if notify_file.modified or notify_file.moved:
# add reference to new file
cursor.execute(
"""SELECT *
FROM files
WHERE old_file_id = ?;""",
(notify_file.file_id, ),
)
file_row = cursor.fetchone()
if file_row is not None:
notify_file.new_file = File.fromRow(file_row)
course.files.append(notify_file)
changed_courses.append(course)
conn.close()
return changed_courses
def get_stored_files(self) -> [Course]:
# get all stored files (that are not yet deleted)
conn = sqlite3.connect(self.db_file)
conn.row_factory = sqlite3.Row
cursor = conn.cursor()
stored_courses = []
cursor.execute("""SELECT course_id, course_fullname
FROM files WHERE deleted = 0 GROUP BY course_id;""")
curse_rows = cursor.fetchall()
for course_row in curse_rows:
course = Course(course_row['course_id'],
course_row['course_fullname'], [])
cursor.execute(
"""SELECT *
FROM files WHERE deleted = 0 AND course_id = ?;""",
(course.id, ))
file_rows = cursor.fetchall()
course.files = []
for file_row in file_rows:
notify_file = File.fromRow(file_row)
course.files.append(notify_file)
stored_courses.append(course)
conn.close()
return stored_courses