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_old_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 DISTINCT course_id, course_fullname
FROM files WHERE old_file_id IS NOT NULL""")
course_rows = cursor.fetchall()
for course_row in course_rows:
course = Course(course_row['course_id'],
course_row['course_fullname'])
cursor.execute(
"""SELECT *
FROM files
WHERE course_id = ?
AND old_file_id IS NOT NULL""",
(course.id, ),
)
updated_files = cursor.fetchall()
course.files = []
for updated_file in updated_files:
cursor.execute(
"""SELECT *
FROM files
WHERE file_id = ?""",
(updated_file['old_file_id'], ),
)
old_file = cursor.fetchone()
notify_file = File.fromRow(old_file)
course.files.append(notify_file)
stored_courses.append(course)
conn.close()
return stored_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 AND modified = 0 AND moved = 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 modified = 0
AND moved = 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