def test_add_basic_reservation(self):
num_reservations_before = len(Reservation.query.all())
student = User.query.filter_by(name='student').first()
team_type = TeamType.query.filter_by(name='other_team').first()
team = Team(name="testteam1")
team.team_type = team_type
team.members.append(student)
database.get_db().add(team)
database.get_db().commit()
team_id = team.id
room = Room.query.first()
rv = self.app.post(
'/v1/reservation',
data=json.dumps({
"team_id": team.id,
"room_id": room.id,
"start": datetime.datetime.now().isoformat(),
"end": (datetime.datetime.now() + datetime.timedelta(hours=1)).isoformat()
}),
content_type='application/json',
headers={
"Authorization": "Bearer " + student.generate_auth_token()
}
)
num_reservations_after = len(Reservation.query.all())
self.assertEquals(rv.status_code, 201)
self.assertEquals(len(Reservation.query.filter_by(team_id=team_id).all()), 1)
self.assertEquals(num_reservations_after - num_reservations_before, 1)
def team_update(token_user, team_id):
"""Update a team's name given name."""
team = Team.query.get(team_id)
if team is None:
abort(404, 'team not found')
if not json_param_exists('name'):
abort(400, 'one or more required parameter is missing')
name = request.json['name']
if not (token_user.has_permission('team.update.elevated') or
(token_user.has_permission('team.update') and
team.has_member(token_user))):
abort(403, 'insufficient permissions to modify team')
team.name = name
try:
get_db().add(team)
get_db().commit()
except IntegrityError:
abort(409, 'team name is already in use')
return '', 204
def test_get_team_user_is_not_on(self):
"""Test that a non-elevated user can not query extended details of
other teams."""
student = User.query.filter_by(name='student').first()
professor = User.query.filter_by(name='professor').first()
team_type = TeamType.query.filter_by(name='single').first()
team = Team(name="testteam1")
team.team_type = team_type
team.members.append(professor)
database.get_db().add(team)
database.get_db().commit()
rv = self.app.get(
'/v1/team/' + str(team.id),
content_type='application/json',
headers={
"Authorization": "Bearer " + student.generate_auth_token()
}
)
self.assertEquals(rv.status_code, 200)
got = json.loads(rv.data)
self.assertEquals(got["id"], team.id)
self.assertEquals(got["type"], team.team_type.name)
self.assertTrue("name" not in got)
self.assertTrue("members" not in got)
self.assertTrue("advance_time" not in got)
def room_update(room_id):
"""Update a room given its room number and feature list."""
room = Room.query.get(room_id)
if room is None:
abort(404, 'room not found')
if not json_param_exists('number'):
abort(400, 'invalid room number')
number = request.json['number']
room.number = number
if not json_param_exists('features'):
abort(400, 'one or more required parameter is missing')
features = request.json['features']
# remove relationships not in features
for r in room.features:
if r not in features:
room.features.delete(r)
# add relationships in features
for f in features:
if f not in room.features:
room.features.add(f)
get_db().commit()
return '', 204
def team_add(token_user):
"""Add a team given a team name."""
if not json_param_exists('name') or \
not json_param_exists('type'):
abort(400, "one or more required parameter is missing")
name = request.json['name']
team_type = TeamType.query.filter_by(name=request.json['type']).first()
if not team_type:
abort(400, "invalid team type")
if team_type.name == 'other_team':
if not token_user.has_permission('team.create') and \
not token_user.has_permission('team.create.elevated'):
abort(403, 'team creation is not permitted')
else: # creating any team other than 'other_team' requires elevated
if not token_user.has_permission('team.create.elevated'):
abort(403, 'insufficient permissions to create a team of this type')
team = Team(name=name)
team.team_type = team_type
try:
get_db().add(team)
get_db().commit()
except IntegrityError:
abort(409, 'team name is already in use')
return '', 201
def team_user_add(token_user, team_id, user_id):
"""Add a user to a team given the team and user IDs."""
team = Team.query.get(team_id)
if team is None:
abort(404, 'team not found')
# check for permissions to update the team
if not (token_user.has_permission('team.update.elevated') or
(token_user.has_permission('team.update') and
team.has_member(token_user))):
abort(403, 'insufficient permissions to add user to team')
# don't allow adding to 'single' teams
if team.team_type == TeamType.query.filter_by(name='single').first():
abort(400, 'cannot add a user to a "single" team')
user = User.query.get(user_id)
if user is None:
abort(400, 'invalid user id')
if team.has_member(user):
abort(409, 'user already in team')
user.teams.append(team)
get_db().commit()
return '', 201
def test_add_team_member_invalid_already_in_team(self):
"""Test that users can be added from teams."""
team_creator = User.query.filter_by(name='student').first()
t = Team(name='test')
t.members.append(team_creator)
team_type = TeamType.query.filter_by(name='other_team').first()
t.team_type = team_type
second_user = User.query.filter_by(name='labbie').first()
t.members.append(second_user)
database.get_db().add(t)
database.get_db().commit()
team_id = t.id
second_user_id = second_user.id
# test adding the user to the team
rv = self.app.post(
'/v1/team/' + str(team_id) + '/user/' + str(second_user_id),
content_type='application/json',
headers={
'Authorization': 'Bearer ' + team_creator.generate_auth_token()
}
)
self.assertEquals(rv.status_code, 409)
new_team = Team.query.filter_by(name='test').first()
self.assertEquals(len(new_team.members), 2)
def get_sequence(self):
if not self.sequence:
if self.phage_id:
row = get_db().get("SELECT Sequence from phage where phageID = %s", self.phage_id)
self.sequence = row[0]
elif self.name:
row = get_db().get("SELECT Sequence from phage where Name like %s", self.name)
self.sequence = row[0]
return self.sequence
def room_delete(room_id):
"""Remove a room given its ID."""
room = Room.query.get(room_id)
if room is None:
abort(404, 'room not found')
get_db().delete(room)
get_db().commit()
return '', 204
def reservation_update(token_user, res_id):
"""Update a reservation.
Uses a room ID, start and end datetimes.
"""
if not json_param_exists('room_id') or \
not json_param_exists('start') or \
not json_param_exists('end'):
abort(400, 'one or more required parameter is missing')
room_id = request.json['room_id']
room = Room.query.get(room_id)
if room is None:
abort(400, 'invalid room id')
start = parse_datetime(request.json['start'])
end = parse_datetime(request.json['end'])
if start is None or end is None:
abort(400, 'cannot parse start or end date')
res = Reservation.query.get(res_id)
if res is None:
abort(400, 'invalid reservation id')
if not token_user.has_permission('reservation.update.elevated'):
is_my_reservation = any(map(lambda m: m.id == token_user.id,
res.team.members))
if not (is_my_reservation and
token_user.has_permission('reservation.update')):
abort(403, 'insufficient permissions to update reservation')
res.room = room
res.start = start
res.end = end
attempt_override = False
if json_param_exists("override") and isinstance(request.json["override"], bool):
attempt_override = request.json["override"]
conflict_status, conflicting_reservations = res.validate_conflicts()
if conflict_status == Reservation.NO_CONFLICT:
pass
elif conflict_status == Reservation.CONFLICT_OVERRIDABLE:
if attempt_override:
# Delete conflicting reservations
for conflict in conflicting_reservations:
get_db().delete(conflict)
else:
return json.dumps({"overridable": True}), 409
elif conflict_status == Reservation.CONFLICT_FAILURE:
return json.dumps({"overridable": False}), 409
get_db().commit()
return '', 204
def insertResetAttempt(email):
db = get_db()
(dt,) = query_db("SELECT datetime('now','localtime')", one=True)
t = (email, dt)
db.execute("INSERT INTO resetPw (email, timestamp) VALUES (?,?)", t)
db.commit()
def set_user():
g.user = None
if "user_id" in session:
user_id = session.get("user_id")
stmt = t_user.select([t_user]).where(t_user.c.FId == user_id)
db = get_db()
g.user = db.execute(stmt).fetchone()
def get_name(self):
if not self.name:
row = get_db().get("SELECT Name, Cluster, Sequence from phage where PhageID = %s", self.phage_id)
self.name = row[0]
self.cluster = row[1]
self.sequence = row[2]
return self.name
def main(self):
self._running = True
self.bind_sig()
if Config["auto_start"]:
self.start_alarm("Auto Start")
if Config["auto_arm"]:
self.alarm.arm("Auto Arm")
while self._running:
try:
with closing(database.get_db()) as db:
c = db.cursor()
c.execute("select cmd_id, data from cmdq;")
cmds = c.fetchall()
if cmds:
for cmd in cmds:
cmd_id, data_s = cmd
data = json.loads(data_s)
self.process_cmd(cmd_id, data)
self.log_state()
time.sleep(Config["manager_sleep"])
except Exception as err:
self.log("Error in manager Main loop", err)
except KeyboardInterrupt:
self.log("Recivecd Ctl-C: exiting ")
self.stop_alarm("Recivecd Ctl-C")
self._running = False
self.log_state()
def refreshVerification(email):
db = get_db()
dt = query_db("SELECT datetime('now','localtime')",one=True)
t = (dt[0],email)
db.execute("UPDATE VERIFICATION SET timestamp=? WHERE email=(?)",t)
updateVerificationCount(email,1)
g.db.commit()
def addVerification( email, token):
db = get_db()
dt = query_db("SELECT datetime('now','localtime')",one=True)
t = (token,dt[0],email)
db.execute("INSERT INTO VERIFICATION values(?,?,'0',?)",t)
updateVerificationCount(email,1)
g.db.commit()
def verificationFromToday(email):
g.db = get_db()
t = (email,)
result = query_db("SELECT count(strftime('%Y-%m-%d',timestamp,'localtime')) from verification WHERE strftime(" + \
"'%Y-%m-%d',timestamp,'localtime')=strftime('%Y-%m-%d','now','localtime') and email=?",t,one=True)
count = result[0]
return count == 1
def process(raw):
# Check the message is from the right chat channel
if raw['c'] == '@A' or raw['c'] == 'privatein' or (raw['c'] == '@C' and raw['m'].strip()[0] == '!') or (raw['c'] == '@C' and 'botx' in raw['m'].lower()) or (raw['c'] == '@C' and 'random' in raw['m'].lower()):
# Save sender name if whispering
database.set_setting('lastwhisper',raw['s'][1:])
# Filter out the text and the sender
text = raw['m']
sender = raw['s'][1:]
# Run through filters file
filters.process(text,sender,raw['c'][1:])
# Query the database for any matching filters
db = database.get_db()
r = db.execute("select * from filters where ? like search",(text,))
# Loop through results
for out in r:
# Replace sender and arg with correct values
out = out[1].replace('{{sender}}', sender)
try:
out = out.replace('{{arg}}', text.split(' ',1)[1])
except IndexError:
pass
output(out,raw['c'][1:])
return True
def chat(identifier):
bad_session = 'identifier' not in session or session['identifier'] != identifier
db = get_db()
entry = db.execute('select chat from chats where chat = ?', [int(identifier,16)]).fetchone()
if ((not entry) | bad_session):
return redirect(url_for('index', _external=True))
return render_template('chat.html')
def get_id(self):
if not self.phage_id:
row = get_db().get("SELECT PhageID, Cluster, Sequence from phage where Name like %s", self.name)
self.phage_id = row[0]
self.cluster = row[1]
self.sequence = row[2]
return self.phage_id
def update_db(self):
''' update tagname of tag. '''
db = get_db()
db.execute('''update tags set tagname = ?, creater = ?, create_time = ?
where tag_id = ?''',
[self.tagname, self.creater, self.init_time, self.tag_id])
db.commit()
def test_user_found(self):
"""Test that the user is returned."""
u = User(name='Catherine', email='*****@*****.**')
database.get_db().add(u)
database.get_db().commit()
rv = self.app.get(
'/v1/user/' + str(u.id),
content_type='application/json'
)
self.assertEquals(rv.status_code, 200)
got = json.loads(rv.data)
self.assertEquals(got["id"], u.id)
self.assertEquals(got["name"], u.name)
self.assertEquals(got["email"], u.email)
self.assertEquals(len(got["teams"]), 0)
self.assertEquals(len(got["permissions"]), 0)
def login():
error = None
userFound = False
passwordCorrect = False
#calling db
db = get_db()
cur = db.execute('select username, password from users order by usid desc')
entries = cur.fetchall()
if request.method == 'POST':
#gets user info
for entry in entries:
if request.form['username'] == entry[0]:
userFound = True
if request.form['password'] == entry[1]:
passwordCorrect = True
#performs check on user info
if not userFound:
error = 'Invalid username'
elif not passwordCorrect:
error = 'Invalid password'
else:
session['logged_in'] = True
session['currentuser'] = request.form['username']
cur2 = db.execute('select usid from users where username=?',(session['currentuser'],))
session['currentuserid']= cur2.fetchone()['usid']
flash('You were logged in')
return redirect(url_for('show_entries'))
return render_template('login.html', error=error)
def get_protein_sequences():
proteins = []
results = get_db().query('SELECT GeneID, translation from gene')
for row in results:
gene_id = row[0].replace("-", "_")
protein = SeqRecord(Seq(row[1].replace('-', ''), IUPAC.protein), id=gene_id+"_", name=row[0], description=gene_id)
proteins.append(protein)
return proteins
def index():
cursor = get_db().cursor()
# cursor.execute("[QUERY 1]")
rows = cursor.fetchall()
return render_template(
'index.html',
colnames=('Movie', 'Director', 'Year', 'Rating'),
rows=rows)
def create_db(self):
'''
create a new connection betweem tag and task.
'''
db = get_db()
db.execute('''insert into ttl (task_id, tag_id) values (?, ?)''',
[self.task.task_id, self.tag.tag_id])
db.commit()
def room_add():
"""Add a room, given the room number."""
if not json_param_exists('number'):
abort(400, 'invalid room number')
if not isinstance(request.json['number'], str):
abort(400, 'room number must be string')
num = request.json['number']
room = Room(number=num)
try:
get_db().add(room)
get_db().commit()
except IntegrityError:
abort(409, 'room number is already in use')
return json.dumps(room.as_dict(include_features=False)), 201
def send(identifier):
if 'identifier' in session and session['identifier'] == identifier:
sender = session['name']
db = get_db()
db.execute('insert into messages values (?, ?, ?, ?, ?)',
[int(identifier, 16), session['name'], session['name'] ^ 1, request.form['message'], timegm(gmtime())])
db.commit()
return str()
def join():
cursor = get_db().cursor()
cursor.execute("[QUERY 3]")
rows = cursor.fetchall()
return render_template(
'index.html',
colnames=('Name', 'Movie', 'Rating'),
rows=rows)
def reservation_delete(token_user, res_id):
"""Remove a reservation given its ID."""
res = Reservation.query.get(res_id)
if res is None:
abort(404, 'reservation not found')
if not token_user.has_permission('reservation.delete.elevated'):
is_my_reservation = any(map(lambda m: m.id == token_user.id,
res.team.members))
if not (is_my_reservation and
token_user.has_permission('reservation.delete')):
abort(403, 'insufficient permissions to delete reservation')
get_db().delete(res)
get_db().commit()
return '', 204
def promote(doctor_id):
db = get_db()
db.execute('update doctor set expert =1 where id = ?',[doctor_id])
db.commit()
return redirect(url_for('doctors'))
def get_pubs():
db = d.get_db()
cur = db.execute('select * from pubs where visible!=?', [0])
entries = cur.fetchall()
return entries
def before_request():
flask.g.db = database.get_db()
flask.g.title = Config["title"]
def promote(user_id):
db = get_db()
db.execute('update users set expert = 1 where id = ?', [user_id])
db.commit()
return redirect(url_for('users'))
def viewresult():
db = get_db()
db.execute('select id, x, y, z from location')
results = db.fetchall()
return render_template('viewresult.html', result=results)
def get_user_by_username(username):
db = d.get_db()
cur = db.execute('select pass from users where name=?', [username])
entries = cur.fetchall()
return entries
def delete(self, id):
collection = get_db()['services']
res = collection.delete_one({"_id": ObjectId(id)})
if res.deleted_count <= 0:
return {"_id": id}, 404
return {}, 200
def delete_actual_voting(user):
db = d.get_db()
db.execute('delete from votings where date=? and user=?',
[h.get_current_time_in_s(), user])
db.commit()
def remove_booking(booking_id):
db = get_db()
db.cursor().execute('DELETE FROM bookings WHERE booking_id=?',
(booking_id, ))
db.commit()
def get_actual_voting_for_user(user):
db = d.get_db()
cur = db.execute('select * from votings where date=? and user=?',
[h.get_current_time_in_s(), user])
entries = cur.fetchall()
return entries
def close_connection(exception):
get_db(g).close()
def get_member(member_id):
db = get_db()
member_cur = db.execute('select id, name, email, level from members where id = ?', [member_id])
member = member_cur.fetchone()
return jsonify({'member' : {'id' : member['id'], 'name' : member['name'], 'email' : member['email'], 'level' : member['level']}})
from database import get_db
conn, cursor = get_db()
def get_job_list():
cursor.execute("SELECT * FROM dbo.Job")
print("\t*-- JOB LIST --*")
for row in cursor:
print("\t{} - \t{}".format(str(row[0]), row[1]))
def insert_job_to_db(job_name):
query = "INSERT INTO Job (Name) VALUES (%s)"
cursor.execute(query, job_name)
conn.commit()
def save(self):
#fetch new session ideally we get the same one.
db=next(get_db())
db.add(self)
db.commit()
def delete_member(member_id):
db = get_db()
db.execute('DELETE FROM members WHERE id = ?', [member_id])
db.commit()
return jsonify({'message': 'The member has been deleted!'})
def get_member(member_id):
db = get_db()
member_cur = db.execute('SELECT id, name, email, level FROM members WHERE id = ?', [member_id])
member = member_cur.fetchone()
return jsonify({'members' : {'id' : member['id'], 'name' : member['name'], 'email' : member['email'], 'level' : member['level'] }})
def delete_member(member_id):
db = get_db()
db.execute('delete from members where id = ?', [member_id])
db.commit()
return jsonify({'message': 'the member has been deleted'})
def get_groups(cls):
with get_db() as session:
return cls.serialize_groups(
session.run("MATCH (g:Group) RETURN g").value())
import challonge
import trueskillapi
from fuzzywuzzy import fuzz, process
from operator import itemgetter
import sqlite3
import sys
import datetime
import re
import json
import os
import database
database.get_db()
def set_credentials(user):
if os.environ.has_key(user):
challonge.set_credentials(user, os.environ[user])
else:
challonge.set_credentials(user, json.loads(open("./credentials.json").read())[user])
set_credentials("hartlax")
set_credentials("gameworksseattle")
set_credentials("epeengaming")
def get_all_the_sets(tourneyURL):
list_of_sets = challonge.matches.index(tourneyURL)
return list_of_sets
def get_all_sponsors():
sponsors = []
def doc_list():
method_display = {
"recent_write": "최근에 작성한 순서",
"recent_edit": "최근에 수정한 순서",
"가": "ㄱ/ㄲ",
"나": "ㄴ",
"다": "ㄷ",
"라": "ㄹ",
"마": "ㅁ",
"바": "ㅂ/ㅃ",
"사": "ㅅ/ㅆ",
"아": "ㅇ",
"자": "ㅈ/ㅉ",
"차": "ㅊ",
"카": "ㅋ",
"타": "ㅌ",
"파": "ㅍ",
"하": "ㅎ",
"a": "A",
"b": "B",
"c": "C",
"d": "D",
"e": "E",
"f": "F",
"g": "G",
"h": "H",
"i": "I",
"j": "J",
"k": "K",
"l": "L",
"m": "M",
"n": "N",
"o": "O",
"p": "P",
"q": "Q",
"r": "R",
"s": "S",
"t": "T",
"u": "U",
"v": "V",
"w": "W",
"x": "X",
"y": "Y",
"z": "Z",
"special": "특수문자"
}
hanguel = [
'가', '나', '다', '라', '마', '바', '사', '아', '자', '타', '카', '타', '파',
'하', '힣'
]
if not request.args.get('method'):
method = 'recent_write'
else:
method = request.args.get('method')
if not request.args.get('page_number'):
page_number = 1
else:
page_number = int(request.args.get('page_number'))
limit_point = (page_number - 1) * 30
db = database.get_db()
if method == 'recent_write':
count = db.execute(
"SELECT count(*) as count FROM doc_list").fetchone()['count']
list_data = db.execute(
"SELECT name FROM doc_list ORDER BY datetime(date) DESC LIMIT ? OFFSET ?",
(30, limit_point))
elif method == 'recent_edit':
count = db.execute(
"SELECT count(*) as count FROM history").fetchone()['count']
list_data = db.execute(
"SELECT name FROM (history join doc_list using (id)) as t ORDER BY datetime(t.date) DESC LIMIT ? OFFSET ?",
(30, limit_point))
elif method in hanguel: # 한글 일 경우
start = hanguel.index(method)
end = start + 1
if method == '하': # 마지막일 경우
count = db.execute(
"SELECT count(*) as count FROM doc_list WHERE name >= ? and name <= ?",
(hanguel[start], hanguel[end])).fetchone()['count']
list_data = db.execute(
"SELECT name FROM doc_list WHERE name >= ? and name <= ? LIMIT ? OFFSET ?",
(hanguel[start], hanguel[end], 30, limit_point))
else:
count = list_data = db.execute(
"SELECT count(*) as count FROM doc_list WHERE name >= ? and name < ?",
(hanguel[start], hanguel[end])).fetchone()['count']
list_data = db.execute(
"SELECT name FROM doc_list WHERE name >= ? and name < ? LIMIT ? OFFSET ?",
(hanguel[start], hanguel[end], 30, limit_point))
else: #영문 또는 기타 문자임
if method == 'special':
count =\
db.execute("SELECT count(*) as count FROM doc_list WHERE not (name >= '가' and name <= '힣') and not (LOWER(name) >= 'a' and LOWER(name) <= 'z')").fetchone()['count']
list_data =\
db.execute("SELECT name FROM doc_list WHERE not (name >= '가' and name <= '힣') and not (LOWER(name) >= 'a' and LOWER(name) <= 'z') LIMIT ? OFFSET ?", (30, limit_point))
else:
statement = method + '%'
count = db.execute(
"SELECT count(*) as count FROM doc_list WHERE LOWER(name) LIKE ?",
(statement, )).fetchone()['count']
list_data = db.execute(
"SELECT name FROM doc_list WHERE LOWER(name) LIKE ? LIMIT ? OFFSET ?",
(statement, 30, limit_point))
page_numbers = []
if count % 30 == 0:
last_page = int(count / 30)
else:
last_page = int(count / 30) + 1
for number in range(-4, 4):
if 0 < page_number + number <= last_page:
page_numbers.append(page_number + number)
if list_data is not None:
list_data = list_data.fetchall()
return render_template('document/doc_list.html',
setting=get_sidebar_info(),
sidebar_list=get_current_list(),
nav={'document': False},
method=method,
method_print=method_display[method],
list=list_data,
page_numbers=page_numbers,
page_number=page_number,
last_page=last_page)
def delete_group(cls, group_id):
with get_db() as session:
group_link = cls.form_group_link(group_id)
session.run(
"MATCH (g:Group {groupLink: $group_link}) DETACH DELETE g",
group_link=group_link)
def delete_food(food_id):
db = get_db()
db.execute('DELETE from foods WHERE id = ?', [food_id])
db.commit()
return jsonify({'message': 'The food has been deleted!'})
def post(self):
collection = get_db()['acuerdos']
body = api.payload
res = collection.insert_one(body)
return {"_id": str(res.inserted_id)}, 200
def question(question_id):
user = get_current_user()
db = get_db()
question_cur = db.execute('select questions.question_text, questions.answer_text, askers.name as asker_name, experts.name as expert_name from questions join users as askers on askers.id = questions.asked_by_id join users as experts on experts.id = questions.expert_id where questions.id = ?', [question_id])
question = question_cur.fetchone()
return render_template('question.html', question=question, user=user)
def db_test():
client, database = get_db()
with client.start_session() as session:
def delete(self, id):
db = get_db()
res = db['users'].delete_one({"_id": ObjectId(id)})
if res.deleted_count <= 0:
return {"_id": id}, 404
return {}, 200
def shutdown_session(exception=None):
"""End the database session."""
get_db().remove()
def clear_cmd(self, cmd_id):
with closing(database.get_db()) as db:
c = db.cursor()
c.execute("delete from cmdq where cmd_id = %s" % (cmd_id, ))
db.commit()
def update_password(user, new_pass):
db = d.get_db()
cur = db.execute('update users set pass=? where name=?',
[h.compute_hash_in_hex(new_pass), user])
db.commit()
def delete_member(member_id):
db = get_db()
db.execute('delete from members where id = ?', [member_id])
db.commit()
return jsonify({'message': 'Delete complete'})
