Adding Indexes in SAP PowerDesigner with VBScript

PowerDesigner

The following code will help in adding columns to indexes or creating indexes in SAP PowerDesigner:

.

and it can be downloaded with test files, from here:

https://drive.google.com/folderview?id=0B2xC75mde6w0cWp1ajMxeV9aRUU&usp=sharing

.

PowerDesigner code

  • ‘******************************************************************************
    ‘* File:     __PDM_Before_Generation_AddIndexes.vbs
    ‘* Purpose:  Add indexes for specific fields
    ‘*             also:
    ‘*            Learn how to Add an index
    ‘*            Learn how to remove an index
    ‘*            Learn how to search for Inclusive indexes
    ‘*            Learn how to search for an index
    ‘*            Learn how to search for columns in an index
    ‘*
    ‘* Title:
    ‘* Category: PDM
    ‘* Version:  1.0
    ‘* Company:  Dev
    ‘******************************************************************************
    Option Explicit
    Dim mdl
    Dim Fldr
    Dim RQ
    Dim isFound
    Dim strLongReferenceNames
    dim iCountChanged, iCountNotChanged, iCountEntities
    dim iAllTables
    dim iAllIndex
    ‘ Get the current active model
    Set mdl = ActiveModel
    call mainProcedure
    ‘—————————————————————————–
    ‘ Main function
    ‘—————————————————————————–
    sub mainProcedure()
    If (mdl Is Nothing) Then
    MsgBox “There is no Active Model”
    exit sub
    End If
    If Not mdl.IsKindOf(PdPDM.cls_Model) Then
    MsgBox “This is not PDM”
    exit sub
    end if
       Set Fldr = ActiveDiagram.Parent
       RQ = MsgBox (“Starting at Folder: ” & Fldr.Name & ” Is Run ?”, vbYesNo + vbInformation,”Confirmation”)
    if RQ= VbNo then
    exit sub
    end if

    ‘————————————
    iCountChanged = 0
    iCountEntities = 0
    iCountNotChanged = 0

       iAllTables = 0
    iAllIndex = 0

    creatRequiredIndexes mdl

       output “_____Index Creation, Created = ” & iCountChanged & ” in ” & iCountEntities & ” Entities. and not changed = ” &  iCountNotChanged & “, all Tables = ” & iAllTables & “, All Indexes= ” & iAllIndex
    End Sub
    ‘—————————————————————————–
    ‘ This will recursivly search for all tables, and then add to them the required indexes
    ‘—————————————————————————–
    Sub creatRequiredIndexes(package)
    Dim tbl
         For Each tbl In package.Tables
    If tbl.IsShortcut = false Then
    iAllTables = iAllTables + 1
    makeTableIndexes(tbl)
    End If
    Next
         Dim subpackage
    For Each subpackage In package.Packages
    If Not subpackage.IsShortcut Then
    creatRequiredIndexes(subpackage)
    End If
    Next
    End Sub
    ‘———————————————————–
    ‘ Creates indexes for specific general fields, or for specific table fields.
    ‘———————————————————–
    Sub makeTableIndexes(ByVal tbl)
       ‘add index for specific tables:
    ‘———————————————————————–
    If tbl.Code = “Table_2” Then
    AddFieldToIndex tbl, “IX_Table_2_Column_4”, True , “Column_5”
    End If
     End Sub
    ‘———————————————————–
    ‘Make the indexName_ field in a unique Index
    ‘———————————————————–
    Sub AddFieldToIndex( tbl_,  indexName_, isUnique, sFieldName_)
    Dim oCol’ As PdPDM.BaseColumn
    Dim oIndex’ As PdPDM.BaseIndex
    Set oIndex = getTableIndex(tbl_, indexName_)
    If oIndex Is Nothing Then
    output “aa ” & tbl_.code & “.” & sFieldName_
    iCountChanged = iCountChanged + 1
    Set oIndex = tbl_.Indexes.CreateNew()
    oIndex.Code = indexName_
    oIndex.Name = indexName_
    else
    ‘output “+found Index: ” & tbl_.code & “.” & oIndex.Code
    iCountNotChanged = iCountNotChanged + 1
    End If
         If isUnique = True And oIndex.Unique <> True Then oIndex.Unique = isUnique
         dim oFoundColumn
    dim isColumnFound
    isColumnFound=false
    For Each oFoundColumn In oIndex.IndexColumns
    if oFoundColumn.Column.Code =sFieldName_ then
    isColumnFound=true
    end if
    Next
          if isColumnFound = false then

    getTableColumn tbl_, sFieldName_, oCol
    If oCol Is Nothing Then
    output “-Not found column: ” & tbl_.code & “.” & sFieldName_
    Exit Sub
    End If

             Dim oIndexCol’ As PdPDM.IndexColumn
    Set oIndexCol = oIndex.IndexColumns.CreateNew()
    With oIndexCol
    .Column = oCol
    End With
    end if

    End Sub

    ‘———————————————————–
    ‘Make the indexName_ field in a unique Index
    ‘———————————————————–
    Sub AddIndex( tbl_,  indexName_, isUnique)
    Dim oCol’ As PdPDM.BaseColumn
    Dim oIndex’ As PdPDM.BaseIndex
    Dim sPrefix’ As String

    sPrefix= “”

         getTableColumn tbl_, indexName_, oCol
    If oCol Is Nothing Then
    ‘output “-Not found column: ” & tbl_.code & “.” & indexName_
    Exit Sub
    End If
    ‘output “+found column: ” & tbl_.code & “.” & indexName_

    If isUnique = True Then
    sPrefix = “AK” ‘Alternate Key

             If CBool(oCol.GetAttribute(“Mandatory”)) = False Then
    oCol.SetAttribute “Mandatory”, True
    End If
    Else
    sPrefix = “IX” ‘Indexing
    End If

    Dim sName
    sName = sPrefix & “_” & tbl_.Code & “_” &  indexName_

    Set oIndex = getTableIndex(tbl_, sName)
    If oIndex Is Nothing Then
    iCountChanged = iCountChanged + 1
    Set oIndex = tbl_.Indexes.CreateNew()
    oIndex.Code = sName
    oIndex.Name = sName
    else
    ‘output “+found Index: ” & tbl_.code & “.” & oIndex.Code
    iCountNotChanged = iCountNotChanged + 1
    End If

         If isUnique = True And oIndex.Unique <> True Then oIndex.Unique = isUnique
         If oIndex.IndexColumns.Count <= 0 Then
    iCountChanged = iCountChanged + 1
    Dim oIndexCol’ As PdPDM.IndexColumn
    Set oIndexCol = oIndex.IndexColumns.CreateNew()
    With oIndexCol
    .Column = oCol
    End With
    ‘output “Index created:” & oIndex.Code & “, col:” & oCol.Code
    End If
    End Sub
    ‘———————————————————–
    ‘ Recursivly removes all empty indexes from all tables
    ‘———————————————————–
    Sub removeEmptyIndexes(package)
    Dim tbl
         For Each tbl In package.Tables
    If tbl.IsShortcut = false Then
    iAllTables = iAllTables + 1
    removeTableEmptyIndexes(tbl)
    End If
    Next
         Dim subpackage
    For Each subpackage In package.Packages
    If Not subpackage.IsShortcut Then
    removeEmptyIndexes(subpackage)
    End If
    Next
     End Sub
    ‘———————————————————–
    ‘Make the Primary Index as a Clustered index
    ‘———————————————————–
    Sub MakePrimaryIndex( tbl_)
    Dim indPrimary ‘ As PdPDM.BaseIndex

    Set indPrimary = getTableIndex(tbl_, “PK_” & tbl_.Code)

    If indPrimary Is Nothing = false then
    If indPrimary.Clustered = False then
    indPrimary.Clustered = True
    output indPrimary.code
    end if
    End if

    End Sub

    ‘———————————————————–
    ‘ Gets the first index object of the table
    ‘———————————————————–
    Function getTableIndex( tbl_,  name_)’ As PdPDM.BaseIndex
    dim oIndex’ As PdPDM.BaseIndex
    For Each oIndex In tbl_.Indexes
    If oIndex.Code = name_ Then
    Set getTableIndex = oIndex
    exit Function
    End If
    Next
    Set getTableIndex = Nothing
    End Function
    ‘———————————————————–
    ‘ Gets the index that contains the columnCodeName_
    ‘———————————————————–
    Function getTableIndexColumn( oIndex_,  columnCodeName_)’ As PdPDM.IndexColumn
    dim oColumn’ As PdPDM.IndexColumn

    For Each oColumn In oIndex_.IndexColumns
    If oColumn.Code = columnCodeName_ Then
    set getTableIndexColumn = oColumn
    exit Function
    End If
    Next
    set getTableIndexColumn =  Nothing
    End Function

    ‘———————————————————–
    ‘ Gets the table columnCodeName_ object
    ‘———————————————————–
    Sub getTableColumn( tbl_,  columnCodeName_, ByRef retVal_ )’ As PdPDM.BaseColumn
    dim col

    For Each col In tbl_.Columns
    If col.Code = columnCodeName_ Then
    set retVal_ = col
    ‘output “++ found col: ” & col.Code
    exit sub
    End If
    Next

    set retVal_ =  Nothing
    End sub

About Reader Man حسين ناجي الصفافير

حسين ناجي الصفافير Hussain Naji Al-Safafeer Info: https://readerman1.wordpress.com/aboutme
This entry was posted in Development and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s