SQL query :: Returns Node Path Elements

[atallec]

Hi,

I am looking for an SQL query that returns the Node Path for all element defined in a model.

This Node Path is the one returned by the "Right Click / Copy Reference / Copy Node Path to Clipboard" feature from the Project Browser View.

Thanks !
Alexandre Tallec

[Geert Bellekens]

Alexandre,

In theory you need a recursive query for that, but I tried that once, and it didn't work with EA.
The next best thing is to go as deep as you need to.
That's what I did when writing an export routine.
Here's a query written for SQL Server that might give you a head start:

Code: [Select]

SELECT o.Object_ID as ID,o.Object_Type as Type,isNull(o.Name,'') as Name,isnull(o.Author,'') as Author,o.CreatedDate as Created,o.ModifiedDate as Modified,isnull(o.Note,'') as Documentation,isnull(o.Package_ID,'') as ParentID,isnull(p.name,'') as ParentName
 ,isnull(p9.Name +'.','') + isnull(p8.Name+'.','')+ isnull(p7.Name+'.','')+ isnull(p6.Name+'.','')  +
 + isnull(p5.Name+'.','')+ isnull(p4.Name+'.','')+ isnull(p3.Name+'.','')+ isnull(p2.Name+'.','')+ isnull(p.Name,'') as QualifiedName  
 FROM (((((((((t_object as o  
 left join t_package p on o.Package_ID = p.Package_ID )
 left join t_package p2 on p.Parent_ID = p2.Package_ID )
 left join t_package p3 on p2.Parent_ID = p3.Package_ID )
 left join t_package p4 on p3.Parent_ID = p4.Package_ID )
 left join t_package p5 on p4.Parent_ID = p5.Package_ID )
 left join t_package p6 on p5.Parent_ID = p6.Package_ID )
 left join t_package p7 on p6.Parent_ID = p7.Package_ID )
 left join t_package p8 on p7.Parent_ID = p8.Package_ID )
 left join t_package p9 on p8.Parent_ID = p9.Package_ID )
where o.name like '%name%'
 order by QualifiedName;If you are using a .eap file you might need to tweak the syntax a bit to match MS Access SQL syntax.

Geert

[atallec]

Hi Geert,

Thank you for your answer.

There a great post on reccursive query with postgresql.
http://old.storytotell.org/blog/2009/08/11/postgresql84-recursive-queries.html

The following sequence works fine, but I should admit it's a bit complex !

-- All packages are defined in the t_object table except the root node of the model
select P.name from t_package P where P.name not in
(select O.name from t_object O where O.object_type='Package')

--[1]
CREATE TABLE t_nodepath_tmp (ea_guid VARCHAR,name VARCHAR, parent_ea_guid VARCHAR);

--[2]                          
INSERT INTO t_nodepath_tmp  (
-- child is an element, parent is an object
select C.ea_guid as "id", 'E::' || C.name as "name", P.ea_guid as "parent_id"
from t_object C, t_object P
where C.parentid=P.object_id
union
-- child is an element, parent is a package
select C.ea_guid as "id", 'E::' || C.name as "name", P.ea_guid as "parent_id"
from t_object C, t_package P
where C.package_id=P.package_id
and C.object_type != 'Package'
and C.parentid='0'
union
-- child is a package, parent is a package
select C.ea_guid as "id", 'P::' || C.name as "name", P.ea_guid as "parent_id"
from t_object C, t_package P
where C.package_id=P.package_id
and C.object_type='Package'
and C.parentid='0'
union
-- child is the root node of the model, no parent
select C.ea_guid as "id", 'P::' || C.name as "name", '0' as "parent_id"
from t_package C
where C.parent_id='0'
union
-- child is a diagram, parent is an object
select C.ea_guid as "id", 'D::' || C.name as "name", P.ea_guid as "parent_id"
from t_diagram C, t_object P
where C.parentid=P.object_id
union
-- child is a diagram, parent is an object
select C.ea_guid as "id", 'D::'|| C.name as "name", P.ea_guid as "parent_id"
from t_diagram C, t_package P
where C.package_id=P.package_id
and C.parentid='0'
order by name)

-- [3]

CREATE TABLE t_nodepath (id SERIAL PRIMARY KEY, ea_guid VARCHAR,name VARCHAR, parent_ea_guid VARCHAR);

-- [4]
INSERT INTO t_nodepath (ea_guid, name, parent_ea_guid) select ea_guid, name, parent_ea_guid from t_nodepath_tmp

-- [5]

INSERT INTO fs (id, name, parent_id)
SELECT c.id as 'id', c.name as 'name', p.id as 'parent_id'
FROM t_nodepath c, t_nodepath p
where c.parent_ea_guid=P.ea_guid

INSERT INTO fs (id, name, parent_id)
SELECT c.id as "id", c.name as "name", NULL as "parent_id"
FROM t_nodepath c
WHERE c.parent_ea_guid is NULL

-- [6]

WITH RECURSIVE path(name, path, parent, id, parent_id) AS (
          SELECT name, '/', NULL, id, parent_id FROM fs WHERE id = (SELECT id from fs where parent_id is NULL)
          UNION
          SELECT
            fs.name,
            parentpath.path ||
              CASE parentpath.path
                WHEN '/' THEN ''
                ELSE '/'
              END || fs.name,
            parentpath.path, fs.id, fs.parent_id
          FROM fs, path as parentpath
          WHERE fs.parent_id = parentpath.id)
        SELECT * FROM path;

[Geert Bellekens]

There is a similar way to do recursive queries using SQL Server (Common  Table Expressions) and although they work directly on the database, they won't when you try to use them as SQL Search.

Geert