In this exercise, you and a partner will extend on the Pomodoro Timer feature you worked on in EX01 and EX02 by implementing a SQLAlchemy backend database for the timers (as opposed to the list/dict in the ProductivityService). This exercise will allow you to become more familiar with SQLAlchemy and database relationships in general.

You should complete ALL READINGS on SQLAlchemy before attempting to begin implementing this exercise.

Be sure to scan the full write up (especially the technical requirements) before you begin writing code to ensure that you adhere to all of the outlined specifications.

Workflow Expectations

Pair Program, ideally in person, on each of the tasks. Do so in work-in-progress branches that get pushed to your team’s repository and merged back into the main branch. Each member of the pair should be the commit author for at least one of the requirements (unless there is only 1). You should create a branch per story and a commit per requirement completion. Once all requirements are complete, merge the task’s commits back into stage.

Once finished with a story, the “driving” partner should create a pull request for the completion of the story, and the “navigating” partner should review it and merge it into main via GitHub’s interface. Each partner should create at least two pull requests, and each partner must merge two pull requests from the other partner. For reference on pull requests see Creating a pull request and merging a pull request.

Tasks & Requirements

For this exercise, there are a series of tasks you will need to complete in order. Make sure to read the instructions carefully.

Aside from adding a database to the Pomodoro Timer feature, this exercise will also modify the feature such that users are associated with specific the timers they create! Please be sure to complete the tasks below with this change in mind.

If you are unsure about how to implement something, don’t forget to reference the SQLAlchemy readings and documentation before attending office hours. These resources should contain all the information you need. You are also welcome to reference similar tables in the CSXL repository, but keep in mind that different features are likely to have meaningfully different implementations!

Task 1: Set Up the PomodoroTimerEntity

The first step to complete when it comes to creating a new table in SQLAlchemy is creating the entity for it. The file has already been set up for you, but it is up to you to complete the TODO items listed in the file.

In order to fully and properly connect the new entity/table to the database, you will also need to modify the User Entity to finish establishing the one-to-many relationship between the two tables.

Technical Requirements:

1. Define the main fields of the Pomodoro Timer entity. These will correspond to the model for the Pomodoro Timer!
2. Define the foreign key to establish the one-to-many relationship between the pomodoro and user tables.
3. Complete the from_model() method for the entity.
   • This method should take in a PomodoroTimer model convert it to the appropriate entity object.
4. Complete the to_model() method for the entity.
   • This method should be called directly on the PomodoroTimerEntity and convert it to a PomodoroTimer model.
5. In the user_entity.py file, define the field to establish the one-to-many relationship between the pomodoro and user tables.

Task 2: Load the Demo Data Into the Database

The next task is loading the fake Pomodoro Timer data into the database for testing on your local development server. This will ensure that Productivity data is pre-populated when you go to implement service methods.

The demo data is already set up for you - it’s found in the backend/test/services/productivity_data.py file. Pay attention to what user the fake timers are being added to!

Requirements:

1. Go to the backend/script/reset_demo.py script file and complete the TODO to load the demo data into the productivity feature.
2. Once you have completed it, run the python3 -m backend.script.reset_demo script to ensure it works and loaded the data.

Task 3: Complete the get_timers Service Method and Run its Tests

Now that we have the entity set up, we may continue on to implementing the service methods in backend/services/productivity.py! In this task, you will focus on just one method to complete it end-to-end. Since the API routes in backend/api/productivity.py simply call on the service methods, we don’t need to make any changes to the routes.

However, because we would like to ensure that only the user that created the timer can view it, we do need to update the code in a few ways to allow for user-specific data querying and permissions checks.

In the API routes, the first step has already been completed: adding subject: User = Depends(registered_user) to each of endpoint functions. In brief, Depends(registered_user) is a dependency injection that allows the currently logged in user to be injected and bound to the subject parameter. Please refer to the docs/auth.md docs file “Backend Routes Requiring a Registered User” section for more information about how this works!

The second step is updating the service method to take in this subject parameter and enforce permissions as needed. You will complete this according to the TODOs in the service file and the requirements below.

The third step is testing via Pytest tests, one which is already provided for get_timers, found in backend/test/services/productivity_test.py. You will run this test via the CLI command:

pytest backend/test/services/productivity_test.py::test_get_timers

The final step is validating the end-to-end route from the running web app in development.

Requirements:

