def delete(self, id):
with get_connection().cursor() as cursor:
query = """DELETE FROM files WHERE id = %s"""
try:
cursor.execute(query, [id])
get_connection().commit()
return True
except:
return False
def create(self, course):
with get_connection().cursor() as cursor:
now = datetime.datetime.now()
query = """INSERT INTO courses (department_code, course_code, title, created_at, updated_at)
VALUES (%s, %s, %s, %s, %s)
RETURNING id, department_code, course_code, title, created_at, updated_at"""
cursor.execute(query, (course.department_code, course.course_code, course.title, course.created_at, course.updated_at))
get_connection().commit()
course = Course.from_database(cursor.fetchone())
return course
def create(self, section):
with get_connection().cursor() as cursor:
now = datetime.datetime.now()
query = """INSERT INTO sections (course_id, teacher_id, crn, building, day, time, room, capacity, enrolled, created_at, updated_at)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
RETURNING id, course_id, teacher_id, crn, building, day, time, room, capacity, enrolled, created_at, updated_at"""
cursor.execute(query, (section.course_id, section.teacher_id, section.crn, section.building, section.day, section.time, section.room, section.capacity, section.enrolled, section.created_at, section.updated_at))
get_connection().commit()
section = Section.from_database(cursor.fetchone())
return section
def create(self, teacher):
with get_connection().cursor() as cursor:
now = datetime.datetime.now()
query = """INSERT INTO teachers (name, created_at, updated_at)
VALUES (%s, %s, %s)
RETURNING id, name, created_at, updated_at"""
cursor.execute(
query, (teacher.name, teacher.created_at, teacher.updated_at))
get_connection().commit()
teacher = Teacher.from_database(cursor.fetchone())
return teacher
def create(self, user):
with get_connection().cursor() as cursor:
now = datetime.datetime.now()
query = """INSERT INTO users (email, username, password, session_token, created_at, updated_at)
VALUES (%s, %s, %s, %s, %s, %s)
RETURNING id, email, username, password, session_token, created_at, updated_at"""
cursor.execute(
query, (user.email, user.username, user.password_hash,
user.session_token, user.created_at, user.updated_at))
get_connection().commit()
user = User.from_database(cursor.fetchone())
return user
def create(self, message):
with get_connection().cursor() as cursor:
now = datetime.datetime.now()
query = """INSERT INTO messages (thread_id, user_id, course_id, section_id, section_only, title, message, created_at, updated_at)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
RETURNING id, thread_id, user_id, course_id, section_id, section_only, title, message, created_at, updated_at"""
cursor.execute(
query,
(message.thread_id, message.user_id, message.course_id,
message.section_id, message.section_only, message.title,
message.message, message.created_at, message.updated_at))
get_connection().commit()
message = Message.from_database(cursor.fetchone())
return message
def create(self, file):
with get_connection().cursor() as cursor:
now = datetime.datetime.now()
query = """INSERT INTO files (course_id, section_id, user_id, section_only, title, filename, original_filename, content_type, created_at, updated_at)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
RETURNING id, course_id, section_id, user_id, section_only, title, filename, original_filename, content_type, created_at, updated_at"""
cursor.execute(query,
(file.course_id, file.section_id, file.user_id,
file.section_only, file.title, file.filename,
file.original_filename, file.content_type,
file.created_at, file.updated_at))
get_connection().commit()
file = File.from_database(cursor.fetchone())
return file
def search(self, q):
with get_connection().cursor() as cursor:
query = """SELECT * FROM courses WHERE UPPER(title) ILIKE %s"""
cursor.execute(query, ['%'+ q.upper() +'%'])
data = cursor.fetchall()
def parse_database_row(row): return Course.from_database(row)
return list(map(parse_database_row, data))
def find_sections_of_course(self, course_id):
with get_connection().cursor() as cursor:
query = """SELECT * FROM sections WHERE course_id = %s ORDER BY created_at"""
cursor.execute(query, [course_id])
data = cursor.fetchall()
def parse_database_row(row): return Section.from_database(row)
return list(map(parse_database_row, data))
def find_by_email(self, email):
with get_connection().cursor() as cursor:
query = """SELECT * FROM users WHERE email = %s LIMIT 1"""
cursor.execute(query, [email])
data = cursor.fetchone()
if data is None:
return None
return User.from_database(data)
def find_by_name(self, name):
with get_connection().cursor() as cursor:
query = """SELECT * FROM teachers WHERE name = %s LIMIT 1"""
cursor.execute(query, [name])
data = cursor.fetchone()
if data is None:
return None
return Teacher.from_database(data)
def find_by_department_and_course_code(self, department_code, course_code):
with get_connection().cursor() as cursor:
query = """SELECT * FROM courses WHERE department_code = %s AND course_code = %s LIMIT 1"""
cursor.execute(query, [department_code, course_code])
data = cursor.fetchone()
if data is None:
return None
return Course.from_database(data)
def find_by_session_token(self, session_token):
with get_connection().cursor() as cursor:
query = """SELECT * FROM users WHERE session_token = %s LIMIT 1"""
cursor.execute(query, [session_token])
data = cursor.fetchone()
if data is None:
return None
return User.from_database(data)
def find_by_id(self, id):
with get_connection().cursor() as cursor:
query = """SELECT * FROM sections WHERE id = %s LIMIT 1"""
cursor.execute(query, [id])
data = cursor.fetchone()
if data is None:
return None
return Section.from_database(data)
def find_random(self, limit = 0):
with get_connection().cursor() as cursor:
if limit > 0:
query = """SELECT * FROM sections ORDER BY capacity DESC OFFSET random() * (SELECT count(*) FROM sections) LIMIT %s"""
cursor.execute(query, [limit])
else:
query = """SELECT * FROM sections ORDER BY capacity DESC OFFSET random() * (SELECT count(*) FROM sections)"""
cursor.execute(query)
data = cursor.fetchall()
def parse_database_row(row): return Section.from_database(row)
return list(map(parse_database_row, data))
def search(self, q):
with get_connection().cursor() as cursor:
query = """SELECT f.id, f.course_id, f.section_id, f.user_id, f.section_only, f.title, f.filename, f.original_filename, f.content_type, f.created_at, f.updated_at,
c.id, c.department_code, c.course_code, c.title, c.created_at, c.updated_At FROM files AS f INNER JOIN courses AS c ON f.course_id = c.id WHERE UPPER(f.title) ILIKE %s"""
cursor.execute(query, ['%' + q.upper() + '%'])
def parse_database_row(row):
file = File.from_database(row[0:11])
file.course = Course.from_database(row[11:17])
return file
return list(map(parse_database_row, cursor.fetchall()))
def find_files_of_user(self, user_id):
with get_connection().cursor() as cursor:
query = """SELECT f.id, f.course_id, f.section_id, f.user_id, f.section_only, f.title, f.filename, f.original_filename, f.content_type, f.created_at, f.updated_at,
c.id, c.department_code, c.course_code, c.title, c.created_at, c.updated_At FROM files AS f INNER JOIN courses AS c ON f.course_id = c.id WHERE f.user_id = %s ORDER BY f.created_at"""
cursor.execute(query, [user_id])
def parse_database_row(row):
file = File.from_database(row[0:11])
file.course = Course.from_database(row[11:17])
return file
return list(map(parse_database_row, cursor.fetchall()))
def find_messages_of_user(self, user_id):
with get_connection().cursor() as cursor:
query = """SELECT m.id, m.thread_id, m.user_id, m.course_id, m.section_id, m.section_only, m.title, m.message, m.created_at, m.updated_at,
c.id, c.department_code, c.course_code, c.title, c.created_at, c.updated_At FROM messages AS m INNER JOIN courses AS c ON m.course_id = c.id WHERE m.user_id = %s ORDER BY m.created_at"""
cursor.execute(query, [user_id])
def parse_database_row(row):
message = Message.from_database(row[0:10])
message.course = Course.from_database(row[10:16])
return message
return list(map(parse_database_row, cursor.fetchall()))
def update(self, old_user, new_user):
with get_connection().cursor() as cursor:
query = """UPDATE users SET updated_at = %s """
query_tuple = (new_user.updated_at, )
if old_user.username != new_user.username:
query += """, username = %s """
query_tuple += (new_user.username, )
if old_user.email != new_user.email:
query += """, email = %s """
query_tuple += (new_user.email, )
if old_user.password_hash != new_user.password_hash:
query += """, password = %s """
query_tuple += (new_user.password_hash, )
query += """WHERE id = %s"""
query_tuple += (old_user.id, )
try:
cursor.execute(query, query_tuple)
get_connection().commit()
return True
except:
return False
def find_threads_of_section(self, section_id):
with get_connection().cursor() as cursor:
query = """SELECT m.id, m.thread_id, m.user_id, m.course_id, m.section_id, m.section_only, m.title, m.message, m.created_at, m.updated_at,
u.id, u.email, u.username, u.password, u.session_token, u.created_at, u.updated_at FROM messages AS m INNER JOIN users AS u ON m.user_id = u.id WHERE section_id = %s AND thread_id is NULL ORDER BY m.created_at"""
cursor.execute(query, [section_id])
data = cursor.fetchall()
def parse_database_row(row):
message = Message.from_database(row[0:10])
message.user = User.from_database(row[10:17])
return message
return list(map(parse_database_row, data))
def find_files_of_section(self, section_id):
with get_connection().cursor() as cursor:
query = """SELECT f.id, f.course_id, f.section_id, f.user_id, f.section_only, f.title, f.filename, f.original_filename, f.content_type, f.created_at, f.updated_at,
u.id, u.email, u.username, u.password, u.session_token, u.created_at, u.updated_at FROM files AS f INNER JOIN users AS u ON f.user_id = u.id WHERE f.section_id = %s ORDER BY f.created_at"""
cursor.execute(query, [section_id])
data = cursor.fetchall()
def parse_database_row(row):
file = File.from_database(row[0:11])
file.user = User.from_database(row[11:18])
return file
return list(map(parse_database_row, data))
def find_recents(self, limit=0):
with get_connection().cursor() as cursor:
if limit > 0:
query = """SELECT * FROM teachers ORDER BY updated_at LIMIT %s"""
cursor.execute(query, [limit])
else:
query = """SELECT * FROM teachers ORDER BY updated_at"""
cursor.execute(query)
data = cursor.fetchall()
def parse_database_row(row):
return Teacher.from_database(row)
return list(map(parse_database_row, data))
def find_department_codes(self):
with get_connection().cursor() as cursor:
query = """SELECT DISTINCT(department_code) FROM courses"""
cursor.execute(query)
return cursor.fetchall()
def message_count(self, thread_id):
with get_connection().cursor() as cursor:
query = """SELECT count(*) FROM messages WHERE thread_id = %s"""
cursor.execute(query, [thread_id])
return cursor.fetchone()[0]
def all(self):
with get_connection().cursor() as cursor:
query = """SELECT * FROM sections"""
cursor.execute(query)
def parse_database_row(row): return Section.from_database(row)
return list(map(parse_database_row, cursor.fetchall()))
def initialize_database():
with get_connection().cursor() as cursor:
query = """DROP TABLE IF EXISTS users CASCADE"""
cursor.execute(query)
query = """CREATE TABLE users (
id serial PRIMARY KEY,
email varchar(255) NOT NULL,
username varchar(255) NOT NULL,
password varchar(255) NOT NULL,
session_token varchar(255) NOT NULL,
created_at timestamp NOT NULL,
updated_At timestamp NOT NULL
)"""
cursor.execute(query)
query = """DROP TABLE IF EXISTS courses CASCADE"""
cursor.execute(query)
query = """CREATE TABLE courses (
id serial PRIMARY KEY,
department_code varchar(3) NOT NULL,
course_code varchar(5) NOT NULL,
title varchar(255) NOT NULL,
created_at timestamp NOT NULL,
updated_At timestamp NOT NULL
)"""
cursor.execute(query)
query = """DROP TABLE IF EXISTS teachers CASCADE"""
cursor.execute(query)
query = """CREATE TABLE teachers (
id serial PRIMARY KEY,
name varchar(255) NOT NULL,
created_at timestamp NOT NULL,
updated_At timestamp NOT NULL
)"""
cursor.execute(query)
query = """DROP TABLE IF EXISTS sections CASCADE"""
cursor.execute(query)
query = """CREATE TABLE sections (
id serial PRIMARY KEY,
course_id integer REFERENCES courses ON DELETE SET NULL ON UPDATE CASCADE,
teacher_id integer REFERENCES teachers ON DELETE SET NULL ON UPDATE CASCADE,
crn varchar(255) NOT NULL,
building varchar(255) NOT NULL,
day varchar(255) NOT NULL,
time varchar(255) NOT NULL,
room varchar(255) NOT NULL,
capacity varchar(255) NOT NULL,
enrolled varchar(255) NOT NULL,
created_at timestamp NOT NULL,
updated_At timestamp NOT NULL
)"""
cursor.execute(query)
query = """DROP TABLE IF EXISTS messages CASCADE"""
cursor.execute(query)
query = """CREATE TABLE messages (
id serial PRIMARY KEY,
thread_id integer REFERENCES messages ON DELETE CASCADE ON UPDATE CASCADE,
user_id integer REFERENCES users ON DELETE CASCADE ON UPDATE CASCADE,
course_id integer REFERENCES courses ON DELETE SET NULL ON UPDATE CASCADE,
section_id integer REFERENCES sections ON DELETE SET NULL ON UPDATE CASCADE,
section_only boolean NOT NULL DEFAULT false,
title varchar(255) NOT NULL,
message varchar(1000) NOT NULL,
created_at timestamp NOT NULL,
updated_At timestamp NOT NULL
)"""
cursor.execute(query)
query = """DROP TABLE IF EXISTS files"""
cursor.execute(query)
query = """CREATE TABLE files (
id serial PRIMARY KEY,
course_id integer REFERENCES courses ON DELETE SET NULL ON UPDATE CASCADE,
section_id integer REFERENCES sections ON DELETE SET NULL ON UPDATE CASCADE,
user_id integer REFERENCES users ON DELETE SET NULL ON UPDATE CASCADE,
section_only boolean NOT NULL DEFAULT false,
title varchar(255) NOT NULL,
filename varchar(255) NOT NULL,
original_filename varchar(255) NOT NULL,
content_type varchar(255) NOT NULL,
created_at timestamp NOT NULL,
updated_At timestamp NOT NULL
)"""
cursor.execute(query)
get_connection().commit()
return redirect(url_for('home_page'))
def update_section_only(self, id, section_only):
with get_connection().cursor() as cursor:
query = """UPDATE files SET section_only = %s WHERE id = %s"""
cursor.execute(query, [section_only, id])
get_connection().commit()
return True
def update_section_only(self, id, section_only):
with get_connection().cursor() as cursor:
query = """UPDATE messages SET section_only = %s WHERE id = %s OR thread_id = %s"""
cursor.execute(query, [section_only, id, id])
cursor.close()
return True
