Mapping for localized columns

bschoate · **Joined:** Thu May 20, 2004 10:21 am **Posts:** 5

Using Hibernate 2.1.3.

I have a table layout that is a little unconventional and need some help mapping it properly for Hibernate.

Here are the table definitions:

Code:

lang
    lang_id (pk)
    code

lang_asset
    lang_asset_id (pk)
    table_name
    row_id (numeric, id of foreign table)
    field_name
    lang_id
    text_asset

resource
    resource_id
    resource_type

resource_file
    resource_id
    file_size
    [file_name]
    [file_summary]

resource_book
    resource_id
    page_count
    [author_bio]

(pseudo fields are shown in brackets)

Sample content:

Code:

lang
   id   code
    1   USEN (US/English)
    2   JPJA (Japanese)

resource
   id   type
    1   file
    2   book
    3   file

resource_file
   id   file_size
    1   1234
    3   9012

resource_book
   id   page_count
    2   567

lang_asset
   id   table_name     row_id   field_name    lang_id   text_asset
    1   resource_file      1    file_name           1   abcd_usen.txt
    2   resource_file      1    file_name           2   abcd_jpja.txt
    3   resource_file      1    file_summary        1   summary in english
    4   resource_file      1    file_summary        2   summary in japanese
    5   resource_book      2    author_bio          1   author bio in english
    6   resource_book      2    author_bio          2   author bio in japanese
    7   resource_file      3    file_name           1   efgh_usen.txt
    8   resource_file      3    file_name           2   efgh_jpja.txt
    9   resource_file      3    file_summary        1   summary in english
   10   resource_file      3    file_summary        2   summary in japanese

So here's what I'm looking for:

And functionality similar to this:

Code:

    Lang lang = new Lang(1);  # english only
    ResourceFile file = (ResourceFile)resourceDAO.loadLocalized(new Long(1), lang);

    Long size = file.getFileSize();
    String name = file.getFileName();
    String summary = file.getFileSummary();

    file.setLang(lang);
    file.setFileName("something_usen.txt");
    file.setFileSummary("Summary of file in english");

    resourceDAO.saveOrUpdate(file);

    // deletion would cascade into text_asset, deleting just
    // the rows for that particular lanaguage
    resourceDAO.deleteLocalized(file, lang);

    // deletion would delete all text_assets for this resource
    resourceDAO.delete(file);

Is that possible?

anthony · **Posted:** Thu May 20, 2004 12:50 pm

have you take a look at
http://www.hibernate.org/hib_docs/refer ... tance.html
?

bschoate · **Joined:** Thu May 20, 2004 10:21 am **Posts:** 5

Yes, I did. And that is a step in the right direction. However, the subclasses are all contained within the "resource" table, with the exception of these localized fields.

What I'm having trouble with is how to define these properties that are not physical fields in the resource table, but instead are mapped to a selection from the lang_asset table. And where the lookup into the lang_asset table is based on both field data from resource (resource_id would map to row_id) as well as constants (table_name, field_name) and a dynamic piece (lang_id).

Here's a select for a single pseudo-field:

Code:

select text_asset from lang_asset
where table_name='resource' and field_name='file_name' and lang_id=1 and row_id=1

I want that value to be retrieved when I do a "getFileName()" for a resource_file subclass.

anthony · **Posted:** Thu May 20, 2004 1:32 pm

Quote:

However, the subclasses are all contained within the "resource" table, with the exception of these localized fields.

what do you mean?

if i understand for one ressource_id in table ressource, you must have one ressource_id in table resource_file OR in table resource_book

now explain exactly what you mean by pseudo fields, where are these data stored? if they are in another tables, there must be a link no?

Excuse me, i may not have understood all your problem... ;)

wohlgemuth · **Posted:** Thu May 20, 2004 2:22 pm

just a simple question,

why you used internatiolized column names?
I can'T see a reson to do so?

can you explain it to me?

bschoate · **Joined:** Thu May 20, 2004 10:21 am **Posts:** 5

anthony wrote:

bschoate wrote:

However, the subclasses are all contained within the "resource" table, with the exception of these localized fields.

what do you mean?

if i understand for one ressource_id in table ressource, you must have one ressource_id in table resource_file OR in table resource_book

Sorry, that was a typo. I meant to say "all contained within the resource tables". Meaning resource, resource_file and resource_book. But the additional "pseudo" fields have their content stored in the lang_asset table.

anthony wrote:

now explain exactly what you mean by pseudo fields, where are these data stored? if they are in another tables, there must be a link no?

By pseudo-fields, I mean fields that are not physically defined in resource (or resource_file or resource_book), but are available through a lookup in the lang_asset table. I would like to be able to retrieve those values in a transparent way.

bschoate · **Joined:** Thu May 20, 2004 10:21 am **Posts:** 5

By the way, if you want to talk about this in real time, you can either instant message me or join #hibernate on irc.freenode.net and talk with me there. My ID for either is 'bschoate'.

anthony · **Posted:** Thu May 20, 2004 3:01 pm

so in ressource_book and ressource_file table you have an lang_asset_id ?

anthony · **Posted:** Thu May 20, 2004 3:03 pm

forget last post, i've think i've understood, your lang_asset table is very strange... i don't know how you can map it.

anthony · **Posted:** Thu May 20, 2004 3:06 pm

what about mapping ressourceX 1 -- many lang_asset

bschoate · **Joined:** Thu May 20, 2004 10:21 am **Posts:** 5

I'll do whatever it takes to make it work. I'm just unclear of the mapping definition I need to make it happen. From the Java side of things, I expect to get something along these lines:

Code:

Lang lang = LangDAO.getInstance().findByCode("USEN");

List books = BookDAO.getInstance().findAllByLang(lang);
Iterator book_iterator = books.iterator();
while (book_iterator.hasNext()) {
    String bio;

    Book book = (Book)book_iterator.next();

    // bio getter -- most desired syntax:

    bio = book.getAuthorBio();

    // generic getter -- second choice:

    bio = book.getTextAsset("author_bio");

}

anthony · **Posted:** Fri May 21, 2004 3:10 am

don't forget that all your pojo properties are not to be persistent.
What i mean is that you can have ressourceX 1 -- many lang_asset
with it you can have an unmapped fileName property
call myRessource.setFileName()
this method will work with the mapped collection myRessource.lang_assets, doing myRessource.lang_assets.add....

I'm not sure i explain it well...