Re: Transparent i18n? - Mailing list pgsql-general

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@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83