Welcome to Dball’s documentation!¶

API can both pull and push information to the application. To pull information, users should use specific routes that has been designed for that record. Users can either pull information for specific ID or they can pull all the records that has been stored in database. All responses will be in JSON format. GET routes are only allows GET method. Thus if it encounters with a POST request it would give a 405 error.

$ curl http://localhost:5000/api/<record_name>/<id>

Example request for GET operation.

To complete add operation through API, user must be logged in. In other words, it should have a session in computer. This prevents unauthorized users to alter records. After login operation user can add using /api/<record_name>/add route to add new record to the system. It only accepts POST method.

$ curl -X POST -d "{...}" http://localhost:5000/api/<record_name>/add

Example request for ADD operation.

Again to complete update operation user should be logged in. After logged in, user can use /api/<record_name>/update route to update records that have been stored in database. It only accepts POST method.

$ curl -X POST -d "{...}" http://localhost:5000/api/<record_name>/update

Example request for UPDATE operation.

After login operation user can delete records on database from the route /api/<record_name>/delete. It only accepts POST method.

$ curl -X POST -d "{...}" http://localhost:5000/api/<record_name>/delete

Example request for DELETE operation.

User login is secure and critical process for users to alter records that have been stored in database. Since API is open, we had to require users to login before done any operation on records to prevent data persistence. When user tries to login through user interface data which user entered, gathered by AJAX and formatted into JSON notation. From here AJAX handler generates a request to the API. API gets JSON-formatted data and creates a respond again in JSON format. According to respond message AJAX handler either generates an error message or reloads the window.

def api_user_login():
# Get request header #
json_user_data = request.get_json()

# Get user object #
user_info = user.User()
user_info.get_user(json_user_data['user_email'])

# Check user credentials #
if user_info is not None and user_info.password_hash is not None:
    if bcrypt.verify(json_user_data['user_password'],
       user_info.password_hash) is True:
        # Create session for user #
        session['logged_in'] = True
        session['email'] = json_user_data['user_email']
        session['alias'] = user_info.alias

        status = True
    else:
        status = False
else:
    status = False

return jsonify({'result': status})

API method for user login.

API is heavily dependent on User class which has multiple methods for completing database operations. API method first creates an User class object. Then it gets data from database and compares entered password with stored salt. If they match it returns success message, otherwise error message.

$ curl -X POST -d '{"user_email":"test@test.com", "user_password":"ali"}' http://localhost:5000/api/login

Example request for user login operation through.

User registration has been only implemented in API level. From user interface there is not possible to register a new user. When user creates and sends a request to API path, API generates a new User class object. Then it invokes add_user_to_db() method to store record in database. Before it stores data to database, it encrypts user password with bcrypt key derivation function to increase security.

def api_user_register():
    # Get request header #
    json_user_info = request.json

    # Convert it into user #
    user_info = user.User(
        user_alias=json_user_info['alias'],
        user_email=json_user_info['user_email'],
        user_pass=json_user_info['user_password']
    )

    # Add user to database #
    status = user_info.add_user_to_db()

    return jsonify({'result': status})

API method for user register.

"""INSERT INTO users (user_name, password_hash, user_email, is_admin)
        VALUES (%s, %s, %s, %s);"""

SQL Query used to store user information to database.

$ curl -X POST -d '{"alias":"tester", "user_name":"test", "user_password":"ali"}'
        http://localhost:5000/api/register

Example request for user register operation through.

Because of foreign keys, when getting person information JOIN SQL operation has been used. Tables has been joined where their keys has been intersect and data derived according to resulted table.

# Get person type #
type_obj = people.PersonType()
type_obj.get_person_type(type_id)
# Create a dict #
data = {
    'id': type_obj.id,
    'type': type_obj.type
}

return jsonify(data)

API method for get operation

"""SELECT * FROM person
            JOIN city ON city.city_id = person.person_birth_location
            JOIN person_types ON person_types.id = person.person_type
            WHERE person_id = %s"""

SQL query used for get operation.

Since person table has two foreign keys, thus before saving record into database it should have take foreign ids from city_id attribute from City table and id attribute from person type table. After it got the city_id and id it can store data to database. It uses name attribute for both foreign keys as search point because it is unique.

def api_add_person():
    # Prevent unauthorized access from API #
    if not session.get('logged_in'):
        return jsonify({"result": "Unauthorized Access. Please identify yourself"})

    # Get json request from AJAX Handler #
    json_post_data = request.get_json()
    # print(json_post_data)
    # Create an person object #
    person_info = people.Person(json_post_data['person_name'], json_post_data['person_birth_date'],
                                json_post_data['person_birth_place'], json_post_data['person_type'])

    # Add it to db and send result #
    result = person_info.add_to_db()

    if result:
        description = "Added " + json_post_data['person_name'] + " to Persons"
        log_info = log.Log(description, session['alias'], datetime.datetime.now())
        log_status = log_info.add_to_db()

    return jsonify({'result': result})

