DROP TABLE S_XRT.T_MOT_ROTATION_MRT; DROP TABLE S_XRT.T_MOT; DROP PROCEDURE S_XRT.P_INDEXATION_ROTATIVE; DROP FUNCTION S_XRT.F_SUPER_LIKE; DROP SCHEMA S_XRT; GO --===========================================================================-- -- INDEX ROTATIF - solution au problème du LIKE '%toto%' -- --===========================================================================-- -- Fredéric Brouard alias SQLpro http://sqlpro.developpez.com -- -- Société SQL SPOT - http://www.sqlspot.com 2017-01-05 - version 1.0 -- --===========================================================================-- --▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄-- -- PHASE 1 : création des tables -- --▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀-- -- création d'un schema spécifique pour CREATE SCHEMA S_XRT; GO CREATE TABLE S_XRT.T_MOT (MOT_ID INT IDENTITY PRIMARY KEY, --> doit être sensible aux accents mais pas à la casse ! Exemple maïs et -- mais, sur et sûr, retraite et retraité, congres et congrès ! MOT_MOT VARCHAR(32) COLLATE French_BIN NOT NULL UNIQUE); GO CREATE TABLE S_XRT.T_MOT_ROTATION_MRT (MRT_ID INT IDENTITY PRIMARY KEY, -- pointe vers la rotation du mot MOT_ID INT NOT NULL REFERENCES S_XRT.T_MOT (MOT_ID), -- pointe vers la racine du mot MOT_ID_RACINE INT NOT NULL REFERENCES S_XRT.T_MOT (MOT_ID), -- indice de rotation MRT_ROTATION TINYINT NOT NULL, UNIQUE (MOT_ID, MOT_ID_RACINE, MRT_ROTATION)); GO --▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄-- -- PHASE 2 : création des routines --▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀-- CREATE PROCEDURE S_XRT.P_INDEXATION_ROTATIVE @MOT VARCHAR(32) AS /****************************************************************************** * INDEX ROTATIF * * Procédure S_XRT.P_INDEXATION_ROTATIVE : * * Ajoute les données nécessaire à l'indexatio rotative pour un mot * ******************************************************************************* * Frédéric Brouard - SQLpro@SQLspot.com - Sté SQL SPOT http://www.sqlspot.com * * Plus d'info. sur http://sqlpro.developpez.com - 2017-01-05 - version 1.0 * ******************************************************************************* * Cette procédure prend en argment en entrée le mot à indexer et insère * * le mot et toutes ses rotations dans la table S_XRT.T_MOT ainsi que les * * références croisées entre mot racine et mot en rotation dans la table * * S_XRT.T_MOT_ROTATION_MRT * ******************************************************************************* * Paramètre en entrée : mot à indexer @MOT VARCHAR(32) * * Type en sortie : valeur de retour de la procédure (paramètre 0) * * * * EXEMPLES : * * Execution * * EXEC S_XRT.P_INDEXATION_ROTATIVE 'LOCOMOTIVE' * * EXEC S_XRT.P_INDEXATION_ROTATIVE 'émotive' * * Resultats * * Génère les entrées suivantes : * * Table S_XRT.T_MOT : * * MOT_ID MOT_MOT * * ----------- -------------------------------- * * 1 locomotive * * 2 ocomotive * * 3 comotive * * 4 omotive * * 5 motive * * 6 otive * * 7 tive * * 8 ive * * 9 ve * * 10 e * * 11 émotive * * * * Table S_XRT.T_MOT_ROTATION_MRT * * MRT_ID MOT_ID MOT_ID_RACINE MRT_ROTATION * * ----------- ----------- ------------- ------------ * * 1 1 1 0 * * 2 2 1 1 * * 3 3 1 2 * * 4 4 1 3 * * 5 5 1 4 * * 6 6 1 5 * * 7 7 1 6 * * 8 8 1 7 * * 9 9 1 8 * * 10 10 1 9 * * 11 11 11 0 * * 12 5 11 1 * * 13 6 11 2 * * 14 7 11 3 * * 15 8 11 4 * * 16 9 11 5 * * 17 10 11 6 * ******************************************************************************/ SET NOCOUNT ON; SET @MOT = LOWER(LTRIM(RTRIM(@MOT))); DECLARE @L TINYINT = LEN(@MOT), @ID_MOT INT; DECLARE @ROTATIONS TABLE (MOT VARCHAR(32) COLLATE French_BIN, ROT TINYINT); -- calcul des rotations, racine comprise (0) WITH T AS (SELECT @MOT AS M, 0 AS I UNION ALL SELECT RIGHT(M, @L-I - 1), I + 1 FROM T WHERE I < @L - 1 ) INSERT INTO @ROTATIONS SELECT * FROM T; -- insertions des mots manquants INSERT INTO S_XRT.T_MOT SELECT MOT FROM @ROTATIONS WHERE MOT NOT IN (SELECT MOT_MOT FROM S_XRT.T_MOT); -- récupération de l'ID du mot racine SELECT @ID_MOT = MOT_ID FROM S_XRT.T_MOT WHERE MOT_MOT = @MOT; -- insertions des rotations manquantes INSERT INTO S_XRT.T_MOT_ROTATION_MRT SELECT M.MOT_ID, @ID_MOT, R.ROT FROM @ROTATIONS AS R JOIN S_XRT.T_MOT AS M ON R.MOT = M.MOT_MOT WHERE NOT EXISTS(SELECT * FROM S_XRT.T_MOT_ROTATION_MRT AS MR WHERE MR.MOT_ID = M.MOT_ID AND MR.MOT_ID_RACINE = @ID_MOT AND MR.MRT_ROTATION = R.ROT); GO CREATE FUNCTION S_XRT.F_SUPER_LIKE (@MOT VARCHAR(32)) RETURNS TABLE /****************************************************************************** * INDEX ROTATIF * * Fonction table en ligne de recherche S_XRT.F_SUPER_LIKE : * * Rehcerche le(s) mot(s) racine d'un mot partiel * ******************************************************************************* * Frédéric Brouard - SQLpro@SQLspot.com - Sté SQL SPOT http://www.sqlspot.com * * Plus d'info. sur http://sqlpro.developpez.com - 2017-01-05 - version 1.0 * ******************************************************************************* * Cette fonction prend en argment en entrée le mot partiel dont on recherche * * au moins une racine et renvoie ce(s) mot(s) et la/les clef(s) primaire(s) * * du/des mot(s) racine(s) ******************************************************************************* * Paramètre en entrée : mot partiel dont on cherche au moins une racine * * Type en sortie : table constituée des colonnes suvantes : * * MOT_ID INT * * MOT_MOT VARCHAR(32) * * * * EXEMPLE : * * Préparation : * * EXEC S_XRT.P_INDEXATION_ROTATIVE 'LOCOMOTIVE' * * EXEC S_XRT.P_INDEXATION_ROTATIVE 'émotive' * * Execution : * * SELECT * FROM S_XRT.F_SUPER_LIKE('mot') * * Résultats : * * MOT_ID MOT_MOT * * ----------- -------------------------------- * * 1 locomotive * * 11 émotive * ******************************************************************************/ AS RETURN (SELECT MR.MOT_ID, MR.MOT_MOT FROM S_XRT.T_MOT AS M JOIN [S_XRT].[T_MOT_ROTATION_MRT] AS R ON M.MOT_ID = R.MOT_ID JOIN S_XRT.T_MOT AS MR ON R.MOT_ID_RACINE = MR.MOT_ID WHERE M.MOT_MOT LIKE LOWER(RTRIM(LTRIM(@MOT))) + '%'); GO CREATE FUNCTION S_XRT.F_ROTATIONS (@MOT VARCHAR(32)) RETURNS TABLE /****************************************************************************** * INDEX ROTATIF * * Fonction table en ligne de recherche S_XRT.F_ROTATIONS : * * Recherche les rotations d'un mot racine * ******************************************************************************* * Frédéric Brouard - SQLpro@SQLspot.com - Sté SQL SPOT http://www.sqlspot.com * * Plus d'info. sur http://sqlpro.developpez.com - 2017-01-05 - version 1.0 * ******************************************************************************* * Cette fonction prend en argment d'entrée le mot racine dont on veut * * connaitre les rotations * ******************************************************************************* * Paramètre en entrée : mot entier dont on veut obteir toutes les rotations * * Type en sortie : table constituée des colonnes suvantes : * * MOT_ID INT * * MOT_MOT VARCHAR(32) * * * * EXEMPLE : * * Préparation : * * EXEC S_XRT.P_INDEXATION_ROTATIVE 'LOCOMOTIVE' * * EXEC S_XRT.P_INDEXATION_ROTATIVE 'émotive' * * Execution : * * SELECT * FROM S_XRT.F_ROTATIONS('LOCOMOTIVE') * * Résultats : * * MOT_MOT MRT_ROTATION * * -------------------------------- ------------ * * motive 4 * * otive 5 * * tive 6 * * ive 7 * * ve 8 * * locomotive 0 * * ocomotive 1 * * comotive 2 * * omotive 3 * * e 9 * ******************************************************************************/ AS RETURN (SELECT M.MOT_MOT, MR.MRT_ROTATION FROM [S_XRT].[T_MOT] AS M JOIN [S_XRT].[T_MOT_ROTATION_MRT] AS MR ON MR.MOT_ID = M.MOT_ID JOIN [S_XRT].[T_MOT] AS R ON MR.MOT_ID_RACINE = R.MOT_ID WHERE R.MOT_MOT = LOWER(RTRIM(LTRIM(@MOT)))) GO