3 Replies Latest reply on Sep 5, 2018 8:19 PM by gcarrion03

    JiveBrowse and JiveDocument Table Questions

    Alex Evans Guru

      Q1: Write a query to show documents with more than 2 versions.
      Q2: Write a query to show documents that aren't in the browse index.

       

      jivebrowsecnt
      https://docs.jivesoftware.com/schemas/9_0/sbs/tables/jivebrowsecnt.html

      Column | Type | Modifiers
      ------------------+------------------------+-----------
      browseid | bigint | not null
      objecttype | integer | not null
      objectid | bigint | not null
      authorid | bigint | not null
      status | integer | not null
      containertype | integer | not null
      containerid | bigint | not null
      creationdate | bigint | not null
      modificationdate | bigint | not null
      lastactivitydate | bigint | not null
      subject | character varying(255)

       

       

      jivedocument
      https://docs.jivesoftware.com/schemas/9_0/sbs/tables/jivedocument.html
      Column | Type | Modifiers
      -----------------+------------------------+-----------
      internaldocid | bigint | not null
      userid | bigint | not null
      documentid | character varying(255) | not null
      containertype | integer | not null
      containerid | bigint | not null
      typeid | bigint | not null
      editpolicy | integer | not null
      creationdate | bigint | not null
      expirationdate | bigint | not null
      recommended | smallint | not null
      meanrating | integer | not null
      readcount | integer | not null
      commentstatus | integer | not null
      workflowid | bigint |
      versionid | integer | not null
      exstoragefileid | bigint |

        • Re: JiveBrowse and JiveDocument Table Questions
          gcarrion03 Novice

          Hi Alex, I've solved the questions that you sent me, however I couldn't test them properly since I don't have access to the DB-CLI, Anyway here are my answers based on the diagrams that you sent me:

           

          Q1: Write a query to show documents with more than 2 versions.

           

          /*THIS RETRIEVES THE INTERNALDOCID FOR DOCUMENTS WITH MORE THAN 2 VERSIONS*/
          SELECT jdv.internaldocid idi, COUNT(jdv.internaldocid) cnt FROM jivedocversion jdv GROUP BY jdv.internaldocid HAVING cnt>2;
          
          /*RETRIEVES ALL THE JIVEDOCUMENTs TUPLES THAT COMPLIES WITH THE >2 REQUIREMENT*/
          SELECT * FROM jivedocument
          WHERE internaldocid
          IN (SELECT jdv.internaldocid FROM jivedocversion jdv GROUP BY jdv.internaldocid HAVING COUNT(jdv.internaldocid) > 2);

           

          Q2: Write a query to show documents that aren't in the browse index.

           

          /*It is possible to use the containerid FK present in the 'jivedocument' and 'jivebrowsecnt' to check if a document is not associated with any browse index registry*/
          SELECT * FROM jivedocument WHERE containerid NOT IN (SELECT DISTINCT containerid FROM jivebrowsecnt);

           

          Regards

           

          Gustavo M. Carrion

            • Re: JiveBrowse and JiveDocument Table Questions
              Alex Evans Guru

              Hello Gustavo,

               

              The results from Q1 and Q2 weren't quite right and I understand you were working without an actual dataset. I do know that developing sql rarely takes place without access to test data. I've attached a dump of the jivebrowsecnt and jivedocument tables. Can I ask you to please restore these tables in an empty postgres database and rework the sql scripts above? The attached sql files both contain DDL statements so you shouldn't have any trouble restoring them in postgres.

               

              Thank you!

                • Re: JiveBrowse and JiveDocument Table Questions
                  gcarrion03 Novice

                  Hi Alex Evans

                   

                  Ok, I've restored these tables, here are my answers along with a few assumptions required to make the queries work:

                   

                  Q1: requires to query jivedocument table only

                   

                  Solution:

                   

                  --Implying that all docs start from version 1 and increase by 1
                  select * from jivedocument where versionid > 2;

                   

                   

                  and

                  Q2: requires to query jivebrowsecnt and jivedocument tables but it is safe to assume that the objectid column in jivebrowsecnt references internaldocid in jivedocument?

                   

                  Solution 1 :

                  --After analyzing jivedocument table vs jivebrowsecnt objecttype #102 refers to documents so the easy way to detect if a document is not indexed is
                  select * from jivedocument where internaldocid not in (select objectid from jivebrowsecnt jb where objecttype=102);

                   

                  Solution 2 :

                  --It is posible to associate them assuming that internaldocid of jive document references objectid of jivebrowse along with their creation dates to prevent objecttype inference  
                  select * from jivedocument where internaldocid not in (select internaldocid from jivedocument jd, jivebrowsecnt jb where jd.internaldocid = jb.objectid and jd.creationdate = jb.creationdate);
                  --As a side note the containertype, containerid, authorid, objectid could be used with and operators if the creation date is not the same due to some business logic

                   

                  As you can see I've made a few assumptions regarding to some columns references since they were not marked as FKs, however I hope these are the correct solutions

                   

                  Regards,

                   

                  Gustavo