The solution lies in entering the data rules into the database, so that the database can enforce data integrity (note that although the same name of student_id was used in both the Tstudent and Taddress tables, this doesn't mean that there is any implicit connection between the two tables). This is the situation alluded to earlier of a “foreign key”—here we would want any attempt to update Taddress to check that the student_id already exists in Tstudent. We can think of Tstudent as being the “parent” whilst the corresponding multiple address rows are “child” entries. Potential problems could also happen if we were to delete a row from the parent, but we can instruct the database to automatically delete all the child entries. Unfortunately, the Firefox GUI does not support the generation of SQL for foreign keys. Furthermore, it actually has foreign key supported switched off (by default) this is easily fixed by telling Firefox to run a command to enable foreign keys! It is important to type the PRAGMA foreign_keys=ON; correctly, and press save. You need to do this only once for each database you create, as future connections to the database will automatically run this PRAGMA command.

La solution consiste à saisir les règles sur les données dans la base, pour que la base de données puisse garantir l'intégrité des données (notez que, même si le nom id_etudiant a été utilisé à la fois dans les tables Tetudiant et Tadresse, cela ne signifie pas qu'il y ait un lien implicite entre les deux tables).

C'est le cas évoqué plus haut d'une « clé étrangère » — ici, nous voulons que, lors d'une tentative de mise à jour de TAdresse on vérifie que l'id_etudiant existe déjà dans Tetudiant. Nous pouvons penser à Tetudiant comme étant le « parent », tandis que les multiples lignes d'adresses correspondantes sont les « enfants ». Des problèmes potentiels peuvent également se produire si nous supprimons une ligne du parent, mais on peut demander à la base de données de supprimer automatiquement toutes les entrées enfant.

Malheureusement, l'interface graphique de Firefox ne supporte pas la génération de code SQL pour les clés étrangères. En outre, le support des clés étrangères est désactivé (par défaut), mais ceci est facilement corrigé en disant à Firefox d'exécuter une commande pour activer les clés étrangères ! Il est important de saisir le « PRAGMA foreign_keys=ON; » correctement et d'appuyer sur Enregistrer. Vous devez effectuer cette opération une seule fois pour chaque base de données que vous créez, car les futures connexions à la base de données exécuteront automatiquement cette commande PRAGMA.

The correct syntax for the foreign key specification can be rather complicated. With a little practice, this is straightforward; the following will fix the situation: DROP TABLE “Taddress”; delete the existing table and data… CREATE TABLE “Taddress” (“student_id” CHAR NOT NULL ,“line_no” INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ,“address” CHAR, FOREIGN KEY(“student_id”) REFERENCES Tstudent (“student_id”) ON DELETE CASCADE); This will cause updates to check that the FOREIGN KEY(“student_id”) (in Taddress) REFERENCES (matches) in the table Tstudent (“student_id” column). The ON DELETE CASCADE phrase will cause any deletion of a student in the parent (Tstudent) to delete all relevant instances from Taddress.

La syntaxe correcte pour la spécification d'une clé étrangère peut être assez compliquée. Avec un peu de pratique, c'est tout simple ; ce qui suit résoudra la situation :

DROP TABLE “Tadresse” ; supprime la table et les données existantes…

CREATE TABLE “Tadresse” (“id_etudiant” CHAR NOT NULL, “numero_ligne” INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, “adresse” CHAR, FOREIGN KEY (“id_etudiant”) REFERENCES Tetudiant (“id_etudiant”) ON DELETE CASCADE);

Cela forcera les mises à jour à vérifier que la FOREIGN KEY “id_etudiant” (dans Tadresse) référence (correspond à) celle de Tetudiant (colonne “id_etudiant”). La phrase ON DELETE CASCADE va faire en sorte que la suppression d'un étudiant dans le parent (Tetudiant) supprime toutes les instances correspondantes de Tadresse.

We can summarise the major constraints we can place on a column: • NOT NULL • UNIQUE • PRIMARY KEY • FOREIGN KEY • CHECK e.g. CHECK(price > 0) • DEFAULT (the value to be used if none is supplied) We can try and add an address line for a non-existent student:- Note that only two input parameters are needed This gives an error message—foreign key constraint failed. An attempt to add a valid student will succeed.

