Toad World® Forums

Strange things in generated MySQL Alter Script


#1

I updated my MySQL model:

  • renamed all PK columns (Caption is the old name, Name the new name)
  • renamed 7 FK relations

Look at the generated alter script…

The create table statements generate several PK columns!

What’s going on here? I guess it’s a problem with different Name/Caption of the PK columns!?

I will try it with both called id…

Andreas


#2

The different Name/Caption seems NOT to matter.
I tried again with the same result, e.g.:

CREATE TABLE MX_MATERIAL_CONFIRMATION_377E9F4286F743539A6A3FF52F84F6C2
(
id Bigint NOT NULL AUTO_INCREMENT,
id Bigint NOT NULL,
MAC_WKU_ID Bigint,
MAC_MATERIAL_NO Varchar(255),
MAC_SERIAL_NO Varchar(255),
MAC_NAME Varchar(255),
MAC_STORAGE Varchar(255),
MAC_AMOUNT Int,
MAC_AMOUNT_UNIT Varchar(255),
MAC_SJM_EXPIRY_DATE Datetime,
MAC_SJM_FROM_HOSPITAL_STORAGE Bool NOT NULL,
MAC_SJM_COMMENT Longtext,
MAC_ID Bigint NOT NULL AUTO_INCREMENT,
id Bigint,
id Bigint,
WKU_ID Bigint,
ASG_ID Bigint,
PRIMARY KEY (id,MAC_ID),
INDEX MATERIAL_CONFIRMATION_FKIndex1 (id)
)

  • new PK column id is generated 4 times
  • old pk MAC_ID is generated again (was removed from model)
  • PK constraint id is generated as (id, MAC_ID)

Andreas


#3

Andreas,

Could you please send us both models you are comparing? Our developers will check out the problem. The models will be used only for the testing purposes.
If it is possible, please send them to: modeling@quest.com.

Thanks very much.

In any case, we are verifying the problem now, trying to simulate the problem.

Regards,

Vladka


#4

Models sent…

Andreas


#5

Hello Andreas,

Thanks. We created a simple MySQL example and managed to simulate the situation.
We know the Convertor is not an easy thing in TDM to understand at all… Anyway, although it seems strange, the behavior is correct. Let me explain in brief why:

Items in Convertor are compared by physical names. So, if you rename e.g. an attribute in Model 1, Convertor does not recognize it is the same attribute that already exists in Model 2 and is only renamed
BUT
considers it as a new attribute. So, you can see the attribute again.

Moreover, if you rename a relationship, it is considered as a new relationship. With a relationship, PK attribute is propagated. So, in Model 2 the PK attribute is generated once again.

Other important information on Convertor:

  • Blue arrows in Convertor show your intention to generate the change (in alter script or apply the change in Model2).
  • In Convertor, you can generate alter script and also merge models (to update existing model or to create a new model). The default settings of blue arrows is this: Take the objects (that are missing in Model 2) from Model1 and add them to Model 2
    and
    take the changed properties of existing objects from Model 1 and apply them in Model 2.

The default settings of blue arrows do not remove (drop) objects. If you want to do so, you need to make modifications of the blue arrows.

In your case (provided that you only changed the names and did not make other changes in object properties), simply refresh the first blue arrow (click it - the icon will change, and click again to display the blue arrow in direction from Model 1 to Model 2 - =>.)

Then you will get this:
CREATE TABLE MX_MATERIAL_CONFIRMATION_377E9F4286F743539A6A3FF52F84F6C2
(
ID Bigint NOT NULL AUTO_INCREMENT,
MAC_ASG_ID Bigint NOT NULL,
MAC_WKU_ID Bigint,
MAC_MATERIAL_NO Varchar(255),
MAC_SERIAL_NO Varchar(255),
MAC_NAME Varchar(255),
MAC_STORAGE Varchar(255),
MAC_AMOUNT Int,
MAC_AMOUNT_UNIT Varchar(255),
MAC_SJM_EXPIRY_DATE Datetime,
MAC_SJM_FROM_HOSPITAL_STORAGE Bool NOT NULL,
MAC_SJM_COMMENT Longtext,
PRIMARY KEY (ID),
INDEX MATERIAL_CONFIRMATION_FKIndex1 (MAC_ASG_ID)
)

It should be right now.

Regarding Convertor, please visit this site and read the series on Convertor to understand how it works:
http://modeling.inside.quest.com/kbcategory.jspa?categoryID=159
(Convertor + Generate Alter Script - Part I, Part II)

In case of any questions, please write me back. Thanks.

Regards,

Vladka


#6

I do - right now - not really understand what happens through deselecting and selecting the first blue arrow, but the generated alter script looks much better.

But the INSERT INTO statement does not copy the PK column (I do not use auto increment).

I’ll read your docu hint, perhaps I’ll understand it better after reading…

Andreas


#7

Just very briefly:
If a significant change is generated in alter script, TDM creates a temporary table where the changes are added, existing items inserted. Then the original table is deleted and the temporary table is renamed.

In your case: temporary table -
MX_MATERIAL_CONFIRMATION_377E9F4286F743539A6A3FF52F84F6C2

then later in the script -
DROP TABLE MX_MATERIAL_CONFIRMATION
;
ALTER TABLE MX_MATERIAL_CONFIRMATION_377E9F4286F743539A6A3FF52F84F6C2 RENAME TO MX_MATERIAL_CONFIRMATION
;

It is explained in the series about Convertor that I sent you a link to.

Regards,

Vladka