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);
Gustavo M. Carrion
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.
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
--Implying that all docs start from version 1 and increase by 1 select * from jivedocument where versionid > 2;
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