Project

General

Profile

« Previous | Next » 

Revision 7407

simplify the xml_access query, and instead use guid to check for permission. Now the docid/rev join (to get most recent version for search results) happens "higher up" in the query.
http://bugzilla.ecoinformatics.org/show_bug.cgi?id=5696

View differences:

test/edu/ucsb/nceas/metacattest/QuerySpecificationTest.java
55 55
    /** The utilities object for accessing property values */
56 56
    
57 57
    private static String selectionQuery = 
58
    	"SELECT docid,docname,doctype,date_created, date_updated, rev " +
59
    	"FROM xml_documents WHERE docid IN (((((SELECT DISTINCT docid FROM xml_nodes " +
58
    	"SELECT xml_documents.docid, docname, doctype, date_created, date_updated, xml_documents.rev " +
59
    	"FROM xml_documents, identifier " +
60
    	"WHERE xml_documents.docid = identifier.docid AND xml_documents.rev = identifier.rev " +
61
    	"AND xml_documents.docid IN (((((SELECT DISTINCT docid " +
62
    	"FROM xml_nodes " +
60 63
    	"WHERE UPPER(nodedata) LIKE %JONES% ) )))) ";
61 64
    /*private static String extendedQuery = "select xml_nodes.docid, 'dataset/title' as path, xml_nodes.nodedata, xml_nodes.parentnodeid from xml_nodes, xml_documents where parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'title' AND parentnodeid IN (SELECT nodeid FROM xml_nodes WHERE nodename LIKE 'dataset' ) )  AND xml_nodes.docid in " +
62 65
    		"('obfs.45337', 'obfs.45338', 'obfs.45346') AND xml_nodes.nodetype = 'TEXT' AND " +
src/edu/ucsb/nceas/metacat/QuerySpecification.java
249 249
    /*
250 250
     * Method to get owner query. If it is owner it has all permission
251 251
     */
252
    private String createOwerQuery()
252
    private String createOwnerQuery()
