From: | Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> |
---|---|
To: | David Pratt <fairwinds(at)eastlink(dot)ca> |
Cc: | postgres-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Transparent i18n? |
Date: | 2005-07-04 19:58:41 |
Message-ID: | Pine.GSO.4.63.0507042357040.6997@ra.sai.msu.su |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
From | Date | Subject | |
---|---|---|---|
Next Message | Oleg Bartunov | 2005-07-04 20:04:13 | Re: tsearch2 and case |
Previous Message | Uwe C. Schroeder | 2005-07-04 19:47:35 | tsearch2 and case |