Demo

Contributors: Alicia Wang, Conner Swenberg, Alanna Zhou

We will be starting where we left off in our last demo. Previously, we had defined a task table within our DB driver class and some public methods for interacting with the database. We will be demonstrating a one-to-many relationship by adding functionality for subtasks to belong under a parent task.

Add Subtasks to Database

Define Subtask Table

One benefit of our relational modeling is that we can define a new table that relates to task without needing to edit our original table. We can define a new method to create our subtask table as follows:

def create_subtask_table(self):
    try:
        self.conn.execute("""
            CREATE TABLE subtask (
                ID INTEGER PRIMARY KEY,
                DESCRIPTION TEXT NOT NULL,
                DONE BOOL NOT NULL,
                TASK_ID INTEGER NOT NULL,
                FOREIGN KEY(TASK_ID) REFERENCES task(ID)
            );
        """)
    except Exception as e:
        print(e)

Our subtasks will contain basic fields like description and done like our parent tasks, but what makes this table special is the addition of a foreign key. As described in lecture, we can define a new column to store the id of a task and then add this FOREIGN KEY constraint. To translate line 9 into plain english, we would say that we are defining our field TASK_ID as a FOREIGN KEY that REFERENCES the task table's ID column. To reiterate: the ID at the end of line 9 does not refer to the ID column specified in our new subtask table, it refers to the ID column in the task table. Just like before, we will wrap this execution within a try-except so that we can prevent ourselves from crashing if the subtask table already exists.

Update Database Initialization

Now we have defined a method to create our new table, we want to call this upon initializing our database driver.

def __init__(self):
    self.conn = sqlite3.connect("todo.db", check_same_thread=False)
    self.conn.execute("PRAGMA foreign_keys = 1")
    self.create_task_table()
    self.create_subtask_table()

Also notice that we have added a new SQL execution line with an unfamiliar looking command PRAGMA. Thinking back to our intentions with defining the foreign key relationship, our goal is to define the structure and rules for storing information in our database. We could have easily been fine just defining our TASK_ID column in the subtask table and maintain the same functionality. However, because we understand the implications of this column (i.e. that it represents a relationship between real entities), we want to enforce some restrictions on the field. In essence, our PRAGMA command execution on line 3 is "activating" the imposed restrictions of having a foreign key reference. Also note that we set foreign_keys = 1 regardless of the actual number of foreign keys; You can think of 1 as symbolically representing True.

By default, our FOREIGN KEY definition will not enforce restrictions on creating subtasks without a parent task, for example, so this line will tell SQLite to alert us of these issues as they arise.

Add a Subtask

For inserting new subtasks into our database, we will design a method do take in description, done, and task_id arguments and execute the typical INSERT SQL command:

def insert_subtask(self, description, done, task_id):
    cursor = self.conn.cusor()
    cursor.execute(
        "INSERT INTO subtask 
        (DESCRIPTION, DONE, TASK_ID) 
        VALUES (?, ?, ?)", 
        (description, done, task_id)
    )
    self.conn.commit()
    return cursor.lastrowid

This is implementation is very similar to our insert_task method from the last demo and don't forget to commit our changes and return the id of this newly added subtask.

Get Subtasks of a Task

To get the subtasks of a parent task, all we need is to run a SQL query on the criteria of matching task_id values:

def get_subtasks_of_task(self, task_id):
    cursor = self.conn.execute(
        "SELECT * FROM subtask WHERE TASK_ID = ?", (task_id,)
    )
    subtasks = []

    for row in cursor:
        subtasks.append({"id": row[0], "description": row[1], "done": bool(row[2])})

    return subtasks

This implementation follows analogously from querying our regular tasks; all that has changed is our new WHERE statement to only take subtasks belonging to a particular parent.

Add Subtasks Routes

Create a Subtask

To create a new subtask, we need to know which parent task to attach it to within the request. We will extend our typical task/id/ route path to gain this information and append a subtasks/ to make this path unique. Our accepted request method needs to be POST because we are expecting data to come in the body of the request.

@app.route("/task/<int:task_id>/subtasks/", methods=["POST"])
def create_subtask(task_id):
    body = json.loads(request.data)
    description = body["description"]

    try:
        subtask = {
            "id": Db.insert_subtask(description, False, task_id),
            "description": description,
            "done": False,
            "task_id": task_id
        }
        return json.dumps({"success": True, "data": subtask})
    except sqlite3.IntegrityError:
        return json.dumps({"success": False, "error": "Task not found!'}), 404

To create a new subtask, we simply call our custom method Db.insert_subtask and provide our desired attributes attributes (defaulting done -> False and task_id -> task_id). Just like with creating new tasks, we will nest this call within constructing a new subtask dictionary so that we can populate the "id" field. Finally, by nesting this subtask construction in a try-except, we are accounting for the case where we try to add a subtask to a task that does not exist with the given task_id. As noted in the except statement, we are expecting a sqlite3.IntegrityError if this case does arise.

Get Subtasks of a Task

To retrieve the subtasks of a task, we can utilize the same path as the previous method, only now allowing GET methods (recall that the methods argument is optional and defaults to ["GET"]).

@app.route("/task/<int:task_id>/subtasks/")
def get_subtasks_of_task(task_id):
    res = {"subtasks": Db.get_subtasks_of_task(task_id)}
    return json.dumps(res), 200

Because we have abstracted all interactions with the database in db.py, we simply need to call our method Db.get_subtasks_of_task(task_id).