Changing an attributes data type

[SinoTech]

Dear all,
We are working with several partners on a project based on a mysql repository. Each partner defines one or more services which hence will work together. Till now every partner has defined data types (Such as collections, ...) by its own needs, which has lead to inconsistencies between the resulting services. Some weeks ago we've tried to harmonize that and we've defined a bunch of data types which shall be used when applicable (Only define new data types when needed). Already existing type definitions were deleted.
Now we've got the problem that operation parameters and class attributes still refer to the old data types, which in fact doesn't exist anymore. Our first thought was that each partner has to check for consistency within its own services. However, after we made a list of inconsistencies (operation parameters, return types and class attributes refering to non-existing data types), we've seen that's a big work to perform. So we thought about updating class attributes and so on by accessing the MySQL database directly (So we can update a bunch of attributes in one step without having to select each of them separately in the EA).
For updating the data type of a class attribute, I would simply update the following column:

t_attribute.Classifier

For the return type of an operation the following column
t_operation.Classifier

And last but not least the follwing column for updating an operations parameter.
t_operationparams.Classifier

So all I want to know is, if the columns shown above are the sole points where updates must be performed, or are there any other tables affected as well?
(The EA repository consists of 93 tables and I 'am not sure if there's somewhere an important link between those which I have overseen).

Best regards,

Sino

[Eve]

You've got the right columns there.  I just want to say to make sure you update the Type columns with the names of each type too.

[SinoTech]

You've got the right columns there.  I just want to say to make sure you update the Type columns with the names of each type too.

Thank you for your reply, that was what I#ve expected.

Oh, and one more thing. If I try to change the data type of an operations parameter in the EA, but both have the same name, then EA won't do anything. e.g. the data type "CharacterString" was defined multiple times. Now I want to change in each operation the parameters type from the currernt CharacterString (which obviously doesn't exist anymore) to the sole existing version of CharacterString. After that I see in the database that the parameter still refers to the old non-existing data type. The solution currently is to first select another data type (having a different name) and after that select CharacterString again.
So is this a known Problem that EA compares data types by their names instead of their ID's?

However, think I'll do most of the changes on the database directly.

Thank you again for you reply.

Best regards,

Sino

[«Midnight»]

Sino,

You should be able to set up a trigger - I think this will work with either MySQL engine - to handle updates to the 'using' tables when a datatype is changed. EA has several tables with both primary and candidate keys, so this might help you elsewhere.

EA is pretty good about tolerating triggers and stored procedures layered on top of its base functionality.

That said, test (test, test, test...)

David

[SinoTech]

Sino,

You should be able to set up a trigger - I think this will work with either MySQL engine - to handle updates to the 'using' tables when a datatype is changed. EA has several tables with both primary and candidate keys, so this might help you elsewhere.

EA is pretty good about tolerating triggers and stored procedures layered on top of its base functionality.

That said, test (test, test, test...)

David

Thank you for your answer, Unfortunatelly we're still using MySQL 4.0, which doesn't support tiggers, views, procedures, ... :-(. Even sub-queries are not supported which let me run into some trouble the last two days (I've solved with a local install of mysql-5 on my personal computer and then importing a Backup from the original repository).
However, seems as I've to talk to my boss and ask him to upgrade the database engine.
Thanks again for your answer.

Best regards,

Sino

[thomaskilian]

MySQL 5 supports triggers. But is it already released?

[SinoTech]

Yes, according to "http://dev.mysql.com/downloads/" MySQL-5.0 is the current stable release and 5.1 is beta in the moment.

Regards,

Sino

[«Midnight»]

Before you go too far, take a look at the MySQL site for information about the longer-term future of the InnoDB engine.

There is another popular engine that MySQL supports, but I do not know if it also allows triggers etc. in version 5.0.

Also, MySQL will soon - for some value of "soon" - have its own engine. Once again, take a look at their site.