API method for person add operation.

"""SELECT id FROM person_types WHERE person_type_name = %s"""
"""SELECT city_id FROM city WHERE city_name = %s"""
"""INSERT INTO person(person_name, person_birth_date, person_birth_location, person_type)
                    VALUES (%s, %s, %s, %s)"""

SQL Queries used to store information to database.

Update operation is rather similar to add operation. After data passes from AJAX handler, API invokes update_db() method.

def api_update_person():
    # Get request from AJAX #
    json_data = request.get_json()
    # Get person from db #
    person_obj = people.Person()
    person_obj.get_person_by_id(json_data['person_id'])

    # Update person object's values #
    person_obj.name = json_data['person_name']
    person_obj.birth_date = json_data['person_birth_date']
    person_obj.birth_place = json_data['person_birth_place']
    person_obj.type = json_data['person_type']

    # Update db #
    result = person_obj.update_db()

    # Log operations #

    return jsonify({'result': result})

API method for person update operation.

"""SELECT city_id FROM city WHERE city_name=%s"""
"""SELECT id FROM person_types WHERE person_type_name=%s"""
"""UPDATE person
           SET person_name=%s, person_birth_date=%s, person_birth_location=%s, person_type=%s
           WHERE person_id=%s"""

SQL Queries used to update stored information on database.

Delete operation is relatively simple when comparing the other operations. API gets a list of ids that wanted to be deleted from request and just invokes delete_from_db() method for each.

def api_delete_person():
    # Prevent unauthorized access #
    if not session.get('logged_in'):
        return jsonify({"result": "Unauthorized Access. Please identify yourself"})

    status = False
    # Get request #
    person_id_json = request.get_json()
    # print(person_id_json)
    # Delete every requested id #
    for person_id in person_id_json:
        person_obj = people.Person()
        person_obj.get_person_by_id(person_id)
        # print(person_id)
        status = person_obj.delete_from_db()

        if status:
            description = "Deleted " + person_obj.name + " from Persons"
            log_info = log.Log(description, session['alias'], datetime.datetime.now())
            log_status = log_info.add_to_db()

    return jsonify({'result': status})

API method for person delete operation.

"""DELETE FROM person WHERE person_id = %s"""

SQL Query used to delete stored information from database.

Again JOIN operation has been used for getting all data in same manner as people table.

def api_get_penalty(data_id):
    # Create empty penalty and fill it from db #
    penalty_obj = penalties.Penalty()
    penalty_obj.get_penalty_by_id(data_id)

    # Create a dict for jsonify #
    data = {
        'id': penalty_obj.id,
        'person': penalty_obj.person,
        'given_date': penalty_obj.given_date.strftime('%d/%m/%Y'),
        'penalty_type': penalty_obj.type
    }

    return jsonify(data)

API method for get operation

"""SELECT * FROM penalty
            JOIN person ON penalty_given_person = person.person_id
            JOIN penalty_type ON penalty_type = penalty_type.id
            WHERE penalty_id = %s"""

SQL query used for get operation.

Add operation also in same way as person table. But differently, this time it takes person id directly from user, thus no additional query is needed for penalty add operation.

def api_add_penalty():
    # Prevent unauthorized access from API #
    if not session.get('logged_in'):
        return jsonify({"result": "Unauthorized Access. Please identify yourself"})

    # Get json request from AJAX Handler #
    json_post_data = request.get_json()
    # print(json_post_data)
    # Create an penalty object #
    penalty_info = penalties.Penalty(json_post_data['person_name'], json_post_data['penalty_given_date'],
                                     json_post_data['penalty_type'])

    # Add it to db and send result #
    result = penalty_info.add_to_db()

    if result:
        log_person = people.Person().get_person_by_id(json_post_data['person_name'])
        description = "Added Penalty For " + log_person.name + " to Penalties"
        log_info = log.Log(description, session['alias'], datetime.datetime.now())
        log_status = log_info.add_to_db()

    return jsonify({'result': result})

API method for add operation.

"""SELECT id FROM penalty_type WHERE penalty_type_name = %s"""
 """INSERT INTO penalty(penalty_type, penalty_given_person, penalty_given_date)
           VALUES (%s, %s, %s)"""

SQL Queries used to store information to database.

Again it is similar to add operation when updating record.

