How to report cascading deletes?

[ChrisMW]

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]

Isn't that stored in a tagged value?

Geert

[ChrisMW]

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]

t_operationtag corresponds to Operation>Tagged Value> in the templates.

Geert

[ChrisMW]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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.