Welcome to itucsdb1710’s documentation!¶

Login Management System¶

For the login part, i have used flask_login packages.

from flask_login import LoginManager
from flask_login.utils import login_required, login_user, current_user, logout_user

login_required This property should return True if the user is authenticated. login_user Once a user has authenticated, you log them in with the login_user function. logout_user Logs a user out. current_user you can access the logged-in user with the current_user proxy.

Login and logout forms

USERS TABLE¶

The USERS table stores the informations of the users such as their name, username, mail, password.

CREATE TABLE USERS (
                 NAME VARCHAR(80) NOT NULL,
                 USERNAME VARCHAR(20) PRIMARY KEY,
                 MAIL VARCHAR(80) NOT NULL UNIQUE,
                 PASSWORD VARCHAR(120) NOT NULL)

To connect the database with the login system, I have used a user class as shown below.

class User(UserMixin):
    def __init__(self, name, userName, eMail, password):

     self.name = name
     self.userName = userName
     self.email    = eMail
     self.password = password
     self.active = True
     self.is_admin = False

  def get_id(self):
     return self.userName
 @property
 def is_active(self):
     return self.active

In the signup page, a new user is created and their information is added to the USERS table, later on you can use this e-mail and password to login successfully. The query below shows it.

INSERT INTO USERS (NAME, USERNAME, MAIL, PASSWORD) VALUES (%s, %s, %s, %s)

I have used SELECT operation for the USERS table in the Mainpage to show the name of the user shown below.

SELECT * FROM USERS

After selecting the users table, in the mainpage.html we see that if current_user.userName is the same as the username in the USERS table, then print the name of the user.

{% if users %}
             {% for u in users %}

             {% if user.userName == u[1] %}
  <h1 class="title">Welcome back, {{u[0]}}</h1>
                     {% endif %}

                     {% endfor %}
                     {% endif %}

RESTAURANT TABLE¶

The RESTAURANT table stores the restaurant name and the username for the user who have added this restaurant, also the name of the restaurant has to be unique as shown in table below.

CREATE TABLE RESTAURANT (
                 ID SERIAL PRIMARY KEY,
                 NAME VARCHAR(20) NOT NULL,
                 USERNAME VARCHAR(20) REFERENCES USERS(USERNAME) ON DELETE CASCADE,
                 UNIQUE(NAME) )

I have used insertion and selection operations in the Mainpage to add a new restaurant and list it in this page as shown below.

INSERT INTO RESTAURANT(NAME, USERNAME) VALUES(%s, %s)
SELECT * FROM RESTAURANT"

Below in the mainpage.html it shows all the restaurants in the table printed the names as a link to restaurant_page

{% if names %}
             {% for name in names %}
                             <a href="{{url_for('site.restaurant_page', rst_id = name[0] )}}" class="" style= "font-size: 15px">
                             <li>{{name[1]}}</li>
                     </a>
                                     {% endfor %}
                                     {% endif %}

I have used delete operation on the restaurant_page to delete a restaurant, also select operation to print the name of the restaurant where the id for both is the id of the restaurant chosen as shown below.

DELETE FROM RESTAURANT WHERE ID = %s
SELECT * FROM RESTAURANT WHERE ID = %s

at restaurant_page.html below is the given code to print the name of the restaurant.

{% if names %}
                     {% for name in names %}
             <h1 >{{name[1]}}<br> Restaurant</h1>

      {% endfor %}
     {% endif %}

In the edit_restaurant page I have used the select operation to print the name of the restaurant as mentioned before.

RST_DETAILS TABLE¶

To have full informatoin about this table you can see it in jilan’s part, but i have used an update operation in this table to update the location and food category of the selected restaurant as shown below.

if len(location)!=0 or len(food)!=0 :
         execute=[]
         query="""UPDATE RST_DETAILS SET """
         if len(location)!=0:
             execute+=[str(location)]
             query+="""LOCATION=%s"""
         if len(food)!=0:
             execute+=[str(food)]
             if len(location)!=0:
                 query+=""", """
             query+="""CATEGORY=%s"""

         query+=""" WHERE (ID=%s)"""
         execute+=[rst_id[0]]

the code above can let you update one information or both of them.

MYFAVOURITE TABLE¶

The MYFAVOURITE table stores the username, restaurant name and restaurant id as shown below.

CREATE TABLE MYFAVORITE (
                 USERNAME VARCHAR(30) REFERENCES USERS(USERNAME) ON DELETE CASCADE,
                 RESTAURANT VARCHAR(50) REFERENCES RESTAURANT(NAME) ON DELETE CASCADE,
                 RST_ID SERIAL REFERENCES RESTAURANT(ID) ON DELETE CASCADE,
                 PRIMARY KEY(USERNAME,RESTAURANT) )