def api_update_penalty():
     # Get request from AJAX #
     json_data = request.get_json()
     # Get penalty from db #
     penalty_obj = penalties.Penalty()
     penalty_obj.get_penalty_by_id(json_data['penalty_id'])

     # Update penalty object's values #
     penalty_obj.person = json_data['person_name']
     penalty_obj.given_date = json_data['penalty_given_date']
     penalty_obj.type = json_data['penalty_type']

     # Update db #
     result = penalty_obj.update_db()

     if result:
         description = "Updated Element With id=" + json_data['penalty_id'] + " in Penalties"
         log_info = log.Log(description, session['alias'], datetime.datetime.now())
         log_status = log_info.add_to_db()

     return jsonify({'result': result})

API method for update operation.

"""SELECT id FROM penalty_type WHERE penalty_type_name=%s"""
"""UPDATE penalty
           SET penalty_given_date=%s, penalty_given_person=%s, penalty_type=%s
           WHERE penalty_id=%s"""

SQL Queries used to update stored information on database.

As it was in person table, API invokes delete_from_db() method to delete given ids.

def api_delete_penalty():
    # Prevent unauthorized access #
    if not session.get('logged_in'):
        return jsonify({"result": "Unauthorized Access. Please identify yourself"})

    status = False
    # Get request #
    penalty_id_json = request.get_json()
    # Delete every requested id #
    for penalty_id in penalty_id_json:
        penalty_obj = penalties.Penalty()
        penalty_obj.get_penalty_by_id(penalty_id)
        # print(penalty_id)
        status = penalty_obj.delete_from_db()

        if status:
            description = "Deleted Penalty For " + penalty_obj.person + " from Penalties"
            log_info = log.Log(description, session['alias'], datetime.datetime.now())
            log_status = log_info.add_to_db()

    return jsonify({'result': status})

API method for delete operation.

"""DELETE FROM penalty WHERE penalty_id = %s"""

SQL Query used to delete stored information from database.

Again JOIN operation has been used for getting all data in same manner as people table. But this time it as more joins.

def api_get_popularity(data_id):
    # Create empty popularity and fill it from db #
    popularity_obj = popularity.Popularity()
    popularity_obj.get_popularity_by_id(data_id)

    # Create a dict for jsonify #
    data = {
        'id': popularity_obj.id,
        'team': popularity_obj.team,
        'match': popularity_obj.match,
        'player': popularity_obj.player,
        'supporters': popularity_obj.supporters
    }

    return jsonify(data)

API method for get operation

"""SELECT * FROM popularity
            JOIN team AS team1 ON popularity.team_name = team1.team_id
            JOIN matches ON popularity.most_popular_match = matches.match_id
            JOIN team AS team2 ON matches.match_team_1 = team2.team_id
            JOIN team AS team3 ON matches.match_team_2 = team3.team_id
            JOIN person ON popularity.most_popular_player = person.person_id"""

SQL query used for get operation.

In order to display multiple teams there has been multiple joins on teams used.

Add operation takes foreign key values directly from the user in order to optimize queries.

def api_add_popularity():
    # Prevent unauthorized access from API #
    if not session.get('logged_in'):
        return jsonify({"result": "Unauthorized Access. Please identify yourself"})

    # Get json request from AJAX Handler #
    json_post_data = request.get_json()
    # print(json_post_data)
    # Create an popularity object #
    popularity_info = popularity.Popularity(json_post_data['team'], json_post_data['match'],
                                            json_post_data['player'], json_post_data['supporters'])

    # Add it to db and send result #
    result = popularity_info.add_to_db()

    if result:
        description = "Added Popularity Info for " + json_post_data['team'] + " to Popularity"
        log_info = log.Log(description, session['alias'], datetime.datetime.now())
        log_status = log_info.add_to_db()

    return jsonify({'result': result})

API method for add operation.

"""INSERT INTO popularity(team_name, most_popular_match, most_popular_player, supporters)
                     VALUES (%s, %s, %s, %s)"""

SQL Queries used to store information to database.

Again it is similar to add operation when updating record.

def api_update_popularity():
     # Get request from AJAX #
     json_data = request.get_json()
     # Get person from db #
     popularity_obj = popularity.Popularity()
     popularity_obj.get_popularity_by_id(json_data['popularity_id'])

     # Update person object's values #
     popularity_obj.team = json_data['team']
     popularity_obj.match = json_data['match']
     popularity_obj.player = json_data['player']
     popularity_obj.supporters = json_data['supporters']

     # Update db #
     result = popularity_obj.update_db()

     if result:
         description = "Updated Element With id=" + json_data['popularity_id'] + " in Popularity"
         log_info = log.Log(description, session['alias'], datetime.datetime.now())
         log_status = log_info.add_to_db()

     return jsonify({'result': result})

