Sparx Systems Forum
Enterprise Architect => Suggestions and Requests => Topic started by: Graham_Moir on November 21, 2005, 07:02:48 am
-
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
-
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.
-
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.
-
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 !
-
Any update on this feature ?
-
No update yet :(
-
Do have any indication of when this feature may be available?
-
Any news ?
-
Currently under development... :)
-
Thanks, looking forward to it !
-
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.
-
I take it this isn't making it into 6.5 ?
-
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. :)
-
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.
-
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!
-
Any news on this functionality ? 6.6 ? 7.0 ?
thanks
-
this feature will help me a lot in doing my daily business!!!
i do not need a perfect solution which covers every aspect, but maybe you could deliver a beta-version, maybe for only one specific database and with a limited feature-set?
i will play the beta-tester for you and will give feedback of how it could be improved.
often it is better to deliver a VW Golf within 1 year, than a rolls royce in 10 years :)
-
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.
No, that's not true; at least not in my experience. I had worked with Oracle's Designer tool to build dozens of DB schemas, and this tool never ever assumed 'preserve data' when generating the alter DDL statements.
In my experience, when developing applications and the back-end DB schemas, you'll want complete flexibility to revise the schema design during development.
We either had a set of manual 'load lookup table' and 'load test data' scripts, or had an off-the-shelf 'test data generator' utility.
In the 1st case, we manually updated these scripts as we revised the schema. In the 2nd case, we just re-ran the 'test data generator' utility, which could read the updated schema and revise its generation accordingly.
We always had a 'drop all' or 'truncate all' script that we ran in between revisions to the schema objects, and then we'd re-run whatever scripts on hand to pre-populate (or what I called 'bootstrapping') the schema.
At certain milestones, we'd run a fresh 'generate new' instead of 'alter ddl', to get DDL scripts that create brand new schema objects. And then the cycle would start anew, until we got to implementation.
---
In the case where you're running an 'alter ddl' on a production DB schema, well that's a different beast, and you'd want to manually write the 'move data from old to new' schema objects.
So, what I'm saying is (assuming Sparx hasn't already spent time on this feature) users probably won't require a 'preserve data' option when generating the ALTER DDL.
Cheers,
gary
-
Everything's quiet about the feature set in 7.0. I shouldn't really be optimistic, as I'm bound to be disappointed, but you never know......
-
Looking at recent and current design tasks I am working on I can only recommend to provide a simple solution first.
This could be limited to only growing the data model and maybe changing data types in a straightforward way.
(e.g. number to char, changing sizes)
Even deletion of complete columns/fields could be permitted causing an obvious loss of data.
I can only warn against an approach trying to solve all the problems at once. The general problem is very difficult, but a useful if limited solution should be not so hard.
I think this is better than nothing and trying to solve the problem and all its features for all cases is a massive task which will keep us from having a limited but already useful solution anytime soon.
I've seen a few problem solving attempts where nothing came out because the goal was put too high.....
-
Looks like I was being too optimistic...
-
Any news on this matter?
-
This did not seem to make it into version 7.0.
AFAIK there is no further news.
Sigh...
-
Hi,
we are currently evaluating EA, because we need a new tool for UML and data modeling. I'm also looking for this feature to alter an existing database. I wonder, why the suggested commands displayed when the database and generated DDL are compared cannot be executed.
JDeveloper form Oracle has this feature and is FOR FREE! Off course not everything is possible, but adding and deleting columns, constraints or procedures shouldn't be a problem.
It would also be nice to synchronize individual tables and not the whole schema. I really suggest you have a look at LDeveloper, which has really some nice features in thi aera!
So, the question is if there is a timeframe when this feature is implemented?
Best regards Thomas.
-
Hi Thomas,
No, I do not think there is a specific time frame. But this does not necessarily mean you cannot get what you need from EA. Sparx has done some work in this area, and continues to (gradually) add functionality.
The good news, in your case at least, is that Sparx has concentrated their work on a few major DBMS products, of which Oracle is one. That means that you have a better chance at solving this product on Oracle than on some other platforms.
Your comparison to JDeveloper is quite correct in this regard, but not necessarily valid across a broader perspective. JDeveloper addresses a much more specific set of needs, of which Oracle database development is one. EA is a UML tool, with data model functionality layered on. [See my other posts - well in the past - for issues relating to the legacy data modeling profile used in this case. I discuss why some limits still exist, and why these limits do not originate with either EA or Sparx.] JDeveloper does an excellent job of what it was intended for, but quickly becomes the wrong tool for other purposes - which is certainly not a flaw in JDeveloper.
So if you are looking for a general-purpose modeling tool, based on UML but with broad extensibility, and a high-end data modeling tool with solid round-trip engineering, then you will probably want to keep looking. If you can tolerate using two tools then EA and JDeveloper might be just what you need. This could be a particularly powerful combination if you work with Oracle and Java. [More or less so, depending on your development platform.]
In the latter of the above cases you might want to experiment with XMI (or other) transfers of model fragments between the tools. [Can any other users shed light on this for us?]
HTH, David
-
Hello David,
thanks for your answer! I don't want to say that JDevelope is the perfect tool (otherwise we would not look for an alternative!). It has it's limitations when you try to synchronize the datamodel with multiple database instances, and it doesn't support the conversion between different database types.
What we need is, to synchronze the logical database model with differeet database instances of different vendores (Oracle and MySql). We hoped, that we achieve this with EA, because it supports also the data type mapping. However es far as I can see now the database type is an attribute of the table. So when we want to generate a DDL for the different database types we have to change this in all tables?
However EA cannot help us in this aerea, so we'll have to look for another tool for this purpose. We just want not to spent money for another tool when this features are available in EA in the next two months.
Best regards Thomas
-
Hi again Thomas,
Perhaps you should take another look at EA, given your need.
What you can do with EA is create a Platform Independent Model (ala MDA) and a set of transformations that create models for each target architecture. That might do what you need.
In this case you have considerable control. You can also tweak the transformation templates to handle your specific requirements.
Take a look at the EA documentation, the white papers on the Resources page of the Sparx site, and then search this forum for more hints and such.
HTH, David