Archive for Macros

SQL Advent Calendar Day -15 – Naming Default Constraints

AdventCalendarDay15First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

Standardization

I truly believe in having coding standards. I think the best standards that a team can have, are the ones that they are willing to adhere to. If no one agrees that commas belong on the left rather than the right, then why does your standards say they have to be on the left?

One standard that I really like to have, and is supper easy to do using macros,  is having decent names for indexes and constraints. DF_Mytable_1837489274 does absolutely nothing for me. But DF_Mytable_isActive, is helpful when looking at all the constraints on my table.

ER Studio DA has three macros to help with these naming standards. Name foreign Key Constraints, Name Primary Key Constraints, and Index Naming. When creating default constraints in ER Studio Data Architect ( ER Studio DA), I found that I was getting random numbers on my default names in SQL Server. This was especially true if I created my defaults from Domains. (Domains are templates for fields. I wrote about Domains here.)So I created an additional macro called Name Default Constraints-Selected to round out this collection of macros.

My particular standard for naming defaults is as follows: DF__. If you have different standards that can be derived from information in the field, then you can change the pattern on line 42. Take a look at lines 36 and 37 too. These two lines are necessary when working with Domains. They make it possible to edit the default name for a field that is tied to a domain.

This macro will perform two different actions. It will create a name for defaults that don’t have one, and it will ask the user if they want to change a name that does not adhere to the standards. After the macro has completed, a dialog box is displayed showing how many defaults were named and how many had their name updated.

'----------------------------------------------------------------------------
' PURPOSE:  	This macro will create and update Default Constraint names on
'				Attributes For All selected Entities. If the attribute is
'				bound, it will overwrite the bound default and name the default.
' Author:   	Mickey Stuewe
' Date:     	12/15/2013
'----------------------------------------------------------------------------
Option Explicit

Sub Main

	Dim objModel As Model
	Dim objSubModel As SubModel
	Dim objSelObject As SelectedObject
	Dim objAttrib As AttributeObj
	Dim objEntity As Entity
	Dim iCounter As Integer
	Dim iNCcounter As Integer
	Dim iunBoundCounter As Integer
	Dim strDefaultName As String

	Set objModel = DiagramManager.ActiveDiagram.ActiveModel
	Set objSubModel = objModel.ActiveSubModel

	If Not objModel.Logical Then

		' Iterate through all the selected objects in the current
		' model.
		For Each objSelObject In objSubModel.SelectedObjects
			If objSelObject.Type = 1 Then  'Entities
				Set objEntity = objModel.Entities.Item(objSelObject.ID)
				For Each objAttrib In objEntity.Attributes
					If Len(objAttrib.DeclaredDefault) > 0 Then
						'if the attribute default is bound, then unbind it so that it can be named.
						If objAttrib.DomainId > 0 Then
							'You have to override the bound default value and the default text (name).
							objAttrib.EnableOverride (1,True)
							objAttrib.EnableOverride (2,True)
							iunBoundCounter = iunBoundCounter + 1
						End If

						strDefaultName = "DF_" & objEntity.EntityName & "_" & objAttrib.AttributeName
						If Len(objAttrib.DefaultName) = 0 Then
							objAttrib.DefaultName = strDefaultName
							iCounter = iCounter + 1
						ElseIf objAttrib.DefaultName <> strDefaultName Then
							If MsgBox("The name of the Default Constraint does not match our naming standards. Would you like to replace it?" & vbCrLf & _
								vbCrLf & vbCrLf & "Attribute: " & objAttrib.AttributeName & " - Default Name: " & objAttrib.DefaultName & " - Value: " & _
								objAttrib.DeclaredDefault & vbCrLf & " Standardized Name: " & strDefaultName, _
								vbYesNo + vbQuestion, "Stadardize Name?") = vbYes Then
									objAttrib.DefaultName = strDefaultName
									iCounter = iCounter + 1
							End If
						End If
					Else
						iNCcounter = iNCcounter + 1
					End If
				Next objAttrib
			End If
		Next objSelObject

		MsgBox iunBoundCounter & " had their defaults unbound so that they could be named." & vbCrLf & vbCrLf & _
				iCounter & " Default Constraints had their name created or updated." & vbCrLf & vbCrLf & _
				iNCcounter & " Attributes were not modified.", vbOkOnly + vbInformation, "Default Constraint Name(s) Created or Updated"

	Else
		MsgBox "You are in a Logical model. Move to a Physical model to name of Default Constraints.", vbOkOnly + vbCritical, "Need Physical Model"
	End If
End Sub

 

Note: You can download this macro from my Script Library under Resources. It’s called Name Default Constraints – Selected.

 

