Scripts et Programmation

SQLITE : L'essentiel sur cette base de données (CLI, SQL, PHP)

Table des matières

Introduction
Installation
Spécificités SQLite
SQlite en ligne de commande
Sauvegarde (dump) et restauration
1. Sauvegarder la base
2. Restauration
Interagir avec SQLite en BASH
Interagir avec SQLite en PHP

Introduction

SQLite est une bibliothèque écrite en langage 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.

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.

Installation

Pour installer SQLite :

Gentoo :

Code BASH :

emerge -av dev-db/sqlite

Fedora / CentOS / Oracle Linux :

Code BASH :

dnf install sqlite

Debian :

Code BASH :

apt install sqlite3

A partir de là, la commande sqlite3 est disponible.

Spécificités SQLite

Concernant les types de données (lors de la création des tables) :

Code TEXT :

NULL => null
INTEGER => 0 1 2 3
INT => 0 1 2 3
VARCHAR(64) => CHAINE
TEXT => TEXTE
BOOLEAN => TRUE/FALSE
DATETIME => YYYY-MM-JJ HH:MM:SS
DATE => YYYY-MM-JJ
FLOAT => -0,123, 1.2345

Concernant les commandes de base, elles commencent par un point une fois la base ouverte :

Code TEXT :

.help
.databases
.tables
.schema TABLE
.exit

Les simples quotes s'échappent avec le doublement de celles-ci.

SQlite en ligne de commande

Créer la base

Pour créer une base, il suffit de créer un fichier :

Code BASH :

sqlite3 base.sqlite

Créer et Supprimer des tables

Pour créer une table, voici un exemple, mais on utilise la commande CREATE TABLE SQL classique :

Code SQL :

CREATE TABLE message (
id INT PRIMARY KEY NOT NULL,
texte VARCHAR(64),
DATE DATETIME DEFAULT (datetime('now','localtime'))
);

Vous avez un exemple avec un entier, une clé primaire, une chaîne et une date et heure.

A noter : La valeur AUTOINCREMENT ne fonctionne qu'avec un type INTEGER + PRIMARY KEY. On a une valeur implicite nommée rowid qui s'incrémente d'office :

Code SQL :

CREATE TABLE compter (
id INTEGER PRIMARY KEY AUTOINCREMENT,
texte VARCHAR(64)
);

Pour supprimer une table :

Code SQL :

DROP TABLE message;

Manipuler les données : INSERT SELECT UPDATE DELETE

Pour insérer du contenu c'est du SQL classique :

Code SQL :

INSERT INTO message VALUES (1,'coucou', datetime('now','localtime'));
INSERT INTO message('id', 'texte') VALUES ('2','Message 2');
INSERT INTO message('id', 'texte', 'date') VALUES ('3','Date fixe', '2020-01-01 10:00:00');

Quand on a une valeur autoincrémentée, on peut ne pas renseigner le champ, il s'incrémente tout seul :

Code SQL :

INSERT INTO compter (texte) VALUES ('un');
INSERT INTO compter (texte) VALUES ('deux');
INSERT INTO compter (texte) VALUES ('trois');

Pour afficher le contenu c'est du SQL classique :

Code SQL :

SELECT * FROM message;

Code TEXT :

1|coucou|2021-01-06 13:58:19
2|Message 2|2021-01-06 13:58:19
3|Date fixe|2020-01-01 10:00:00

Code SQL :

SELECT * FROM compter;

Code TEXT :

1|un
2|deux
3|trois

Si on souhaite afficher l'ID autoincrémenté implicite de manière explicite :

Code SQL :

SELECT rowid, * FROM message;

Code TEXT :

1|1|coucou|2021-01-06 13:58:19
2|2|Message 2|2021-01-06 13:58:19
3|3|Date fixe|2020-01-01 10:00:00

A noter : Si on supprime la deuxième ligne, le rowid de la 3ème ligne reste 3.

Pour modifier le contenu ou supprimer, c'est comme du SQL classique :

Code SQL :

DELETE FROM message WHERE id=2;

Code SQL :

UPDATE message SET id=2 WHERE texte='coucou';

