Naming template for Key (PK, UK)

[former_member1194361]

Where can I find the rules on how PD 16.5 generates the text it puts in the Name AND Code columns for a Key on a Table. Key could be Primary or Unique.

Right now it is generating a value such as key_1, key_2. I would like it to use the same value as used by ConstraintName, for which I have a rule defined in the DBMS Resource file to generate the value such as PK_tablename, UK_tablename_seqnbr.

Having the Name and Code columns as Key_1 is not impacting the DDL being generated, but when merging the Dev model to Production model, it is getting difficult to identify which changes to merge.

I want to do a similar thing for Name and Code column for References (i.e Relationships). Would like to put the same value as in Constraint name in this case too.

I am using Oracle 10 or 11 databases.

[GeorgeMcGeachie]

Navin, I don't understand your issue when you say "when merging the Dev model to Production model" - if you compare two versions of the same model, won't the object GUIDs be the same, so the comparison matches them up?

Are you reverse-engineering production in order to compare it? Are the key names amended after generating the DDL? If so, PD will have a problem matching them up, and there's little you can dio about that unless you make the keys in the model match those in the database.

When you generate DDL or create/update a database, do you save a PDM archive file? If you don't, you should; if you do, then you can compare the development PDM against the archive file, the GUIDs will match and you'll get fewer (maybe zero) false differences.

[former_member1194361]

Our model lifecycle is as below.

We "Generate a New Physical Data model" from the Production data model in repository at the start of a project. We call this Dev data model. This model is changed during the course of a project, changes usually restricted to one or more diagrams in the data model. Once the DDL is deployed to production, I merge this Dev model back into the Production data model. It is at this point that we I see several Key_1, Key_2 names for the Primary and Unique keys in Dev model that I don't know if they are from the new changes in that project. Hence, I wanted to have them named same as the Constraint name, making it easier to pick what changes to merge.

Typically in the Merge objects screen, I de-select everything and then select ONLY the things I want to merge.

[GeorgeMcGeachie]

Ok, two things to say here.

1) how to handle model versions

The internal identifiers (GUIDs) for the objects in your DEV model are different from those in your PROD model, because you generated a new DEV model. If the GUIDs are the same, the Merge operation will link keys that have different names; without the GUIDs it will assume that Key_1 in DEV matches Key_1 in PROD.

Instead of generating a new DEV model every time, why not either:

• take a copy of the PROD model to be your DEV model, kept in a separate folder - do this via 'File | Save As' or just by copying the file in Windows
  • this will have the same GUIDs, so objects match up OK
• a better option is to create DEV and PROD branches in the repository, consolidating DEV changes into PROD when you need to
  • you can have multiple DEV branches if you need to, each with different permissions

2) how to match up two similar models with different GUIDs

When you generated the DEV model, did you keep the generation links from PROD to DEV? I think the merge operation can use these to match things up. If you didn't keep the links you can re-create them.

