Alter Table (SQL DDL)

[djurden]

I am using the Data Modeling features and often need to add a field or modify an exising field.  Is there a way to have EA generate an ALTER TABLE script to implement these types of changes?

If not, could it be added?  I do this quite often...

[hd]

We are considering adding ALTER TABLE DDL generation in the future. An option to compare the existing data model with an ODBC source will be included - would that satisfy your needs?

[David Ainsley]

Was this ALTER TABLE DDL generation ever implemented?  If so I can't seem to find it.  The comparison option in DDL generation is not that useful.

[xaba]

We've got the same problem! Support for generating some "difference DDL" (ALTER scripts) is needed. It could be based on managing baselines for instance...
Ainsley is right: the current comparison option isn't useful.

[PeterA]

Assuming it may be useful ... I vote in favour of an ALTER TABLE capability. I'd also vote in favour of improving the compare feature ...

Unless someone has a better way?  

How I do it currently:
- have the base data model documented in EA.
- reverse engineer the actual production implementation in a separate folder via ODBC connection
- export either (the documented model or the reverse engineered) to an XMI 1.1 file
- perform the compare between the other model and the XMI file

Last tests show that everything is different ... Unless I do something completely wrong? Meaning, it is sifting manually through 180 tables  

Thx for suggestions!

[skiwi]

Vote +1 for Alter Table Capability
(we use redgate sql compare and compare actual instances of databases)

[Paolo F Cantoni]

[size=18]...[/size]
Last tests show that everything is different ... Unless I do something completely wrong? Meaning, it is sifting manually through 180 tables  
[size=18]...[/size]

Hi Peter,

I think the problem is in the process you used versus the process the comparison functionality expects.  In order to compare things the things have to be comparable.  Using the inbuilt comparison feature, the comparable mechanism (I believe) is the GUID of the element.  In other words, you have to have the same named element end up with the same GUID to be able to compare one element with its prior version.

This you can do by saving the original structure as a baseline then reverse engineering the new structure (over the top of the old) and then running a compare to baseline.

Naturally you should do this on a copy of your repository.  

HTH,
Paolo

[son-of-sargasso]

Getting back to the revivalist post.  

I've been going through this for years, not only in EA but in other RDBMS modelling tools including whats-its-name from CA.

The restrictions on ALTER vary so much between database types and even within a database type depending on whether or not a relationship is populated or not are such that the useability of this is questionable.

The disruption of indices, side effects on views etc etc has meant that I have often found that reloading the data into a new schema is a lot easier than trying to use and fix what ALTER has done.

Not actively voting against it, JM20cW
bruce

[David Ainsley]

I think there are 2 areas in EA where comparison functionality could be improved by adding delta script generation.
1) Compare model to external database (from the Code Engineering->Generate DDL) - This was the option that prompted my interest originally, would be useful for comparing a model to a physical implementation and bringing them in line.
2) Manage Baselines - > Show Differences - (thanks Paolo for pointing this out!) I'm fairly new to EA and have just had a quick look at this option  for the first time but adding difference DDL generation here would be more useful than 1) and exactly what I would like to see in order to generate update scripts for incrementing versions, i.e. everything is contained within the repository and not dependent on external sources.  

[Graham_Moir]

Here's another thread on this topic which at the time confirmed Sparx was working on it.

http://www.sparxsystems.com/cgi-bin/yabb/YaBB.cgi?num=1132585369/0

[xaba]

I think these three options would cover everybody's needs:
- Compare current model to a physical database
- Compare current model to an SQL script file
- Compare current model to a baseline
The most important: the result of any comparison process shall be an SQL script!
Any news from EA development team about the planned implementation?