SchemaUpdateStrategy

[Tore Halset <ha...@pvv.ntnu.no>]

Hello.

I just took a look at this new feature for the first time. It is nice  
to be able to set a SchemaUpdateStrategy in the model. From my  
perspective, it is a little strange that this functionality is not  
based on the DbMerger.

There is one hint to the reason for this in CAY-1193: "This version  
non use DBMerger for ThrowOnPartialSchemaStrategy and  
ThrowOnPartialOrCreateSchemaStrategy. In this strategy used new  
StrategyAnalyser, which can speed up implementation in oracle."

If StrategyAnalyser is faster than DbMerger on oracle, is it possible  
to make DbMerger faster as well? I do not have access to Oracle, and  
know metadata fetching in Oracle is super slow.

Is it ok if I create a new SchemaUpdateStrategy that use the DbMerger?  
It will be a "safe" version that only adds tables and columns, not  
remove them. And expand, but not shrink column length.

Some people are already using DbMerger for auto-upgrade during  
startup. I do this in 3 steps. The first step could fit in a  
SchemaUpdateStrategy.
1. Use DbMerger and issue all the safe tokens. That is all tokens that  
does not remove information.
2. Use cayenne code to fill/update content of database. This is  
application specific.
3. Offer the user to "clean up" the database using all the unsafe  
tokens that typically remove columns and so on.

Regards,
- Tore.

Re: SchemaUpdateStrategy

[Andrus Adamchik <an...@objectstyle.org>]

Hmm... unit tests ... Somehow I forgot about those :-)

In any event, I'll keep watching the evolution of DbMerger, as I am  
sure it will have many more good applications.

Andrus


On Jun 11, 2009, at 11:36 AM, Tore Halset wrote:
> Hello.
>
> Okay, I understand. We all have different setups and needs.
>
> I am creating all the tokens and filter by type. I plan to clean up  
> this by adding a method named something like mayRemoveInformation()  
> to all the tokens. The startup merge (step 1 including metadata  
> fetching and issuing of tokens) is done in 6 seconds on PostgreSQL  
> with ~90 tables, so I have not bothered optimizing. A little bit  
> slower with Derby.
>
> For my setup, filtering out extra tables is not important. To speed  
> up junit tests, I implemented support for this in DbMerger. You just  
> override the includeTableName-method.
>
> Regards,
> - Tore.
>
> On Jun 10, 2009, at 15:32 , Andrus Adamchik wrote:
>
>> Hi Tore,
>>
>> We definitely looked at DbMerger, but for the implementation it was  
>> decided to go a parallel way, essentially because most strategies  
>> are using a much smaller subset of JDBC metadata operations for its  
>> schema analysis, and the analysis flow needed to be flexible. E.g.  
>> we don't care if there are extra tables in the DB, and we don't  
>> want to fetch the columns for those. Attribute rules checking is  
>> fairly lax (null/not null mismatch is ignored). All or nothing  
>> strategies would abort out early if they detect a certain single  
>> object presence/absence. Things like that, all affecting  
>> application startup speed (not only on Oracle).
>>
>> Finally we decided against including a partial update strategy for  
>> the reasons we discussed before - production schema upgrade may  
>> require data migration, something the strategy won't know anything  
>> about. So essentially the conclusion was that the main strength of  
>> DbMerger wasn't applicable for the problem at hand. Users can still  
>> implement such strategy. With the help of DbMerger it will be  
>> trivial indeed.
>>
>> I am open to reconciling the two, just thought that it will be too  
>> invasive on the DbMerger end. So is there optimized API for Step  
>> #1, or does it still get all tokens with DbLoader, and then throws  
>> away unsafe ones?
>>
>> Andrus
>
>

Re: SchemaUpdateStrategy

[Tore Halset <ha...@pvv.ntnu.no>]

Hello.

Okay, I understand. We all have different setups and needs.

I am creating all the tokens and filter by type. I plan to clean up  
this by adding a method named something like mayRemoveInformation() to  
all the tokens. The startup merge (step 1 including metadata fetching  
and issuing of tokens) is done in 6 seconds on PostgreSQL with ~90  
tables, so I have not bothered optimizing. A little bit slower with  
Derby.

