1: <?php
  2: 
  3: namespace Alchemy\tests;
  4: use Alchemy\core\schema\Table;
  5: use Alchemy\core\query\Expression as E;
  6: use Alchemy\core\query\Query;
  7: use Alchemy\dialect\ANSICompiler;
  8: 
  9: 
 10: class ANSISelectTest extends BaseTest {
 11: 
 12:     protected $users;
 13:     protected $addrs;
 14:     protected $phones;
 15: 
 16:     public function setUp() {
 17:         $this->users = Table::Core('users', array(
 18:             'columns' => array(
 19:                 'UserID' => 'Integer(11)',
 20:                 'UserName' => 'String',
 21:                 'Email' => 'String')
 22:         ));
 23: 
 24:         $this->addrs = Table::Core('addresses', array(
 25:             'columns' => array(
 26:                 'UserID' => 'Integer',
 27:                 'AddressType' => 'Integer',
 28:                 'StreetAddress' => 'String')
 29:         ));
 30: 
 31:         $this->phones = Table::Core('phones', array(
 32:             'columns' => array(
 33:                 'UserID' => 'Integer',
 34:                 'PhoneNum' => 'String')
 35:         ));
 36:     }
 37: 
 38:     public function testSimpleSelect() {
 39:         $users = $this->users->getRef();
 40: 
 41:         $query = Query::Select($users)
 42:             ->columns($users->UserName, $users->Email)
 43:             ->limit(2);
 44: 
 45:         $ansi = new ANSICompiler();
 46:         $vern = $ansi->compile($query, array('alias_tables' => true));
 47: 
 48:         $this->assertExpectedString('ANSISelectTest-1.sql', $vern);
 49:     }
 50: 
 51: 
 52:     public function testSingleJoinSelect() {
 53:         $addrs = $this->addrs->getRef();
 54:         $users = $this->users->getRef();
 55: 
 56:         $query = Query::Select($users)
 57:             ->columns($users->UserName, $users->Email, $addrs->StreetAddress)
 58:             ->join($addrs, $addrs->UserID->equal($users->UserID));
 59: 
 60:         $ansi = new ANSICompiler();
 61:         $vern = $ansi->compile($query, array('alias_tables' => true));
 62: 
 63:         $this->assertExpectedString('ANSISelectTest-2.sql', $vern);
 64:     }
 65: 
 66: 
 67:     public function testMultiJoinSelect() {
 68:         $addrs = $this->addrs->getRef();
 69:         $users = $this->users->getRef();
 70:         $phones = $this->phones->getRef();
 71: 
 72:         $addrJoin = E::AND_($addrs->UserID->equal($users->UserID),
 73:                             $addrs->AddressType->equal(5));
 74: 
 75:         $phoneJoin = $phones->UserID->equal($users->UserID);
 76: 
 77:         $query = Query::Select($users)
 78:             ->columns($users->UserName, $addrs->StreetAddress, $phones->PhoneNum)
 79:             ->join($addrs, $addrJoin)
 80:             ->join($phones, $phoneJoin);
 81: 
 82:         $ansi = new ANSICompiler();
 83:         $vern = $ansi->compile($query, array('alias_tables' => true));
 84: 
 85:         $this->assertExpectedString('ANSISelectTest-3.sql', $vern);
 86:     }
 87: 
 88: 
 89:     public function testWhereSelect() {
 90:         $users = $this->users->getRef();
 91: 
 92:         $query = Query::Select($users)
 93:             ->columns($users->UserID, $users->UserName)
 94:             ->where($users->UserName->equal('user1@example.com'))
 95:             ->offset(2)->limit(5);
 96: 
 97:         $ansi = new ANSICompiler();
 98:         $vern = $ansi->compile($query, array('alias_tables' => true));
 99: 
100:         $this->assertExpectedString('ANSISelectTest-4.sql', $vern);
101:     }
102: }
103: