"""Utility procedures for the COMP249 web application"""
from comp249db import connect, pwcrypt
import os, Cookie, time
# global to hold the name of the cookie we use
COOKIE_NAME = "sessionid"
##--------------code provided for you-------------------------------------
import random
def randomidentifier():
"""Generate a random identifier, for example for a session identifier"""
alpha = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
id = ""
for i in range(10):
id += random.choice(alpha)
return id
##---------------code you need to write ------------------------------------
# Functions dealing with updates
#
def add_update(email, update):
"""Add an update for this user. The time of the update
is set to the current time.
Returns the id of the update once it's stored.
"""
# Create database connection
conn = connect()
c = conn.cursor()
# Get current epoch time, cast as int
curTime = int(time.time())
# SQL insert and commit
c.execute("INSERT INTO updates (user, time, text) VALUES (?, ?, ?)", (email, curTime, update))
conn.commit()
# Assign last row'd id to variable
id = c.lastrowid
# Clean up
c.close()
conn.close()
# Return id from last row in table
return id
def get_update(upid):
"""Return a tuple of (user, time, text) for the given update id.
If the update doesn't exist, return None"""
# Create database connection
conn = connect()
c = conn.cursor()
# Render upid value safe
safeId = (upid, )
# SQL select using safe value and assign to a variable
c.execute("SELECT user, time, text FROM updates WHERE id = ?", safeId)
update = c.fetchone()
# Clean up
c.close()
conn.close()
# If update is empty, return none, else return the tuple
if not update:
return None
else:
return update
def get_user_updates(email):
"""Return the five most recent updates from this user
in reverse order - most recent first.
Returns a list of tuples containing:
[(upid, email, summary, time),
(upid, email, summary, time)...]
where upid is the update id,
email is the id of the user posting the update and
summary is the first 30 characters of the update text.
"""
# Create database connection
conn = connect()
c = conn.cursor()
# Nerf supplied email value
safeEmail = (email, )
# SQL select, take a substring of the update text, order by most recent id and limit responses to 5
c.execute("SELECT id, user, SUBSTR(text,1,30), time FROM updates WHERE user = ? ORDER BY ID DESC LIMIT 5", safeEmail)
# Assign all returned tuples to variable
updates = c.fetchall()
# Clean up
c.close()
conn.close()
# Since no validation is specified for this function, return the tuples
return updates
def get_friend_updates(email):
"""Return the five most recent updates from any friend of
this user in reverse order - most recent first.
Returns a list of tuples containing:
[(upid, email, summary, time),
(upid, email, summary, time)...]
where upid is the update id,
email is the id of the user posting the update and
summary is the first 30 characters of the update text.
"""
# Create database connection
conn = connect()
c = conn.cursor()
# Nerf supplied email value
safeEmail = (email, )
# SQL select, join updates & friends table, take a substring of the update text, order by most recent id and limit responses to 5
c.execute("SELECT id, user, SUBSTR(text,1,30), time FROM updates, friends WHERE updates.user = friends.user2 AND friends.user1 = ? ORDER BY ID DESC LIMIT 5", safeEmail)
# Assign all returned tuples to variable
updates = c.fetchall()
# Clean up
c.close()
conn.close()
# Since no validation is specified for this function, return the tuples
return updates
# Functions dealing with users
#
def display_name(email):
"""Return a display name for this user. This will
be the first name followed by the last name
separated by a space, suitable for display in the web application.
"""
# Create database connection
conn = connect()
c = conn.cursor()
# Nerf supplied email value
safeEmail = (email, )
# SQL select, concatenate first and last name with a space in between
c.execute("SELECT first || ' ' || last FROM users WHERE email = ?", safeEmail)
# Insert the tuple into a variable
name = c.fetchone()
# Clean up
c.close()
conn.close()
# Since no validation is specified for this function, return the tuples
return name[0]
# Functions dealing with login/logout and cookies
#
def check_login(email, password):
"""Check that the password is valid for this user (email)
The email address is treated case insensitively.
If the password is ok, create a session key and store a new
entry in the sessions table with this user's email address and
the new session key.
Return a SimpleCookie object with
the session key stored under COOKIE_NAME (constant
defined in util.py).
If the password is not ok, return False."""
# Create database connection
conn = connect()
c = conn.cursor()
# Nerf supplied email value
safeEmail = (str.lower((email), )
# SQL select, username and password hash
c.execute("SELECT password FROM users WHERE email = ?", safeEmail)
pw = c.fetchone()
# Clean up
c.close()
conn.close()
if pwcrypt(password) == pw[0]:
sessId = randomidentifier()
# Create database connection
conn = connect()
c = conn.cursor()
# SQL insert session data into sessions table
c.execute("INSERT INTO sessions (sessionid, user) VALUES (?, ?)", (sessId, safeEmail))
conn.commit()
# Clean up
c.close()
conn.close()
# Create Cookie Object
cookie = Cookie.SimpleCookie()
cookie[COOKIE_NAME] = sessId
# Return Cookie Object
return cookie
else:
# Password does not match
return False
def logout(email):
"""Remove the user from the sessions table
If this user's id is in the sessions table,
remove it and return True. If there are multiple entries
for this user, remove them all. If there are no entries
return False."""
# Nerf provided email address
safeEmail = (email, )
# Create database connection
conn = connect()
c = conn.cursor()
c.execute("SELECT COUNT(*) FROM sessions WHERE user = ?", safeEmail)
tpl = c.fetchone()
sessions = tpl[0]
if sessions > 0:
c.execute("DELETE FROM sessions WHERE user = ?", safeEmail)
conn.commit()
return True
else:
return False
def get_user_from_cookie():
"""Check the environment for our session cookie, return
the user id if we find a valid one. Return None
if no cookie or an invalid session is found."""
pass # pass does nothing, remove this line and replace with your own code