Project

General

Profile

Revision 7451

get total (or subtotal when non-slicing params are present) count as a separate query from the field selection query.

View differences:

src/edu/ucsb/nceas/metacat/EventLog.java
337 337
        }
338 338
        memberNode.setValue(nodeId);
339 339
        
340
        StringBuffer query = new StringBuffer();
341
        query.append(
342
        		"select " +
340
        String countClause = "select count(*) ";
341
        String fieldsClause = "select " +
343 342
        		"entryid, " +
344 343
        		"id.guid as identifier, " +
345 344
        		"ip_address, " +
......
349 348
        		"	when event = 'insert' then 'create' " +
350 349
        		"	else event " +
351 350
        		"end as event, " +
352
        		"date_logged " +
351
        		"date_logged ";
352
        
353
        StringBuffer queryWhereClause = new StringBuffer();
354
        queryWhereClause.append(		 
353 355
        		"from access_log al, identifier id " +
354 356
        		"where al.docid = id.docid||'.'||id.rev "
355 357
        );
356 358
        
357
        
358 359
        boolean clauseAdded = true;
359
        int startIndex = 0;
360
        int endIndex = 0;
361 360
        
362 361
        List<String> paramValues = new ArrayList<String>();
363 362
        if (ipAddress != null) {
364 363
        	if (clauseAdded) {
365
                query.append(" and ");
364
                queryWhereClause.append(" and ");
366 365
            }
367
        	query.append("ip_address in (");
366
        	queryWhereClause.append("ip_address in (");
368 367
        	for (int i = 0; i < ipAddress.length; i++) {
369 368
        		if (i > 0) {
370
            		query.append(", ");
369
            		queryWhereClause.append(", ");
371 370
        		}
372
        		query.append("?");
371
        		queryWhereClause.append("?");
373 372
        		paramValues.add(ipAddress[i]);
374 373
        	}
375
        	query.append(") ");
374
        	queryWhereClause.append(") ");
376 375
            clauseAdded = true;
377 376
        }
378 377
        if (principal != null) {
379 378
        	if (clauseAdded) {
380
                query.append(" and ");
379
                queryWhereClause.append(" and ");
381 380
            }
382
        	query.append("principal in (");
381
        	queryWhereClause.append("principal in (");
383 382
        	for (int i = 0; i < principal.length; i++) {
384 383
        		if (i > 0) {
385
            		query.append(", ");
384
            		queryWhereClause.append(", ");
386 385
        		}
387
        		query.append("?");
386
        		queryWhereClause.append("?");
388 387
        		paramValues.add(principal[i]);
389 388
        	}
390
        	query.append(") ");
389
        	queryWhereClause.append(") ");
391 390
            clauseAdded = true;
392 391
        }
393 392
        if (docid != null) {
394 393
        	if (clauseAdded) {
395
                query.append(" and ");
394
                queryWhereClause.append(" and ");
396 395
            }
397
        	query.append("al.docid in (");
396
        	queryWhereClause.append("al.docid in (");
398 397
        	for (int i = 0; i < docid.length; i++) {
399 398
        		if (i > 0) {
400
            		query.append(", ");
399
            		queryWhereClause.append(", ");
401 400
        		}
402
        		query.append("?");
401
        		queryWhereClause.append("?");
403 402
        		paramValues.add(docid[i]);
404 403
        	}
405
        	query.append(") ");
404
        	queryWhereClause.append(") ");
406 405
            clauseAdded = true;
407 406
        }
408 407
        if (event != null) {
409 408
        	if (clauseAdded) {
410
                query.append(" and ");
409
                queryWhereClause.append(" and ");
411 410
            }
412
        	query.append("event in (");
413
    		query.append("?");
411
        	queryWhereClause.append("event in (");
412
    		queryWhereClause.append("?");
414 413
    		String eventString = event.xmlValue();
415 414
    		if (event.equals(Event.CREATE)) {
416 415
    			eventString = "insert";
417 416
    		}
418 417
    		paramValues.add(eventString);
419
        	query.append(") ");
418
        	queryWhereClause.append(") ");
420 419
            clauseAdded = true;
421 420
        }
422 421
        else {
423 422
	        if (clauseAdded) {
424
	            query.append(" and ");
423
	            queryWhereClause.append(" and ");
425 424
	        }
426
	    	query.append("event in (");
425
	    	queryWhereClause.append("event in (");
427 426
	    	for (int i = 0; i < Event.values().length; i++) {
428 427
	    		if (i > 0) {
429
	        		query.append(", ");
428
	        		queryWhereClause.append(", ");
430 429
	    		}
431
	    		query.append("?");
430
	    		queryWhereClause.append("?");
432 431
	    		Event e = Event.values()[i];
433 432
	    		String eventString = e.xmlValue();
434 433
	    		if (e.equals(Event.CREATE)) {
......
436 435
	    		}
437 436
	    		paramValues.add(eventString);
438 437
	    	}
439
	    	query.append(") ");
438
	    	queryWhereClause.append(") ");
440 439
	        clauseAdded = true;
441 440
        }
442 441
        if (startDate != null) {
443 442
            if (clauseAdded) {
444
                query.append(" and ");
443
                queryWhereClause.append(" and ");
445 444
            }
446
            query.append("date_logged >= ?");
445
            queryWhereClause.append("date_logged >= ?");
447 446
            clauseAdded = true;
448
            startIndex++;
449 447
        }
450 448
        if (endDate != null) {
451 449
            if (clauseAdded) {
452
                query.append(" and ");
450
                queryWhereClause.append(" and ");
453 451
            }
454
            query.append("date_logged < ?");
452
            queryWhereClause.append("date_logged < ?");
455 453
            clauseAdded = true;
456
            endIndex = startIndex + 1;
457 454
        }
458 455

  
459 456
        // order by
460
        query.append(" order by entryid ");
457
        String orderByClause = " order by entryid ";
461 458

  
462
		// limit by count
463
        String pagedQuery = DatabaseService.getInstance().getDBAdapter().getPagedQuery(query.toString(), start, count);
459
        // select the count
460
        String countQuery = countClause + queryWhereClause.toString();
461
        
462
		// select the fields
463
        String pagedQuery = DatabaseService.getInstance().getDBAdapter().getPagedQuery(fieldsClause + queryWhereClause.toString() + orderByClause, start, count);
464 464

  
465 465
        DBConnection dbConn = null;
466 466
        int serialNumber = -1;
......
470 470
            serialNumber = dbConn.getCheckOutSerialNumber();
471 471

  
472 472
            // Execute the query statement
473
            PreparedStatement stmt = dbConn.prepareStatement(pagedQuery);
473
            PreparedStatement fieldsStmt = dbConn.prepareStatement(pagedQuery);
474
            PreparedStatement countStmt = dbConn.prepareStatement(countQuery);
475

  
474 476
            //set the param values
475 477
            int parameterIndex = 1;
476 478
            for (String val: paramValues) {
477
            	stmt.setString(parameterIndex++, val);
479
            	countStmt.setString(parameterIndex, val);
480
            	fieldsStmt.setString(parameterIndex, val);
481
            	parameterIndex++;
478 482
            }
479 483
            if (startDate != null) {
480
                stmt.setTimestamp(parameterIndex++, startDate); 
484
            	countStmt.setTimestamp(parameterIndex, startDate); 
485
                fieldsStmt.setTimestamp(parameterIndex, startDate); 
486
            	parameterIndex++;
481 487
            }
482 488
            if (endDate != null) {
483
            	stmt.setTimestamp(parameterIndex++, endDate);
489
            	countStmt.setTimestamp(parameterIndex, endDate);
490
            	fieldsStmt.setTimestamp(parameterIndex, endDate);
491
            	parameterIndex++;
484 492
            }
485
            stmt.execute();
486
            ResultSet rs = stmt.getResultSet();
487
            //process the result and return it
493

  
494
            // for the return Log list
488 495
            List<LogEntry> logs = new Vector<LogEntry>();
489
            
490
            while (rs.next()) {
491
            	LogEntry logEntry = new LogEntry();
492
            	logEntry.setEntryId(rs.getString(1));
493
            	
494
            	Identifier identifier = new Identifier();
495
            	identifier.setValue(rs.getString(2));
496
				logEntry.setIdentifier(identifier);
497 496

  
498
            	logEntry.setIpAddress(anonymous ? "N/A" : rs.getString(3));
499
            	String userAgent = "N/A";
500
            	if (rs.getString(4) != null) {
501
            		userAgent = rs.getString(4);
502
            	}
503
            	logEntry.setUserAgent(userAgent);
504
            	
505
            	Subject subject = new Subject();
506
            	subject.setValue(anonymous ? "N/A" : rs.getString(5));
507
				logEntry.setSubject(subject);
508
				
509
				
510
				String logEventString = rs.getString(6);
511
				Event logEvent = Event.convert(logEventString );
512
				if (logEvent == null) {
513
					logMetacat.info("Skipping uknown DataONE Event type: " + logEventString);
514
					continue;
515
				}
516
				logEntry.setEvent(logEvent);
517
				logEntry.setDateLogged(rs.getTimestamp(7));
518
				
519
				logEntry.setNodeIdentifier(memberNode);
520
				logs.add(logEntry);
497
            // get the fields form the query
498
            if (count != 0) {
499
	            fieldsStmt.execute();
500
	            ResultSet rs = fieldsStmt.getResultSet();
501
	            //process the result and return it            
502
	            while (rs.next()) {
503
	            	LogEntry logEntry = new LogEntry();
504
	            	logEntry.setEntryId(rs.getString(1));
505
	            	
506
	            	Identifier identifier = new Identifier();
507
	            	identifier.setValue(rs.getString(2));
508
					logEntry.setIdentifier(identifier);
509
	
510
	            	logEntry.setIpAddress(anonymous ? "N/A" : rs.getString(3));
511
	            	String userAgent = "N/A";
512
	            	if (rs.getString(4) != null) {
513
	            		userAgent = rs.getString(4);
514
	            	}
515
	            	logEntry.setUserAgent(userAgent);
516
	            	
517
	            	Subject subject = new Subject();
518
	            	subject.setValue(anonymous ? "N/A" : rs.getString(5));
519
					logEntry.setSubject(subject);
520
					
521
					String logEventString = rs.getString(6);
522
					Event logEvent = Event.convert(logEventString );
523
					if (logEvent == null) {
524
						logMetacat.info("Skipping uknown DataONE Event type: " + logEventString);
525
						continue;
526
					}
527
					logEntry.setEvent(logEvent);
528
					logEntry.setDateLogged(rs.getTimestamp(7));
529
					
530
					logEntry.setNodeIdentifier(memberNode);
531
					logs.add(logEntry);
532
	            }
533
	            fieldsStmt.close();
521 534
            }
522 535
            
523
			log.setLogEntryList(logs);
524
			// d1 paging
525
		    int total = logs.size();
526
		    // NOTE: now using query limits
527
//		    if (start != null && count != null) {
528
//		    	int toIndex = start + count;
529
//		    	// do not exceed total
530
//		    	toIndex = Math.min(toIndex, total);
531
//		    	// do not start greater than total
532
//		    	start = Math.min(start, total);
533
//		    	// sub set of the list
534
//		    	logs = new ArrayList<LogEntry>(logs.subList(start, toIndex));
535
//		    }
536

  
537
		    log.setLogEntryList(logs);
536
            // set what we have
537
            log.setLogEntryList(logs);
538 538
		    log.setStart(start);
539 539
		    log.setCount(logs.size());
540
            			
541
			// get total for out query
542
		    int total = 0;
543
            countStmt.execute();
544
            ResultSet countRs = countStmt.getResultSet();
545
            if (countRs.next()) {
546
            	total = countRs.getInt(1);
547
            }
548
            countStmt.close();
540 549
		    log.setTotal(total);
541 550

  
542
            stmt.close();
543 551
        } catch (SQLException e) {
544 552
        	logMetacat.error("Error while getting log events: " + e.getMessage(), e);
545 553
        } finally {

Also available in: Unified diff