Previous Post In Series: SQL Advent Calendar – Day 14 – Working With Forms in a Macro in ER Studio Data Architect

Next Post In Series: SQL Advent Calendar – Day 16 – Snippet For In-Line Table Functions

SQL Advent Calendar – Day 14 – Working With Forms in a Macro in ER Studio Data Architect

AdventCalendar14

First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

Creating a Form

One of the options that you have when creating a macro in ER Studio Data Architect (ER Studio DA),  is the ability to create a form known as a dialog. The form can gather information for the macro to use, display information to the user after an action has been performed, or interact with the user while the objects in the diagram are updated.

There are three steps to perform when setting up a form.

  1. Creating the layout of the form, including placing the controls on the form.
  2. Writing the code to pre-populate the controls.
  3. Writing the code to perform actions after the dialog is used by the user.
Don’t recreate the wheel

ER Studio DA comes with quite a few working macros. They provide great examples on how to work with the objects and how to work with forms. I would recommend looking at the Definition Editor macro and the Index Naming macro. They both have highly interactive forms which show how to work with the different controls and how to interact with the events of the controls.

Note: While the objects representing the various aspects of the data model are well documented, I have yet to find documentation on the dialog object or the controls that are placed on it. I do admit, I have not tried contacting Embarcadero as of yet either.

Let’s look at the code from the Definition Editor macro.  The first line below begins the definition of the form followed by a list of controls that will appear on the form. The order they are listed will be the sequential order of the controls if you TAB through them. Below I numbered a few areas to look at.

  1. The first two numbers are the height and width of the form followed by the name that will appear in the title bar.
  2. The last parameter contains the name of the Sub that you would need to write to consume any actions generated by the form. (e.g. When you click the OK button, the MyDialogFunc procedure would be triggered.)
  3. At the end of controls that will cause an event, such as when a button is pushed, you will find a name preceded by a period. This is not a procedure name like we saw with #2 above. This will be the keyword you search for in the MyDialogFunc. We’ll see this in a minute.
  4. After the definition of the form is created, a variable is created to represent the form during its life.
  5. Next the controls that need to be initialized and pre-populated are coded.
  6. And Finally the call to the form is called which will display the form to the user.

 

AdventCalendar2013Day14_Img1

You can manually create and modify the form, or you can use the Editor User Dialog button on the toolbar. Make sure your cursor is in the middle of the code for the form, or you will create another form.

AdventCalendar2013Day14_Img2

When the myDialogFunc procedure is created, it requires three parameters.

  • DlgItem is the name of the control that caused an event, which caused the MyDialogFunc to be called.
  • Action is the action of the event. (e.g. A button was pushed.)
  • SuppValue contains the data of the DlgItem.

If you were to capture the pushbutton event for the UpdateTable, you would create an If statement to look at the DlgItem value when Action = 2.

There are some other functions that are needed while working the controls. In the screenshot below, you can see DlgValue and DlogText. These allow you to get information from other controls on the form. There are others that you look at in other macros as well.

AdventCalendar2013Day14_Img3

Can’t leave it alone

I really like this macro, but there was one issue. I use schemas (called owners in ER Studio DA), and I wanted them to be displayed with the table names… So I modified the code.

The first thing I did was to widen the list boxes. I like long names and they just didn’t fit. I used the User Dialog Editor to accomplish this.

The second thing I did, was add MyEntity.Owner to the name of the tables. This was done in the procedure called getTables.

 

Note: Tables are referenced differently in the logical model than the physical model. Make sure you are checking to see if the user executed the macro from the logical or physical model and then use the correct object.

 

 

AdventCalendar2013Day14_Img4

 

Previous Post In Series: SQL Advent Calendar – Day 13 – Snippet for New Transactional Procedure

Next Post In Series:  SQL Advent Calendar Day -15 – Naming Default Constraints

SQL Advent Calendar 2013 – Day 9 – Creating Change Tracking Fields (2 of 3)

AdventCalendar09First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

Second Step Is Creating The Change Tracking Fields

Today I’ll share with you my macro called Add Change Tracking Attributes. This script will add the attributes InsertedBy, InsertedDTS, ModifiedBy, and ModifiedDTS to the selected entities. These attributes are based on the domains that were created by the macro in yesterday’s post found here. If you add these fields, and then decide you don’t want them, you can use my macro called Remove Attribute From Selected Tables, which I blogged about here to remove them.

'----------------------------------------------------------------------------
' PURPOSE:  	This macro adds Change Tracking Attributes to Selected Entities.
' Author:   	Mickey Stuewe
' Date:     	12/9/2013
'----------------------------------------------------------------------------

