'MACRO TITLE: INSERT ORACLE SYNONYM IN POSTSQL.BAS
'DESCRIPTION: This Macro will add a Synonym to a specified table/view.
'The macro will prompt the user with a dialog to choose the table/view.
'The synonym code will be inserted into the PostSQL of the specified
'table/view.
Sub Main
Dim MyModel As Model
Dim MyDiagram As Diagram
Dim MyEntity As Entity
Dim MyView As View
Dim Synonym As String
'Current Model has to be physical
'set ER variables
Set MyDiagram = DiagramManager.ActiveDiagram
Set MyModel = MyDiagram.ActiveModel
If MyModel.Logical = True Then
MsgBox("Current Model has to be Physical.")
ElseIf MyModel.MajorPlatform <> "Oracle 7/8" Then
MsgBox ("Current model has to be Oracle 7/8 platform.")
Else
'initialize ObjectArray that holds tables and views
'for the Dialog list
Dim ObjectCount As Integer
ObjectCount = MyModel.Entities.Count + MyModel.Views.Count
ReDim ObjectArray( 0 To ObjectCount ) As String
Dim count As Integer
count = 0
'insert tables and views into the ObjectArray for the dialog list
For Each MyEntity In MyModel.Entities
ObjectArray(count) = MyEntity.EntityName
count = count + 1
Next
For Each MyView In MyModel.Views
ObjectArray(count) = MyView.Name
count = count + 1
Next
Begin Dialog UserDialog 510,224,"Synonym Editor" ' %GRID:10,7,1,1
GroupBox 20,7,470,161,"Choose the Table or View that you would like to create a synonym for:",.GroupBox1
CancelButton 350,182,130,28
DropListBox 40,28,220,168,ObjectArray(),.ObjectList
OKButton 200,182,130,28
TextBox 40,140,220,21,.SynName
OptionGroup .Group1
OptionButton 40,91,60,14,"Yes",.yes
OptionButton 110,91,60,14,"No",.no
Text 30,70,430,14,"Do you want the Synonym accessible to all users (ie, PUBLIC)?",.Text2
Text 30,119,370,14,"Please give a name for your synonym.",.Text1
End Dialog
Dim dlg As UserDialog
If Dialog(dlg) = -1 Then
If dlg.group1 = 0 Then
Synonym = "CREATE PUBLIC SYNONYM " & dlg.SynName & vbCrLf
Synonym = Synonym & vbTab & "FOR " & ObjectArray(dlg.objectlist) & vbCrLf & ";" & vbCrLf
Else
Synonym = "CREATE SYNONYM " & dlg.SynName & vbCrLf
Synonym = Synonym & vbTab & "FOR " & ObjectArray(dlg.objectlist) & vbCrLf & ";" & vbCrLf
End If
Dim ObjName As String
ObjName = ObjectArray(dlg.objectlist)
Set MyEntity = MyModel.Entities.Item(ObjName)
Set MyView = MyModel.Views.Item(ObjName)
If MyView Is Nothing Then
MyEntity.PostSQL = MyEntity.PostSQL & Synonym
Else
MyView.PostSQL = MyView.PostSQL & Synonym
End If
End If 'dialog
End If 'Physical check
End Sub