def get_vote_data(rack_id, user_id): # return db row for row with rack_id=rack_id and user_id=user_id db = get_db() query = "SELECT * from votes WHERE rack_id = ? AND user_id = ?" result = db.execute(query, (rack_id, user_id)).fetchall() result = [h.dict_from_row(row) for row in result] return result
def get_racks(): status = request.args.get('status', type=str) user_id = request.args.get('userId', type=str) # make a connection to the database db = get_db() racks = helper.get_racks(db, status, user_id) return racks
def get_single_rack(): # get rack based on rack_id rack_id = request.args.get('rack_id', type=int) if not rack_id: return "No rack_id specified", 400 # database connection db = get_db() rack = helper.get_single_rack(db, rack_id) return rack
def submit_vote(): # insert a vote into the vote db for rack with rack_id = rack_id, vote by # user with user_id = user_id and vote_type=vote_type rack_id = request.args.get('rack_id', type=int) user_id = request.args.get('user_id', type=str) new_vote = request.args.get('vote_type', type=int) delta_up = 0 delta_down = 0 if not rack_id: return "No rack_id specified", 400 # connect to db db = get_db() # check if user has voted on this rack before row = h.get_vote_status(db, rack_id, user_id) old_vote = row[0] if row else 0 # vote_status is an object, {vote_type: -1} for example or None if new_vote == 0: query = """ DELETE FROM votes WHERE rack_id = ? AND user_id = ? """ db.execute(query, (rack_id, user_id)) else: query = """ INSERT INTO votes (rack_id, user_id, vote_type) VALUES (?, ?, ?) ON CONFLICT(rack_id, user_id) DO UPDATE SET vote_type=?""" db.execute(query, (rack_id, user_id, new_vote, new_vote)) db.commit() if new_vote > old_vote: # This is a new upvote (1, 0) or change from down to up (1, -1) or change from down to no vote (0, -1) delta_up = new_vote delta_down = old_vote elif new_vote < old_vote: # This is a new downvote (0, 1) or change from up to down (-1, 1) or change from up to no vote (-1, 0) delta_up = -old_vote delta_down = -new_vote if delta_up or delta_down: h.update_vote_count(db, rack_id, delta_up, delta_down) return ('OK', 200)
def submitSuggestion(): rack_id = request.form.get("rack_id", type=float) reason_id = request.form.get("reason_id", type=int) user_id = request.form.get("user_id", type=str) # connect to database db = get_db() query = """ INSERT INTO suggested_removals (rack_id, user_id, reason_id, time_stamp) VALUES (?, ?, ?, datetime('now'))""" db.execute(query, (rack_id, user_id, reason_id)) db.commit() return ('OK', 200)
def get_vote_status(): # return true if user with user_id = user_id has voted for # rack with rack_id=rack_id, false otherwise rack_id = request.args.get('rack_id', type=int) user_id = request.args.get('user_id', type=str) if not rack_id: return "No rack_id specified", 400 # make connection the database db = get_db() query = "SELECT vote_type FROM votes WHERE rack_id = ? AND user_id = ?" result = db.execute(query, (rack_id, user_id)).fetchone() if result: result = h.dict_from_row(result) return jsonify(result)
def coordinates(): # get the coordinates from the request lat = request.form.get('lat', 0, type=float) lng = request.form.get('lng', 0, type=float) address = request.form.get('address', '') if not helper.validate_coordinates((lat, lng)): return "Invalid Coordinates", 500 # if valid, save in database # connect to database to be able to store new coordinates for temporary bikerack db = get_db() db.execute( 'INSERT INTO bikeracks (latitude, longitude, address) VALUES (?, ?, ?);', (lat, lng, address)) db.commit() # return data for the added temporary marker bike_rack = helper.get_rack_state(db, lat, lng) return bike_rack