Scripts et Programmation

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

Cet article a été mis à jour, vous consultez ici une archive de cet article!

Table des matières

Introduction
Installation
Spécificités SQLite
SQlite en ligne de commande
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
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

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'))
);

A noter : il n'y a pas de valeur AUTOINCREMENT comme avec MySQL, on a une valeur implicite nommée rowid qui s'incrémente d'office.

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');

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

Si on souhaite afficher l'ID autoincrémenté explicitement :

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';

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