CMP tables - can they be created automatically?

[Jacek Laskowski <jl...@apache.org>]

Hi,

Just wondering if there's a way to automatically generate tables for 
CMPs. Do I need to create them manually? I wish I wouldn't have to.

Jacek

Re: CMP tables - can they be created automatically?

[Jeremy Boynes <jb...@apache.org>]

Jacek Laskowski wrote:
> Hi,
> 
> Just wondering if there's a way to automatically generate tables for 
> CMPs. Do I need to create them manually? I wish I wouldn't have to.
> 

It really depends on how far you want to go with generating the schema.

It is fairly easy to do basic generation, executing DDL to create the 
tables. This can work well in a development environment where there is 
no DBA support for Java developers.

However, one challenge comes when you want to run this the second time. 
There are basically two options: drop and rebuild the database, or issue 
alter statements to modify the schema to match the new object model. The 
first works well with small databases (few tables, sample/test data) but 
not with large ones. The second is complex to get right - many database 
specific modelling tools have struggled with this problem over the years.

For a production environment a whole other set of issues arise:
* avoiding being killed by the DBAs
* table/column design rules
* having permission to issue DDL statements at all
* avoiding being killed by the DBAs
* physical database design issues (e.g. which tablespace/segment)
* index generation and other tuning
* avoiding being killed by the DBAs

I'm not trying to raise barriers here, but this is something I have run 
into before and just want to make sure some of the non-technical issues 
are flagged up front.

TranQL has models of the various schema levels involved and we are 
starting to see the emergence of database specific behaviour (Derby and 
now DB2 dialects). Given it knows what the physical schema looks like, 
it should be possible for it to convert that to DDL.

I think the place to start is to add a DDL generation module to TranQL 
which initially can generate a script containing appropriately ordered 
DDL statements that can be run using the database's tools, maven or ant. 
Once that is there, having the server execute it automatically should be 
a firly trivial task.

--
Jeremy

Re: CMP tables - can they be created automatically?

[Dain Sundstrom <da...@iq80.com>]

On Jul 5, 2005, at 4:29 PM, sissonj@insession.com wrote:

> A generateSQL option would be nice.  It would be useful to have an  
> option
> (not the default) as to whether the generated DDL is applied to enable
> simple deployment of demonstration/evaluation applications.  If we  
> make
> this easy then more software vendors may utilise Geronimo in their
> demo/evaluation versions of their J2EE applications.

+1

-dain

Re: CMP tables - can they be created automatically?

[si...@insession.com]

Jacek Laskowski <jl...@apache.org> wrote on 06/07/2005 07:42:42 AM:

> Aaron Mulder wrote:
> >    I like the idea of generating DDL automatically.  I don't much
> > like the idea of applying it automatically, though if we generate it, 
we
> > could easily provide a tool or command to apply the generated DDL. 
> > Still, that reflects my development style as much as anything.  If 
people
> > feel that it's valuable to support automatic creating and/or deleting,
> > that's OK, I just feel strongly that we should not make that the 
default
> > behavior.
> 
> I saw that the SunONE deployer has a command line option -generateSQL 
> which I think is just for that. I assume it generates the SQLs to be run 

> by a DBA. I wish we could have it, too. We could also have an option to 
> configure if the schema should be altered or recreated or anything one 
> could think of. At the very first, having SQLs generated is what I think 

> developers would love.
> 
> I've reported it as http://issues.apache.org/jira/browse/GERONIMO-710
> 

A generateSQL option would be nice.  It would be useful to have an option 
(not the default) as to whether the generated DDL is applied to enable 
simple deployment of demonstration/evaluation applications.  If we make 
this easy then more software vendors may utilise Geronimo in their 
demo/evaluation versions of their J2EE applications.

John

> > Aaron
> 
> Jacek
> 

Re: CMP tables - can they be created automatically?

[Jacek Laskowski <jl...@apache.org>]

Aaron Mulder wrote:
> 	I like the idea of generating DDL automatically.  I don't much
> like the idea of applying it automatically, though if we generate it, we
> could easily provide a tool or command to apply the generated DDL.  
> Still, that reflects my development style as much as anything.  If people
> feel that it's valuable to support automatic creating and/or deleting,
> that's OK, I just feel strongly that we should not make that the default
> behavior.

I saw that the SunONE deployer has a command line option -generateSQL 
which I think is just for that. I assume it generates the SQLs to be run 
by a DBA. I wish we could have it, too. We could also have an option to 
configure if the schema should be altered or recreated or anything one 
could think of. At the very first, having SQLs generated is what I think 
developers would love.

I've reported it as http://issues.apache.org/jira/browse/GERONIMO-710

> Aaron

Jacek

Re: CMP tables - can they be created automatically?

[Aaron Mulder <am...@alumni.princeton.edu>]

	I like the idea of generating DDL automatically.  I don't much
like the idea of applying it automatically, though if we generate it, we
could easily provide a tool or command to apply the generated DDL.  
Still, that reflects my development style as much as anything.  If people
feel that it's valuable to support automatic creating and/or deleting,
that's OK, I just feel strongly that we should not make that the default
behavior.

Aaron

On Tue, 5 Jul 2005, Jacek Laskowski wrote:
> Hi,
> 
> Just wondering if there's a way to automatically generate tables for 
> CMPs. Do I need to create them manually? I wish I wouldn't have to.
> 
> Jacek
> 
> 

Re: CMP tables - can they be created automatically?

[Jeremy Boynes <jb...@apache.org>]

Sing Li wrote:
> I have the same problem with a CMP example - how to
> create Derby SQL tables "once only" during build
> and/or deployment??
> 
> The ActiveMQ code does it programmatically, but it
> seems to try it again each time during server boot.
> 

This is kind of illustrative of the problem. ActiveMQ needs a couple of 
tables as backing store but finding out whether they are there already 
is harder than it appears.

For example, you can query database metadata but for that to work you 
need to know how the catalog and schema are defined by your databases 
(setting aside minor annoyances like which case the database uses to 
store the names).

The simple but pragmatic approach ActiveMQ takes is just to create the 
tables and ignore errors that result, deferring the real failure until 
it needs to insert a message. This works for a simple schema like they 
user (I think it's just one or two tables) but is less useful for a 
complex schema like that being used by an entire application.

--
Jeremy

Re: CMP tables - can they be created automatically?

[Sing Li <we...@yahoo.com>]

I have the same problem with a CMP example - how to
create Derby SQL tables "once only" during build
and/or deployment??

The ActiveMQ code does it programmatically, but it
seems to try it again each time during server boot.

- Sing

--- Jacek Laskowski <jl...@apache.org> wrote:

> Hi,
> 
> Just wondering if there's a way to automatically
> generate tables for 
> CMPs. Do I need to create them manually? I wish I
> wouldn't have to.
> 
> Jacek
> 
>