API method for update operation.

"""UPDATE popularity
           SET team_name=%s, most_popular_match=%s, most_popular_player=%s, supporters=%s
           WHERE popularity_id=%s"""

SQL Query used to update stored information on database.

As it was in person table, API invokes delete_from_db() method to delete given ids.

def api_delete_popularity():
    # Prevent unauthorized access #
    if not session.get('logged_in'):
        return jsonify({"result": "Unauthorized Access. Please identify yourself"})

    status = False
    # Get request #
    popularity_id_json = request.get_json()
    # Delete every requested id #
    for popularity_id in popularity_id_json:
        popularity_obj = popularity.Popularity()
        popularity_obj.get_popularity_by_id(popularity_id)
        # print(penalty_id)
        status = popularity_obj.delete_from_db()

        if status:
            description = "Deleted Popularity Info For " + popularity_obj.team + " from Penalties"
            log_info = log.Log(description, session['alias'], datetime.datetime.now())
            log_status = log_info.add_to_db()

    return jsonify({'result': status})

API method for delete operation.

"""DELETE FROM popularity WHERE popularity_id = %s"""

SQL Query used to delete stored information from database.

Get operation is simple for city table. There is no joins since it does not have any foreign key.

def api_get_city(city_id):
    # Create empty city and fill it from db #
    city_obj = cities.City()
    city_obj.get_city_by_id(city_id)

    # Create a dict for jsonify #
    data = {
        'id': city_obj.id,
        'city_name': city_obj.name,
        'city_coordinates': city_obj.name,
        'city_population': city_obj.name
    }

    return jsonify(data)

API method for get operation

"""SELECT * FROM city WHERE city_id = %s"""

SQL Queries used for get operation

Add operation get city nme and population as input, then sends city name to Maps API and gets geolocation to store.

def api_add_city():
    # Prevent unauthorized access #
    if not session.get('logged_in'):
        return jsonify({"result": "Unauthorized Access. Please identify yourself"})

    # Get request #
    json_post_data = request.get_json()
    # print(json_post_data)

    city_info = cities.City(json_post_data['city_name'],
                            json_post_data['city_population'])
    # Add it to db #
    result = city_info.add_to_db()

    if result:
        description = "Added " + json_post_data['city_name'] + " to Cities"
        log_info = log.Log(description, session['alias'], datetime.datetime.now())
        log_status = log_info.add_to_db()

    return jsonify({'result': result})

API method for add operation

"""INSERT INTO city (city_name, city_population, city_coordinates)
                VALUES (%s, %s, %s)"""

SQL Queries used for add operation

Again update operation also does same thing as ha been done in add operation.

def api_update_city():
    # Get request from AJAX #
    json_data = request.get_json()
    # Get city from db #
    city_obj = cities.City()
    city_obj.get_city_by_id(json_data['city_id'])

    # Update city object's values #
    city_obj.name = json_data['city_name']
    city_obj.population = json_data['city_population']

    # Update db #
    result = city_obj.update_db()

    if result:
        description = "Updated Element With id=" + json_data['city_id'] + " in Cities"
        log_info = log.Log(description, session['alias'], datetime.datetime.now())
        log_status = log_info.add_to_db()

    return jsonify({'result': result})

API method for update operation

"""UPDATE city
           SET city_name=%s, city_population=%s, city_coordinates=%s
           WHERE city_id=%s"""

SQL Queries used for update operation

Delete operation directly deletes data from database.

def api_delete_city():
     # Prevent unauthorized access #
     if not session.get('logged_in'):
         return jsonify({"result": "Unauthorized Access. Please identify yourself"})

     status = False

     # Get request #
     city_id_json = request.get_json()

     for city_id in city_id_json:
         city_obj = cities.City()
         city_obj.get_city_by_id(city_id)
         status = city_obj.delete_from_db()

         if status:
             description = "Deleted " + city_obj.name + " from Cities"
             log_info = log.Log(description, session['alias'], datetime.datetime.now())
             log_status = log_info.add_to_db()

     return jsonify({'result': status})

API method for delete operation

"""DELETE FROM city WHERE city_id = %s"""

SQL Queries used for delete operation

This operation is the most essential one as it’s used for several key actions. Either by sending an id to get a specific tuple or to get the whole table get_sponsorshop_by_id method is called by the API. Then the SELECT queries found below, will be called with the only difference of WHERE sponsorship_id = %s which indicates that a unique id is specified. Three different OUTER JOIN operations are made to get the league, team and person names by joining these tables over their ids.

