2021 S2 ISYS2120 - DB Application Programming Assignment

Welcome to assignment 03 for ISYS2120.

This is the code base that you must modify for the purposes of the assignment.

Please look at the specification file to see the full details regarding what you need to do for this assessment.

You will have to mainly have to modify 2 files: routes.py and database.py

You will also have to modify some templates in the 'templates/*.html' folder

Task Outline

The media server is designed to keep track of files and metadata information regarding various audio and video media.

Schema Description

As per the above schema you have various consumable media:

1. Movies
2. TV Shows (which have TV Episodes)
3. Songs (which are performed by Artists and appear in Albums)
4. Podcasts (which have Podcast Episodes)

There is also some User Account information which contains information about the user including:

1. Contact Methods
2. Username / password
3. Subscribed podcasts
4. Consumed media

We have abstracted most of the common elements such descriptions, artwork and genres into a MetaData table system. The primary Metadata table contains 3 fields:

1. an ID field (which other tables use a FK)
2. a MetaData type ID field (which maps to the MetaDataType table)
3. an actual value for the metadata

Presently, there are 4 possible types of metadata:

1. Song Genre
2. Film Genre
3. Artwork
4. Description

Tasklist

You can find all the functions that you need to work on by looking for a 'TODO' comment in the code.

We have provided fully implemented functions and features that you can adapt to complete your tasks.

SQL Tasks

Your first task is to complete the 10 missing SQL queries in ’database.py’ They are as follows:

1. A user logs into the system by providing their username and password. Once logged in, they reach the landing page which displays:

   1. User subscribed Podcasts
   2. User Playlists
   3. User current in-progress items

2. When a user clicks on a song, they should see all the song information, including:

   1. Song Name
   2. Song Artists
   3. Song length
   4. Song Metadata such as Artwork, description, Genres

3. When a user clicks on the ’tv shows’ nav item, they should see a list of all tv shows information, including:

   1. TV Show ID
   2. TV Show Name
   3. Total number of tv show episodes for this tv show

4. When a user clicks on a single tv Show, they should see a list of relevant information, including:

   1. TV Show Name

   2. TV Show Metadata such as Artworks, Descriptions, Genres

   3. A list of every episode for this tv show ordered by Season and then Episode including:

      1. TV Show Episode ID
      2. TV Show Episode Title
      3. Season
      4. Episode
      5. AirDate

5. When a user clicks on a single Album, they should see a list of relevant information, including:

   1. Album Name

   2. Album Metadata such as Artworks, Descriptions

   3. All genres for this Album. The Genres for an album are composed of all the Genres for all it’s songs.

   4. A list of all songs in an album ordered by track number including:

      1. the song ID
      2. the Song Name
      3. The Song Artist(s)

6. When a user clicks on a single podcast from the ’podcasts’ list (or their subscribed podcasts), they should see a list of relevant information including:

   1. Podcast ID

   2. Podcast Name

   3. Podcast URI

   4. Last Updated

   5. Podcast Metadata such as Artworks, Descriptions, Genres

   6. A list of all podcast episodes in this podcast ordered by descending publication date including:

      1. Podcast Episode ID
      2. Podcast Episode Title
      3. Podcast Episode URI
      4. Podcast Episode Date Published
      5. Podcast Episode Length

7. When A user clicks on a single podcast episode from the list in part 7, they should see all relevant podcast episode information including:

   1. Podcast Episode ID
   2. Podcast Episode Title
   3. Podcast Episode URI
   4. Podcast Episode Date Published
   5. Podcast Episode Length
   6. Podcast Episode Metadata such as Artworks, Descriptions, Genres

8. Write the SQL to ensure the proper insert of a new Song, including valid artist checks and appropriate MetaData inserts. You may need to complete the function addSong() in the schema.sql file and reload the schema.sql file (or just run just that create statement)

9. Write SQL for getting all relevant details for searching through all movies by title

10. When a user clicks on a single genre, they should see all appropriate items that are associated with that genre (Movies and TV Shows for 'film genre'; Songs for 'song genre'; and Podcasts for 'podcast genre'). Each item listed should include the following:

    1. Item ID
    2. Item Title
    3. Item Type (Movie / Tv Show / Song / Podcast)

Data handling tasks

Your second task set is to complete the data handling for the last 5 SQL tasks from above.

You must complete the following functions in ’routes.py’:

1. single_podcast()
2. single_podcastep()
3. add_song()
4. search_movies()
5. single_genre()

You can write as many helper functions to assist you as you wish, just remember to document them in your report.

Data Representation tasks

Your third task set is to complete the data representation for the last 5 tasks in the SQL tasks.

You must complete the following files in the 'templates' folder:

1. 'templates/singleitems/podcast.html'
2. 'templates/singleitems/podcastep.html'
3. 'templates/createitems/createsong.html'
4. 'templates/searchitems/search_movies.html’ and add a link to ’templates/top.html’ enabling the drop-down option to search by movies
5. 'templates/singleitems/genres.html' and add clickability to all genres in 'templates/singleitems/podcast.html', 'templates/singleitems/song.html', 'templates/singleitems/tvshow.html', 'templates/singleitems/movie.html'

New functionality

Your fourth task is to create a new functionality. This will usually involve the following:

1. Create a new function(s) in 'routes.py' to handle the new route and handle data flow.
2. Create a new function(s) in 'database.py' or new functions in SQL.
3. Create a new template(s) in 'templates/*.html'.
4. Add new Menu items to 'templates/top.html' or links in other existing files to access your new routes