Hi all,
I am using some views now to put together a particular format for my Java client factory to produce Java Beans from the database.
Because we support internationalisation we are representing values as an id then storing their multiple languages in unicode to support the same repesentation at the database.
This format is:
base_table, id bigint, is_disabled boolean default false.
resource_table, foreign_key_to_base_table, locale_foreign_key, display_name, is_translated
As such, my views are quite slow because there are a number of Right Joins occuring so that I can present a single "locale" field in the view that all the localised information will attach to correctly.
That way I can > select * FROM v_object where locale = 'en_GB' and object_id = 120031;
So if there are three localised joins they are bound to the single locale.
E.G
create view v_object as
select loc.id as locale,
obj.id as object_id,
obj.user_data as user_data,
type.id as object_type,
type_res.disp_name as object_type_display_name
size.id as object_size,
size_res.disp_name as object_size_disp_name
from locale as loc,
object as obj
left join object_type as type on type.id = obj.object_type
left join object_type_res as type_res on type_res.object_type = obj.object_type
left join object_size as size on size.id = obj.object_size
left join object_size_res as size_res on size_res.object_size = obj.object_size
where ( type_res.locale = loc.id OR type_res.locale IS NULL ) AND
( size_res.locale = loc.id OR size_res.locale IS NULL );
In this example the left joins are required to ensure the columns are returned even if null as not all fields are required.
Anyway, there is a performance problem, and we have a temporary solution.
I was wondering if it is possible to create a function that will return a set of data with the correct view names and have this function perform additional and fast checks server side?
Regards
