Expand all | Collapse all

How are Review Comments stored in the Jama's DB?

  • 1.  How are Review Comments stored in the Jama's DB?

    Posted 05-30-2016 08:56

    Hi everyone,

    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?


    /Ekaterina" />

  • 2.  Re: How are Review Comments stored in the Jama's DB?

    Posted 05-31-2016 05:54
    Hi Ekaterina,

    below there is a working query (MS SQL Server) out of a working BirtReport, the parameter "?" is the reviewID.

    select vlri.*,d.text2 as ext_id, d.documentKey--,,d.description
    ,d.text5 as rationale
    , as status
    ,d.string9 as req_status
    ,d.string10 as req_release_status
    ,d.string11 as ie_phase
    ,d.string12 as tranche
    ,(select firstname+' '+lastname from userbase where id=ru.userId) as CommentUser
    ,jc.commentText as 'Comment Text'
    , as review_item_user_status
    ,rut.modifiedDate as rui_modifiedDate
    , as ruit_id
    ,max(revisionid) as final_revisionid
    ,max(vid) as final_Versionid
    from document va
    inner join 
    (select v.documentId, as revisionId,rv.reviewId,bv.baselineid as bid, as vid,v.originDocumentId  from version v, baseline_version bv, revision rv where and bv.baselineId=rv.baselineId 
    ) rrvv on
    ) reviewComments
    group by 
    originDocumentId, reviewid) vlri -- latest version of reviewed item in review
    inner join version v on
    inner join document d on
    inner join lookup status on
    inner join revision_item ri on ri.revisionId=vlri.final_revisionid and ri.versionId=vlri.final_versionid
    inner join revision_user_item rut on
    inner join revision_user ru on
    inner join revisionuseritemstatus ruis on
    left outer join jamacomment jc on jc.documentId in (select documentId from version where originDocumentId in (select originDocumentid from version where and jc.createdBy=ru.userId and'T' 
    and jc.refId in (select from revision_item ri where ri.revisionId in (select id from revision where reviewid=vlri.reviewid)) and jc.commentText is not null
    where  vlri.reviewid= ? --405
    order by 1,, asc

  • 3.  Re: How are Review Comments stored in the Jama's DB?

    Posted 05-31-2016 09:21

    Hi Harald,

    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? 

    Best regards,


  • 4.  Re: How are Review Comments stored in the Jama's DB?

    Posted 05-31-2016 09:59
    Hi Ekaterina,

    this should be:

    select COUNT(*) from jamacomment jc, revision rv where and rv.reviewid=?

    The attribute sequence in the table review should give you the # of REV-#.


  • 5.  Re: How are Review Comments stored in the Jama's DB?

    Posted 06-01-2016 05:37

    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,
    from        dbo.jamacomment AS jc INNER JOIN
                          dbo.revision AS rv ON jc.refId = INNER JOIN
                          dbo.userbase ON jc.userId =
    where     (rv.reviewId = ?)