EDAF75

During the lab session, we want one of you to run the test program described at the bottom of this page – when it passes, we'll look a bit closer at your code (mostly your queries). At the bottom of the page are also some tips which can be useful if you want to test your program 'manually' using curl.

You must, as usual when writing program code, be meticulous when you implement this server. Previous years, many groups have had problems caused by a mis-reading of the specs – as one example: a User is defined using a JSON object like this:

{
    "username": "alice",
    "fullName": "Alice Lidell",
    "pwd": "aliceswaytoosimplepassword"
}

JSON is based on Javascript, and in Javascript the convention is to use camelCase. So, if you want to parse this object in Python, you can write:

user = request.json
username = user['username']
full_name = user['fullName']
pwd = user['pwd']
...

But if we write:

user = request.json
...
full_name = user['full_name']
...

the JSON-parser will not find the key full_name.

And in Java, to have Gson convert it into a POJO, the POJO class must be defined as:

class User {

    public final String username;
    public final String fullName;
    public final String pwd;

    // ...
}

If we define the class as

class User {

    public final String username;
    public final String full_name;
    public final String pwd;

    // ...
}

Gson wouldn't be able to convert a JSON object which has an attribute fullName into our POJO.

The same goes for all other names in the specs – the point of having a protocol is that everyone use it the same way. It may seem illogical to call the attribute username, and not userName (since we call it fullName), but "username" is a word these days, whereas "fullname" isn't.

In this lab we'll implement a REST server for the database we designed in lab 2. You can implement it using one of the following tools:

• Python 3 and Bottle (which we used during lecture 6)
• Java and Spark (which you can read about in the notes to lecture 6)

There are many other alternatives, but our teaching staff can't keep track of them all, so we have to restrict your options to the ones above.

As a starting point, you can look at either of the examples in the lecture notes for lecture 6.

We want you to write a REST server running on http://localhost:7007 – it should handle the following endpoints:

• curl -X GET http://localhost:7007/ping
  

  returns the string "pong", and status code 200 – this is just used for checking that your service is running.

• curl -X POST http://localhost:7007/reset
  

  empties the database, and enters the following theaters:

  • "Kino", 10 seats
  • "Regal", 16 seats
  • "Skandia", 100 seats

  The names of the theaters will always be unique (as in lab 2), and we will not provide any method to add more theaters in our service (unless you really want to do it, but then you should make sure not to accept duplicate names).

• Two endpoints for creating new users and movies – to create a new user we want:

  curl -X POST http://localhost:7007/users
  

  with a request body containing a JSON object of the form:

  {
       "username": "alice",
       "fullName": "Alice Lidell",
       "pwd": "aliceswaytoosimplepassword"
  }
  

  If there is already a user with the given user name, the service shouldn't add anything to its database, and it should return an empty string, and the status code 400. Otherwise it should add the new user, and then return the string /users/<username> (i.e., the username provided in the request body), and the status code 201.

  As a matter of principle, we should never, ever save a password in clear text in a database – near the bottom of this page there are some hints for how to encrypt the users' passwords.

  We want corresponding endpoint for movies:

  curl -X POST http://localhost:7007/movies
  

  with a JSON object of the form:

  {
      "imdbKey": "tt4975722",
      "title": "Moonlight",
      "year": 2016
  }
  

  If the IMDB key is already in our database, we'll not add anything to the database, return an empty string and the status code 400, otherwise we add the movie to our database, return the string /movies/<imdbKey> (i.e., /movies/tt4975722 for "Moonlight"), and the status code 201.

• We want a way to add performances:

  curl -X POST http://localhost:7007/performances
  

  with a JSON object of the form:

  {
       "imdbKey": "tt5580390",
       "theater": "Kino",
       "date": "2021-02-22",
       "time": "19:00"
  }
  

  This should add a new performance of a given movie, at a given theater, at a given date and time. We won't bother checking for overlapping performances etc. – if the movie and theater exist, we add the performance to our database, no matter what time it is scheduled for.

  The server should give each performance a unique id, and it should return the resource for the new performance (as a simple string), so a successful call should get a status code of 201, and a string such as:

  /performances/bfd3c03b041173ab1e45a6032a163418
  

  Below the problem text are instructions on how we can get the value of an autogenerated key in SQLite (we also looked at it during lecture 6).

  If either the movie or theater is missing in our database, we just return the string "No such movie or theater", and the status code 400.

