Project

General

Profile

Revision 7410

handle /object?count=0 queries using simpler (quicker) sql
https://redmine.dataone.org/issues/3065

View differences:

src/edu/ucsb/nceas/metacat/IdentifierManager.java
1429 1429
            String sql = "select guid, date_uploaded, rights_holder, checksum, "
1430 1430
                    + "checksum_algorithm, origin_member_node, authoritive_member_node, "
1431 1431
                    + "date_modified, submitter, object_format, size from systemmetadata";
1432
            
1433
            // handle special case quickly
1434
            String countSql = "select count(guid) from systemmetadata";
1435
            if (count == 0) {
1436
            	sql = countSql;
1437
            }
1432 1438

  
1433 1439
            boolean f1 = false;
1434 1440
            boolean f2 = false;
......
1469 1475
            }
1470 1476

  
1471 1477
            // order the results for slicing ops
1472
            sql += " order by guid ";
1478
            if (count != 0) {
1479
            	sql += " order by guid ";
1480
            }
1473 1481
            
1474 1482
            dbConn = DBConnectionPool.getDBConnection("IdentifierManager.querySystemMetadata");
1475 1483
            serialNumber = dbConn.getCheckOutSerialNumber();
......
1499 1507
            // logMetacat.debug("LISTOBJECTS QUERY: " + stmt.toString());
1500 1508

  
1501 1509
            ResultSet rs = stmt.executeQuery();
