Location: PHPKode > projects > DIY Blog > diy-blog/lib/creole/test/classes/creole/ResultSetTest.php
<?php

require_once 'creole/CreoleBaseTest.php';
include_once 'creole/PreparedStatementTest.php';

/**
 * Tests for the ResultSet class.
 * 
 * - test all the scrolling functions for correct behavior & return values
 * - test limit/offset (this is especially important for drivers that emulate this)
 * - test fetchmodes & test COMPAT_ASSOC_LOWER option on FETCHMODE_ASSOC
 * - test the field getters for correct formatting & correct exception throwing
 * - test close method
 * 
 * @author Hans Lellelid <hide@address.com>
 * @version $Revision: 1.19 $
 */
class ResultSetTest extends CreoleBaseTest {
    
    protected $conn;    
    
    /**
     * Re-initialize the database.
     * 
     * We only need to do this in setUp() method -- not in every invocation of this class --
     * since the ResultSet methods do not modify the db.
     */    
    public function setUp() {
        DriverTestManager::restore();
    }
    
    public function __construct() {
        $this->conn = DriverTestManager::getConnection();
    }   
    
    /**
     * Initialize the default resultset.
     * Not all methods need this initialized.
     */
    protected function allRs() {
        $exch = DriverTestManager::getExchange('ResultSetTest.ALL_RECORDS');
        $rs = $this->conn->executeQuery($exch->getSql(), ResultSet::FETCHMODE_NUM);
        return $rs;
    }            
    
    /**
     * Test the getRecordCount method.  Note that this will not work w/
     * unbuffered result sets ... e.g. I think Oracle.
     */
    public function testGetRecordCount() {
        // SELECT COUNT(*) ...
        $exch1 = DriverTestManager::getExchange('RecordCount');
        $rs = $this->conn->executeQuery($exch1->getSql(), ResultSet::FETCHMODE_NUM);
        $rs->next();
        $expected = $rs->getInt(1);
        
        // SELECT * ...
        $exch = DriverTestManager::getExchange('ResultSetTest.ALL_RECORDS');
        $rs = $this->conn->executeQuery($exch->getSql());
        $this->assertEquals($expected, $rs->getRecordCount());
    }
    
    public function testFetchmodeNum() {
    
        $exch = DriverTestManager::getExchange('ResultSetTest.ALL_RECORDS');
        $rs = $this->conn->executeQuery($exch->getSql(), ResultSet::FETCHMODE_NUM);
        $rs->next();
        $fields = $rs->getRow();
        $this->assertTrue( array_key_exists("0", $fields) ); 
        $this->assertTrue( !array_key_exists("ProductID", $fields) );
        $rs->close();
                                    
    }    

    public function testFetchmodeAssoc() {   
        $exch = DriverTestManager::getExchange('ResultSetTest.ALL_RECORDS');                
        $rs = $this->conn->executeQuery($exch->getSql(), ResultSet::FETCHMODE_ASSOC);
        $rs->next();
        $fields = $rs->getRow();
        $keys = array_keys($fields);
        $this->assertTrue( !array_key_exists("0", $fields), "Expected not to find '0' in fields array." );
        $this->assertEquals( "productid" , $keys[0], 0, "Expected to find lcase column name in field array.");
        $rs->close();
    }                   
    
    /**
     * Test an ASSOC fetch with a connection that does not have the Creole::COMPAT_ASSOC_LOWER flag set.
     */
    public function testFetchmodeAssocNoChange() {
    
        $exch = DriverTestManager::getExchange('ResultSetTest.ALL_RECORDS');
        
        $conn2 = Creole::getConnection(DriverTestManager::getDSN());        
        DriverTestManager::initDb($conn2);
        
        $rs = $conn2->executeQuery($exch->getSql(), ResultSet::FETCHMODE_ASSOC);
        $rs->next();
        $keys = array_keys($rs->getRow());
        $this->assertEquals("ProductID", $keys[0], 0, "Expected to find mixed-case column name.");
        $rs->close();
        
        // do NOT close the connection; in many cases both COnnection objects will share
        // the same db connection
                
    }
    
