Loading databases and custom DDL generation

Toad Data Modeler
1

I am researching a DB model tool that can help us with the following 1. Common way to represent schema and make changes to it (Reverse engineering features will be helpful here)2. Ability to generate DDL scripts across databases – Oracle, Postgres, Sql Server, DB2 etc, basically forward engineering features. Should be able to customize DDL generated per database3. Change management: Can generate diff sql for various databases by comparing models. 4. Provides a way to load DDL to the DB – should help out in dropping/creating databases etc across databases.5. Ability to represent DML across databases and load it. For eg: just like DDL is abstraced away by the model, can we have a common syntax for DML across DBs?6. Versioning support I went through the videos on your site and looks like 1,2,3,6 are supported. I need the following clarifications: · Is there support for 4 and 5?· How much control do I have in the DDL generated per database? Can I use custom scripting for DDL generation? If so, are there any limitations. Basically, I want the tool to remember the setting I enforce through scripting, lets say for a specific DB.Thanks-Sai

2

I am posting it again as the text did not get formatted right - sorry.

I am researching a DB model tool that can help us with the following

  1. Common way to represent schema and make changes to it (Reverse engineering features will be helpful here)
  2. Ability to generate DDL scripts across databases – Oracle, Postgres, Sql Server, DB2 etc, basically forward engineering features. Should be able to customize DDL generated per database
  3. Change management: Can generate diff sql for various databases by comparing models.
  4. Provides a way to load DDL to the DB – should help out in dropping/creating databases etc across databases.
  5. Ability to represent DML across databases and load it. For eg: just like DDL is abstraced away by the model, can we have a common syntax for DML across DBs?
  6. Versioning support

I went through the videos on your site and looks like 1,2,3,6 are supported. I need the following clarifications:

Is there support for 4 and 5?
How much control do I have in the DDL generated per database? Can I use custom scripting for DDL generation? If so, are there any limitations. Basically, I want the tool to remember the setting I enforce through scripting, lets say for a specific DB.

Thanks
-Sai

3

Hello Sai,

Thanks for your interest in Toad Data Modeler. Let me reply to your questions.

  1. Common way to represent schema and make changes to it (Reverse engineering features will be helpful here)

Yes, you can reverse your database easily and make changes in the model then.
TDM offers: standard RE, LIVE RE, loading of DDL script (not for all databases*).
For details, please see the Help file, Index tab and type the keyword.

Flash movies to see TDM in action:
RE:
http://modeling.inside.quest.com/entry.jspa?externalID=1673&categoryID=158
LIVE RE:
http://modeling.inside.quest.com/entry.jspa?externalID=1684&categoryID=158
Load model from DDL:
http://modeling.inside.quest.com/entry.jspa?externalID=2062&categoryID=158

  • Note: TDM can load DDL of the following databases:
    DB2 UDB 8, DB2 v9, DB2 v9.5, MS SQL Server 2005 and 2008, MySQL 5.0 and 5.1, Oracle 9i, Oracle 10g and Oracle 11g
  1. Ability to generate DDL scripts across databases – Oracle, Postgres, Sql Server, DB2 etc, basically forward engineering features.

Yes, you can generate DDL script for the databases (F9). TDM also allows you to generate alter script.
Forward engineering features are not supported in TDM.

Flash movies:
Create ERD, Generate DDL:
http://modeling.inside.quest.com/entry.jspa?externalID=1667&categoryID=158
Generate alter script:
http://modeling.inside.quest.com/entry.jspa?externalID=3029&categoryID=158

Should be able to customize DDL generated per database.
Customization of generated output is possible, however it is not as easy for users as we would like it to be. We are aware of it and plan to make significant changes. So, current situation is: yes, the customization is possible. However, we prefer the following: write us what exact changes you need to make and we will guide you through the customization process - in your particular use case.
(You can write to our developers directly to modeling@quest.com.)

  1. Change management: Can generate diff sql for various databases by comparing models.

Yes, TDM allows you to compare models and see the differences, generate Alter report. You can merge models, generate alter script for changes between models.
All this can be performed in Sync&Convert Wizard.
For each operation there is a flash movie.
http://modeling.inside.quest.com/kbcategory.jspa?categoryID=158

  1. Provides a way to load DDL to the DB – should help out in dropping/creating databases etc across databases.

