SqlTest.php

<?php

declare(encoding='UTF-8');
namespace SqlTest;

use Adapter\Sql;

require_once 'Sql.php';

class SqlTest extends \PHPUnit_Framework_TestCase {
  
  private $db = NULL;
  
  // Fixture creation
  public function setUp() {
    $this->db = new \PDO('sqlite::memory:');
    $sql_data = @file_get_contents(DIRECTORY_DATA . 'SqlData.sql');
    
    if ( false !== $sql_data ) {
      $this->db->exec($sql_data);
    }
  
  }
  
  
  public function tearDown() {
    $this->db = NULL;
  }
  
  
  // Custom assertions
  public static function assertIsPdo($obj, $message = '') {
    self::assertTrue(is_object($obj), $message);
    self::assertTrue($obj instanceof \PDO, $message);
  }
  
  
  public static function assertArray($a, $message = '') {
    self::assertTrue(is_array($a), $message);
  }
  
  
  // Tests
  public function testAttachDb_CanAttachPdoObject() {
    $sql = new Sql;
    $sql->attachDb($this->db);
    
    $this->assertIsPdo($sql->getDb());
  }
  
  
  /**
   * @expectedException PHPUnit_Framework_Error
   */
  public function testQuery_InputParametersMustBeArray() {
    $sql = new Sql;
    $sql->attachDb($this->db);
    
    $sql->find('FAKE QUERY', NULL);
  }
  
  
  /**
   * @expectedException \Adapter\Exception
   */
  public function testQuery_RequiresPdo() {
    $sql = new Sql;
    $sql->find('FAKE QUERY', array());
  }
  
  
  /**
   * @expectedException \Adapter\Exception
   */
  public function testQuery_RequiresSql() {
    $sql = new Sql;
    $sql->attachDb($this->db);
    
    $sql->find(NULL, array());
  }
  
  
  /**
   * @dataProvider providerInvalidSelectPreparedQuery
   * @expectedException \Adapter\Exception
   */
  public function testQuery_RequiresValidSql($query) {
    $sql = new Sql;
    $sql->attachDb($this->db);
    
    $sql->find($query, array());
  }
  
  
  /**
   * @dataProvider providerValidSelectPreparedQuery
   * @expectedException \Adapter\Exception
   */
  public function testQuery_RequiresValidSqlExecution($query, $input_parameters) {
    $sql = new Sql;
    $sql->attachDb($this->db);
    
    $sql->find($query, array(':does_not_exist' => 'non-existent value'));
  }
  
  
  /**
   * @dataProvider providerValidPreparedQuery
   */
  public function testQuery_ReturnsNumberAffectedRows($query, $input_parameters, $expected_affected_rows) {
    $sql = new Sql;
    $sql->attachDb($this->db);
    
    $actual_rows = $sql->query($query, $input_parameters);
    
    $this->assertEquals($actual_rows, $expected_affected_rows);
  }
  
  
  /**
   * @dataProvider providerValidSelectPreparedQuery
   */
  public function testFind_ReturnsArray($query, $input_parameters) {
    $sql = new Sql;
    $sql->attachDb($this->db);
    
    $result_list = $sql->find($query, $input_parameters);
    
    $this->assertArray($result_list);
  }
  
  
  public function providerInvalidSelectPreparedQuery() {
    return array(
      array("SELECT * FROM products WHERE id = :i-d"),
      array("SELECT * FROM products WHERE sku = "),
      array("SELECT * FROM products_invalid_table WHERE id > :id"),
      array("SELECT products.invalid_field FROM products WHERE price <= :price")
    );
  }
  
  
  public function providerValidPreparedQuery() {
    return array(
      array("UPDATE products SET price = :price", array(':price' => 11.00), 3),
      array("UPDATE products SET name = :name", array(':name' => "Baba O'Reilly"), 3),
      array("UPDATE products SET sku = :sku WHERE id = :id", array(':sku' => 'PROD2', ':id' => 1), 1),
      array("INSERT INTO products (name, price, sku) VALUES(:name, :price, :sku)", array(':name' => 'Very Expensive "Product"', ':price' => 11.99, ':sku' => 'PRODUCT4'), 1)
    );
  }
  
  
  public function providerValidSelectPreparedQuery() {
    return array(
      array("SELECT * FROM products WHERE id = :id", array(':id' => 1)),
      array("SELECT * FROM products WHERE sku = :sku", array(':sku' => 'P1')),
      array("SELECT * FROM products WHERE id > :id", array(':id' => 1)),
      array("SELECT * FROM products WHERE price <= :price", array(':price' => 11.95))
    );
  }
}