SQLite¶

Qu’est ce que `SQLite` ?¶

SQLite (prononcé [ɛs.ky.ɛl.ajt]) est une bibliothèque écrite en C qui propose un moteur de base de données relationnelle accessible par le langage SQL. SQLite implémente en grande partie le standard SQL-92 et des propriétés ACID.

Important

Contrairement aux serveurs de bases de données traditionnels, comme MySQL ou PostgreSQL, sa particularité est de ne pas reproduire le schéma habituel client-serveur mais d’être directement intégrée aux programmes. L’intégralité de la base de données (déclarations, tables, index et données) est stockée dans un fichier indépendant de la plateforme.

La simplicité de ce SGBDR en fait le parfait candidat pour des sites web avec peu de données.

Propriétés ACID¶

En informatique, les propriétés ACID sont un ensemble de propriétés qui garantissent qu’une transaction informatique est exécutée de façon fiable.

Dans le domaine des bases de données, une opération sur les données est appelée une transaction ou transaction informatique.

Exemple

Un transfert de fonds d’un compte de banque à un autre, même s’il implique plusieurs actions comme le débit d’un compte et le crédit d’un autre, est une seule transaction.

Elles sont au nombre de 4 pour chaque lettre de l’acronyme:

Atomique: la suite d’opérations est indivisible, en cas d’échec en cours d’une des opérations, la suite d’opérations doit être complètement annulée (rollback) quel que soit le nombre d’opérations déjà réussies.
Cohérente: le contenu de la base de données à la fin de la transaction doit être cohérent sans pour autant que chaque opération durant la transaction donne un contenu cohérent. Un contenu final incohérent doit entraîner l’échec et l’annulation de toutes opérations de la transaction.
Isolée: lorsque deux transactions \(A\) et \(B\) sont exécutées en même temps, les modifications effectuées par \(A\) ne sont ni visibles par \(B\), ni modifiables par \(B\) tant que la transaction \(A\) n’est pas terminée et validée (commit).
Durable: Une fois validé, l’état de la base de données doit être permanent, et aucun incident technique (exemple: crash) ne doit pouvoir engendrer une annulation des opérations effectuées durant la transaction.

Historique de SQLite¶

Ce moteur est développé depuis 2000 D. Richard Hipp.

La Bibliothèque ainsi que son code source sont sous la licence du domaine public.

Note

Sa particularité est de ne pas reproduire le schéma habituel client-serveur mais d’être directement intégrée aux programmes.

L’intégralité de la base de données (déclarations, tables, index et données) est stockée dans un fichier indépendant de la plateforme.

Il s’agit d’un des moteurs de bases de données les plus utilisés au monde, celui-ci est notamment utilisé par Firefox, Skype, Google Gears, Android, iOS, des sites web, etc.

Une des raisons de son succès est qu’elle est intégrée dans les bibliothèques standards de nombreux langages comme Python, Ruby, PHP, C#, C++, etc.

De plus son extrême légèreté (moins de 600 Kio), la rende populaire sur les systèmes embarqués.

La première version de SQLite est publiée en août 2000, celle-ci utilise gdbm (GNU Database Manager) pour manipuler des arbres. Par la suite SQLite 2.0 supprime la dépendance à gdbm et ajoute le support des transactions. Enfin SQLite 3.0, produite avec l’aide d’AOL, est publiée en 2004.

Caractéristiques de SQLite¶

Base de données embarquée

La majorité des SGBD sont construit selon l’architecture Client-Serveur, SQLite s’intégre directement à l’applicatif.

L’accès à une base de données avec SQLite se fait par l’ouverture du fichier correspondant à celle-ci. Chaque base de données est enregistrée dans un fichier qui lui est propre, avec ses déclarations, ses tables et ses index mais aussi ses données.

Indication

Il n’y a pas d’extension propre aux fichiers de base de données de SQLite, il est courant de rencontrer des extensions comme .sqlite ou .db, parfois suivie du numéro de version de la bibliothèque (.sqlite3, .db2, etc.).

Notons qu’il est possible d’utiliser une base de données uniquement sauvegardée en mémoire vive, sans créer de fichier de base de données sur le disque, via le nom de fichier spécial :memory:.

Gestion des droits

Avertissement