I used insert operation in the restaurant page to add a restaurant to the myfavourite table as shown below.

INSERT INTO MYFAVORITE(USERNAME, RESTAURANT,RST_ID) VALUES(%s, %s,%s)

on the favourite page I used delete and select operation to list the favourite restaurants or delete one as shown below.

SELECT * FROM MYFAVORITE
DELETE FROM MYFAVORITE WHERE (USERNAME= %s and RESTAURANT = %s)

POST TABLE¶

The POST table is for the comments and rate which stores postid, username, comment and rate as shown below.

POSTID SERIAL PRIMARY KEY,
                 USERNAME VARCHAR(20) REFERENCES USERS(USERNAME) ON DELETE CASCADE,
                 COMMENT VARCHAR(500) NOT NULL,
                 RATE VARCHAR(20) DEFAULT 1)

In the restaurant page i have used insert operation to insert the feeds to the POST table, also select POSTId from this table to add it to the POSTCAST table which i will explain later, also the last select operation is for printing all the posts for the chosen rastaurant page

INSERT INTO POST(USERNAME, COMMENT,RATE) VALUES(%s, %s,%s)
SELECT POSTID FROM POST WHERE (USERNAME = %s and COMMENT = %s)
SELECT * FROM POST WHERE POSTID= %s

In the edit_comment page i have used delete and select operation to delete a comment and to print it in screen as shown below.

DELETE FROM POST WHERE (POSTID= %s)
SELECT * FROM POST WHERE POSTID = %s

POSTCAST TABLE¶

The POSTCAST table is cast for POST and RESTAURANT tables which stores id, restaurant id and post id as shown below.

 CREATE TABLE POSTCAST (
                   ID SERIAL PRIMARY KEY,
                   RSTID INTEGER REFERENCES RESTAURANT(ID) ON DELETE CASCADE,
                   POSTID INTEGER REFERENCES POST(POSTID) ON DELETE CASCADE )

In the **restaurant page**  I have used the insert operation also delete operation after a restaurant is deleted the POSTCAST with the same restaurant is also deleted, also an importent select operation which will select the post id that have the restaurant id of the chosen restaurant to print all the posts of this restaurant in this page as shown below.

INSERT INTO POSTCAST(RSTID, POSTID) VALUES(%s, %s)
DELETE FROM POSTCAST WHERE RSTID = %s
SELECT POSTID FROM POSTCAST WHERE RSTID = %s

FRIENDS table¶

FRIENDS table adds/deletes a friend to the users profile. the USERNAME & FRIEND are primary key because each user is kept well distinguished in the process of deleting or adding a friend to the table. both USERNAME & FRIENDS have a maximum of 50 character and this table is connected to the USERS table.

CREATE TABLE FRIENDS
USERNAME VARCHAR(30) REFERENCES USERS(USERNAME) ON DELETE CASCADE,
FRIEND VARCHAR(50) REFERENCES USERS(USERNAME) ON DELETE CASCADE,
PRIMARY KEY(USERNAME,FRIEND) )

if the user choose to update one of his operations the below code will handle this task, he can only do one update at a time as it can be seen below

friend=request.form['friend']
if request.form['action'] == 'add':
query = """INSERT INTO FRIENDS(USERNAME, FRIEND) VALUES(%s, %s)"""
cursor.execute(query,(current_user.userName, friend))
connection.commit()

elif request.form['action'] == 'remove':
query = """DELETE FROM FRIENDS WHERE (USERNAME= %s and FRIEND = %s)"""
cursor.execute(query,(current_user.userName, friend))
connection.commit()
return redirect(url_for('profile_page'))

the friend that the user typed in, is added to the FRIENDS table and into the users profile as seen below.

if request.form['action'] == 'add':
query = """INSERT INTO FRIENDS(USERNAME, FRIEND) VALUES(%s, %s)"""
cursor.execute(query,(current_user.userName, friend))
connection.commit()

the friend that the user typed in, is deleted from the FRIENDS table and from the users profile as seen below.

elif request.form['action'] == 'remove':
query = """DELETE FROM FRIENDS WHERE (USERNAME= %s and FRIEND = %s)"""
cursor.execute(query,(current_user.userName, friend))
connection.commit()
return redirect(url_for('profile_page'))

INFO table¶

