1. Database

Riddle Database

In this lab we are going to delve into databases and a new software, banjo.

[0] Experience the ISF Riddle Server

The last time we saw riddles, they existed only within each of your computers. You had to manually add new ones and there was no way to keep track of how many people guessed them.

We are now going to look at Riddles that are hosted on the internet!

๐Ÿ’ป Visit http://riddles.student.isf.edu.hk/riddles/all to view riddle server.

Not very easy to read, right? You can install the JSON Formatter Chrome extension to view better formatted JSON.*

๐Ÿ’ป Now try making get request to receive the same information in your terminal using httpie.

http get http://riddles.student.isf.edu.hk/riddles/all
HTTP/1.1 200 OK
Connection: close
Content-Length: 94
Content-Type: application/json
Date: Thu, 15 Sep 2022 04:53:08 GMT
Server: gunicorn

    "riddles": [
            "correct": 0,
            "guesses": 3,
            "id": 1,
            "question": "Where does dragon milk come from?"
๐Ÿ’ป Do you know the answer? Try posting a guess via the Terminal.
http post http://riddles.student.isf.edu.hk/riddles/guess id=1 guess="scales"

It is common for POST requests to send a payload with the request. In this case, the payload is a parameter called id specifying which riddle we are guessing, as well as guess.

We are also sending this request to a different url endpoint: /guess

You should see something like this:

HTTP/1.1 200 OK
Connection: close
Content-Length: 94
Content-Type: application/json
Date: Thu, 15 Sep 2022 04:57:08 GMT
Server: gunicorn

    "correct": false,
    "guess": "scales",
    "riddle": {
        "correct": 0,
        "guesses": 4,
        "id": 1,
        "question": "Where does dragon milk come from?"

[Explore the Endpoints]

Server APIs often rely on different URL endpoints to determine what the API should do.

Here is a cheatsheet of the Riddle endpoints, what parameters they take in their payload, and what they do:

Method URL Required Payload Action
GET /riddles/all Returns a list of all the riddles, without answers.
GET /riddles/one id Returns the riddle if it exists. (Otherwise, it returns an error with status code 404.)
POST /riddles/new question, answer Creates a new riddle (with an automatically-assigned id). Returns the riddle.
POST /riddles/guess id, guess Checks whether the guess is correct. In the response, correct is True or False.

๐Ÿ’ป Explore each endpoint, and be sure to successfully:

  • view all riddles without the answers
  • view a single riddle
  • add a new riddle
  • guess a riddle

[1] Explore the Riddle Model

Now that you’ve experienced the riddle server, let’s delve into how it’s made.

The Riddle objects looks almost identical to how it looked in the Riddler lab. The main difference, is we must define the datatype of each property. This tells the database, what type of information each property will store.

class Riddle(Model):
    question = StringField()
    answer = StringField()
    guesses = IntegerField()
    correct = IntegerField()


[Set Up]

For this lab, we need to download software to view the database in a nicely formatted chart.

๐Ÿ’ป Download dbsqlite onto your computer: https://sqlitebrowser.org/dl/

๐Ÿ’ป Get the lab and install any necessary packages.
mwc update
๐Ÿ’ป In the Terminal, open the lab folder.
cd ~/desktop/making_with_code/cs10/unit00_networking/lab_riddle_database

๐Ÿ’ป Enter the Poetry Shell. As a reminder, we will run this command at the start of each lab, but only when we are inside a lab folder.

poetry shell
๐Ÿ‘พ ๐Ÿ’ฌ Exiting the poetry shell
When you want to exit the shell, you can type exit or ^D
๐Ÿ’ป Install Banjo.
pip3 install django-banjo

[Viewing the Database]

You have just downloaded a simple server that hosts Riddles onto your laptop. Let’s start by looking at the its database.

๐Ÿ’ป Open the database in the DB Browser:
open database.sqlite

๐Ÿ’ป Select Browse Data

๐Ÿ‘€Here you will see all of the riddles that are in your locally hosted server. This database file gets updated each time you guess or add a Riddle.

[Adding and Guessing Riddles]

Now, let’s guess a Riddle and add a new Riddle and see how that affects the database.

In this unit, we will be using Banjo and wrapper over Django. Django is a popular web framework that is used to quickly create web apps. We will be using Django in the spring, so for now we will Banjo to introduce the software.

๐Ÿ’ป Let's enter the Banjo shell to interact with the riddles.
banjo --shell
No changes detected in apps 'app', 'banjo'
Operations to perform:
  Apply all migrations: admin, app, auth, contenttypes, sessions
Running migrations:
  No migrations to apply.
# Shell Plus Model Imports
from app.models import Riddle
Python 3.10.6 (v3.10.6:9c7b4bd164, Aug  1 2022, 17:13:48) [Clang 13.0.0 (clang-1300.0.29.30)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
๐Ÿ’ป Let's start by just viewing all the riddles in the shell:
<QuerySet [<Riddle 1: I'm tall when I'm young, and I'm short when I'm old. What am I? (11/33)>, <Riddle 2: How many months of the year have 28 days? (5/10)>, <Riddle 3: I can fly but have no wings. I can cry but I have no eyes. Wherever I go, darkness follows me. What am I? (0/2)>, <Riddle 4: What has to be broken before you can use it? (3/7)>, <Riddle 5: The more you take, the more you leave behind. What are they? (3/10)>, <Riddle 6: What is so delicate that saying its name breaks it? (5/6)>, <Riddle 7: I am always in front and never behind. What am I? (2/2)>]>
๐Ÿ’ป Now, let's add a new riddle:
new_riddle = Riddle(question='If you feed it, it lives; If you water it, it dies.', answer='Fire')

Note how you must save() the riddle to ensure it gets saved in the database.


Before moving on, make sure the Riddle was properly saved:

  • view all the Riddle objects
  • refresh the database in the DB Browser
๐Ÿ’ป Now, let's try to guess the riddle we just added:
>>> new_riddle.check_guess('fire')

Before moving on, make sure the Riddle properties, guesses, and correct, were properly updated:

  • view all the Riddle objects
  • refresh the database in the DB Browser

[Database Querying]

Now that you’ve experienced adding and updating the database, we’re going to explore how to search, or query, the database.

๐Ÿ‘€Open the Banjo documentation: https://cs.fablearn.org/docs/banjo/index.html

โœ๏ธ Using the Banjo documentation, fill out the worksheet and properly query the database using the Banjo shell.

[3] Deliverables

Once you’ve successfully completed the worksheet be sure to fill out this Google form.

[4] Extension

Next lab we will explore how to write the Riddle server.

๐Ÿ’ป Feel free to open up the code and start to understand how it works!
atom .