Author Topic: DDL to manage a changing schema  (Read 6428 times)

Graham_Moir

  • EA User
  • **
  • Posts: 708
  • Karma: +6/-9
    • View Profile
Re: DDL to manage a changing schema
« Reply #15 on: March 23, 2007, 02:26:41 am »
Any news on this functionality ?  6.6 ?  7.0 ?
thanks

mfritz

  • EA Novice
  • *
  • Posts: 16
  • Karma: +0/-0
  • Visualize the World!
    • View Profile
Re: DDL to manage a changing schema
« Reply #16 on: April 06, 2007, 03:53:32 am »
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 :)

Gary W.

  • EA User
  • **
  • Posts: 139
  • Karma: +0/-0
    • View Profile
Re: DDL to manage a changing schema
« Reply #17 on: April 09, 2007, 08:03:23 pm »
Quote
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

Graham_Moir

  • EA User
  • **
  • Posts: 708
  • Karma: +6/-9
    • View Profile
Re: DDL to manage a changing schema
« Reply #18 on: April 27, 2007, 12:09:29 am »
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......

doctor_uv

  • EA Novice
  • *
  • Posts: 17
  • Karma: +0/-0
    • View Profile
Re: DDL to manage a changing schema
« Reply #19 on: May 08, 2007, 09:24:36 am »
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.....
« Last Edit: May 08, 2007, 09:27:18 am by doctor_uv »
Dr. UV
Software Architect
Alcatel-Lucent IPTV

Graham_Moir

  • EA User
  • **
  • Posts: 708
  • Karma: +6/-9
    • View Profile
Re: DDL to manage a changing schema
« Reply #20 on: May 22, 2007, 11:23:57 pm »
Looks like I was being too optimistic...

scania

  • EA Novice
  • *
  • Posts: 1
  • Karma: +0/-0
  • I love YaBB 1G - SP1!
    • View Profile
Re: DDL to manage a changing schema
« Reply #21 on: September 11, 2007, 09:56:11 am »
Any news on this matter?

«Midnight»

  • EA Guru
  • *****
  • Posts: 5651
  • Karma: +0/-0
  • That nice Mister Grey
    • View Profile
Re: DDL to manage a changing schema
« Reply #22 on: September 11, 2007, 09:57:31 am »
This did not seem to make it into version 7.0.

AFAIK there is no further news.

Sigh...
No, you can't have it!

Thomas Mayr

  • EA Novice
  • *
  • Posts: 12
  • Karma: +0/-0
    • View Profile
Re: DDL to manage a changing schema
« Reply #23 on: August 20, 2008, 12:32:20 am »
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.

«Midnight»

  • EA Guru
  • *****
  • Posts: 5651
  • Karma: +0/-0
  • That nice Mister Grey
    • View Profile
Re: DDL to manage a changing schema
« Reply #24 on: August 20, 2008, 02:48:46 am »
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
No, you can't have it!

Thomas Mayr

  • EA Novice
  • *
  • Posts: 12
  • Karma: +0/-0
    • View Profile
Re: DDL to manage a changing schema
« Reply #25 on: August 20, 2008, 08:33:50 pm »
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

«Midnight»

  • EA Guru
  • *****
  • Posts: 5651
  • Karma: +0/-0
  • That nice Mister Grey
    • View Profile
Re: DDL to manage a changing schema
« Reply #26 on: August 20, 2008, 10:59:04 pm »
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
No, you can't have it!