Import attributes from csv file to datatable

[MatthiasVDE]

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?

[qwerty]

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.

[MatthiasVDE]

Can I do it with this function?

Code: [Select]

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

[qwerty]

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.

[Geert Bellekens]

Can I do it with this function?

Code: [Select]

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 in order to import attributes

Geert

[MatthiasVDE]

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

[qwerty]

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.

[MatthiasVDE]

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

[Geert Bellekens]

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

[qwerty]

This little snippet

Code: [Select]

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

Code: [Select]

$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:

Code: [Select]

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.

[MatthiasVDE]

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

[qwerty]

I assume the Update follows since you say the DB is changed. Close/re-open the diagram to make the changes visible.

q.

[MatthiasVDE]

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.

[Aaron B]

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.

Code: [Select]

'
' 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

[peterm833]

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