Databases project

1 Introduction and technologies

1.1 Introduction: staticity with HTML and CSS

Mozilla tutorial

Make sure you understand

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

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

CSS stylesheets can be imported in the head section,

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

1.1.1 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.

HTML elements and their attributes

1.1.2 Using CDNs (optional)

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

In this part, we’ll see how to make the server compute the page on demand. We’ll see two ways to do that, the first uses the lighttpd web server with PHP as the programming language used to produce pages through the CGI specification. The second method uses Django, a popular web framework using the python programming language.

1.2.1 Using PHP, lighttpd

1.2.1.1 Standard installation

See https://redmine.lighttpd.net/projects/lighttpd/wiki/GetLighttpd. Standard installations usually provide a sufficient configuration file.

1.2.1.2 Installation without root access

This procedure compiles programs from source and install them in the $HOME directory.

php:

curl http://www.lsv.fr/~hondet/resources/db-project/install-php.sh | sh

this command will install PHP 8.0.1.

lighttpd:

curl http://www.lsv.fr/~hondet/resources/db-project/install-lighttpd.sh | sh

This script will install lighttpd in $HOME/.local. The binary will be located at $HOME/.local/sbin/lighttpd, and some configuration files at $HOME/.local/etc/lighttpd.

Note: Add $HOME/.local/sbin to the $PATH variable to be able to call lighttpd without its full path.

A configuration file for lighttpd is then required at $HOME/.local/etc/lighttpd/lighttpd.conf. The following one may be used,

server.document-root = "<HOME>/www/"
server.port = 8080 # 80 is reserved and requires root access
server.modules = ( "mod_access", "mod_fastcgi", "mod_auth", "mod_accesslog" )

# Log everything
debug.log-request-header = "enable"
debug.log-response-header = "enable"
debug.log-request-handling = "enable"
debug.log-file-not-found = "enable"
debug.log-condition-handling = "enable"

# Logging locations
server.errorlog = "<HOME>/.local/var/log/lighttpd/error.log"
accesslog.filename = "<HOME>/.local/var/log/lighttpd/access.log"

dir-listing.activate = "enable"
index-file.names += ( "index.html", "index.php" )
static-file.exclude-extensions = ( ".php", ".py" )

include "conf.d/mime.conf"

# PHP configuration
fastcgi.server = ( ".php" =>
  (
    ( "socket" => "/tmp/php.socket",
      "bin-path" => "<HOME>/.local/bin/php-cgi",
      "bin-environment" => (
        "PHP_FCGI_CHILDREN" => "16",
        "PHP_FCGI_MAX_REQUESTS" => "10000" ),
  "min-procs" => 1,
  "max-procs" => 1,
  "idle-timeout" => 20 ) ) )

Errors are logged in ~/.local/var/log/lighttpd/error.log.

1.2.1.3 Testing

Let <www> be the root repository of the web server, so either $HOME/www Installation without root access has been followed; and probably /var/www/html/ otherwise (the path depends on the distribution).

To test the setup, write the following into <www>/index.php

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <title>HTML is processed</title>
  </head>
  <body>
    <p>The page</p>
    <?php echo "PHP is processed as well" ?>
  </body>
</html>

Launch the server with

~/.local/sbin/lighttpd -f ~/.local/etc/lighttpd/lighttpd.conf

or

sudo lighttpd

and go to http://localhost:8080 or http://localhost:80, where you should see the two lines “HTML is processed” and “PHP is processed as well”.

1.2.2 Install and setup, Django

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

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 Templates and pages

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

In PHP, text is by default interpreted as HTML, except if enclosed between <?php <instructions> ?> where <instructions> will be interpreted as PHP code, and the output of the instructions are inserted in the page. For instance, a list of $n items may be printed with a for loop with

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

In Django, things are a bit more complicated. We start by creating a static template,

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

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, Django documentation. 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.2.4 Exercise

2 Exchanging data between the server and the user

Up to now, data is only sent from the server to the user. The opposite can be achieved through HTML forms, but there are two techniques to send data, the GET and the POST methods.

2.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.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.3 Accessing data

In PHP, the server has access to the data of a GET or POST request through the $_GET and $_POST variables. In the above example, $_POST['who'] is defined and contains the value input by the user.

In Django, this information is encoded into the request given to the views:

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

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

To try using requests in Django, add , add

print(request.GET)

in views.py, in the body of index, before render; then perform a GET request on your main page and have a look at the terminal where you launched Django.

2.4 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

3.1 Installation

Afterwards, the PostgreSQL tutorial may be a good way to start using PostgreSQL.

3.2 Some SQL

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

3.2.1 Creating the database

To start with, create a database with

createdb dvd

3.2.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.2.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.2.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.2.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.2.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.3 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.4 References

3.5 Exercises

3.5.1 Schemes

Translate the schemes seen in TD.

3.5.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, and allow to keep information between access of the website.

For more information on sessions in PHP, see PHP’s documentation (french).

In Django 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