Find Triggers that are NOT associated

[epizykloid]

Hi there,

I am very new to EA and currently working on some state machines. Because of co-working with other people on the same model it happened that we  have created triggers that are now ending up not used in the state machines. How can I identify them?
I know I can search if a trigger is used in a diagram, but the ones we use are not actually in the diagram, they are only associated to a transition.
I also know that the other way round it is possible to find triggers associated to a Transition.
But I would love to have a list of all triggers that are not associated to any Transition, so that I can delete them.
The search for Orphans in Diagram Searches doesn't help either (because they are not represented as an element in a diagram)
I am sure it must be possible with an SQL search query, but here I am lacking knowledge, and so far my research led only to simmilar search queries that did not bring me the wished result.

I am thankful for any hint or help.
Best regards,
epizykloid

[qwerty]

You need to write aSQL search for that. Maybe tomorrow I can have a closer look. Though more likely Geert has already one in his drawer.

q.

[epizykloid]

I thought so, I really would like try to do this on my own, are there any resources, how databases, elements etc are named and how I adress them?
But if someone has already sth similar ready I would be more than happy

[shimon]

Hi,
This past week I had the same need. I didn't find the solution yet, but started building a query that would help me to find the information.
I started writing a query, that would query all the tables in the DB that have any records. This way if I open an empty project and add some elements to it, I can easily find that tables that contain the information that I entered.
If you want to go this long route, fine.
I wrote the SQL for this specific case below.

Sincerely,
Shimon
P.S. Geert's navigator does not show this information for Triggers.

[Geert Bellekens]

I don't have anything in my drawer, and I haven't really worked with those triggers and state machines for a long while, but I might be able to give you some pointers.

- There is no "official" documentation of the database scheme, but you can reverse engineer the database in EA itself, and there is the Inside EA book by Thomas: https://leanpub.com/InsideEA
- There are a few usual suspects for how EA usually links stuff like this
  - a field in the t_connector (like pdata, styleex,...) that contains the id (or GUID) of the trigger
  - a tagged value on the connector (t_connectorTag) that contains the id (or GUID) of the trigger
  - a record in t_xref that contains the guid of the trigger in the description
- You can ususally figure out where EA stores stuff like this by setting up something like SQL profiler that captures all sql statements executed on the database. (make sure to start, save your connector, and stop the profiler, otherwise you have way too much noise)

Geert

[shimon]

Hi,
I agree with Geert in general. In this specific instance there is (almost) nothing in Thomas' book.

As I wrote in another post, EA sometimes does not delete (or clean up) all references to deleted objects.
If you replaced the Triggers, the following SQL will probably give you the desired results. If you deleted the transitions (or deleted the States) , it might give you partial (or FLASE) results.


select * from t_object where t_object.Object_Type = 'Trigger' and t_object.ea_guid not in (
select Cstr(Description) from t_xref where t_xref.Type ='connector property' and t_xref.Behavior = 'Trigger'
).


Cstr(Description) is for Access DB.  For SQL you do not need to CAST the Description column. I have no idea why this is necessary in Access.

If you don't know where to enter this SQL ( or do not know how to add the CLASSGUID and CLASSTYPE, that enables you to go straight to the object in the browser) I would agree with Geert that you should read Thomas' book. It will save you many hours of trial and error.


Sincerely,
Shimon

[qwerty]

Well, I'm really no SQL coder. So I gave up with join and where. Sorry. What I do in such cases is wrting a script that returns all t_object.object_id in an array and then run SQLs over  t_diagramobjects where diagram_id = <from the array> and check if the result is empty in which case I have an orphan. Takes me a couple of minutes vs. half an hour poking in obscure SQL.

q.

[epizykloid]

Thank you all for responses. I will definitely take a look into the recommended book and try your suggestions.

@qwerty that sounds like a way I could try too. Are you writing this in the search query window? Could you give me an example how such a script could look like?

[qwerty]

I'm using my Python framework

Code: [Select]

for (objId,) in rep.query("SELECT obj.object_id from t_object obj WHERE object_type='Trigger'"):
    dos =  rep.query("SELECT obj.object_id from t_diagramobjects obj WHERE object_id=" + objId)
    if len(dos) == 0:
       
        print(objId) # orphan
It should not be too hard to translate that into VB or Java. IIRC you could create some search window result, but I forgot any details. Additionally to the ObjId you could query more details like the name or the guid (I have a sql-builder query to show details for  guids)

Good luck, come back, if you have more questions.

q.

[epizykloid]

Thanks @qwerty


I am very surprised to find out that the asociated trigger of a connector is written into t_connector.PDATA1

So I wrote this SQL query and it returns all triggers that are not associated to any trigger:

Code: [Select]

SELECT * FROM t_object WHERE t_object.Object_Type = 'Trigger'
AND t_object.Name NOT IN
(SELECT *
FROM
(SELECT t_connector.PDATA1 FROM t_connector WHERE t_connector.PDATA1 IS NOT NULL))
I am surprised that it was that easy, possibly I did missunderstand something?  What do you think about this solution?

[qwerty]

Looks fine. The DB schema of EA isn't that logical one might hope for. Lots of legacy and sloppy base design. They never made any attempt to improve it. Rather they cramped it more with csv lists an t_xref. You might have a look into my Inside book where I tried to publish a few of these "miracles".

q.