we are investigating a possibility to use BIRT Reports for a Review Center in Jama. Therefore couple of Data Model related questions in this regard, that was quite difficult to grasp only by using a Schema spy.
The general question is: Where are the comments from 1 and 2 (see a pic) are stored in the DB and how they are referred to a "review" table?
Here follow some details: Comments from 1 are stored in the "jamacomment" table there is an attribute refId, that is not described in the SchemaSpy what table it refers to. Seems like, those comments are connected to reviews through this attribute but the question is how?
The second question is how the comments from 2 are stored? Does it somehow happen through a baseline?
Are there any Jama's DB experts out there who could help to sort this out?
thanks a lot that's a cool query! I could investigate the connections, that are relevant for the comments (1) (and its a bit more complex, than I expected). Do you also know where the comments (2) are stored? And also, do you know where the review IDs from my picture below are stored?
Hi Harald, thanks a lot for your hint! I modified your query a bit, to display the information I need. Leave it here in case someone finds it helpful:
select rv.reviewId, rv.sequence, rv.overview, rv.createdDate, rv.createdBy, jc.commenttype, jc.isPrivate, jc.commentText, dbo.userbase.firstName, dbo.userbase.lastNamefrom dbo.jamacomment AS jc INNER JOIN dbo.revision AS rv ON jc.refId = rv.id INNER JOIN dbo.userbase ON jc.userId = dbo.userbase.idwhere (rv.reviewId = ?)
HEADQUARTERS|135 SW Taylor Suite 200, Portland, Oregon, 97204