Toad World® Forums

Sync & convert generate alter script View of what to change wrong ?


#1

Hello,

may be I do not correctly understand the display of the “what to create on the right and what to delete”.

  1. I’am re-engeneering on the fly on the right and my model on the left. I use a user, which has permissions to three different Schemas/Users. I only select all tables from one user. But on the display I see tables and relations of all three Schemas.
    OK, all of the selected schema are blue, but the not selected are green, what means
    he will create them. But why ? Why I have to deselect them ?
    I have not selected this schema at the time of reengeneering.
    Could it be, that data modeler takes all schemas accessible of the used user for reengenering than taking only the selected tables ?

In other words : how can I select on the left side what I want to put on the right side, if the model a little bit bigger ? And it is not helping to have only one of the three workspaces ( these with the schema user wanted ) open.

  1. At relations he is not showing the schema names. Same with rules.

Best regards

Linus


#2

Hi Linus,

thank you for your post. Unfortunately, I am a bit confused and I’d like to ask you for details. Please try to do the following:

Do reverse engineering of your existing database (select tables from one schema only). New model will be created. Then start the Sync & Convert Wizard, set your original model to left side and your reversed model to right side. Then please define settings in groupbox Options for Default Selection of Items in the fourth step of the Sync & Convert Wizard (step Settings). Then try to finish the wizard and generate alter script.

If possible, please send us screenshots. The following part of your post “…not selected are green…” makes me confused.

In general, there are many situations when it is necessary to synchronize a model with your physically existing database structure. For example: You have a model for your physically existing database and you wish to enhance it. In this scenario you will have your “original model” equal to your database and then another model to which you will add new objects, drop some objects from it, modify something etc. Your aim will be to generate alter script that will modify your physically existing database. That means that your modified model will have to be set to Left Side and your original model will have to be set to Right Side. Alter script for changes that exist in left model will be generated. That means, all objects that exist in left model and don’t exist in right model will be created, all objects that don’t exist in left model but exist in right model will be dropped and all for all modified objects alter script will be generated - considering the default settings in fourth step of the wizard are left untouched.

In your case, if you try to compare your model with three schemas (left side) with reverse engineered model where only one schema exists (right side), then you will have to tell the application to not use the two additional schemas. Alternatively, you can remove the two schemas from your model and then do the comparison of “simplified model” with “reversed model”. I guess what you are trying to do is to compare large model with something like a “subset” of your model.

I’d like to also mention that there are also things that cannot be compared perfectly. E.g. if you use domains in your model and then you will reverse engineer a model and compare the model where domains are used with reversed model, then there certainly will be differences, just because domains are logical items that are converted to SQL code - but backward guessing what might be domain in your model - this is something that no application can do to an acceptable level of satisfaction. Of course, there is a workaround - in similar situations, don’t use domains. Similarly, if you wish to work with a model where there are three users and you wish to compare a subset of the model with existing database, it might be better to split the model to three independent models first and then do the comparison…

I hope it helps,

Have a nice day,

Vaclav


#3

Hello Vaclav,

OK, understood - but if I select to create a script ( normal DDL, no sync and convert ),
I’m able to select the schema/user for which I wish to create the statements. And I can select which Workspace I can use to generate DDL script and I receive a fine script only for that schema.

For me it seems to be logical, that I will have the same choices to generate from schema/user in the minimum, may be from schema/user/workspace in the maximum.

I don’t want to use domains.

Your proposal to separate tables in different models to achive the possibility to change with sync&convert/alter script only for one user is away from a realistic usage.
If I have a production schmema and I put the historical archive data in another schema and have relations between them ( because I have to have access to the old data ) you propose to put the production data ( one schema ) in one model and the archive in another model for the possibility to alter schemas independently ? What should I do in this case with my relations ? Do you have a functionality for creating entieties over model boundaries ?

I believe many softwares have such a structure. I know one additional in the minimum … May be it’s a german approach.

OK.

What about my bullet point Nr. 2 about not schowing the schema-names for rules and relations ?

Best regards

Linus


#4

Hi Linus,

thank you for your reply. You are right, there are options that allow you to generate new SQL script for selected schema and for selected workspace. I agree it may seem to be logical to have these options for generation of alter scripts. I will create new enhancement request for this and the request will be discussed with our team members/developers. Thank you for your suggestion.

Re domains: this was just an example and I only wanted to write you that there are limits in what you can do with reverse engineered model.