    /**
     * Test next() and bounded result sets.
     * We test to make sure that next() will loop until the end.
     */
    public function testNext() {
        $rs = $this->allRs();
        $i=0;
        while($rs->next()) $i++;
        $this->assertEquals($rs->getRecordCount(), $i);
        
        $rs->close();
    }
    
    /**
     * Ensures that results are no longer available after
     * closing a resultset.
     */
    public function testClose() {
        $rs = $this->allRs();
        $rs->next();
        $rs->close();
        try {
            $rs->get(1);
            $this->fail("Expected SQLException to be thrown for invalid column after closing ResultSet.");
        } catch (SQLException $e) {
            $this->expectException("Invalid resultset column", $e);
        }
    }
    
    /**
     * Test behavior of seek().  Note that the return results of seek
     * are not reliable for determining whether a cursor position exists.
     */
    public function testSeek() {
        $rs = $this->allRs();
        
        $rs->seek(0);
        $rs->next();
        
        $this->assertEquals(1, $rs->getInt(1));
        $rs->seek(3);
        $this->assertEquals(1, $rs->getInt(1), 0, "Expected to still find same value for get(1), since seek() isn't supposed to load row.");
        $rs->next();
        $this->assertEquals(4, $rs->getInt(1), 0, "Expected next() to now fetch 4 after call to seek(3)");
        
        $rs->close();
    }
    
    
    public function testIsBeforeFirst() {        
        $rs = $this->allRs();
        // before calling next() we can expect RS to be before first
        $this->assertTrue($rs->isBeforeFirst());       
        
        $rs->close();
    }
    
    public function testIsAfterLast() {
        $rs = $this->allRs();
        while($rs->next()); // advance to end        
        $this->assertTrue($rs->isAfterLast());
        
        $rs->close();
    }
    
    // these are not scrolling functions:
    
    public function testBeforeFirst() {
        $rs = $this->allRs();
        for($i=0;$i<10;$i++) { // advance a few positions
            $rs->next();
        }
        
        $rs->beforeFirst();
        $this->assertTrue($rs->isBeforeFirst());
        
        $rs->close();
    }
    
    
    public function testAfterLast() {
        $rs = $this->allRs();
        for($i=0;$i<10;$i++) { // advance a few positions
            $rs->next();
        }        
        $rs->afterLast();
        $this->assertTrue($rs->isAfterLast());    
        
        $rs->close();
    }
    
    //
    // scrolling functions -- do not work w/ all RDBMS, so must be overridden when applicable
    // 
    
    public function testPrevious() {
    
        $rs = $this->allRs();
        
        // advance to the fifth record, which will have ProductID of 5
        for($i=0;$i<5;$i++) $rs->next();
        
        $this->assertEquals(5, $rs->getInt(1));
        
        $rs->previous();
        
        $this->assertEquals(4, $rs->getInt(1));
                
        // now keep going back until false
        while($rs->previous());
        
        $this->assertTrue($rs->isBeforeFirst());
        
        $rs->close();
    }
    
    public function testRelative() {
    
        $rs = $this->allRs();
        
        $rs->next(); // advance one record
            
        // move ahead 5 spaces
        $rs->relative(5);
        $this->assertEquals(6, $rs->getInt(1));
        
        $rs->relative(-2);
        $this->assertEquals(4, $rs->getInt(1));
        
        
        $res = $rs->relative(200);
        $this->assertTrue($rs->isAfterLast());
        $this->assertFalse($res, "relative() should return false if offset after end of recordset");
        //$this->expectWarning('Offset after end of recordset', $rs);
        
        $res = $rs->relative(-200);
        $this->assertTrue($rs->isBeforeFirst());
        $this->assertFalse($res, "relative() should return false if offset before start of recordset");
        //$this->expectWarning('Offset before start of recordset', $rs);
        
        $rs->relative(2);
        $this->assertEquals(2, $rs->getInt(1));        
        
        $rs->close();
    }
    
