repo_zf1/tests/Zend/Db/Adapter/SqlsrvTest.php

<?php
/**
 * Zend Framework
 *
 * LICENSE
 *
 * This source file is subject to the new BSD license that is bundled
 * with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://framework.zend.com/license/new-bsd
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@zend.com so we can send you a copy immediately.
 *
 * @category   Zend
 * @package    Zend_Db
 * @subpackage UnitTests
 * @copyright  Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)
 * @license    http://framework.zend.com/license/new-bsd     New BSD License
 * @version    $Id$
 */

/**
 * @see Zend_Db_Adapter_TestCommon
 */
require_once 'Zend/Db/Adapter/TestCommon.php';

/**
 * @see Zend_Db_Adapter_Sqlsrv
 */
require_once 'Zend/Db/Adapter/Sqlsrv.php';

PHPUnit_Util_Filter::addFileToFilter(__FILE__);

/**
 * @category   Zend
 * @package    Zend_Db
 * @subpackage UnitTests
 * @copyright  Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)
 * @license    http://framework.zend.com/license/new-bsd     New BSD License
 */
class Zend_Db_Adapter_SqlsrvTest extends Zend_Db_Adapter_TestCommon
{
    protected $_numericDataTypes = array(
        Zend_Db::INT_TYPE    => Zend_Db::INT_TYPE,
        Zend_Db::BIGINT_TYPE => Zend_Db::BIGINT_TYPE,
        Zend_Db::FLOAT_TYPE  => Zend_Db::FLOAT_TYPE,
        'INT'                => Zend_Db::INT_TYPE,
        'SMALLINT'           => Zend_Db::INT_TYPE,
        'TINYINT'            => Zend_Db::INT_TYPE,
        'BIGINT'             => Zend_Db::BIGINT_TYPE,
        'DECIMAL'            => Zend_Db::FLOAT_TYPE,
        'FLOAT'              => Zend_Db::FLOAT_TYPE,
        'MONEY'              => Zend_Db::FLOAT_TYPE,
        'NUMERIC'            => Zend_Db::FLOAT_TYPE,
        'REAL'               => Zend_Db::FLOAT_TYPE,
        'SMALLMONEY'         => Zend_Db::FLOAT_TYPE
    );

    /**
     * Test AUTO_QUOTE_IDENTIFIERS option
     * Case: Zend_Db::AUTO_QUOTE_IDENTIFIERS = true
     */
    public function testAdapterAutoQuoteIdentifiersTrue()
    {
        $params = $this->_util->getParams();

        $params['options'] = array(
            Zend_Db::AUTO_QUOTE_IDENTIFIERS => true
        );
        $db = Zend_Db::factory($this->getDriver(), $params);
        $db->getConnection();

        $select = $this->_db->select();
        $select->from('zfproducts');
        $stmt = $this->_db->query($select);
        $result = $stmt->fetchAll();
        $this->assertEquals(3, count($result), 'Expected 3 rows in first query result');

        $this->assertEquals(1, $result[0]['product_id']);
    }

    /**
     * Test the Adapter's insert() method.
     * This requires providing an associative array of column=>value pairs.
     */
    public function testAdapterInsert()
    {
        $row = array (
            'bug_description' => 'New bug',
            'bug_status'      => 'NEW',
            'created_on'      => '2007-04-02',
            'updated_on'      => '2007-04-02',
            'reported_by'     => 'micky',
            'assigned_to'     => 'goofy',
            'verified_by'     => 'dduck'
        );

        $rowsAffected = $this->_db->insert('zfbugs', $row);
        $this->assertEquals(1, $rowsAffected);

        $lastInsertId = $this->_db->lastInsertId();
        $this->assertType('string', $lastInsertId);
        $this->assertEquals('5', (string) $lastInsertId,
            'Expected new id to be 5');

        $lastInsertId = $this->_db->lastInsertId('zfbugs');
        $this->assertEquals('5', (string) $lastInsertId,
            'Expected new id to be 5, selecting by table');
    }

