Project

General

Profile

« Previous | Next » 

Revision 9410

merge from 2.5 branch: use subquery to do paging before joining to identifier table. https://redmine.dataone.org/issues/7491

View differences:

src/edu/ucsb/nceas/metacat/EventLog.java
417 417
        }
418 418
        memberNode.setValue(nodeId);
419 419
        
420
        String countClause = "select count(*) ";
421
        String fieldsClause = "select " +
422
        		"entryid, " +
423
        		"id.guid as identifier, " +
424
        		"ip_address, " +
425
        		"user_agent, " +
426
        		"principal, " +
427
        		"case " +
428
        		"	when event = 'insert' then 'create' " +
429
        		"	else event " +
430
        		"end as event, " +
431
        		"date_logged ";
420
        // subquery does the heavy lifting
421
        StringBuffer subQueryFrom = new StringBuffer();
422
        subQueryFrom.append("from access_log ");
432 423
        
433
        StringBuffer queryWhereClause = new StringBuffer();
434
        queryWhereClause.append(		 
435
        		"from access_log al, identifier id " +
436
        		"where al.docid = id.docid||'.'||id.rev "
437
        );
424
        boolean clauseAdded = false;
438 425
        
439
        boolean clauseAdded = true;
440
        
441 426
        List<String> paramValues = new ArrayList<String>();
442 427
        if (ipAddress != null) {
443 428
        	if (clauseAdded) {
444
                queryWhereClause.append(" and ");
429
        		subQueryFrom.append(" and ");
430
            } else {
431
            	subQueryFrom.append(" where ");
445 432
            }
446
        	queryWhereClause.append("ip_address in (");
433
        	subQueryFrom.append("ip_address in (");
447 434
        	for (int i = 0; i < ipAddress.length; i++) {
448 435
        		if (i > 0) {
449
            		queryWhereClause.append(", ");
436
        			subQueryFrom.append(", ");
450 437
        		}
451
        		queryWhereClause.append("?");
438
        		subQueryFrom.append("?");
452 439
        		paramValues.add(ipAddress[i]);
453 440
        	}
454
        	queryWhereClause.append(") ");
441
        	subQueryFrom.append(") ");
455 442
            clauseAdded = true;
456 443
        }
457 444
        if (principal != null) {
458 445
        	if (clauseAdded) {
459
                queryWhereClause.append(" and ");
446
        		subQueryFrom.append(" and ");
447
            } else {
448
            	subQueryFrom.append(" where ");
460 449
            }
461
        	queryWhereClause.append("principal in (");
450
        	subQueryFrom.append("principal in (");
462 451
        	for (int i = 0; i < principal.length; i++) {
463 452
        		if (i > 0) {
464
            		queryWhereClause.append(", ");
453
        			subQueryFrom.append(", ");
465 454
        		}
466
        		queryWhereClause.append("?");
455
        		subQueryFrom.append("?");
467 456
        		paramValues.add(principal[i]);
468 457
        	}
469
        	queryWhereClause.append(") ");
458
        	subQueryFrom.append(") ");
470 459
            clauseAdded = true;
471 460
        }
472 461
        if (docid != null) {
473 462
        	if (clauseAdded) {
474
                queryWhereClause.append(" and ");
463
        		subQueryFrom.append(" and ");
464
            } else {
465
            	subQueryFrom.append(" where ");
475 466
            }
476
        	queryWhereClause.append("al.docid in (");
467
        	subQueryFrom.append("al.docid in (");
477 468
        	for (int i = 0; i < docid.length; i++) {
478 469
        		if (i > 0) {
479
            		queryWhereClause.append(", ");
470
        			subQueryFrom.append(", ");
480 471
        		}
481
        		queryWhereClause.append("?");
472
        		subQueryFrom.append("?");
482 473
        		paramValues.add(docid[i]);
483 474
        	}
484
        	queryWhereClause.append(") ");
475
        	subQueryFrom.append(") ");
485 476
            clauseAdded = true;
486 477
        }
487 478
        if (event != null) {
488 479
        	if (clauseAdded) {
489
                queryWhereClause.append(" and ");
480
        		subQueryFrom.append(" and ");
481
            } else {
482
            	subQueryFrom.append(" where ");
490 483
            }
491
        	queryWhereClause.append("event in (");
492
    		queryWhereClause.append("?");
484
        	subQueryFrom.append("event in (");
485
        	subQueryFrom.append("?");
493 486
    		String eventString = event;
494 487
    		if (eventString.equals(Event.CREATE.xmlValue())) {
495 488
    			eventString = "insert";
496 489
    		}
497 490
    		paramValues.add(eventString);
498
        	queryWhereClause.append(") ");
491
    		subQueryFrom.append(") ");
499 492
            clauseAdded = true;
500 493
        }
501 494
        
502 495
        if (startDate != null) {
503 496
            if (clauseAdded) {
504
                queryWhereClause.append(" and ");
497
            	subQueryFrom.append(" and ");
498
            } else {
499
            	subQueryFrom.append(" where ");
505 500
            }
506
            queryWhereClause.append("date_logged >= ?");
501
            subQueryFrom.append("date_logged >= ?");
507 502
            clauseAdded = true;
508 503
        }
509 504
        if (endDate != null) {
510 505
            if (clauseAdded) {
511
                queryWhereClause.append(" and ");
506
            	subQueryFrom.append(" and ");
507
            } else {
508
            	subQueryFrom.append(" where ");
512 509
            }
513
            queryWhereClause.append("date_logged < ?");
510
            subQueryFrom.append("date_logged < ?");
514 511
            clauseAdded = true;
515 512
        }
516 513

  
514
        // count query
515
        String countSelect = "select count(*) ";
516
        
517
        // subquery select
518
        String subquerySelect = "select entryid ";
519
        
520
        // for selecting fields we want in the join
521
        String fieldSelect = 
522
        		"select " +
523
        		"entryid, " +
524
        		"id.guid as identifier, " +
525
        		"ip_address, " +
526
        		"user_agent, " +
527
        		"principal, " +
528
        		"case " +
529
        		"	when event = 'insert' then 'create' " +
530
        		"	else event " +
531
        		"end as event, " +
532
        		"date_logged " +	 
533
        		"from access_log al, identifier id " +
534
        		"where al.docid = id.docid||'.'||id.rev " +
535
        		"and al.entryid in ";
536
        
517 537
        // order by
518 538
        String orderByClause = " order by entryid ";
519

  
539
        
520 540
        // select the count
521
        String countQuery = countClause + queryWhereClause.toString();
522
        logMetacat.debug("The count query is "+countQuery);
523
		// select the fields
524
        String pagedQuery = DatabaseService.getInstance().getDBAdapter().getPagedQuery(fieldsClause + queryWhereClause.toString() + orderByClause, start, count);
525
        logMetacat.debug("The selection query is "+pagedQuery);
526
        logMetacat.debug("The startDate in the query is "+startDate);
527
        logMetacat.debug("The endDate in the query is "+startDate);
541
        String countQuery = countSelect + subQueryFrom.toString();
542
        logMetacat.debug("The count query is " + countQuery);
543
		// select the fields using paged subquery and fields join query
544
        String pagedSubquery = DatabaseService.getInstance().getDBAdapter().getPagedQuery(subquerySelect + subQueryFrom.toString() + orderByClause, start, count);
545
        String pagedQuery = fieldSelect + " ( " + pagedSubquery + " ) " + orderByClause; 
546
        logMetacat.debug("The selection query is " + pagedQuery);
547
        logMetacat.debug("The startDate in the query is " + startDate);
548
        logMetacat.debug("The endDate in the query is " + startDate);
528 549

  
529 550
        DBConnection dbConn = null;
530 551
        int serialNumber = -1;

Also available in: Unified diff