Retrieve Path for Elements

[floerio]

Hi!

Does anybody knows if there is a function to get the "path" for an element?

I have an object id and to that ID I want to know the tree, like "Model/packageA/packageC/[elementName]".

Does this exists? Or Do I have to iterate over the "parentId" element by myself (which would be quite quite time consuming)?

Cheers,
   Oliver

[Geert Bellekens]

You could do that, or like I did, write an sql query that retrieves the whole hierarchy from the database and run that using Repository.SQLQuery.
I've done so in an export utility that exports over 10000 elements and puts their information (including the qualified path) in an excel file.
The whole process, including writing to excel and saving the file takes no longer then 3 seconds. (and that is on a remote sql server repository).
Before I used the query I had written the same functionality using only the API objects. This took about 3 hours to finish, which is the reason why I went for the sql query option.

Geert

[floerio]

Hie Geert,

thanx for your answer. I was afraid I had to do something like this... So I have to dig into writing SQL Statements.. :-(

Is the data model somewhere explained in detail in the documentation? I have not seen anything useful so far.

Cheers
   Oliver

[Geert Bellekens]

No, there is no documentation regarding the database.
Even worse, the database is far from normalized and contains a lot of undocumented "magic string" columns. (something like "property=xyz;otherproperty=123" )
The C# code I used to get the xml formatted details for all elements in a package (and all of its subpackages) looks like this:

Code: [Select]

/// <summary>
        /// returns an xml document with a flat list of the owned elements modified after the given date
        /// </summary>
        /// <param name="modifiedAfter">the date after wich the elements should be modified</param>
        /// <returns>the owned elements in xml format</returns>
        public XmlDocument exportOwnedElements(DateTime modifiedAfter)
        {
            string sqlDateModified = modifiedAfter.ToString("yyyy-MM-dd");
            string sqlQuery =
                " SELECT o.Object_ID as ID,o.Object_Type as Type,isNull(o.Name,'') as Name,ISNULL(t.value,0) as ImportanceLevel,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_objectproperties t on t.Object_ID = o.Object_ID " +
                " 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 (t.Property = 'ImportanceLevel' or t.Property is null)" +
                " and o.ModifiedDate > '" + sqlDateModified + "' " +
                " and (p.package_ID = " + this.getPackageID() + "  " +
                    " or p2.package_ID = " + this.getPackageID() + "  " +
                    " or p3.package_ID = " + this.getPackageID() + "  " +
                    " or p4.package_ID = " + this.getPackageID() + "  " +
                    " or p5.package_ID = " + this.getPackageID() + "  " +
                    " or p6.package_ID = " + this.getPackageID() + "  " +
                    " or p7.package_ID = " + this.getPackageID() + "  " +
                    " or p8.package_ID = " + this.getPackageID() + "  " +
                    " or p9.package_ID = " + this.getPackageID() + " ) " +
                " order by QualifiedName";
            return ((EAModel)this.model).SQLQuery(sqlQuery);

        }
I hope this helps.

Geert

[floerio]

Thx!

I will try to write something based on that snippet