Idem avec la clause WHERE, les tests habituels sont fonctionnels : BEWTEEN x AND y - IN ( 'val1', 'val2') - LIKE - AND - OR - ORDER BY - GROUP BY
Même la commande LIMIT 10 limitera comme dans MySQL le nombre d'enregistrements retournés.

Pour les jointures, on pourra utiliser INNER JOIN, qui ne listera que les requêtes n'ayant d'une et d'autre part d'enregistrements NULL :

Code SQL :

SELECT * FROM message m INNER JOIN compter c ON m.id=c.id;

Code TEXT :

1|coucou|2021-01-06 13:58:19|1|un
2|Message 2|2021-01-06 13:58:19|2|deux
3|Date fixe|2020-01-01 10:00:00|3|trois

Pour lister même si la deuxième table a des enregistrements NULL, on préférera LEFT OUTER JOIN :

Code SQL :

SELECT * FROM message m LEFT OUTER JOIN compter c ON m.id=c.id;

Code TEXT :

1|coucou|2021-01-06 13:58:19|1|un
2|Message 2|2021-01-06 13:58:19|2|deux
3|Date fixe|2020-01-01 10:00:00|3|trois
4|Message 4|2021-01-07 20:49:27||

Sauvegarde (dump) et restauration

Sauvegarder la base

Il est possible de sauvegarder le fichier qui contient la base.
Si on veut faire un dump, on se connecte à la base :

Code BASH :

sqlite3 base.sqlite

On indique l'endroit où stocker la sauvegarde (si pas de chemin spécifié, c'est sauvegardé là où on était en console) :

Code SQL :

.output dump.SQL

Et on lance le dump :

Code SQL :

.dump

Restauration

Il est possible de restaurer le fichier qui contient la base.

Si on veut restaurer un dump (ou tout fichier qui contient du SQL), on se connecte à la base :

Code BASH :

sqlite3 base.sqlite

Et on importe le fichier contenant les transaction SQL avec :

Code SQL :

.READ SQL.SQL

Interagir avec SQLite en BASH

Il est possible d'interagir avec la base depuis BASH directement. Il suffit de mettre la requête SQL dans la ligne de commande :

Code TEXT :

sqlite3 base.sqlite "SELECT rowid, texte, date FROM message"

Interagir avec SQLite en PHP

Prérequis

Gentoo : Avoir installé dev-lang/php avec le USE sqlite

Fedora / CentOS / Oracle Linux :

Code BASH :

dnf install php-pdo

Debian :

Code BASH :

apt install php-sqlite3

Ouvrir et Fermer la base de données

Pour ouvrir la base de données :

Code PHP :

$db = new SQLite3('base.sqlite', SQLITE3_OPEN_READWRITE);

On pourra vérifier la bonne ouverture en testant la variable $db :

Code PHP :

if(!$db) {
    echo $db->lastErrorMsg();
} else {
    echo "Base ouverte avec succès\n";
}

Pour fermer la base :

Code PHP :

$db->close();

SELECT

Pour afficher des données, on prépare la requête, qu'on exécute et on parcourt le tableau. Voici un exemple simple :

Code PHP :

$sql="SELECT rowid, texte, date FROM message";
$res = $db->query($sql);
while ($row = $res->fetchArray(SQLITE3_ASSOC) ) {
    echo "Rowid : ".$row['rowid']." - Texte :  ".$row['texte']."  - Date : ".$row['date']."<br>";
}

INSERT

Pour insérer des données, il faut bien s'assurer que la base est accessible en écriture par le serveur web qui exécute PHP.

Voici un exemple d'insertion :

Code PHP :

$sql="INSERT INTO message('id', 'texte') VALUES (8,'message depuis PHP');";
$res = $db->exec($sql);

On pourra vérifier la bonne exécution et le nombre de lignes traitées en testant la variable $res :

Code PHP :

if(!$res) {
    echo $db->lastErrorMsg();
} else {
    echo $db->changes()." Lignes affectées\n";
}

UPDATE et DELETE

UPDATE et DELETE sont identiques à INSERT