The DB World Tour^®, stop #1 of 4: MariaDB SQLite

Your mission in the final four assignments of the semester is to design a functioning Web application that allows a user from anywhere in the world to browse and contribute to a repository of data on a topic of your choice. Your repository will pull from four different database backends to deliver various facets of your subject matter: MariaDB SQLite, Redis, Mongo, and Neo4j.

In this first installment, you'll put together the infrastructure for your Flask app, install the supporting tools, and configure your server so that it can be accessed by end users. You'll also add the first fourth of your website, powered by the relational database MariaDB SQLite.

Your topic and assignment #6

Choose a personal hobby or interest that you find engaging enough to not get bored with in the next six weeks. (It should be considerably more interesting to you than commercial airline flights are, for example.) It should also be complex enough that there are several different interesting aspects about which you can collect data. (This is probably true of just about any hobby, but in case you have a very "simple" interest, you might want to choose something else.)

From this overarching theme, identify one facet of it that you think would lend itself well to the table structure of the relational model. Don't use up all your ideas on this first stop of the tour: you'll need three other aspects of your hobby to collect data about for the other three stops! Pick something interesting and non-trivial, but not exhaustive.

Create a new Flask app

You should begin a new Flask app for this project:

Create a directory called something like "dbtour". In it, create and source a new Python virtual environment, with the right packages:
```
$ python -m venv venv
$ source venv/bin/activate
$ pip install flask ipython
```
Also in that directory, create a fresh git repo for your project:
```
$ git init .
```
If you'd like to create a .gitignore file at this point, to keep your git status messages clean, you can give it these contents:
```
venv
__pycache__
.gitignore
```
Also in that directory, create the standard three required Flask files:

__init__.py:

from flask import Flask
dbtour_app = Flask(__name__)
from dbtour import routes

dbtour_app.py:
```
from dbtour import dbtour_app
```

routes.py:

from dbtour import dbtour_app
import sqlite3

@dbtour_app.route("/")
def does_this_work():
    conn = sqlite3.connect("whateverYouWantToCallYourSqliteDatabase.db")
    cursor = conn.execute("select msg from message")
    msg = cursor.fetchall()
    return f"<HTML><BODY><H1>{msg[0][0]}</H1></BODY></HTML>"

Also in that directory, type this on the command line:

$ sqlite whateverYouWantToCallYourSqliteDatabase.db
sqlite> create table message (msg text);
sqlite> insert into message values ('READY TO GO ON TOUR, BABY!!');
sqlite> (press CTRL-D to quit)

And also on the command line, run:

$ export FLASK_APP=dbtour_app
$ flask run -h 0.0.0.0 -p yourSECONDPortNumber

Finally, in your browser, go to the url "http://cpsc.umw.edu:yourSECONDPortNumber" and make sure you see a READY TO GO ON TOUR, BABY!! message.

Now that you've sanity check this all works end-to-end, add and commit your files to git (yes, you should add the .db file too, even though it's not plain text). Also, feel free to make whatever aliases in your .bash_profile you may want in order to automate stuff.

An important note about keeping your website up

Don't forget the instructions from assignment #5 about keeping your website up and running 24/7, even when you're not logged in. For the present, you'll keep your assignment #5 (Filbert) site up and running on your first port number, while you develop your assignment #6 site on your second port number.

Okay, the actual assignment #6 requirements

Your assignment #6 should fulfill the following requirements:

Your MariaDB SQLite database should have at least two tables, at least one of which has at least twenty rows.
The two tables must be "joinable" — i.e., it must make sense to join them. (Giving one table a foreign key that references the primary key of the other table is the normal way to achieve this.)
Your main landing page should show a table of information, containing ten rows only. This HTML table should present information from both of your MariaDB SQLite tables, properly joined together. (Which specific ten rows are shown is not important.)
Immediately beneath the table should be one or more drop-down widgets, each containing the current values for one or more of your table columns, and also the word "All." There should also be a button called "Search" or "Filter." When the button is pressed, the page should refresh and display only rows of the table that match the chosen value (or with no such restriction if "All" is chosen). If there are fewer than ten matching rows, show only the matching ones. If there are more, show only ten.
Also beneath the table should be a link with the words "Add data." When pressed, the user will be directed to a second page with an HTML form, in which they can enter values for new rows of either MariaDB SQLite table, or both. After adding a row, the user should be redirected back to the main page, with the table contents updated if appropriate. (If the user attempts to add information which violates a key constraint, they should be stopped with a friendly error message.)