1. Update the get_timers method in productivity.py to take in subject as a parameter. Pay close attention to the order that the API routes input the parameters in. Update the docstring of each method to reflect this.
2. Complete the implementation of the get_timers service method, querying the session for a user’s timers and returning a list of PomodoroTimer models.
3. Run the pytest command above to run the pre-written test for this method. It should pass! If it does not, keep working on your implementation until it does.
4. Finally, try running honcho start to bring up the dev server. Log in as Sally Student by appending /auth/as/sally/111111111 to the localhost:1560 path (be sure that there is nothing following the root path when you do this). There should be three test timers already populated. Authenticating as yourself, via ONYEN login, should result in no timers visible. The buttons for creating/editing/etc will not yet work, of course, because you have more implementation work in the next task.

Task 4: Update the remaining service methods and test them

Now that you have completed one service method end-to-end, with tests, your job is to work through the remaining service methods in the ProductivityService class one-by-one using a similar process.

For the next methods, there are some additional specific expectations on permissions, though! We would like to ensure that only the user that created the timer can view/edit/delete it, we do need to update the code in a few ways to allow for this permission check.

You will complete this according to the TODOs in the service file and the requirements below.

Requirements:

1. Update each of the remaining four service methods in productivity.py to take in subject as a parameter. Pay close attention to the order that the API routes input the parameters in. Update the docstring of each method to reflect this. Implement each method and run its tests as you go, with special notes on the following methods:
2. get_timer - Raise a UserPermissionException if the user attempting to get the timer with the given id is not the same as the user associated with the timer entity in the database.
   • Raise this exception as follows: UserPermissionException("productivity.view", f"productivity/{timer_id}").
3. create_timer - Be sure the new timer created is associated with the user making the request.
4. update_timer
   0. You will need to implement two of your own unit tests in productivity_test.py for this service method!
   1. Refer to the testing data preloaded in the unit tests found in productivity_data.py. Additionally, look toward other test functions in this file for inspiration.
   2. Raise a UserPermissionException if the user attempting to update the timer with the given id is not the same as the user associated with the timer.
      • Raise this exception as follows: UserPermissionException("productivity.update", f"productivity/{timer_id}").
5. delete_timer - Raise a UserPermissionException if the user attempting to delete the timer with the given id is not the same as the user associated with the timer.
   • Raise this exception as follows: UserPermissionException("productivity.delete", f"productivity/{timer_id}").

Note:

UserPermissionException is a special exception we created to handle situations where a user attempts to complete an action they do not have permission for. It takes two parameters – an action string and a resource string. As listed in the requirements, we have provided the parameters for each UserPermissionException you must raise. We will go over this in more detail in class, so you do not need to concern yourself with the logic behind this at the moment!

Task 5: Test Your Code on the Local Server

At this point, if you completed all the tasks above correctly, your new SQLAlchemy backend should be completely set up, your service method unit tests should be passing, and your code should work.

Once you are certain your backend is set up properly, the final task is running your code on the local server to check if everything still works as expected. You can also check to ensure that timers are actually associated with individual users!

Follow the steps below to test your code on the local server.

1. Run python3 -m backend.script.reset_demo. This will delete and recreate the database and load it with the development data defined in the repository.
2. Run honcho start.
3. Test the individual backend methods using the localhost:1560/docs, just as you did in EX02.
4. As described in the docs/get_started.md file, you can log in as various mock users to test your code.
   • Log in as Sally Student by appending /auth/as/sally/111111111 to the localhost:1560 path (be sure that there is nothing following the root path when you do this). There should be three test timers already populated from Task #4. Create, edit, and delete some timers to ensure that everything is working properly.
   • Log in as Rhonda Rhoot by appending /auth/as/root/999999999 to the path. Note that Sally Student’s timers should not appear on Rhonda’s page! Create, edit, and delete timers.
   • Log in as Sally Student again and ensure that Rhonda’s changes do not affect Sally’s page.

If everything is working, congratulations! You have created a new feature for the CSXL with a fully-developed frontend and backend. Great work!

Getting Started

1. Find your Team Name and paired partner on the sheet: https://docs.google.com/spreadsheets/d/1uQ_LlalW6lHUZGS7HMnWtbO0t4hu2kC_WDDSa024rj0/edit?usp=sharing
2. Lookup their contact information in the UNC Directory if you do not have it already.
3. E-mail them, if they have not e-mailed you already, and propose some of the next possible dates and times you can get together to pair program!
4. Once together, create your team, follow the set up instructions, and get to work!