Re: [GENERAL] Internationalization
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thank you Sam for the valuable input! Best regards, Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam Sam Mason wrote: > On Sat, Apr 11, 2009 at 02:47:53AM +0100, Pedro Doria Meunier wrote: >> Actually what I have is a fully internationalized site by means of >> getttext. >> *Some* of the content comes from the PGSQL database where 2 tables >> relation with others (namely for sensor data description). > > Why not continue using gettext? > >> These tables have the simplest arrangement: id, description :] >> >> I wondered if there was some sort of pgsql extension providing a text >> replacement mechanism of sorts in order to achieve something like >> gettext ... > > As with any specialised problem, PG doesn't solve it directly but > provides various tools for you to solve it in which ever way is best for > you. > >> I guess I'll have to resort to what I've previously thought of ... > > If, by this, you mean having a column for each language, I'd recommend > against doing this. Normalisation is normally the thing to aim for in > databases, so something like: > > CREATE TABLE descriptions ( > id INTEGER, > lang TEXT, > PRIMARY KEY (id,lang) > description TEXT > ); > > would generally be considered better. The reason being that this way > you don't need to change the database every time somebody wants to > translate the software into a new language. If you wanted to maintain > compatibility with the rest of the existing code, you could create a > view like: > > CREATE VIEW descriptions AS > SELECT id, description > FROM descriptions > WHERE lang = 'en'; > > Or whatever language your messages are already in and call the table > above something else. Your code can carry on thinking there's a > "descriptions" table (or whatever you call the view) and doesn't need to > know that there are other languages available. You can then slowly move > the code across to the new version of the table and get rid of the view > when you're done. > -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFJ4LhQ2FH5GXCfxAsRAqKwAJ98yZXXpFyVrQCqa5vMEA40UMDsXQCfcQXB nlxeWHPba/Ab35F2gko4OVs= =YGTU -END PGP SIGNATURE- begin:vcard fn:Pedro Doria Meunier n:Doria Meunier;Pedro org:MGPS - MADEIRA GPS, LDA. adr:;;Portugal email;internet:pdo...@netmadeira.com title:CEO tel;home:+351291603721 tel;cell:+351961720188 version:2.1 end:vcard -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Internationalization
On Sat, Apr 11, 2009 at 02:47:53AM +0100, Pedro Doria Meunier wrote: > Actually what I have is a fully internationalized site by means of > getttext. > *Some* of the content comes from the PGSQL database where 2 tables > relation with others (namely for sensor data description). Why not continue using gettext? > These tables have the simplest arrangement: id, description :] > > I wondered if there was some sort of pgsql extension providing a text > replacement mechanism of sorts in order to achieve something like > gettext ... As with any specialised problem, PG doesn't solve it directly but provides various tools for you to solve it in which ever way is best for you. > I guess I'll have to resort to what I've previously thought of ... If, by this, you mean having a column for each language, I'd recommend against doing this. Normalisation is normally the thing to aim for in databases, so something like: CREATE TABLE descriptions ( id INTEGER, lang TEXT, PRIMARY KEY (id,lang) description TEXT ); would generally be considered better. The reason being that this way you don't need to change the database every time somebody wants to translate the software into a new language. If you wanted to maintain compatibility with the rest of the existing code, you could create a view like: CREATE VIEW descriptions AS SELECT id, description FROM descriptions WHERE lang = 'en'; Or whatever language your messages are already in and call the table above something else. Your code can carry on thinking there's a "descriptions" table (or whatever you call the view) and doesn't need to know that there are other languages available. You can then slowly move the code across to the new version of the table and get rid of the view when you're done. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Internationalization
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Justin, First of all thank you for your input. :) Actually what I have is a fully internationalized site by means of getttext. *Some* of the content comes from the PGSQL database where 2 tables relation with others (namely for sensor data description). These tables have the simplest arrangement: id, description :] I wondered if there was some sort of pgsql extension providing a text replacement mechanism of sorts in order to achieve something like gettext ... I guess I'll have to resort to what I've previously thought of ... Regards, Pedro Doria Meunier. Justin wrote: > Pedro Doria Meunier wrote: >> Hi all, >> >> I'm wondering how to internationalize contents of a table, short >> of having a column for each language string ... Anyone with some >> experience to share? :) >> >> Regards, Pedro Doria Meunier >> > How about parent child table layout. The child table has one > record for translation for each document. something like this > > Create table ParentDoc ( docid serial, description text ) > > Create table ChildDoc ( docid integer, doc_text text, > short_description text, language text ) > -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFJ3/bA2FH5GXCfxAsRAlZ0AKCnP9LDOJlOrDSF+Ci4G3CpdX8AlgCdFJqQ 5CrOIuWvgVBXCmyZwhjR8r8= =DmmH -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Internationalization
Pedro Doria Meunier wrote: Hi all, I'm wondering how to internationalize contents of a table, short of having a column for each language string ... Anyone with some experience to share? :) Regards, Pedro Doria Meunier How about parent child table layout. The child table has one record for translation for each document. something like this Create table ParentDoc ( docid serial, description text ) Create table ChildDoc ( docid integer, doc_text text, short_description text, language text ) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Internationalization
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I'm wondering how to internationalize contents of a table, short of having a column for each language string ... Anyone with some experience to share? :) Regards, Pedro Doria Meunier -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFJ34pd2FH5GXCfxAsRAlbLAJ95Yk0Ab5Zak5B7sRl6ux2ybgrR5ACgl+ZH wB8tgw7sotlCE/bCakW+OC4= =ATNN -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Internationalization
Tom Lane wrote: Dennis Gearon <[EMAIL PROTECTED]> writes: Tom Lane wrote: The indexes on the shared system tables (eg, pg_database) are the only issue here. One possible solution is to require that no locale-aware datatypes ever be used in these indexes. I think right now this is true because "name" doesn't use locale-aware sorting; but we'd have to be careful not to break the restriction in future. Tom what about table names? Isn't it part of the SQL spec to be able to set table names to other langauges other than English? [shrug...] So which language/encoding would you like to force everyone to use? The issue is not really whether you can create a database name that looks like however you want. The issues are (a) what it will look like to someone else using a different encoding; and (b) how it will sort if you ask for "select * from pg_database order by datname", relative to someone else's database name that he thinks is in a different locale and encoding than you think yours is. AFAICT the Postgres user community is not ready to accept a "thou shalt use Unicode" decree, so I don't think that mandating a one-size-fits-all answer is going to fly. regards, tom lane So for now, my database is set up as: show all shows -- server encoding SQL_ASCII I didn't see anything that said what the LC_COLLATE and LC_TYPE settings were when initdb was done. How can I find that out? in postgresql.conf -- LC_MESSAGES = 'C' LC_MONETARY = 'C' LC_NUMERIC = 'C' LC_TIME = 'C' So I have what: 8 bit encoding with standard ASCII ? I can put what langauges in it? It will sort in standard ASCII order, all not English characters will sort last? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Internationalization
Dennis Gearon <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> The indexes on the shared system tables (eg, pg_database) are the only >> issue here. One possible solution is to require that no locale-aware >> datatypes ever be used in these indexes. I think right now this is true >> because "name" doesn't use locale-aware sorting; but we'd have to be >> careful not to break the restriction in future. >> > Tom what about table names? Isn't it part of the SQL spec to be able > to set table names to other langauges other than English? [shrug...] So which language/encoding would you like to force everyone to use? The issue is not really whether you can create a database name that looks like however you want. The issues are (a) what it will look like to someone else using a different encoding; and (b) how it will sort if you ask for "select * from pg_database order by datname", relative to someone else's database name that he thinks is in a different locale and encoding than you think yours is. AFAICT the Postgres user community is not ready to accept a "thou shalt use Unicode" decree, so I don't think that mandating a one-size-fits-all answer is going to fly. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Internationalization
Dennis Gearon <[EMAIL PROTECTED]> writes: > Is there anyway for a single statement to access more than one database? > Could a query, regexes, etc be facing indexes in different > encodings/sorting collations if different databases in a cluster had > different encodings/collations? The indexes on the shared system tables (eg, pg_database) are the only issue here. One possible solution is to require that no locale-aware datatypes ever be used in these indexes. I think right now this is true because "name" doesn't use locale-aware sorting; but we'd have to be careful not to break the restriction in future. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
