How to create a new check constraint through a Script

[ea1020]

So I want to have EA automatically add a check constraint, for any column in a modelled database table that uses a particular data type; in my case, the (added) datatype "dbo.adtYesNo".

I made myself a nice script.  I then select all the database tables on the diagram, and run the script.  The script works great for processing all the tables, checking all the columns, and it can alter/update the check constraints that it finds.
The problem is that the script cannot create a new check constraint, if there is currently none.  And that is the biggest part of what I wanted to be automatically handled.  The following is the related code fragment; the comments for "PROBLEM" note the various properties in the EA.Method object that EA does not allow me to set.

dim newMethod as EA.Method
'This line works to create a new EA.Method object, but that object is useless
'because:
'  - FQStereotype is set to "", it is a read-only property, and that property must be set.
set newMethod = myElement.Methods.AddNew(requiredCheckConstraintName, "")

'This line works, but the resulting EA.Method object is useless because FQStereotype is "".
' ReturnType is also incorrectly set to "EAUML::check"; it needs to be "".
'set newMethod = myElement.Methods.AddNew(constraintName, "EAUML::check")

' For the new EA.Method object, set the various properties.
newMethod.Code = requiredCheckConstraintExpression
newMethod.Concurrency = "Sequential"
'newMethod.FQStereotype = "EAUML::check" - PROBLEM: this property must be set, but it is read-only
'newMethod.MethodGUID - need to set?
'newMethod.MethodID   - need to set?  - PROBLEM: this property likely needs be set, as the value is currently 0, but it is read-only
'newMethod.Name - this is already set; good
'newMethod.Notes - not needed
'newMethod.ObjectType = 24  'From EAConstants-VBScript:  otMethod = 24   - PROBLEM: this property must be set, but it is read-only
'newMethod.Parameters - - PROBLEM: this collection must have an object added, to list the affected table column, but it is documented as read-only in https://www.sparxsystems.com/enterprise_architect_user_guide/15.2/automation/method.html
'newMethod.ParentID = myElement.ElementID ' - PROBLEM: need to set the table ElementID into newMethod.ParentID, but it is read-only
newMethod.Pos = myElement.Methods.Count ' Pos is 0-based.  Use next value.
'newMethod.PostConditions - will be left empty
'newMethod.PreConditions  - will be left empty
newMethod.Stereotype   = "check"
newMethod.StereotypeEx = "check"
'newMethod.Style and StyleEx - will be left as empty string
'newMethod.TaggedValues - will be left empty
'newMethod.TypeInfoProperties - might not need to be set
newMethod.Visibility = "Public"

' Save the Method object
newMethod.Update()
myElement.Methods.Refresh()


dim retrievedMethod as EA.Method
retrievedMethod = myElement.Methods.GetByName(requiredCheckConstraintName)

dim newParameter as EA.Parameter
newParameter = retrievedMethod.Parameters.AddNew(attribute.Name, attribute.Type)

newParameter.ClassifierID = "0"
newParameter.Kind = "in"
'newParameter.Name - should already be set
'newParameter.Notes - not needed
newParameter.ObjectType = 25 ' From EAConstants-VBScript:  otParameter                = 25
newParameter.OperationID = retrievedMethod.MethodID
'newParameter.ParameterGUID - ?
newParameter.Position = 0  ' Pos is 0-based
'newParameter.Stereotype and Stereotype - ""
'newParameter.Style and StyleEx - ""
'newParameter.TaggedValues - will be left empty
newParameter.Type = attribute.Type
'newParameter.TypeInfoProperties - might not need to be set

newParameter.Update()
retrievedMethod.Parameters.Refresh()



Any ideas?
I had a look at Geert's framework for add-ons.  Geert has code there for check constraints; I don't know if his framework is only capable of reading existing check constraints, of if it can successfully create new ones also.

Thanks.

[Geert Bellekens]

A Few things:

- FQStereotype is more of a "get" method than an actual property. No need to set it. It will probably only return a valid result after saving
- MethodID and MethodGUID and ObjectType are automatically assigned by EA. You don't need to set these.
- ParentID is assinged because you add the new method in Element.Methods.AddNew

Normally you should use fully qualified stereotype  "EAUML::check" in the AddNew method like you do now, but if for some reason that doesn't work, you can leave the type parameter empty and do
newMethod.StereotypeEx = "EAUML::check" (make sure the stereotype is correct, including uppercase/lowercase)

In some cases you need to save an object (update()) before you can continue to add some other properties.
In most cases you don't need Refresh()

Yes, the addinframework is capable of creating new constraints. The magic happens in Constraint.SaveMe()
It is possible though that the stereotype handling needs updating, since EA is now a lot stricter with regards to fully qualified stereotypes. Back in the day you could use the stereotype "check" and EA would figure that you are actually talking about EAUML::check. I suspect that might not work correctly anymore in the current version of EA.

Geert

[ea1020]

Thank you Geert.  I'll implement your suggestions and see what happens.

[ea1020]

I have fixed code and a question about setting the Assigned Columns via the Parameters collection.

Fixed code:  With the suggestions from Geert, I successfully updated my code to create the check constraint.  Feel free to use this in your own script; it works except for issue noted in my question below.

dim newMethod as EA.Method
'This line works to create a new EA.Method object, but that object is useless
'because:
'  - FQStereotype is set to "", it is a read-only property, and that property must be set.
set newMethod = myElement.Methods.AddNew(requiredCheckConstraintName, "")

'This line works, but the resulting EA.Method object is useless because FQStereotype is "".
' ReturnType is also incorrectly set to "EAUML::check"; it needs to be "".
'set newMethod = myElement.Methods.AddNew(constraintName, "EAUML::check")

' For the new EA.Method object, set the various properties.
'ParentID - this is already set to the ElementID value of the parent table, due to the AddNew() function above

newMethod.Code = requiredCheckConstraintExpression
newMethod.Concurrency = "Sequential"
'newMethod.FQStereotype - this will be correctly set to "EAUML::check" when StereotypeEx is set to "check" below.
'newMethod.MethodGUID - this value will be set when the Update() function is executed below
'newMethod.MethodID   - this value will be set when the Update() function is executed below
'newMethod.Name - this is already be set, using value from AddNew() function above
'newMethod.Notes - not needed
'newMethod.ObjectType  - this value is correctly automatically being set 24  'From EAConstants-VBScript:  otMethod = 24
'newMethod.Parameters - PROBLEM: this collection must have an object added, to list the affected table column, but it is documented as read-only
'newMethod.ParentID =  - this value is correctly automatically being set to the table ElementID value
newMethod.Pos = myElement.Methods.Count ' Pos is 0-based.  Use next value.
'newMethod.PostConditions - will be left empty
'newMethod.PreConditions  - will be left empty
newMethod.Stereotype   = "check"
newMethod.StereotypeEx = "check"   ' or maybe newMethod.StereotypeEx = "EAUML::check"
'newMethod.Style and StyleEx - will be left as empty string
'newMethod.TaggedValues - will be left empty
'newMethod.TypeInfoProperties - 9 elements are automatically added to this collection when the Update() function is executed below
newMethod.Visibility = "Public"


dim newParameter as EA.Parameter
' attribute is the database table column object, from the containing "for each attribute in myElement.Attributes" for loop
' this command fails:  newParameter = newMethod.Parameters.AddNew(attribute.Name, attribute.Type)
' this command fails:  newParameter = newMethod.Parameters.AddNew(attribute.Name, "")
' this command fails:  newParameter = newMethod.Parameters.AddNew("", "")


' Save the Method object
newMethod.Update()
myElement.Methods.Refresh()




question about setting the Assigned Columns via the Parameters collection
The above code is sufficient to create a new check constraint. 
Unfortunately I cannot create an element for the Parameters collection in the EA.Method object; this is true both before and after executing Update() and Refresh() on the Methods collection. 
The Parameters collection stores the column that is in the Assigned Columns list in the Involved Columns tab on the Constraints/Indexes page on the Columns and Constraints dialog for the related modelled database table.
The check constraint, without the column in the Parameters collection, will still generate the correct DDL syntax for database generation, so this is not an insurmountable problem.  But I would prefer to have the model include this information.



' An attempt to use GetByName() will fail.
'dim retrievedMethod as EA.Method
'retrievedMethod = myElement.Methods.GetByName(requiredCheckConstraintName)

' Since GetByName() does not work, loop through the methods until the new check constraint is found
'dim method
for each method in myElement.Methods
   if method.FQStereotype = "EAUML::check" and method.Name = requiredCheckConstraintName then
      dim newParameter1 as EA.Parameter
      ' attribute is the database table column object, from the containing "for each attribute in myElement.Attributes" for loop
      ' this command fails:  newParameter1 = method.Parameters.AddNew(attribute.Name, attribute.Type)
      ' this command fails:  newParameter1 = method.Parameters.AddNew(attribute.Name, "")
      ' this command fails:  newParameter1 = method.Parameters.AddNew("", "")

      ' Do not try to set values for newParameter1; the AddNew() command above fails
      if false then
         'dim newParameter as EA.Parameter
         'Alias - ""
         newParameter1.ClassifierID = "0"
         'Default - ""
         'IsConst - False
         newParameter1.Kind = "in"
         'newParameter1.Name - should already be set
         'newParameter1.Notes - not needed
         newParameter1.ObjectType = 25 ' From EAConstants-VBScript:  otParameter                = 25
         newParameter1.OperationID = retrievedMethod.MethodID
         'newParameter1.ParameterGUID - ?
         newParameter1.Position = 0  ' Pos is 0-based
         'newParameter1.Stereotype and StereotypeEx - ""
         'newParameter1.Style and StyleEx - ""
         'newParameter1.TaggedValues - will be left empty
         newParameter1.Type = attribute.Type
         'newParameter1.TypeInfoProperties - might not need to be set; for existing, has 11 elements

         newParameter1.Update()
         'newMethod.Parameters.Refresh()
      end if
      
      exit for
   end if
next


Does anyone have suggestions for how to get EA to allow the creation of a new element for the Parameters collection?

[Geert Bellekens]

I think you need to save your method before trying to add a parameter.
There is usually no need to do a Refresh on any of the collections.

When you say "failed" what does that mean? Which error message are you getting?
I think you forgot the "Set" in

Code: [Select]

newParameter = newMethod.Parameters.AddNew(attribute.Name, attribute.Type)Needs to be

Code: [Select]

set newParameter = newMethod.Parameters.AddNew(attribute.Name, attribute.Type)
In VBScript syntax you have to use SET for all objects (and you are not allowed to use it on primitives) You did that correctly for set newMethod but you forgot for the parameter

Geert