    /**
     * Test the Adapter's insert() method.
     * This requires providing an associative array of column=>value pairs.
     * Multiple rows are insert in one query
     */
    public function testAdapterMultipleInsert()
    {
        $row = array (
            'bug_description' => 'New bug',
            'bug_status'      => 'NEW',
            'created_on'      => '2007-04-02',
            'updated_on'      => '2007-04-02',
            'reported_by'     => 'micky',
            'assigned_to'     => 'goofy',
            'verified_by'     => 'dduck'
        );

        $bugs = $this->_db->quoteIdentifier('zfbugs');

        $values = '(?, ?, ?, ?, ?, ?, ?)';

        $query = 'INSERT INTO ' . $bugs . ' VALUES ' . implode(',', array($values, $values, $values));

        $data = array();

        for ($i = 0; $i < 3; $i++) {
            foreach ($row as $value) {
                $data[] = $value;
            }
        }

        $stmt = $this->_db->query($query, $data);
        $rowsAffected = $stmt->rowCount();
        $this->assertEquals(3, $rowsAffected);
    }

    public function testAdapterDescribeTableAttributeColumn()
    {
        $desc = $this->_db->describeTable('zfproducts');

        $this->assertEquals('zfproducts',   $desc['product_name']['TABLE_NAME']);
        $this->assertEquals('product_name', $desc['product_name']['COLUMN_NAME']);
        $this->assertEquals(2,              $desc['product_name']['COLUMN_POSITION']);
        $this->assertRegExp('/varchar/i',   $desc['product_name']['DATA_TYPE']);
        $this->assertEquals('',             $desc['product_name']['DEFAULT']);
        $this->assertTrue($desc['product_name']['NULLABLE'], 'Expected product_name to be nullable');
        $this->assertNull($desc['product_name']['SCALE'], 'scale is not 0');

        // MS SQL Server reports varchar length in the PRECISION field.  Whaaa?!?
        $this->assertEquals(100, $desc['product_name']['PRECISION'], 'precision is not 100');
        $this->assertFalse($desc['product_name']['PRIMARY'], 'Expected product_name not to be a primary key');
        $this->assertNull($desc['product_name']['PRIMARY_POSITION'], 'Expected product_name to return null for PRIMARY_POSITION');
        $this->assertFalse($desc['product_name']['IDENTITY'], 'Expected product_name to return false for IDENTITY');
    }

    /**
     * test that describeTable() returns empty array on not existing table
     * @group ZF-9079
     */
    public function testAdapterDescribeTableNotExistingTable()
    {
        $desc = $this->_db->describeTable('not_existing_table');

        $this->assertEquals(0, count($desc), 'Expected to have empty result');
    }

    public function testAdapterDescribeTablePrimaryKeyColumn()
    {
        $desc = $this->_db->describeTable('zfproducts');

        $this->assertEquals('zfproducts', $desc['product_id']['TABLE_NAME']);
        $this->assertEquals('product_id', $desc['product_id']['COLUMN_NAME']);
        $this->assertEquals(1,            $desc['product_id']['COLUMN_POSITION']);
        $this->assertEquals('',           $desc['product_id']['DEFAULT']);
        $this->assertFalse($desc['product_id']['NULLABLE'], 'Expected product_id not to be nullable');
        $this->assertEquals(0,            $desc['product_id']['SCALE'], 'scale is not 0');
        $this->assertEquals(10,           $desc['product_id']['PRECISION'], 'precision is not 10');
        $this->assertTrue($desc['product_id']['PRIMARY'], 'Expected product_id to be a primary key');
        $this->assertEquals(1,            $desc['product_id']['PRIMARY_POSITION']);
    }

    /**
     * Test that quote() takes an array and returns
     * an imploded string of comma-separated, quoted elements.
     */
    public function testAdapterQuoteArray()
    {
        $array = array("it's", 'all', 'right!');
        $value = $this->_db->quote($array);
        $this->assertEquals("'it''s', 'all', 'right!'", $value);
    }

    /**
     * test that quote() escapes a double-quote
     * character in a string.
     */
    public function testAdapterQuoteDoubleQuote()
    {
        $string = 'St John"s Wort';
        $value  = $this->_db->quote($string);
        $this->assertEquals("'St John\"s Wort'", $value);
    }

