Project 02: SearchIt

Overview

The goal of the second project is to allow you to practice build web applications and databases in Python. Therefore, this project requires you to use Tornado and SQLite to create a music database.

You are to work in your Raspberry Pi groups and your code should run on the provided machines (although you can do your development anywhere and using any resources you wish).

Once again, to keep track of your work and to facilitate collaboration, your group will use your Projects repository on Bitbucket. The starter code for the project has already been merged into your Projects repository1. You simply need to do a git pull on your local clone to get the latest files.

After you have the files, you can see that the searchit project as the following layout:

searchit
    \_ assets
            \_ html
                    \_ album.html       SearchIt Album Template
                    \_ base.html        SearchIt Base Template
                    \_ gallery.html     SearchIt Gallery Template
                    \_ index.html       SearchIt Index Template
                    \_ track.html       SearchIt Track Template
            \_ yaml
                    \_ data.yaml        Music data YAML
    \_ searchit
            \_ __init__.py
            \_ __main__.py              Package Main Execution Module
            \_ database.py              SearchIt Database Module
            \_ web.py                   SearchIt Web Module

For this project, you need to modify the following files:

  1. data.yaml: This is the source of the data for our database.

  2. album.html: This should display a table consisting of the tracks found in the album.

  3. gallery.html: This should display a series of thumbnails for the items found in the group.

  4. index.html: This should display your group name, group members, and group logo.

  5. track.html: This should display information about the track.

  6. database.py: This implements the SQLite database.

  7. web.py: This implements the Tornado web application.

Each of these files has a TODO marker that identifies what needs to be implemented. Further details on what needs to be done are provided below.

The order in which you implement the different components is up to you. My recommendation is to get the database created and loaded and then implement and test one selection at a time.

Data

The information for our database is stored in the file assets/yaml/data.yaml. As you can see, it is in YAML format, which is a distant cousin to the JSON format we have discussed previously. The data file looks like this:

- name:     Taylor Swift
  image:    https://upload.wikimedia.org/wikipedia/commons/thumb/b/b2/Taylor_Swift_Red_Tour_2%2C_2013.jpg/800px-Taylor_Swift_Red_Tour_2%2C_2013.jpg
  albums:
      - name:   Red
        image:  https://upload.wikimedia.org/wikipedia/en/thumb/e/e8/Taylor_Swift_-_Red.png/220px-Taylor_Swift_-_Red.png
        tracks:
          - State of Grace
          - Red
          - Treacherous
          - I Knew You Were Trouble
          - All Too Well
          - 22
          - I Almost Do
          - We Are Never Ever Getting Back Together
          - Stay Stay Stay
          - The Last Time
          - Holy Ground
          - Sad Beautiful Tragic
          - The Lucky One
          - Everything Has Changed
          - Starlight
          - Begin Again

Each member in the group should modify this file by adding at least one artist and at least one album. To find images, I simply used the Wikipedia entries for the respective artists and albums.

Database

The database code can be found in searchit/database.py. To complete this module, you will need finish the searchit.database.Database class by implementing the following methods:

Constructor

def __init__(self, data=YAML_PATH, path=SQLITE_PATH):
    # TODO: Set instance variables and call _create_tables and _load_tables
    self.logger = logging.getLogger()
    self.data   = None
    self.path   = None
    self.conn   = None

The constructor requires you to set the data, path, and conn instance variables appropriately. Likewise, you need to call the _create_tables() and _load_tables() methods to create and load the database tables when the database instance is created.

Create Tables

def _create_tables(self):
    # TODO: Create Artist, Album, and Track tables
    pass

The _create_tables method should create the following tables:

Artists:
    Columns:      ArtistID | Name | Image
    Primary Key:  ArtistID

Albums:
    Columns:      ArtistID | AlbumID | Name | Image
    Primary Key:  AlbumID
    Foreign Key:  ArtistID

Tracks:
    Columns:      AlbumID | TrackID | Number | Name
    Primary Key:  TrackID
    Foreign Key:  AlbumID

ID and other numerical columns should be INTEGER while everything should be TEXT. None of the files should be NULL.

Load Tables

def _load_tables(self):
    # TODO: Insert Artist, Album, and Track tables from YAML
    pass

The _load_tables method should load the tables above by reading the assets/yaml/data.yaml file and inserting data into the appropriate tables. A sketch of the code looks something like this:

for artist in yaml.load(open(self.data)):
    EXECUTE INSERT ARTIST SQL

    for album in artist['albums']:
        EXECUTE INSERT ALBUM SQL

        for track in album['tracks']:
            EXECUTE INSERT TRACK SQL

A few notes:

  1. The YAML data structure contains a list of artists, which in turn consists of a dictionary with a list of albums, where each album contains a list of tracks.

  2. The ArtistID, AlbumID, TrackID, and Track Number are stored in the data file. Instead, you must keep counters for each field and increment or reset them properly.

  3. To help debug, you should use the logging facility by doing something like:

    self.logger.debug('Added Artist: id={}, name={}'.format(artist_id, artist['name']))

Artists

def artists(self, artist):
    # TODO: Select artists matching query
    pass

The artists method is used to search the Artists table for any Names that are similar to the artist query. It should return the ArtistID, Name, and Image.

Artist

def artist(self, artist_id=None):
    # TODO: Select artist albums
    pass

The artist method is used to retrieve the AlbumID, Name, and Image of the Albums associated with the artist specified by artist_id.

Albums

def albums(self, album):
    # TODO: Select albums matching query
    pass

The albums method is used to search the Albums table for any Names that are similar to the album query. It should return the AlbumID, Name, and Image.

Album

def album(self, album_id=None):
    # TODO: Select specific album
    pass