• curl -X GET http://localhost:7007/movies
  

  which should return the movies, in the following format:

  {
      "data": [
          {
              "imdbKey": "tt5580390",
              "title": "The Shape of Water",
              "year": 2017
          },
          {
              "imdbKey": "tt4975722",
              "title": "Moonlight",
              "year": 2016
          },
          {
              "imdbKey": "tt0097045",
              "title": "Moonlight",
              "year": 1989
          },
          {
              "imdbKey": "tt1895587",
              "title": "Spotlight",
              "year": 2015
          },
          {
              "imdbKey": "tt2562232",
              "title": "Birdman",
              "year": 2014
          },
          {
              "imdbKey": "tt2084970",
              "title": "The Imitation Game",
              "year": 2014
          },
      ]
  }
  

• curl -X GET http://localhost:7007/movies/<imdb-key>
  

  should give information about the movie with the given IMDB-key, so

  curl -X GET http://localhost:7007/movies/tt5580390
  

  returns

  {
      "data": [
          {
              "imdbKey": "tt5580390",
              "title": "The Shape of Water",
              "year": 2017
          }
      ]
  }
  

  Observe that we return a list (although the title should be unique given a IMDB-key), so for a non-existing IMBD key we just return an empty list.

• curl -X GET http://localhost:7007/performances
  

  should return all performances, like this:

  {
      "data": [
          {
              "performanceId": "397582600f8732a0ba01f72cac75a2c2",
              "date": "2021-02-22",
              "startTime": "19:00",
              "title": "The Shape of Water",
              "year": 2017,
              "theater": "Kino",
              "remainingSeats": 10
          },
          {
              "performanceId": "1c45e074c9b15c4d3214cc13a2228319",
              "date": "2021-02-22",
              "startTime": "19:00",
              "title": "Moonlight",
              "year": 2016,
              "theater": "Skandia",
              "remainingSeats": 100
          },
          {
              "performanceId": "fe8a6132139d53bfc043a3f9d80bda71",
              "date": "2021-02-23",
              "startTime": "19:00",
              "title": "The Shape of Water",
              "year": 2017,
              "theater": "Kino",
              "remainingSeats": 10
          },
          {
              "performanceId": "087c47eb3b1a9b234eccadf5c8aabd30",
              "date": "2021-02-23",
              "startTime": "19:00",
              "title": "Moonlight",
              "year": 2016,
              "theater": "Skandia",
              "remainingSeats": 100
          },
          {
              "performanceId": "b1dd27bf234048b20666f05197f04351",
              "date": "2021-02-24",
              "startTime": "21:00",
              "title": "Moonlight",
              "year": 2016,
              "theater": "Skandia",
              "remainingSeats": 100
          }
      ]
  }
  

  As we saw during lab 2, there are (at least) two ways of keeping track of how many remaining seats we have for each performance. You are free to choose any method you like, but try to generate the data for this call in one SQL statement, whatever method you choose (using WITH-statements or subqueries often help). If you can't solve it in one statement, that's OK, but please give it a try!

• curl -X POST http://localhost:7007/tickets
  

  with a JSON object like:

  {
       "username": <username>,
       "pwd": <pwd>,
       "performanceId": <performance-id>
  }
  

  should try to let <username> buy a ticket for <performance-id>, using the password <pwd>.

  If the order is OK, i.e., there is such a performance, there is a user with the given username and password, and there are still free seats, the server should add the new ticket, set the return status to 201, and return the name of its new resource, which could be something like:

  /tickets/9cd452e81be30858c8597245682255db
  

  Otherwise:

  • If there are no free seats left, the server should return the string "No tickets left" and return status 400.
  • If there is no such user, or if the password is wrong, the server should return the string "Wrong user credentials" and the status 401.
  • If something else goes astray, the server should return the annoyingly vague error message "Error" and a status of 400.

