Différences

Ci-dessous, les différences entre deux révisions de la page.

--- store_data_safely:an_intro_to_sqlite [2013/10/20 07:42] – [4] fcm_-_ekel
+++ store_data_safely:an_intro_to_sqlite [2013/10/20 12:16] (Version actuelle) – andre_domenech
@@ Ligne 25: / Ligne 25: @@
 Avec notre base de données relationnelle, nous voulons :
-• des structures de données complexes
+• des structures de données complexes ;
-• la facilité d'utilisation
+• la facilité d'utilisation ;
-• un accès simple à partir de n'importe quel langage de programmation
+• un accès simple à partir de n'importe quel langage de programmation ;
-• des instructions de base de données dans une langue proche de l'anglais
+• des instructions de base de données dans une langue proche de l'anglais ;
 • l'atomicité — si une transaction effectue plusieurs mises à jour de plusieurs fichiers, soit elle exécute toutes les mises à jour (si elle réussit), soit aucune mise à jour (si elle échoue).
-Heureusement (pour nous), la base de données relationnelle a été inventé il y a quelque temps (1970), avec un langage simple, le « langage structuré de requêtes» ou SQL, avec lequel on la contrôle et on y accède. Toutes les versions de SQL sont largement standardisées, donc il suffit de l'apprendre une seule fois.
+Heureusement (pour nous), la base de données relationnelle a été inventée il y a quelque temps (1970), avec un langage simple, le « langage structuré de requêtes » ou SQL, avec lequel on la contrôle et on y accède. Toutes les versions de SQL sont largement standardisées, donc il suffit de l'apprendre une seule fois.
 Un exemple d'instruction SQL simple (pour insérer des données dans une base de données) :
@@ Ligne 47: / Ligne 47: @@
 Hopefully, you use Firefox as your browser, as you need to install an extension (SQLite Manager 0.7.7) in order to load a full GUI interface which will allow for prompting, execution and testing of your SQL. Once you like what you see, you can transfer the SQL statements to your favourite programming language, which could be anything from BASH to BASIC.**
-Une commande SQL commence par un verbe, éventuellement suivi de plusieurs qualificatifs, et se termine toujours par un point virgule. J'ai suivi une convention commune en écrivant les mots-clés SQL en majuscules.
+Une commande SQL commence par un verbe, éventuellement suivi de plusieurs qualificatifs, et se termine toujours par un point virgule. J'ai suivi une convention commune en écrivant les mots clés SQL en majuscules.
 Outre l'aspect zéro configuration de SQLite, il y a un avantage supplémentaire pour le néophyte SQL : Firefox utilise SQLite pour gérer les caches internes, les signets, l'historique, etc. Et donc Firefox a jugé utile d'écrire une extension graphique qui facilite grandement le prototypage et la création de votre propre base de données SQLite.
@@ Ligne 71: / Ligne 71: @@
 Pour ajouter la bonne extension pour Firefox :
-• dans Firefox, cliquez sur le menu Outils > Modules complémentaires
+• dans Firefox, cliquez sur le menu Outils > Modules complémentaires ;
-• dans la boîte de recherche, saisissez SQLite Manager, vous devriez voir une entrée
+• dans la boîte de recherche, saisissez SQLite Manager, vous devriez voir une entrée ;
 • cliquez dessus et vous devriez voir une boîte de dialogue vous demandant de confirmer votre décision.
 Vous devrez alors redémarrer Firefox pour que cela prenne effet.
-Nous sommes maintenant prêts à démarrer — mais nous devrions faire un peu de conception de base de données avant de plonger.
+Nous sommes maintenant prêts à démarrer, mais nous devrions faire un peu de conception de base de données avant de plonger.
-Une base de données SQL a une structure très simple : elle consiste en un certain nombre de table qui ressemblent beaucoup à des feuilles de calcul ; elles ont un nombre prédéfini de colonnes (défini lors de la création de la table), et un nombre illimité de lignes.
+Une base de données SQL a une structure très simple : elle consiste en un certain nombre de tables qui ressemblent beaucoup à des feuilles de calcul ; elles ont un nombre prédéfini de colonnes (défini lors de la création de la table), et un nombre illimité de lignes.
-Il n'y a effectivement aucune étiquette de ligne ; nous utilisons les données elles-mêmes pour mettre en relation une ligne de données d'une table à une ligne de données dans une autre table. Il n'y a aucune limite au nombre de tables — même si le nombre de tables devient rarement très grand.
+Il n'y a effectivement aucune étiquette de ligne ; nous utilisons les données elles-mêmes pour mettre en relation une ligne de données d'une table à une ligne de données dans une autre table. Il n'y a aucune limite au nombre de tables, même si le nombre de tables devient rarement très grand.
 ====== 5 ======