Nous pouvons résumer les principales contraintes que l'on peut placer sur une colonne : • NOT NULL • UNIQUE • PRIMARY KEY • FOREIGN KEY • CHECK par exemple CHECK(prix > 0) • DEFAULT (la valeur à utiliser si aucune n'est fournie)

Nous pouvons essayer d'ajouter une ligne d'adresse pour un étudiant inexistant :

Notez que seuls deux paramètres d'entrée sont nécessaires.

Cela donne un message d'erreur : la contrainte de clé étrangère a échoué.

Une tentative d'ajout d'un étudiant valide réussira.

We don't really care whether the line number of the address is 1, 2, 3, … or not—just that it is increasing. We have arranged for the database to do it and save some work in our program. A simple example (in Python) of adding just a few address lines would be: Note that the interface converts a Python “None” into an SQL “Null”, there is no error handling, and the address line sequence number is generated (correctly) by the database. We now need to create a new, super simple, table which will be a “codes file”; it associates exam codes with their titles. Since it's so simple, we can just type it into the “Execute SQL” area without any prompting. CREATE TABLE Texam_name (exam_id CHAR PRIMARY KEY NOT NULL UNIQUE, exam_title CHAR); Because we have the data available, we shall load the initial data from a CSV file (created from a spreadsheet):

Il ne nous importe pas vraiment que le numéro de ligne de l'adresse soit 1, 2, 3, … ou pas ; il faut juste qu'il augmente. Nous avons pris des dispositions pour que la base de données le fasse, ce qui nous fait gagner du temps côté programme.

Un exemple simple (en Python) pour ajouter quelques lignes d'adresse serait :

Notez que l'interface convertit un « None » Python en un « Null » en SQL, qu'il n'y a pas de gestion des erreurs et que le numéro de séquence de la ligne d'adresse est généré (à juste titre) par la base de données.

Nous devons maintenant créer une nouvelle table super simple, qui sera un « fichier des codes » ; elle associe les codes d'examen avec leur titre. C'est tellement simple que nous pouvons le saisir dans la zone « Exécuter le SQL » sans aucune contrainte.

CREATE TABLE Tnom_examen (id_examen CHAR PRIMARY KEY NOT NULL UNIQUE, titre_examen CHAR);

Puisque que nous avons les données à disposition, nous allons charger les données initiales à partir d'un fichier CSV (créé à partir d'une feuille de calcul) :

We now have a number of tables and we are in a position to use one of the most powerful features of SQL—when we combine two or more tables together. To do this we use the JOIN keyword. There are two main varieties of JOIN available – by far the most common type of JOIN is a matching columns JOIN (technically an INNER JOIN). You can guess the name of the other kind of JOIN! When specifying our JOIN, a problem that sometimes occurs is that we have used the same name for a column in two tables – but perhaps it has different meanings – in which case we need to qualify the column name by table name. As an example: SELECT surname, forename, address FROM Tstudent AS S JOIN Taddress AS A ON S.student_id = A.student_id; (The multiple lines are purely for readability), we enter this into the “execute SQL” section:- Note the optional temporary renaming of the tables to single letter names, purely so the full qualification of field names is abbreviated. We don't have much data in our database, so it looks like that shown below.

Nous avons maintenant de nombreuses tables et nous sommes en mesure d'utiliser l'une des fonctionnalités les plus puissantes de SQL lorsque nous combinons deux ou plusieurs tables. Pour ce faire, nous utilisons le mot-clé JOIN. Il existe deux variétés principales de jointure disponibles – de loin le type le plus commun de jointure est sur des colonnes qui se correspondent (techniquement un INNER JOIN). Vous pouvez deviner le nom de l'autre type de JOIN !

Lorsque nous spécifions notre jointure, un problème qui survient parfois est que nous avons utilisé le même nom pour une colonne dans deux tables, mais peut-être avec des significations différentes, auquel cas nous devons qualifier le nom de la colonne par le nom de la table. À titre d'exemple :

SELECT nom, prenom, adresse FROM Tetudiant AS S         JOIN Tadresse AS A

      ON S.id_etudiant = A.id_etudiant;