@app.route('/api/sponsorship/<int:data_id>', methods=['GET'])
def api_get_sponsorship(data_id):
    # Create empty sponsorship and fill it from db #
    sponsorship_obj = sponsorships.Sponsorship()
    sponsorship_obj.get_sponsorship_by_id(data_id)

    # Create a dict for jsonify #
    data = {
        'id': sponsorship_obj.id,
        'name': sponsorship_obj.name,
        'start_date': sponsorship_obj.start_date.strftime('%d/%m/%Y'),
        'league': sponsorship_obj.league,
        'team': sponsorship_obj.team,
        'person': sponsorship_obj.person
    }

    return jsonify(data)

def get_sponsorship_by_id(self, get_id=None):
    connection = db_connect()
    cursor = connection.cursor()

    if get_id is not None:
        statement = """SELECT sponsorship.sponsorship_id, sponsorship.sponsorship_name, sponsorship.sponsorship_start_date,
                        sponsorship.sponsorship_league, sponsorship.sponsorship_team, sponsorship.sponsorship_person,
                        person.person_name FROM sponsorship
                        LEFT OUTER JOIN league ON league.league_id = sponsorship.sponsorship_league
                        LEFT OUTER JOIN team ON team.team_id = sponsorship.sponsorship_team
                        LEFT OUTER JOIN person ON person.person_id = sponsorship.sponsorship_person
                        WHERE sponsorship_id = %s"""
        try:
            cursor.execute(statement, (get_id,))
            connection.commit()
        except connection.Error:
            connection.rollback()

        data = cursor.fetchone()
        if data is not None:
            self.id = data[0]
            self.name = data[1]
            self.start_date = data[2]
            self.league = data[3]
            self.team = data[4]
            self.person = data[5]
            cursor.close()
            connection.close()
            return self
        else:
            cursor.close()
            connection.close()
            return None

    else:
        statement = """SELECT sponsorship.sponsorship_id, sponsorship.sponsorship_name,
                        sponsorship.sponsorship_start_date, sponsorship.sponsorship_league,
                        sponsorship.sponsorship_team, sponsorship.sponsorship_person,
                        league.league_id, league.league_name,
                        team.team_id, team.team_name,
                        person.person_id, person.person_name FROM sponsorship
                        LEFT OUTER JOIN league ON league.league_id = sponsorship.sponsorship_league
                        LEFT OUTER JOIN team ON team.team_id = sponsorship.sponsorship_team
                        LEFT OUTER JOIN person ON person.person_id = sponsorship.sponsorship_person"""
        try:
            cursor.execute(statement, (get_id,))
            connection.commit()
        except connection.Error:
            connection.rollback()

        sponsorship_array = []
        data = cursor.fetchall()
        for sponsorship in data:
            sponsorship_array.append(
                {
                    'id': sponsorship[0],
                    'name': sponsorship[1],
                    'start_date': sponsorship[2].strftime('%d/%m/%Y'),
                    'league': sponsorship[7],
                    'team': sponsorship[9],
                    'person': sponsorship[11]
                }
            )

        cursor.close()
        connection.close()
        return sponsorship_array

After the forms on the modal for adding sponsorship are submitted, first the authorization process is made for the user by the API. If the authorization is successful, the API gets the json request from the AJAX handler. This data is then used to create a sponsorship object by calling the sponsorship constructor. Then add_to_db function is called on this object to perform the insertion query for sponsorship that can be found below. Note that the INSERT query is called by using foreign keys to league, team and person tables ids. Thus their ids should be fetched by using provided names.

@app.route('/api/sponsorship/add', methods=['POST'])
def api_add_sponsorship():
    # Prevent unauthorized access from API #
    if not session.get('logged_in'):
        return jsonify({"result": "Unauthorized Access. Please identify yourself"})

    # Get json request from AJAX Handler #
    json_post_data = request.get_json()
    # print(json_post_data)
    # Create a sponsor object #
    sponsorship_info = sponsorships.Sponsorship(json_post_data['sponsorship_name'],
                                                json_post_data['sponsorship_start_date'],
                                                json_post_data['sponsorship_league'],
                                                json_post_data['sponsorship_team'],
                                                json_post_data['sponsorship_person'])

    # Add it to db and send result #
    result = sponsorship_info.add_to_db()

    if result:
        description = "Added " + json_post_data['sponsorship_name'] + " to Sponsorships"
        log_info = log.Log(description, session['alias'], datetime.datetime.now())
        log_status = log_info.add_to_db()

    return jsonify({'result': result})