• curl -X GET http://localhost:7007/users/<username>/tickets
  

  should give a summary of all tickets for a user, like this:

  curl -X GET http://localhost:7007/users/alice/tickets
  {
      "data": [
          {
              "date": "2021-02-22",
              "startTime": "19:00",
              "theater": "Kino",
              "title": "The Shape of Water",
              "year": 2017,
              "nbrOfTickets": 2
          },
          {
              "date": "2021-02-23",
              "startTime": "19:00",
              "theater": "Skandia",
              "title": "Moonlight",
              "year": 2016,
              "nbrOfTickets": 1
          }
      ]
  }
  

  Generating the data for this call can be done in one SQL statement (you're free to use WITH-statements and subqueries), give it a try! If you can't find a way to do it, it's OK to use more than one statement.

• Optional: Using the GET /movies endpoint we saw above, we also want to be able to search for a movie given its title and/or year:

  curl -X GET http://localhost:7007/movies\?title=Moonlight
  

  should return:

  {
      "data": [
          {
              "imdbKey": "tt4975722",
              "title": "Moonlight",
              "year": 2016
          },
          {
              "imdbKey": "tt0097045",
              "title": "Moonlight",
              "year": 1989
          }
      ]
  }
  

  We can also narrow the search by specifying the year:

  curl -X GET http://localhost:7007/movies\?title=Moonlight\&year=2016
  

  which should return only:

  {
      "data": [
          {
              "imdbKey": "tt4975722",
              "title": "Moonlight",
              "year": 2016
          }
      ]
  }
  

  It should also be possible to search only for the year, so:

  curl -X GET http://localhost:7007/movies\?year=2014
  

  should produce:

  {
      "data": [
          {
              "imdbKey": "tt2562232",
              "title": "Birdman",
              "year": 2014
          },
          {
              "imdbKey": "tt2084970",
              "title": "The Imitation Game",
              "year": 2014
          }
      ]
  }
  

  Beware that we can't send spaces in our query strings, we need to URLencode them first.

  It's not uncommon for groups to get stuck on this endpoint – in the notes for lecture 6 there is one Python and one Java program which shows a way to handle query parameters, but to make sure you don't have to spend too much time here, I've commented away the test of this endpoint (once the rest of the tests for this lab works, you could try this out – in the project we'll require that queries work, and you might as well learn how to implement them now).

When we insert values into a table which generates a key, we often want to know what the newly generated key is.

Assume we have a table:

CREATE TABLE customers (
  customer_id    TEXT DEFAULT (lower(hex(randomblob(16)))),
  name           TEXT,
  ...
)

We insert new values with an INSERT statement, and if we have a decently recent version of SQLite (3.35+, from March 2021), we can use a very nice extension of the INSERT statement which returns an expression:

INSERT
INTO       customers(name, ...)
VALUES     (?, ...)
RETURNING  customer_id

Since we don't explicitly set the value of customer_id, it gets a default value (which in a randomblob), and the insert statement will return this value (see the Python version of the server in lecture 6 for an example of how we can use this).

Unfortunately older versions af SQLite don't have this feature – if your SQLite version is prior to 3.35, you can instead issue a traditional INSERT statement, and then use two built in features of SQLite:

• each row has an id called rowid
• there is a function last_insert_rowid() which returns the rowid of the last inserted row in each table

Using this you can get the most recently generated key as:

SELECT  customer_id
FROM    customers
WHERE   rowid = last_insert_rowid()

The sqlite3 package has a clever feature allowing us to log the SQL code executed over a connection – if we add a call to .set_trace_callback(print) on our connection:

db = sqlite3.connect("movies.sqlite")
db.set_trace_callback(print)

all SQL-code run inside execute statements will be printed in the window in which your server is running.

