Arjuna Aircraft Ident.: F-MBSD | Alors voilà, je fais un petit jeu du pendu (mais ça pourrait être n'importe quoi d'autre).
j'ai une table dans laquelle je mets les mots qu'apprend le serveur, avec un flag qui indique la langue de chaque mot.
Et pour que le jeu puisse suggérer de façon intelligente des lettres, j'ai une table lettre, qui va associer ne nombre d'occurences de chaque lettre pour chaque mot. Ainsi, pour que le jeu aie plus de chances de trouver, il va aller chercher quelles sont les lettres les plus courantes pour la langue du mot recherché.
La structure des deux tables :
Code :
- CREATE TABLE [dbo].[mot] (
- [id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
- [mot] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
- [language] [char] (2) COLLATE French_CI_AS NOT NULL
- ) ON [PRIMARY]
- GO
- CREATE TABLE [dbo].[lettre] (
- [lettre] [char] (1) COLLATE French_CI_AS NOT NULL ,
- [mot_id] [numeric](18, 0) NOT NULL ,
- [nb] [tinyint] NOT NULL
- ) ON [PRIMARY]
- GO
|
OK, c'est cool.
Sauf que si je décide que depuis l'application web je puisse entrer des mots, mais aussi via un petit intégrateur afin d'ajouter un dictionnaire entier d'un coup, ça va me lourder de dupliquer le code de splitage des mots en lettres.
J'ai donc fait les triggers suivants :
Code :
- CREATE TRIGGER upperMot ON dbo.mot
- instead of INSERT
- AS
- insert into mot (mot, language) (select upper(mot), upper(language) from inserted)
|
Ce trigger est lancé avant que l'insertion d'un mot soit effective (plus exactement "à la place de l'insert", car SQL Server ne reconnait pas "before insert" mais ça revient au même ici. Il ne sert pas à grand chose, juste à ne stocker dans la base que des mot en majuscules (à la place de l'instruction initiale, je fais la même en mettant le mot en majuscules).
Ensuite, un second trigger, sur la même table, va se lancer une fois l'insertion du mot terminée "after insert" :
Code :
- CREATE TRIGGER addMot ON dbo.mot
- AFTER INSERT
- AS
- declare @umot as varchar(50)
- declare @i as tinyint
- select @umot = mot from inserted
- set @i = 0
- while (@i < len(@umot))
- begin
- select @i = @i + 1
- insert into lettre (lettre, mot_id, nb)
- values (substring(@umot, @i, 1), ident_current('mot'), 1)
- end
|
Là, pour chaque lettre, on l'extrait, et on l'insert simplement dans a table des lettres, tout en laisse le système insérer le mot tel quel dans la table mot.
Mais c'est pas tout.
Si je mets le mot "toto", ça sert à rien de faire deux lignes avec "T" et deux lignes avec "O". Donc malgré la commande "insert" dans la table "lettre", je veux que la ligne existe déjà, je fasse un update dessus en incrémentant le nombre d'occurences (champ NB de la table lettres).
Et si y'avait que ça... Maintenant, je peux avoir le mot "édition". Seulement, la lettre É et la lettre E ne sont pas les mêmes en SQL. Moi ça m'embête, dans un jeu du pendu on ne fait pas la différence.
Du coup, je vais devoir, lettre par lettre, virer les accents.
Idem pour le cas des lettres æ et : ben en fait, c'est deux lettres ! (AE et OE)
Donc, voici le trigger exécuté à la place de l'insertion dans la table "lettre" :
Code :
- CREATE TRIGGER addLettre ON [dbo].[lettre]
- INSTEAD OF INSERT
- AS
- declare @lettre1 as char(1)
- declare @lettre2 as char(1)
- declare @mot_id as numeric
- declare @nb as tinyint
- select @lettre1 = lettre, @mot_id = mot_id, @nb = nb from inserted
- -- Je ne peux pas utiliser de "IN" parcequ'en SQL, c'est très con, si É ou Æ ne sont pas égaux à des lettres de A à Z, ils sont pourtant dans l'interval A..Z
- if @lettre1 in ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z')
- begin
- set @lettre2 = ' '
- end
- -- Cas des A accentués
- else if @lettre1 in ('À', 'Á', 'Â', 'Ã', 'Ä', 'Å')
- begin
- set @lettre1 = 'A'
- set @lettre2 = ' '
- end
- -- Cas du C cédille
- else if @lettre1 = 'Ç'
- begin
- set @lettre1 = 'C'
- set @lettre2 = ' '
- end
- -- Cas du E accentué
- else if @lettre1 in ('È', 'É', 'Ê', 'Ë')
- begin
- set @lettre1 = 'E'
- set @lettre2 = ' '
- end
- -- Cas du I accentué
- else if @lettre1 in ('Ì', 'Í', 'Î', 'Ï')
- begin
- set @lettre1 = 'I'
- set @lettre2 = ' '
- end
- -- Cas du N accentué
- else if @lettre1 = 'Ñ'
- begin
- set @lettre1 = 'N'
- set @lettre2 = ' '
- end
- -- Cas du O accentué
- else if @lettre1 in ('Ò', 'Ó', 'Ô', 'Õ', 'Ö')
- begin
- set @lettre1 = 'O'
- set @lettre2 = ' '
- end
- -- Cas du U accentué
- else if @lettre1 in ('Ù', 'Ú', 'Û', 'Ü')
- begin
- set @lettre1 = 'U'
- set @lettre2 = ' '
- end
- -- Cas du Y accentué
- else if @lettre1 = 'Ý'
- begin
- set @lettre1 = 'Y'
- set @lettre2 = ' '
- end
- -- Cas des lettres "double"
- else if @lettre1 = 'Æ'
- begin
- set @lettre1 = 'A'
- set @lettre2 = 'E'
- end
- else if @lettre1 = ''
- begin
- set @lettre1 = 'O'
- set @lettre2 = 'E'
- end
- else
- -- Cas des autres caractères tels que '-' par exemple
- begin
- set @lettre1 = ' '
- set @lettre2 = ' '
- end
- -- On a bien trouvé une lettre
- if @lettre1 != ' '
- begin
- if exists(select null from lettre where lettre = @lettre1 and mot_id = @mot_id)
- begin
- -- La lettre a déjà été inscrite pour ce mot : on incrémente le compteur
- update lettre set nb = nb + @nb where lettre = @lettre1 and mot_id = @mot_id
- end
- else
- begin
- -- C'est la première occurence de la lettre pour ce mot
- insert into lettre (lettre, mot_id, nb) values (@lettre1, @mot_id, @nb)
- end
- end
- -- On a trouvé une seconde lettre (cas des lettres "doubles" )
- if @lettre2 != ' '
- begin
- if exists(select null from lettre where lettre = @lettre2 and mot_id = @mot_id)
- begin
- -- La lettre a déjà été inscrite pour ce mot : on incrémente le compteur
- update lettre set nb = nb + @nb where lettre = @lettre2 and mot_id = @mot_id
- end
- else
- begin
- -- C'est la première occurence de la lettre pour ce mot
- insert into lettre (lettre, mot_id, nb) values (@lettre2, @mot_id, @nb)
- end
- end
|
Et voilà !
Maintenant, si je veux ajouter un mot, je n'ai qu'à l'ajouter dans la table des mots (avec un bête "insert" ), et zou, ça va me le découper en rondelles comme je veux. ça fait autant de code en moins à faire dans chacune des applications succeptibles d'ajouter des mots à ma base.
Mieu ! J'ai mis une contrainte d'unicité sur "mot,langage". Si je tente d'insérer un doublon, bête et discipliné, la base va commencer à faire le travail, et une fois terminé, elle va s'appercevoir qu'elle ne peut pas. Pas de panique : les triggers sont transactionnels : toutes les lettres qui ont commencée à s'écrire dans la base vont être supprimée aussitôt, en même temps qu'une erreur d'unicité sera levée !
Mais c'est pas tout ! Il ne faut pas que je puisse modifier un mot déjà existant : en effet, ce serait trop le bordel de recompter les lettres par trigger, je suis fénéant. Du coup, en gérant les droits sur la table, je ne permets que l'ajout et la suppression d'un mot. Jamais sa modification.
Pareil pour les lettres : je ne dois ni pouvoir les mettre à jour, ni les supprimer, ni même les ajouter à la main : je vire tous les droits sur cette table.
Et enfin, grace à l'option "cascade on delete" sur la FK entre "mot.id" et "lettre.mot_id", si je supprime un mot, je vais aussitôt effacer les lettres associées. Et malgré une interdiction de supprimer dans la table lettres, l'intérité des données étant la plus forte, ça va supprimer quand même par propagation dans la table lettres, alors que je n'ai pas le droit de le faire à la main !
Et pour finir, parceque tout le monde n'a pas à connaître la structure de ma base, j'interdit les select sur "lettres" et "mot", et j'ai fait une vue qui permet de récupérer les lettres les plus souvent utilisées dans les mots pour chaque langage :
Code :
- CREATE VIEW lettre_count
- AS
- SELECT dbo.mot.[language], dbo.lettre.lettre, SUM(dbo.lettre.nb) AS nb_total
- FROM dbo.lettre INNER JOIN
- dbo.mot ON dbo.lettre.mot_id = dbo.mot.id
- GROUP BY dbo.mot.[language], dbo.lettre.lettre
|
Message édité par Arjuna le 17-12-2005 à 15:20:54
|
) avec n'importe quelle base de données. Le SQL, qui est un langage normalisé dédié aux bases de données relationnelles te permet, principalement (mais pas que) d'interroger ta base facilement et assez naturellement (une fois compris le principe). Tu peux ainsi extraire des données filtrées, triées, synthétisées beaucoup plus facilement et rapidement (dans la mesure ou les moteurs de base de données sont hautement optimisés) que si tu devais programmer ça à la main.
) :