For my setup, filtering out extra tables is not important. To speed up  
junit tests, I implemented support for this in DbMerger. You just  
override the includeTableName-method.

Regards,
  - Tore.

On Jun 10, 2009, at 15:32 , Andrus Adamchik wrote:

> Hi Tore,
>
> We definitely looked at DbMerger, but for the implementation it was  
> decided to go a parallel way, essentially because most strategies  
> are using a much smaller subset of JDBC metadata operations for its  
> schema analysis, and the analysis flow needed to be flexible. E.g.  
> we don't care if there are extra tables in the DB, and we don't want  
> to fetch the columns for those. Attribute rules checking is fairly  
> lax (null/not null mismatch is ignored). All or nothing strategies  
> would abort out early if they detect a certain single object  
> presence/absence. Things like that, all affecting application  
> startup speed (not only on Oracle).
>
> Finally we decided against including a partial update strategy for  
> the reasons we discussed before - production schema upgrade may  
> require data migration, something the strategy won't know anything  
> about. So essentially the conclusion was that the main strength of  
> DbMerger wasn't applicable for the problem at hand. Users can still  
> implement such strategy. With the help of DbMerger it will be  
> trivial indeed.
>
> I am open to reconciling the two, just thought that it will be too  
> invasive on the DbMerger end. So is there optimized API for Step #1,  
> or does it still get all tokens with DbLoader, and then throws away  
> unsafe ones?
>
> Andrus

Re: SchemaUpdateStrategy

[Andrus Adamchik <an...@objectstyle.org>]

Hi Tore,

We definitely looked at DbMerger, but for the implementation it was  
decided to go a parallel way, essentially because most strategies are  
using a much smaller subset of JDBC metadata operations for its schema  
analysis, and the analysis flow needed to be flexible. E.g. we don't  
care if there are extra tables in the DB, and we don't want to fetch  
the columns for those. Attribute rules checking is fairly lax (null/ 
not null mismatch is ignored). All or nothing strategies would abort  
out early if they detect a certain single object presence/absence.  
Things like that, all affecting application startup speed (not only on  
Oracle).

Finally we decided against including a partial update strategy for the  
reasons we discussed before - production schema upgrade may require  
data migration, something the strategy won't know anything about. So  
essentially the conclusion was that the main strength of DbMerger  
wasn't applicable for the problem at hand. Users can still implement  
such strategy. With the help of DbMerger it will be trivial indeed.

I am open to reconciling the two, just thought that it will be too  
invasive on the DbMerger end. So is there optimized API for Step #1,  
or does it still get all tokens with DbLoader, and then throws away  
unsafe ones?

Andrus



On Jun 10, 2009, at 4:10 PM, Tore Halset wrote:

> Hello.
>
> I just took a look at this new feature for the first time. It is  
> nice to be able to set a SchemaUpdateStrategy in the model. From my  
> perspective, it is a little strange that this functionality is not  
> based on the DbMerger.
>
> There is one hint to the reason for this in CAY-1193: "This version  
> non use DBMerger for ThrowOnPartialSchemaStrategy and  
> ThrowOnPartialOrCreateSchemaStrategy. In this strategy used new  
> StrategyAnalyser, which can speed up implementation in oracle."
>
> If StrategyAnalyser is faster than DbMerger on oracle, is it  
> possible to make DbMerger faster as well? I do not have access to  
> Oracle, and know metadata fetching in Oracle is super slow.
>
> Is it ok if I create a new SchemaUpdateStrategy that use the  
> DbMerger? It will be a "safe" version that only adds tables and  
> columns, not remove them. And expand, but not shrink column length.
>
> Some people are already using DbMerger for auto-upgrade during  
> startup. I do this in 3 steps. The first step could fit in a  
> SchemaUpdateStrategy.
> 1. Use DbMerger and issue all the safe tokens. That is all tokens  
> that does not remove information.
> 2. Use cayenne code to fill/update content of database. This is  
> application specific.
> 3. Offer the user to "clean up" the database using all the unsafe  
> tokens that typically remove columns and so on.
>
> Regards,
> - Tore.
>