Author Topic: How to report cascading deletes?  (Read 1372 times)

ChrisMW

  • EA User
  • **
  • Posts: 81
  • Karma: +2/-0
    • View Profile
How to report cascading deletes?
« on: May 15, 2018, 02:01:00 am »
Hi,

I have a database model that contains a couple of cascading deletes (as defined on the FK connection, Properties->Foreign key->On Delete with the value cascade. Now I want to generate a report and include this info, I've looked in some of the e-books that explain the database and automation interface, and the report writer isn't offering anything directly useful, so I can't seem to make it appear.

I search this forum as well, and wasn't able to find an answer.

Does anyone know how to report this setting?

Rgds Chris

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 9547
  • Karma: +275/-27
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: How to report cascading deletes?
« Reply #1 on: May 15, 2018, 02:45:11 am »
Isn't that stored in a tagged value?

Geert

ChrisMW

  • EA User
  • **
  • Posts: 81
  • Karma: +2/-0
    • View Profile
Re: How to report cascading deletes?
« Reply #2 on: May 15, 2018, 06:11:19 am »
Yes, I found the value in t_operationtag, twice. Once as property=property & value=Delete Cascade=1; and once as property=Delete & value=Cascade

Same elementid but different property id's. 1 I can find in t_objectproperties, the other I haven found yet. This is done by looking at the DB using PgAdmin, so Iḿ still not connecitng it to something I could add to a report template.....

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 9547
  • Karma: +275/-27
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: How to report cascading deletes?
« Reply #3 on: May 15, 2018, 02:00:53 pm »
t_operationtag corresponds to Operation>Tagged Value> in the templates.

Geert

ChrisMW

  • EA User
  • **
  • Posts: 81
  • Karma: +2/-0
    • View Profile
Re: How to report cascading deletes?
« Reply #4 on: May 16, 2018, 06:36:52 am »
Hmmm, in the diagram it is accessed by clicking on the association and there it is. No operation in sight. I was thinking the Database Foreign Key fragment was the right template to tweak but it seems that might be wrong. I need the table fragment and look in operations?

ChrisMW

  • EA User
  • **
  • Posts: 81
  • Karma: +2/-0
    • View Profile
Re: How to report cascading deletes?
« Reply #5 on: May 16, 2018, 06:56:00 am »
Can't see it there either. In the Foreign key template, I can add the section element->connector->constraint  but it doesn't allow me to insert a field that seems to make sense

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 9547
  • Karma: +275/-27
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: How to report cascading deletes?
« Reply #6 on: May 16, 2018, 01:53:45 pm »
Yes, a foreign key is represented in the model by both a relation as an operation.

The properties are stored on the operation and its tagged values.

Geert

ChrisMW

  • EA User
  • **
  • Posts: 81
  • Karma: +2/-0
    • View Profile
Re: How to report cascading deletes?
« Reply #7 on: May 16, 2018, 08:57:46 pm »
Yes, that worked. I added the method and tag sections on the table fragment.  I now get

(name)=(value)
Delete=Cascade
property=Delete Cascade=1;


And would like to have only one (the first looks more report friendly. I've forgotten how to do that, could I pick your mind one more time?

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 9547
  • Karma: +275/-27
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: How to report cascading deletes?
« Reply #8 on: May 16, 2018, 09:11:24 pm »
You can try to play with the other filters in your template properties.

If that doesn't work for the whole template you might be better off creating a fragment and setting the filter there.

Geert

ChrisMW

  • EA User
  • **
  • Posts: 81
  • Karma: +2/-0
    • View Profile
Re: How to report cascading deletes?
« Reply #9 on: May 16, 2018, 10:30:31 pm »
With a separate fragment I can filter the methods so that only those that have a OnDelete, OnCreate set are reported, but I am not able to ignore the 'property' tag and just report the Delete tag.. What I am not doing right?

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 9547
  • Karma: +275/-27
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: How to report cascading deletes?
« Reply #10 on: May 16, 2018, 11:04:04 pm »
I'm not sure.

At this point I often switch to SQL fragments.
That is often easier as you can precisely select each tagged value you need (and ignore the others) and output them into one dataset together with the rest of the FK details.


Geert

ChrisMW

  • EA User
  • **
  • Posts: 81
  • Karma: +2/-0
    • View Profile
Re: How to report cascading deletes?
« Reply #11 on: May 16, 2018, 11:31:21 pm »
Time to learn that then.... I've got some idea on the query, but wonder on the starting point. Right now, my fragment is called from a Foreign Key fragment, called from a Database Table fragment. It's called on the Element level, where I've added the method and method tag sections.

I'm guessing that I would need a query that returns the stuff I want (method name, tag name, tag value) and the where you select only those tag names that are 'Delete' or 'Create'. But for the element at large, how do I add that into the query?

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 9547
  • Karma: +275/-27
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: How to report cascading deletes?
« Reply #12 on: May 16, 2018, 11:47:31 pm »
The fragment always starts from the element.
The idea is to return all fields for all foreign keys in your table.

Here's a query I made to report on attributes.

Code: [Select]
select att.[Pos] AS Pos, att.[Name], att.Notes AS [Description-Formatted], att.LowerBound, att.UpperBound ,
CASE WHEN x.[Description] is null THEN 'no' ELSE 'yes' END AS IsID,
  isnull(v.[Value], 'TBD') AS Versioned,isnull(dc.VALUE,'TBD') as DataClassification, isnull(ts.[Value], 'TBD')  AS Timesliced, att.TYPE AS Format
from ((((t_attribute att
left outer join [t_attributetag] v on (v.[ElementID] = att.[ID]
                                and v.[Property] = 'Versioned'))
left outer join [t_attributetag] ts on (ts.[ElementID] = att.[ID]
                                and ts.[Property] = 'Timesliced'))
left outer join [t_attributetag] dc on (dc.[ElementID] = att.[ID]
                                and dc.[Property] = 'Atrias::Data Classification'))
left outer join t_xref x on (x.[Client] = att.[ea_guid]
                      and x.Type = 'attribute property'
   and x.[Description] like '%@PROP=@NAME=isID@ENDNAME;@TYPE=Boolean@ENDTYPE;@VALU=1@ENDVALU;%'))     
where att.Object_ID = #OBJECTID#
union all
select 0, 'N/A', 'This class has no specific attributes' AS [Description-Formatted], '', '',
'' AS IsID,
'' AS Versioned,'' as DataClassification, '' AS Timesliced, '' AS Format
where not exists
(select a.ID from t_attribute a where a.Object_ID = #OBJECTID#)
order by Pos

For operations that is pretty much similar.

Geert

ChrisMW

  • EA User
  • **
  • Posts: 81
  • Karma: +2/-0
    • View Profile
Re: How to report cascading deletes?
« Reply #13 on: May 17, 2018, 01:30:28 am »
Thanks a tonne!

My query was no where as robust as your example, nor as complicated. I used PGAdmin's query ability to find what I needed and then applied a few things I saw in your example to add it into the fragment. It works like a charm. Yet another way to tweak reports, this one seems quite useful. Fragments, Virtual documents I have used before, but this SQL stuff is quite easy and very effective for certain tricks (like reporting cascading delete's). In the end, as there are only two tags, I opted to exclude the tag with the value property, so the result should work fine with all the options you can set on the FK. No doubt it will be improved at some point, but at least now I can send the document including this particular bit of info.