def add_to_db(self):
    connection = db_connect()
    cursor = connection.cursor()

    new_league = None
    new_team = None
    new_person = None

    select_league = """SELECT league_id FROM league WHERE league_name = %s"""
    select_team = """SELECT team_id FROM team WHERE team_name = %s"""
    select_person = """SELECT person_id FROM person WHERE person_name = %s"""

    statement = """INSERT INTO sponsorship (sponsorship_name, sponsorship_start_date,
                    sponsorship_league, sponsorship_team, sponsorship_person )
                    VALUES (%s, %s, %s, %s, %s)"""
    try:
        cursor.execute(select_league, (self.league,))
        connection.commit()
        new_league = cursor.fetchone()

        cursor.execute(select_team, (self.team,))
        connection.commit()
        new_team = cursor.fetchone()

        cursor.execute(select_person, (self.person,))
        connection.commit()
        new_person = cursor.fetchone()

        cursor.execute(statement, (self.name, self.start_date, new_league, new_team, new_person))
        connection.commit()
        status = True
    except connection.Error:
        connection.rollback()
        status = False

    cursor.close()
    connection.close()
    return status

Delete operation is a single DELETE query. delete_from_db function is called after the id of the selected rows’ data is fetched and corresponding objects are found.

@app.route('/api/sponsorship/delete', methods=['POST'])
def api_delete_sponsorship():
    # Prevent unauthorized access #
    if not session.get('logged_in'):
        return jsonify({"result": "Unauthorized Access. Please identify yourself"})

    status = False
    # Get request #
    sponsorship_id_json = request.get_json()
    # print(sponsorship_id_json)
    # Delete every requested id #
    for sponsorship_id in sponsorship_id_json:
        sponsorship_obj = sponsorships.Sponsorship()
        sponsorship_obj.get_sponsorship_by_id(sponsorship_id)
        status = sponsorship_obj.delete_from_db()

        if status:
            description = "Deleted " + sponsorship_obj.name + " from Sponsorships"
            log_info = log.Log(description, session['alias'], datetime.datetime.now())
            log_status = log_info.add_to_db()

    return jsonify({'result': status})

def delete_from_db(self):
    connection = db_connect()
    cursor = connection.cursor()

    statement = """DELETE FROM sponsorship WHERE sponsorship_id = %s"""

    try:
        cursor.execute(statement, (self.id,))
        connection.commit()
        status = True
    except connection.Error:
        connection.rollback()
        status = False

    cursor.close()
    connection.close()
    return status

Update operation works similar to the add operation except the fact that there is existing data. The AJAX handler provides the data to the API which assigns them to corresponding data members. Finally the UPDATE query is executed to apply the changes to the database

@app.route('/api/sponsorship/update', methods=['POST'])
def api_update_sponsorship():
    # Get request from AJAX #
    json_data = request.get_json()
    # Get sponsorship from db #
    sponsorship_obj = sponsorships.Sponsorship()
    sponsorship_obj.get_sponsorship_by_id(json_data['sponsorship_id'])

    # Update sponsorship object's values #
    sponsorship_obj.name = json_data['sponsorship_name']
    sponsorship_obj.start_date = json_data['sponsorship_start_date']
    sponsorship_obj.league = json_data['sponsorship_league']
    sponsorship_obj.team = json_data['sponsorship_team']
    sponsorship_obj.person = json_data['sponsorship_person']

    # Update db #
    result = sponsorship_obj.update_db()

    if result:
        description = "Updated element with id=" + json_data['sponsorship_id'] + " in Sponsorships"
        log_info = log.Log(description, session['alias'], datetime.datetime.now())
        log_status = log_info.add_to_db()

    return jsonify({'result': result})

def update_db(self):
    connection = db_connect()
    cursor = connection.cursor()
    status = False

    new_league = None
    new_team = None
    new_person = None

    select_league = """SELECT league_id FROM league WHERE league_name = %s"""
    select_team = """SELECT team_id FROM team WHERE team_name = %s"""
    select_person = """SELECT person_id FROM person WHERE person_name = %s"""

    statement = """UPDATE sponsorship
                   SET sponsorship_name=%s, sponsorship_start_date=%s, sponsorship_league=%s,
                   sponsorship_team=%s, sponsorship_person=%s
                   WHERE sponsorship_id=%s"""

    try:
        cursor.execute(select_league, (self.league,))
        connection.commit()
        new_league = cursor.fetchone()

        cursor.execute(select_team, (self.team,))
        connection.commit()
        new_team = cursor.fetchone()

        cursor.execute(select_person, (self.person,))
        connection.commit()
        new_person = cursor.fetchone()

        cursor.execute(statement, (self.name, self.start_date, new_league, new_team, new_person, self.id))
        connection.commit()
        status = True
    except connection.Error:
        connection.rollback()
        status = False

    cursor.close()
    connection.close()
    return status