Forward engineering is not supported. TDM cannot help you to drop/create database either. You have to use a third party software to execute the DDL script generated in TDM. For Oracle db, you can use e.g. Toad for Oracle.

  1. Ability to represent DDL across databases and load it. For eg: just like DDL is abstraced away by the model, can we have a common syntax for DML across DBs?

Do you mean one DDL valid for all databases? If so, TDM is not able to load/generate such, let's say, universal DDL. (Maybe we don't understand it correctly. Sorry.)
Anyway, please write us more details, your workflow to have a better idea of what you mean. Thanks.

  1. Versioning support

You can use the Version Manager feature. You create projects and add models (and other files) to them and keep track of their versions/revisions.
Version Manager flash movie:
http://modeling.inside.quest.com/entry.jspa?externalID=1697&categoryID=281

  1. I need the following clarifications:
  • How much control do I have in the DDL generated per database?

Unfortunately, we are not sure what you mean. Anyway, DDL is all generated via script in JScript. You can modify it, although it is not easy.

  • Can I use custom scripting for DDL generation? If so, are there any limitations. Basically, I want the tool to remember the setting I enforce through scripting, lets say for a specific DB.

As I wrote above, customization is possible. Write us details and we will help you.
Generally, for every model type (ORACLE10G, MySQL etc.), an object called Generator exists. Methods of the Generator are scriptable and therefore it is possible to modify any generated code.
We can discuss more specific issues via email. Our developers will reply to your questions. In any case, it is good to show/explain it on particular example.

When you press F9 to open the DDL Script Generation dialog, you may notice the Save Settings button. All settings you define on tabs of the dialog can be saved via this button. It will be saved together with the particular model (you have to save the model too).
Other nice features are e.g. customization of OTPs and setting default values.
Please feel free to watch movies on them (it takes few minutes and you will see how it works):
OTPs:
http://modeling.inside.quest.com/entry.jspa?externalID=1695&categoryID=281
Default Values for Class:
http://modeling.inside.quest.com/entry.jspa?externalID=1696&categoryID=281

Have a nice day.

Regards,

Vladka + TDM Team

4

Thanks and I appreciate the prompt replies.

  1. Ability to represent DDL across databases and load it. For eg: just like DDL is abstraced away by the model, can we have a common syntax for DML across DBs?

Do you mean one DDL valid for all databases? If so, TDM is not able to load/generate such, let’s say, universal DDL. (Maybe we don’t understand it correctly. Sorry.)
Anyway, please write us more details, your workflow to have a better idea of what you mean. Thanks.

[Sai]: Sorry - my mistake for not being clear. What I meant is do we have any help for generating DML for DB initial data. I mean the static app data that we need in the schema before we can start using it. For eg: A table name event_types containing all the various event types possible.
Basically, this will help us from not having to maintain initial data scripts across databases. Just like we will have one model - we can have one representation of static data in database.

Another question I had was about the Alter script - the diff between models. Customization of the Alter script generated follows the same paradigm used for full DDL generated?

Btw, our current DB is postgres and we have to support Oracle and DB2 soon. Too bad I cannot reverse engineer Postgres. Are there any workarounds around this - because once I have the model, then I should be good for DDL generation, comparing and generating Alter scripts etc for Postgres, right?

Thanks
-Sai

5

Hi,

Thanks for your reply.
Firstly, let me have a quick comment on:

Btw, our current DB is postgres and we have to support Oracle and DB2 soon. Too bad I cannot reverse engineer Postgres. Are there any workarounds around this - because once I have the model, then I should be good for DDL generation, comparing and generating Alter scripts etc for Postgres, right?

TDM trial/commercial version supports the following versions of the databases you mentioned:
DB2 9.5 (LUW), DB2 v. 9 (LUW), DB2 UDB v. 8 (LUW), Oracle 11g Release 2, Oracle 11g Release 1, Oracle 10g, Oracle 9i, PostgreSQL 8.3, PostgreSQL 8.2, PostgreSQL 8.1

Moreover, current TDM BETA 3.5.2 supports also PostgreSQL 8.4.

Postgresql is fully supported - - RE, DDL.... including alter script generation.

Why can't you reverse engineer PostgreSQL? What version do you use?
If v. 8.4, please feel free to use BETA to check it out.

Regards,

Vladka

6

Customization of alter script is much more difficult. We don't recommend users to spend time on this.

Regards,

Vladka + TDM Team

Hello Sai again,

Let me finish my reply.

What I meant is do we have any help for generating DML for DB initial data. I mean the static app data that we need in the schema before we can start using it. For eg: A table name event_types containing all the various event types possible.
Basically, this will help us from not having to maintain initial data scripts across databases. Just like we will have one model - we can have one representation of static data in database.

Now we understand. Thanks.
Unfortunately, TDM doesn't allow you to do this. In TDM, you can write data to AfterScript but that is all.
In any case, it is a very interesting idea. If you know a tool that can do it, please let us know. We are interested.

Another question I had was about the Alter script - the diff between models. Customization of the Alter script generated follows the same paradigm used for full DDL generated?

7

I have a question in a different area. Once I reverse engineered from, lets say, postgres, I only seem to be able to generate DDL for postgres. How can I generate DDL from the model to Oracle or other database?

I did not see this info in any of the training videos.

Does this require saving to a different model? If so, then we will end up having to maintain parallel hierarchies (as product progresses thru releases) of model per database vendor. That would mean again having to change model at different places.

Please clarify. It would be great if we can have one logical model and have control over the datatypes and the DDL generated per DB.

Thanks
-Sai

8

Hello Sai,

Let me reply.

Once I reverse engineered from, lets say, postgres, I only seem to be able to generate DDL for postgres.

Yes, it is right. You can generate DDL only for the database of which the model is.

How can I generate DDL from the model to Oracle or other database?

You can convert the Postgres model to Oracle model.
This is possible either via Simple Model Conversion of in Sync & Convert Wizard where you can set up some options for conversion.
Be aware of changes between the databases - different data types, SQL code will be commented, objects that do not exist in target database etc.
Please see the Help file on Contents tab | Model | Model Conversion.

Flash movies:
About setting up data types before conversion:
http://modeling.inside.quest.com/entry.jspa?externalID=2063&categoryID=158
Conversion:
http://modeling.inside.quest.com/entry.jspa?externalID=3027&categoryID=158
http://modeling.inside.quest.com/entry.jspa?externalID=2064&categoryID=158

Does this require saving to a different model? If so, then we will end up having to maintain parallel hierarchies (as product progresses thru releases) of model per database vendor. That would mean again having to change model at different places.

Possible solution would be the following:

  1. You convert Postgres model to Oracle -> Ora Model 1 is created.
  2. You make changes in Postgres model again. - You can do the conversion to Oracle again. -> Ora Model 2 is createad.
  3. You can compare and merge the two Oracle models (Ora Model 2 - source model, Ora Model 1 target model). You can do this for all other databases - convert again and merge two models of the same db system.

Note: Default behavior during model merge is: Take changes from source model (Left model) and apply them in target model (Right model). After the merge, the two models are identical (target = source). The merge process is easy but can be more complicated provided that you made changes in Ora Model 1 too - and you wanted to keep the changes there. In this case, you would have to go through the list of changes carefully before you merge the models. I mean this: To keep the changes that are in target model and are not in source model, you would have to disable the Action box for the particular change. Example on attribute: Attribute X does not exist in source model but exists in target model. Default behavior of TDM: Action box for Attribute X is selected = take the change from source model and apply it in target model = delete Attribute X from target model. If you didn't want to delete the attribute from target model, you would have to clear the Action box.
In other words, during model merge TDM synchronizes target model so as it is identical with source model.

Please watch the movie + read the PDF document at:
http://modeling.inside.quest.com/entry.jspa?externalID=3028&categoryID=158
http://modeling.inside.quest.com/entry.jspa?externalID=3055&categoryID=158

It would be great if we can have one logical model and have control over the datatypes and the DDL generated per DB.

Yes, you can have one logical model and convert it to various database systems. Let me just write that logical model is giving a picture of the business area. Logical model is independent of the database platform and is much simpler than physical model. It uses objects such as inheritance or valid values.
There are three methods how inheritances can be converted to PER model.
In LER model, attributes do NOT migrate to child entities.
It is not possible to define some particular settings in LER model (autoincrement etc.)

I recommend to have a look at the Help file - Model | Model Conversion. + Model | Logical Model | Conversion to Physical Model where you can find details, screenshots etc.

Regards,

Vladka + TDM Team