Stored procedure met dubbele zelf-refererende sleutel

Door Ward van der Put op 03-11-2014 10:06 gewijzigd op 03-11-2014 11:03

4.262 views

Wie kan me op weg helpen met het volgende?

Ik heb een datamodel gebouwd waarin vertalingen in een translation memory (TM) worden gekoppeld aan talen. Dit model ziet er zo uit in het SQL-dialect van MySQL:


--
-- Languages
--
CREATE TABLE `oc_languages` (
  `language_id` SMALLINT(5) UNSIGNED  NOT NULL  AUTO_INCREMENT  COMMENT 'LCID',
  `parent_id`   SMALLINT(5) UNSIGNED  NOT NULL  DEFAULT '2057',
  `name`        VARCHAR(32)           NOT NULL,
  `iso_code`    VARCHAR(5)            NOT NULL,
  `locale`      VARCHAR(255)          NOT NULL,
  `image`       VARCHAR(64)           NOT NULL,
  `sort_order`  SMALLINT(5) UNSIGNED  NOT NULL DEFAULT '0',
  `status`      TINYINT(1) UNSIGNED   NOT NULL,
  PRIMARY KEY (`language_id`),
  KEY `fk_parent_id` (`parent_id`),
  UNIQUE (`iso_code`),
  KEY `name` (`name`),
  CONSTRAINT `fk_parent_id` FOREIGN KEY (`parent_id`) 
    REFERENCES `oc_languages` (`language_id`)
    ON DELETE RESTRICT  ON UPDATE CASCADE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8  COLLATE=utf8_general_ci;

--
-- Translation Memory (TM)
--
CREATE TABLE `oc_translation_memory` (
  `translation_id`  VARCHAR(255)          NOT NULL,
  `language_id`     SMALLINT(5) UNSIGNED  NOT NULL  DEFAULT '2057',
  `translation`     TEXT                  NULL,
  `is_admin_only`   TINYINT(1) UNSIGNED   NOT NULL  DEFAULT '0',
  `last_modified`   TIMESTAMP             NOT NULL  DEFAULT CURRENT_TIMESTAMP  ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY `pk_translation_id` (`translation_id`, `language_id`),
  KEY `language_id` (`language_id`),
  CONSTRAINT `fk_language_id` FOREIGN KEY (`language_id`)
    REFERENCES `oc_languages` (`language_id`)
    ON DELETE CASCADE  ON UPDATE CASCADE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8  COLLATE=utf8_general_ci;

So far, so good. Ik loop nu echter vast op een complicatie die ik nog heb ingebouwd. Het datamodel kent twee toestanden/beslissingsregels voor het bouwen van een language pack:

1. In bijvoorbeeld Hollands (nl-NL) en Vlaams (nl-BE) worden alleen verschillen met de master Standaardnederlands (nl) vastgelegd.

2. Ontbreekt ook daarin een vertaling, dan is er één algemene fallback, namelijk Brits Engels (en-GB).

Met andere woorden, wil je een language pack voor Hollands (nl-NL) maken, dan is dat een vereniging van drie verzamelingen:

1. Nederlands Nederlands (nl-NL)
2. Nederlands (nl)
3. Brits Engels (en-GB)

Om te voorkomen dat ik 3 queries uitvoer en daarna 3 keer een resultaat moet verwerken, wil ik een stored procedure schrijven die met de ISO-taalcode (zoals nl-NL) abstract dit kan doen:


SELECT
    translation_id,
    translation
  WHERE
    is_admin_only = 0
  AND
    iso_code = '...';

Hoe zou een stored procedure eruit moeten zien?

Ger van Steenderen

15-11-2014 18:58 gewijzigd op 17-11-2014 09:23

Ongeveer zo:


DELIMITER $$

CREATE PROCEDURE getTranslations (IN lang_code CHAR(5))
	READS SQL DATA
	BEGIN
		DECLARE EOF BOOLEAN DEFAULT FALSE;
		DECLARE lcode VARCHAR(5);
		DECLARE lid INT(5);
		DECLARE lang_cursor CURSOR FOR 
			SELECT l.language_id,
				   REPLACE(iso_code, '-', '_') lcode
			FROM oc_languages o
			JOIN oc.languages l
				ON o.language_id = l.parent_id
			WHERE o.iso_code = lang_code
			ORDER BY o.sort_order;
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET EOF = TRUE;
		SET @sel := 'SELECT language_id, COALESCE(';
		SET @fr0m := 'FROM oc_translation_memory en_gb';
		OPEN lang_cursor;
		lang_loop: LOOP
			FETCH lang_cursor INTO lid, lcode;
			IF EOF THEN
				CLOSE lang_cursor;
				LEAVE lang_loop;
			END IF;
			SET @sel := CONCAT(@sel, lcode, '.translation,');
			SET @fr0m := CONCAT(@fr0m, ' LEFT JOIN oc_translation_memory ', lcode,
								' ON en_gb.language_id = ', lcode, '.language_id AND ',
								lcode, '.language_id = ', CAST(lid AS CHAR));
		END LOOP;
		SET @aquery := CONCAT(@sel, ' en_gb.translation) translation ', @fr0m,
					 ' WHERE en_gb.language_id = 2057');
		PREPARE thequery FROM @aquery;
		EXECUTE thequery;
		DEALLOCATE PREPARE thequery;
	END$$

DELIMITER ;

Ward van der Put

16-11-2014 08:14

O wat is die mooi ... die ga ik inlijsten :-)

Hartelijk dank, Ger!

Ger van Steenderen

17-11-2014 09:25 gewijzigd op 17-11-2014 09:26

Heb nog even een order by toegevoegd, belangrijk voor de volgorde in de COALESCE.

Ward van der Put

17-11-2014 09:57

Thx!

Je had de query al zó snel gemaakt dat ik twijfel of de stored procedure überhaupt nog wel nodig is. Met alleen de query kom ik er ook: deze procedure is bedoeld voor incidentele updates via een backend waarin meestal slechts één enkele admin actief is.

Wat denk je?

Ger van Steenderen

17-11-2014 10:40

Je kan de query natuurlijk ook in de applicatie samenstellen, dat is namelijk wat de sproc ook doet.
Grote voordelen qua performance zal de sproc niet opleveren.

Ward van der Put

17-11-2014 10:46

Okay, dan doen we dat.

Reageren

Inloggen om te reageren