Stored procedure met dubbele zelf-refererende sleutel
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:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
--
-- 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;
-- 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:
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT
translation_id,
translation
WHERE
is_admin_only = 0
AND
iso_code = '...';
translation_id,
translation
WHERE
is_admin_only = 0
AND
iso_code = '...';
Hoe zou een stored procedure eruit moeten zien?
Gewijzigd op 03/11/2014 11:03:20 door Ward van der Put
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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 ;
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 ;
Gewijzigd op 17/11/2014 09:23:47 door Ger van Steenderen
Hartelijk dank, Ger!
Gewijzigd op 17/11/2014 09:26:18 door Ger van Steenderen
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?
Grote voordelen qua performance zal de sproc niet opleveren.