Re separation of tables: I understand that in this case separation of tables might not be useful. However, if you compare your model where all shemas are defined, with reversed model where only one schema exists, then you will have to setup more settings in the Sync & Convert Wizard manually (and ucheck the items in green that you don’t want to create etc.). Or… you may try to do reverse engineering that will contain as much identical objects as possible - in other words, using reverse engineering create a model where all the three schemas exists and then do the comparison. But I understand that this process may require time.

Re your questions: There are many ways how to achieve similar result, especially in the world of programming. As you can see, also in this case you have several possibilities. If you separated tables, you would have troubles with your relations. How often do you change such relations? If more than rarely, then you have two other options.

What may help you is popup menu in the Sync & Convert Wizard. For example, if you know there is no object that should be created you can (in step Select Items, where a a comparison table of two models is displayed) right click the model name and select “Uncheck All Children to Add”. The same works if you click Entities item or any other object group item. This nice feature may save your time. :slight_smile:

Re schema names for rules and relations: I need to discuss it with my co-workers/developers/DBAs. Some of them are on vacations now and will be back on 4th January. We will discuss it during our standard dev meeting and I will write you more details laters. Thank you for understanding it.

Regards,

Vaclav


#5

Hello Vaclav,

I will only answer your proposals for “Re separation of tables:” for now :

I’m a little bit anxious yet about using sync and convert and for that I would only uncheck something I have seen … That means, I have to make all visible and scroll through it. That takes time 1. for opening, 2. for scrolling through all the stuff … , 3. to uncheck.
I would feel much safer, if I could separate before what Data Modeler would take an eye on … ( for minimizing the faults … :wink: )

Best regards

Linus


#6

Hi,

Just a quick explanation:
Show schema names for relationships in Convertor - Unfortunately, it is not possible because relationship in TDM doesn’t belong to a particular table. In TDM it is a connection of two tables. And these two tables can be each in a different user/schema.

Thanks for understanding it.

Regards,

Vladka + TDM Team


#7

Hello Vladka,

Okeeeee.

What about bullet point No. 1 ?

Linus


#8

Hi Linus,

generation of alter scripts by schema/workspace is not available, however you have several options:

a] divide your model to smaller models (one model per schema) and then compare one smaller model with your RE model.

b] RE all tables from all schemas. Then compare your model with the complex RE model.

c] Continue the same way as you work now and compare your model where all three schemas exist with a model where only one schema exists, but you know you have to do a detailed selection manually.

In the latest beta you can use Wildcard filter where you can specify Name with wildcard. For example: SCOTT*. See the attached screenshots. All objects where Name starts with SCOTT will be checked/unchecked. The filter only works for objects in Model1 (left side) and not in both models. What does it mean? Let’s say you have entity SCOTT.Customer in model2, but this entity doesn’t exist in model1. If you use filter SCOTT*, the entity will not be checked for “Drop”, because the filter won’t find it in model1.

Regards,

Vaclav


#9

Hi Linus,

generation of alter scripts by schema/workspace is not available, however you have several options:

a] divide your model to smaller models (one model per schema) and then compare one smaller model with your RE model.

b] RE all tables from all schemas. Then compare your model with the complex RE model.

c] Continue the same way as you work now and compare your model where all three schemas exist with a model where only one schema exists, but you know you have to do a detailed selection manually.

In the latest beta you can use Wildcard filter where you can specify Name with wildcard. For example: SCOTT*. See the attached screenshots. All objects where Name starts with SCOTT will be checked/unchecked. The filter only works for objects in Model1 (left side) and not in both models. What does it mean? Let’s say you have entity SCOTT.Customer in model2, but this entity doesn’t exist in model1. If you use filter SCOTT*, the entity will not be checked for “Drop”, because the filter won’t find it in model1.

Regards,

Vaclav


#10

Hi Linus,

generation of alter scripts by schema/workspace is not available, however you have several options:

a] divide your model to smaller models (one model per schema) and then compare one smaller model with your RE model.

b] RE all tables from all schemas. Then compare your model with the complex RE model.

c] Continue the same way as you work now and compare your model where all three schemas exist with a model where only one schema exists, but you know you have to do a detailed selection manually.

In the latest beta you can use Wildcard filter where you can specify Name with wildcard. For example: SCOTT*. See the attached screenshots. All objects where Name starts with SCOTT will be checked/unchecked. The filter only works for objects in Model1 (left side) and not in both models. What does it mean? Let’s say you have entity SCOTT.Customer in model2, but this entity doesn’t exist in model1. If you use filter SCOTT*, the entity will not be checked for “Drop”, because the filter won’t find it in model1.

Regards,

Vaclav