Il n’existe aucune gestion des droits d’accès et de modification des données. Cela est déléguée au système de fichiers du système d’exploitation.

La gestion des droits avec les instructions GRANT et REVOKE est donc inexistante, bien que ceux-ci fassent partie de la spécification SQL-9210. Ainsi l’utilisation d’une base SQLite ne nécessite aucune procédure d’installation ou de configuration.

Portabilité

Étant entièrement écrite en C-ANSI, la version normalisée du langage de programmation C, celle-ci est compilable sans modification sur toutes les architectures,

Important

Ainsi les fichiers de base de données SQLite sont entièrement indépendants du système d’exploitation et de l’architecture.

Gestion des types de données

Celle-ci se base sur l’utilisation d’un typage dynamique pour le contenu des cellules, contrairement à la quasi-totalité des SGBD qui utilisent un typage statique.

Lors de la création de la table on ne peut que renseigner un type d’affinité. La convertion des données vers le type si possible se fait au moment de la saisie des données.

Type de données¶

Il existe plusieurs types d’affinité dans SQLite, définissant la façon dont celui-ci va travailler lors de l’entrée des nouvelles données.

TEXT
NUMERIC
INTEGER
REAL
NONE

TEXT: Permet d’enregistrer la donnée comme une chaine de caractères, sans limite de taille. Si un nombre est entré dans une colonne de ce type, il sera automatiquement converti en une chaine de caractères.
NUMERIC: Tente d’enregistrer la donnée comme un entier ou comme un réel, si cela s’avère impossible la donnée sera enregistrée comme une chaine de caractères.
INTEGER: Enregistre la donnée comme un entier si celle-ci peut être encodée sans perte, mais peut utiliser les types REAL ou TEXT si ça ne peut être fait.
REAL: Enregistre la donnée comme un réel, même s’il s’agit d’un entier. Si la valeur est trop grande la donnée sera convertie en chaine de caractères.
NONE: La donnée est enregistrée telle quelle sans conversion.

Et les dates ?

Il n’existe pas de type pour représenter des dates bien qu’un ensemble de fonctions existe pour manipuler celles-ci. La convention étant de les stocker sous la forme d’une chaine de caractères ou dans un entier sous la forme d’un timestamp UNIX.

Les contraintes¶

La gestion des contraintes est possible et se fait sur une ou plusieurs colonnes.

Exemple de contraintes déclarées sur une colonne¶

NULL
NOT NULL
DEFAULT

Exemple de contraintes pouvant être déclarés sur une ou plusieurs colonnes:¶

PRIMARY KEY
UNIQUE
FOREIGN KEY

Indication

La contrainte UNIQUE crée automatiquement un index sur la ou les colonnes sur lesquelles elle est appliquée.

Contraintes plus complexe¶

Les contraintes CHECK nous permettent de définir des vérifications supplémentaires sur les données au-delà de UNIQUE ou de NOT NULL.

Syntaxe sur une colonne¶

CREATE TABLE table_name(
   ...,
   column_name data_type CHECK(expression),
   ...
);

Syntaxe au niveau de la table¶

CREATE TABLE table_name(
   ...,
   CHECK(expression)
);

Exemple¶

CREATE TABLE contacts (
   contact_id INTEGER PRIMARY KEY,
   first_name TEXT    NOT NULL,
   last_name  TEXT    NOT NULL,
   email      TEXT,
   phone      TEXT    NOT NULL CHECK (length(phone) >= 10)
);

CREATE TABLE products (
   product_id   INTEGER         PRIMARY KEY,
   product_name TEXT            NOT NULL,
   list_price   DECIMAL (10, 2) NOT NULL,
   discount     DECIMAL (10, 2) NOT NULL
   DEFAULT 0,
   CHECK (list_price >= discount AND
   discount >= 0 AND
   list_price >= 0)
);

PRIMARY KEY¶

Permet la création d’une contrainte UNIQUE sur la ou les colonnes concernées, il est de plus conseillé de rajouter la contrainte NOT NULL à la déclaration d’une clé primaire.

ROWID¶

Chaque ligne d’une table est identifiée par un entier signé de 64 bits appelé ROWID. Une colonne ayant été déclaré en INTEGER PRIMARY KEY est un alias du ROWID.

Si la table est vide le premier identifiant prendra comme valeur 1 lors de l’insertion de du premier enregistrement. Celui-ci sera incrémenter pour chaque enregistrement jusqu’à la limite d’un entier signé de 64 bits (\(2^{63} - 1\)).

AUTOINCREMENT¶

Le mot clé AUTOINCREMENT modifie légèrement l’algorithme d’attribution de la clé primaire. Une fois la limite d’un entier atteinte il ne sera plus possible d’insérer un nouvel enregistrement.

Ceci permet de garantir qu’un même identifiant ne sera jamais porté par deux enregistrements distincts, même s’ils ne coexistent pas à un même moment.

FOREIGN KEY¶

La version 3 a apporté le support et la gestion des contraintes de clé étrangère depuis la version 3. Toutefois celle-ci est désactivé par défaut. Toute colonne référencée par une clé étrangère doit être déclarée comme UNIQUE.

Il est possible de l’activer via le pragma suivant:

PRAGMA foreign_keys;

Pragma¶

L’instruction PRAGMA est une extension SQL de SQLite et permet de modifier le fonctionnement de la bibliothèque SQLite. Nous aurons l’occasion de détailler leurs fonctionnement dans la suite de ce document.

Déclencheurs¶

SQLite intégre la gestion des déclencheurs:

BEFORE
AFTER
INSTEAD OF

Vue¶

Note

Une vue est une synthèse d’une requête d’interrogation de la base. Il s’agit d’une sorte de table virtuelle, définie par une requête.

Et évideement, SQLite intégre le support des vues.

Transaction¶

Toutes les instructions hormis le SELECT implique la création d’une transaction lors du requêtage sur la base. Les instructions sont ainsi toutes atomiques.

Comment utiliser SQLite ?¶

SQLite embarque une interface en ligne de commande (CLI) simple et pratique pour créer et requêter sur une base de données SQLite. Son utilisation se fait simplement par la commande sqlite3

Note

Une interface en ligne de commande (en anglais command line interface, couramment abrégé CLI) est une interface homme-machine dans laquelle la communication entre l’utilisateur et l’ordinateur s’effectue en mode texte au sein d’un terminal (console).

$ sqlite3 -version
3.26.0 2018-12-01 12:34:55 bf8c1b2b7a5960c282e543b9c293686dccff272512d08865f4600fb58238alt1

Quelques commandes utiles¶

.exit       --Permet de quitter la commande CLI
.table      --Liste l'ensemble des tables de la base
.dbinfo     --Information sur la base
.dump       --Obtenir un dump sql de la base
.schema     --Obtenir le code de création de la base
.headers (on, off) --Affichage ou non des entêtes
.mode (list, column, csv, ..) --Affichage en liste, etc.
.version    --Information de version
.import     --Import de données dans la base

Pour se connecter à une base de données SQLite, il suffit d’indiquer le nom du fichier de base de données en argument de la commande sqlite3

$ sqlite3 boardgames.sqlite3

Attention

Si le fichier existe alors la commande ouvre la base de données sinon celle-ci est créée avant ouverture.

Exemple d’utilisation¶

$ sqlite3 boardgames.sqlite3
SQLite version 3.26.0 2018-12-01 12:34:55
Enter ".help" for usage hints.

sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;

sqlite> .version
SQLite 3.26.0 2018-12-01 12:34:55 bf8c1b2b7a5960c282e543b9c293686dccff272512d08865f4600fb58238alt1
zlib version 1.2.11
gcc-8.3.1 20190223 (Red Hat 8.3.1-2)

sqlite> .exit

$

Indication

Voici quelques astuces utiles avec la commande sqlite3 à faire après chaque lancement de la commande:

$ sqlite3 boardgames.sqlite3
sqlite> .headers on -- Display column name
sqlite> .mode columns -- Change display mode, try it
sqlite> .tables -- Show all db tables
sqlite> PRAGMA table_info(nom_table); -- Show structure of nom_table

SQLiteBrowser¶

Astuce

Nous pouvons utiliser un utilitaire, SQLiteBrowser , en mode GUI pour créer une base de données de type SQLite. Il s’agit d’un logiciel permettant de créer et de manipuler très simplement des BDD SQLite.

