Tuesday, July 3, 2007

Directly querying the Mantis DB for file resource ownership

A request came in to me the other day from Dave Baker on behalf of a customer and I wanted to share the answer with everyone. The ask is to directly query the SQL DB for the owning component of a file resource instead of using Target Designer's Filter Manager.

This query below will return all components in the DB that own a file, there's no reason why you can't pass in a list of files or use the query in an ASP or vbscript. You'll need query the MantisSQLDB, you can copy and past this query from the web page directly into Query Analyzer.

SELECT ExtendedProperties.StringValue, ComponentObjects.DisplayName

FROM ExtendedProperties INNER JOIN ComponentObjects ON ComponentObjects.ComponentID = ExtendedProperties.OwnerID

WHERE (ExtendedProperties.StringValue LIKE 'iexplore.exe') AND (ExtendedProperties.ResourceTypeID = 1) ORDER BY ExtendedProperties.StringValue

That query returns a list of components in my database, as you can see it found all versions of components that own the iexplore.exe file resource. The 'Primitive: iexplorer.exe' component is from the Feature Pack 2007 refactoring work we did to reduce footprint.

Internet Explorer
Internet Explorer - Hotfix Q319182
Internet Explorer
Internet Explorer - Hotfix Q321232
Internet Explorer - Hotfix Q313675
Internet Explorer - Hotfix Q316059
Internet Explorer

You could do some more interesting things here with the query, such as return the version number of the component, order them by version so you know which is the winning component to look for in Target Designer, etc...Or to find all file resources with something like the string 'wmi' in it, change 'iexplore.exe' to '%wmi%' in the query above.

Later we'll modify the script to return the owning components for registry key(s).

Note: This method of information retrieval is not for everybody. My recommendation to folks is to pick up a copy of Sean's XPe Component developer tools, I use them as well.

No comments: