Tag Archive for ER/Studio Data Architect

Data Models, SQL Server, SQLite, and PowerShell

The Fourth Mission

In this installment of SQLCoOp, we are sharing our views on something we’ve learned this month. While I learned quite a bit this month in order to create a new SQLite Database with test data that mapped back to my SQL Server database, I will limit this post to how my first PowerShell script aided me in creating a new SQLite Database from Embarcadero’s ER/Studio Data Architect.

MCJJ_ALL

The Project

I’ve been working on a project recently where we have a SQL Server backend on our server and SQLite databases on our client machines. The two databases have quite a few tables in common, but the two database platforms don’t share the same toolsets. Here are the hurdles I had to overcome.

  1. The SQL language that SQLite uses is similar to T-SQL, but not the same. This means scripting tables, constraints, and other SQL objects are slightly different.
  2. I use Embarcadero’s ER/Studio Data Architect (DA) to do all of my data modeling, but there isn’t a driver that will work with DA to compare my database model to a SQLite database so that I can keep them in sync.
  3. I needed to create test data in both databases, but I don’t have an environment where the databases can coexist so that I can write the proper insert scripts.

In this post, I’ll focus on the first two hurdles. The third hurdle was solved using Red Gate’s SQL DataGenerator, Python, and SSIS. I will save this story for another time since it is worthy of a blog post all on its own.

Hurdle 1: Understanding SQLite

The first thing I had to learn was the ins and outs of SQLite. I acquired a great book, Using SQLite and started reading. I also searched the depths of the internet. What I found was that SQLite likes to store almost everything as integer, numeric, and text, BUT there are data types that we are familiar with like varchar, datetime, etc. They just get “interpreted” as integers, numeric, and text depending on what the data types are. This is a good reference for understanding how the more familiar data types are actually stored in the SQLite database.

When I was first learning about SQLite, I mistook the storage classes as actual data types. I thought SQLite was going to use a type of implicit conversion, but SQLite is not the same as SQL Server. There is also a big benefit to using the more familiar data types instead of sticking to the storage classes that SQLite uses. The benefit has to do with the ADO.NET class and making life easier for your application developer who has to take data from SQL Server and get it into SQLite.

NOTE: You REALLY need to understand how dates work in SQLite. Depending on how the date and time are stored in the underlying database (string, integer, numeric) will determine what “zero” is equivalent to. Read this to find out more.

Hurdle 2: Creating SQL Scripts for Schema Changes From ONE Environment

There are several software packages out in the world that will allow you to get visual data models of SQLite tables, but I already use Embarcadero’s ER/Studio Data Architect (DA) and I didn’t want to learn another one. I also didn’t want to have my SQL Server data models in one software package and my SQLite data models in a different one. So I had to figure out how to get around this hurdle…and I used PowerShell to do it.

The first thing I did, was to create my data models. My first SQLite data model was based on my initial research which implied I should store all my data in integer and text data types since that is what the engine will be using to store the data. This was a poor assumption on my part. Since I had both my SQL Server and my SQLite database models in DA, I was able to easily update my data types in my SQLite database with their SQL Server counter parts. I did have to inspect each field manually since not all attributes were the same between the two databases. For instance, I have to use CURRENT_TIMESTAMP instead of getdate() and I had to model auto incrementing columns differently.

Note: This is a sample database

SQLCoOp_Image3

SQL Server database

SQLCoOp4_Image2

First iteration of my SQLite database

NOTE: I was able to use the Compare and Merge Utility by selecting the option to compare against another model.

SQLCoOp4_Image1

 

SQLCoOp_Image4

Second iteration of my SQLite database

Making a SQLite Script from ER/Studio Data Architect

Before we go down this rabbit hole, there is another piece of information that you need to know about SQLite. You can’t add foreign key constraints after the table is created. This is very frustrating for me since I know of some existing tables that need foreign key constraints and it will be a bigger task than it would be in SQL Server to get foreign key constraints applied. For this project, it means I need to script the foreign keys as well as the primary keys in the table creation script.

