Parts Implemented by Alperen Kantarcı

User creation, update and deleting functions implemented.Flask login and session management used with this user class. Message creation, and deletion operations implemented. News creation and deletion implemented. Functions that users could use also implemented by me. Any input form handling and code cleanup made by me.

Users Table, Class & Functions

There are 3 types of users admin,restaurant owner and customer. Restaurant owner and customer is generated by registration form but admin user create itself by init db.

Attributes of Users Table

  • ID SERIAL PRIMARY KEY
    Primary key of user
  • FIRSTNAME VARCHAR(80) NOT NULL
    Name of the user
  • LASTNAME VARCHAR(80) NOT NULL
    Surname of the user
  • MAIL VARCHAR(80) NOT NULL
    Email for sign in
  • PASSWORD VARCHAR(500) NOT NULL
    Hashed password
  • BIRTHDATE DATE NOT NULL
    Birthdate of th user
  • CITY VARCHAR(80) NOT NULL
    City that user’s live
  • GENDER VARCHAR(20)
    Gender of the user man,woman and other
  • USERTYPE INTEGER NOT NULL
    0 for admin 1 for restaurant owner 2 for customer
  • AVATAR VARCHAR(255)
    Avatar link
  • BIO VARCHAR(500)
    If user gives bio

Operations

To operate create, select, update and delete operations a class implemented.Also this class inherits UserMixin so for current_user in flask i added some properties to the user class. .. code-block:: python

@property
def get_gender(self):
return self.Gender

@property def get_birthdate(self):

return self.Birthdate

@property def get_bio(self):

return self.Bio

@property def get_city(self):

return self.City

@property def get_avatar(self):

return self.Avatar

@property def get_mail(self):

return self.Mail

@property def get_name(self):

return self.FirstName

@property def get_lastname(self):

return self.LastName

@property def get_type(self):

return self.UserType

@property def is_active(self):

return True

@property def is_authenticated(self):

return True

@property def is_anonymous(self):

return False
def get_id(self):
return self.Mail

@property def get_Id(self):

return self.Id

Create

To add new users constructor of Users class used. This constructor takes input parameters of the user. I inherited UserMixin class in order to handle flask_login operations

  class Users(UserMixin):
