Search Postgresql Archives

Re: Transparent i18n?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi there,

sorry if just misunderstanding but we have contrib/hstore available from
http://www.sai.msu.su/~megera/postgres/gist/
which could be used for storing as many languages as you need.
It's sort of perl hash.

	Oleg
On Mon, 4 Jul 2005, David Pratt wrote:

Hi Greg. Not sure about this one since I have never made my own type. Do you mean like an ip to country type of situation to guess locale? If so, I am using a ip to country table to lookup ip from request and get the country so language can be passed automatically to display proper language (but I need some translation work done first before I can activate this). I will also use this for black listing purposes and other things so multi purpose.

I have got a good part of what I wanted working so far. I am just working on language update delete trigger since there does not appear to be a direct way of surgically removing a specific element from an array in postgres unless I have missed something. For example if I knew spanish was 3rd array in my multi-dimensional array of say 10 lang/translation arrays in the array containing all translations - to remove just this one without having rewrite the array and update the field (which is what I am hoping to complete today).

So my language update delete trigger needs to scan the array for lang/translation for deletion, update language key for each language from a reference field (other than for the language being deleted), rewrite the array without the lang/translation that was deleted, and then update the field with rewritten array. Sounds worse that it really is since the multidimensional array containing each lang/translation array is same length and you are performing this by iterating with a loop through records in multi_language table. Further, each translation can be compared by key (for me this is the iso language code). Also, realistically how many times do you need to add and drop languages. And number of languages in use for me will likely never exceed say 20. So this process, even with large numbers of multi-language fields should not be that problematic even if you had say a few thousand text fields fields you wanted translations available for. I think you would still be looking at milliseconds to perform this. This will be an after type trigger (after deletion). I guess I will see what performance is like when I am finished - so far it is pretty fast for adding.

You also have a sensible structure for multi_language fields where each one is referenced to multi_language table by id (normalized) with referential integrity (something I was seeking). The only thing not normalized are translations which is okay to me since array structure is dynamic yet keys give you exactly what you want. I am also going to look at Karsten's material shortly to see how his system works but I am interested in following through with what I started first with arrays approach since I am happy with what I am seeing.

Regards,
David

On Monday, July 4, 2005, at 12:06 PM, Greg Stark wrote:


I wonder if you could make an SQL type that used text[] as its storage format
but had an output function that displayed the correct text for the "current
locale". Where "current locale" could be something you set by calling a
function at the beginning of the transaction.

Do pg_dump and all the important things use the send/receive functions not the input/output functions? so even though this output function loses information
it wouldn't cause serious problems?

You would still need a way to retrieve all the languages for the cases like
administrative interfaces for updating the information. I'm not entirely
convinced this would be any better than the alternative of retrieving all of them by default and having a function to retrieve only the correct language.

--
greg


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
     choose an index scan if your joining column's datatypes do not
     match


	Regards,
		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@xxxxxxxxxx, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux