| 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: |
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.