Toad World® Forums

Reverse Engineering and Schema/Owner-Assignment


#1

Hello Toad Team,

i’am using the button “reverse engineering” to create a model from a mysql-5.1 database with the default settings from the wizard (Toad 5.0.5.31) and connection via tcp/ip as data provider.

This works good and the model is created …

But now i wonder why i need to manually relate a database-name to the model, like you see
in the following screenshot (i would have expected that the database-name of the database would be related automatically by the wizard?)

mysql51-databases-schema.png

After that i need to relate all my entities with this database by using “Tools”->“Schema/Owner Assignment”. Only if i do this the “DDL Script” and the “Change Script” Generation will create sql scripts that are database-specific. So far this is all ok with me. In my opinion generated SQL-Scripts should always specify the database-name, because only then they can be executed safely, so that not the wrong database is filled with data or somethin like that. Example:

ALTER TABLE **DEV_V720.**SHIPPING_COSTS ADD PRIMARY KEY (Country,ArtType,OrderValue,ShopID)
;
ALTER TABLE **DEV_V720.**SHOPASPECT ADD PRIMARY KEY (ShopAspectID)

So far all ok with me…

But now when i want to synchronize the model with the database, to merge new changes from the database into the model (via “Model”->“Synchronize”->“Update Model”) all those schema/owner assignments will be shown as changes. Is there any way to hide those irrelevant changes ?

thanks in advance for help

Bernd Huber
orbiz Software GmbH


#2

I just tried doing a reverse engineer of a MySQL 5.1 DB and it did assign the database name to each entity. Could it possibly be something in the Options during the reverse? Here is how mine are set.


#3

Hello Kevin,

i can show you all set options for info. You need to know i still use the trial-version for evaluation of Toad.

Reverse5.png

for Info: i know that 300 Entities are too much for the trial version, i also tried with a database that only contains 20 entities.
same problem.

greetings

Bernd Huber


#4

I got the same result as you when I specified the database name in the connection window. Try leaving the database name blank and see if that makes a difference for you.


#5

Hi Bernd and Kevin,

As you found, there are two ways to connect when doing RE of MySQL and therefore two ways RE behaves.

It depends whether you connect directly to a database (internal database object in MySQL), or not.

5153.mysql_re_db_1.jpeg

If you do not specify the Database Name, then when you RE a table you can select a database (or multiple dbs) from which to RE. Once RE is finished, the tables are matched with particular databases.

5658.mysql_re_db_2.jpeg

On the other hand, if you specify the Database Name in the Connecting pane, there is no Database selection option in the last step.

After RE the Database object is not created and thus the table is not matched.

When running SQL script on a database it is possible to be logged in the database or not the same way.

If you are on the database directly, there is no need to specify FullName in Create statements etc…

Regards,

Lukas


#6

Bernd, one more thing concerning the differences during Model Update.

When you RE and you specify the Database Name, the database object is not created, therefore the table is not matched in the database. Then when you create and the database/schema object manually and add it in the table, you get to see this difference during Model Update.

Either do not create and match the database object manually, or during RE use connection without specifying the Database Name in Connecting pane and select the Database in the last step.

Regards,

Lukas


#7

thanks Kevin and Lukas, the provided solution works.

i leave the database-name blank now in the reverse-engineering-connection.

Afterwards Model-Update works like expected and does not show any changes, and all entities are related to their database.
Script-Generation also works like expected and contains the database-name.

thanks,

Bernd


#8

You’re welcome! Glad to hear it works as desired.

Lukas


#9

Thanks for add me as a member.