253 253
    {
254 254
        String ownerQuery = null;
255 255
        //if user is public, we don't need to run owner query
......
272 272
    {
273 273
        String allowQuery = null;
274 274
        String allowString = constructAllowString();
275
        allowQuery = "SELECT id.docid from xml_access xa, identifier id, xml_documents xmld " +
276
        		"WHERE id.guid = xa.guid AND id.docid = xmld.docid AND id.rev = xmld.rev AND ( " + allowString;
275
        allowQuery = "SELECT guid from xml_access  " +
276
        		"WHERE ( " + allowString;
277 277
        allowQuery = allowQuery + ")";
278 278
        logMetacat.info("QuerySpecification.createAllowRuleQuery - allow query is: " + allowQuery);
279 279
        return allowQuery;
......
320 320
    {
321 321
        String denyQuery = null;
322 322
        String denyString = constructDenyString();
323
        denyQuery = "SELECT id.docid from xml_access xa, identifier id, xml_documents xmld " +
324
        		"WHERE id.guid = xa.guid AND id.docid = xmld.docid AND id.rev = xmld.rev AND ( " + denyString;
323
        denyQuery = "SELECT guid from xml_access " +
324
        		"WHERE ( " + denyString;
325 325
        denyQuery = denyQuery + ") ";
326 326
        logMetacat.info("QuerySpecification.createDenyRuleQuery - denyquery is: " + denyQuery);
327 327
        return denyQuery;
......
370 370
    public String getAccessQuery()
371 371
    {
372 372
        String accessQuery = null;
373
        String onwer = createOwerQuery();
373
        String owner = createOwnerQuery();
374 374
        String allow = createAllowRuleQuery();
375 375
        String deny = createDenyRuleQuery();
376 376

  
377
        if (onwer != null)
377
        if (owner != null)
378 378
        {
379
          accessQuery = " AND (docid IN(" + onwer + ")";
380
          accessQuery = accessQuery + " OR (docid IN (" + allow + ")"
381
                + " AND docid NOT IN (" + deny + ")))";
379
          accessQuery = " AND (xml_documents.docid IN (" + owner + ")";
380
          accessQuery = accessQuery + " OR (identifier.guid IN (" + allow + ")"
381
                + " AND identifier.guid NOT IN (" + deny + ")))";
382 382
        }
383 383
        else
384 384
        {
385
        	accessQuery = " AND (docid IN (" + allow + ")"
386
                + " AND docid NOT IN (" + deny + "))";
385
        	accessQuery = " AND (identifier.guid IN (" + allow + ")"
386
                + " AND identifier.guid NOT IN (" + deny + "))";
387 387
        }
388 388
        logMetacat.info("QuerySpecification.getAccessQuery - access query is: " + accessQuery);
389 389
        return accessQuery;
......
749 749
        StringBuffer self = new StringBuffer();
750 750
        StringBuffer queryString = new StringBuffer();
751 751

  
752
        queryString.append("SELECT docid,docname,doctype,");
753
        queryString.append("date_created, date_updated, rev ");
754
        queryString.append("FROM xml_documents WHERE");
752
        queryString.append("SELECT xml_documents.docid, docname, doctype, date_created, date_updated, xml_documents.rev ");
753
        queryString.append("FROM xml_documents, identifier ");
754
        queryString.append("WHERE xml_documents.docid = identifier.docid AND xml_documents.rev = identifier.rev AND");
755 755

  
756 756
        // Get the query from the QueryGroup and check
757 757
        // if no query has been returned
......
766 766
        logMetacat.info("QuerySpecification.printSQL - Query : " + queryFromQueryGroup);
767 767
        
768 768
        if(!queryFromQueryGroup.trim().equals("")){
769
            self.append(" docid IN (");
769
            self.append(" xml_documents.docid IN (");
770 770
            self.append(queryFromQueryGroup);
771 771
            self.append(") ");
772 772
            // add the parameter values
src/edu/ucsb/nceas/metacat/DBQuery.java
640 640
    	  // condition for the docids
641 641
    	  List<Object> docidConditionValues = new ArrayList<Object>();
642 642
    	  StringBuffer docidCondition = new StringBuffer();
643
    	  docidCondition.append( " docid IN (" );
643
    	  docidCondition.append( " xml_documents.docid IN (" );
644 644
          for (int i = 0; i < givenDocids.size(); i++) {  
645 645
        	  docidCondition.append("?");
646 646
        	  if (i < givenDocids.size()-1) {
......
652 652
		  
653 653
    	  // include the docids, either exclusively, or in conjuction with the query
654 654
    	  if (operator == null) {
655
    		  query = "SELECT docid, docname, doctype, date_created, date_updated, rev FROM xml_documents WHERE";
655
    		  query = "SELECT xml_documents.docid, docname, doctype, date_created, date_updated, xml_documents.rev " +
656
    		  		"FROM xml_documents, identifier " +
657
    		  		"WHERE xml_documents.docid = identifier.docid AND xml_documents.rev = identifier.rev AND ";
656 658
              query = query + docidCondition.toString();
657 659
              parameterValues.addAll(docidConditionValues);
658 660
    	  } else {
src/edu/ucsb/nceas/metacat/QueryGroup.java
29 29

  
30 30
package edu.ucsb.nceas.metacat;
31 31

  
32
import edu.ucsb.nceas.dbadapter.*;
33
import edu.ucsb.nceas.metacat.shared.MetacatUtilException;
34
import edu.ucsb.nceas.metacat.util.MetacatUtil;
35
import edu.ucsb.nceas.metacat.util.SystemUtil;
36

  
37
import java.io.*;
38 32
import java.util.ArrayList;
39
import java.util.Hashtable;
33
import java.util.Enumeration;
40 34
import java.util.List;
41
import java.util.Stack;
42 35
import java.util.Vector;
43
import java.util.Enumeration;
44 36

  
45 37
import org.apache.log4j.Logger;
46 38

  
39
import edu.ucsb.nceas.metacat.shared.MetacatUtilException;
40
import edu.ucsb.nceas.metacat.util.SystemUtil;
41

  
47 42
 /** a utility class that represents a group of terms in a query */
48 43
public  class QueryGroup {
49 44
    private String operator = null;  // indicates how query terms are combined

Also available in: Unified diff