DDL to manage a changing schema

[Graham_Moir]

Unless I've missed something (highly possible), EA is great for generating the scripts necessary to set up your database schema for the first time,  but then nowhere near as good at managing ongoing changes to the schema from that point forward.

If this is the case, please add the functionaity to baseline the schema and generate scripts that will modify an existing schema as necessary based on differences between the current schema and the baseline.  

This would be fantastic for maintenance projects.
Thanks

[hd]

There are plans in the pipeline to add such a feature.  

We envisage that when generating the DDL script, the user will have the option to generate an "ALTER" script instead.

EA will then connect to the target database through ODBC and make a comparison with existing objects and prompt the user to resolve any conflicts.

Any comments/suggestions would be appreciated.

[Bruno.Cossi]

Excellent :-) This would make the database modeling functionality in EA really usable.

Personally, I would like it if, just like on elements, there were a Status (and maybe Phase) field on the Column/Attribute level. Then when generating the DDL, one could filter the tables/columns by Status/Phase, only the selected ones would be relfected in the final DDL.

Not a crucial thing as there is a workaround for this even now, but definitely a nice-to-have!

Bruno

There are plans in the pipeline to add such a feature.  

We envisage that when generating the DDL script, the user will have the option to generate an "ALTER" script instead.

EA will then connect to the target database through ODBC and make a comparison with existing objects and prompt the user to resolve any conflicts.

Any comments/suggestions would be appreciated.

[Graham_Moir]

Great to hear something is in the pipeline.  

I think it would be great to be able to interrogate the current live schema in realtime from the database and then generate scripts to handle the differences between that and the logical model in EA.  A report of differences without actually generating any scripts would also be useful.  

However I think having the flexibility of baselining the schema in EA and then generating scripts for changes/differences that have occurred since the baseline would be just as useful, if not more so, where you have unit/development, system test, UAT and production environments through which all changes must migrate.  

Looking forward to this feature !

[Graham_Moir]

Any update on this feature ?

[hd]

No update yet  

[walter_sung]

Do have any indication of when this feature may be available?

[Graham_Moir]

Any news ?

[hd]

Currently under development...  

[Graham_Moir]

Thanks, looking forward to it !

[Owl Saver]

You may want to look at the ERWin tool from CA (Computer Associates) to see how they do it. Their implementation is for databases only and is very robust - but not complete.

There are two areas I am interested in:

- Is every required feature of the database available through ODBC? You may have to limit functionality so that all ODBC connections will work.

- Some changes cannot be made with a simple Alter. This is particularly a problem if the database has data in it. Also referential integrity creates interesting problems. The most general solution is to dump the data, delete the tables and such, create the new tables, and load the data back. But, this may not be efficient.

I am looking forward to this feature.

[Graham_Moir]

I take it this isn't making it into 6.5 ?

[hd]

Unfortunately, it won't be available for 6.5.

As Michael pointed out, simple Alter is not viable if data is to be preserved. I think most users would want the alter DDL statements to preserve data in the target DB.

Also, there is referential integrity to consider, as well as dependent views and stored procedures. Do we do like SQL Server and simply warn that dependent objects may break, or do we parse all objects and make the appropriate changes?

Any comments would be welcomed.  

[«Midnight»]

Henk,

Now that you have some support for SQL views the Alter problem gets thornier. In particular, Oracle's treatment of views is fairly extensive, largely because they built a lot of the functionality 'pretty close to the metal' as it were.

The Oracle documentation on views, and in particular 'writable' views that allow you to alter underlying data trough a view, was quite interesting. As of Oracle 8i, the last time I looked at this in depth, you really had to dig to find all of their documentation.

IMHO, it is well worth the effort if you want to understand some of the real issues that affect the workings of a DBMS with both tables and views, and the linkages between the two 'layers.' Much of this would apply to any DBMS with view support. Even if you don't support writable views it might prove worthwhile to browse through this stuff, since the effects of an Alter Table statement will be more profound in future versions of EA.

HTH, David

PS: And yes, I too am eagerly awaiting the Alter functionality, but I'll wait - however impatiently - until it works.

[LMun]

I'm a newbie, it sounds as if some of you know of a workaround, since "Alter DDL" isn't available yet.  Could someone give me a quick overview of what the workaround would be?  Would it be to write my own DDL?  All I want to do is be able to handle a adding a column (for now).

Thanks!