    /**
     * test that quote() escapes a single-quote
     * character in a string.
     */
    public function testAdapterQuoteSingleQuote()
    {
        $string = "St John's Wort";
        $value  = $this->_db->quote($string);
        $this->assertEquals("'St John''s Wort'", $value);
    }

    /**
     * test that quoteInto() escapes a double-quote
     * character in a string.
     */
    public function testAdapterQuoteIntoDoubleQuote()
    {
        $string = 'id=?';
        $param  = 'St John"s Wort';
        $value  = $this->_db->quoteInto($string, $param);
        $this->assertEquals("id='St John\"s Wort'", $value);
    }

    /**
     * test that quoteInto() escapes a single-quote
     * character in a string.
     */
    public function testAdapterQuoteIntoSingleQuote()
    {
        $string = 'id = ?';
        $param  = 'St John\'s Wort';
        $value  = $this->_db->quoteInto($string, $param);
        $this->assertEquals("id = 'St John''s Wort'", $value);
    }

    public function testAdapterInsertSequence()
    {
        $this->markTestSkipped($this->getDriver() . ' does not support sequences.');
    }

    public function testAdapterInsertDbExpr()
    {
        $bugs   = $this->_db->quoteIdentifier('zfbugs');
        $bug_id = $this->_db->quoteIdentifier('bug_id');
        $expr   = new Zend_Db_Expr('2+3');

        $row = array (
            'bug_id'          => $expr,
            'bug_description' => 'New bug',
            'bug_status'      => 'NEW',
            'created_on'      => '2007-04-02',
            'updated_on'      => '2007-04-02',
            'reported_by'     => 'micky',
            'assigned_to'     => 'goofy',
            'verified_by'     => 'dduck'
        );

        $this->_db->query("SET IDENTITY_INSERT $bugs ON");

        $rowsAffected = $this->_db->insert('zfbugs', $row);
        $this->assertEquals(1, $rowsAffected);

        $this->_db->query("SET IDENTITY_INSERT $bugs OFF");

        $value = $this->_db->fetchOne("SELECT $bug_id FROM $bugs WHERE $bug_id = 5");
        $this->assertEquals(5, $value);
    }

    /**
     * @group ZF-1541
     */
    public function testCharacterSetUtf8()
    {
        // Create a new adapter
        $params = $this->_util->getParams();

        $params['charset'] = 'utf8';

        $db = Zend_Db::factory($this->getDriver(), $params);

         // create a new util object, with the new db adapter
        $driver    = $this->getDriver();
        $utilClass = "Zend_Db_TestUtil_{$driver}";
        $util      = new $utilClass();
        $util->setAdapter($db);

        // create test table using no identifier quoting
        $util->createTable('charsetutf8', array(
            'id'    => 'IDENTITY',
            'stuff' => 'VARCHAR(32)'
        ));
        $tableName = $this->_util->getTableName('charsetutf8');

        $table = $db->quoteIdentifier('charsetutf8');

        $db->query("SET IDENTITY_INSERT $table ON");

        // insert into the table
        $numRows = $db->insert($tableName, array(
            'id'    => 1,
            'stuff' => 'äöüß'
        ));

        // check if the row was inserted as expected
        $select = $db->select()->from($tableName, array('id', 'stuff'));

        $stmt = $db->query($select);
        $fetched = $stmt->fetchAll(Zend_Db::FETCH_NUM);
        $a = array(
            0 => array(0 => 1, 1 => 'äöüß')
        );
        $this->assertEquals($a, $fetched,
            'result of query not as expected');

        $db->query("SET IDENTITY_INSERT $table OFF");

        // clean up
        unset($stmt);
        $util->dropTable($tableName);
    }

