It's all about the answers!

Ask a question

Work item comments


Serdar Biçer (6) | asked Nov 25 '09, 2:51 a.m.
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.

3 answers



permanent link
James Moody (3.3k24) | answered Nov 25 '09, 11:53 a.m.
JAZZ DEVELOPER
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

permanent link
James Moody (3.3k24) | answered Nov 25 '09, 11:53 a.m.
JAZZ DEVELOPER
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

permanent link
Serdar Biçer (6) | answered Nov 26 '09, 9:31 a.m.
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

Thanks jmoody. I've tried to use SQL directly until today, now I will try to use the APIs.

Your answer


Register or to post your answer.


Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.