INFO table stores all user’s information. the table has different necessary varriables like the ‘USERNAME’, it is both the primary and foreign key in the table and it refers to the USERS table primary key, and it has a maximum of 50 characters, other varriable is ‘AGE’ which has a maximum of 20 characters, the ‘COUNTRY’ & ‘city’ has a maximum of 100 characters, the ‘GENDER’ which has a maximum of 50 characters the name is a maximum of 80 characters

CREATE TABLE INFO (
USERNAME VARCHAR (50) REFERENCES USERS(USERNAME) ON DELETE CASCADE,
NAME VARCHAR(80) NULL,
AGE VARCHAR(20) NULL,
COUNTRY VARCHAR(100)NULL,
CITY VARCHAR(100) NULL,
GENDER VARCHAR(50) NULL,
PRIMARY KEY(USERNAME))
cursor.execute(query)

the bellow data is inserted in the table while initializing database.

query = """INSERT INTO INFO (USERNAME, SURNAME,AGE,COUNTRY,CITY,GENDER) VALUES ('admin123','alrehaili','30','turkey','istanbul','female')"""
   cursor.execute(query)

the initial stage of these informations are as defult values. and the user can pick to update all or some of his information only the user by his account can change these personal informations no other user can access this page.

  query = """INSERT INTO INFO (USERNAME, SURNAME, AGE, COUNTRY,CITY,GENDER)
  VALUES ('%s','%s', '%s', '%s', '%s', '%s')""" %(username,'........','........','........','........','........')
 cursor.execute(query)
 connection.commit()

 login_user(user)
return redirect(url_for('site.main_page'))

the bellow code makes a form request with the data specified in the values to access the form data.

surname=request.form['Surname']
         age=request.form['Age']
         country=request.form['Country']
         city=request.form['City']
         gender=request.form['Gender']

this instructions checks for columns that has new data to insert into the INFO table, and it handles any data that is updated.

if len(surname)!=0 or len(age)!=0 or len(country)!=0 or len(city)!=0 or len(gender)!=0:
execute=[]
query="""UPDATE INFO SET """
if len(surname)!=0:
execute+=[str(surname)]
query+="""SURNAME=%s"""
if len(age)!=0:
execute+=[str(age)]
if len(surname)!=0:
query+=""", """
query+="""AGE=%s"""
if len(country)!=0:
execute+=[str(country)]
if len(surname)!=0 or len(age)!=0:
query+=""", """
query+="""COUNTRY=%s"""
if len(city)!=0:
execute+=[str(city)]
if len(surname)!=0 or len(age)!=0 or len(country)!=0:
query+=""", """
query+="""CITY=%s"""
if len(gender)!=0:
execute+=[str(gender)]
if len(surname)!=0 or len(age)!=0 or len(country)!=0 or len(city)!=0:
query+=""", """
query+="""GENDER=%s"""

RST_DETAILS table¶

RESTURANT table stores the names of the resturants and the usernames of who added the resturant, the ‘ID’ in the table is the primary key with a serial number, and the ‘USERNAME’ is the foreign key with a maximum of 20 characters, and the last one is the ‘NAME’ with a ‘20’ characters as shown bellow. this table is connected to the RESTURANTS table which is connected to USERS table.

CREATE TABLE RST_DETAILS (
               ID SERIAL REFERENCES RESTAURANT(ID) ON DELETE CASCADE,
               NAME VARCHAR(20) REFERENCES RESTAURANT(NAME) ON DELETE CASCADE,
               LOCATION VARCHAR(20) NULL,
               CATEGORY VARCHAR(20) NULL,
               PRIMARY KEY(ID) )

the bellow data is inserted in the table while initializing database.

query = """INSERT INTO RST_DETAILS ( NAME, LOCATION,CATEGORY ) VALUES ('Burger king', 'Levent','Fast food')"""
   cursor.execute(query)
   query = """INSERT INTO RST_DETAILS ( NAME, LOCATION,CATEGORY ) VALUES ('Mado', 'Taksim','Turkish food')"""
   cursor.execute(query)

the nearby page uses the RST_DETAILS table to fetch all the information needed in this operation. the ‘LOCATION’ attribute of the RST_DETAILS table is used to distinguish the location of each resturant.

 def nearby_page():
if request.method == 'POST':
    with dbapi2.connect(app.config['dsn']) as connection:
        cursor = connection.cursor()
        location = request.form['Location']
        query = """SELECT * FROM RST_DETAILS WHERE LOCATION = %s"""
        cursor.execute(query,[location])
        names = cursor.fetchall()
        connection.commit()
    return render_template("nearby.html",names=names)
else:
    return render_template("nearby.html")