@@ Ligne 95: / Ligne 95: @@
 I've tried to follow a simple naming convention:- to prefix table names with 'T', column names are in lower-case, and SQL statements are in upper-case.**
-Cela soulève probablement la question de la performance lorsqu'on travaille sur de grandes bases de données. Cela ne fait pas que patiner parmi des quantités de données (même si cela peut arriver avec de mauvaises conceptions). Dans les coulisses, SQL maintient un certain nombre d'index, certains créés automatiquement et certains facultativement créé par l'utilisateur. De nombreuses opérations de base de données sont simplement des opérations sur ces index efficaces.
+Cela soulève probablement la question de la performance lorsqu'on travaille sur de grandes bases de données. Cela ne fait pas que patiner parmi des quantités de données (même si cela peut arriver avec de mauvaises conceptions). Dans les coulisses, SQL maintient un certain nombre d'index, certains créés automatiquement et certains facultativement créés par l'utilisateur. De nombreuses opérations de base de données sont simplement des opérations sur ces index efficaces.
-Il est important de souligner qu'une base de données SQLite est stockée comme un seul grand fichier — ce qui rend les sauvegardes (très important) une tâche particulièrement facile.
+Il est important de souligner qu'une base de données SQLite est stockée comme un seul grand fichier, ce qui rend, concernant les sauvegardes (très important), une tâche particulièrement facile.
 L'application
@@ Ligne 120: / Ligne 120: @@
 Nous allons créer quatre tables :
