[news.eclipse.rt.eclipselink] Re: How to upgrade database schema built w

[Markus Zander <atrav99@xxxxxx>]

Hi Enrico,

yes, I mean "update my schema". Your Idea is good for new tables. But 
what, if I have to add a new field or key or if I have to split an 
entity in two?

Here a very simple example:



Old version:
Entity "Student"
Attributes: Name, Registration No., Adress, Phone



New version:
Entity "Student"
Attributes: Sure Name, Given Name, Registration No., Street, City, Zip Code

Entity "Phone_Numbers"
Attribtutes: Registration No. (as foreign key), Phone type, Phone




How can I convert an existing database from "old version" to "new 
version" without having database platform specific SQL statements?

Markus




Enrico schrieb:
> Hi MArkus,
>
> I am not sure what do you mean with upgrade...d you mean update your 
> schema?If so, what about to use:
>
> <property name="eclipselink.ddl-generation" value="create-tables" />
> <property name="eclipselink.ddl-generation.output-mode" value="database"/>
>
>
> in your persitence.xml file.
>
> This will generate schema when you run your application, basing on 
> properties of your entities (if table). As options you can have:
>
> *  none â EclipseLink does not generate DDL; no schema is generated.
> * create-tables â EclipseLink will attempt to execute a CREATE TABLE SQL 
> for each table. If the table already exists, EclipseLink will follow the 
> default behavior of your specific database and JDBC driver combination 
> (when a CREATE TABLE SQL is issued for an already existing table). In 
> most cases an exception is thrown and the table is not created. 
> EclipseLink will then continue with the next statement. (See also 
> eclipselink.create-ddl-jdbc-file-name.)
> * drop-and-create-tables â EclipseLink will attempt to DROP all tables, 
> then CREATE all tables. If any issues are encountered, EclipseLink will 
> follow the default behavior of your specific database and JDBC driver 
> combination, then continue with the next statement. (See also 
> eclipselink.create-ddl-jdbc-file-name and 
> eclipselink.drop-ddl-jdbc-file-name.)
>
> BR,
> Enrico
>
>
>
> Markus Zander ha scritto:
> > Hi,
> >
> > I'm looking for a general solution for upgrading database schema built 
> > with EclipseLink. How do you do it in your projects?
> >
> > The first solution that comes to my mind is to create my own mechanism 
> > for upgrading databases, with SQL scripts doing all the work. But in 
> > this case I'll have to remember about creating new scripts every time 
> > the object mappings are updated. And I'll still have to deal with 
> > low-level SQL queries, instead of just defining mappings and allowing 
> > the ORM tools to do all the job...
> >
> > So the question is how to do it properly. Maybe some tools allow for 
> > simplifying this task (for example, I heard that Rails have such 
> > mechanism built-in).
> >
> > Thanks for any suggestions.
> >
> > Markus