Database translations

From MapbenderWiki

use gettext instead?

It's not a good idea to translate strings stored in a Mapbender database with gettext:

• The automatic .po file generation is not possible
• so the po file is almost impossible to maintain

There is more then one way to i18n a database. Three different approaches are illustrated in

Gettext-style database function

-- notice language plpgsql has to be installed
-- 
-- How to install plpgsql? 
-- createlang plpgsql mapbender

--
-- table wfs_conf_element: change in WFS configuration: access to geometries may now be restricted
--
ALTER TABLE wfs_conf_element ADD COLUMN f_auth_varname VARCHAR(50);

--
-- table gui_element: new column for translation
--
ALTER TABLE gui_element ADD COLUMN e_title VARCHAR(255);

-- 
-- new table translation: new table for translations
-- 

CREATE TABLE translations
(
 trs_id serial PRIMARY KEY not null,
 locale varchar(8),
 msgid varchar(512),
 msgstr varchar(512)
);

CREATE INDEX msgid_idx ON translations(msgid);

-- 
-- new function gettext for easy translations
-- 
CREATE OR REPLACE FUNCTION gettext(locale_arg "text", string "text")
RETURNS "varchar" AS
$BODY$
DECLARE
   msgstr varchar(512);
   trl RECORD;
BEGIN
   -- RAISE NOTICE '>%<', locale_arg;
   SELECT INTO trl * FROM translations
   WHERE trim(from locale) = trim(from locale_arg) AND msgid = string;
   -- we return the original string, if no translation is found.
   -- this is consistent with gettext's behaviour
   IF NOT FOUND THEN
       RETURN string;
   ELSE
       RETURN trl.msgstr;
   END IF; 
END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE;

translate via

SELECT gettext(<column>) as <column>

or

 SELECT my.text_col, trns.msgstr as translated_string FROM mytable my LEFT OUTER JOIN translations trns ON my.text_col = trns.msgid AND trns.locale='de_DE'

To Do

Create an equivalent MySQL function.