Paste number 65561: Why we don't get translated values back from the DB

Paste number 65561: Why we don't get translated values back from the DB
Pasted by: dbs
When:10 months, 2 weeks ago
Share:Tweet this! | http://paste.lisp.org/+1EL5
Channel:#openils-evergreen
Paste contents:
Raw Source | XML | Display As
Our query (cut and paste from postgresql log):

evergreen=#  SELECT  "cam".code, oils_i18n_xlate('config.audience_map', 'cam', 'description', 'code', "cam".code::TEXT, 'hy-AM') AS "description", oils_i18n_xlate('config.audience_map', 'cam', 'value', 'code', "cam".code::TEXT, 'hy-AM') AS "value" FROM config.audience_map AS "cam" WHERE "cam".code IS NOT NULL;

Our translations in config.i18n_core:

evergreen=# select * from config.i18n_core where fq_field = 'config.audience_map' limit 10;
  id  |      fq_field       |     identity_value     | translation |          string           
------+---------------------+------------------------+-------------+---------------------------
  763 | config.audience_map | Unknown or unspecified | hy-AM       | Չիմացված կամ չբնութագրված
  764 | config.audience_map | Preschool              | hy-AM       | Նախադպրոցական
  765 | config.audience_map | Primary                | hy-AM       | Նախնական

Our definition for oils_i18n_xlate. Note that it's matching on keyfield (class + column) instead of keytable - but our translated values all have keytable in fq_field: 

CREATE OR REPLACE FUNCTION oils_i18n_xlate ( keytable TEXT, keyclass TEXT, keycol TEXT, identcol TEXT, keyvalue TEXT, raw_locale TEXT ) RETURNS TEXT AS $func$
DECLARE
    locale      TEXT := REGEXP_REPLACE( REGEXP_REPLACE( raw_locale, E'[;, ].+$', '' ), E'_', '-', 'g' );
    language    TEXT := REGEXP_REPLACE( locale, E'-.+$', '' );
    result      config.i18n_core%ROWTYPE;
    fallback    TEXT;
    keyfield    TEXT := keyclass || '.' || keycol;
BEGIN

    -- Try the full locale
    SELECT  * INTO result
      FROM  config.i18n_core
      WHERE fq_field = keyfield
            AND identity_value = keyvalue
            AND translation = locale;

    -- Try just the language
    IF NOT FOUND THEN
        SELECT  * INTO result
          FROM  config.i18n_core
          WHERE fq_field = keyfield
                AND identity_value = keyvalue
                AND translation = language;
    END IF;

    -- Fall back to the string we passed in in the first place
    IF NOT FOUND THEN
	EXECUTE
            'SELECT ' ||
                keycol ||
            ' FROM ' || keytable ||
            ' WHERE ' || identcol || ' = ' || quote_literal(keyvalue)
                INTO fallback;
        RETURN fallback;
    END IF;

    RETURN result.string;
END;
$func$ LANGUAGE PLPGSQL;

And here's the complete set of values for fq_field:

evergreen=# select distinct fq_field from config.i18n_core - no classes / columns here:

           fq_field           
------------------------------
 actor.org_unit
 actor.org_unit_type
 actor.usr
 aout.opac_label
 asset.copy_location
 config.audience_map
 config.bib_level_map
 config.bib_source
 config.copy_status
 config.i18n_locale
 config.identification_type
 config.item_form_map
 config.item_type_map
 config.language_map
 config.lit_form_map
 config.net_access_level
 config.non_cataloged_type
 config.rule_age_hold_protect
 config.rule_circ_duration
 config.rule_max_fine
 config.rule_recuring_fine
 config.standing
 permission.perm_list
(23 rows)

This paste has no annotations.

Colorize as:
Show Line Numbers

Lisppaste pastes can be made by anyone at any time. Imagine a fearsomely comprehensive disclaimer of liability. Now fear, comprehensively.