• Either create your own gradle project (see lectures 5 and 6), or use the distributed code for lecture 6, and start from there. You will need to define the proper endpoints yourself (it's some get- and post-calls in the main-method in App).
• Open a terminal window (there is at least one such program for Linux, Mac and Windows), and cd into the directory in which you unzipped the project.
• Use some text editor (VS Code, Atom, Sublime, Emacs, vim, …) to create the file lab3-schema.sql (it should be in the app directory of the project), and write the SQL code needed to create the tables in your database.

• Now, in your terminal window, create your initial database by running the command:

  $ sqlite3 lab3.sqlite < lab3-schema.sql
  

  This command should produce the file lab3.sqlite (you can call it anything you want, but it has to be in the app directory, and it should be the same name you use when you open your database file in the class App.

• In your terminal window, compile and start your REST service by executing either

  $ ./gradlew run
  

  if you're running Linux or Mac, or

  $ gradlew.bat run
  

  if your're running Windows.

  This will take some time, and gradle will fetch all the libraries needed to run the project.

  After a while, you'll see something like this in your terminal window:

  > Task :run
  SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
  SLF4J: Defaulting to no-operation (NOP) logger implementation
  SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
  <=========----> 75% EXECUTING [39s]
  > :run
  

  It may look like something went wrong, but it's fine – your server is now listening on port 7007.

• Open another terminal window (you'll use two terminal windows for this lab – one for running your server and one for sending curl commands to the server), you can cd into any directory you want (you don't have to be in the same directory as your project). Then run:

  $ curl -X GET http://localhost:7007/ping
  

  This will send the 'command' /ping to your server (if it listens on port 7007), and it will return the string pong (this endpoint is implemented in the distributed project).

• Now open the file app/src/main/java/lab3/App.java in your project with a text editor (probably the same you used for editing your lab3-schema.sql file), and add one of the endpoints in the list above (I'd suggest you begin with /reset) – then add a method in your Database class to handle the endpoint. Go to the terminal window running your server, stop the server (Ctrl-C, or something like that), and recompile/restart it by issuing

  $ ./gradlew run
  

  or

  $ gradlew.bat run
  

  (depending on if you're running Linux/Mac, or Windows).

  Then call your newly implemented endpoint using either curl or insomnia=/=postman.

• Then add endpoint after endpoint until you're finished.

Normally we would encrypt the passwords, but to make things a bit easier, it's OK if you skip it this time. If you want to try to 'encrypt' the passwords, you can use the following code snippets (it's not really 'state of the art', but good enough for our purposes).

Java:

import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.nio.charset.StandardCharsets;
import java.math.BigInteger;

class PasswordHashGenerator {

    public static String hash(String text) {
        try {
            MessageDigest md = MessageDigest.getInstance("SHA-256");
            md.update(text.getBytes(StandardCharsets.UTF_8));
            byte[] digest = md.digest();
            return String.format("%064x", new BigInteger(1, digest));
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

Python:

def hash(msg):
    import hashlib
    return hashlib.sha256(msg.encode('utf-8')).hexdigest()

The two passwords dobido and whatsinaname should be hashed into:

dobido: 5dae7886bbe2b1fb134e79f4b83612a16f5b26ef2ac06a91cd7caee0ee7b5557
whatsinaname: 053fc66a6cef5cac353d70925c660075075b3be0abc34e01a15ccfc8e17fbcbd

I've written a test program which checks your server, it checks the endpoints in the lab description above in chronological order (so you can use it as you work your way down the list of endpoints).

You can download the test script here, just save the file (it's a simple Python program) in any directory, and run it like:

python check-lab3.py

You run the program from the directory in which you saved it, but it could be any directory on your computer (it doesn't have to be the same as the one with your server code).

The program uses the requests library, and you might have to install it somehow – the easiest way is probably to just run

pip install requests

To test your REST server, start it (either using gradlew, if it's a Java program, or by just running your python program), and then, in another command line window, run the test program:

$ python check-lab3.py
--------------------------------
Reached check_ping:
   curl -X GET http://localhost:7007/ping
     => OK
--------------------------------
Reached check_reset:
   curl -X POST http://localhost:7007/reset
     => OK
--------------------------------
Reached check_post_user:
   curl -X POST http://localhost:7007/users
     => OK
--------------------------------
Reached check_post_movie:
   curl -X POST http://localhost:7007/movies
     => OK
--------------------------------
Reached check_post_performances:
   curl -X POST http://localhost:7007/performances
     => OK
--------------------------------
Reached check_get_movies:
   curl -X GET http://localhost:7007/movies
     => OK
--------------------------------
Reached check_get_movies_with_ids:
   curl -X GET http://localhost:7007/movies/tt3661210
     => OK
   curl -X GET http://localhost:7007/movies/tt10016180
     => OK
   curl -X GET http://localhost:7007/movies/tt6857376
     => OK
   curl -X GET http://localhost:7007/movies/tt10332588
     => OK
   curl -X GET http://localhost:7007/movies/not_a_real_imdb_key
     => OK
--------------------------------
Reached check_get_performances:
   curl -X GET http://localhost:7007/performances
     => OK
--------------------------------
Reached check_ticket_hoarding:
   curl -X POST http://localhost:7007/tickets
     => OK
     => OK
     => OK
     => OK
     => OK

==========================================
           Initial version
Your server passes our tests -- well done!
==========================================

When you get the output above, your server works well enough for you to pass lab 3.

If you want to test 'manually' during development, you can either use curl at the command line, or a tool like insomnia/postman. For sending JSON objects in the request body, using insomnia/postman is probably easier, but you can do it using curl using the -d and -H flags:

$ curl -X POST http://localhost:7007/movies -d '{"imdbKey": "tt5580390","title": "The Shape of Water","year": 2017}' -H "Content-Type: application/json"