Forum OpenACS Development: lang::util::localize in SQL

Posted by Daniël Mantione on 02/02/06 11:26 PM

Hello,

It can sometime be usefull to have a variation of lang::util::localize in SQL. For example, if you have a table with fields with message tags inside them...

SELECT field
FROM table
ORDER BY field;

... may not give the desired result if "field" is later passed through lang::util::localize.

As far as I can see there exists currently no variation of lang::util::localize in SQL. I wrote one:

CREATE OR REPLACE FUNCTION localize(TEXT,TEXT) RETURNS TEXT AS '
  DECLARE lang_tag TEXT;
          s TEXT;
          package TEXT;
          key TEXT;
          r RECORD;

  BEGIN
    s := $1;
    lang_tag := substring (s FROM ''#[-a-zA-Z0-9_:\.]+\#'');
    WHILE lang_tag IS NOT NULL LOOP
        package := substring(lang_tag FROM ''#(.*)\\\\.'');
        key := substring(lang_tag FROM ''\\\\.(.*)#'');

        SELECT INTO r *
        FROM lang_messages
        WHERE message_key=key AND package_key=package AND locale=$2;

        s := replace(s,lang_tag,r.message);
        lang_tag := substring (s FROM ''#[-a-zA-Z0-9_:\.]+\#'');
    END LOOP;
    RETURN s;
  END
' LANGUAGE 'plpgsql';

Now you can do:

SELECT localize(field,'nl_NL') as localized_field
FROM table
ORDER by localized_field

2: Re: lang::util::localize in SQL (response to 1)

Posted by gustaf neumann on 02/03/06 11:50 AM

nice. be aware that this won't work with TRN mode or with substitution vars (bla bla %somevar% blurb).

3: Re: lang::util::localize in SQL (response to 1)

Posted by Malte Sussdorff on 02/05/06 10:52 AM

Can you put this function into acs-lang? This would be great and a very good idea indeed.

4: Re: Re: lang::util::localize in SQL (response to 3)

Posted by Daniël Mantione on 02/05/06 11:17 AM

Putting this function in acs-lang looks like a good idea. Below is the current version, which adds a fall-back to en_US if a translation is missing.

CREATE OR REPLACE FUNCTION localize(TEXT,TEXT) RETURNS TEXT AS '
  DECLARE lang_tag TEXT;
          s TEXT;
          package TEXT;
          key TEXT;
          r RECORD;
  BEGIN
    s := $1;
    lang_tag := substring (s FROM ''#[-a-zA-Z0-9_:\.]+\#'');
    WHILE lang_tag IS NOT NULL LOOP
        package := substring(lang_tag FROM ''#(.*)\\\\.'');
        key := substring(lang_tag FROM ''\\\\.(.*)#'');

        SELECT INTO r *
        FROM lang_messages
        WHERE message_key=key AND package_key=package AND locale=$2;
        IF r.message = '''' OR r.message IS NULL THEN
            SELECT INTO r *
            FROM lang_messages
            WHERE message_key=key AND package_key=package AND locale=''en_US'';
        END IF;

        s := replace(s,lang_tag,r.message);
        lang_tag := substring (s FROM ''#[-a-zA-Z0-9_:\.]+\#'');
    END LOOP;
    RETURN s;
  END
' LANGUAGE 'plpgsql';