dimanche 23 avril 2023

SQLite

Quelques expérimentations avec SQLite et sqlite3 notamment une extension avec une fonction de chaines de caractères écrite en C.

Introduction

SQLite est une bibliothèque écrite en C. Voir page wikipedia.

Le paquet sqlite founit en plus de la librairie une interface (CLI) rudimentaire sqlite3. La documentation hors ligne est dans le paquet sqlite-doc.

La particularité de SQLite est d'encapsuler dans un seul fichier l'intégralité d'une base de donnée sans passer par un schéma client-serveur.

Exemple de session sqlite3 :

$ sqlite3 exemple.db
SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
sqlite> create table t(j TEXT, h TEXT, objet TEXT);
sqlite> .schema t
CREATE TABLE t(j TEXT, h TEXT, objet TEXT);
sqlite> insert into t(j, h, objet) 
  values ('2023-04-23','07:00', 'lever'), 
  ('2023-04-23', '10:15', 'grognon'), 
  ('2023-04-23', '23:00', 'dodo');
sqlite> select * from t;
2023-04-23|07:00|lever
2023-04-23|10:15|grognon
2023-04-23|23:00|dodo
sqlite> .quit

Commentaires

Ligne 1 : Ouverture d'une session sqlite3 avec le fichier example.db. Si ce dernier n'existe pas il est crée.

Ligne 4 : (sql) Création d'une table nommée t. Le type de donnée TEXT est précisé mais on pourrait l'omettre. SQLite utilise les classes de stockage NULL, INTEGER, REAL, TEXT, BLOB. Mais SQLite a un typage automatique et flexible. Voir Datatypes In SQLite.

Ligne 5 : (méta commande) Affiche le résultat de la déclaration de la table.

Lignes 7 à 10 : (sql) Ajoute trois lignes à la table t.

Ligne 11 : (sql) requête classique.

Lignes 12 à 14 : résultat de la requête.

Ligne 15 : (méta commande) fin de la session.

Remarque : SQLite accepte les mots clé sql en minuscules. Il les traduit dans la ligne 6 en majuscules.

man sqlite3 fournit de façon sommaire les méta-commandes de l'interface.

Le langage sql est assez standard, nonobstant le typage particulier et implicite de SQLite.

Extension de SQLite

Ici je présente une tentative d'une extension de SQLite : une fonction le qui complète la saisie d'une date par les valeurs courantes.

Code:

/*  le.c
:w | !gcc -g -fPIC -shared % -o %<.so
 
(c) 2023 Mourad Arnout marnout à free.fr
*/
 
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <stdbool.h>
#include <assert.h>
#include <time.h>
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1
 
/* le('french date') input french date format
Let us say the current month is '04' and the current year is '2023'
le('12') -> '2023-04-12'
le('6/5') -> '2023-05-06'
le('1/2/2024') -> '2024-02-01'
*/
static void le(sqlite3_context *context, int argc, sqlite3_value **argv)
{
   assert(argc==1);
   char *s = (char *)sqlite3_value_text(argv[0]); // in string
   char buf[32]; // out string
   time_t t = time(NULL); // time now
   struct tm *tm;
   tm = localtime(&t); // current local time
   int n, d, m, y;
   n = sscanf(s, "%d/%d/%d", &d, &m, &y);
   if(n > 0) tm->tm_mday = d;
   if(n > 1) tm->tm_mon = m - 1;
   if(n > 2) tm->tm_year = y - 1900;
   strftime(buf, sizeof(buf), "%Y-%m-%d", tm);
   sqlite3_result_text(context, buf, -1, SQLITE_TRANSIENT);
}
 
int sqlite3_le_init(sqlite3 *db, char **pzErrMsg, 
	const sqlite3_api_routines *pApi)
{
   int rc = SQLITE_OK;
   SQLITE_EXTENSION_INIT2(pApi);
   (void)pzErrMsg;   /* Unused parameter */
   rc = sqlite3_create_function(db, "le", 1, SQLITE_UTF8, 0, le, 0, 0);
   return rc;
}

Compilation : gcc -g -fPIC -shared % -o %<.so.

Le but de cette fonction est de faciliter la saisie d'une date dans sqlite3.

L'argument est chaine de caractères au format 'j', 'j/m' ou 'j/m/a', où j est le jour du mois (1 .. 28, 29, 30 ou 31), m le mois (1 .. 12) et a est l'année (4 chiffres). Le résultat est une date au format sql.

Exemple :

Admettons qu'on soit le 23 avril 2023 (date de rédaction de cet article).

le('24') renvoie '2023-04-24'

le('12/9') renvoie '2023-09-12'

le('4/1/2024') renvoie '2024-01-04'.

Je me suis inspiré de la fonction rot13 donnée en exemple dans la documentation rot13.c. Je ne comprends pas tout là dedans. Exemple la ligne 45, que j'ai gardée à défaut d'en saisir l'utilité.

La documentation disponible Obtaining SQL Values et Setting The Result Of An SQL Function est assez imbuvable.

Pour la ligne 46 voir Create Or Redefine SQL Functions.

Les lignes 27 à 36 sont du C classique.

Exemple d'utilisation avec la base définie au paragraphe 1 :

$ sqlite3 exemple.db 
SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
sqlite> .load lib/le.so
sqlite> insert into t(j, h, objet) values(le(''), '16:00', 'mise en ligne');
sqlite> insert into t(j, h, objet) values(le('24'), '11:20', 'repos');
sqlite> .mode box
sqlite> select * from t;
┌────────────┬───────┬───────────────┐
│     j      │   h   │     objet     │
├────────────┼───────┼───────────────┤
│ 2023-04-23 │ 07:00 │ lever         │
│ 2023-04-23 │ 10:15 │ grognon       │
│ 2023-04-23 │ 23:00 │ dodo          │
│ 2023-04-23 │ 16:00 │ mise en ligne │
│ 2023-04-24 │ 11:20 │ repos         │
└────────────┴───────┴───────────────┘
sqlite> 

Ligne 1 : ouverture de session avec la base du paragraphe 1

Ligne 4 : (meta-command) chargement de la librairie lib/le.so.

Ligne 5 : (sql) ajout d'une ligne, le paramètre '' de la fonction le étant vide, c'est la date courante (aujourd'hui: 23/4/2023).

Ligne 6 : (sql) ajout d'une ligne, le paramètre '24' de le est le jour du mois courant (04) de l'année courante (2023).

Ligne 7 : (meta-command) mode d'affichage tableau

Ligne 8 : (sql) requête ordinaire.

Lignes 9 à 17 : output de sqlite3