(Les lignes multiples ne sont présentes que pour la lisibilité), nous saisissons cela dans la section « Exécuter le SQL » : notez le changement de nom temporaire facultatif des tables en une seule lettre, afin d'abréger la qualification complète des noms de champs. Puisque nous n'avons pas beaucoup de données dans notre base de données, ça ressemble à l'illustration ci-dessous.

It happens frequently that we make a habit of making up new names for the same thing, so in fact our student_id means the same thing in the two tables. As a shortcut we have the idea of a “NATURAL JOIN”—where any matching names are assumed to be required to be matched; so an equivalent to the previous JOIN is: SELECT surname, forename, address FROM Tstudent NATURAL JOIN Taddress; Note that a NATURAL JOIN can give unexpected results if extra columns are added to the tables at a later date. If we expect to want to use our address list periodically, then we should create a View (using our SELECT statement above). There is no overhead in doing so, since it is just an instruction on how to view existing tables. However, it is important to realize that for the same reason, only tables can be updated—views are just a reflection of the current state of the tables; the data in views cannot be updated. We can also create joins which are a JOIN of a table with itself, to create Views of a single table—perhaps to hide a particularly complex piece of SQL. It's also possible to join more than two tables in one statement—but then things tend to get complicated!

Il arrive fréquemment que nous prenions l'habitude d'utiliser de nouveaux noms pour la même chose et, en fait, notre id_etudiant signifie la même chose dans les deux tables. Cela donne l'idée d'un « NATURAL JOIN » comme raccourci, dans lequel les noms correspondants sont supposés devoir être mis en correspondance, de sorte qu'un équivalent de la jointure précédente est :

SELECT nom, prenom, adresse FROM Tetudiant                           NATURAL JOIN Tadresse;

Notez qu'un NATURAL JOIN peut donner des résultats inattendus si des colonnes supplémentaires sont ajoutées aux tables à une date ultérieure.

Si nous risquons de devoir utiliser notre liste d'adresses périodiquement, alors nous devrions créer une vue (en utilisant notre instruction SELECT ci-dessus). Il n'y a pas de surcharge à faire cela, puisque c'est juste une instruction sur la façon de consulter des tables existantes. Cependant, il est important de réaliser que, pour la même raison, seules les tables peuvent être modifiées — les vues sont simplement un reflet de l'état actuel des tables ; les données des vues ne peuvent pas être modifiées.

Nous pouvons également créer des jointures d'une table avec elle-même, pour créer des vues d'une seule table — peut-être pour cacher un morceau particulièrement complexe de SQL. Il est également possible de joindre plus de deux tables dans une instruction, mais, alors, les choses ont tendance à se compliquer !

If we have a very big database—many thousands of rows in several tables—performance will suffer. In this case, we should look at how we are using our database, especially with respect to JOINs, and specify the Index → Create index option. This is likely to speed up queries, at a small penalty for database updates (we have to maintain each additional index). The last major feature that can be used is the TRIGGER statement – where it is desirable that some database event should 'trigger' a sequence of actions. This is really a rather advanced topic and I refer you elsewhere! We still have one last table to construct—the one that links the students with their exams. Texams student_id exam_id score The exam score will be zero if they have not sat the exam yet. To access the score, we need the combination of student_id and exam_id to be our unique (primary key). This is a slightly tricky case, because we don't allow a student to take the same exam twice, but the data structure means that we need to accept duplicate student_id's and duplicate exam_id's, but reject any combinations of the two which are not unique. This is a situation where a composite primary key has the effect we want.

Si nous avons une très grande base de données — plusieurs milliers de lignes dans plusieurs tables — les performances s'en ressentiront. Dans ce cas, nous devrions examiner comment nous utilisons notre base de données, en particulier en ce qui concerne les jointures, et utiliser le menu Index → Créer un index. Ceci est susceptible d'accélérer les requêtes, au détriment de la facilité des mises à jour de la base de données (nous devons maintenir chaque index supplémentaire).