    public function testAdapterTransactionCommit()
    {
        $bugs   = $this->_db->quoteIdentifier('zfbugs');
        $bug_id = $this->_db->quoteIdentifier('bug_id');

        // notice the number of rows in connection 2
        $count = $this->_db->fetchOne("SELECT COUNT(*) FROM $bugs");
        $this->assertEquals(4, $count, 'Expecting to see 4 rows in bugs table (step 1)');

        // start an explicit transaction in connection 1
        $this->_db->beginTransaction();

        // delete a row in connection 1
        $rowsAffected = $this->_db->delete(
            'zfbugs',
            "$bug_id = 1"
        );
        $this->assertEquals(1, $rowsAffected);

        // we should still see all rows in connection 2
        // because the DELETE has not been committed yet
        $count = $this->_db->fetchOne("SELECT COUNT(*) FROM $bugs");
        $this->assertEquals(3, $count, 'Expecting to still see 4 rows in bugs table (step 2); perhaps Adapter is still in autocommit mode?');

        // commit the DELETE
        $this->_db->commit();

        // now we should see one fewer rows in connection 2
        $count = $this->_db->fetchOne("SELECT COUNT(*) FROM $bugs");
        $this->assertEquals(3, $count, 'Expecting to see 3 rows in bugs table after DELETE (step 3)');

        // delete another row in connection 1
        $rowsAffected = $this->_db->delete(
            'zfbugs',
            "$bug_id = 2"
        );
        $this->assertEquals(1, $rowsAffected);

        // we should see results immediately, because
        // the db connection returns to auto-commit mode
        $count = $this->_db->fetchOne("SELECT COUNT(*) FROM $bugs");
        $this->assertEquals(2, $count);
    }

    public function testAdapterTransactionRollback()
    {
        $bugs   = $this->_db->quoteIdentifier('zfbugs');
        $bug_id = $this->_db->quoteIdentifier('bug_id');

        // notice the number of rows in connection 2
        $count = $this->_db->fetchOne("SELECT COUNT(*) FROM $bugs");
        $this->assertEquals(4, $count, 'Expecting to see 4 rows in bugs table (step 1)');

        // start an explicit transaction in connection 1
        $this->_db->beginTransaction();

        // delete a row in connection 1
        $rowsAffected = $this->_db->delete(
            'zfbugs',
            "$bug_id = 1"
        );
        $this->assertEquals(1, $rowsAffected);

        // we should still see all rows in connection 2
        // because the DELETE has not been committed yet
        $count = $this->_db->fetchOne("SELECT COUNT(*) FROM $bugs");
        $this->assertEquals(3, $count, 'Expecting to still see 4 rows in bugs table (step 2); perhaps Adapter is still in autocommit mode?');

        // rollback the DELETE
        $this->_db->rollback();

        // now we should see the same number of rows
        // because the DELETE was rolled back
        $count = $this->_db->fetchOne("SELECT COUNT(*) FROM $bugs");
        $this->assertEquals(4, $count, 'Expecting to still see 4 rows in bugs table after DELETE is rolled back (step 3)');

        // delete another row in connection 1
        $rowsAffected = $this->_db->delete(
            'zfbugs',
            "$bug_id = 2"
        );
        $this->assertEquals(1, $rowsAffected);

        // we should see results immediately, because
        // the db connection returns to auto-commit mode
        $count = $this->_db->fetchOne("SELECT COUNT(*) FROM $bugs");
        $this->assertEquals(3, $count, 'Expecting to see 3 rows in bugs table after DELETE (step 4)');
    }

    public function testCanChangeIsolationLevel()
    {
        $db = $this->_db;

        // All of these should work
        $this->assertTrue($db->setTransactionIsolationLevel(SQLSRV_TXN_READ_UNCOMMITTED));
        $this->assertTrue($db->setTransactionIsolationLevel(SQLSRV_TXN_READ_COMMITTED));
        $this->assertTrue($db->setTransactionIsolationLevel(SQLSRV_TXN_REPEATABLE_READ));
        $this->assertTrue($db->setTransactionIsolationLevel(SQLSRV_TXN_SNAPSHOT));
        $this->assertTrue($db->setTransactionIsolationLevel(SQLSRV_TXN_SERIALIZABLE));

        try {
            $db->setTransactionIsolationLevel('not existing isolation level');
            $this->fail("Not existing isolation types are allowed to set");
        } catch (Zend_Db_Adapter_Sqlsrv_Exception $e) {
        }

        $this->assertTrue($db->setTransactionIsolationLevel(), "Setting to default should work by passsing null or nothing");
    }
    