The album method is used to retrieve the TrackID, Number, and Name of the Tracks associated with the album specified by album_id.

Tracks

def tracks(self, track):
    # TODO: Select tracks matching query
    pass

The tracks method is used to search the Tracks table for any Names that are similar to the track query. It should return the TrackID, Name, and Albums.Image. To retrieve the Image associated with the track you will need to perform a JOIN on the Albums table.

Track

def track(self, track_id=None):
    # TODO: Select specific track
    pass

The track method is used to retrieve the TrackID, Artists.ArtistID, Artists.Name, Albums.AlbumID, Albums.Name, Number, Tracks.Name the track specified by track_id. To retrieve all this information, you will need to perform a JOIN on the Albums table and a JOIN on the Artists table.

Web Application

The web application code can be found in searchit/web.py. To complete this module, you will need finish the searchit.web.Application class and complete the various Handler classes:

Application Constructor

def __init__(self, port=DEFAULT_PORT):
    tornado.web.Application.__init__(self, debug=True, template_path=Application.TEMPLATE_PATH)
    # TODO: Initialize database and port
    self.logger   = logging.getLogger()
    self.ioloop   = tornado.ioloop.IOLoop.instance()
    self.database = None
    self.port     = None

    # TODO: Add Index, Artist, Album, and Track Handlers

For the Application constructor, you need to set the database and port instance variables. Additionally, you must add the handlers for the IndexHandler, ArtistHandler, AlbumHandler, and TrackHandler classes.

Index Handler

class IndexHandler(tornado.web.RequestHandler):
    def get(self):
        # TODO: Implement Index Handler
        pass

The IndexHandler should check the query and table arguments from the HTML form.

If the table is Artists then it should call the artists database method on the query to compute the list of matching artists.

If the table is Albums then it should call the albums database method on the query to compute the list of matching albums.

If the table is Tracks then it should call the tracks database method on the query to compute the list of matching tracks.

After this list of matches is computed, it should render the gallery.html template with the appropriate arguments.

If no table is specified, then it should render the index.html template with the appropriate arguments.

Artist Handler

class ArtistHandler(tornado.web.RequestHandler):
    def get(self, artist_id=None):
        # TODO: Implement Artist Handler
        pass

The ArtistHandler should check if the artist_id is valid. If so, then it should call the artist database method to retrieve a list of albums and then render the gallery.html template with appropriate arguments.

If artist_id is not valid, then it should call the artists database method with an empty string to generate a list of all possible artists and then render the gallery.html template with the appropriate arguments.

Album Handler

class AlbumHandler(tornado.web.RequestHandler):
    def get(self, album_id=None):
        # TODO: Implement Album Handler
        pass

The AlbumHandler should check if the album_id is valid. If so, then it should call the album database method to retrieve a list of tracks then render the album.html template with appropriate arguments.

If album_id is not valid, then it should call the albums database method with an empty string to generate a list of all possible albums and then render the gallery.html template with the appropriate arguments.

Track Handler

class TrackHandler(tornado.web.RequestHandler):
    def get(self, track_id=None):
        # TODO: Implement Album Handler
        pass

The TrackHandler should check if the track_id is valid. If so, then it should call the track database method to retrieve track information then render the track.html template with appropriate arguments.

If track_id is not valid, then it should call the tracks database method with an empty string to generate a list of all possible tracks and then render the gallery.html template with the appropriate arguments.

HTML

Although the base.html template is provided to you, you will need to edit the following templates:

index.html

This should be modified to include: a list of the group members and your group logo.

album.html

This is used to display all the tracks in an album. This can be done with either a list or a table.

<table class="table table-striped">
  <thead>
    <th>Number</th>
    <th>Name</th>
  </thead>
  <tbody>
    <tr>
      <td>$TRACK_NUMBER</td>
      <td><a href="/track/TRACK_ID">TRACK_NAME</a></td>
    </tr>
    ...
    <tr>
      <td>$TRACK_NUMBER</td>
      <td><a href="/track/TRACK_ID">TRACK_NAME</a></td>
    </tr>
  </tbody>
</table>

gallery.html

This is used to display a series of thumbnail images.

<div class="row">
  <div class="col-sm-3">
    <a href="/PREFIX/ITEM_ID" class="thumbnail text-center">
      <img src="ITEM_IMAGE">
      <div class="caption">
        <h4>ITEM_NAME</h4>
      </div>
    </a>
  </div>
  ...
  <div class="col-sm-3">
    <a href="/PREFIX/ITEM_ID" class="thumbnail text-center">
      <img src="ITEM_IMAGE">
      <div class="caption">
        <h4>ITEM_NAME</h4>
      </div>
    </a>
  </div>
</div>

track.html

This is used to display the information amount a track.

<dl>
    <dt>Track ID</dt>
    <dd>TRACK_ID</dd>

    <dt>Artist</dt>
    <dd><a href="/artist/ARTIST_ID">ARTIST_NAME</a></dd>

    <dt>Album</dt>
    <dd><a href="/album/ALBUM_ID">ALBUM_NAME</a></dd>

    <dt>Track Number</dt>
    <dd>TRACK_NUMBER</dd>

    <dt>Track Name</dt>
    <dd>TRACK_NAME</dd>
</dl>

base.html

The look of the web application is provided by the Bootstrap framework, which we will be learning after Spring Break. The particular theme is Flatly, which is part of the Bootswatch suite of themes.

Feel free to modify the base.html to use other themes or to apply different layouts.

Running

To run web application, you can do the following:

$ python -m searchit

You can find an example of the web application at http://xavier.h4x0r.space:9876/.

Submission

To submit your project, please commit your work to your Projects repository by the beginning of class, Tuesday, March 15.

  1. Charlie's and Sam's groups need to give Write permissions to repository. ↩