eZ components PHP Cross Reference Developer Tools

Source: /Database/tests/sqlabstraction/query_insert_test.php - 272 lines - 8463 bytes - Summary - Text - Print

   1  <?php
   2  /**
   3   * @copyright Copyright (C) 2005-2010 eZ Systems AS. All rights reserved.
   4   * @license http://ez.no/licenses/new_bsd New BSD License
   5   * @version 1.4.7
   6   * @filesource
   7   * @package Database
   8   * @subpackage Tests
   9   */
  10  
  11  /**
  12   * Testing the SQL expression abstraction layer for INSERT queries.
  13   *
  14   * @package Database
  15   * @subpackage Tests
  16   */
  17  class ezcQueryInsertTest extends ezcTestCase
  18  {
  19      private $q;
  20  
  21      protected function setUp()
  22      {
  23          try
  24          {
  25              $db = ezcDbInstance::get();
  26          }
  27          catch ( Exception $e )
  28          {
  29              $this->markTestSkipped();
  30          }
  31  
  32          $this->q = new ezcQueryInsert( $db );
  33          try
  34          {
  35              $db->exec( 'DROP TABLE query_test' );
  36          }
  37          catch ( Exception $e ) {} // eat
  38  
  39          // insert some data
  40          $db->exec( 'CREATE TABLE query_test ( id int, company VARCHAR(255), section VARCHAR(255), employees int )' );
  41  
  42      }
  43  
  44      protected function tearDown()
  45      {
  46          $db = ezcDbInstance::get();
  47          $db->exec( 'DROP TABLE query_test' );
  48      }
  49  
  50      public function testSingle()
  51      {
  52          $reference = "INSERT INTO legends ( Gretzky ) VALUES ( 99 )";
  53          $this->q->insertInto( 'legends' )
  54              ->set( 'Gretzky', '99' );
  55          $this->assertEquals( $reference, $this->q->getQuery() );
  56      }
  57  
  58      public function testMulti()
  59      {
  60          $reference = "INSERT INTO legends ( Gretzky, Lindros ) VALUES ( 99, 88 )";
  61          $this->q->insertInto( 'legends' )
  62              ->set( 'Gretzky', '99' )
  63              ->set( 'Lindros', '88' );
  64          $this->assertEquals( $reference, $this->q->getQuery() );
  65      }
  66  
  67      public function testNoTable()
  68      {
  69          try
  70          {
  71              $this->q->set( 'Gretzky', '99' )->set( 'Lindros', '88' );
  72              $this->q->getQuery();
  73          }
  74          catch ( Exception $e )
  75          {
  76              return;
  77          }
  78          $this->fail( "Insert query with no table did not fail!" );
  79      }
  80  
  81      public function testNoValues()
  82      {
  83          try
  84          {
  85              $this->q->insertInto( 'MyTable' );
  86              $this->q->getQuery();
  87          }
  88          catch ( Exception $e )
  89          {
  90              return;
  91          }
  92          $this->fail( "Insert query with no values did not fail!" );
  93      }
  94  
  95      // test on a real database.
  96      public function testOnDatabase()
  97      {
  98          $q = $this->q;
  99          $q->insertInto( 'query_test' )
 100              ->set( 'id', 1 )
 101              ->set( 'company', $q->bindValue( 'eZ systems' ) )
 102              ->set( 'section', $q->bindValue( 'Norway' ) )
 103              ->set( 'employees', 20 );
 104          $stmt = $q->prepare();
 105          $stmt->execute();
 106  
 107          // check that it was actually correctly set
 108          $db = ezcDbInstance::get();
 109          $q = $db->createSelectQuery(); // get select query
 110          $q->select( '*' )->from( 'query_test' )
 111              ->where( $q->expr->eq( 'id', 1 ) );
 112          $stmt = $q->prepare();
 113          $stmt->execute();
 114          $result = $stmt->fetchAll();
 115          $this->assertEquals( 1, (int)$result[0][0] );
 116          $this->assertEquals( 'eZ systems', $result[0][1] );
 117      }
 118  
 119      // test several inserts on a real database.
 120      public function testSeveralInsertsOnDatabase()
 121      {
 122          $q = $this->q;
 123          $company = 'eZ systems';
 124          $section = 'Norway';
 125          $q->insertInto( 'query_test' )
 126              ->set( 'id', 1 )
 127              ->set( 'company', $q->bindParam( $company ) )
 128              ->set( 'section', $q->bindParam( $section ) )
 129              ->set( 'employees', 20 );
 130          $stmt = $q->prepare();
 131          $stmt->execute();
 132  
 133          $q->insertInto( 'query_test' );
 134          $q->set( 'id', 2 );
 135          $q->set( 'employees', 70 );
 136          $company = 'trolltech';
 137          $section = 'Norway';
 138          $stmt = $q->prepare();
 139          $stmt->execute();
 140  
 141          // check that it was actually correctly set
 142          $db = ezcDbInstance::get();
 143          $q = $db->createSelectQuery(); // get select query
 144          $q->select( '*' )->from( 'query_test' )
 145              ->where( $q->expr->eq( 'id', 1 ) );
 146          $stmt = $q->prepare();
 147          $stmt->execute();
 148          $result = $stmt->fetchAll();
 149          $this->assertEquals( 1, (int)$result[0][0] );
 150          $this->assertEquals( 'eZ systems', $result[0][1] );
 151  
 152          // check that it was actually correctly set
 153          $db = ezcDbInstance::get();
 154          $q = $db->createSelectQuery(); // get select query
 155          $q->select( '*' )->from( 'query_test' )
 156              ->where( $q->expr->eq( 'id', 2 ) );
 157          $stmt = $q->prepare();
 158          $stmt->execute();
 159          $result = $stmt->fetchAll();
 160          $this->assertEquals( 2, (int)$result[0][0] );
 161          $this->assertEquals( 'trolltech', $result[0][1] );
 162  
 163      }
 164  
 165      public function testSeveralInsertsWithValueBind()
 166      {
 167          $q = $this->q;
 168          $company = 'eZ systems';
 169          $section = 'Norway';
 170          $q->insertInto( 'query_test' )
 171              ->set( 'id', 1 )
 172              ->set( 'company', $q->bindValue( $company ) )
 173              ->set( 'section', $q->bindValue( $section ) )
 174              ->set( 'employees', 20 );
 175          $stmt = $q->prepare();
 176          $stmt->execute();
 177  
 178          $q->insertInto( 'query_test' );
 179          $q->set( 'id', 2 );
 180          $q->set( 'employees', 70 );
 181          $company = 'trolltech'; // This should be ignored
 182          $section = 'Norway';
 183          $stmt = $q->prepare();
 184          $stmt->execute();
 185  
 186          // check that it was actually correctly set
 187          $db = ezcDbInstance::get();
 188          $q = $db->createSelectQuery(); // get select query
 189          $q->select( '*' )->from( 'query_test' )
 190              ->where( $q->expr->eq( 'id', 1 ) );
 191          $stmt = $q->prepare();
 192          $stmt->execute();
 193          $result = $stmt->fetchAll();
 194          $this->assertEquals( 1, (int)$result[0][0] );
 195          $this->assertEquals( 'eZ systems', $result[0][1] );
 196  
 197          // check that it was actually correctly set
 198          $db = ezcDbInstance::get();
 199          $q = $db->createSelectQuery(); // get select query
 200          $q->select( '*' )->from( 'query_test' )
 201              ->where( $q->expr->eq( 'id', 2 ) );
 202          $stmt = $q->prepare();
 203          $stmt->execute();
 204          $result = $stmt->fetchAll();
 205          $this->assertEquals( 2, (int)$result[0][0] );
 206          $this->assertEquals( 'eZ systems', $result[0][1] );
 207      }
 208  
 209      public function testInsertsWithSequence()
 210      {
 211          $q = $this->q;
 212          $db = ezcDbInstance::get();
 213          $company = "eZ systems";
 214          $section1 = "Norway";
 215          $section2 = "Ukraine";
 216  
 217          if ( $db->getName() == 'mysql' || $db->getName() == 'sqlite' || $db->getName() == 'mssql')
 218          {
 219              return;  // no need to test it in MySQL, SQLite and MSSQL as they have autoincrement
 220          }
 221  
 222          if ( $db->getName() == 'oracle' )
 223          {
 224              $db->exec( "CREATE SEQUENCE query_test_id_seq start with 1 increment by 1 nomaxvalue" );
 225          }
 226          else if ( $db->getName() == 'pgsql' ) 
 227          {
 228              $db->exec( "CREATE SEQUENCE query_test_id_seq START 1" );
 229          }
 230  
 231          // row 1
 232          $q->insertInto( 'query_test' )
 233              ->set( 'id', 'nextval(\'query_test_id_seq\')' )
 234              ->set( 'company', $q->bindParam( $company ) )
 235              ->set( 'section', $q->bindParam( $section1 ) )
 236              ->set( 'employees', 20 );
 237  
 238          $stmt = $q->prepare();
 239          $stmt->execute();
 240  
 241          // row 2
 242          $q->insertInto( 'query_test' )
 243              ->set( 'id', 'nextval(\'query_test_id_seq\')' )
 244              ->set( 'company',  $q->bindParam( $company ) )
 245              ->set( 'section',  $q->bindParam( $section2 ) )
 246              ->set( 'employees', 10 );
 247          $stmt = $q->prepare();
 248          $stmt->execute();
 249          
 250          // check that it was actually correctly set
 251          $q = $db->createSelectQuery(); // get select query
 252          $q->select( '*' )->from( 'query_test' )->where( $q->expr->eq( 'id', 2 ) );
 253          $stmt = $q->prepare();
 254          $stmt->execute();
 255          $result = $stmt->fetchAll();
 256          $this->assertEquals( 2, (int)$result[0][0] );
 257          $this->assertEquals( 'eZ systems', $result[0][1] );
 258          $this->assertEquals( 'Ukraine', $result[0][2] );
 259          $this->assertEquals( 10, $result[0][3] );
 260  
 261          if ( $db->getName() == 'oracle' || $db->getName() == 'pgsql' )
 262          {
 263              $db->exec( "DROP SEQUENCE query_test_id_seq" );
 264          }
 265      }
 266  
 267      public static function suite()
 268      {
 269          return new PHPUnit_Framework_TestSuite( 'ezcQueryInsertTest' );
 270      }
 271  }
 272  ?>

title

Description

title

Description

title

Description

title

title

Body