Databases project

1 Introduction and technologies

1.1 Introduction: staticity with HTML and CSS

https://developer.mozilla.org/en-US/docs/Learn/Getting_started_with_the_web/HTML_basics

Make sure you get

You can use id to identify elements of the page such as

<p id="thepar">The paragraph</p>

1.1.1 Being pretty and responsiveness

CSS stylesheets can be imported in the head section,

<link rel="stylesheet" href="style.css">

1.1.2 Exercise

Make your webpage look like a LaTeX document. For this, use the KaTeX rendering by adding

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/katex@0.11.1/dist/katex.min.css" integrity="sha384-zB1R0rpPzHqg7Kpt0Aljp8JPLqbXI3bhnPWROx27a9N0Ll6ZP/+DiW/UqRcLbRjq" crossorigin="anonymous">

<!-- The loading of KaTeX is deferred to speed up page rendering -->
<script defer src="https://cdn.jsdelivr.net/npm/katex@0.11.1/dist/katex.min.js" integrity="sha384-y23I5Q6l+B6vatafAwxRu/0oK/79VlbSz7Q9aiSZUvyWYIYsd+qj+o24G5ZU2zJz" crossorigin="anonymous"></script>

<!-- To automatically render math in text elements, include the auto-render extension: -->
<script defer src="https://cdn.jsdelivr.net/npm/katex@0.11.1/dist/contrib/auto-render.min.js" integrity="sha384-kWPLUVMOks5AQFrykwIup5lo0m3iMkkHrD0uJ4H5cjeGihAutqP0yW0J6dpFiVkI" crossorigin="anonymous"
  onload="renderMathInElement(document.body);"></script>

at the top of the head section.

Centre the text of the body in your stylesheet. Provide a way to declare definitions, whose text will be upright, and propositions whose text will be in italic.

1.1.3 References

1.1.4 Using CDNs

Content can be loaded from a third party provider. See for instance https://cdnjs.com to search for available stylesheets or fonts. Visit https://getbootstrap.com to get it in your project

1.2 Towards dynamicity

1.2.1 Install and setup

apt install libpq-dev
pip3 install django psycopg2
django-admin startproject <a_name>

To test,

cd <a_name>
python3 manage.py runserver

then head to http://localhost:8000

1.2.2 More setup

We consider that we are at the root of the project.

python3 manage.py startapp <an_app_name>

Add into an_app_name/views.py

from django.http import HttpResponse
def index(request):
    return HttpResponse("Youhou")

Then add into an_app_name/urls.py

from . import views
urlpatterns = [path('', views.index, name='index')]

and into a_name/urls.py

from django.urls import include
urlpatterns = [
    path('an_app/', include('an_app_name.urls')),
    path('admin/', admin.site.urls),
]

and go to http://localhost:8000/an_app/

1.2.3 Having a richer page

We introduce here the mechanism of templates which basically allow to introduce algorithmic instructions into HTML.

Traditionally, it comes from PHP where the following could be done,

<li>
<?php
for($i = 0; $i < $n; $i++) {
  print("<ul>The $i th item</ul>")
}
?>
</li>
}

where the <?php ?> delimit PHP code.

We start by creating a static template (not very interesting),

mkdir templates/an_app_name/

Add in the file templates/an_app_name/index.html

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <title>The title page</title>
  </head>
  <body>
    <p>The page</p>
  </body>
</html>

edit a_name/settings.py to have the correct DIRS variable,

TEMPLATES = [
    {
        'BACKEND': 'django.template.backends.django.DjangoTemplates',
        'DIRS': ['templates'],
        'APP_DIRS': True,
        'OPTIONS': {
            'context_processors': [
                'django.template.context_processors.debug',
                'django.template.context_processors.request',
                'django.contrib.auth.context_processors.auth',
                'django.contrib.messages.context_processors.messages',
            ],
        },
    },
]

and replace the content of an_app_name/views.py by

from django.shortcuts import render

def index(request):
    return render(request, 'an_app_name/index.html', {})

and head back to http://localhost:8000/an_app

1.3 Dynamic pages

Now we add control instructions to the html, in python.

The last argument of render contains an environment to pass to the template. Add the line

<p>The value is:<b>{{variable}}</b></p>

For the syntax of templates, see here In short, python variables are between double braces {{x}} and control instructions are between {% %} such as

<ul>
{% for i in values %}
  <li>The list item number {{i}}</li> 
{% endfor %}
</ul>

and pass {"values": range(10)} into the context (3rd argument of render).

1.3.1 Exercise

1.4 References and documentation

2 Exchanging data between the server and the user

2.1 In general

2.1.1 GET

