Search for all tables that do not have the column "timeStamp", and add it

[Former Member]

I always add the field "timeStamp", which i call it in my DBs "row_timestamp", so to lessen my forgetfulness and to ease on my typing of it in each table, the following script will add it to all tables that do not have it.

also benefits for:

  Adding specific fields to all tables

Option Explicit

Dim mdl ' the current model

Dim fldr

Dim sSearchCode

Dim sSearchCodeExchange

Dim RQ

dim iCountAddedNewColumn, iCountNotAddedNewColumn, iCountEntities

dim FSO

dim txtStream

dim sCSVFile

' 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"

   End If

   If Not mdl.IsKindOf(PdCDM.cls_Model) Then

      MsgBox "This is not CDM"

      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

 

   sSearchCode="row_timestamp"

   sSearchCodeExchange="row_timestamp"

   iCountAddedNewColumn=0

   iCountEntities=0

   iCountNotAddedNewColumn=0

   searchAllTables(fldr)

   output  "Added Fields = " & iCountAddedNewColumn & " in " & iCountEntities & " Entities. and not changed = " &  iCountNotAddedNewColumn

end sub

'-----------------------------------------------------------------------------

' Receptively search for all tables, to add to them the required field

'-----------------------------------------------------------------------------

Sub searchAllTables(parentFolder_)

   Dim obj ' running object

   For Each obj In parentFolder_.children

      if obj.ClassName="Entity" then

         iCountEntities = iCountEntities + 1

         addColumnToEntity obj

      end if

   Next

   ' go into the sub-packages

   Dim innerFolder ' running folder

   For Each innerFolder In parentFolder_.Packages

      searchAllTables innerFolder

   Next

End Sub

'-----------------------------------------------------------------------------

' Check if the column is not found then add it

'-----------------------------------------------------------------------------

Sub addColumnToEntity(entity_)

   if entity_.ClassName <>"Entity" then exit sub

   'Export informations to the output list

   'output "Found "+entity_.ClassName+" "+entity_.Name+", Created by "+entity_.Creator+" On "+mid(Cstr(entity_.CreationDate),1,10) 

    

   Dim attr

   Dim isFound

   isFound=false

 

   For Each attr In entity_.Attributes

      if attr.code = sSearchCode  then 'instr(1, attr.code, sSearchCode, 1)>0

         isFound=true

         attr.Mandatory = false

         attr.code = sSearchCodeExchange

  exit for

      end if

   Next

 

   If isFound=false Then

      iCountAddedNewColumn= iCountAddedNewColumn + 1

      Set attr = entity_.Attributes.CreateNew  ' Creating Attribute

      attr.Name = "متغير عند تغيير السطر"

      attr.Code = sSearchCodeExchange

      attr.Mandatory = false

      'attr.Domain = "TypeTimeStamp_Dom"

   else

      iCountNotAddedNewColumn= iCountNotAddedNewColumn + 1

   End If

End Sub