def __init__(self,Id,FirstName,LastName,Mail,Password,Birthdate,City,Gender,UserType,Avatar,Bio):
    self.Id = Id
    self.FirstName = FirstName
    self.LastName = LastName
    self.Mail = Mail
    self.Password = Password
    self.Birthdate = Birthdate
    self.Bio = Bio
    self.City = City
    self.Gender = Gender
    self.UserType = UserType
    self.Avatar = Avatar
    self.active = True
    if UserType == 0:
        self.is_admin = True
    else:
        self.is_admin = False

    with dbapi2.connect(current_app.config['dsn']) as connection:
        cursor = connection.cursor()
        query = """
        INSERT INTO USERS (FIRSTNAME, LASTNAME, MAIL, PASSWORD, BIRTHDATE, CITY, GENDER, USERTYPE, AVATAR, BIO)
        VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""

        cursor.execute(query, (firstName, lastName, email, hashed_password, birthDate,city, gender, userType, avatar,bio))

Select

There are 3 select methods for users. First one, get_user which flask login use it takes username as input and return user. Second one returns user by taking userid as input. Last one returns whole user list in the database.

    def get_user(db_mail):
if type(db_mail) is int:
    return None

if db_mail in current_app.config['ADMIN_USERS']:
    user = Users(1,'admin','admin','admin@restoranlandin.com',current_app.config['PASSWORD'], '2012-10-10', '', '',0, '','')
    return user

with dbapi2.connect(current_app.config['dsn']) as connection:
    cursor = connection.cursor()
    statement = """SELECT * FROM USERS WHERE MAIL = %s"""
    cursor.execute(statement, [db_mail])
    db_user = cursor.fetchone()
    user = Users(db_user[0],db_user[1], db_user[2], db_user[3],db_user[4], db_user[5], db_user[6], db_user[7], db_user[8], db_user[9], db_user[10])


if user is not None:
    user.is_admin = user.Mail in current_app.config['ADMIN_USERS']

return user

Update

An User can be updated from User Edit form.

with dbapi2.connect(current_app.config['dsn']) as connection:
            cursor = connection.cursor()
            query = """
                UPDATE USERS
                    SET FIRSTNAME = %s,
                        LASTNAME = %s,
                        MAIL = %s,
                        BIRTHDATE = %s,
                        CITY = %s,
                        GENDER = %s,
                        AVATAR = %s,
                        BIO = %s
                    WHERE (ID = %s)"""

            cursor.execute(query, (firstName, lastName, email, birthDate, city, gender, avatar, bio, current_user.get_Id))

Delete

Users can be deleted by admin from admin panel.

def delete_user_by_id(userID):
with dbapi2.connect(current_app.config['dsn']) as connection:
    cursor = connection.cursor()
    query = """
        DELETE FROM USERS WHERE ID = %s"""
    cursor.execute(query, [userID] )
    connection.commit()

Users deleted by their id.

Many of other tables referencing users table so other members implemented different functions for different usages.

News Table, Class & Functions

Admin can publish news from admin panel which includes topic and content and also can give external links or direction to the restaurant pages.

Attributes of News Table

  • ID SERIAL PRIMARY KEY
    Primary key of news id
  • TOPIC VARCHAR(80) NOT NULL
    Header of the news
  • CONTENT VARCHAR(800) NOT NULL
    Message content of the news
  • LINK VARCHAR(200)
    External link
  • RESTAURANT INTEGER REFERENCES RESTAURANTS(ID) ON DELETE CASCADE
    Restaurant link for restaurant that will be linked

Operations

To operate create, select and update and delete a class implemented.

Create

Whenever admin creates news this function is used.

Select

There are 2 select methods for news. First one, return only one new . Second one returns whole news as list in the database.

def get_news_by_id(id):
with dbapi2.connect(current_app.config['dsn']) as connection:
        cursor = connection.cursor()
        query = """
        SELECT * FROM NEWS WHERE ID = %s"""
        cursor.execute(query,[id])
        db_news = cursor.fetchone()
        news_class = News(db_news[1],db_news[2],db_news[3],db_news[4])
        news_class.find_news_id()
        return news_class



def get_all_news():
with dbapi2.connect(current_app.config['dsn']) as connection:
        cursor = connection.cursor()
        query = """SELECT * FROM NEWS"""
        cursor.execute(query)
        db_news = cursor.fetchall()

        if db_news is None:
        db_news = {}
        return db_news

Update

A News can be updated from News Edit form.

def update_news(self):
    with dbapi2.connect(current_app.config['dsn']) as connection:
        cursor = connection.cursor()
        if self.Restaurant == "":
            query = """UPDATE NEWS SET TOPIC = %s, CONTENT = %s, LINK = %s WHERE ID = %s"""
            cursor.execute(query,(self.Topic,self.Content,self.Link,self.Id))
        else:
            query = """UPDATE NEWS SET TOPIC = %s, CONTENT = %s, LINK = %s, RESTAURANT = %s WHERE ID = %s"""
            cursor.execute(query,(self.Topic,self.Content,self.Link,self.Restaurant,self.Id))
        connection.commit()

Delete

A News can be deleted from News Edit form.

def delete_news(Id):
if Id != '':
    with dbapi2.connect(current_app.config['dsn']) as connection:
        cursor = connection.cursor()
        statement = """DELETE FROM NEWS WHERE ID = %s"""
        cursor.execute(statement, [Id])
        connection.commit()

Messages Table, Class & Functions

Every user can send private messages to each other.Yet, admin user has access to see all user emails to send message to the users. So get_all_users function only can be used in admin page.

Attributes of Messages Table

  • ID SERIAL PRIMARY KEY
    Id used for primary key
  • SENDER INTEGER REFERENCES USERS(ID) ON DELETE CASCADE
    Sender id used for identification of sender, if user deleted messages will be deleted as well
  • RECEIVER INTEGER REFERENCES USERS(ID) ON DELETE CASCADE
    Receiver id used for identification of sender, if user deleted messages will be deleted as well
  • TOPIC VARCHAR(80) NOT NULL
    Topic of message
  • CONTENT VARCHAR(800) NOT NULL
    Content of the message
  • SENDDATE TIMESTAMP NOT NULL
    Send time for message

Operations

To operate create, select a class implemented.

Create

Whenever any user sends message this function will be used.

Select

Selecting all user messages from database.

def select_all_messages(user_id):

with dbapi2.connect(current_app.config[‘dsn’]) as connection:
cursor = connection.cursor() statement = “”“SELECT * FROM MESSAGES WHERE SENDER = %s OR RECEIVER = %s”“” cursor.execute(statement,(user_id,user_id)) messages = cursor.fetchall()
if len(messages) == 0:
return messages
for i in range(len(messages)):
with dbapi2.connect(current_app.config[‘dsn’]) as connection:
cursor = connection.cursor() statement = “”“SELECT FIRSTNAME FROM USERS WHERE ID = (SELECT SENDER FROM MESSAGES WHERE ID = %s)”“” cursor.execute(statement,[ messages[i][0]]) sender_name = (cursor.fetchone())[0]
with dbapi2.connect(current_app.config[‘dsn’]) as connection:
cursor = connection.cursor() statement = “”“SELECT LASTNAME FROM USERS WHERE ID = (SELECT SENDER FROM MESSAGES WHERE ID = %s)”“” cursor.execute(statement,[messages[i][0]]) sender_name += ” ” + (cursor.fetchone())[0]

messages[i] = list(messages[i]) messages[i].append(messages[i][1]) messages[i].append(messages[i][2]) messages[i][1] = sender_name messages[i] = tuple(messages[i])

with dbapi2.connect(current_app.config[‘dsn’]) as connection:
cursor = connection.cursor() statement = “”“SELECT FIRSTNAME FROM USERS WHERE ID = (SELECT RECEIVER FROM MESSAGES WHERE ID = %s)”“” cursor.execute(statement,[messages[i][0]]) receiver_name = (cursor.fetchone())[0]
with dbapi2.connect(current_app.config[‘dsn’]) as connection:
cursor = connection.cursor() statement = “”“SELECT LASTNAME FROM USERS WHERE ID = (SELECT RECEIVER FROM MESSAGES WHERE ID = %s)”“” cursor.execute(statement,[messages[i][0]]) receiver_name += ” ” + (cursor.fetchone())[0]

messages[i] = list(messages[i]) messages[i][2] = receiver_name messages[i] = tuple(messages[i])

return messages