Updating a foreign key programmatically nulls the reference? (C#)

[Necrowizard]

Hi there,

I'm trying to programmatically rename a bunch of things. Renaming Tables and columns works perfectly, however, when I try to rename a foreign key, it will lose the reference to the table.
Here's the script it generates when I havent changed anything programmatically:

Code: [Select]

/* ---------------------------------------------------- */
/*  Generated by Enterprise Architect Version 13.5 */
/*  Created On : 30-May-2018 3:09:57 PM */
/*  DBMS       : MySql */
/* ---------------------------------------------------- */

SET FOREIGN_KEY_CHECKS=0
;

/* Drop Tables */

DROP TABLE IF EXISTS `ChildTable` CASCADE
;

DROP TABLE IF EXISTS `ParentTable` CASCADE
;

/* Create Tables */

CREATE TABLE `ChildTable`
(
`Id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`ParentFK_Id` INT NOT NULL,
CONSTRAINT `PK_ChildTable_FFFFFFFFFFFFFFFF` PRIMARY KEY (`Id` ASC)
)

;

CREATE TABLE `ParentTable`
(
`Id` INT NOT NULL,
CONSTRAINT `PK_ParentTable` PRIMARY KEY (`Id` ASC)
)

;

/* Create Primary Keys, Indexes, Uniques, Checks */

ALTER TABLE `ChildTable`
 ADD INDEX `IXFK_WorseNamedIndex` (`ParentFK_Id` ASC)
;

/* Create Foreign Key Constraints */

ALTER TABLE `ChildTable`
 ADD CONSTRAINT `FK_PoorlyNamedKey`
FOREIGN KEY (`ParentFK_Id`) REFERENCES `ParentTable` (`Id`) ON DELETE Restrict ON UPDATE Restrict
;

SET FOREIGN_KEY_CHECKS=1
;

Then when I do update the foreign key, this is the result:

Code: [Select]

/* ---------------------------------------------------- */
/*  Generated by Enterprise Architect Version 13.5 */
/*  Created On : 30-May-2018 3:35:19 PM */
/*  DBMS       : MySql */
/* ---------------------------------------------------- */

SET FOREIGN_KEY_CHECKS=0
;

/* Drop Tables */

DROP TABLE IF EXISTS `ChildTable` CASCADE
;

DROP TABLE IF EXISTS `ParentTable` CASCADE
;

/* Create Tables */

CREATE TABLE `ChildTable`
(
`Id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`ParentFK_Id` INT NOT NULL,
CONSTRAINT `PK_ChildTable_FFFFFFFFFFFFFFFF` PRIMARY KEY (`Id` ASC)
)

;

CREATE TABLE `ParentTable`
(
`Id` INT NOT NULL,
CONSTRAINT `PK_ParentTable` PRIMARY KEY (`Id` ASC)
)

;

/* Create Primary Keys, Indexes, Uniques, Checks */

ALTER TABLE `ChildTable`
 ADD INDEX `IXFK_WorseNamedIndex` (`ParentFK_Id` ASC)
;

/* Create Foreign Key Constraints */

ALTER TABLE `ChildTable`
 ADD CONSTRAINT `FK_Programmatically_Renamed`
FOREIGN KEY (`ParentFK_Id`) REFERENCES  () ON DELETE Restrict ON UPDATE Restrict
;

SET FOREIGN_KEY_CHECKS=1
;


specifically this line:

Code: [Select]

FOREIGN KEY (`ParentFK_Id`) REFERENCES  () ON DELETE Restrict ON UPDATE Restrict`
My C# code to update the foreign keys is roughly this:

Code: [Select]

            Repository repo = RepositoryContainer.Repository;

            foreach (Package package in RepositoryContainer.Repository.Models)
            {
                foreach (Package innerPackage in package.Packages)
                {
                    foreach (Element element in innerPackage.Elements)
                    {
                        foreach (Method method in element.Methods)
                        {
                            if (method.Stereotype == "FK")
                            {
                                if (method.Name == "FK_PoorlyNamedKey")
                                {
                                    method.Name = "FK_Programmatically_Renamed";
                                    method.Update();
                                }
                            }
                        }
                    }
                }
            }

In the model it looks updated, however when I export my script, the reference is gone.

I don't think I can attach files here, so I've updated a valid sample and a broken sample to my dropbox, which you can see here: and it should export the sql from above.
https://www.dropbox.com/s/6n73fvzc33840z5/Samples.zip?dl=1

Any help on how to do this would be appreciated.

[Geert Bellekens]

A FK in EA is stored as a combination of an operation and an association.
If you want to update one you should also update the other.

The association stores the name of the FK in two places:
- the role at the end of the table that owns the FK
- the styleEx field.

Here's the relevant part of my C# code on Github that saves a FK in EA:

Code: [Select]

if (this._wrappedAssociation != null)
{
if (((Table)this.foreignTable).wrappedClass != null)
{
this._wrappedAssociation.target = this._foreignTable.wrappedClass;
this.wrappedAssociation.sourceEnd.name = this.name;
this.setLogicalName(true);
if (this._foreignTable.primaryKey != null) this.wrappedAssociation.targetEnd.name = this._foreignTable.primaryKey.name;
this.wrappedAssociation.sourceEnd.EAMultiplicity = new Multiplicity("0..*");
if (this.logicalAssociation != null)
{
//get the correct end
var correspondingEnd = this.logicalAssociation.memberEnds.FirstOrDefault(x => this.foreignTable.logicalElements.Contains(x.type));
this.wrappedAssociation.targetEnd.EAMultiplicity = correspondingEnd != null ? (Multiplicity)correspondingEnd.multiplicity : new Multiplicity("1..1");
}
//set FKINFO in StyleEx of association
this._wrappedAssociation.wrappedConnector.StyleEx = "FKINFO=SRC="+this.name+":DST="+this.wrappedAssociation.targetEnd.name+":;";
this.wrappedAssociation.save();
}
else
{
Logger.logError(string.Format("foreign table {0} has not been saved yet",_foreignTable.name));
}
}
Geert

[Necrowizard]

Thanks for your quick response.

I'm trying to compile your github repo, but I'm getting about 2000 errors.
I guess mainly because of this.

Code: [Select]

4019: The imported project "C:\Program Files\dotnet\sdk\2.1.101\Microsoft\WiX\v3.x\Wix.targets" was not found. Confirm that the path in the <Import> declaration is correct, and that the file exists on disk.

I'm still trying to get it to compile by installing a bunch of things (like the WiX framework) and updating everything, but that might take a while

Anyway, I tried to follow your code, and I still have no clue what to do...

Would this be possible with a simple Interlop.EA call? Because none of those things even exist there


The operations in there are called methods, and there's no mention of associations, or anything that seems similar

Edit: btw, I just started off with the sample from the "C:\Program Files (x86)\Sparx Systems\EA\Code Samples" folder

[Geert Bellekens]

Yeah, sorry, I might have left the repository in a broken state as I forgot to check-in the changes in the UML tooling Framework repository.
Should now be OK again.

Wix is only needed for the installers, so if you unload the setup projects you can compile the solution without having wix installed.

To get the whole thing compiling you will need to download the projects it depends on as well.
A bit more info can be found here

But anyways, trying to understand the whole framework will probably take a while and might not be needed for the task ahead.

- Yes you can do all that with the EA API directly as well.
- Yes, operations are (wrongly) called methods in the API
- You can get the associations by iterating the EA.Element.Connectors collection. (there might be other types of connectors there as well)

Geert

[Necrowizard]

Hey, Geert,

Ok thanks. After a while I did get the EAAddinFramework project to compile, after removing most of the add-in related things from the projects.

I mentioned just using Interlop.EA, because your library seemed very big, with all kinds of dependencies, some related to UML diagrams, some TFS work ticket stuff, some related to Addin UI stuff. - No offense to your libraries though, from the looks of it you can a lot of things with it.

My initial plan was just some simple validations are refactoring/renaming that won't become an addin, but just a winform or console instead.
(for example, some validations:
- name all foreign keys $"FK_{child.column}_{parent.column}
- name all primary keys $"PK_{parent.column}
- this seems like a EA bug but: When I have a unsigned int with a relation that I change to a char, the char(n) becomes an unsigned char in my script - not valid sql)

Anyway - the whole wrapping framework you've made seems pretty useful. I've been busy trying to debug against these COM calls the whole day, and it's going terrible... If I could load the whole model in memory first or something, it'd make my life a lot easier.

I'm home now, tomorrow at work I'll continue trying.

Is the assumption correct:
I can just include the EAAddinFramework project, create a class that inherits from EAAddinBase, and then us that on a EA.Repository, and use it for parsing and renaming purposes?

Or - probably a better question, any tutorial or guide on how to use your libraries (as processing libs, not an actual add-in)

Thanks a lot for the help so far.

[Geert Bellekens]

Hi,

The link I sent you was the most relevant article I wrote about using the library.

In the https://github.com/GeertBellekens/Enterprise-Architect-Toolpack repository you'll find all of my actual add-ins that use the library.
More articles about writing EA add-ins here: https://bellekens.com/writing-ea-add-ins/

The add-in that uses the database classes is the EADatabaseTransformer. It's goal is to transform a logical datamodel into a technical database model.

The simplest example is the MyAddin but that only uses the EAAddinBase, not the actual wrappers library.

Basically what you need to do is use the EA.Repository to create a Model object

Code: [Select]

using TSF_EA = TSF.UmlToolingFramework.Wrappers.EA;
--- snip ---
        public override void EA_FileOpen(EA.Repository repository)
        {
            // initialize the model
            this.model = new TSF_EA.Model(repository);
            // indicate that we are now fully loaded
            this.fullyLoaded = true;
        }From there on out you can use Model and the wrapper classes. All the EA nastiness is now neatly hidden.

If you don't need an add-in, then you can connect to the running instance using the parameterless constructor of Model

Code: [Select]

model = new TSF_EA.Model();
Few examples of standalone programs are:
- MagicdrawMigrator
- EA Validator App
- EAAddinTester

I never really intended to write a library, its rather the result of me writing add-ins. So the library only contains those things I needed in my add-ins.
It has organically grown, over more than 10 years, so there are definitely parts that may be improved or should be refactored.

If I had a task like yours, renaming some of the FK's and PK's I probably would just quickly write a script and not bother with a compiled C# tool.

Most of the scripts I wrote can be found here: https://github.com/GeertBellekens/Enterprise-Architect-VBScript-Library

This example (I think it was an example provided by Sparx) should get you going as well.

Geert


The