Revision 7451
Added by ben leinfelder almost 12 years ago
| 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
get total (or subtotal when non-slicing params are present) count as a separate query from the field selection query.