SQLite

SQLite est un système de gestion de bases de données pour construire des BD dites de poche, car elles offrent toutes les fonctionnalités des BD relationnelles sans l'architecture client-serveur, avec en plus l'avantage que toutes les données résident sur un seul fichier disque qui peut simplement être copié sur un autre ordinateur. Des applications écrites dans plusieurs langages bien connus peuvent lire et écrire dans un fichier SQLite par des requêtes SQL standard via leur API d'interaction avec les BD.

Les bases de données SQLite, comme toutes les autres, ne devraient pas être utilisées dans des systèmes de fichiers partagés comme /home, /scratch et /project. Au début d'une tâche, vous devriez en principe copier le fichier SQLite sur l'espace local /scratch $SLURM_TMPDIR où vous pourrez utiliser la base de données sans problème tout en bénéficiant de la meilleure performance. Notez que SQLite ne prévoit pas l'emploi de plusieurs fils ou processus qui écrivent dans la base de données en même temps; pour ce faire, vous devriez utiliser une solution client-serveur.

Utiliser SQLite directement

You can access an SQLite database directly using the native client:

[name@server ~]$ sqlite3 foo.sqlite

If the file foo.sqlite does not already exist, the SQLite software will create it and the client starts in an empty database, otherwise you are connected to the existing database. You may then execute whichever queries you wish on the database, such as SELECT * FROM tablename; to print to the screen the entire contents of the table tablename.

Accessing SQLite from software

This most common way of interacting with an SQLite (or other) database is programmatically, i.e. inside of a program written in one of various languages like R, C++ and Python, using a series of function calls to open a connection to the database, execute queries that can read or update existing data in the database as well as inserting new data and finally close the connection to the SQLite database so that the changes (if any) are flushed to the SQLite file. In the simple example below, we suppose that the database has already been created with a table called employee that has two columns, the string name and age, an integer.

PythonRC++

#!/usr/bin/env python3

# For Python we can use the module sqlite3, installed in a virtual environment, 
# to access an SQLite database
import sqlite3

age = 34

# Connect to the database...
dbase = sqlite3.connect("foo.sqlite")

dbase.execute("INSERT INTO employee(name,age) VALUES(\"John Smith\"," + str(age) + ");")

# Close the database connection
dbase.close()

# Using R, the first step is to install the RSQLite package in your R environment, 
# after which you can use code like the following to interact with the SQLite database
library(DBI)

age <- 34

# Connect to the database...
dbase <- dbConnect(RSQLite::SQLite(),"foo.sqlite")

# A parameterized query
query <- paste(c("INSERT INTO employee(name,age) VALUES(\"John Smith\",",toString(age),");"),collapse='')
dbExecute(dbase,query)

# Close the database connection
dbDisconnect(dbase)

#include <iostream>
#include <string>
#include <sqlite3.h>

int main(int argc,char** argv)
{
  int age = 34;
  std::string query;
  sqlite3* dbase;

  sqlite3_open("foo.sqlite",&dbase);

  query = "INSERT INTO employee(name,age) VALUES(\"John Smith\"," + std::to_string(age) + ");";
  sqlite3_exec(dbase,query.c_str(),nullptr,nullptr,nullptr);

  sqlite3_close(dbase);

  return 0;
}

Caveats

SQLite is as the name suggests easy to use and intended for relatively simple databases and which are neither excessively large (hundreds of gigabytes or more) nor too complicated in terms of their entity-relationship diagram. As your SQLite database grows in size and complexity the performance could start to degrade, in which case the time may have come to consider the use of more sophisticated database software which uses a client/server model. The SQLite web site includes an excellent page on Appropriate Uses For SQLite, including a checklist for choosing between SQLite and client/server databases.