Fetching a team’s statistics is done when def get_team_stat_by_id function is called by the API which executes the following query. It’s a select query that gets the only tuple from the database for the provided id since all ids are unique.

statement = """SELECT team_stat.team_stat_id, team_stat.team_stat_name, team_stat.team_stat_run,
                team_stat.team_stat_hit, team_stat.team_stat_save, team_stat.team_stat_win,
                team_stat.team_stat_draw, team_stat.team_stat_loss
                FROM team_stat
                WHERE team_stat_id = %s"""

Adding team statistics can be cumbersome since the matches data is not structured taking total wins, draws and losses into consideration. Therefore a few SELECT queries should be executed prior to the main insertion query. Extra queries for draws and losses as well as the foreign key related queries are omitted for the sake of simplicity. The different tables are counted to get home and away wins. These values are then summed before inserted into the table. Draws and losses are calculated in the same manner. Each team’s total matches are also counted with a simple SELECT count query.

count_win1 = """SELECT COUNT(*) FROM matches
                LEFT OUTER JOIN team ON team.team_id = matches.match_team_1
                WHERE (matches.match_team1_score > matches.match_team2_score AND team.team_name = %s)
                GROUP BY team.team_name"""

count_win2 = """SELECT COUNT(*) FROM matches
                LEFT OUTER JOIN team ON team.team_id = matches.match_team_2
                WHERE (matches.match_team1_score < matches.match_team2_score AND team.team_name = %s)
                GROUP BY team.team_name"""

total_wins = count_win1 + count_win2

statement = """INSERT INTO team_stat (team_stat_name, team_stat_run,
                team_stat_hit, team_stat_save, team_stat_win,
                team_stat_draw, team_stat_loss )
                VALUES (%s, %s, %s, %s, %s, %s, %s)"""

count_matches = """SELECT count(match_id) FROM matches"""

Deletion is a simple operation which is executed after getting the ids of the selected rows from the table.

statement = """DELETE FROM team_stat WHERE team_stat_id = %s"""

Update also has several extra queries like the add function which calculates total wins, draws and losses. Runs, hits and saves fields can also be updated when provided with new data.

statement = """UPDATE team_stat
               SET team_stat_name=%s, team_stat_run=%s, team_stat_hit=%s, team_stat_save=%s,
               team_stat_win=%s, team_stat_draw=%s, team_stat_loss=%s
               WHERE team_stat_id=%s"""

Fetching a team’s stadium is done when def get_stadium_by_id function is called by the API which executes the following query. It’s a select query that gets the only tuple from the database for the provided id since all ids are unique.

statement = """SELECT stadium.stadium_id, stadium.stadium_name, stadium.stadium_team,
                stadium.stadium_location, stadium.stadium_capacity,
                team.team_id, team.team_name,
                city.city_id, city.city_name
                FROM stadium
                LEFT OUTER JOIN team ON team.team_id = stadium.stadium_team
                LEFT OUTER JOIN city ON city.city_id = stadium.stadium_location
                WHERE stadium_id = %s"""

There are only two foreign keys for stadium table which are teams and locations. Id is automatically generated for each new entry therefore the rest of the fields like name and capacity should be provided by the user. After the API gets the data from the AJAX handler add_to_db function of the stadium is called which executes the following query to add the new stadium to the database.

statement = """INSERT INTO stadium (stadium_name, stadium_team,
                stadium_location, stadium_capacity )
                VALUES (%s, %s, %s, %s)"""

Deletion is just a single query which is executed after getting the ids of the selected rows from the table.

statement = """DELETE FROM stadium WHERE stadium_id = %s"""

After selecting the correct team_id for the chosen team and city_id for the chosen city name, all the inputs are passed to the UPDATE query which applies the changes to the database.

statement = """UPDATE stadium
               SET stadium_name=%s, stadium_team=%s, stadium_location=%s, stadium_capacity=%s
               WHERE stadium_id=%s"""

Team tuples have three columns; id, name and coach. Coach is a foreign key to the person table.

Player tuples have four columns; id,name, team and number of goals. Team is a foreign key to the teams table.

Tournament tuples have seven columns; id,name,number of matches,start date,end date,country and prize. Country is a foreign key to the countries table.

To be able to show logs in home screen or manager main screen we needed to add them to the database. To add log data to the database, we simply created an object and then set its properties. After an instance of object have all properties set, we simply call add_to_db() function. This function basically use insert query, the variables in query are the properties of this log instance.

Function to Add Logs to Database

In log class, one of the properties was user or author and it is a foreign key to the user table. But we were setting author or user by its name, so to get user’s id with that name we needed to run a query first to find user id.

