> -----Ursprüngliche Nachricht----- > Von: Aristedes Maniatis [mailto:[email protected]] > Gesendet: Mittwoch, 13. Juni 2012 09:05 > An: [email protected] > Betreff: Re: AW: Mapping a relationship with type List<Long> > > On 13/06/12 12:53am, Schönfisch, Jörg wrote: > > Thanks for your detailed answer! > > I think what I'm trying to achieve is a bit more difficult than this. I'm > > already > using polymorphic joins and it works really well. However, in the scenario I > try > to model here I don't want to use single table inheritance for performance > reasons. > > What performance problem do you see? We have tables with several > hundred thousand records managed in this way without the slightest issue. > And Cayenne maps queries to proper SQL with joins which the db optimises > nicely. >
Well, I only expect performance problems. Premature optimization maybe... I modeled this schema after the one Hibernate Envers uses. We should discuss this approach once again internally. > > > I'm implementing some kind of history for my entities. If a relation > between two entities is changed, the old relation is written to a history > table > and then gets replaced by the new one. The historized relation still > references the two entities in the normal tables. If now one of these entities > is deleted, it is also put in a separate history table. The historized > relation > from before now references one normal entity and one historized entity. So > what is referenced by the relation changed without this information being > explicitly available anywhere. > > The IDs for all these entities and relations are permanent, so the normal > and the corresponding historized entities always have the same ID and only > differ by their revision (and the changed values, of course). The normal and > the history tables should be split to improve query performance if the > information from the history is not needed. > > That just made my head hurt. I can see what you are doing... but it is > complicated. Are you sure you need to save this to a new table? Will it really > be a performance problem or are you just assuming it will be? More records > in a table need not slow things down if the indexes work well for you. > > It sounds like you have two parallel schemas. One for current data and > another for all the history. > Sorry for giving you a headache. Exactly. There are two almost identical schemas, one for current data and one for the history. > > > It might be possible to model this as vertical inheritance with NormalEntity > and HistorizedEntity being subclasses to a common superclass. But this would > complicate my model and introduce additional tables which is what I am > trying to avoid. > > But above you said that "it is also put in a separate history table". I'm > unclear > whether you have separate database tables or not. At any rate, whether you > have separate database tables, it sounds like a Java subclass approach will > give you some benefits. > > Ari > After explaining this here and thinking a bit more about it, the subclass approach really looks more appealing. I'll try and model this. Nonetheless, is it somehow possible to select a collection of primitive (boxed) types or Strings? Thanks again for your time! Joerg > > > > So I want to select only the IDs from the relationship table and then do two > additional queries for the normal and the historized entities identified by > these. But I have not found a possible to model this. > > > > > > Cheers, > > Joerg > > > > > > -----Ursprüngliche Nachricht----- > > Von: Aristedes Maniatis [mailto:[email protected]] > > Gesendet: Dienstag, 12. Juni 2012 15:53 > > An: [email protected] > > Betreff: Re: Mapping a relationship with type List<Long> > > > > On 12/06/12 7:01pm, Schönfisch, Jörg wrote: > >> Hi, > >> > >> I have two tables, one relates to an object entity, the other is a join > >> table. > A FK relationship exists between those two. However the "other" IDs in the > join table may be present in several different tables so I cannot define a FK > relationship for them. Thus I directly want to read a list of IDs and not > convert > them into object entities. The mapping of IDs to objects will later take place > in the business code. > >> Any hints on how to do this or if it's possible at all? > > > > What you are describing is a polymorphic join. In Rails it looks like this: > > > > http://guides.rubyonrails.org/association_basics.html#polymorphic- > associations > > > > There is no built-in construction in Cayenne, but it isn't hard to map. > > Let's > pretend you want to have the ability to add "attachments" to various other > entities. Contacts, Artists, Paintings. These attachments might be a picture > or > other information. > > > > So you create: > > > > [Attachment] > > id > > other columns > > > > [AttachmentRelation] > > foreignRecordId (int) > > foreignTable (string or enum) > > > > [ContactAttachmentRelation] > > [PaintingAttachmentRelation] > > [ArtistAttachmentRelation] > > > > These last three classes you create in Cayenne model and you make them a > subclass of AttachmentRelation. There is no database table that goes with > them, so effectively you are creating them as single table inheritance. Three > subclasses, and only table table in the database. > > > > Put a method like this in the AttachmentRelation: > > > > public abstract void setAttachedRelation(Attachable attachable); > > > > > > And then code like this goes in the ContactAttachmentRelation subclass: > > > > public void setAttachedRelation(Attachable attachable) { > > setAttachedContact((Contact) attachable); > > } > > > > The model will have a qualifier like this: > > > > <obj-entity name="ContactAttachmentRelation" > superEntityName="AttachmentRelation" > className="x.y.z.ContactAttachmentRelation" > clientClassName="x.y.z.ContactAttachmentRelation"> > > <qualifier><![CDATA[entityIdentifier like "Contact"]]></qualifier> > > </obj-entity> > > > > > > > > There is a bit more to do, but it is pretty simple. The main trick is to > > use the > Cayenne inheritance and let it handle all your SQL generation. It works really > nicely and we've had something like this in production for many years. > > > > > > > > Ari > > > > > > > > -- > --------------------------> > Aristedes Maniatis > GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A >
