Stored procedure met dubbele zelf-refererende sleutel

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

Ward van der Put
Moderator

Ward van der Put

03/11/2014 10:06:14
Quote Anchor link
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:

Code (php)
PHP script in nieuw venster Selecteer het PHP script
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
--
-- 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)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
SELECT
    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
 
PHP hulp

PHP hulp

08/05/2021 15:49:27
 
Ger van Steenderen
Tutorial mod

Ger van Steenderen

15/11/2014 18:58:01
Quote Anchor link
Ongeveer zo:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
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
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
 
Ward van der Put
Moderator

Ward van der Put

16/11/2014 08:14:12
Quote Anchor link
O wat is die mooi ... die ga ik inlijsten :-)

Hartelijk dank, Ger!
 
Ger van Steenderen
Tutorial mod

Ger van Steenderen

17/11/2014 09:25:34
Quote Anchor link
Heb nog even een order by toegevoegd, belangrijk voor de volgorde in de COALESCE.
Gewijzigd op 17/11/2014 09:26:18 door Ger van Steenderen
 
Ward van der Put
Moderator

Ward van der Put

17/11/2014 09:57:12
Quote Anchor link
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
Tutorial mod

Ger van Steenderen

17/11/2014 10:40:41
Quote Anchor link
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
Moderator

Ward van der Put

17/11/2014 10:46:56
Quote Anchor link
Okay, dan doen we dat.
 



Overzicht Reageren

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.