-Tetudiant (une ligne par élève)
+Tetudiant (une ligne par élève).
-Tadresse (une ligne par ligne d'adresse pour chaque élève)
+Tadresse (une ligne par ligne d'adresse pour chaque élève).
-Tnom_examen (une ligne pour chaque série d'examens - une liste des titres d'examens)
+Tnom_examen (une ligne pour chaque série d'examens - une liste des titres d'examens).
-Texamens (une ligne par étudiant et par examen passé)
+Texamens (une ligne par étudiant et par examen passé).
-Notez que nous aurions pu stocker l'adresse dans le tableau Tetudiant dans un nombre prédéfini de champs — mais combien ? Il y a forcément quelqu'un qui aura besoin de plus de lignes pour son adresse que ce que nous avions prévu.
+Notez que nous aurions pu stocker l'adresse dans le tableau Tetudiant dans un nombre prédéfini de champs, mais combien ? Il y a forcément quelqu'un qui aura besoin de plus de lignes pour son adresse que ce que nous avions prévu.
 Ce processus par lequel un champ extensible est déplacé dans une table secondaire est un exemple de « normalisation » et est une caractéristique importante de la conception de base de données.
@@ Ligne 143: / Ligne 143: @@
 So it's now time to create our first table. In practice, it's a good idea to set out your tables on paper before entering them into the computer as there is rarely an absolutely “correct” way to design these things.**
-Tout d'abord, nous devons créer notre base de données, il est donc temps de lancer Firefox, et de cliquer sur le menu Outils > SQLite Manager
+Tout d'abord, nous devons créer notre base de données, il est donc temps de lancer Firefox, et de cliquer sur le menu Outils > SQLite Manager.
-Vous verrez alors un écran plutôt clairsemé ; choisissez Base de données dans le menu et cliquez sur Nouvelle base de données:-
+Vous verrez alors un écran plutôt clairsemé ; choisissez Base de données dans le menu et cliquez sur Nouvelle base de données : - Nous allons appeler notre base de données « Examen », saisissez Examen dans la boîte de dialogue — le système ajoutera .sqlite à la fin et vous demandera de choisir un dossier pour la mettre —  je vous suggère de choisir votre répertoire personnel.
-Nous allons appeler notre base de données « Examen », saisissez Examen dans la boîte de dialogue — le système ajoutera .sqlite à la fin et vous demandera de choisir un dossier pour la mettre —  je vous suggère de choisir votre répertoire personnel.
 Cela nous donne un écran initial d'aspect assez compliqué (ci-dessus).
@@ Ligne 166: / Ligne 165: @@
 So now we have a properly formatted table, but it still has to be filled with data.**
-Tetudiant aura quatre colonnes (celles-ci sont parfois appelés champs).
+Tetudiant aura quatre colonnes (celles-ci sont parfois appelées champs).
-Cette liste montre les principales opérations que nous pouvons faire sur les tables — (Drop signifie supprimer une table en langage SQL).
+Cette liste montre les principales opérations que nous pouvons faire sur les tables (Drop signifie supprimer une table en langage SQL).
 Nous voulons créer une table ; faisons-le grâce au formulaire qui sert à définir notre table (ci-dessus).
-SQLite est différent des autres bases de données SQL en ce sens qu'il n'y a que quelques classes de données différentes (entier, réel, texte, null, BLOB); aucune taille n'est mentionné car SQLite va utiliser autant d'espace que nécessaire. Un BLOB (« Binary Large Object ») est une collection de données binaires stockées comme une seule entité.
+SQLite est différent des autres bases de données SQL en ce sens qu'il n'y a que quelques classes de données différentes (entier, réel, texte, null, BLOB) ; aucune taille n'est mentionnée car SQLite va utiliser autant d'espace que nécessaire. Un BLOB (« Binary Large Object ») est une collection de données binaires stockées comme une seule entité.
-Il faut confirmer que nous voulons vraiment créer cette table — et on voit l'instruction SQL qui permet d'atteindre cet objectif :
+Il faut confirmer que nous voulons vraiment créer cette table, et on voit l'instruction SQL qui permet d'atteindre cet objectif :
 Maintenant, nous avons donc un tableau bien formaté, mais il doit encore être rempli de données.
@@ Ligne 189: / Ligne 188: @@
 id_etudiant mérite quelques explications ; nous l'avons décrit comme la « clé primaire », que nous avons réglée comme étant unique. Ici la clé id_etudiant est utilisée comme une référence à l'information de chaque étudiant dans cette table et dans d'autres. En spécifiant la propriété « Unique », la base de données refusera toute tentative visant à ajouter une ligne avec une valeur id_etudiant en double.
-Les clés primaires peuvent être constituées de plus d'une colonne — par exemple, nous pourrions insister pour que la combinaison (nom, prénom) soit notre clé primaire, mais cela interdirait d'inscrire deux étudiants avec la même combinaison nom/prénom. Chaque clé primaire oblige SQLite à maintenir un index.
+Les clés primaires peuvent être constituées de plus d'une colonne - par exemple, nous pourrions insister pour que la combinaison (nom, prénom) soit notre clé primaire, mais cela interdirait d'inscrire deux étudiants avec la même combinaison nom/prénom. Chaque clé primaire oblige SQLite à maintenir un index.
 C'est habituel, mais pas obligatoire, que la clé primaire ait le mot-clé « Unique ».
-Bien que nous ayons mentionné les clés primaires, ce ne sont pas les seuls types de clé à considérer. Il convient de mentionner qu'il existe des « clés étrangères » et nous donnerons un exemple de leur utilité plus tard.
+Bien que nous ayons mentionné les clés primaires, ce ne sont pas les seuls types de clés à considérer. Il convient de mentionner qu'il existe des « clés étrangères » et nous donnerons un exemple de leur utilité plus tard.
 ====== 10 ======
@@ Ligne 204: / Ligne 203: @@
 However, entering data one line at a time will lead to very poor database performance. Because we didn't specify how many SQL statements form a logical transaction unit, SQLite assumes the single  INSERT statement is an entire transaction. The system needs to lock the database against update, check there isn't a pre-existing row with the same unique key, write the data row, and flush the data to the disk to ensure the integrity of the database. All this activity is probably going to require a couple of rotations of the disk drive. We gain “atomicity” (a row is never partially written) but suffer a large performance penalty in disk-wait time.**
-Nous pouvons maintenant inscrire des étudiants (un à la fois). Le collège a décidé que l'id_etudiant doit être imprimé sur tous les documents, et sera composé d'une lettre suivie de sept chiffres. C'est une contrainte importante qui devrait (et pourrait être) contrôlée par la base de données. Cependant, pour des raisons de simplicité, nous supposerons qu'il est manipulé correctement par le programme utilisateur.
+Nous pouvons maintenant inscrire des étudiants (un à la fois). Le collège a décidé que l'id_etudiant doit être imprimé sur tous les documents et sera composé d'une lettre suivie de sept chiffres. C'est une contrainte importante qui devrait (et pourrait être) contrôlée par la base de données. Cependant, pour des raisons de simplicité, nous supposerons qu'il est manipulé correctement par le programme utilisateur.
 Sélectionnez la table Tetudiant et choisissez l'option Ajouter :
@@ Ligne 210: / Ligne 209: @@
 Après avoir entré vos données, le système vous montrera l'instruction SQL appropriée pour ajouter une ligne.
-Toutefois, saisir des données ligne par ligne mènera à de très mauvaises performances de la base de données. Comme nous n'avons pas précisé combien de requêtes SQL forment une transaction logique, SQLite suppose que la seule instruction INSERT est une transaction complète. Le système doit verrouiller la base de données contre les mises à jour, vérifier qu'il n'y a pas une ligne préexistante avec la même clé unique, écrire la ligne de données, et forcer l'écriture des données sur le disque pour assurer l'intégrité de la base de données. Toute cette activité va probablement nécessiter quelques rotations du disque. Nous gagnons « l'atomicité » (une ligne n'est jamais partiellement écrite) mais souffrons d'une grande pénalité de performance en temps d'attente disque.
+Toutefois, saisir des données ligne par ligne mènera à de très mauvaises performances de la base de données. Comme nous n'avons pas précisé combien de requêtes SQL forment une transaction logique, SQLite suppose que la seule instruction INSERT est une transaction complète. Le système doit verrouiller la base de données contre les mises à jour, vérifier qu'il n'y a pas une ligne préexistante avec la même clé unique, écrire la ligne de données et forcer l'écriture des données sur le disque pour assurer l'intégrité de la base de données. Toute cette activité va probablement nécessiter quelques rotations du disque. Nous gagnons « l'atomicité » (une ligne n'est jamais partiellement écrite) mais souffrons d'une grande pénalité de performance en temps d'attente disque.
 ====== 11 ======
@@ Ligne 227: / Ligne 226: @@
 La solution consiste à effectuer un certain nombre de mises à jour de base de données comme une seule transaction logique. Nous faisons simplement un peu plus de travail et passons moins de temps d'attente des réponses disques.
-Nous marquons le début et la fin de la transaction avec des instructions SQL
+Nous marquons le début et la fin de la transaction avec des instructions SQL :
 BEGIN TRANSACTION;
@@ Ligne 234: / Ligne 233: @@
 COMMIT;
-Nous pouvons entrer n'importe quel nombre raisonnable d'instructions (quelques milliers) qui forment un tout — couvrant plusieurs tables et diverses opérations. Lorsque nous atteignons l'instruction COMMIT, on peu considérer que la base de données a été correctement mise à jour. Par ailleurs, si une erreur se produit à mi-chemin (ou que nous saisissons la commande ROLLBACK), la base de données est replacée dans l'état où elle était avant que nous commencions notre transaction.
+Nous pouvons entrer n'importe quel nombre raisonnable d'instructions (quelques milliers) qui forment un tout, couvrant plusieurs tables et diverses opérations. Lorsque nous atteignons l'instruction COMMIT, on peut considérer que la base de données a été correctement mise à jour. Par ailleurs, si une erreur se produit à mi-chemin (ou que nous saisissons la commande ROLLBACK), la base de données est replacée dans l'état où elle était avant que nous ne commencions notre transaction.
 ====== 12 ======
@@ Ligne 251: / Ligne 250: @@
 This is unexpected—but it turns out that Firefox always arranges for invisible BEGIN and COMMIT instructions to be inserted into any SQL that is run. The error (correctly) says that you can't nest transactions.**
-Ajoutons donc un peu plus d'étudiants (nous savons que nous voulons exécuter du SQL, et la « manipulation de données » semble la plus appropriée).
+Ajoutons donc un peu plus d'étudiants (nous savons que nous voulons exécuter du SQL et la « manipulation de données » semble la plus appropriée).
-Nous obtenons comme invite
+Nous obtenons comme invite :
 INSERT INTO nomTable [(liste-colonnes)] VALUES(liste-valeurs)
@@ Ligne 287: / Ligne 286: @@
 Aucun langage de programmation ne ferait jamais ça à votre place ; dans une situation réelle, vous voulez intercepter les erreurs, et soit les corriger, soit simplement les accepter comme des erreurs qui ne sont pas suffisamment graves pour provoquer l'échec d'une transaction.
-Dans ce cas, nous supprimons simplement les instructions BEGIN et COMMIT et cela fonctionne parfaitement. Il existe une commande SQL très simple pour afficher toutes les lignes et toutes les colonnes d'une table — et nous allons l'exécuter pour prouver que notre table contient les « bons trucs » (la page suivante, en haut à gauche).
+Dans ce cas, nous supprimons simplement les instructions BEGIN et COMMIT et cela fonctionne parfaitement. Il existe une commande SQL très simple pour afficher toutes les lignes et toutes les colonnes d'une table, et nous allons l'exécuter pour prouver que notre table contient les « bons trucs » (la page suivante, en haut à gauche).
 Jusqu'à présent, nous avons réussi à créer une seule table. Ce n'est que lorsque nous avons plusieurs tables que nous pouvons cartographier les relations entre elles. Vous serez heureux d'apprendre que le module Firefox vous permet d'importer des données initiales si vous les avez au format CSV, XML ou code source SQL ; il est également possible d'exporter une table vers ces types de fichiers. Il faut quand même écrire les instructions SQL pour créer les tables, mais nous pouvons importer nos données depuis une feuille de calcul.
@@ Ligne 309: / Ligne 308: @@
 CREATE TABLE "Tadresse" ("id_etudiant" char NOT NULL UNIQUE, ENTIER "numero_ligne" NOT NULL UNIQUE, "adresse" CHAR, PRIMARY KEY ("id_etudiant", "numero_ligne"));
-Toutefois, nous avons choisi d'utiliser une fonctionnalité de SQLite, puisque nous n'avons qu'une seule colonne de clé primaire et qu'elle est de type entier, nous pouvons demander qu'elle soit auto-incrémentée à chaque écriture dans la table. Vous avez sans doute remarqué qu'une colonne (appelée rowid) est automatiquement insérée dans chaque ligne d'une table ; en utilisant l'auto-incrémentation, nous obtenons une suite ascendante de numéros de ligne (éventuellement avec des écarts entre deux).
+Toutefois, nous avons choisi d'utiliser une fonctionnalité de SQLite, puisque nous n'avons qu'une seule colonne de clé primaire et qu'elle est de type entier, nous pouvons demander qu'elle soit auto-incrémentée à chaque écriture dans la table. Vous avez sans doute remarqué qu'une colonne (appelée rowid) est automatiquement insérée dans chaque ligne d'une table ; en utilisant l'auto-incrémentation, nous obtenons une suite ascendante de numéros de lignes (éventuellement avec des écarts entre deux).
 Comme il serait absurde de permettre de saisir l'adresse de quelqu'un qui n'est pas dans la table Tetudiant, nous devons empêcher cela. Ce genre de problème est décrit comme une « question d'intégrité référentielle », et il pourrait être évité par une programmation adaptée. Cependant, le problème est vraiment intrinsèque à ma conception de notre base de données, et une base de données avec des dizaines de tables souffrirait de nombreux problèmes d'intégrité référentielle de ce style.
 À suivre...