Lab work is assumed to be done with Linux using most of the time the command-line or Python. If you are lost for technical reasons please ask for help. The goal of the lectures is not to train you with command line scripting.

Databases interactions

Most of databases have support to be integrated into many environement:

• Programming languages
• Command line interface
• Dedicated graphical interfaces

In most of the case, the interaction itself is done purely in SQL and the interface is mostly a way to simplify some of the work.

A database are often remote and we connect to them as they have a client/server layout. It is designed to be shared among many processes. A notable exception is SQLite which is a uniq file-based database.

SQLite

To create a Database we can simply execute query and provides it to the SQLite3 programs.

echo "SELECT 1" | sqlite3

1

Or we can precise a file to store data more permanently:

echo "
    CREATE TABLE Edges(x,y); --- It is untyped in SQLite
    INSERT INTO Edges(x,y) VALUES (0,1), (1, 2), (2,3);
" | sqlite3 /tmp/ex.db

Parse error near line 2: table Edges already exists
  CREATE TABLE Edges(x,y); --- It is untyped in SQLite
               ^--- error here
Runtime error near line 3: attempt to write a readonly database (8)

Then you can execute queries on this file:

echo "
    SELECT E1.x, E2.y FROM Edges as E1, Edges as E2 WHERE E1.y = E2.x;
" | sqlite3 /tmp/ex.db

0|2
0|2
1|3
1|3
0|2
0|2
1|3
1|3

You can also execute it in Python if you want:

import sqlite3
db = sqlite3.connect("/tmp/ex.db")
print(list(db.execute("SELECT E1.x, E2.y FROM Edges as E1, Edges as E2 WHERE E1.y = E2.x")))

[(0, 2), (0, 2), (1, 3), (1, 3), (0, 2), (0, 2), (1, 3), (1, 3)]

You can also use sqlite3 as in interactive command line interface (very much like psql) just by typing sqlite3 ex.db or use a graphic inteface as dbbrowser (if installed).

Exercice

Download the database here which is stolen from Sławek Staworko You can explore the table definitions with the SQLite specifics command .schema:

echo '.schema' | sqlite3 disques.db

CREATE TABLE Artiste(
   ID INTEGER PRIMARY KEY,
   Nom TEXT
);
CREATE TABLE Disque (
  CDDB         TEXT PRIMARY KEY, 
  ID_Artiste   INT, 
  Titre        TEXT, 
  Annee_Sortie INTEGER,
  Prix         FLOAT,
  Etat         TEXT
);
CREATE TABLE Genre (
  ID   INTEGER PRIMARY KEY,
  Nom  TEXT,
  Description TEXT
);
CREATE TABLE Chanson (
   ID          INTEGER,
   Titre       TEXT,
   Texte_Libre BOOL,
   ID_Genre    INTEGER REFERENCES Genre(ID)
);
CREATE TABLE Ecrit (
  ID_Artiste INTEGER REFERENCES ARTISTE(ID),
  ID_Chanson INTEGER REFERENCES CHANSON(ID)
);
CREATE TABLE Contient (
  CDDB_Disque TEXT REFERENCES Disque(CDDB),
  Numero INTEGER,
  ID_Chanson INTEGER REFERENCES Chanson(ID),
  ID_Artiste INTEGER REFERENCES Artiste(ID)
);

Gives the queries for the following tasks:

• Display all artists name
• Find all disques of Enrico Macias
• Find all genres of all songs of Enrico Macias
• Find all disques published in 1992

Reads about Group By operations on the documentation of SQLite webpage and display the number of disque by Artiste.

PostgreSQL/psql presentations

To connect to PostgreSQL

psql -h postgresql-edu.priv.centralelille.fr -U first_name

To not remember the URL you can use:

export PGHOST=postgresql-edu.priv.centralelille.fr
export PGUSER=YOUR_FIRST_NAME

and then, simply type

psql 

Exercice

• Find the documentation of PostgreSQL
• Read about the function generate_series seen during the lecture
• Reproduce the random graphs with 1000 edges at most.

In Python:

• Construct a simple implementation that fetch triangles from Graph given as a set of edges
• Load the graph from the database into Python
• Compare the time of execution of your simple implementation with the time taken to PostreSQL.

Try to improve your implementation if you can.

Compiled the: dim. 07 janv. 2024 23:18:57 CET