def deleteDepartment(dept_id):
try:
# retrieve dept to delete
dept_to_delete = session.query(Department).filter_by(id=dept_id).one()
departments = session.query(Department).order_by(asc(Department.name))
except (DBAPIError, SQLAlchemyError) as e:
return "Error 404! Requested URL was not found."
# check if dept belongs to curr user
if dept_to_delete.user_id != login_session['user_id']:
flash("You do not have permissions to delete %s" % dept_to_delete.name)
return redirect(url_for('viewDepartment', dept_id=dept_to_delete.id))
if request.method == 'POST':
# delete courses of this dept
dept_courses = session.query(Course).filter_by(department_id=dept_id)
for course in dept_courses:
session.delete(course)
# delete dept
session.delete(dept_to_delete)
flash("You have successfully deleted '%s'" % dept_to_delete.name)
session.commit()
return redirect(url_for('showDepartments'))
else:
return render_template("delete_dept.html",
departments=departments,
name=dept_to_delete.name)
def showDepartments():
try:
departments = session.query(Department).order_by(asc(Department.name))
courses = session.query(Course).filter_by(
department_id=departments[0].id)
except (DBAPIError, SQLAlchemyError) as e:
return "Error 404, requested URL was not found! Exception occured in Database."
except IndexError: # when no departments are found
if 'provider' in login_session:
flash(
"There are no departments or courses. Please start creating them!"
)
else:
flash(
"There are no departments or courses. Please login to get started!"
)
return render_template("dept_page.html",
departments=None,
curr_dept=None,
courses=None)
else:
return render_template("dept_page.html",
departments=departments,
curr_dept=departments[0],
courses=courses)
def editDepartment(dept_id):
try:
# retrieve dept to edit
dept_to_edit = session.query(Department).filter_by(id=dept_id).one()
departments = session.query(Department).order_by(asc(Department.name))
except (DBAPIError, SQLAlchemyError) as e:
return "Error 404! Requested URL was not found."
# check if dept belongs to curr user
if dept_to_edit.user_id != login_session['user_id']:
flash("You do not have permissions to edit %s" % dept_to_edit.name)
return redirect(url_for('viewDepartment', dept_id=dept_to_edit.id))
if request.method == 'POST':
dept_to_edit.name = request.form['name']
dept_to_edit.address = request.form['address']
dept_to_edit.img_url = request.form['img_url']
dept_to_edit.description = request.form['description']
flash("You have successfully edited %s" % dept_to_edit.name)
session.commit()
return redirect(url_for('viewDepartment', dept_id=dept_id))
else:
return render_template("new_department.html",
departments=departments,
name=dept_to_edit.name,
address=dept_to_edit.address,
img_url=dept_to_edit.img_url,
description=dept_to_edit.description,
edit=True)
def deleteCourse(dept_id, course_id):
# retrieve course for given dept from DB
try:
departments = session.query(Department).order_by(asc(Department.name))
curr_dept = session.query(Department).filter_by(id=dept_id).one()
dept_courses = session.query(Course).filter_by(department_id=dept_id)
curr_course = session.query(Course).filter(
and_(Course.id == course_id,
Course.department_id == dept_id)).one()
# handle database exceptions
except (DBAPIError, SQLAlchemyError) as e:
return "Error 404, requested URL was not found! Exception occured in Database."
# check if course belongs to current user
if curr_course.user_id != login_session['user_id']:
flash("You do not have permissions to delete this course.")
return redirect(
url_for('viewCourse', dept_id=dept_id, course_id=course_id))
if request.method == 'POST':
session.delete(curr_course)
flash("You have successfully deleted %s - %s" %
(curr_course.id, curr_course.name))
session.commit()
return redirect(url_for('viewDepartment', dept_id=dept_id))
else:
return render_template("delete_course.html",
departments=departments,
id=curr_course.id,
name=curr_course.name)
def deptCoursesJSON(dept_id):
# JSON endpoint for a particular department's courses
try:
curr_dept = session.query(Department).filter_by(id=dept_id).one()
dept_courses = session.query(Course).filter_by(
department_id=dept_id).all()
except (DBAPIError, SQLAlchemyError) as e:
return "Error 404, requested URL was not found! Exception occured in Database."
else:
return jsonify(department=curr_dept.serialize,
courses=[course.serialize for course in dept_courses])
def viewDepartment(dept_id):
# retrieve given dept from DB
try:
departments = session.query(Department).order_by(asc(Department.name))
curr_dept = session.query(Department).filter_by(id=dept_id).one()
courses = session.query(Course).filter_by(department_id=dept_id)
# handle database exceptions
except (DBAPIError, SQLAlchemyError) as e:
return "Error 404, requested URL was not found! Exception occured in Database."
else:
return render_template("dept_page.html",
departments=departments,
curr_dept=curr_dept,
courses=courses)
def newDepartment():
if request.method == 'POST':
name = request.form['name']
address = request.form['address']
img_url = request.form['img_url']
description = request.form['description']
# create new department and commit to DB
try:
newDepartment = Department(name=name,
address=address,
img_url=img_url,
description=description,
user_id=login_session['user_id'])
session.add(newDepartment)
session.commit()
# handle database exceptions
except (DBAPIError, SQLAlchemyError) as e:
flash("An exception occurred in the database. Please try again!")
return redirect(url_for('showDepartments'))
else:
flash("Successfully created new department %s" %
newDepartment.name)
return redirect(url_for('showDepartments'))
else:
departments = session.query(Department).order_by(asc(Department.name))
return render_template("new_department.html", departments=departments)
def createUser(login_session):
newUser = User(name=login_session['username'],
email=login_session['email'],
picture=login_session['picture'])
session.add(newUser)
session.commit()
user = session.query(User).filter_by(email=login_session['email']).one()
return user.id
def __init__(self):
super(Network, self).__init__()
self._outputs = {}
self._inputs = {}
self._devices = {}
Device = Base.metadata.tables['device']
for device in session.query(Device).all():
with Domuino(device.address, device.code, 115200) as d:
self._devices[device.code] = d
def get_snapshot_for_task(task_id):
'''Get snapshot of existing task'''
task = session.query(CareTask).filter(CareTask.id == task_id).one()
new_snapshot_name = '{}-{}.png'.format(task.id, task.last_run_id + 1)
new_snapshot_path = '../snapshot/{}'.format(new_snapshot_name)
if take_snapshot(task, new_snapshot_path):
return send_file(new_snapshot_path, mimetype='image/png')
return 'Failed to take snapshot.'
def query_task():
task = (
session.query(Task)
.join(Story)
.join(User)
.filter(Story.story_title == "Story 001")
.filter(User.user_name == "User 003")
).one()
logger.info("Task in Story 001 and owned by User 003 is " + task.task_title)
return task
def courseJSON(dept_id, course_id):
# JSON endpoint for a particular course of a dept
try:
curr_course = session.query(Course).filter(
and_(Course.id == course_id,
Course.department_id == dept_id)).one()
except (DBAPIError, SQLAlchemyError) as e:
return "Error 404, requested URL was not found! Exception occured in Database."
else:
return jsonify(course=curr_course.serialize)
def run(pin_code=None, value=None):
global _inputs, _outputs
_inputs = network.get_inputs()
_outputs = network.get_outputs()
if pin_code:
device, pin_id = pin_code.split('_')
_inputs[int(device)][int(pin_id)] = int(value)
pin_served = []
for event in session.query(Event).all():
if event.start == event.end or event.start <= time.time() <= event.end:
if _get_pin_value(event.event_pin) == event.event_value:
for action in event.actions:
if action.output_pin not in pin_served:
if action.ref_value is None or _get_pin_value(
action.ref_pin) == action.ref_value:
functions[action.function.name](action)
pin_served.append(action.output_pin)
def next(klass):
query = """
UPDATE %s SET
processing = true,
tries_left = tries_left - 1,
error = NULL,
next_try_at = NULL,
updated_at = CURRENT_TIMESTAMP
WHERE id = (
SELECT id
FROM %s
WHERE tries_left > 0
AND (
next_try_at IS NULL OR
next_try_at < CURRENT_TIMESTAMP
)
AND (
processing = false OR
updated_at < CURRENT_TIMESTAMP - INTERVAL '5 SEC'
)
ORDER BY priority ASC, next_try_at ASC, id ASC
FOR UPDATE SKIP LOCKED
LIMIT 1
)
RETURNING *
""" % (klass.__tablename__, klass.__tablename__)
try:
rs = session.execute(query).fetchone()
session.commit()
if rs:
result = session.query(PaymentTask).filter_by(
id=rs['id']).one()
return result
else:
return None
except NoResultFound:
print('No task to update')
# -*- coding: utf-8 -*-
import os
import logging
from datetime import datetime
from db.model import CareTask, TaskLog
from db_session import session
from snapshot import take_snapshot
from image_diff import compare_img
from mailgun import notify_change
import setup
logger = logging.getLogger(__name__)
for t in session.query(CareTask).all():
now = datetime.utcnow()
new_snapshot_taken = False
try:
time_past = (now - t.last_run_time).total_seconds()
if time_past >= t.interval:
check_log = TaskLog(task_id=t.id, timestamp=now, success=False)
try:
logger.info('[Task {}] last_run_time {}'.format(
t.id, t.last_run_time))
# get new snapshot
old_snapshot_path = '../snapshot/{}-{}.png'.format(
t.id, t.last_run_id)
new_snapshot_name = '{}-{}.png'.format(t.id, t.last_run_id + 1)
new_snapshot_path = '../snapshot/{}'.format(new_snapshot_name)
def allCoursesJSON():
# JSON endpoint for all courses in DB
courses = session.query(Course).order_by(asc(Course.department_id)).all()
return jsonify(courses=[course.serialize for course in courses])
def allDepartmentsJSON():
# JSON endpoint for all departments in DB
departments = session.query(Department).order_by(asc(
Department.name)).all()
return jsonify(departments=[dept.serialize for dept in departments])
import logging
from db_objects import Story, User, Task
from db_session import session
logging.basicConfig()
logger = logging.getLogger("db_query")
logger.setLevel(logging.DEBUG)
# Query one task
task = (session.query(Task).join(Story).join(User)
.filter(Story.story_title == 'Story 001')
.filter(User.user_name == 'User 003')).one()
logger.info('Task in Story 001 and owned by User 003 is:')
logger.info('\tid = ' + str(task.id))
logger.info('\ttask_title = ' + task.task_title)
logger.info('\tstory_id = ' + str(task.story_id))
logger.info('\tuser_id = ' + str(task.user_id))
# Delete this task and query again
session.delete(task)
session.commit()
num = (session.query(Task).join(Story).join(User)
.filter(Story.story_title == 'Story 001')
.filter(User.user_name == 'User 003')).count()
logger.info(str(num) + " task queried")
# Add the task back and query again
session.add(Task(id=3, task_title='Task 003', story_id=1, user_id=3))
session.commit()
task = (session.query(Task).join(Story).join(User)
import logging
from db_objects import Story, User, Task
from db_session import session
logging.basicConfig()
logger = logging.getLogger("db_query")
logger.setLevel(logging.DEBUG)
# Query by instance
logger.info('------ Query by instance ------')
for story in session.query(Story).order_by(Story.id).all():
logger.info('ID = ' + str(story.id) + ', Story Title = '
+ story.story_title)
for task in session.query(Task).order_by(Task.id).all():
logger.info('ID = ' + str(task.id) + ', Task Title = '
+ task.task_title + ', Story ID = '
+ str(task.story_id) + ', User ID = '
+ str(task.user_id))
# Query by attributes
logger.info('------ Query by attributes ------')
for id, user_name in (session.query(User.id, User.user_name).
order_by(User.id).all()):
logger.info('ID = ' + str(id) + ', User Name = ' + user_name)
# Query with foreign key
logger.info('------ Query with foreign key ------')
for story in session.query(Story).order_by(Story.id).all():
logger.info('ID = ' + str(story.id) + ', Story Title = '
def getUserID(email):
try:
user = session.query(User).filter_by(email=email).one()
return user.id
except:
return None
def getUserInfo(user_id):
user = session.query(User).filter_by(id=user_id).one()
return user
def editCourse(dept_id, course_id):
# retrieve course for given dept from DB
try:
departments = session.query(Department).order_by(asc(Department.name))
curr_dept = session.query(Department).filter_by(id=dept_id).one()
dept_courses = session.query(Course).filter_by(department_id=dept_id)
curr_course = session.query(Course).filter(
and_(Course.id == course_id,
Course.department_id == dept_id)).one()
# handle database exceptions
except (DBAPIError, SQLAlchemyError) as e:
return "Error 404, requested URL was not found! Exception occured in Database."
# check if course belongs to current user
if curr_course.user_id != login_session['user_id']:
flash("You do not have permissions to edit this course.")
return redirect(
url_for('viewCourse', dept_id=dept_id, course_id=course_id))
if request.method == 'POST':
name = request.form['name']
id = request.form['id']
professor = request.form['professor']
img_url = request.form['img_url']
credits = request.form['credits']
max_capacity = request.form['max_capacity']
description = request.form['description']
fields = [
departments, curr_dept, name, id, professor, img_url, credits,
max_capacity, description
]
# check if course code already exists
# check only if new id is different from old id
id_exists = (id != course_id) and session.query(Course).filter_by(
id=id).count() > 0
if id_exists:
error = "This course code already exists!"
return report_error(fields, error)
# validate course code length
if len(id) > 6:
error = "Course code cannot be more than 6 characters!"
return report_error(fields, error)
# validate credits
try:
int_credits = int(credits)
if int_credits > 5:
raise ValueError
except ValueError:
error = "Please enter a valid number of credits"
return report_error(fields, error)
# validate capacity
try:
int_capacity = int(max_capacity)
except ValueError:
error = "Please enter a valid integer for capacity."
return report_error(fields, error)
curr_course.name = name
curr_course.id = id
curr_course.professor = professor
curr_course.img_url = img_url
curr_course.credits = credits
curr_course.max_capacity = max_capacity
curr_course.description = description
session.commit()
flash("You have successfully edited %s" % curr_course.name)
return redirect(
url_for('viewCourse', dept_id=dept_id, course_id=curr_course.id))
else:
return render_template("new_course.html",
departments=departments,
curr_dept=curr_dept,
name=curr_course.name,
id=curr_course.id,
professor=curr_course.professor,
img_url=curr_course.img_url,
credits=curr_course.credits,
max_capacity=curr_course.max_capacity,
description=curr_course.description,
edit=True)
