Sparx Systems Forum
Enterprise Architect => Automation Interface, Add-Ins and Tools => Topic started by: Eamonn John Casey on January 05, 2017, 10:16:03 pm
-
Hi!
I am importing stuff into EA and I am putting the primary key from the Source system into the TaggedValues Collection. Has anyone implemented a way to find the element again using the TaggedValues? Maybe some sort of SQL Query or something.
Any help would be appreaciated,
Eamonn J.
-
Here's an example of an SQL search that searches elements for a certain value in the tagged value with the name "CR"
SELECT o.ea_guid AS CLASSGUID, o.Object_Type AS CLASSTYPE,
re.Name AS Release, cr.StereoType AS ChangeType, cr.Name AS ChangeRequest, o.name AS ChangedItem,
substring(op.Notes, charindex('date=',op.[Notes]) + len('date='),10) as ChangeDate,
substring(op.Notes,charindex('user=',op.[Notes]) + len('user='),charindex(';date=', op.[Notes]) - (charindex('user=',op.[Notes]) + len('user='))) as UserName,
substring(op.Notes,charindex('comments=',op.[Notes]) + len('comments='),500) as ChangeComments,
package.name AS PackageName, package_p1.name AS Package_level1, package_p2.name AS Package_level2, package_p3.name AS Package_level3
FROM ((((((((t_object o
INNER JOIN t_objectproperties op ON o.Object_ID = op.Object_ID)
INNER JOIN t_object cr ON cr.ea_guid = op.value)
LEFT JOIN t_connector crre ON crre.Start_Object_ID = cr.Object_Id)
LEFT JOIN t_object re ON (re.Object_Id = crre.End_Object_Id AND re.StereoType = 'Release'))
INNER JOIN t_package package ON o.package_id = package.package_id)
LEFT JOIN t_package package_p1 ON package_p1.package_id = package.parent_id)
LEFT JOIN t_package package_p2 ON package_p2.package_id = package_p1.parent_id)
LEFT JOIN t_package package_p3 ON package_p3.package_id = package_p2.parent_id)
WHERE op.Property = 'CR'
and cr.Name like '#WC#<Search Term>#WC#'
AND o.Package_ID IN (#Branch#)
UNION
SELECT a.ea_guid AS CLASSGUID, 'Attribute' AS CLASSTYPE,
re.Name AS Release, cr.StereoType AS ChangeType, cr.Name AS ChangeRequest, o.name +'.'+ a.Name AS ChangedItem,
substring(atv.Notes, charindex('date=',atv.[Notes]) + len('date='),10) as ChangeDate,
substring(atv.Notes,charindex('user=',atv.[Notes]) + len('user='),charindex(';date=', atv.[Notes]) - (charindex('user=',atv.[Notes]) + len('user='))) as UserName,
substring(atv.Notes,charindex('comments=',atv.[Notes]) + len('comments='),500) as ChangeComments,
package.name AS PackageName, package_p1.name AS Package_level1, package_p2.name AS Package_level2, package_p3.name AS Package_level3
FROM (((((((((t_attribute a
INNER JOIN t_attributetag atv ON a.ID = atv.ElementID)
INNER JOIN t_object cr ON cr.ea_guid = atv.value)
LEFT JOIN t_connector crre ON crre.Start_Object_ID = cr.Object_Id)
LEFT JOIN t_object re ON (re.Object_Id = crre.End_Object_Id AND re.StereoType = 'Release'))
INNER JOIN t_object o ON a.Object_ID = o.Object_ID)
INNER JOIN t_package package ON o.package_id = package.package_id)
LEFT JOIN t_package package_p1 ON package_p1.package_id = package.parent_id)
LEFT JOIN t_package package_p2 ON package_p2.package_id = package_p1.parent_id)
LEFT JOIN t_package package_p3 ON package_p3.package_id = package_p2.parent_id)
WHERE atv.Property = 'CR'
and cr.Name like '#WC#<Search Term>#WC#'
AND o.Package_ID IN (#Branch#)
ORDER BY Release, ChangeType, ChangeRequest, ChangedItemThis should get you started
Geert
-
I come up with a lite version:
select t_object.ea_guid As CLASSGUID, t_object.Object_type As CLASSTYPE, t_object.* from t_object inner join t_objectproperties on t_object.Object_ID = t_objectproperties.Object_ID where t_objectproperties.Value = "search vaue"
q.
-
This SQL gets all elements from the selected package, for example in the Project Browser ( currentSelectedElement ) which have the tagged value TAGGED_VALUE. You can remove this option to search in the whole model. Assumes no package hierarchy eg elements in the packages in packages.. but only the root elements
function getElementsFromSelectedPackageSQL(currentSelectedElement) {
if ( (currentSelectedElement == null) || (typeof currentSelectedElement == 'undefined') )
return null;
//get all the root elements from the currentSelectedElement that:
// - have a tagged value TAGGED_VALUE
var sql = "SELECT obj.Object_ID " +
"FROM (( t_object obj " +
"INNER JOIN t_objectproperties op on op.Object_ID = obj.Object_ID ) " +
"LEFT JOIN t_package pck1 on obj.Package_ID = pck1.Package_ID ) " +
"WHERE op.Property='" + TAGGED_VALUE +
"'AND ( obj.Package_ID = " + currentSelectedElement.PackageID + " ) ";
var elementCollection;
try {
elementCollection = GetElementSet(sql, 2);
} catch (ex) {
LOGError("SQL I/O LOGError" + ex.description);
return null;
}
return elementCollection;
}