    public function testAbsolute() {        
        $rs = $this->allRs();
         // advance to the fifth record, which will have ProductID of 5
        $rs->absolute(5);
        $this->assertEquals(5, $rs->getInt(1));
        
        $rs->absolute(50);
        $this->assertEquals(50, $rs->getInt(1));
        
        $res = $rs->absolute(300);
        $this->assertTrue($rs->isAfterLast());
        $this->assertFalse($res, "absolute() should return false if pos is after end of recordset"); // returns false if offset is after last or before first
        //$this->expectWarning('Offset after end of recordset', $rs);
        
        $res = $rs->absolute(0);
        $this->assertTrue($rs->isBeforeFirst());
        $this->assertFalse($res, "absolute() should return false if offset is before start of recordset"); // returns false if offset is after last or before first
        //$this->expectWarning('Offset before start of recordset', $rs);
        
        $res = $rs->absolute(-2);
        $this->assertTrue($rs->isBeforeFirst());
        $this->assertFalse($res, "absolute() should return false if offset is before start of recordset"); // returns false if offset is after last or before first
        //$this->expectWarning('Offset before start of recordset', $rs);
        
        $rs->close();            
    }
    
    public function testFirst() {    
        $rs = $this->allRs();
        
        $exch = DriverTestManager::getExchange('ResultSetTest.MIN_ID');
        $minRs = $this->conn->executeQuery($exch->getSql(), ResultSet::FETCHMODE_NUM);
        $minRs->next();
        $min = $minRs->get(1);
        
        $rs->first();
        $this->assertEquals($min, $rs->get(1));
        
        $rs->close();
    }
    
    public function testLast() {
        $rs = $this->allRs();
              
        $exch = DriverTestManager::getExchange('ResultSetTest.MAX_ID');
        $maxRs = $this->conn->executeQuery($exch->getSql(), ResultSet::FETCHMODE_NUM);
        $maxRs->next();
        $max = $maxRs->get(1);
        
        $rs->last();
        $this->assertEquals($max, $rs->get(1));
        
        $rs->close();
    }    
    
    /**
     * This test is primarily to test emulated LIMIT/OFFSET. 
     * 
     * It will, of course, test the natively supported LIMIT/OFFSET, but
     * the real potential for issues lies in the drivers that emulate these.
     * 
     * This class only uses forward-scrolling cursor functions.
     * @see testLimitScrollBackwards
     */
    public function testLimit() {
        
        $exch = DriverTestManager::getExchange('ResultSetTest.ALL_RECORDS');
        $stmt = $this->conn->createStatement();
        $stmt->setLimit(10);
        $stmt->setOffset(5);
         
        // 1) make sure contains right number of rows
            $rs1 = $stmt->executeQuery($exch->getSql(), ResultSet::FETCHMODE_NUM);
            $count = 0;
            while($rs1->next()) $count++;            
            $this->assertEquals(10, $count, 0, "LIMITed resultset contains wrong number of rows.");
            $rs1->close();
            unset($rs1);
        
        // 2) make sure that first record is the correct one
            // using next()
            $rs2 = $stmt->executeQuery($exch->getSql(), ResultSet::FETCHMODE_NUM);
            $rs2->next();
                // first() relative() and absolute() handled by testLimitScrollBackwards()
            $this->assertEquals(6, $rs2->getInt(1), 0, "LIMITed resultset starts on the wrong row.");
            $rs2->close();
            unset($rs2);
        
        // 3) make sure that the last record is the correct one.
            $rs3 = $stmt->executeQuery($exch->getSql(), ResultSet::FETCHMODE_NUM);
            while($rs3->next()) { $last = $rs3->getInt(1); }
            $this->assertEquals(15, $last, 0, "LIMITed resultset ends on the wrong row.");
            $rs3->close();
            unset($rs3);
            
            $rs4 = $stmt->executeQuery($exch->getSql(), ResultSet::FETCHMODE_NUM);
            $rs4->last();
            $this->assertEquals(15, $rs4->getInt(1), 0, "LIMITed resultset ends on the wrong row.");
            $rs4->close();
            unset($rs4);     
                                   
        // 4) make sure that the relative() and absolute() (forward) method will report appropriate end
           
            $rs5 = $stmt->executeQuery($exch->getSql(), ResultSet::FETCHMODE_NUM);
            $res = $rs5->absolute(11);
            //$this->expectWarning('Offset after end of recordset',$rs5);
            $this->assertFalse($res, "absolute() should return false when after end of resultset");
            $rs5->close();
            unset($rs5);     
            
            $rs6 = $stmt->executeQuery($exch->getSql(), ResultSet::FETCHMODE_NUM);
            $res = $rs6->relative(11);
            $this->assertFalse($res, "relative() should return false when after end of resultset");
            //$this->expectWarning('Offset after end of recordset', $rs6);
            $rs6->close();
            unset($rs6);
            
        $stmt->close();
    }
    
    /**
     * Continues LIMIT tests, but using backwards-scrolling methods.
     * 
     * Some RDBMS drivers don't support backwards scrolling; they'll need
     * to override this method.
     */
    public function testLimitScrollBackwards() {
    
        $exch = DriverTestManager::getExchange('ResultSetTest.ALL_RECORDS');
        $stmt = $this->conn->createStatement();
        $stmt->setLimit(10);
        $stmt->setOffset(5);
                        
        // using next()
        $rs2 = $stmt->executeQuery($exch->getSql(), ResultSet::FETCHMODE_NUM);
        $rs2->first();            
        $this->assertEquals(6, $rs2->getInt(1), 0, "LIMITed resultset starts on the wrong row.");
        $rs2->close();
        unset($rs2);

        $rs3 = $stmt->executeQuery($exch->getSql(), ResultSet::FETCHMODE_NUM);
        for($i=0;$i<3;$i++) $rs3->next(); // move ahead 3 spaces            
        $res = $rs3->relative(-4);
        $this->assertFalse($res, "relative() should return false when before start of resultset");
        //$this->expectWarning('Offset before start of recordset', $rs3);
        $rs3->close();          
        unset($rs3);
        
        $rs4 = $stmt->executeQuery($exch->getSql(), ResultSet::FETCHMODE_NUM);
        for($i=0;$i<3;$i++) $rs4->next(); // move ahead 3 spaces            
        $res = $rs4->absolute(-1);
        $this->assertFalse($res, "absolute() should return false when before start of resultset");
        //$this->expectWarning('Offset before start of recordset', $rs4);
        $rs4->close();       
        unset($rs4);
        
        $stmt->close();
    }
    
    //
    // column accessors -- many of these will be overridden in driver classes so
    // that derived values can be checked against native values in DB.
    //
    
    public function testGet() {   
        $exch = DriverTestManager::getExchange('ResultSetTest.SINGLE_RECORD');
        $rs = $this->conn->executeQuery(sprintf($exch->getSql(), 1), ResultSet::FETCHMODE_NUM);
        $rs->next();         
        $this->assertEquals(1, $rs->getInt(1));
        
        $rs->close();
    }

    public function testGetArray() {
        // coming soon
    }
    
    public function testGetBoolean() {
        $exch = DriverTestManager::getExchange('ResultSetTest.getBoolean.FALSE');
        $rs = $this->conn->executeQuery($exch->getSql(), ResultSet::FETCHMODE_NUM);
        $rs->next();        
        $this->assertTrue($rs->getBoolean(1) === false, "Expected answer to be false, was: " . $rs->getBoolean(1));
        
        // avoid using absolute() or relative() because not all drivers support it.
        $exch = DriverTestManager::getExchange('ResultSetTest.getBoolean.TRUE');
        $rs = $this->conn->executeQuery($exch->getSql(), ResultSet::FETCHMODE_NUM);
        $rs->next();
        $this->assertTrue($rs->getBoolean(1) === true);
        try {
            $rs->getBoolean("productid");
            $this->fail("Expected SQLException to be thrown for invalid column.");
        } catch (SQLException $e) {
            $this->expectException("Invalid resultset column", $e);
        }
        
        $rs->close();
    }
    