• In the PROD model, use Tools menu option to generate to DEV in update modde, setting option to keep generation links. In the merge dialogue, match any objects you need to match, deselect all actions (so it doesn't update any objects), then click <OK>. This should re-create the generation links.

By the way, when you're merging, don't forget that you can save the current selection for use again later.

[former_member1194361]

Hi George,

The issue is not matching existing objects, but figuring out from the list of new objects been shown in the Dev model properly. Having the References and Keys with their Key names, instead of the physical name, makes it difficult to identify. Only choice I have is to copy the physical name into the default PD Generated Reference Name, Key name field in the DEV model before starting the Merge. I am trying to avoid that by having a macro(like in Erwin) to put the same physical name as Key or Reference Name.

Reason I have to generate a new Physical data model for Dev purpose, from the Prod model is that I specifically want to break the link with the Prod model by generating a new model GUID. We do frequently (maybe once a week), save the DEV model to repository in a separate project folder. If I did not break the link, it disables the folder where you want to save the DEV model and points straight to the PROD Model name and folder. I do not want Developers updating the Prod model while project is in Development, plus they are not familiar with the merge functionality. Besides it would fail anyway, as they don't have access to the Prod model folder. But then they risk not being able to backup their data model to the repository and having it only on their machine.

[GeorgeMcGeachie]

To enable developers to backup their DEV models to the repository, I wouldn't use "save as new model", I would use branching, as I mentioned before - each project or person could have their own branch. They could check in as often as they want to.

[GeorgeMcGeachie]

Did the validate event handler work for you?

[former_member1194361]

I will have read in help about this Branching, don't have much knowledge about it.

As for the event handler, honestly, don't know where to put that in PD.

Do I put that script under Tools -> Execute Command -> Edit/Run Script ?

If so, then this macro or script would apply only to the open data model and not any model that I open in future. That will not work as I want this at the Tool level and not data model specific.

[GeorgeMcGeachie]

You define Event Handlers in model extensions or your database definition. Here's an example that automatically manages the names of CDM and LDM relationships:

The script that does the real work (setRelationshipName) is defined on the Global Script tab, so it can be used in menus and custom checks as well or instead of the event handler.

[former_member1194361]

I don't see a Event Handlers folder at all under Profile folder in my Database Resource file. If I right click, I can add a new Metaclass, but Event Handler is not one of them. So I am confused where to paste it. There is a Global Script tab when I select the Profile folder and that already has some code in it from PD.

And under Tools -> Resources - Extensions -> Physical Data model. I see the below dialog. Not clear where Event Handler fits in.

[GeorgeMcGeachie]

The metaclass you need is already in the profile - it's 'Key'. Right-click it and select the option to add an event handler. This screen shot shows that SQL Server 2008 already has an event handler, which could be amended to manage the object names.

Make sure you're working on a copy of the definition, or embed it in your model first (by changing the definition to the same one, and clicking on 'embed resource in model' before clicking OK:

If the database definition is embedded in the model, it'll be included in any models generated from it, though it won't be available for any new models unless you also save it in a file in the path that PD looks in for such files.

[former_member185199]

http://scn.sap.com/thread/3443769

HTH

DJ

[former_member200945]

It' very easy to rename object name in script.

In your case, you can modify the following code:

set tables=model.tables

for each t in tables

  set keys= t.keys

  for each k in keys

     output k.name

     output k.code

     output k.constraintName

     output k.name & " is primary key " & k.primary

  next

next

set references=model.references

for each ref in references

     output ref.name

     output ref.code

next

You can reset key name or reference name using statement like

   k.name=" abc"

   ref.code=" ref_code"

To execute the code, open your model, go to Tools->Execute Commands->Edit /Run Script.

For more information, go to Help->Meta objects Help. Search for an object name.

[former_member1194361]

I am not sure I quite undertand what this is doing. This is still a manual activity each time I modify the model or generate a DDL.

I am looking for a solution similar to how my PK or FK Constraint name is generated, but for the Name and Code columns. The constraint name being a physical property, I put the below in my Oracle 11g DBMS Resource file, under the path : ORA11GR1::Script\Objects\Key\ConstName

UK_%TABLE%_%.U-18.1:AKEY%

I am assuming the Name and Code cannot be modified this way from DBMS resource file and there must be some other place for it, as I cannot see which property under Key would do this.

I see ORA11GR1::Script\Objects\Key\Add which comment says is "Command for defining an alternate key" and has this text

[constraint %CONSTNAME%] unique (%COLUMNS%)

      [%OPTIONS%]

[GeorgeMcGeachie]

Hello Navin

You need to have event handlers to take control of the object names, leave the codes to work themselves out. I've worked some code you can use to start with, it'll still need some work - perhaps put code into a global script, so it can be referenced in a custom model check instead.

The initialise event could look like this:

Function %Initialize%(obj)

   ' Implement your initialization on <obj> here

   ' and return True in case of success

   Dim count

   Count = 0

   Dim newName

   if obj.Primary then ' this is the PK

      newName = "PK_" & obj.Table.Name

      ' need code here to check if this name already exists

      if not obj.Name = newName then obj.Name = newName

      %Initialize% = True

   else

      count = count + 1

      newName = "UK_" & obj.Table.Name & "_" & count

      ' need code here to check if this name already exists

      if not obj.Name = newName then obj.Name = newName

      %Initialize% = True

    end if

End Function

The Validate event could look like this:

Function %Validate%(obj, ByRef message)

   ' Implement your object validation rule on <parent> here

   ' and return True in case of success, False otherwise with a message

   Dim count

   Count = 0

   DIM newName

   if obj.Primary then ' this is the PK

      newName = "PK_" & obj.Table.Name

      ' need code here to check if this name already exists

      if not obj.Name = newName then obj.Name = newName

      %Validate% = True

   else

      count = count + 1

      newName = "UK_" & obj.Table.Name & "_" & count

      ' need code here to check if this name already exists

      if not obj.Name = newName then obj.Name = newName

      %Validate% = True

   end if

End Function