Lancer DB Browser for SQLite puis cliquer sur New Database, sauvegarder la dans le dossier data de votre site web sous le nom data.sqlite.

Lors de la création de votre BDD, vous êtes invité à créer une première table qui pourrait ressembler à ceci:

Enfin ajouter quelques enregistrements dans cette table. Avant de quitter le logiciel n’oubliez pas de sauvegarder votre base via Write Changes.

Un peu de pratique avec quelques exemples¶

🛬 Aéroports¶

Note

Un aéroport est l’ensemble des bâtiments et des installations qui servent au traitement des passagers ou du fret aérien situés sur un aérodrome. Le bâtiment principal est, généralement, l’aérogare par où transitent les passagers (ou le fret) entre les moyens de transport au sol et les avions.

Nous allons au cours de cet exercice utiliser une base de données d’aéroport autour du monde. Cette base de donnée a été compilé à partir de plusieurs sources du domaine public dont un listing se trouve sur le site http://ourairports.com/data/ <http://ourairports.com/data/>.

La base de donnée « airport »¶

Ci-dessous le schéma UML de la base de données airport

@startuml

scale 2.5
skinparam backgroundcolor transparent
skinparam defaultFontName Hack
left to right direction

class Airports {
{field} id: integer
{field} ident: string
{field} type: string
{field} name: string
{field} elevation: integer
{field} continent: string
{field} iso_country: string
{field} iso_region: string
{field} municipality: string
{field} gps_code: string
{field} iata_code: string
{field} local_code: string
{field} coordinates: string
}

hide methods
hide circle

@enduml — Diagramme UML de la base de données airport¶

Point culture

Un code d’aéroport peut faire référence au code IATA, un code à trois lettres utilisé dans les systèmes de réservation de billets et de traitement des bagages. Ou au code OACI, qui est un code à quatre lettres utilisé par les systèmes ATC et les aéroports n’ayant pas de code IATA.

Base de données des aéroport en Gréce¶

Attention

La base de données comptant plus de 57 000 aéroports, nous utiliserons dans un premier temps une partie de cette base de données ne contenant que les aéroport de Gréce.

Quelques rappels sur les commandes SQLite

Ouvrir une connexion sur une base de données SQLite¶

$ sqlite3 airport_greece.sqlite3

Afficher les colonnes lors du retour des requêtes¶

sqlite> .headers on

Changer le mode d’affichage du retour des requêtes¶

sqlite> .mode columns

Afficher la liste des tables¶

sqlite> .tables

Afficher la structure d’une table¶

sqlite> PRAGMA table_info(nom_table);

Exercice sur les aéroports de Gréce¶

Avant de vous lancer dans cet exercice pensez à télécharger la base de données des aéroports de Gréce en cliquant ici.

Important

Pour chaque question pensez à écrire la version de la requête en algèbre relationnel puis en SQL que vous sauvegarderez dans un fichier avec le résultat de chaque appel.

À faire

Lister les noms des aéroports,
Lister les régions,
Lister les municipalités,
Lister les différents types d’aéroports,
Lister les aéroports avec leurs noms, régions et types,
Lister les régions où se trouvent un héliport,
Lister les noms, régions et municipalités des aéroports à plus de 1000m d’altitude,
Lister les noms, régions et municipalités des aéroports entre 1000m et 1600m d’altitude,
Lister les aéroports (nom, code iata et code local) qui posséde un code iata,
Lister les aéroports dont le code de région est GR-U-A,
Lister les aéroports dont le code de région est GR-U-A avec une altitude supérieur à 1,
Lister les aéroports dont le nom commence par la lettre « M »,
Lister les aéroports dont l’identification commence par « GR »,
Lister les aéroports dont l’identification commence par « LG »,
Lister les aéroports qui sont fermées,
Lister les aéroports d’Athénes,
Lister les aéroports dont la ville commence par la lettre A.

🃏 Keyforge¶

Note

KeyForge est un JCE à Deck unique créé par Richard Garfield (le papa de Magic!) et publié par Fantasy Flight Games.

Dans ce jeu, les joueurs jouent le rôle d’Archontes dans le monde du Creuset. Le premier joueur à collecter suffisamment d“« Æmber » pour forger trois clés est déclaré vainqueur.

Le jeu dans sa version de lancement comporte 371 cartes différentes.