There are two operations that need to be done in order to generate a SQLite script from DA. The first is to use the Generate Database Wizard to generate a whole database instead of using the Compare and Merge Utility to compare the database model with the actual database. This operation is outlined below in steps 1 though 6. The second operation is to use a simple PowerShell script to make the necessary changes to the script so that it will no longer be a T-SQL script made to run in SQL Server, but a SQL script ready to run in SQLite. This is outlined in step 7 below.

Here are the steps you’ll need to do:

Step 1

Make sure that all auto incrementing fields are not set up as primary keys in the database model. The reason for this, is all auto incrementing fields are primary keys, so the syntax for creating an auto incrementing field is very specific.

Take a look at the EmployeeAward table in DM SQLite above. It is setup as an identity column, but not as a primary key. In order for me to have a visual cue that the field is a primary key, I used an object called a Domain. Domains are templates that can be used throughout the database model so that data types, defaults, and even definitions remain consistent and can easily be changed throughout an entire database model or across all database models if the Repository feature of DA is being used.

Step 2

Right click the physical model that you will be working with, and select Generate Database. You will be walked through a wizard. At the end you are going to want to save your settings so that you don’t have to select them again.

SQLCoOp_Image6

Step 3

On Page 1 of the wizard, you can create individual scripts or a single long script. I’ve been doing both so that I can select a single table later on if I need to regenerate it. You will also need to select the folder that you would like the scripts to be created in. I created 3 folders for my project.

  • MultiScripts (This will hold scripts for each object separately)
  • SingleScript (This will hold a single script that contains all the objects.)
  • Standardize (This holds the PowerShell script called StandardizeSQL.ps1 which will be explained in step 7.)
Step 3

I don’t do anything on page 2 of the wizard since this script will not run in SQL Server.

Step 4

Page 3 of the wizard is where the fun starts. Set the following options for the tables you want to include in the script. These options are found on the General Options tab. All the other tabs don’t need to have their options modified…unless you used features like sparse columns that can’t be used in SQLite. Then you might need to fiddle with some of the other settings.

 

SQLCoOp_Image5

Step 5

Save your settings on page 4 of the wizard so that you can quickly run through the wizard next time.

Step 6

If you are asked to open your newly created script, then say no. We’re not done yet.

Step 7

Run the following PowerShell script. You can do this by right clicking the file containing the PowerShell script and selecting Run with PowerShell. Note: The ability to run PowerShell scripts needs to be enabled first.

The script will go through all the *.sql files in the indicated folder and change the T-SQL specific syntax to the SQLite syntax. Here are the syntactical changes.

  • Int will be changed to integer.
  • Identity columns will be changed to primary key autoincrement columns.
  • The words nonclustered and clustered will be removed from primary and foreign key constraints since all indexes are nonclustered and those keywords are not supported in SQLite.

Before you run the script below, you’ll need to change the path to your scripts on line 8.

Note: there is a space before the asterisk on line 8 and there are spaces on either side of “int” on line 12.

# To Run this in the Powershell it would be written as ./StandardizeSQL.ps1
# Note: nonclustered needs to come before clustered since clustered is in the word nonclustered.

$wshell = New-Object -ComObject Wscript.Shell -ErrorAction Stop

Try
{
$configFiles=get-childitem -Recurse C:\SQLiteScripts *.sql

foreach ($file in $configFiles)
{
(Get-Content $file.PSPath) |
Foreach-Object {$_ -replace " int ", " integer "} |
Foreach-Object {$_ -replace "IDENTITY\(1,1\)", "PRIMARY KEY AUTOINCREMENT"} |
Foreach-Object {$_ -replace "nonclustered", ""} |
Foreach-Object {$_ -replace "clustered", ""} |
Set-Content $file.PSPath

}

$wshell.Popup("Success! And there was much rejoicing!", 0,"Done.",64)
}
Catch
{

$wshell.Popup($_.Exception.Message,0, "Oops! An error occurred.",48)
Break
}

After you are done running the PowerShell script, you’ll have a SQLite script that you can run in your favorite SQLite querying tool.

Here is the before and after scripts for two of the tables from my example.

