Scripts et Programmation

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

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


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





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
Cette page a été vue 1947 fois