@startuml

scale 2.5
skinparam backgroundcolor transparent
skinparam defaultFontName Hack
left to right direction
allow_mixing

class Card {
{field} id: integer
{field} name: string
{field} num: string
{field} rarity: string
{field} aember: integer
{field} power: integer
{field} armor: integer
{field} abilities: string
}

class Type {
{field} id: integer
{field} name: string
}

class Trait {
{field} id: integer
{field} name: string
}

class House {
{field} id: integer
{field} name: string
}

Card "1" -- "*" House
Card "1" -- "*1*" Type
Card "0..*" -- "*" Trait

hide methods
hide circle
@enduml — Diagramme UML de la base de données keyforge¶

Important

Pour chaque question pensez à écrire la version de la requête en algèbre relationnelle puis en SQL que vous sauvegarderez dans un fichier avec le résultat de chaque appel.

Avant de vous lancer dans cet exercice pensez à télécharger la base de données Keyforge en cliquant ici.

À faire

Lister les noms des différentes familles,
Lister les différents types de cartes,
Lister les différents types de traits,
Lister les cartes dont le nom commmence par la lettre A,
Lister les cartes dont le nom se termine par ain,
Lister les cartes dont la puissance est supérieur à 5,
Lister les cartes dont l’armure est supérieur à 3,
Lister les 10 premières cartes de la base en suffixant chaque nom de colonne par _card,
Lister les cartes dont l’identifiant est égal à 36, 80, 137, 195, 249, 298 ou 347,
Lister le nom, nom de la maison et nom du type de la carte numéro 36 en effectuant une jointure entre les tables cards, types et houses; et en suffixant chaque champ par le nom de la table d’origine,
Lister le nom, nom de la maison et nom du type des cartes numéro 36, 80, 137, 195, 249, 298 et 347 en effectuant une jointure entre les tables cards, types et houses; et en suffixant chaque champ par le nom de la table d’origine,
Lister les cartes de la famille dis dont l’armure est supérieur à 0 et la puissance à 7,
Lister les 10 premières cartes « créatures » de la base de données,
Lister les 10 premières cartes « créatures » de la maison Mars,
Lister les artefacts de la famille Logos,
Lister les actions de la famille Dis,
Lister les 10 premières cartes « créatures » dont la puissance est supérieur à 7.

🃏 Keyforge Le Retour¶

Important

Pour chaque question pensez à écrire la version de la requête en algèbre relationnelle puis en SQL que vous sauvegarderez dans un fichier avec le résultat de chaque appel.

Avant de vous lancer dans cet exercice pensez à télécharger la base de données Keyforge en cliquant ici.

À faire

Combien y’a t’il de familles différentes,
Combien y’a t’il de types de cartes différentes,
Combien y’a t’il de cartes,
Combien y’a t’il de cartes qui commence par la lettre A,
Quelle est la carte avec la puissance la plus élevée dans la famille brobnar,
Quelle est la moyenne de puissance des cartes « créatures » de la famille mars,
Quelle est la carte avec le moins de puissance dans la famille brobnar,
Combien de cartes y a t’il par maison,
Combien de cartes Artefact y’a t’il par maison,
Combien de cartes Créatures y’a t’il par maison,
Combien de cartes Action y’a t’il par maison,
Quelle est la somme des puissances de la famille mars,
Quelle est la somme des puissances de la famille brobnar,
Combien de cartes font gagner une Æmber,
Combien de cartes font gagner deux Æmber,
Combien de cartes font gagner une ou deux Æmber,
Combien de cartes font gagner une ou deux Æmber par maison,
Combien de cartes possédent une armure,
Combien de cartes possédent une armure par famille,
Combien de cartes par niveau de rareté.

🦕 InGen¶

Note

La société InGen; spécialisée dans la création de créatures préhistoriques génétiquement modifiées; souhaite créer une base de données pour son SI.

La majeure partie de son activité consiste à faire naître des dinosaures et à les présenter au sein de Jurassic Park. Les dinosaures sont définis par un nom, une date de naissance et une espèce. Une espèce est définie par un nom.

@startuml
scale 2.5
skinparam backgroundcolor transparent
skinparam defaultFontName Hack
left to right direction

class Dinosaur {
{field} id: integer
{field} name: string
{field} birthday: date
}