    /**
     * @group ZF-9252
     * @see zf-trunk/tests/Zend/Db/Adapter/Zend_Db_Adapter_TestCommon#testAdapterLimit()
     */
    public function testAdapterLimit()
    {
        parent::testAdapterLimit();

        $products = $this->_db->quoteIdentifier('zfproducts');
        $product_id = $this->_db->quoteIdentifier('product_id');

        $sql = $this->_db->limit("SELECT * FROM $products ORDER BY $products.$product_id", 1);

        $stmt = $this->_db->query($sql);
        $result = $stmt->fetchAll();
        $this->assertEquals(1, count($result),
            'Expecting row count to be 1');
        $this->assertEquals(1, $result[0]['product_id'],
            'Expecting to get product_id 1');

    }
    
    /**
     * @group ZF-9252
     * @see zf-trunk/tests/Zend/Db/Adapter/Zend_Db_Adapter_TestCommon#testAdapterLimitOffset()
     */
    public function testAdapterLimitOffset()
    {
        parent::testAdapterLimitOffset();
        
        $products = $this->_db->quoteIdentifier('zfproducts');
        $product_id = $this->_db->quoteIdentifier('product_id');

        $sql = $this->_db->limit("SELECT * FROM $products ORDER BY $products.$product_id", 1, 1);

        $stmt = $this->_db->query($sql);
        $result = $stmt->fetchAll();
        $this->assertEquals(1, count($result),
            'Expecting row count to be 1');
        $this->assertEquals(2, $result[0]['product_id'],
            'Expecting to get product_id 2');
    } 

    /**
     * @group ZF-8148
     */
    public function testAdapterLimitOffsetWithOffsetExceedingRowCount()
    {
        $products = $this->_db->quoteIdentifier('zfproducts');
        $product_id = $this->_db->quoteIdentifier('product_id');

        $sql = $this->_db->limit("SELECT * FROM $products ORDER BY $products.$product_id", 2, 2);

        $stmt = $this->_db->query($sql);
        $result = $stmt->fetchAll();
        $this->assertEquals(1, count($result),
            'Expecting row count to be 1');
    }  

    /**
     * @group ZF-8148
     */
    public function testAdapterLimitWithoutOrder()
    {
        $products = $this->_db->quoteIdentifier('zfproducts');
        $product_id = $this->_db->quoteIdentifier('product_id');

        $sql = $this->_db->limit("SELECT * FROM $products", 1);

        $stmt = $this->_db->query($sql);
        $result = $stmt->fetchAll();
        $this->assertEquals(1, count($result),
            'Expecting row count to be 1');
        $this->assertEquals(1, $result[0]['product_id'],
            'Expecting to get product_id 1');
    }     

    /**
     * @group ZF-8148
     */
    public function testAdapterLimitOffsetWithoutOrder()
    {
        $products = $this->_db->quoteIdentifier('zfproducts');
        $product_id = $this->_db->quoteIdentifier('product_id');

        $sql = $this->_db->limit("SELECT * FROM $products", 1, 1);

        $stmt = $this->_db->query($sql);
        $result = $stmt->fetchAll();
        $this->assertEquals(1, count($result),
            'Expecting row count to be 1');
        $this->assertEquals(2, $result[0]['product_id'],
            'Expecting to get product_id 2');
    }  
    
    /**
     * @group ZF-8901
     */
    public function testAdapterLimitOffsetWithMultipleOrderColumns()
    {
        $products = $this->_db->quoteIdentifier('zfproducts');
        $product_id = $this->_db->quoteIdentifier('product_id');
        $product_name = $this->_db->quoteIdentifier('product_name');

        $sql = $this->_db->limit("SELECT * FROM $products ORDER BY $products.$product_id ASC, $products.$product_name DESC", 1, 1);

        $stmt = $this->_db->query($sql);
        $result = $stmt->fetchAll();
        $this->assertEquals(1, count($result),
            'Expecting row count to be 1');
        $this->assertEquals(2, $result[0]['product_id'],
            'Expecting to get product_id 2');
    }    

    public function getDriver()
    {
        return 'Sqlsrv';
    }
}