3 namespace RedUNIT\Base;
5 use RedUNIT\Base as Base;
6 use RedBeanPHP\Facade as R;
7 use RedBeanPHP\QueryWriter\SQLiteT as SQLiteT;
8 use RedBeanPHP\OODB as OODB;
9 use RedBeanPHP\OODBBean as OODBBean;
10 use RedBeanPHP\ToolBox as ToolBox;
11 use RedBeanPHP\AssociationManager as AssociationManager;
12 use RedBeanPHP\RedException as RedException;
13 use RedBeanPHP\RedException\SQL as SQL;
14 use RedBeanPHP\QueryWriter\MySQL as MySQL;
15 use RedBeanPHP\QueryWriter\PostgreSQL as PostgreSQL;
16 use RedBeanPHP\QueryWriter\CUBRID as CUBRID;
17 use RedBeanPHP\Adapter\DBAdapter as DBAdapter;
22 * Tests basic RedBeanPHP database functionality.
24 * @file RedUNIT/Base/Database.php
25 * @desc Tests basic database behaviors
26 * @author Gabor de Mooij and the RedBeanPHP Community
27 * @license New BSD/GPLv2
29 * (c) G.J.G.T. (Gabor) de Mooij and the RedBeanPHP Community.
30 * This source file is subject to the New BSD/GPLv2 License that is bundled
31 * with this source code in the file license.txt.
33 class Database extends Base
36 * What drivers should be loaded for this test pack?
38 public function getTargetDrivers()
40 return array( 'mysql', 'pgsql', 'sqlite', 'CUBRID' );
44 * Test setter maximum integer bindings.
48 public function testSetMaxBind()
50 $driver = R::getDatabaseAdapter()->getDatabase();
51 $old = $driver->setMaxIntBind( 10 );
52 //use SQLite to confirm...
53 if ( $this->currentlyActiveDriverID === 'sqlite' ) {
54 $type = R::getCell( 'SELECT typeof( ? ) ', array( 11 ) );
55 asrt( $type, 'text' );
56 $type = R::getCell( 'SELECT typeof( ? ) ', array( 10 ) );
57 asrt( $type, 'integer' );
58 $type = R::getCell( 'SELECT typeof( ? ) ', array( 9 ) );
59 asrt( $type, 'integer' );
61 $new = $driver->setMaxIntBind( $old );
64 $driver->setMaxIntBind( '10' );
66 } catch( RedException $e ) {
69 $new = $driver->setMaxIntBind( $old );
71 $new = $driver->setMaxIntBind( $old );
76 * Can we use colons in SQL?
80 public function testColonsInSQL()
83 $book = R::dispense( 'book' );
84 $book->title = 'About :';
87 $book = R::findOne( 'book', ' title LIKE :this ', array(
90 asrt( ( $book instanceof OODBBean ), TRUE );
92 $book = R::findOne( 'book', ' title LIKE :this ', array(
95 asrt( ( $book instanceof OODBBean ), TRUE );
96 $book = R::findOne( 'book', ' title LIKE :this ', array(
99 asrt( ( $book instanceof OODBBean ), TRUE );
100 $book = R::findOne( 'book', ' title LIKE :this OR title LIKE :that', array(
101 'this' => '%:%', ':that' => 'That'
103 asrt( ( $book instanceof OODBBean ), TRUE );
104 $records = R::getAll('SELECT * FROM book WHERE title LIKE :this', array( ':this' => 'About :' ) );
105 asrt( count( $records ), 1 );
106 $records = R::getAll('SELECT * FROM book WHERE title LIKE :this', array( 'this' => 'About :' ) );
107 asrt( count( $records ), 1 );
108 $records = R::getAll('SELECT * FROM book WHERE title LIKE :this OR title LIKE :that', array( ':this' => 'About :', ':that' => 'That' ) );
109 asrt( count( $records ), 1 );
110 $records = R::getRow('SELECT * FROM book WHERE title LIKE :this', array( ':this' => 'About :' ) );
111 asrt( count( $records ), 2 );
112 $records = R::getRow('SELECT * FROM book WHERE title LIKE :this', array( 'this' => 'About :' ) );
113 asrt( count( $records ), 2 );
114 $records = R::getRow('SELECT * FROM book WHERE title LIKE :this OR title LIKE :that', array( ':this' => 'About :', ':that' => 'That' ) );
115 asrt( count( $records ), 2 );
119 * Test setting direct PDO.
120 * Not much to test actually.
124 public function testDirectPDO()
126 $pdo = R::getDatabaseAdapter()->getDatabase()->getPDO();
127 R::getDatabaseAdapter()->getDatabase()->setPDO( $pdo );
132 * Test for testConnection() method.
136 public function testConnectionTester()
138 asrt( R::testConnection(), TRUE );
142 * Tests the various ways to fetch (select queries)
143 * data using adapter methods in the facade.
144 * Also tests the new R::getAssocRow() method,
145 * as requested in issue #324.
147 public function testFetchTypes()
151 $page = R::dispense( 'page' );
156 $page = R::dispense( 'page' );
161 $expect = '[{"id":"1","a":"a","b":"b"},{"id":"2","a":"c","b":"d"}]';
162 asrt( json_encode( R::getAll( 'SELECT * FROM page' ) ), $expect );
164 $expect = '{"1":"a","2":"c"}';
165 asrt( json_encode( R::getAssoc( 'SELECT id, a FROM page' ) ), $expect );
167 $expect = '{"1":{"a":"a","b":"b"},"2":{"a":"c","b":"d"}}';
168 asrt( json_encode( R::getAssoc( 'SELECT id, a, b FROM page' ) ), $expect );
170 $expect = '[{"id":"1","a":"a"},{"id":"2","a":"c"}]';
171 asrt( json_encode( R::getAssocRow( 'SELECT id, a FROM page' ) ), $expect );
173 $expect = '[{"id":"1","a":"a","b":"b"},{"id":"2","a":"c","b":"d"}]';
174 asrt( json_encode( R::getAssocRow( 'SELECT id, a, b FROM page' ) ), $expect );
176 $expect = '{"id":"1","a":"a","b":"b"}';
177 asrt( json_encode( R::getRow( 'SELECT * FROM page WHERE id = 1' ) ), $expect );
180 asrt( json_encode( R::getCell( 'SELECT a FROM page WHERE id = 1' ) ), $expect );
183 asrt( json_encode( R::getCell( 'SELECT b FROM page WHERE id = 1') ), $expect );
186 asrt( json_encode( R::getCell('SELECT a FROM page WHERE id = 2') ), $expect );
188 $expect = '["a","c"]';
189 asrt( json_encode( R::getCol( 'SELECT a FROM page' ) ), $expect );
191 $expect = '["b","d"]';
192 asrt( json_encode( R::getCol('SELECT b FROM page') ), $expect );
196 * Tests whether we can store an empty bean.
197 * An empty bean has no properties, only ID. Normally we would
198 * skip the ID field in an INSERT, this test forces the driver
199 * to specify a value for the ID field. Different writers have to
200 * use different values: Mysql uses NULL to insert a new auto-generated ID,
201 * while Postgres has to use DEFAULT.
203 public function testEmptyBean()
205 testpack( 'Test Empty Bean Storage.' );
207 $bean = R::dispense( 'emptybean' );
208 $id = R::store( $bean );
209 asrt( ( $id > 0 ), TRUE );
210 asrt( R::count( 'emptybean' ), 1 );
211 $bean = R::dispense( 'emptybean' );
212 $id = R::store( $bean );
213 asrt( ( $id > 0 ), TRUE );
214 asrt( R::count( 'emptybean' ), 2 );
215 //also test in frozen mode
217 $bean = R::dispense( 'emptybean' );
218 $id = R::store( $bean );
219 asrt( ( $id > 0 ), TRUE );
220 asrt( R::count( 'emptybean' ), 3 );
225 * Test the database driver and low level functions.
229 public function testDriver()
231 $currentDriver = $this->currentlyActiveDriverID;
233 R::store( R::dispense( 'justabean' ) );
235 $adapter = new TroubleDapter( R::getToolBox()->getDatabaseAdapter()->getDatabase() );
237 $adapter->setSQLState( 'HY000' );
239 $writer = new SQLiteT( $adapter );
240 $redbean = new OODB( $writer );
241 $toolbox = new ToolBox( $redbean, $adapter, $writer );
243 // We can only test this for a known driver...
244 if ( $currentDriver === 'sqlite' ) {
246 $redbean->find( 'bean' );
249 } catch (\Exception $e ) {
250 var_dump( $e->getSQLState() );
256 $adapter->setSQLState( -999 );
259 $redbean->find( 'bean' );
262 } catch (\Exception $e ) {
267 $redbean->wipe( 'justabean' );
270 } catch (\Exception $e ) {
274 $toolbox = R::getToolBox();
275 $adapter = $toolbox->getDatabaseAdapter();
276 $writer = $toolbox->getWriter();
277 $redbean = $toolbox->getRedBean();
278 $pdo = $adapter->getDatabase();
280 $page = $redbean->dispense( "page" );
283 $adapter->exec( "an invalid query" );
289 // Special data type description should result in magic number 99 (specified)
290 if ( $currentDriver == 'mysql' ) {
291 asrt( $writer->code( MySQL::C_DATATYPE_SPECIAL_DATE ), 99 );
294 if ( $currentDriver == 'pgsql' ) {
295 asrt( $writer->code( PostgreSQL::C_DATATYPE_SPECIAL_DATE ), 99 );
298 if ( $currentDriver == 'CUBRID' ) {
299 asrt( $writer->code( CUBRID::C_DATATYPE_SPECIAL_DATE ), 99 );
302 asrt( (int) $adapter->getCell( "SELECT 123" ), 123 );
304 $page->aname = "my page";
306 $id = (int) $redbean->store( $page );
308 asrt( (int) $page->id, 1 );
309 asrt( (int) $pdo->GetCell( "SELECT count(*) FROM page" ), 1 );
310 asrt( $pdo->GetCell( "SELECT aname FROM page LIMIT 1" ), "my page" );
311 asrt( (int) $id, 1 );
313 $page = $redbean->load( "page", 1 );
315 asrt( $page->aname, "my page" );
316 asrt( ( (bool) $page->getMeta( "type" ) ), TRUE );
317 asrt( isset( $page->id ), TRUE );
318 asrt( ( $page->getMeta( "type" ) ), "page" );
319 asrt( (int) $page->id, $id );
327 public function testSelects()
329 $rooms = R::dispense( 'room', 2 );
331 $rooms[0]->kind = 'suite';
332 $rooms[1]->kind = 'classic';
333 $rooms[0]->number = 6;
334 $rooms[1]->number = 7;
336 R::store( $rooms[0] );
337 R::store( $rooms[1] );
339 $rooms = R::getAssoc('SELECT * FROM room WHERE id < -999');
340 asrt(is_array($rooms), TRUE);
341 asrt(count($rooms), 0);
343 $rooms = R::getAssoc( 'SELECT ' . R::getWriter()->esc( 'number' ) . ', kind FROM room ORDER BY kind ASC' );
345 foreach ( $rooms as $key => $room ) {
346 asrt( ( $key === 6 || $key === 7 ), TRUE );
347 asrt( ( $room == 'classic' || $room == 'suite' ), TRUE );
350 $rooms = R::getDatabaseAdapter()->getAssoc( 'SELECT kind FROM room' );
351 foreach ( $rooms as $key => $room ) {
352 asrt( ( $room == 'classic' || $room == 'suite' ), TRUE );
356 $rooms = R::getAssoc( 'SELECT `number`, kind FROM rooms2 ORDER BY kind ASC' );
358 asrt( count( $rooms ), 0 );
359 asrt( is_array( $rooms ), TRUE );
361 // GetCell should return NULL in case of exception
362 asrt( NULL, R::getCell( 'SELECT dream FROM fantasy' ) );
367 * Malfunctioning database adapter to test exceptions.
369 class TroubleDapter extends DBAdapter
373 public function setSQLState( $sqlState )
375 $this->sqlState = $sqlState;
378 public function get( $sql, $values = array() )
380 $exception = new SQL( 'Just a trouble maker' );
381 $exception->setSQLState( $this->sqlState );
385 public function getRow( $sql, $aValues = array() )
387 $this->get( $sql, $aValues );
390 public function exec( $sql, $aValues = array(), $noEvent = FALSE )
392 $this->get( $sql, $aValues );