    public function getBlob() {
        $exch = DriverTestManager::getExchange('ResultSetTest.getBlob');
        $rs = $this->conn->executeQuery($exch->getSql(), ResultSet::FETCHMODE_NUM);
        $rs->next();
        $b = $rs->getBlob(1);
        $rs->close();
        return $b;
    }

    public function getClob() {
        $exch = DriverTestManager::getExchange('ResultSetTest.getClob');
        $rs = $this->conn->executeQuery($exch->getSql(), ResultSet::FETCHMODE_NUM);
        $rs->next();
        $c = $rs->getClob(1);
        $rs->close();
        return $c;
    }
    
    /**
     * This function depends on ability to set Blob values -- so 
     * PreparedStatement::setBlob() is also implicitly tested.
     */
    public function testGetBlob() {    
        $pst = new PreparedStatementTest();
        $b1 = $pst->createBlob();
        $pst->setBlob($b1);
        
        $b2 = $this->getBlob();
        $this->assertEquals(strlen($b1->getContents()), strlen($b2->getContents()), 0, "BLOB lengths do not match.");
        $this->assertEquals(md5($b1->getContents()), md5($b2->getContents()), 0, "BLOB contents do not match.");
    }
    
    /**
     * This function depends on ability to set Blob values -- so 
     * PreparedStatement::setBlob() is also implicitly tested.
     */
    public function testGetClob() {        
        $pst = new PreparedStatementTest();
        $b1 = $pst->createClob();
        $pst->setClob($b1);
        
        $b2 = $this->getClob();        
        $this->assertEquals(strlen($b1->getContents()), strlen($b2->getContents()), 0, "CLOB lengths do not match.");
        $this->assertEquals(md5($b1->getContents()), md5($b2->getContents()), 0, "CLOB contents do not match.");
    }

    public function testGetDate() {
        $exch = DriverTestManager::getExchange('ResultSetTest.getDate');
        $rs = $this->conn->executeQuery($exch->getSql(), ResultSet::FETCHMODE_NUM);
        $rs->next();
        
        $result_ts = strtotime($exch->getResult());        
        $ts = (int) $rs->getDate(1, "U");
        
        $this->assertEquals($result_ts, $ts);        
        
        $this->assertEquals(strftime("%x", $result_ts), $rs->getDate(1, "%x"));        
                
        try {
            $rs->getDate("orderdate");
            $this->fail("Expected SQLException to be thrown for invalid column.");
        } catch (SQLException $e) {
            $this->expectException("Invalid resultset column", $e);
        }
        
        $rs->close();

        // try w/ invalid date
        try {
            $exch = DriverTestManager::getExchange('ResultSetTest.getString');
            $rs = $this->conn->executeQuery($exch->getSql(), ResultSet::FETCHMODE_NUM);
            $rs->next();
            $rs->getDate(1);
            $this->fail("Expected SQLException to be thrown for bad date type.");
        } catch (SQLException $e) {
            $this->expectException("Unable to convert value", $e);
        }
        
        $rs->close();
                
    }        

    public function testGetFloat() {
        $exch = DriverTestManager::getExchange('ResultSetTest.getFloat');
        $rs = $this->conn->executeQuery($exch->getSql(), ResultSet::FETCHMODE_NUM);
        $rs->next();
        
        $exp_val = (float) $exch->getResult();
        
        $this->assertEquals($exp_val, $rs->getFloat(1));
        
        try {
            $rs->getFloat("UnitPrice");
            $this->fail("Expected SQLException to be thrown for invalid column.");
        } catch (SQLException $e) {
            $this->expectException("Invalid resultset column", $e);
        }
        
        $rs->close();
    }
        
