@Robert, That's kinda what I was thinking would be required. Having a db patcher tool along with versioned database schema's is the only thing I could come up with as a solution as well.
@Michael, I'll take a look at Flyway. It sounds as if I could potentially use this as the patcher tool itself and have the versioned schemas in either a Java file or SQL file as long as I keep track of the current version of the schema in the database. --Steve On Mon, May 2, 2011 at 5:48 PM, Michael Gentry <[email protected]>wrote: > I agree with Robert that it is a difficult task and you should > probably handle it in a more controlled fashion. I haven't had a > chance to use this yet, but Flyway sounds promising to me for > automating schema updates: > > http://code.google.com/p/flyway/ > > mrg > > > On Mon, May 2, 2011 at 8:28 PM, Robert Zeigler > <[email protected]> wrote: > > In the modeler, there are tools for migrating a database schema, so the > basic functionality exists, but... > > Migrating a db schema is inherently tricky. If you're adding a new > column... what should be the value supplied for existing records, for > instance? > > The complexity only increases from there. > > What I've done in the past is to use the the db migration tools in the > modeler to generate a set of sql updates, augmented those updates (sometimes > with sql, and sometimes with code), and then created a "db patcher" tool. > One strategy that worked reasonably well was to have a small, unmapped > table in the database that keeps track of the current "patch level" of the > db. The tool would then find all of the unapplied patches and apply them in > incremental order. This strategy allowed for some fairly complex schema > changes (splitting data from a table into a separate joined table, for > instance) which still largely automating the process of schema migration > across multiple application installations. > > > > Robert > > > > On May 2, 2011, at 5/27:15 PM , Steve Springett wrote: > > > >> Currently I'm using CreateIfNoSchemaStrategy and it seems to work quite > well > >> for the database platforms I'm targeting. > >> > >> My question is, is there any way or examples somewhere to update the > schema > >> if it's different? For example, if the map contains an additional > field, > >> table or index I would like Cayenne to be able to create the field, > table or > >> index to match what is in the map. This would occur on startup for > example > >> during a product update. > >> > >> > >> --Steve > > > > >
