Generate Plumbing Columns

Plumbing Columns are those that are in each table for data and schema change management purposes. (i.e. CreatedByID, CreatedByName, CreatedByDate, etc)

First, create the Domains that will be used for the Plumbing columns:

***************************************
Sub
Main() Dim MyDiagram As Diagram Dim MyModel As Model Dim MyDomain As Domain Dim MyDictionary As Dictionary Dim ID As Integer 'Create a new diagram. Set MyDiagram = DiagramManager.ActiveDiagram 'Get the data dictionary. Set MyDictionary = MyDiagram.Dictionary 'Get the current model (in this case, the logical model). Set MyModel = MyDiagram.ActiveModel 'ID domain. Set MyDomain = MyDictionary.Domains.Add("ID", "ID") MyDomain.Datatype = "INTEGER" MyDomain.Identity = True MyDomain.IdentitySeed = 1 MyDomain.IdentityIncrement = 1 'Name. Set MyDomain = MyDictionary.Domains.Add("Name", "Name") MyDomain.Datatype = "VARCHAR" MyDomain.DataLength = 30 MyDomain.Nullable = False MyDomain.DeclaredDefault = "user_name()" 'Datetime. Set MyDomain = MyDictionary.Domains.Add("Datetime", "Datetime") MyDomain.Datatype = "DATETIME" MyDomain.Nullable = True End Sub

***************************************
Second, add those fields to the tables
highlight the tables you wish to add the fields to and run the following:
***************************************
Sub Main
    Dim MyDiagram As Diagram
    Dim MyModel As Model
    Dim MySubModel As SubModel
    Dim MyDictionary As Dictionary
    Dim MyDomain As Domain
    Dim MyEntity As Entity
    Dim MyAttribute As AttributeObj
    Dim MyPrimaryKey As Index
    Dim AttributeName As String
    Dim PrimaryKeyName As String
    Dim EntityName As String
    Dim Logical As Boolean
    Dim MySelObject As SelectedObject
    Dim NameID As Integer
    Dim DatetimeID As Integer
    Dim DomainID As Integer
    Dim ID As Integer
    Dim ObjType As Integer
    Dim MyIndex As Index

    ' Get the current diagram.

    Set MyDiagram = DiagramManager.ActiveDiagram

    ' Get the current model.

    Set MyModel = MyDiagram.ActiveModel

    'Get the current submodel.

    Set MySubModel = MyModel.ActiveSubModel

    ' Get the current Data Dictionary.

    Set MyDictionary = MyDiagram.Dictionary

    ' Determine Domains.

    Set MyDomain = MyDictionary.Domains.Item("Name")
    NameID = MyDomain.ID

    Set MyDomain = MyDictionary.Domains.Item("Datetime")
    DatetimeID = MyDomain.ID

    'Set MyDomain = MyDictionary.Domains.Item("ID")
    'DomainID = MyDomain.ID

    ' Determine if the current model is logical or physical.

    Logical = MyModel.Logical

    ' Iterate through all the selected objects in the current
    ' model.

    For Each MySelObject In MySubModel.SelectedObjects

        'Get the object type - we are only concerned
        'with entities.

        ObjType = MySelObject.Type

        If ObjType = 1 Then

            ' Get the ID for the selected object.

            ID = MySelObject.ID

            ' Get the actual entity object with this ID.
            ' The model contains the collection of all the
            ' entities.

            Set MyEntity = MyModel.Entities.Item(ID)

''          If Logical = True Then

                ' If the model is logical:

                ' Get the entity name.

                EntityName = MyEntity.EntityName

                ' Set name of PK and PK attribute.

''              AttributeName = EntityName + "_ID"
''              PrimaryKeyName = EntityName + "_PK"

                ' Add the PK attribute

''                Set MyAttribute = MyEntity.Attributes.Add(AttributeName, True)

                ' Bind the ID domain to the PK attribute.

''              MyAttribute.DomainId = DomainID

                ' Get ID for PK, using the kluge that it is the only index.
                 'Set MyPrimaryKey = MyEntity.Indexes.ID

                ' Go through the collection of indexes in the entity and
                ' find the PK index
''
''              For Each MyIndex In MyEntity.Indexes
''                  If (MyIndex.IsPK = True) Then
''                      Set MyPrimaryKey = MyIndex
''                      Exit For
''                  End If
''              Next 'MyIndex

                ' Set the PK name.
''              MyPrimaryKey.Name = PrimaryKeyName

                ' Owner
                Set MyAttribute = MyEntity.Attributes.Add("Owner", False)
                MyAttribute.DomainId = NameID

                ' CreateDate
                Set MyAttribute = MyEntity.Attributes.Add("Create Date", False)
                MyAttribute.DomainId = DatetimeID

                ' CreatedBy
                Set MyAttribute = MyEntity.Attributes.Add("Created By", False)
                MyAttribute.DomainId = NameID

                ' ModifiedDate
                Set MyAttribute = MyEntity.Attributes.Add("Modified Date", False)
                MyAttribute.DomainId = DatetimeID

                ' CreatedBy
                Set MyAttribute = MyEntity.Attributes.Add("Modified By", False)
                MyAttribute.DomainId = NameID
''          End If

        End If

    Next MySelObject

End Sub