    public function testGetInt() {
        $exch = DriverTestManager::getExchange('ResultSetTest.getInt');
        $rs = $this->conn->executeQuery($exch->getSql(), ResultSet::FETCHMODE_NUM);
        $rs->next();    
        
        $exp_val = (int) $exch->getResult();
        
        $this->assertEquals($exp_val, $rs->getInt(1));        
        
        try {
            $rs->getInt("UnitsOnOrder");
            $this->fail("Expected SQLException to be thrown for invalid column.");
        } catch (SQLException $e) {
            $this->expectException("Invalid resultset column", $e);
        }
        
        $rs->close();
    }

    public function testGetString() {
        $exch = DriverTestManager::getExchange('ResultSetTest.getString');
        $rs = $this->conn->executeQuery($exch->getSql(), ResultSet::FETCHMODE_NUM);
        $rs->next();
        
         $exp_val = $exch->getResult();
        
        $this->assertEquals($exp_val, $rs->getString(1));
        
        try {
            $rs->getString("ProductName");
            $this->fail("Expected SQLException to be thrown for invalid column.");
        } catch (SQLException $e) {
            $this->expectException("Invalid resultset column", $e);
        }
        
        $rs->close();
    }

    public function testGetTime() {        
        
        // coming soon ... 
        
        // try w/ invalid time
        try {
            $exch = DriverTestManager::getExchange('ResultSetTest.getString');
            $rs = $this->conn->executeQuery($exch->getSql(), ResultSet::FETCHMODE_NUM);
            $rs->next();
            $rs->getTime(1);
            $this->fail("Expected SQLException to be thrown for bad date type.");
        } catch (SQLException $e) {
            $this->expectException("Unable to convert value", $e);
        }

    }
    
    public function testGetTimestamp() {
        
        // coming soon ...
                
        // try w/ invalid timestamp
        try {
            $exch = DriverTestManager::getExchange('ResultSetTest.getString');
            $rs = $this->conn->executeQuery($exch->getSql(), ResultSet::FETCHMODE_NUM);
            $rs->next();
            $rs->getTimestamp(1);
            $this->fail("Expected SQLException to be thrown for bad date type.");
        } catch (SQLException $e) {
            $this->expectException("Unable to convert value", $e);
        }

    }
    
    /**
     * Make sure that get() and getString() are returning properly rtrimmed results.
     */
    public function testTrimmedGet() {
        
        $conn = Creole::getConnection(DriverTestManager::getDSN(), Creole::COMPAT_RTRIM_STRING);
		DriverTestManager::initDb($conn);
		
		$exch = DriverTestManager::getExchange('ResultSetTest.setString.RTRIM');
        $stmt = $conn->prepareStatement($exch->getSql());
        $stmt->setString(1, "TEST    ");
        $stmt->setInt(2, 1);
        $stmt->executeUpdate();
        $stmt->close();
		     
        $exch = DriverTestManager::getExchange('ResultSetTest.getString.RTRIM');
        $stmt = $conn->prepareStatement($exch->getSql());
        $stmt->setInt(1, 1);
        $rs = $stmt->executeQuery(ResultSet::FETCHMODE_NUM);
        $rs->next();
        $this->assertEquals("TEST", $rs->getString(1));
        
        $stmt->close();
        $rs->close();
    }
	
	/**
     * Make sure that get() and getString() are returning properly rtrimmed results.
     */
    public function testUntrimmedGet() {
        
		$str = "TEST    ";
		
        $exch = DriverTestManager::getExchange('ResultSetTest.setString.RTRIM');
        $stmt = $this->conn->prepareStatement($exch->getSql());
        $stmt->setString(1, $str);
        $stmt->setInt(2, 1);
        $stmt->executeUpdate();
        $stmt->close();
                            
        $exch = DriverTestManager::getExchange('ResultSetTest.getString.RTRIM');
        $stmt = $this->conn->prepareStatement($exch->getSql());
        $stmt->setInt(1, 1);
        $rs = $stmt->executeQuery(ResultSet::FETCHMODE_NUM);
        $rs->next();
        $this->assertEquals($str, $rs->getString(1));
        
        $stmt->close();
        $rs->close();
    }
}
Return current item: DIY Blog