La dernière fonctionnalité importante qui peut être utilisée est l'instruction TRIGGER – quand il est souhaitable qu'un événement de base de données « déclenche » une suite d'actions. C'est vraiment un sujet assez avancé et je vous renvoie ailleurs !

Nous avons encore une dernière table à construire, celle qui relie les élèves et leurs examens.

Texamens id_etudiant id_examen note

La note d'examen sera nulle s'ils n'ont pas encore passé l'examen. Pour accéder à la note, nous avons besoin de la combinaison de id_etudiant et id_examen qui sera notre clé primaire unique. C'est un cas un peu complexe, car nous ne permettons pas à un étudiant de passer deux fois le même examen, mais la structure de données signifie que nous devons accepter des id_etudiant et des id_examen multiples, mais rejeter toute combinaison des deux qui ne serait pas unique. Il s'agit d'une situation où une clé primaire composée a l'effet que nous recherchons.

So Firefox produces: CREATE TABLE “Texams” (“student_id” CHAR NOT NULL, “exam_id” CHAR NOT NULL, “score” INTEGER DEFAULT 0, PRIMARY KEY (“student_id”, “exam_id”)) We should really declare student_id to be a foreign key so that it is validated against Tstudent and similarly exam_id should be a foreign key so that it is validated against Texam_name. So we delete (with DROP Texams) and execute the following SQL; as you can see, it is effectively similar to our previous encounter with foreign keys. CREATE TABLE “Texams” (“student_id” CHAR NOT NULL, “exam_id” CHAR NOT NULL, “score” INTEGER DEFAULT 0, CHECK (score >= 0), PRIMARY KEY (“student_id”, “exam_id”), FOREIGN KEY(“student_id”) REFERENCES Tstudent (“student_id”), FOREIGN KEY(“exam_id”) REFERENCES Texam_name (“exam_id”)); (Actually, this is not quite correct as SQLite allows character values to be stored in numeric columns—so the CHECK statement would not work properly; we could fix this with a more complicated CHECK statement—but it wouldn't add anything to the readability. So we will leave it alone).

Donc Firefox produit :

CREATE TABLE “Texamens” (“id_etudiant” CHAR NOT NULL, “id_examen” CHAR NOT NULL, “note” INTEGER DEFAULT 0, PRIMARY KEY (“id_etudiant”, “id_examen”))

Nous devrions vraiment déclarer id_etudiant comme clé étrangère afin qu'il soit validé par rapport à Tetudiant, et, de façon similaire, id_examen devrait être une clé étrangère afin qu'il soit validé par rapport à Tnom_examen. Donc, nous supprimons (avec DROP Texamens) et exécutons l'instruction SQL suivante ; comme vous pouvez le voir, elle est effectivement similaire à notre précédente rencontre avec les clés étrangères :

CREATE TABLE “Texamens” (“id_etudiant” CHAR NOT NULL, “id_examen” CHAR NOT NULL, “note” INTEGER DEFAULT 0,

CHECK (note >= 0),

PRIMARY KEY (“id_etudiant”, “id_examen”),

FOREIGN KEY(“id_etudiant”) REFERENCES Tetudiant (“id_etudiant”),

FOREIGN KEY(“id_examen”) REFERENCES Tnom_examen (“id_examen”));

(En fait , ce n'est pas tout à fait correct, car SQLite permet que des caractères soient stockés dans des colonnes numériques, donc l'instruction CHECK ne fonctionnerait pas correctement ; nous pourrions résoudre ce problème avec une instruction CHECK plus compliquée, mais cela n'ajouterait rien à la lisibilité. Donc, nous allons le laisser comme ça).

We can enter all of the test cases using INSERT, and they should all behave properly. With our last table we are in a position to do something typical. The college has a few rather arbitrary rules, which change from time to time, so they don't really need to be enforced by the database. For example: “No student can enroll for M829, without achieving a pass in M823 previously”. This is easy to do in SQL with a little bit of decision in a program. As a slight variation, I've supplied a version that runs in a terminal screen; it could easily be converted to a BASH script. Many programs can have the database open, concurrently.

Nous pouvons saisir tous les cas de test en utilisant INSERT, et ils devraient tous se comporter correctement.

Avec notre dernière table, nous sommes en mesure de faire quelque chose de typique. Le collège a quelques règles assez arbitraires, qui changent de temps en temps et elles n'ont donc pas vraiment besoin d'être appliquées par la base de données. Par exemple : « Aucun étudiant ne peut s'inscrire pour M829 sans avoir réussi M823 auparavant. » C'est facile à faire en SQL avec un peu de décision dans un programme.

Comme légère variation, j'ai fourni une version qui s'exécute dans un écran de terminal ; elle pourrait facilement être convertie en un script bash. La base de données peut être ouverte dans beaucoup de programmes, simultanément.

jeremy@HECTOR:~$ # An example where student K1001001 wants to enroll on M829 jeremy@HECTOR:~$ sqlite3 Exam.sqlite # Open database SQLite version 3.7.9 2011-11-01 00:52:41 Enter “.help” for instructions Enter SQL statements terminated with a “;” sqlite> SELECT count(*) FROM Texams WHERE (“student_id” = “K1001001” AND …> “exam_id” = “M823” AND “score” >= 40 ); 1 sqlite> INSERT INTO Texams VALUES(“K1001001”, “M829”, 0); sqlite> .exit jeremy@HECTOR:~$ The SELECT count(*) … statement is an example of one of the many built-in “aggregate” functions – here the number of rows that meet the conditions in the WHERE conditional, in this case a value of '1' is returned. So we can add a row (with a score of zero) for M829.

jeremy@HECTOR:~$ # Un exemple où l'étudiant K1001001 veut s'inscrire à M829

jeremy@HECTOR:~$ sqlite3 Exam.sqlite # ouvre la base de données

SQLite version 3.7.9 2011-11-01 00:52:41

Enter “.help” for instructions

Enter SQL statements terminated with a “;”

sqlite> SELECT count(*) FROM Texamens WHERE (“id_etudiant” = “K1001001” AND

 ...> "id_examen" = "M823" AND "note" >= 40 );

1

sqlite> INSERT INTO Texamens VALUES(“K1001001”, “M829”, 0);

sqlite> .exit

jeremy@HECTOR:~$

L'instruction SELECT count(*)… est un exemple de l'une des nombreuses fonctions « agrégat » intégrées – ici le nombre de lignes qui répondent aux conditions de la clause WHERE conditionnelle, dans ce cas une valeur de 1 est retournée. Ainsi, nous pouvons ajouter une ligne (avec la note zéro) pour M829.

I shall give a number of references for more advanced use; but even if you want only a single keyed file or two—perhaps for an address book—a database is a big advance over some complex coding solution. References: http://www.sqlite.org/docs.html - The full reference; not a tutorial. http://zetcode.com/databases/sqlitetutorial/ - A very concise SQL tutorial. http://zetcode.com/db/sqlitepythontutorial/ - Python. http://zetcode.com/databases/sqlitephptutorial/ - PHP version. http://zetcode.com/db/sqliteperltutorial/ - Perl. http://zetcode.com/db/sqliteruby/ - Ruby. http://zetcode.com/db/sqlitecsharp/ - C#. http://zetcode.com/db/sqlitevb/ - Visual Basic. http://en.wikipedia.org/wiki/List_of_relational_database_management_systems - general interest only.

Je vais donner un certain nombre de références pour une utilisation plus avancée ; mais même si vous voulez seulement un fichier à une ou deux clés — par exemple pour un carnet d'adresses — une base de données est un grand progrès par rapport à une solution de codage complexe.

Références :

http://www.sqlite.org/docs.html - La référence complète ; pas un tutoriel.

http://zetcode.com/databases/sqlitetutorial/ - Un tutoriel SQL très concis.

http://zetcode.com/db/sqlitepythontutorial/ - Python.

http://zetcode.com/databases/sqlitephptutorial/ - version PHP.

http://zetcode.com/db/sqliteperltutorial/ - Perl.

http://zetcode.com/db/sqliteruby/ - Ruby.

http://zetcode.com/db/sqlitecsharp/ - C#.

http://zetcode.com/db/sqlitevb/ - Visual Basic.

http://en.wikipedia.org/wiki/List_of_relational_database_management_systems - Seulement d'intérêt général.