Example

My database has two tables, called SHOWS and LOCATIONS:

SHOWS
+------------------------------+------------------+-------------+-----------+-----------+---------+-------------+
| name                         | creator          | network     | genre     | startYear | endYear | city        |
+------------------------------+------------------+-------------+-----------+-----------+---------+-------------+
| A Touch of Frost             | R.D. Wingfield   | BBC         | cop show  |      1992 |    2010 | Denton      |
| Bang                         | Roger Williams   | S4C         | cop show  |      2017 |    2020 | Port Talbot |
| Columbo                      | Richard Levinson | NBC         | detective |      1968 |    1978 | L.A.        |
| Goliath                      | David Kelley     | Amazon      | courtroom |      2015 |    2021 | L.A.        |
| Injustice                    | Anthony Horowitz | Acorn       | courtroom |      2011 |    2011 | London      |
| Line of Duty                 | Jed Mercurio     | BBC         | cop show  |      2012 |    2021 | London      |
| Mare of Easttown             | Brad Ingelsby    | S4C         | cop show  |      2017 |    2020 | Easttown    |
| No.1 Ladies Detective Agency | Richard Curtis   | BBC         | detective |      2008 |    2008 | Gaborone    |
| Second Sight                 | Paula Milne      | BBC         | cop show  |      2000 |    2001 | London      |
| The Closer                   | James Duff       | Fox         | cop show  |      2005 |    2012 | L.A.        |
| Thou Shalt Not Kill          | Claudio Carbucci | PBS         | cop show  |      2015 |    2016 | Turin       |
| Wallander                    | Henning Mankel   | Yellow Bird | detective |      2005 |    2006 | Ystad       |
| Anatomy of a Scandal         | Melissa Gibson   | Netflix     | courtroom |      2022 |    2022 | London      |
| The Tower                    | Pat Harbinson    | BBC         | detective |      2022 |    2024 | London      |
| Blue Lights                  | Declan Lawn      | BBC         | cop show  |      2023 |    2024 | Belfast     |
+------------------------------+------------------+-------------+-----------+-----------+---------+-------------+

LOCATIONS
+-------------+------------+---------------+
| city        | country    | main_language |
+-------------+------------+---------------+
| Denton      | UK         | English       |
| Easttown    | USA        | English       |
| Gaborone    | Botswana   | English       |
| L.A.        | USA        | English       |
| London      | UK         | English       |
| Port Talbot | UK         | Welsh         |
| Turin       | Italy      | Italian       |
| Ystad       | Sweden     | Swedish       |
| Belfast     | N. Ireland | English       |
+-------------+------------+---------------+

Here's what the landing page looks like:

And here's my "add data" page:

Extra credit opportunities

The basic requirements above will get you +40XP if implemented flawlessly. If you're looking for a greater challenge, or simply some more XP, add either or both of the following features as well:

For an additional +10XP: turn your table headings into links, so that when the user clicks on a column heading, the page refreshes and the table becomes sorted (in ascending order) by that column. (For yet an additional +5XP on top of that, make it so if you click on the same column header two times in a row, the second click will reverse-sort the table by the column — i.e., in descending order).
For an additional +10XP: turn the primary keys of the HTML table into clickable hyperlinks. When the user clicks on one of them (for instance, on the words "Mare of Easttown") they will be presented with a page that displays the current values for that row in an HTML form, and lets the user edit the values and save the changes.

Turning it in

Send me an email with subject line "CPSC/DATA 350 Assignment #6 turn-in". In the body of the email, simply In the body of the email, simply paste the link to the landing page of your website.

Getting help

Come to office hours, or send me email with subject line "CPSC/DATA 350 Assignment #6 help!!"

Assignment #6

The DB World Tour®, stop #1 of 4: MariaDB SQLite