# Demo 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: ```python 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. ```python 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: ```python 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: ```python 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. ```python @app.route("/task//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"]`). ```python @app.route("/task//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)`. --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://backend-course.cornellappdev.com/chapters/relational-databases/demo.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.