class Specie {
{field} id: integer
{field} name: string
{field} name_meaning: string
}

Dinosaur "*" -- "1" Specie: " Is a"

hide methods
hide circle

@enduml — Diagramme UML de la base de données d’InGen¶

Quelle est la commande permettant de lancer l’utilitaire CLI de sqlite ?
Créer la la base de données d’InGen sous le nom ingen.sqlite3.
Dans un fichier sql (schema.sql par exemple), écrivez la requête de création de la table species.
Éxécutez le fichier via le pragma read

sqlite> .read schema.sql

Attention

Pensez à activer le support des clé étrangéres via la PRAGMA foreign keys

Vérification de l’activation des clé étrangéres (ce n’est pas le cas)¶

sqlite> PRAGMA foreign_keys;
foreign_keys
------------
0

Activation et vérification de l’activation des clé étrangéres¶

sqlite> PRAGMA foreign_keys = ON;
sqlite> PRAGMA foreign_keys;
foreign_keys
------------
1

Danger

Le support des clé étrangéres doit être réactivé à chaque connexion, pensez pour cela à indiquer PRAGMA foreign_keys = ON; en haut de tout vos fichiers .sql.

Ajoutez maintenant la création de la table dinosaurs dans votre fichier et éxécutez le.

Vérifiez que les tables ont bien étés créées.

sqlite> .tables

Ajoutez une colonne location de type TEXT à la table dinosaurs.

Insérez les données suivante dans la table species

name         name_meaning
-----------  -----------------
Triceratops  Three-horned face
Stegosaurus  Roofed lizard
Tyrannosaur  Tyrant Lizard Kin
Diplodocus   Double beat

Mettez à jour l’espèce diplodocus avec comme signification de nom « Double beam » au lieu de double beat.

Insérez les données suivante dans la table dinosaurs

name        birthday    specie_id
----------  ----------  ----------
Marie       1991/12/15  1
Louis       1993/05/05  2
Freddy      1994/04/03  3
Nessie      1991/11/20  4

Important

Pour chaque question pensez à écrire la version de la requête en algèbre relationnel puis en SQL que vous sauvegarderez dans un fichier avec le résultat de chaque appel.

À faire

Lister les noms des dinosaures,
Lister les noms d’espèce et leur signification,
Lister les différents dinosaures avec leurs espèce.

🍿 Silence, Moteur, Action !¶

Note

Cet exercice basé sur un extrait d’un dataset de films a pour but de récapituler l’ensemble des notions vus jusqu’à aujourd’hui ensemble. A savoir création d’une base de donnée, insertion de données et requêtage.

La base de données est orientée autour d’une table Movie qui contient l’ensemble des informations d’un film. À savoir son titre, sa catégorie, sa durée, son année de sortie, s’il a été récompensé, sa popularité mais aussi son directeur, son acteur et actrice principaux.

La table Category contient le nom de la catégorie et la table Person, qui servira aussi bien pour les acteurs/actrices que pour les directeurs contient le nom et le prénom de la personne.

Bien évidemment chaque table posséde un champ id.

@startuml
scale 2.5
skinparam backgroundcolor transparent
skinparam defaultFontName Hack
left to right direction

class Category {
+ {field} id: integer
+ {field} name: string
}

class Person {
+ {field} id: integer
+ {field} name: string
+ {field} firstname: string
}

class Movie {
+ {field} id: integer
+ {field} title: string
+ {field} length: int
+ {field} year: int
+ {field} popularity: int
+ {field} awards: boolean
}

Movie "*" -- "1" Category: " Is of"
Movie "*" -- "1" Person: " Directed by"
Movie "*" -- "1" Person: " Is the main actor"
Movie "*" -- "1" Person: " Is the main actress"

hide circle
hide methods

@enduml — Diagramme UML de la base de données de films¶

En vous aidant des informations ci-dessus créer le schéma de base de données via DB Browser for SQLite (penser au clé étrangére).
Insérer les données dans les tables Category, Person et Movie grâce à trois requêtes d’insertion et en vous servant du fichier de données disponible ici.

À faire