CREATE TABLE Employee(
EmployeeID int NOT NULL,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
isActive bit DEFAULT 0 NOT NULL,
InsertedDTS datetime CONSTRAINT [DF_Employee_InsertedDTS] DEFAULT CURRENT_TIMESTAMP NOT NULL,
ModifiedDTS datetime NULL,
CONSTRAINT PK_Employee PRIMARY KEY CLUSTERED (EmployeeID)
)
;

CREATE TABLE EmployeeAward(
EmployeeAwardID int IDENTITY(1,1),
EmployeeID int NOT NULL,
AwardName varchar(100) NOT NULL,
AwardDate datetime NULL,
CONSTRAINT FK_EmployeeAward_Employee FOREIGN KEY (EmployeeID)
REFERENCES Employee(EmployeeID)
)
;

Before

CREATE TABLE Employee(
EmployeeID integer NOT NULL,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
isActive bit DEFAULT 0 NOT NULL,
InsertedDTS datetime CONSTRAINT [DF_Employee_InsertedDTS] DEFAULT CURRENT_TIMESTAMP NOT NULL,
ModifiedDTS datetime NULL,
CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID)
)
;

CREATE TABLE EmployeeAward(
EmployeeAwardID integer PRIMARY KEY AUTOINCREMENT,
EmployeeID integer NOT NULL,
AwardName varchar(100) NOT NULL,
AwardDate datetime NULL,
CONSTRAINT FK_EmployeeAward_Employee FOREIGN KEY (EmployeeID)
REFERENCES Employee(EmployeeID)
)
;

After

Don’t Stop Yet

If you want to read more about the newly released SQL Search, don’t forget to check out these blog posts:

To follow our quest for SQL knowledge through this collaborative project, follow the #SQLCoOp tag on Twitter.

See you next time!!

On a SQL Collaboration Quest

Four SQL professionals gathered from the four corners of the world to share their SQL knowledge with each other and with their readers: Mickey Stuewe from California, USA, Chris Yates from Kentucky, USA, Julie Koesmarno from Canberra, Australia, and Jeffrey Verheul from Rotterdam, The Netherlands. They invite you to join them on their quest as they ask each other questions and seek out the answers in this collaborative blog series. Along the way, they will also include other SQL professionals to join in the collaboration.

Original Post: On a SQL Collaboration Quest

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)

SQL Advent Calendar 2013 – Day 8 – Creating Change Tracking Domains (1 of 3)

AdventCalendar08First 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.

First Step Is The Foundation

Whenever I need to have minimal auditing on a table, I add four fields to the table. InsertedBy, InsertedDTS, Modifiedby, and ModifiedDTS. The Inserted fields tell me who created the row. These two fields are updated based on the defaults set on the columns. The Modified fields tell me who changed the fields and are updated by a trigger.

Over the next couple of days I will show you two ER/Studio Data Architect (ER Studio DA) macros and one SQL Prompt Script that will help you create these four fields and the needed triggers.

The first step is making sure that your data model has four Domains. They reside in the Data Dictionary on the Data Dictionary tab. Domains are templates that fields can be created against.

There are two types of Data Dictionaries. There are local Data Dictionaries and Enterprise Data dictionaries. A data model can only have one local Data Dictionary and it always exists. If you are using ER Studio DA’s Repository feature, then you can create an Enterprise Data Dictionary to use across al Data Models. A Data Model can have multiple Enterprise Data Dictionaries. This is a great feature and I have used it in the past, but it isn’t available if you are not using the Repository.

 

AdventCalendar2013Day8_Img1

 

I’ve created a macro called Create Change Tracking Domains to use the local Data Dictionary.

The macro does the following: If a folder called Change Tracking doesn’t exist in the Data Dictionary, then it is created. If the four change tracking domains don’t exist, then they will be created. Either way, all the properties of these four domains will either be created or reset when this macro is run.

Note: You can modify it to use an Enterprise Data Dictionary, or you can see if I have time to add it to the macro for you.

Below, in the code. You can see the properties that are set for each field (Data type, name, definition, nullability, and default.)

'----------------------------------------------------------------------------
' PURPOSE:  	This macro adds four domains to aid in creating Change Tracking
'				fields.
' Author:   	Mickey Stuewe
' Date:     	12/9/2013
'----------------------------------------------------------------------------

