Revision 7452
Added by ben leinfelder almost 12 years ago
| src/edu/ucsb/nceas/metacat/IdentifierManager.java | ||
|---|---|---|
| 1424 | 1424 |
int start, int count) |
| 1425 | 1425 |
throws SQLException, PropertyNotFoundException, ServiceException {
|
| 1426 | 1426 |
ObjectList ol = new ObjectList(); |
| 1427 |
int total = 0; |
|
| 1428 | 1427 |
DBConnection dbConn = null; |
| 1429 | 1428 |
int serialNumber = -1; |
| 1430 | 1429 |
|
| 1431 | 1430 |
try {
|
| 1432 |
String sql = "select guid, date_uploaded, rights_holder, checksum, "
|
|
| 1431 |
String fieldSql = "select guid, date_uploaded, rights_holder, checksum, "
|
|
| 1433 | 1432 |
+ "checksum_algorithm, origin_member_node, authoritive_member_node, " |
| 1434 | 1433 |
+ "date_modified, submitter, object_format, size from systemmetadata"; |
| 1435 | 1434 |
|
| 1436 | 1435 |
// handle special case quickly |
| 1437 | 1436 |
String countSql = "select count(guid) from systemmetadata"; |
| 1438 |
if (count == 0) {
|
|
| 1439 |
sql = countSql;
|
|
| 1440 |
}
|
|
| 1437 |
|
|
| 1438 |
// the clause
|
|
| 1439 |
String whereClauseSql = "";
|
|
| 1441 | 1440 |
|
| 1442 | 1441 |
boolean f1 = false; |
| 1443 | 1442 |
boolean f2 = false; |
| 1444 | 1443 |
boolean f3 = false; |
| 1445 | 1444 |
|
| 1446 | 1445 |
if (startTime != null) {
|
| 1447 |
sql += " where systemmetadata.date_modified >= ?";
|
|
| 1446 |
whereClauseSql += " where systemmetadata.date_modified >= ?";
|
|
| 1448 | 1447 |
f1 = true; |
| 1449 | 1448 |
} |
| 1450 | 1449 |
|
| 1451 | 1450 |
if (endTime != null) {
|
| 1452 | 1451 |
if (!f1) {
|
| 1453 |
sql += " where systemmetadata.date_modified < ?";
|
|
| 1452 |
whereClauseSql += " where systemmetadata.date_modified < ?";
|
|
| 1454 | 1453 |
} else {
|
| 1455 |
sql += " and systemmetadata.date_modified < ?";
|
|
| 1454 |
whereClauseSql += " and systemmetadata.date_modified < ?";
|
|
| 1456 | 1455 |
} |
| 1457 | 1456 |
f2 = true; |
| 1458 | 1457 |
} |
| 1459 | 1458 |
|
| 1460 | 1459 |
if (objectFormatId != null) {
|
| 1461 | 1460 |
if (!f1 && !f2) {
|
| 1462 |
sql += " where object_format = ?";
|
|
| 1461 |
whereClauseSql += " where object_format = ?";
|
|
| 1463 | 1462 |
} else {
|
| 1464 |
sql += " and object_format = ?";
|
|
| 1463 |
whereClauseSql += " and object_format = ?";
|
|
| 1465 | 1464 |
} |
| 1466 | 1465 |
f3 = true; |
| 1467 | 1466 |
} |
| ... | ... | |
| 1469 | 1468 |
if (!replicaStatus) {
|
| 1470 | 1469 |
String currentNodeId = PropertyService.getInstance().getProperty("dataone.nodeId");
|
| 1471 | 1470 |
if (!f1 && !f2 && !f3) {
|
| 1472 |
sql += " where authoritive_member_node != '" +
|
|
| 1471 |
whereClauseSql += " where authoritive_member_node != '" +
|
|
| 1473 | 1472 |
currentNodeId.trim() + "'"; |
| 1474 | 1473 |
} else {
|
| 1475 |
sql += " and authoritive_member_node != '" +
|
|
| 1474 |
whereClauseSql += " and authoritive_member_node != '" +
|
|
| 1476 | 1475 |
currentNodeId.trim() + "'"; |
| 1477 | 1476 |
} |
| 1478 | 1477 |
} |
| 1479 |
|
|
| 1480 |
// order the results for slicing ops |
|
| 1481 |
String finalQuery = null; |
|
| 1482 |
if (count != 0) {
|
|
| 1483 |
sql += " order by guid "; |
|
| 1484 |
finalQuery = DatabaseService.getInstance().getDBAdapter().getPagedQuery(sql, start, count); |
|
| 1485 |
} else {
|
|
| 1486 |
finalQuery = sql; |
|
| 1487 |
} |
|
| 1488 | 1478 |
|
| 1479 |
// connection |
|
| 1489 | 1480 |
dbConn = DBConnectionPool.getDBConnection("IdentifierManager.querySystemMetadata");
|
| 1490 | 1481 |
serialNumber = dbConn.getCheckOutSerialNumber(); |
| 1491 |
PreparedStatement stmt = dbConn.prepareStatement(finalQuery); |
|
| 1492 | 1482 |
|
| 1483 |
// the field query |
|
| 1484 |
String orderBySql = " order by guid "; |
|
| 1485 |
String fieldQuery = fieldSql + whereClauseSql + orderBySql; |
|
| 1486 |
String finalQuery = DatabaseService.getInstance().getDBAdapter().getPagedQuery(fieldQuery, start, count); |
|
| 1487 |
PreparedStatement fieldStmt = dbConn.prepareStatement(finalQuery); |
|
| 1488 |
|
|
| 1489 |
// construct the count query and statment |
|
| 1490 |
String countQuery = countSql + whereClauseSql; |
|
| 1491 |
PreparedStatement countStmt = dbConn.prepareStatement(countQuery); |
|
| 1492 |
|
|
| 1493 | 1493 |
if (f1 && f2 && f3) {
|
| 1494 |
stmt.setTimestamp(1, new Timestamp(startTime.getTime())); |
|
| 1495 |
stmt.setTimestamp(2, new Timestamp(endTime.getTime())); |
|
| 1496 |
stmt.setString(3, objectFormatId.getValue()); |
|
| 1494 |
fieldStmt.setTimestamp(1, new Timestamp(startTime.getTime())); |
|
| 1495 |
fieldStmt.setTimestamp(2, new Timestamp(endTime.getTime())); |
|
| 1496 |
fieldStmt.setString(3, objectFormatId.getValue()); |
|
| 1497 |
// count |
|
| 1498 |
countStmt.setTimestamp(1, new Timestamp(startTime.getTime())); |
|
| 1499 |
countStmt.setTimestamp(2, new Timestamp(endTime.getTime())); |
|
| 1500 |
countStmt.setString(3, objectFormatId.getValue()); |
|
| 1497 | 1501 |
} else if (f1 && f2 && !f3) {
|
| 1498 |
stmt.setTimestamp(1, new Timestamp(startTime.getTime())); |
|
| 1499 |
stmt.setTimestamp(2, new Timestamp(endTime.getTime())); |
|
| 1502 |
fieldStmt.setTimestamp(1, new Timestamp(startTime.getTime())); |
|
| 1503 |
fieldStmt.setTimestamp(2, new Timestamp(endTime.getTime())); |
|
| 1504 |
// count |
|
| 1505 |
countStmt.setTimestamp(1, new Timestamp(startTime.getTime())); |
|
| 1506 |
countStmt.setTimestamp(2, new Timestamp(endTime.getTime())); |
|
| 1500 | 1507 |
} else if (f1 && !f2 && f3) {
|
| 1501 |
stmt.setTimestamp(1, new Timestamp(startTime.getTime())); |
|
| 1502 |
stmt.setString(2, objectFormatId.getValue()); |
|
| 1508 |
fieldStmt.setTimestamp(1, new Timestamp(startTime.getTime())); |
|
| 1509 |
fieldStmt.setString(2, objectFormatId.getValue()); |
|
| 1510 |
// count |
|
| 1511 |
countStmt.setTimestamp(1, new Timestamp(startTime.getTime())); |
|
| 1512 |
countStmt.setString(2, objectFormatId.getValue()); |
|
| 1503 | 1513 |
} else if (f1 && !f2 && !f3) {
|
| 1504 |
stmt.setTimestamp(1, new Timestamp(startTime.getTime())); |
|
| 1514 |
fieldStmt.setTimestamp(1, new Timestamp(startTime.getTime())); |
|
| 1515 |
// count |
|
| 1516 |
countStmt.setTimestamp(1, new Timestamp(startTime.getTime())); |
|
| 1505 | 1517 |
} else if (!f1 && f2 && f3) {
|
| 1506 |
stmt.setTimestamp(1, new Timestamp(endTime.getTime())); |
|
| 1507 |
stmt.setString(2, objectFormatId.getValue()); |
|
| 1518 |
fieldStmt.setTimestamp(1, new Timestamp(endTime.getTime())); |
|
| 1519 |
fieldStmt.setString(2, objectFormatId.getValue()); |
|
| 1520 |
// count |
|
| 1521 |
countStmt.setTimestamp(1, new Timestamp(endTime.getTime())); |
|
| 1522 |
countStmt.setString(2, objectFormatId.getValue()); |
|
| 1508 | 1523 |
} else if (!f1 && !f2 && f3) {
|
| 1509 |
stmt.setString(1, objectFormatId.getValue()); |
|
| 1524 |
fieldStmt.setString(1, objectFormatId.getValue()); |
|
| 1525 |
// count |
|
| 1526 |
countStmt.setString(1, objectFormatId.getValue()); |
|
| 1510 | 1527 |
} else if (!f1 && f2 && !f3) {
|
| 1511 |
stmt.setTimestamp(1, new Timestamp(endTime.getTime())); |
|
| 1528 |
fieldStmt.setTimestamp(1, new Timestamp(endTime.getTime())); |
|
| 1529 |
// count |
|
| 1530 |
countStmt.setTimestamp(1, new Timestamp(endTime.getTime())); |
|
| 1512 | 1531 |
} |
| 1513 | 1532 |
|
| 1514 |
// logMetacat.debug("LISTOBJECTS QUERY: " + stmt.toString());
|
|
| 1533 |
// logMetacat.debug("LISTOBJECTS QUERY: " + fieldStmt.toString());
|
|
| 1515 | 1534 |
|
| 1516 |
ResultSet rs = stmt.executeQuery(); |
|
| 1517 |
|
|
| 1518 |
// get the total object count |
|
| 1519 |
String totalQuery = "select count(*) from systemmetadata"; |
|
| 1520 |
PreparedStatement statement = dbConn.prepareStatement(totalQuery); |
|
| 1521 |
ResultSet totalResult = statement.executeQuery(); |
|
| 1522 |
total = totalResult.getInt(1); |
|
| 1523 |
|
|
| 1524 |
// handle special count = 0 query here |
|
| 1525 |
if (count == 0) {
|
|
| 1526 |
ol.setStart(start); |
|
| 1527 |
ol.setCount(count); |
|
| 1528 |
ol.setTotal(total); |
|
| 1535 |
// get the total object count no matter what |
|
| 1536 |
int total = 0; |
|
| 1537 |
ResultSet totalResult = countStmt.executeQuery(); |
|
| 1538 |
if (totalResult.next()) {
|
|
| 1539 |
total = totalResult.getInt(1); |
|
| 1529 | 1540 |
} |
| 1530 |
else {
|
|
| 1531 | 1541 |
|
| 1542 |
// set the totals |
|
| 1543 |
ol.setStart(start); |
|
| 1544 |
ol.setCount(count); |
|
| 1545 |
ol.setTotal(total); |
|
| 1546 |
|
|
| 1547 |
// retrieve the actual records if requested |
|
| 1548 |
if (count != 0) {
|
|
| 1549 |
|
|
| 1550 |
ResultSet rs = fieldStmt.executeQuery(); |
|
| 1532 | 1551 |
while (rs.next()) {
|
| 1533 | 1552 |
|
| 1534 | 1553 |
String guid = rs.getString(1); |
| ... | ... | |
| 1580 | 1599 |
ol.addObjectInfo(oi); |
| 1581 | 1600 |
|
| 1582 | 1601 |
} |
| 1602 |
|
|
| 1603 |
// set the actual count retrieved |
|
| 1604 |
ol.setCount(ol.sizeObjectInfoList()); |
|
| 1583 | 1605 |
|
| 1584 | 1606 |
} |
| 1585 | 1607 |
|
| 1586 |
// set the objectList |
|
| 1587 |
ol.setStart(start); |
|
| 1588 |
ol.setCount(count); |
|
| 1589 |
ol.setTotal(ol.sizeObjectInfoList()); |
|
| 1590 | 1608 |
} |
| 1591 | 1609 |
|
| 1592 | 1610 |
finally {
|
Also available in: Unified diff
use dual query for query slicing - one for count, another for the actual records when requested.
https://redmine.dataone.org/issues/3065