when I rename a column in my model, the alter script generator generate a “drop column” and a “create column” DDL instead of “rename column” DDL. When I run the script, it cause data lose. As I read in an earlier thread, the comparization is based on physical attr name. But I see in the model file that all elements have an internal id. If I compare other versions of the same model, can I use this id as the base of the comparization by checking a checkbox or some similar?
If no such easy way, can I inherit the attribute comparization algorithm and the script generation algorithm in a new package? As I see, lot of form can customize, but not the “Sync & convert wizard”. I searched a cadidated method which is run by wizard, but I did not found. Maybe in the PERCodeGeneratorOR script?
Maybe the field renaming is a rare modification, but lost data is not a lucky way. Can anzbody help to solve this problem?
I too am interested in the answer to this problem. I don’t think renaming a column is a ‘rare modification’, and I too would have thought that if you are using the “Sync and convert” wizard it should be able to use the internal ID’s to detect a column name change - as long as you are comparing two models.
In this case, the generated SQL should simply be an “alter table xxx rename column old_col to new_col” with no data loss.
Can the developers please comment on whether there is a solution to this issue?
Sure you can rename in Toad (or any other Oracle client tool). However if you are using your model as the primary record of your design, it is a real nuisance to have to remember if you’ve changed any names before you go to synchronise the model with the DB. There may have been days or weeks of changes and in a complex model needing to manually track name changes seems odd.
I’m hoping TDM can provide this functionality (if it doesn’t already).
Thanks for your feedback on this issue.
Let us comment on it.
Renaming objects in actual TDM version is an issue that can be solved only via the way it works now - drop one object and create another. Pairing of object isn’t performed. (In TDM 90 % of objects is paired (compared) by physical name, however, there are some exceptions, e.g. relationships.)
As you write, there is a possibility to pair (compare) objects by internal ID. First TDM versions worked this way, however, in practise it didn’t work well. It was much worse. There were minimum of cases when the objects were paired correctly by the ID. Objects that shouldn’t have been paired were paired. Objects that should have been paired weren’t paired. (If you want to know technical details about the problem, please let me know. Our developers will further explain.)
What we have in mind now is ‘user pairing of objects’ - users will have a possibility to select objects to pair (compare) in new GUI. It is not a simple task to implement this feature, it’s very time demanding. For now, it hasn’t been included in our feature coverage plan. However, if more users vote for the change, we will work on this new feature and will introduce it in some of future TDM versions. So, vote for the change and post your feedback, suggestions so we can include them while trying to find the best solution. Thanks.
Thanks for the reply Vladka and for giving us some insight into the complexity of the problem.
What about something quite ‘low tech’? We really only care about objects that can be renamed in the DB without losing data. As far as I know this means Tables or Columns. We don’t care about Views or relationships as they can simply be synchronised (dropped and/or recreated) and no data is lost.
So, how about adding an attribute to tables and columns - “Previous Name”. If you change the name of a Table/Column, and the “Previous Name” attribute is empty, it copies the old name into the “Previous Name” attribute. This attribute would then be used in the comparison mechanism. As part of performing the comparison, there would be a check box to “Reset All Previous Names” (or something like that).
Thanks for your idea on the “Previous Name”.
Problematic things relating to this solution:
It will not work in case of multiple rename.
What if I rename the entities/attributes in the database and not in the model.
What if I make a copy of entities/attributes. If I copy it, should the “Previous Name” be preserved in the copy (if it is a real copy, it should be) OR will it be a kind of a pattern and “Previous Name” should be deleted? Both is possible. However, it can happen that I will have more objects with the same “Previous Name”. Generally, there is a problem with this during copy and drag&drop.
Hi,
Thanks for answer. I know, that the comparision by internal Id is a difficult and “dangerous” way in most situation. For exaple on “Model Update” where we can store the internal id on the database schema? But:
When we compare different version of the SAME model, the Ids are unchangable and unique. Do you know any problem of the internal id in this situation?
Can be implemented a combobox, where the user can set the base of the comparision? (id, physical name, maybe a userdefined property)
Can the camparision script is available and changeable(inheritable) by us, similar to the lot of scripts in the script editor?
Thanks: Laszlo.
How about we change “Previous Name” to “Last Sync Name” - which gets set (if the current model is the ‘model of reference’) when the model is sync’d. So, until a model is sync’d, the “Last Sync Name” has no value. If the name gets changed as part of normal model changes, only the name is changed - the “Last Sync Name” is not touched. The name can be changed as many times as you like without affecting the “Last Sync Name”.
If you rename a table or column in the database, you are effectively saying the DB is your “model of record”, so synchronising should be from the DB to the model. In this case it is fine to “drop” a column in the model and add a new one. No data is lost.
After sync’ing the model would have the same value in the Table/Column Name and the “Last Sync Name”.
If you make a copy of an entity or attribute, I would say the “Last Sync Name” would be empty in the copy as it represents the value from the last sync operation.
It’s quite possible there is no perfect solution, but having a solution would be really helpful as long as it is configurable (e.g. would be turned off by default to preserve current behaviour) and/or documented so the behaviour is understood.
In any case, we need to keep in mind that any sync script must be reviewed before being applied (if you didn’t do that you would be in for a nasty surprise if you rename a table or column with current behaviour).
When two versions of the same model are compared, comparison by ID is possible. However, users wouldn’t have to perform some operations… For example the following: User has a column in his model. He should delete it and create a new one. However, some properties are the same. So, he uses the old column to “create” a new column. Of course, why he should delete a column and create a new one with similar properties…
There can be other similar “tricky” operations.
We know our planned solution - the user-pairing of objects - would solve such problems too as it would be a more complex solution.
At the moment there is a method for conversion rules that users might overwrite via the scripting functionality. However, possibility to make it available for scripting changes would result in slower TDM performance, slower alter script generation because the function is called very often.
Another issue is that operations in Convertor are quite complicated - we mean to understand and take into consideration all relations and connections. It is a question how many users would manage to make the user changes in Convertor being aware of their impact.
Again, we think that the user-pairing of objects would solve it.
We’re not sure what you mean by this. Do you want to open final alter script in a window and modify it? Or do you want some kind of “event” in Convertor that would result in having both models, Convertor with its structure and final alter script that you will be able to modify?
Well, your suggestion on "Last Sync Name" sounds interesting. We will further discuss it in our team and will see what we can do about it. CR 73 535.
Just a note:
If you rename a table or column in the database, you are effectively saying the DB is your "model of record", so synchronising should be from the DB to the model. In this case it is fine to "drop" a column in the model and add a new one. No data is lost.
What about logical information that is not generated to database but is in your model? - Categories, notes, caption... During drop of entity in model user would lose the information.
Another issue is the following: what if user makes changes in database and model at the same time? Then he wants to synchronize in direction model -> database, not all changes, just selected ones. Automatic pairing is not possible here, user has to make manual changes on his own. This case is not often, but some users do this.
In any case, thanks a lot for your feedback. We can ensure you that we will deal with this issue.
You wrote: If you rename a table or column in the database, you are effectivelysaying the DB is your “model of record”, so synchronising should befrom the DB to the model. In this case it is fine to “drop” a columnin the model and add a new one. No data is lost.
I think:
Model contains additional informations what is not in the database. For example a long description of the column, a todo list of an entity, attached documentations … . If you rename the column in the database and update the model with drop the column (in the model) and create a new one can cause other type of data lost (documentation lost)
You wrote: How about we change “Previous Name” to “Last Sync Name” - which gets set (…) when the model is sync’d …
I think:
It is dangerous to use a volatile value for identification. For example suppose that you have a field with name ‘a’ (version 1) Then you rename it to ‘b’ (version 2), then from ‘b’ to ‘c’ (version 3). During the renaming “chain” you compare ver1 to ver2 (DDL1-2) and ver2 to ver3 (DDL2-3). Later you compare the ver1 to ver3 (DDL1-3). Will this appropriate in all situation? Do the same effect when you run DDL1-2 and DDL2-3 on a database, and when you run only DDL1-3 on a database?
But the columns in the oracle database doesn’t not contain any unchangable id, as I know. For example the user_tab_cols.column_id of the last column changes when you drop an other column in the table. This is a problem when you try to handle column renaming between database and model. Can anybady an idea to handle this?
I wrote: 3. Can the camparision script is available and changeable(inheritable)by us, similar to the lot of scripts in the script editor?
You answered: 3. We’re not sure what you mean by this. Do you want to open finalalter script in a window and modify it? Or do you want some kind of"event" in Convertor that would result in having both models, Convertorwith its structure and final alter script that you will be able tomodify?
What I mean:
TDM is highly customizable. There are lot of scripts in “Script Explorer” what can be changeable(inheritable) by “end user” (by us). Can I find the code of the attribute pairing algorithm in the “Script Explorer”? Is it open for us? I think some similar code, but I didn’t find (sorry for mistakes in my sample code):
if(PERAttributeOR_Object1.Name == PERAttributeOR_Object2.Name) {…}
or something like this
PairAttribute = PEREntityOR_Object2.Attributes.GetObjectByName(PEREntityOR_Object2.Attributes.GetObject(i).Name);
//where GetObject(i) is the currently pairable attribute
what I can try to rewrite to
//PairingById would be a Boolean userdefined Attribute of the model
if(Model.PairingById) {
if(PERAttributeOR_Object1.Id == PERAttributeOR_Object2.Id) {…}
…
}
or rewrite to
if(Model.PairingById) {
PairAttribute = PEREntityOR_Object2.Attributes.GetObjectById(PEREntityOR_Object2.Attributes.GetObject(i).Id);
The other important code piece is the diff DDL generation of the PERAttribute. Is it in the PERCodeGenerator[OR]? Where? I think some similar code, but I didn’t find the appropriate script where I can insert this.
//i and j are the indexes of the two paired attributes
DDLstring = “ALTER TABLE” + Entity2.Name + " RENAME COLUMN " + Entity1.Attribute(i) + " TO " + Entity2.Attribute(j) + “;”
Thanks for your explanation.
As I wrote in my reply 2), the customization in this case is not easy at all. Let me repeat:
At the moment there is a method for conversion rules that users might overwrite via the scripting functionality. However, possibility to make it available for scripting changes would result in slower TDM performance and therefore slower alter script generation because the function is called very often.
Another issue is that operations in Convertor are quite complicated - we mean to understand and take into consideration all relations and connections. It is a question how many users would manage to make the user changes in Convertor being aware of their impact.
So, these are the main reasons why it’s not open for users.
Anyway, if you do want to try it on your own, please let us know. We will have to prepare few things for you in this case. It might be done for the next Beta release. However, please confirm that you are interested.
Sorry for my late answer. We are seeking a case tool now. The value-price rate of the TDM is good. If we choose the TDM, I will confirm this help. But I cannot ask this help until this, as I think.