Toad World® Forums

Generate Alter Script questions


#1

In this example, I check out model 1 from the version manager, edit table-A by dropping several columns (none of them are FK cols or part of the PK).

I rename the model to model 2 and check it in as a version 2.0.

I next activate model 2, and choose “Merge Alter Script”.

Model 2 is on the left, Model 1 is on the right.

After clicking the Execute button, it runs for awhile and then I get a popup that says: “Generated script can produce data loss. Count of warnins : 1. Do you want to processed conversion without check?”

Minor point, but that message needs revised. Is it supposed to say “Do you want to proceed with conversion without check?” Or possibly, “Do you want generation of script to proceed without check?”

The fact that the word “conversion” is there gave me reason to pause. I had to wonder whether clicking Yes was going to cause my datatbase schema to be revised. I believe that the focus is creating the alter script, not converting the schema. Correct?

Anyway, what is the purpose of this dialog. If I click No, I get no alter script.

If I click yes, I do.

Now, question 2:

In my scenario, I dropped a few columns.

The generated alter script never dropped the columns that I dropped.

Here is what it did:

  1. Dropped all FKs which referenced Table-A
  2. Created Table-A_4A6A139BF60F471CA78E26F79
  3. Inserted all rows into that table from Table-A
  4. Dropped Table-A
  5. Altered the table created in (2), and renamed it to Table-A
  6. Added the PK
  7. Added all the column comments
  8. Added the FKs which it dropped in (1)

So what I end up with is Table-A exactly as it was before with no changes. The columns that I dropped did not get dropped by the alter script.

I believe the previous beta version that I used did not have this problem.

I’m not sure why it created a new table, populated it, then renamed it. It seems like it was trying to set up for preserving the contents of the dropped columns, which in this case I did not care to retain anyway.

Not sure what the intention here is, but in this case I just wanted to drop the columns.

Thanks

Morgan


#2

Hello Morgan,

Thanks very much for your post. Let me reply.

  1. We will revise the message. Suggested change:
    “Generated script can produce data loss. Count of warnings : 1.
    Do you want to proceed with script generation without check?”
    CR # 46 342.

what is the purpose of this dialog. If I click No, I get no alter script.
This is documented in the Help file, topic Alter Scripts. After you select No, warning icons + information in pop-up messages should appear.
However, in some cases, they are not displayed, which is, unfortunately, a bug.
Please find attached the extract from the Help file (steps 3, 4, 5) + information on the bug + suggested workaround.
We know about this bug and will fix it. Unfortunately, the fix will not be available in next official release 3.1.5. I’m sorry.

The generated alter script never dropped the columns that I dropped… I believe the previous beta version that I used did not have this problem.

You’re right. Some changes were made in this Beta. One of them is that now only items that are missing in Model2 are marked by the blue arrows to be applied (in your case, to be generated in the alter script).

In previous versions, all differences were marked by the blue arrows. Items missing in Model2 were marked to be added and extra items in Model2 were marked to be deleted. Users had to go through all of them and decide what actions they want to take, which was confusing.

So, back to the current state: Convertor shows all differences between Model1 and Model2, however now only the items that are missing in Model2 are marked by the blue arrow to be added (merged, generated in alter script) to Model2.

Example: Model 1 has Entity1 (no attributes), Model2 has Entity1 with three attributes. Differences are shown, however in Model2 there is nothing missing, therefore final alter script is empty (default behaviour).

Solution for you: Please see the first line with your model name in the Convertor. In column States, click the arrow on the left - all blue arrows will be left sided. Now click the arrow on the right - all blue arrows will be right sided.
In other words, you will refresh the status for Model2.
Now click Execute and see the result.

Well, I hope you can understand. Sorry for my English. If there’s anything unclear, please write me back. Thanks very much.

Regards,

Vladka
Alter_scripting.pdf (235 KB)


#3

Morgan,

Please watch this movie on this issue.
http://modeling.inside.quest.com/entry.jspa?externalID=1625&categoryID=273

I hope it will help you understand. Thanks.

Regards,

Vladka