Sub Main

	Dim dict As Dictionary

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

	'If the Change Tracking folder does not exist, then add it.
	If (dict.DomainFolders.Item("Change Tracking") Is Nothing) Then
		dict.DomainFolders.Add("Change Tracking","Domains")
	End If

    ' if Domain doesn't exist, then create it.
	If (dict.Domains.Item("InsertedDTS") Is Nothing) Then
		dict.Domains.Add("InsertedDTS", "InsertedDTS")
	End If

	With dict.Domains.Item("InsertedDTS")
		.AttributeName = "InsertedDTS"
		.Datatype="DateTime"
		.DeclaredDefault = "GETDATE()"
		.Definition = "This is the date the row that was inserted."
		.DomainFolder = "Change Tracking"
		.Nullable = False
	End With

    ' if Domain doesn't exist, then create it.
	If (dict.Domains.Item("InsertedBy") Is Nothing) Then
		dict.Domains.Add("InsertedBy", "InsertedBy")
	End If

	With dict.Domains.Item("InsertedBy")
		.AttributeName = "InsertedBy"
		.Datatype = "nvarchar"
		.DataLength = 255
		.DeclaredDefault = "SUSER_NAME()"
		.Definition = "This is the system user who created the row."
		.DomainFolder = "Change Tracking"
		.Nullable = False
	End With

	 ' if Domain doesn't exist, then create it.
	If (dict.Domains.Item("ModifiedDTS") Is Nothing) Then
		dict.Domains.Add("ModifiedDTS", "ModifiedDTS")
	End If

	With dict.Domains.Item("ModifiedDTS")
		.AttributeName = "ModifiedDTS"
		.Datatype="DateTime"
		.Definition = "This is the date the row was modified. It is updated via a trigger."
		.DomainFolder = "Change Tracking"
		.Nullable = True
	End With

 	' if Domain doesn't exist, then create it.
	If (dict.Domains.Item("ModifiedBy") Is Nothing) Then
		dict.Domains.Add("ModifiedBy", "ModifiedBy")
	End If

	With dict.Domains.Item("ModifiedBy")
		.AttributeName = "ModifiedBy"
		.Datatype = "nvarchar"
		.DataLength = 255
		.Definition = "This is the system user who modified the row. It is updated via a trigger."
		.DomainFolder = "Change Tracking"
		.Nullable = True
	End With

	Set dict = Nothing
End Sub

Tomorrow I’ll share the macro that will add these four fields to selected tables.

Previous Post In Series: SQL Advent Calendar – Day 7 – Verify All Tables and Fields Have a Definition

Next Post in Series: SQL Advent Calendar 2013 – Day 9 – Creating Change Tracking Fields (2 of 3)

SQL Advent Calendar – Day 7 – Verify All Tables and Fields Have a Definition

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.

Oops, I missed one.

I try to remember to add definitions as I create my tables so that I don’t forget later, but sometimes I miss one…especially definitions for attributes. Here is a great macro for ER/Studio Data Architect to find all the Entities and Attributes that are missing definitions. It even has a feature to colorize the tables that are missing definitions. I call it Verify Definitions For Selected Objects.

Note: Right now it only provides definitions for Entities and Attributes. I am happy to expand it if you need the other objects.
Option Explicit
'----------------------------------------------------------------------------
' PURPOSE:  	Creates a list of Entities and Attributes that are missing
'				definitions.
' Author:   	Mickey Stuewe
' Date:     	9/7/2013
'----------------------------------------------------------------------------
Sub Main
	'Defines the dialog box
	Begin Dialog UserDialog 680,322,"Verify Definitions for All Objects", .MyDialogAction
		Text 20,7,360,14,"Entities and Attributes that need Definitions",.Text1
		TextBox 20,28,500,287,.txtList,1
		PushButton 540,28,130,21,"Check",.CheckForDefinitions
		OKButton 540,49,130,21
		CheckBox 540,84,130,14,"Colorize Entities",.chkColor
	End Dialog

	Dim dlg As UserDialog

	'Calls the dialogbox
	Dialog dlg, -2
End Sub

Sub CheckForDefinitions()
    Dim objDiagram As Diagram
	Dim objModel As Model
	Dim objSubModel As SubModel
	Dim objSelObject As SelectedObject
	Dim objEntity As Entity
	Dim objAttr As AttributeObj

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

	'Clears list
	DlgText("txtList","")

	' Iterate through all the selected objects in the current
	' model.
	For Each objSelObject In objSubModel.SelectedObjects
		Select Case objSelObject.Type
			Case 1 'Entity
				If objModel.Entities(objSelObject.ID).Definition = "" Then
					Call AddToList("Entity", objModel.Entities(objSelObject.ID).Owner & "." & objModel.Entities(objSelObject.ID).EntityName)
					If DlgValue("chkColor") = 1 Then
						objSubModel.EntityDisplays.Item(objModel.Entities(objSelObject.ID).TableName).BackgroundColor = RGB(243,222,248)
					End If

				End If
				For Each objAttr In objModel.Entities(objSelObject.ID).Attributes
					If objAttr.Definition = "" Then
						Call AddToList("Attrib", objModel.Entities(objSelObject.ID).Owner & "." & objModel.Entities(objSelObject.ID).EntityName & "." & objAttr.AttributeName)
						If DlgValue("chkColor") = 1 Then
						objSubModel.EntityDisplays.Item(objModel.Entities(objSelObject.ID).TableName).BackgroundColor = RGB(243,222,248)
						End If
					End If
				Next

			Case 16 'View
				MsgBox "It's time to write code for looking at Definitions for Views."
			Case 54 'Function
				MsgBox "It's time to write code for looking at Definitions for Functions."
		End Select
	Next objSelObject

End Sub

Sub AddToList(strObject As String, strObjectName As String)
	'Adds Entity or Attribute to list.
	DlgText("txtList",DlgText("txtList") & strObject & ": " & strObjectName & vbCrLf )

End Sub

'This is called for all actions in the dialog box. I will be traping for the button being clicked.
Private Function MyDialogAction(DlgItem As String, Action As Integer, SuppValue&) As Boolean

	If Action = 2 Then ' Value changing or button pressed
		Rem MyDialogFunc = True ' Prevent button press from closing the dialog box

		If DlgItem = "CheckForDefinitions" Then
			CheckForDefinitions
			MyDialogAction = True
		Else
			MyDialogAction = False
		End If
	End If
End Function

 

AdventCalendar2013Day7_Img1

 

Previous Post In Series: SQL Advent Calendar – Day 6 – What’s In All These Indexes?

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

SQL Advent Calendar 2013 – Day 3 – Organize Before All The New Toys Come In

AdventCalendar03First 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.

Installing A New Macro

AdventCalendar2013Day3_Img1

When I first started working with the provided macros in ER Studio Data Architect by Embarcadero (ER Studio DA), I would keep my modified copies in the same folders as the macros that came with the product. I soon realized that it would be better to create my own folders. This allowed me to leave the provided library intact and it allowed me to put my macros in source control (where all code should be kept).

The default location of the macros for version 9.6 is c:\ProgramData\Embarcadero\ERStudioDA_9.6\Macros. You can create your own folders in this location either through the application or through Explorer. There is a “refresh” menu item in the shortcut menu that you can use to refresh the list of macros. I created sub-folders similar to the ones that were under Sample Macros as well. I stored the Remove Attribute From Selected Tables macro I created under Modeling Productivity Macros subfolder.

Once you have your new macros in ER Studio DA, you can add them to the shortcut menus for various objects. You can have up to 10 macros in the shortcut list at a time.

AdventCalendar2013Day3_Img2To add yesterday’s macro to the shortcut menu for Tables, do the following:

  1. Right -click on any table in any model.
  2. Click on Add/Remove Macro Shortcuts.
  3. Select the macro you added to your library called Remove Attribute From Selected Tables.

Next time you right-click on a table, you’ll have that macro listed under macros. You can have up to 10 macros at any given time. The list will change based on what kind of object you have right-clicked on.

Note: Not all macros can be run in both the Logical and Physical models. I usually provide a warning when I run a macro that won’t work in a particular model so that I know it’s not going to do anything. I’ll discuss this more in future posts this month.

Previous Post In Series: SQL Advent Calendar 2013 – Day 2 – Macro to Remove Common Fields in ER Studio Data Architect

Next Post In Series: SQL Advent Calendar 2013 – Day 4–Comments Are Like Love Notes to Your Future Self

SQL Advent Calendar 2013 – Day 2 – Macro to Remove Common Fields in ER Studio Data Architect

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

AdventCalendar02As 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.

Behind Door Number Two

ER Studio Data Architect by Embarcadero (ER Studio DA) has the ability to create macros, which makes me absolutely happy. Why? Because I don’t like repetitive work. If I can create a macro, then I can have the computer do the repetitive work while I go get a peppermint mocha.

One day I was working on a macro that created fields for multiple tables. I realized during the development process that I also needed a macro to delete fields for multiple tables. So I created the macro below. It has come in handy many times.

'----------------------------------------------------------------------------
' PURPOSE:  	This macro adds base attributes to entities that are selected.
' Author:   	Mickey Stuewe
' Date:     	9/3/2013
' version:      1.0
'----------------------------------------------------------------------------

Sub Main

Dim objModel As Model
Dim objSelObject As SelectedObject
Dim objEntity As Entity
Dim iCounter As Integer

Begin Dialog UserDialog 440,98,"Continue?" ' %GRID:10,7,1,1
	Text 20,21,250,28,"Are you sure you want to remove a field from the selected tables?",.lblOwner
	OKButton 300,21,120,28
	CancelButton 300,56,120,28
	Text 20,56,80,14,"Field Name:",.Text1
	TextBox 100,56,170,14,.txtFieldName
End Dialog

Dim dlg As UserDialog

If Dialog(dlg) = -1 Then

	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

			Set objEntity =  objModel.Entities.Item(objSelObject.ID)

			objEntity.Attributes.Remove(dlg.txtFieldName)

			iCounter = iCounter + 1
		End If
	Next objSelObject

	MsgBox dlg.txtFieldName & " was removed from " & iCounter & " Table(s).", vbOkOnly + vbInformation, "Attributes Removed From Tables"
End If

End Sub

To use this macro, follow the steps below.

  1. Add the macro to the library. (Tomorrow I’ll be expanding on this step.)
  2. Add the macro to the shortcut menu for Tables.
  3. Select several tables that have a common field you would like to remove.
  4. Implement your new macro
  5. Watch the unwanted fields go away. (When I do this to 30 tables at one time, I actually feel giddy. True story.)
Note: You can download this macro from my Script Library under Resources. It’s called Remove Attribute From Selected Tables.

Next Post In Series: SQL Advent Calendar 2013 – Day 3 – Organize Before All The New Toys Come In

SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

AdventCalendar01Today my daughter Natalie and I started our first LEGO Advent Calendar – Star Wars edition. While this particular calendar is not Christian centric, it’s a lot of fun for us. For those that aren’t familiar with an Advent Calendar, it’s a calendar that counts down the days until Christmas. Each day a small gift or message is given.

My husband grew up with a handmade Advent Calendar. He told me when we were engaged that he wanted one for his family to use. Since we got married 6 weeks after I graduated from college and I had no money, I made one for my wedding present to him. As I was hanging it up today, I thought I would make one for you, my readers. Over the next 25 days, I’ll share my scripts with you from two of my favorite products as well as tips on how to create your own.

SQL Prompt By Red Gate

One of my favorite tool companies is Red Gate out of the UK. I not only love their tools, but I love their service. They care not only about their products, but what we think of their products and how we use their products. Over the last year I have had the honor to be part of their FoRG group (Friends of Red Gate). As a FoRG member, I get to give my suggestions directly to the project managers and others seeking insight from the FoRG group. Don’t think you can’t give your two cents too. Red Gate loves to hear from everyone. In fact, at PASS Summit this year they dedicated their whole back wall to receiving sticky notes for ideas on how to improve all their products. By the end of the conference, it was quite full.

SQL Prompt was the first of their products that I used. I use it just about every time I’m in SSMS, and have been very fortunate to be able to use it at my new job. (It’s very frustrating when I can’t use it.)

SQL Prompt does a couple of things for you. The most obvious thing that it does, is help you write SQL faster. Yes, SQL 2012 has intellisence, but SQL Prompt has a better version. The second feature that I love is the code standardization options. There are several settings and shortcut keys that you can use to standardize your code. The third feature that I love and will be featuring in my SQL Advent Calendar, are templatized scripts (called snippets). The snippets are accessed by keystrokes that you specify. A library of snippets comes with SQL Prompt, but you can also add your own.

ER/Studio Data Architect by Embarcadero

Embarcadero has a very wide range of products, and one of those is on my favorite tool list. I’m sure you can guess what it is based on my last couple of posts here and here. It’s Embarcadero’s ER/Studio Data Architect.

ER/Studio Data Architect is a database modeling tool. It’s a great product to use as the master copy of a database schema. It also has the ability to create scripts for different versions of databases. This came in real handy when I needed to script a database model for both SQL 2000 and SQL 2012.

One of the features that I really like ER/Studio Data Architect, is the ability to create and use macros inside the product to update the database model. All you need to know is VB script and the Object Model.

So, without further ado…..

Day 1 – Placeholders in SQL Prompt snippets

You may ask why I like the SQL Prompt snippets since SQL 2012 has a template library that is very similar. There are currently 8 reasons why I like them. ($DATE$, $TIME$, $USER$, $PASTE$, $MACHINE$, $CURSOR$, $SERVER$, and $DBNAME$) These are the 8 placeholders that can be used in conjunction with the snippets. As each name suggests, they represent a piece of information that is unique. $USER$ will be replaced by the name of the User logged into SSMS and $DBNAME$ will be replaced by the name of the currently active database. $PASTE$ will grab what is in your copy buffer and $CURSOR$ will move the cursor to the that location in your snippet. (An important thing to remember is that the placeholders are case sensitive.)

Note: There are 8 placeholders with version 6.1, which is the current version. Other versions may have a different set with different functionality. Visit the documentation for the version you have to see the current list.

To create an SSMS placeholder you use the syntax below.

<X,Y,Z>
  • Where X is the name of the placeholder and should be very descriptive. It will be viewed in a dialog box with all the other placeholders
  • Y is the data type of the placeholder. Most of the time I use “string”
  • Z is the default value for the placeholder and is optional, but you still need to have the third parameter position available. This is where you can really leverage the SQL Prompt Placeholders.

In the example below I created three SSMS placeholders and I use three different SQL Prompt placeholders. Two of the SSMS placeholders are identical, so they will appear as one placeholder to the user. Notice that $DBNAME$ is used inside the SSMS placeholder. This will allow the current database name to be the default for that placeholder.

USE <Database Name,string,$DBNAME$>
GO

--Created by: $USER$ on $DATE$

SELECT
     name
FROM
     <Database Name,string,$DBNAME$>.sys.objects AS o
WHERE
     name LIKE '<Search Criteria,string,>%'

To use the code above, copy it into a new snippet in SQL Prompt.

  1. Open up the Snippet Manager.
  2. Click the New button.
  3. Name the snippet TNS.
  4. Set the description Table Name Search.
  5. Save the script.

In a new query window type TNS+. Notice that your SQL credentials were put in place of $USER$. Today’s date was put in place of $DATE$ and the current database was put in place of $DBNAME$.

AdventCalendar2013Day1_Img1

Now let’s leverage the SSMS placeholders by typing ++M. You can now accept the default values, or you can change them. After you click OK, you can run the script.

That’s a wrap

I hope you enjoyed the first day of 2013 SQL Advent Calendar.

Note: Since I live near the end of the GMT day, the calendar will be done based on the PST zone.

Next Post In Series:

SQL Advent Calendar 2013 – Day 2 – Macro to Remove Common Fields in ER Studio Data Architect

 

SQL Bacon Bits No. 2 – Dropping Temporary SQL Objects

Bacon file8121243652304This is my second SQL Tidbit and I have already decided to change the name to SQL Bacon Bits. Why? Because it’s my blog and I can. These quick posts are supposed to be simple and yummy, just like bacon.

Today’s featured product is ER/Studio Data Architect.

The Need

Whenever a change script is created from an ER/Studio Data Architect model, the original objects are kept with a date time stamp as part of their name. These original copies are then left for the developer to determine if they are still needed.

When I first came across these objects in my database, I was flummoxed. “Why would they leave such a mess?”, I asked myself. But they didn’t leave a mess. They were actually being considerate. Sometimes the SQL Objects have issues because of the changes being brought into the database. Since the original objects are kept as back up copies, you can go look at them before deleting them.

So how do you get rid of these objects once you have confirmed they are no longer needed?

Well, you create a script. This script is based on a version that my co-worker Chris Henry created. I’ve added to it and generalized it.

Let’s break it down

I found that ER/Studio Data Architect uses UTC as the time for the timestamp on the temporary objects. (That drove me nuts until I figured it out!). By using the GetUTCDate() function you’ll be able to programmatically create the date to filter the SQL objects that you want to get rid of.

DECLARE @Date AS varchar(10)

SELECT
    @date = '%' + CONVERT(varchar(2), MONTH(GETUTCDATE())) 
				+ ( CASE 
						WHEN DAY(GETUTCDATE()) < 10 THEN '0'
                        ELSE ''
                        END ) 
				+ CONVERT(varchar(2), DAY(GETUTCDATE())) 
				+ CONVERT(varchar(4), YEAR(GETUTCDATE())) + '%'

 

I then use UNION ALL to join the different types of objects with the proper DROP syntax.

SELECT
    'ALTER TABLE [' + s.name + '].[' + p.name + '] DROP CONSTRAINT  [' + o.name + ']' AS ItemToDrop
	,o.type

FROM
    sys.objects AS o
	JOIN sys.objects AS p ON o.parent_object_id = p.object_id
	JOIN sys.schemas AS s ON p.schema_id = s.schema_id
WHERE
    o.name LIKE @date
    AND o.type = 'F'

UNION ALL

SELECT
    'Drop Trigger [' + s.name + '].[' + o.name + ']' AS ItemToDrop
	,o.type
FROM
    sys.objects AS o
	JOIN sys.objects AS p ON o.parent_object_id = p.object_id
	JOIN sys.schemas AS s ON p.schema_id = s.schema_id
WHERE
    o.name LIKE @date
    AND o.type = 'TR'

UNION ALL

SELECT
    'drop table [' + s.name + '].[' + o.name + ']' AS ItemToDrop
	,o.type
FROM
    sys.objects AS o
	JOIN sys.schemas AS s ON o.schema_id = s.schema_id
WHERE
    o.name LIKE @date
    AND o.type  ='U'

UNION ALL

SELECT
    'drop proc [' + s.name + '].[' + o.name + ']' AS ItemToDrop
	,o.type
FROM
    sys.objects AS o
	JOIN sys.schemas AS s ON o.schema_id = s.schema_id
WHERE
    o.name LIKE @date
    AND o.type  ='P'
ORDER BY
	o.type

 

After it executes, I copy the statements out of the results pane and execute them.

Bonus Tip

Since this is a script you’ll use again and again…and again. You’ll want to turn it into a template. Two of the many ways to do that are as follows.

  • SSMS comes with a Template Browser. You can save your script in the Template Browser for future use. Each you need it, simply double click on the script in the Template Browser and copy will be created for you. By adding the following code at the top of the script, you can use Ctrl+M to pick which database you want to use.
USE <DatabaseName, string,>
GO

 

  • If you are a SQL Prompt addict like me, then  you can add your script as a snippet. Instead of adding the code I just showed you, add the following code and a default database name will appear as a default when you use Ctrl+M.
USE <DatabaseName, string,$DBNAME$>
GO

You can download full script from here.

SQL Bacon Bits

SQL Tidbits: No.1– Outputting from ER/Studio Data Architect Directly to SQL Server Management Studio

 

%d bloggers like this: