Sparx Systems Forum
Enterprise Architect => Automation Interface, Add-Ins and Tools => Topic started by: MatthiasVDE on July 20, 2016, 11:34:54 pm
-
I have a data modeling diagram with several tables and much much much attributes.
All the attributes (names, types, lenght, ...) are available in a csv file.
Can I import them to a specific table directly from the csv file?
-
You can adapt the CSV importer script from Sparx. If you have the database concrete you can also use the DB importer to get it into EA.
q.
-
Can I do it with this function?
Public Function addOrUpdateAttribute(parentClass As EA.element, name As String, stereotype As String, description As String, attrType As String) As EA.Attribute
Dim myAttribute As EA.Attribute
'try to find existing attribute with the given name
Set myAttribute = getAttributeByName(parentClass, name)
If myAttribute Is Nothing Then
'no existing attribute, create new
Set myAttribute = parentClass.attributes.AddNew(name, "Attribute")
End If
'set properties
myAttribute.stereotype = stereotype
myAttribute.Notes = description
myAttribute.Type = attrType
'save attribute
myAttribute.Update
'refresh attributes collection
parentClass.attributes.Refresh
'return attribute
Set addOrUpdateAttribute = myAttribute
End Function
-
Yes. You need to read the csv (nobody cares about the RFC and just implements what he thinks might be appropriate; so look into the csv to find which format has been used). Then you find the class where the attributes shall be added and you just add them. You might want to add the type of the attribute (just use strings for that to go the easy way). N.B.: The refresh call is just superfluous.
q.
-
Can I do it with this function?
Public Function addOrUpdateAttribute(parentClass As EA.element, name As String, stereotype As String, description As String, attrType As String) As EA.Attribute
Dim myAttribute As EA.Attribute
'try to find existing attribute with the given name
Set myAttribute = getAttributeByName(parentClass, name)
If myAttribute Is Nothing Then
'no existing attribute, create new
Set myAttribute = parentClass.attributes.AddNew(name, "Attribute")
End If
'set properties
myAttribute.stereotype = stereotype
myAttribute.Notes = description
myAttribute.Type = attrType
'save attribute
myAttribute.Update
'refresh attributes collection
parentClass.attributes.Refresh
'return attribute
Set addOrUpdateAttribute = myAttribute
End Function
Yes, that is the function I wrote in my VBA excel importer (https://bellekens.com/2015/10/24/simple-vba-excel-to-ea-importer-v4/)in order to import attributes :)
Geert
-
It works, but there's stil a problem.
I use the importer to add attributes to columns, but I can't add or update the following fields:
- Length
- PK
- Not Null
- Scale
- Initial Value
-
Create an almost empty DB with just the table/attributes you want to look at. Then inspect what changes in the DB when setting the desired attributes. That will give you a hint where to look further.
q.
-
I checked all the fields in the database and this is the result:
Public Function addOrUpdateAttribute(parentClass As EA.element, name As String, stereotype As String, description As String, attrType As String, attrLength As String, default As String, allowduplicates As Boolean, isordered As Boolean) As EA.Attribute
Dim myAttribute As EA.Attribute
'try to find existing attribute with the given name
Set myAttribute = getAttributeByName(parentClass, name)
If myAttribute Is Nothing Then
'no existing attribute, create new
Set myAttribute = parentClass.attributes.AddNew(name, "Attribute")
End If
'set properties
myAttribute.stereotype = stereotype
myAttribute.Notes = description
myAttribute.Type = attrType
myAttribute.StyleEx = "volatile=0;"
'varchar -> length, number -> precision
If attrType = "VARCHAR2" Then
myAttribute.Length = attrLength
myAttribute.Precision = "0"
End If
If attrType = "NUMBER" Then
myAttribute.Precision = attrLength
myAttribute.Length = "0"
End If
'set initial value (default)
myAttribute.default = default
'set not null (allowduplicates)
myAttribute.allowduplicates = notnull
'set PK (isordered)
myAttribute.isordered = pk
'save attribute
myAttribute.Update
'refresh attributes collection
parentClass.attributes.Refresh
'return attribute
Set addOrUpdateAttribute = myAttribute
End Function
But it doesn't work, the script only creates the table with attributes of a specific type.
No length, pk, not null, ....
I tested it over and over again...
-
I think PK and stuff like that is a bit more complicated.
But I don't know by heart where exactly EA stores this information.
Will need to figure it out in a couple of weeks though as I have a database transformation project coming up.
Geert
-
This little snippet
my $col = $e->Attributes->AddNew("col", "");
$col->{Length} = 123;
$col->{Type} = "VARCHAR";
$col->{Stereotype} = "column";
$col->Update()
create a column col:VARCHAR(123) without any issue.
Adding $col->{AllowDuplicates} = "1";sets the Null attribute.
Further setting Precision and Scale for a DECIMAL sets what one would expect.
Setting the FK seems to be a bit more tricky. Still struggling as the result is currently not consistent.
q.
P.S. It turned out that the "only" issue with FK was that the diagram did not update correctly. Close/reopen solved it. Also the first time showing the FK was somehow "strange" (EAUI, I guess). So here's the code snippet:
my $col = $e->Attributes->AddNew("col1", "");
$col->{Length} = 123;
$col->{Type} = "VARCHAR";
$col->{Stereotype} = "column";
$col->{AllowDuplicates} = "1";
$col->{IsOrdered} = "1";
$col->Update();
my $oper = $e->Methods->AddNew("PK_Table1", "");
$oper->{Stereotype} = "PK";
$oper->Update();
my $opp = $oper->Parameters->AddNew("col1", "VARCHAR");
$opp->Update();
Creates a col1:VARCHAR(123) with non-null and PK.
-
If I run this code, the values are changed in the database, but in Enterprise Architect I don't see anything
If attrType = "VARCHAR2" Then
myAttribute.Length = attrLength
myAttribute.Precision = "0"
End If
If attrType = "NUMBER" Then
myAttribute.Precision = attrLength
myAttribute.Length = "0"
End If
-
I assume the Update follows since you say the DB is changed. Close/re-open the diagram to make the changes visible.
q.
-
I assume the Update follows since you say the DB is changed. Close/re-open the diagram to make the changes visible.
q.
Did it, but no results.
-
Creating Foreign Keys via automation is a bit more complicated. Here is some VBScript code I wrote a while ago which should do the job, or at least provide some further guidance. Hope that helps.
'
' Create a foreign key relationship between two database columns (attributes)
'
' @param[in] fkField (EA.Attribute)
' @param[in] pkField (EA.Attribute)
'
sub DefineForeignKey(fkField, pkField)
dim pkOpName, fkOpName
dim fkTable as EA.Element
dim keyTable as EA.Element
dim fkConnector as EA.Connector
dim fkOperation as EA.Method
dim op as EA.Method
dim param as EA.Parameter
set fkTable = Repository.GetElementByID(fkField.ParentID)
set keyTable = Repository.GetElementByID(pkField.ParentID)
' get target Primary Key name
for each op in keyTable.Methods
if op.Stereotype = "PK" Then
for each param in op.Parameters
if param.Name = pkField.Name then
pkOpName = op.Name
exit for
end if
next
end if
next
' define Foreign Key Name
fkOpName = "FK_" & fkTable.Name & "_" & keyTable.Name
' define connector
Set fkConnector = fkTable.Connectors.AddNew("", "Association")
fkConnector.SupplierID = pkField.ParentID
fkConnector.StyleEx = "FKINFO=SRC=" & fkOpName & ":DST=" & pkOpName & ":;"
fkConnector.StereotypeEx = "EAUML::FK"
fkConnector.ClientEnd.Role = fkOpName
fkConnector.ClientEnd.Cardinality = "0..*"
fkConnector.SupplierEnd.Role = pkOpName
fkConnector.SupplierEnd.Cardinality = "1"
fkConnector.Update
' define fk operation
set fkOperation = fkTable.Methods.AddNew(fkOpName, "")
fkOperation.StereotypeEx = "EAUML::FK"
fkOperation.Update
set param = fkOperation.Parameters.AddNew(fkField.Name, fkField.Type)
param.Update
'set "On Delete" and "On Update" (optional)
SetMethodTag fkOperation, "Delete", "Cascade"
SetMethodTag fkOperation, "Update", "Set Null"
SetMethodTag fkOperation, "property", "Delete Cascade=1;Update Set Null=1;"
' update attribute details
fkField.IsCollection = true
fkField.Update
end sub
function SetMethodTag(theMethod, tagName, tagValue)
dim tag as EA.MethodTag
set tag = theMethod.TaggedValues.GetByName(tagName)
if tag is nothing then
set tag = theMethod.TaggedValues.AddNew(tagName, "")
end if
tag.Value = tagValue
tag.Update
set SetMethodTag = tag
end function
-
Hi,
Don't know if this will also help, but I have also been playing around with Geert's importer (love your work,mate!).
I had a problem setting the attribute.scale property. After some googling, I stumbled across the answer. Scale is evidently a reserved word in VBA (sprung - I am not a developer by trade ;)) - although it does not appear in the list of reserved words on the msdn web site - go figure. Then I learnt that to use a VBA reserved word in a script, it must be enclosed in square brackets []
So the syntax looks like
myAttribute.[Scale] = attScale
NB - I believe this is only relevent to Numeric/Decimal data types
HTH
Cheers Peter