On 11/25/2009 2:52 AM, serdarbicer wrote:

Hi all, I'm a Master's student and working on an application of social
network analysis on RTC database schema. I want to extract who wrote a
comment at a specified time to a work item using SQL but I'm stuck in
DB. My intend is to use Workitem_States table in Workitems Snapshot
to get time information for change in comment counts but I can't find
a way to get who made this change. How can I get this information?

Any help would be appreciated.

Hi,

So the Workitem_states and Workitem_chngs tables are data warehouse
tables which present a somewhat aggregated historical view of the
repository data. In this case, we collect only the count of comments,
not information about who made the comments (or the text of the comments
themselves).

So I think you'll have to use the live (operational) data in the
repository itself. The database schema for this isn't public - is it
possible for you to use either Java or REST APIs? That would be the
recommended way to get this information. That way you could get access
to the comments (stored internally as "internalComments") which have
information about the creator and modification time.

Someone from the work items team might be able to help you with the APIs
that you'll need to get this information.

Hope this helps.

james
RTC Reports Team Lead

On 11/25/2009 11:44 AM, James Moody wrote:

On 11/25/2009 2:52 AM, serdarbicer wrote:
Hi all, I'm a Master's student and working on an application of social
network analysis on RTC database schema. I want to extract who wrote a
comment at a specified time to a work item using SQL but I'm stuck in
DB. My intend is to use Workitem_States table in Workitems Snapshot
to get time information for change in comment counts but I can't find
a way to get who made this change. How can I get this information?

Any help would be appreciated.


Hi,

So the Workitem_states and Workitem_chngs tables are data warehouse
tables which present a somewhat aggregated historical view of the
repository data. In this case, we collect only the count of comments,
not information about who made the comments (or the text of the comments
themselves).

So I think you'll have to use the live (operational) data in the
repository itself. The database schema for this isn't public - is it
possible for you to use either Java or REST APIs? That would be the
recommended way to get this information. That way you could get access
to the comments (stored internally as "internalComments") which have
information about the creator and modification time.

Someone from the work items team might be able to help you with the APIs
that you'll need to get this information.

Hope this helps.

james
RTC Reports Team Lead

As a quick followup, if you have an IWorkItem, getComments() returns an
IComments, from which you can get individual IComment items. Each
IComment supports getCreator() and getCreationDate().

james