The data is in the URI, example, https://musicbrainz.org/search?query=Genesis&type=artist&method=indexed where the data are key=val elements separated by &.

The result can be saved, cached, shared &c.

2.1.2 POST

The data is in the body of the message.

POST requests can be sent through html forms,

<form action="res/url" method="post">
  <label for="who">Enter text: </label>
  <input id="who" type="text" name="whosfield" value="default name">
  <input type="submit" value="OK">
</form>

where action is the resource loaded on submission and method is how the data is communicated (post or get). The action can be left empty to submit the data on the same page.

2.2 In Django

Remember the views?

def index(request):
    render(request, 'file', context)

Their request contains the POST data. The method attribute contains the request method ('POST' or 'GET'), and, if method is 'POST', the attribute POST (obtained with request.POST) contains data.

2.2.1 Try it

In views.py, in the body of index, before render, add

print(request.GET)

then perform a GET request on your main page and have a look at the terminal where you launched Django.

2.2.2 An exercise

Write an html form to add a new theorem. It can contain the fields

And write another one to add definitions.

Try to use buttons, sliders, &c. not only text entries!

3 PostgreSQL and python

3.1 Some SQL

We start by creating a database representing exercise 1 of TD1 in SQL.

3.1.1 Creating the database

To start with, create a database with

createdb dvd

3.1.2 Creating entities

To create tables, enter the REPL with psql dvd

CREATE TABLE films (
titre       varchar(80),
realisateur varchar(80), -- with some comments
sortie      date
);

For a list of available data types, see https://www.postgresql.org/docs/9.5/datatype.html

and to populate the table,

INSERT INTO films VALUES ('A Touch of Zen', 'King Hu', '1971-11-18');

to verify,

SELECT * FROM films;

and if you’re unhappy,

DROP TABLE films;

3.1.3 Keys

You can specify the primary key of the elements of a table using

titre varchar(80) primary key,

We can also use foreign keys to enforce the existence and uniqueness of a couple of elements. For instance, to make entries depend on a film,

CREATE TABLE support (
film varchar(80) references film(titre),
...
);

3.1.4 From the EA model to the database

To transform the EA model to a database schema, you may refer to https://web-engineering.info/book/InformationManagement/MakingTableModels.html

To summarise,

3.1.5 Inheritance, part II

Although we have seen in From the EA model to the database how to eliminate sub-typing, it can also be handled using the inheritance feature of PostgreSQL.

The (comprehensive) manual gives as example,

CREATE TABLE cities (
  name       text,
  population real,
  altitude   int
);
CREATE TABLE capitals (
  state  char(2),
) INHERITS (cities);

which makes any row of capitals have all the attributes of cities.

3.1.5.1 Tip

The SQL commands can be entered in a text file (with a ‘.sql’ extension) and then loaded to create the database with

psql -a -f <file> -d <database_name>

3.2 Using python

We will use python with the psycopg2 library (which should be already installed, if not: pip3 install psycopg2).

The principle is to open a connection to the database and then use a function that transmits SQL queries as strings to the database,

import psycopg2 as pg
conn = pg.connect("dbname=dvd")
cur = conn.cursor()
cur.execute("CREATE TABLE acteurs (nom varchar(80) PRIMARY KEY);")
cur.execute("INSERT INTO acteurs (nom) VALUES (%s)", ("Hsu Feng", ))
cur.execute("SELECT * FROM films;")
a = cur.fetchone()
print(a)
conn.commit()
cur.close()
conn.close()

Note that we pass the value "Hsu Feng" to the command via a placeholder %s so that the function cur.execute sanitises the command to avoid SQL injections.

Note if you can’t use PostgreSQL, you can fall back on SQLite which does not involve a server. The python standard library includes the sqlite3 module which is used similarly to the psycopg2 module.

Instead of creating a database with psql, just create a file dvd.db, and open it in python with

import sqlite3
conn = sqlite3.connect("dvd.db")

3.3 References

3.4 Exercises

3.4.1 Schemes

Translate the schemes seen in TD.

3.4.2 Continuing An exercise

Modify your template created previously to save items entered by the user into a database.

4 Sessions

When a user logs in a website, a session is started. Sessions provide states to the HTTP protocol which itself is stateless. While sessions used to be materialised by cookies, Django provides more secure mechanisms. The session is represented as an attribute of the request of the view.

For more details, see https://developer.mozilla.org/en-US/docs/Learn/Server-side/Django/Sessions.

5 AJAX

AJAX is mainly used to interact with the server without reloading the web-page. They consist approximately in making HTTP requests from javascript.

As usual, for more details, see https://developer.mozilla.org/en-US/docs/Web/Guide/AJAX/Getting_Started