Lister les nom et idenfitiant de chaque catégorie.
Combien de catégorie comporte la base, faites apparaître le résultat sous le nom total_category.
Lister les nom, prénom et identifiant des personnes dont le nom commence par S.
Lister les nom, prénom et identifiant des personnes dont le nom finit par ing.
Combien de personnes y’a t’il dans la base (total_person).
Quel est l’identifiant de Steven Spielberg.
Lister les films (titre, année de sortie, durée, identifiant) dont le titre contient star et en les ordonnant par année de sortie.
Combien de film y’a t’il dans la base (total_movie).
Quelle est l’année de sortie des aventuries de l’arche perdue (Raiders of the Lost Ark).
Lister l’ensemble des films (titre, nom de la catégorie, durée, popularité et récompense) réalisé par Steven Spielberg (attention il s’agit de faire une jointure entre les tables person et movie et non d’utiliser directement l’identifiant).
Lister les films; titre, année de sortie, durée, identifiant, nom de la catégorie, nom et prénom du réalisteur (director_name, director_firstname), nom et prénom de l’acteur et de l’actrice principaux (main_actor_name, main_actor_firstname, main_actress_name, main_actress_firstname); dont le titre contient star et en les ordonnant par année de sortie.
Quelle est la durée moyenne des films (avg_length).
Quelle est la durée moyenne des films réalisé par Steven Spielberg (avg_length, director_name, director_firstname).
Combien de film George Lucas a-t-il réalisé.
Quelle est la durée moyenne des films par catégorie (avg_length, category).
Combien de films ont reçu une récompense (total_rewarded).
Combien de films ont reçu une récompense par catégorie (total_rewarded, category).
Lister le nombre de films réalisé par directeur (total, director_name, director_firstname) dont le total est supérieur à 10 et ordonné décroissant par total.
Combien de films ont une popularité supérieur à 60 (total).
Combien de films ont été réalisé par leurs acteur principal (total).

Les vues en SQL¶

Indication

C’est un ensemble des résultats d’une requête stockée dans une entité. Elle offre la possibilité d’accéder aux données des tables sous-jacentes via la vue. Les tables auxquelles fait référence la requête dans la définition de la vue s’appellent des tables de base.

Une vue est utile dans certains cas :

Fournir une couche d’abstraction sur les tables. Ajout et suppression de colonnes dans la vue sans toucher au schéma des tables sous-jacentes.
Encapsulation des requêtes complexes afin de simplifier l’accès aux données.

Attention

Les vues sont en lecture seule. Cela signifie que vous ne pouvez pas utiliser les instructions INSERT, DELETE et UPDATE pour mettre à jour les données des tables de base via la vue.

Syntaxe de base:¶

CREATE VIEW IF NOT EXISTS view_name
AS
select-statement;

CREATE VIEW v_boardgames
AS
SELECT id, boardgame.name, category.name AS category,
FROM boardgame, category
WHERE boardgame.category_id = category.id;

Suppression d’une vue¶

DROP VIEW IF EXISTS view_name;

À faire

Créez une vue v_dinosaurs qui correspond au schéma UML ci-dessous:

@startuml

scale 2.5
skinparam backgroundcolor transparent
skinparam defaultFontName Hack
left to right direction

class VDinosaur {
{field} id: integer
{field} name: string
{field} birthday: date
{field} version: float
{field} specie: string
{field} specie_name_meaning: string
}
hide methods
hide circle

@enduml — Diagramme UML de la base de données de dinosaurs¶

SQLite¶

Qu’est ce que SQLite ?¶

Propriétés ACID¶

Historique de SQLite¶

Caractéristiques de SQLite¶

Type de données¶

Les contraintes¶

Contraintes plus complexe¶

PRIMARY KEY¶

ROWID¶

AUTOINCREMENT¶

FOREIGN KEY¶

Pragma¶

Déclencheurs¶

Vue¶

Transaction¶

Comment utiliser SQLite ?¶

SQLiteBrowser¶

Un peu de pratique avec quelques exemples¶

🛬 Aéroports¶

La base de donnée « airport »¶

Base de données des aéroport en Gréce¶

Exercice sur les aéroports de Gréce¶

🃏 Keyforge¶

🃏 Keyforge Le Retour¶

🦕 InGen¶

🍿 Silence, Moteur, Action !¶

Les vues en SQL¶

Qu’est ce que `SQLite` ?¶