If instance of log class have all properties set to appropriate values then the function will add log to the database.

To show all logs or some logs in the front view, we needed a function to return all log data or just a single one with given id. get_log_by_id(get_id) function simply takes an id parameter; if the id is none (or no parameter entered), the query will be executed with no specific id parameter and all logs will be returned from query and all will be stored in an array. Thne the function will just simply return that array.

Function to Retrieve All Logs

On the other hand if an id value is entered as a parameter, then the query will be executed with “WHERE id=get_id” and only the log with specific id will be returned.

Function to Retrieve A Log

Deleting a log is not implemented in front view, but is ready in class as a function. Simply we get referenced instance of log and then call delete_from_db() function.

Function to Delete a Log From the Database

Logs are instantly created when user performs an operation in the database. It is generic in all parts of operations, a description is created right after the operation and a log instance is created with this description, user and date. After that add_to_db() function on that log instance is called and log is added to the database.

Generating a Log After an Operation

As you can see right before function is completed a description is created given the operation. Simply “Added”, “Updated” or “Deleted” expressions are used for all operations. Here user is passed to the object constructor as session[‘alias’].

After we have a function to get all logs, it was too easy to select only last five of logs sorted by date. In query of selecting all logs we did already sorted logs in descended by date column. So it is now reduced only to chose first five rows returned from SELECT statement.

Choosing Last Five Changes

Only five log data are stored in array, and then array is sent to the home page. In home page we can now simply display them with a for loop.

Displaying Last Five Changes

Just like displaying last five logs, but now there is no need to use a constraint. We simply retrieve all data and store them in an array. Then send the array to manager main page as data.

Choose All Logs

Then simply display each of them by a for loop.

Displaying All Logs

All classes have same function bodies. They differ with only the queries they have. So to reduce explanation for each of them, I will show generic function bodies.

First is add_to_db() Function,

Generic Add Function

All classes share these bodies, only difference is queries. Another thing is just like in the above example some class properties are set with name values but we instead use id values for them. So first we must call another queries to get their ids. Then simply execute operational query.

update_db() Function,

Generic Update Function

delete_from_db() Function,

Generic Delete Function

get_(classname)_by_id() Function,

Generic Retrieve

Functions up to now were only class operations. Each class have four functions above. Next functions are for add, delete, and update operations done in website. These operation are again same for other classes except some extra operations for getting referenced objects.

Add Operation,

Generic Add Operation

As can be seen above, add operation creates an instance of class with json data provided by forms. After an instance is created that objects is added to the database. After a log will created for this given operation and the operation ends.

Delete Operation,

Generic Delete Operation

In delete operation we get all selected item ids in an array, then in a for loop we delete all selected items.

Update Operation,

Generic Update Operation

Just like in the add operation we get json data from forms and instead creating a new entry, we set properties of this instance to what we get from the forms and then update the item.

We have already provided bodies of all the functions. Those bodies were all same for all classes. What makes each class different are their unique queries for operations. These queries are executed in those functions and we complete what we try to accomplish.

Country Insert Into Query

In above queries, first is used to get id of the referenced capital, and then all properties of class are used as parameters to add this instance to the database.

Country Delete Query

Country with given id is deleted from the database.

Country Select Query

In case we pass no parameter to get_country_by_id() function, the query with no “WHERE” clause will be used. Above query is used when we pass an id parameter.

Country Update Query

Just like in the add operation queries excluding update query gets referenced item ids and then use them as parameter in the update query.

We have already provided bodies of all the functions. Those bodies were all same for all classes. What makes each class different are their unique queries for operations. These queries are executed in those functions and we complete what we try to accomplish.

Match Insert Into Query

In above queries, queries except the last one are used to get ids of the referenced items, and then all properties of class are used as parameters to add this instance to the database.

Match Delete Query

Match with given id is deleted from the database.

Match Select Query

In case we pass no parameter to get_match_by_id() function, the query with no “WHERE” clause will be used. Above query is used when we pass an id parameter.

Match Update Query

Just like in the add operation queries excluding update query gets referenced item ids and then use them as parameter in the update query.

We have already provided bodies of all the functions. Those bodies were all same for all classes. What makes each class different are their unique queries for operations. These queries are executed in those functions and we complete what we try to accomplish.

League Insert Into Query

In above queries, first is used to get id of the referenced country, and then all properties of class are used as parameters to add this instance to the database.

Leauge Delete Query

League with given id is deleted from the database.

League Select Query

In case we pass no parameter to get_league_by_id() function, the query with no “WHERE” clause will be used. Above query is used when we pass an id parameter.

League Update Query

Just like in the add operation queries excluding update query gets referenced item ids and then use them as parameter in the update query.