Powerdesigner VBScript (get column name)
Khou
Physical model
Alternative Name:
>>AK_{table_name}_{table_column_name}
-AK_Account_AccountNumber
Relationship Name:
>>FK_{table_name}_{reference_table_name}_{reference_column_name}
-FK_Account_AccountPhone_HomePhoneID
How do I get the "table_column_name" and
"reference_column_name"?
billbunke
Well, the following sample scripts should answer the
questions you've asked. For more detail, you can look into
the Scripting Meta-Model help file for the collections on
each Table collection (e.g. Columns).
However, the code needed shows a flaw in your approach, in
that there can be multiple columns in these objects, that
you may not have control over. If it happens only once, then
the reason for doing it this way may break down, and your
names will not follow a single pattern.
It also seems that the object names may either get longer
than is comfortable (and beyond server name limits at some
point).
Based on these considerations, you may want to consider
alternative approaches, which will lead to something that
meets your needs but is likely to never exhibit these or
other types of problems.
BTW, one should have clear goals of what they are trying to
accommplish that doesn't rely on system limitations or
specific artifacts. One rule might be that all sub-table
ojects have a name that relates to the table itself in a
consistent manner.
HTH,
Bill
> VBScript attached.
>
>
> [powerdesigner-vbscript.txt]
>
>
> 'Physical Model
> Set mdl=ActiveModel
> For Each Tab in mdl.tables
> If (not tab.isShortcut) Then
>
> Output "Table: " + Tab.Name
>
> Output "Keys:"
> 'Keys
> For Each Key in Tab.Keys
> If Key.Primary Then
> 'Primary
> Key.Name = "PK_" + Tab.Name
> Key.Code = "PK_" + Tab.Code
>
> For Each Ind in Key.Indexes
> Ind.Name = "PK_" + Tab.Name
> Ind.Code = "PK_" + Tab.Code
> Key.ConstraintName = Ind.Code
> Output Ind.Code
>
> Next
> Else
> 'Alternative
> Key.Name = "AK_" + Tab.Name +
> __________________table_column_name?_______________
> Key.Code = "AK_" + Tab.Code +
> __________________table_column_name?_______________
>
> For Each Ind in Key.Indexes
> Ind.Name = "AK_" + Tab.Name + "
> ;_" +
> ___________________table_column_name?_______________
> Ind.Code = "AK_" + Tab.Code + "
> ;_" +
> __________________table_column_name?_______________
> Key.ConstraintName = Ind.Code
> Output Ind.Code
> Next
> End If
> Next
>
> Output "References:"
> For Each Ref in Tab.OutReferences
> 'Join Constraints
> If Ref.ParentRole <> "" Then
> Ref.ForeignKeyConstraintName = "FK_" +
> Ref.ParentTable.Code + "_" + Ref.ChildTable.Code +
> "_" + __________________reference_olumn
> name?_______________
> Else
> Ref.ForeignKeyConstraintName = "FK_" +
> Ref.ParentTable.Code + "_" + Ref.ChildTable.Code +
> "_" + + __________________reference_olumn
> name?_______________
> End If
>
> Output Ref.ForeignKeyConstraintName
>
> Next
>
> End If
> Next
> [Attachment: powerdesigner-vbscript.txt]
David Dichmann
a collection "IndexColumns" - you have to loop "for each ixcol in
Index.IndecColumns" to get the index's columns. This is because it is
possible (and in some RDBMS designs common enough) that the index has more
than one column, Primary or otherwise, so we must store them all as a
collection.
- David.
<Khou> wrote in message news:4a07d5bb.20...@sybase.com...
> VBScript attached.
>
billbunke
Dim lTab , lCol
For Each lTab In pFldr.Tables
For Each lCol In lTab.Columns
Next
Next
Dim c ' column iterator
For Each c In key.Columns
Next
Khou
BTW: what does "pFldr" stand for?
khou
I can see the reason for making your suggestion, so it might
actually better to change the naming pattern to
-AK_{table_name}_{icounter}
-FK_{table_name}_{reference_table_name}_{icounter}
BTW: I was unable to find the Meta-Model/table collection in
help file as suggested.
billbunke
(they did on my test data).
I altered the reference names to place the count between the
two table names (it just looked better). Also, either my
code or test data didn't trigger any problems with dup
names.
Attached is the code and my smaple output results.
HTH,
Bill
> [powerdesigner-vbscript3.txt]
> 'Physical Model
> Dim mdl ' the current model
> Set mdl=ActiveModel
> Dim Tab ' table
> Dim RefCnt 'reference constraint counter
> Dim TempValue 'stores temportary key constraint name for
> comparing Dim NewValue 'stores keyconstraint name
>
> For Each Tab in mdl.tables
> If (not tab.isShortcut) Then
>
> Output "Table: " + Tab.Name
>
>
> '------------------------------------------
> ' SETUP KEYS
> 'All keys follow the naming pattern:
> '<key type>_<Table name>_0<incremental
> number> 'Examples:
> '• PK_Order
> '• AK_Product_01
> '------------------------------------------
>
> Output "Keys:"
> 'Keys
> For Each Key in Tab.Keys
> Cnt = 1
>
> If Key.Primary Then
> 'Primary naming pattern: PK_<Table name>
> Key.Name = "PK_" + Tab.Name
> Key.Code = "PK_" + Tab.Code
> Key.ConstraintName = Tab.Code
>
> Else
> 'Alternative key naming pattern: AK_<Table
> name>_0<incremental number>
> NewValue = "AK_" + Tab.Code + "
> ;_" + Right("0" + CStr(Cnt),2)
> if NewValue = TempValue then
> NewValue = "AK_" + Tab.Code + "
> ;_" + Right("0" + CStr(Cnt+1),2)
> else
> TempValue = NewValue
> end if
> Key.Name = NewValue
> Key.Code = NewValue
> Key.ConstraintName = NewValue
> Cnt = Cnt + 1
> End If
> Next
>
>
> '------------------------------------------
> ' SETUP REFERENCES
> 'All relationship constraints follow the naming pattern:
> 'FK_<Table name>_<referenced table name>_0<
> ;incremental number> 'Examples:
> '• FK_AccountPhone_Phone_01
> '------------------------------------------
>
>
>
> 'This code works but the counter is incorrect, its
> counting the child columns not the parent columns 'Still
> need to rename the reference name/code.
>
> 'Join Reference Key Constraints
> ' RefCnt = 1
>
> ' For Each Ref in Tab.OutReferences
> ' Ref.ForeignKeyConstraintName = "FK_"
> + Ref.ParentTable.Code + "_" + Ref.ChildTable.Code
> + "_" + Right("0" + CStr(RefCnt),2)
> ' Ref.ForeignKeyConstraintName = "FK_"
> + Ref.ParentTable.Code + "_" + Ref.ChildTable.Code
> + "_" + Right("0" + CStr(RefCnt),2)
> ' Output Ref.ForeignKeyConstraintName
> 'RefCnt = RefCnt + 1
> ' Next
>
> '------------------------------------------
>
>
>
> '------------------------------------------
> ' SETUP INDEXES
> '------------------------------------------
> 'All indexes follow the naming pattern:
> '<index type>_<Table name>_0<incremental
> number> 'Examples:
> '• PK_Order
> '• AK_Product_01
> '• FK_AccountPhone_Phone_01
> '• IX_ProductDetail_01
> '------------------------------------------
>
> 'Indexes
> Cnt = 1
> For Each Ind in Tab.Indexes
> 'Primary key indexes
> If (Ind.Primary) and (Not Ind.ForeignKey) and
> (Not Ind.AlternateKey) Then
> Ind.Name = "PK_" + Tab.Name
> Ind.Code = "PK_" + Tab.Code
> Output Ind.Code
> 'Alternative key indexes
> Elseif (Not Ind.Primary) and (Not Ind.ForeignKey)
> and (Ind.AlternateKey) Then
> Ind.Name = "AK_" + Tab.Name + "
> ;_" + Right("0" + CStr(Cnt),2)
> Ind.Code = "AK_" + Tab.Code + "
> ;_" + Right("0" + CStr(Cnt),2)
> Output Ind.Code
> Cnt = Cnt + 1
> 'All other indexes excluding primary, foreign,
> and alternative keys
> Elseif (Not Ind.Primary) and (Not Ind.ForeignKey)
> and (Not Ind.AlternateKey) Then
> Ind.Name = "IX_" + Tab.Name + "
> ;_" + Right("0" + CStr(Cnt),2)
> Ind.Code = "IX_" + Tab.Code + "
> ;_" + Right("0" + CStr(Cnt),2)
> Output Ind.Code
> Cnt = Cnt + 1
> End If
>
> Next
>
> 'Foreign Key Indexes
> Cnt = 1
> For Each Ref in Tab.OutReferences
> For Each Ind in Ref.Indexes
> Ind.Name = "FK_" +
> Ref.ParentTable.Code + "_" + Ref.ChildTable.Code +
> "_" + Right("0" + CStr(Cnt),2)
> Ind.Code = "FK_" +
> Ref.ParentTable.Code + "_" + Ref.ChildTable.Code +
> "_" + Right("0" + CStr(Cnt),2)
> Output Ind.Code
> Cnt = Cnt + 1
> Next
> Next
>
> '------------------------------------------
>
>
>
>
>
> End If
> Next
> [Attachment: powerdesigner-vbscript3.txt]