Sub Main

	Dim objModel As Model
	Dim objSelObject As SelectedObject
	Dim objEntity As Entity
	Dim objAttribute As AttributeObj
	Dim iCounter As Integer
	Dim dict As Dictionary
	Dim iInsertedDateID As Integer
	Dim iInsertedByID As Integer
	Dim iModifiedByID As Integer
	Dim iModifiedDateID As Integer

	' Get the Local Data Dictionary.
	Set dict = DiagramManager.ActiveDiagram.Dictionary

    ' Determine Domains.
    Set MyDomain = dict.Domains.Item("InsertedDTS")
    iInsertedDateID = MyDomain.ID

    Set MyDomain = dict.Domains.Item("InsertedBy")
    iInsertedByID = MyDomain.ID

    Set MyDomain = dict.Domains.Item("ModifiedDTS")
    iModifiedDateID = MyDomain.ID

    Set MyDomain = dict.Domains.Item("ModifiedBy")
    iModifiedByID = MyDomain.ID

	Set objModel = DiagramManager.ActiveDiagram.ActiveModel

	' Iterate through all the selected objects in the current
	' model.
	For Each objSelObject In objModel.ActiveSubModel.SelectedObjects

		'Get the object type - we are only concerned
		'with entities.
		If objSelObject.Type = 1 Then

			' Get the actual entity object with this ID.
			' The model contains the collection of all the
			' entities.
			Set objEntity =  objModel.Entities.Item(objSelObject.ID)

			'Create Tracking fields and set DomainIDs
			Set objAttribute = objEntity.Attributes.Add("InsertedBy", False)
			 SetDefaultDomain objAttribute, iInsertedByID,objEntity.EntityName

			Set objAttribute = objEntity.Attributes.Add("InsertedDTS", False)
			SetDefaultDomain objAttribute, iInsertedDateID,objEntity.EntityName

			Set objAttribute = objEntity.Attributes.Add("ModifiedBy", False)
			SetDefaultDomain objAttribute, iModifiedByID,objEntity.EntityName

			Set objAttribute = objEntity.Attributes.Add("ModifiedDTS", False)
			SetDefaultDomain objAttribute, iModifiedDateID,objEntity.EntityName

			iCounter = iCounter + 1
		End If
	Next objSelObject

	MsgBox "Four new Attributes were added to " & iCounter & " Table(s).", vbOkOnly + vbInformation, "Attributes Added To Tables"

End Sub

Sub SetDefaultDomain(objAttribute As AttributeObj, iDomainID As Integer, strEntityName As String)

	objAttribute.DomainId = iDomainID

	If Len(objAttribute.DeclaredDefault) > 0 Then

		'if the attribute default is bound, then unbind it so that it can be named.
		If objAttribute.DomainId > 0 Then
			'You have to override the bound default value and the default text (name).
			objAttribute.EnableOverride (1,True)
			objAttribute.EnableOverride (2,True)

		End If

		If Len(objAttribute.DefaultName) = 0 Then
			objAttribute.DefaultName = "DF_" & strEntityName & "_" & objAttribute.AttributeName

		ElseIf objAttribute.DefaultName <> "DF_" & strEntityName & "_" & objAttribute.AttributeName Then
			If MsgBox("The name of the Default Constraint does not match our naming standards. Would you like to replace it?" & vbCrLf & _
				vbCrLf & vbCrLf & "Attribute: " & objAttribute.AttributeName & " - Default Name: " & objAttribute.DefaultName & " - Value: " & _
				objAttribute.DeclaredDefault & vbCrLf & " Standardized Name: " & "DF_" & strEntityName & "_" & objAttribute.AttributeName, _
				vbYesNo + vbQuestion, "Stadardize Name?") = vbYes Then
					objAttribute.DefaultName = "DF_" & strEntityName & "_" & objAttribute.AttributeName

			End If
		End If
	End If
End Sub

Take a look at lines 81 and 82. These two lines are needed to be able to give the default a name and here is why: This macro creates attributes based off of domains. The Inserted attributes have defaults. If we don’t name the defaults, then SQL Server will provide names for them…and they are not pretty. In order for us to be able to override the domain, we have to set the enableoverride property for two properties. Then we’ll be able to provide a name to the default. This step can’t be done in the domain, because defaults are a type of constraint and all constraints need to have unique names.

Tomorrow I’ll show you a Red Gate Snippet used to create the triggers for the tables with these fields.

Previous Post In Series: SQL Advent Calendar 2013-Day 8-Creating Change Tracking Domains (1 of 3)

%d bloggers like this: