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

logo-sqlite



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