Using Postgresql's table inheritance or other advanced DDL

[Eric Lazarus <er...@yahoo.com>]

	
	
	
	
	
	

Folks:

We are building a complex object model
where there are a bunch of types and sub types and we are considering
using Postgresql's table inheritance (see:
http://www.postgresql.org/docs/8.1/static/ddl-inherit.html ) 




Does anyone have actual product
deployment experience with building a system that uses Apache Cayenne
on top of tables that were built using Postgresql's table inheritance
capability? 



How could we get the table inheritance
to be reflected as object inheritance in the cayenne data objects.
What problems are associated with this.



What are the issues with regard to
using Apache Cayenne with table inheritance? I assume that one issue
is DB lock-in, i.e., this application will be married to Postgresql,
correct? Other databases handle this sort of thing differently than
Postgresql? 




When I do a search, how do I arrange to
get back the correct cayenne object types back?




If I just map things naively, I assume
that I would get unrelated objects, not object inheritance that match
the table inheritance. How do I get around that?




So, for example, I do a query on the
base table and find some objects that are of a more specific type.
How do I arrange to have my Java code get an instance of the more
specific class?




Is it practical to create something as
in instance of the base type and later have it become something of a
more specific type when the system learns more about the object? If
so, how do I do this sort of thing?



Is using  table inheritance just the
wrong thing? 




Eric
 




      

Re: Using Postgresql's table inheritance or other advanced DDL

[Aristedes Maniatis <ar...@maniatis.org>]

On 10/02/10 1:03 AM, Eric Lazarus wrote:
> Folks:
>
> We are building a complex object model
> where there are a bunch of types and sub types and we are considering
> using Postgresql's table inheritance (see:
> http://www.postgresql.org/docs/8.1/static/ddl-inherit.html )
>
>
>
>
> Does anyone have actual product
> deployment experience with building a system that uses Apache Cayenne
> on top of tables that were built using Postgresql's table inheritance
> capability?
>
>
>
> How could we get the table inheritance
> to be reflected as object inheritance in the cayenne data objects.
> What problems are associated with this.


I'd be very surprised if you were able to make that work.  Cayenne would think the two tables are completely different and would not be able to know that they contain overlapping objects.  You are then liable to run into all sorts of problems modifying the same object twice, dealing with caches, etc

Is there a reason you aren't using inheritance from Cayenne itself? For horizontal and single-table inheritance that works very nicely.


Ari


-- 
-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C  5EFA EF6A 7D2E 3E49 102A