1502
            for (int i = 0; i < start; i++) {
1503
                if (rs.next()) {
1504
                    total++;
1505
                } else {
1506
                    break;
1507
                }
1510
            // handle special count = 0 query here
1511
            if (count == 0) {
1512
            	while (rs.next()) {
1513
            		total = rs.getInt(1);
1514
            		break;
1515
            	}
1516
            	ol.setStart(start);
1517
                ol.setCount(count);
1518
                ol.setTotal(total);
1508 1519
            }
1509

  
1510
            int countIndex = 0;
1511

  
1512
            while (rs.next()) {                
1513
                total++;
1514
                if (countIndex >= count) {
1515
                    // allow unlimited (negative number for count)
1516
                    if (count > 0) {
1517
                        break;
1518
                    }
1519
                }
1520

  
1521
                String guid = rs.getString(1);
1522
                // logMetacat.debug("query found doc with guid " + guid);
1523
                // Timestamp dateUploaded = rs.getTimestamp(2);
1524
                // String rightsHolder = rs.getString(3);
1525
                String checksum = rs.getString(4);
1526
                String checksumAlgorithm = rs.getString(5);
1527
                // String originMemberNode = rs.getString(6);
1528
                // String authoritiveMemberNode = rs.getString(7);
1529
                Timestamp dateModified = rs.getTimestamp(8);
1530
                // String submitter = rs.getString(9);
1531
                String fmtidStr = rs.getString(10);
1532
                String sz = rs.getString(11);
1533
                BigInteger size = new BigInteger("0");
1534

  
1535
                if (sz != null && !sz.trim().equals("")) {
1536
                    size = new BigInteger(rs.getString(11));
1537
                }
1538

  
1539
                ObjectInfo oi = new ObjectInfo();
1540

  
1541
                Identifier id = new Identifier();
1542
                id.setValue(guid);
1543
                oi.setIdentifier(id);
1544

  
1545
                if (dateModified != null) {
1546
                    oi.setDateSysMetadataModified(dateModified);
1547
                }
1548

  
1549
                Checksum cs = new Checksum();
1550
                cs.setValue(checksum);
1551
                try {
1552
                    // cs.setAlgorithm(ChecksumAlgorithm.valueOf(checksumAlgorithm));
1553
                    cs.setAlgorithm(checksumAlgorithm);
1554
                } catch (Exception e) {
1555
                    logMetacat.error("could not parse checksum algorithm", e);
1556
                    continue;
1557
                }
1558
                oi.setChecksum(cs);
1559

  
1560
                // set the format type
1561
                ObjectFormatIdentifier fmtid = new ObjectFormatIdentifier();
1562
                fmtid.setValue(fmtidStr);
1563
                oi.setFormatId(fmtid);
1564

  
1565
                oi.setSize(size);
1566

  
1567
                // when requested count == 0, return an empty object list
1568
                if (count != 0) {
1569
                    ol.addObjectInfo(oi);                    
1570
                }
1571
                countIndex++;
1572
            }
1573

  
1574
            // expend the resultset to get the total count of possible rows
1575
            while (rs.next()) {
1576
                total++;
1577
            }
1520
            else {
1521
            	// do the full query
1522
	            for (int i = 0; i < start; i++) {
1523
	                if (rs.next()) {
1524
	                    total++;
1525
	                } else {
1526
	                    break;
1527
	                }
1528
	            }
1529
	
1530
	            int countIndex = 0;
1531
	
1532
	            while (rs.next()) {                
1533
	                total++;
1534
	                if (countIndex >= count) {
1535
	                    // allow unlimited (negative number for count)
1536
	                    if (count > 0) {
1537
	                        break;
1538
	                    }
1539
	                }
1540
	
1541
	                String guid = rs.getString(1);
1542
	                // logMetacat.debug("query found doc with guid " + guid);
1543
	                // Timestamp dateUploaded = rs.getTimestamp(2);
1544
	                // String rightsHolder = rs.getString(3);
1545
	                String checksum = rs.getString(4);
1546
	                String checksumAlgorithm = rs.getString(5);
1547
	                // String originMemberNode = rs.getString(6);
1548
	                // String authoritiveMemberNode = rs.getString(7);
1549
	                Timestamp dateModified = rs.getTimestamp(8);
1550
	                // String submitter = rs.getString(9);
1551
	                String fmtidStr = rs.getString(10);
1552
	                String sz = rs.getString(11);
1553
	                BigInteger size = new BigInteger("0");
1554
	
1555
	                if (sz != null && !sz.trim().equals("")) {
1556
	                    size = new BigInteger(rs.getString(11));
1557
	                }
1558
	
1559
	                ObjectInfo oi = new ObjectInfo();
1560
	
1561
	                Identifier id = new Identifier();
1562
	                id.setValue(guid);
1563
	                oi.setIdentifier(id);
1564
	
1565
	                if (dateModified != null) {
1566
	                    oi.setDateSysMetadataModified(dateModified);
1567
	                }
1568
	
1569
	                Checksum cs = new Checksum();
1570
	                cs.setValue(checksum);
1571
	                try {
1572
	                    // cs.setAlgorithm(ChecksumAlgorithm.valueOf(checksumAlgorithm));
1573
	                    cs.setAlgorithm(checksumAlgorithm);
1574
	                } catch (Exception e) {
1575
	                    logMetacat.error("could not parse checksum algorithm", e);
1576
	                    continue;
1577
	                }
1578
	                oi.setChecksum(cs);
1579
	
1580
	                // set the format type
1581
	                ObjectFormatIdentifier fmtid = new ObjectFormatIdentifier();
1582
	                fmtid.setValue(fmtidStr);
1583
	                oi.setFormatId(fmtid);
1584
	
1585
	                oi.setSize(size);
1586
	
1587
	                // when requested count == 0, return an empty object list
1588
	                if (count != 0) {
1589
	                    ol.addObjectInfo(oi);                    
1590
	                }
1591
	                countIndex++;
1592
	            }
1593
	
1594
	            // expend the resultset to get the total count of possible rows
1595
	            while (rs.next()) {
1596
	                total++;
1597
	            }
1598
	        }
1599
            
1600
            // set the objectList
1601
            ol.setStart(start);
1602
            ol.setCount(ol.sizeObjectInfoList());
1603
            ol.setTotal(total);
1578 1604
        }
1579 1605

  
1580 1606
        finally {
......
1582 1608
            DBConnectionPool.returnDBConnection(dbConn, serialNumber);
1583 1609
        }
1584 1610

  
1585
        ol.setStart(start);
1586
        ol.setCount(ol.sizeObjectInfoList());
1587
        ol.setTotal(total);
1588

  
1589 1611
        return ol;
1590 1612
    }
1591 1613
    

Also available in: Unified diff