Sparx Systems Forum

Enterprise Architect => Automation Interface, Add-Ins and Tools => Topic started by: tm200014 on December 01, 2010, 07:59:53 pm

Title: pre conditions in sql
Post by: tm200014 on December 01, 2010, 07:59:53 pm

hello,

i am trying to get some data out of my EA model with sql queries.

for operations you can define "pre conditions". I cant seem to find the SQL table in which these pre conditions are listed.

can anyone point me in the right direction?

thanks in advance!

Title: Re: pre conditions in sql
Post by: Geert Bellekens on December 02, 2010, 12:28:55 am

Hi,

This EA search query will return operations that contain the search text in the postconditions.

Code: [Select]

select o.ea_guid as CLASSGUID,'Operation' as CLASSTYPE,o.name as Name, class.name as 'Class Name' ,package.name as 'Package Name',package_p1.name as 'Package level -1',package_p2.name as 'Package level -2',package_p3.name as 'Package level -3'
from t_operation as o
join t_operationposts as p on (o.operationID = p.operationID)
join t_object as class on (o.object_id = class.object_id)
join t_package as package on (class.package_id = package.package_id)
left join t_package as package_p1 on (package_p1.package_id = package.parent_id)
left join t_package as package_p2 on (package_p2.package_id = package_p1.parent_id)
left join t_package as package_p3 on (package_p3.package_id = package_p2.parent_id)
where p.Notes like '%<Search Term>%'
Change the '%' by '*' and add brackets around the joins if you are using a local eap file.

Geert

Title: Re: pre conditions in sql
Post by: Geert Bellekens on December 02, 2010, 12:33:13 am

t_operationpres is of course the table for the pre-conditions.

Geert

Title: Re: pre conditions in sql
Post by: tm200014 on December 02, 2010, 01:40:34 am

ah!!!

thank you very much!

when i open the sql query editor in EA there is a list of all available tables. the t_operationpres is not in there. that tells me that this list is not complete.

is there a complete list of all available tables? i find the documentation from EA very poor on the sql part.

Title: Re: pre conditions in sql
Post by: Geert Bellekens on December 02, 2010, 01:44:44 am

Quote

i find the documentation from EA very poor on the sql part.

You Sir are nominated for "Understatement of the year" :o

No seriously, you can open any eap file with ms-access, or otherwise create your db of choice using the SQL creation scripts from the website.

Geert

Title: Re: pre conditions in sql
Post by: tm200014 on December 02, 2010, 02:04:08 am

Alright. Thanks very much! I got that to work...

Although there is another thing which has been giving me a headache the whole day already :)

I want to list all operations, that do not have a tagged value named 'define' in them. I use the following code:

Code: [Select]

SELECT  DISTINCT t_operation.ea_guid AS CLASSGUID,
             t_operation.Name AS 'Name'
FROM    (t_operation LEFT JOIN t_operationtag ON (t_operation.OperationID = t_operationtag.ElementID))
WHERE   t_operationtag.Property <> 'define' OR t_operationtag.Property IS NULL

This works fine and shows all operations which have either no tagged value or any other tagged value other than 'define'.

My Problem now is, that as soon as the operation has more than one tagged value and one of the tagged values is 'define', then the operation gets displayed anyway because the other tagged values dont match the criteria.

So basically i want to check if there is a tagged value named [ch712]define' and if yes, the whole operation is not supposed to show up in my output at all.

Any ideas how i could do this?

Thanks

Title: Re: pre conditions in sql
Post by: Geert Bellekens on December 02, 2010, 04:20:33 am

Try something like this:

Code: [Select]

SELECT  DISTINCT o.ea_guid AS CLASSGUID, o.Name AS 'Name'
FROM    t_operation o
WHERE   not exists (select 1 from t_operationtag t
                            WHERE t.Property = 'define'
                            AND t.ElementID = o.OperationID)
Geert