Support

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 11: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?

    BR,

    /Ekaterina

    https://d2r1vs3d9006ap.cloudfront.net/s3_images/1424669/RackMultipart20160530-100866-c73j1t-review_inline.jpg?1464608497" />


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

    Posted 05-31-2016 08: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.name,d.description
    ,d.text5 as rationale
    ,status.name 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'
    ,jc.createdDate
    ,jc.id
    ,ruis.name as review_item_user_status
    ,rut.modifiedDate as rui_modifiedDate
    ,rut.id as ruit_id
    from
    (select
    origindocumentid
    ,reviewid
    ,max(revisionid) as final_revisionid
    ,max(vid) as final_Versionid
    from
    (select
     rrvv.originDocumentId
     ,revisionid
     ,rrvv.vid
     ,reviewid
    from document va
    inner join 
    (select v.documentId, rv.id as revisionId,rv.reviewId,bv.baselineid as bid,v.id as vid,v.originDocumentId  from version v, baseline_version bv, revision rv where v.id=bv.versionId and bv.baselineId=rv.baselineId 
    ) rrvv on
    rrvv.documentId=va.id
    ) reviewComments
    group by 
    originDocumentId, reviewid) vlri -- latest version of reviewed item in review
    inner join version v on v.id=vlri.final_Versionid
    inner join document d on v.documentId=d.id
    inner join lookup status on d.statusId=status.id
    inner join revision_item ri on ri.revisionId=vlri.final_revisionid and ri.versionId=vlri.final_versionid
    inner join revision_user_item rut on rut.revisionItemId=ri.id
    inner join revision_user ru on ru.id=rut.revisionUserId
    inner join revisionuseritemstatus ruis on ruis.id=rut.status
    left outer join jamacomment jc on jc.documentId in (select documentId from version where originDocumentId in (select originDocumentid from version where documentid=d.id)) and jc.createdBy=ru.userId and jc.active='T' 
    and jc.refId in (select ri.id 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,rut.id,jc.id asc


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

    Posted 05-31-2016 12: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,

    Ekaterina



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

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

    this should be:

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

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

    regards
    Harald


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

    Posted 06-01-2016 08: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,
                          dbo.userbase.lastName
    from        dbo.jamacomment AS jc INNER JOIN
                          dbo.revision AS rv ON jc.refId = rv.id INNER JOIN
                          dbo.userbase ON jc.userId = dbo.userbase.id
    where     (rv.reviewId = ?)

    BR,

    Ekaterina