3 namespace RedUNIT\Postgres;
5 use RedUNIT\Postgres as Postgres;
6 use RedBeanPHP\Facade as R;
7 use RedBeanPHP\AssociationManager as AssociationManager;
8 use RedBeanPHP\QueryWriter\PostgreSQL as PostgreSQL;
9 use RedBeanPHP\RedException\SQL as SQL;
14 * Tests for PostgreSQL Query Writer.
15 * This test class contains Query Writer specific tests.
16 * Use this class to add tests to test Query Writer specific
17 * behaviours, quirks and issues.
19 * @file RedUNIT/Postgres/Writer.php
20 * @desc A collection of writer specific tests.
21 * @author Gabor de Mooij and the RedBeanPHP Community
22 * @license New BSD/GPLv2
24 * (c) G.J.G.T. (Gabor) de Mooij and the RedBeanPHP Community.
25 * This source file is subject to the New BSD/GPLv2 License that is bundled
26 * with this source code in the file license.txt.
28 class Writer extends Postgres
31 * Test scanning and coding.
35 public function testScanningAndCoding()
37 $toolbox = R::getToolBox();
38 $adapter = $toolbox->getDatabaseAdapter();
39 $writer = $toolbox->getWriter();
40 $redbean = $toolbox->getRedBean();
41 $pdo = $adapter->getDatabase();
43 $a = new AssociationManager( $toolbox );
45 $adapter->exec( "DROP TABLE IF EXISTS testtable" );
47 asrt( in_array( "testtable", $writer->getTables() ), FALSE );
49 $writer->createTable( "testtable" );
51 asrt( in_array( "testtable", $writer->getTables() ), TRUE );
53 asrt( count( array_keys( $writer->getColumns( "testtable" ) ) ), 1 );
55 asrt( in_array( "id", array_keys( $writer->getColumns( "testtable" ) ) ), TRUE );
56 asrt( in_array( "c1", array_keys( $writer->getColumns( "testtable" ) ) ), FALSE );
58 $writer->addColumn( "testtable", "c1", 1 );
60 asrt( count( array_keys( $writer->getColumns( "testtable" ) ) ), 2 );
62 asrt( in_array( "c1", array_keys( $writer->getColumns( "testtable" ) ) ), TRUE );
64 foreach ( $writer->sqltype_typeno as $key => $type ) {
66 asrt( $writer->code( $key, TRUE ), $type );
68 asrt( $writer->code( $key ), PostgreSQL::C_DATATYPE_SPECIFIED );
72 asrt( $writer->code( PostgreSQL::C_DATATYPE_SPECIAL_DATETIME ), PostgreSQL::C_DATATYPE_SPECIFIED );
74 asrt( $writer->code( "unknown" ), PostgreSQL::C_DATATYPE_SPECIFIED );
76 asrt( $writer->scanType( FALSE ), PostgreSQL::C_DATATYPE_INTEGER );
77 asrt( $writer->scanType( TRUE ), PostgreSQL::C_DATATYPE_INTEGER );
79 asrt( $writer->scanType( NULL ), PostgreSQL::C_DATATYPE_INTEGER );
81 asrt( $writer->scanType( 2 ), PostgreSQL::C_DATATYPE_INTEGER );
83 asrt( $writer->scanType( 255 ), PostgreSQL::C_DATATYPE_INTEGER );
84 asrt( $writer->scanType( 256 ), PostgreSQL::C_DATATYPE_INTEGER );
86 asrt( $writer->scanType( -1 ), PostgreSQL::C_DATATYPE_INTEGER );
88 asrt( $writer->scanType( 1.5 ), PostgreSQL::C_DATATYPE_DOUBLE );
90 asrt( $writer->scanType( INF ), PostgreSQL::C_DATATYPE_TEXT );
92 asrt( $writer->scanType( "abc" ), PostgreSQL::C_DATATYPE_TEXT );
94 asrt( $writer->scanType( "2001-10-10", TRUE ), PostgreSQL::C_DATATYPE_SPECIAL_DATE );
96 asrt( $writer->scanType( "2001-10-10 10:00:00", TRUE ), PostgreSQL::C_DATATYPE_SPECIAL_DATETIME );
98 asrt( $writer->scanType( "2001-10-10 10:00:00" ), PostgreSQL::C_DATATYPE_TEXT );
100 asrt( $writer->scanType( "2001-10-10" ), PostgreSQL::C_DATATYPE_TEXT );
102 asrt( $writer->scanType( str_repeat( "lorem ipsum", 100 ) ), PostgreSQL::C_DATATYPE_TEXT );
104 $writer->widenColumn( "testtable", "c1", PostgreSQL::C_DATATYPE_TEXT );
106 $cols = $writer->getColumns( "testtable" );
108 asrt( $writer->code( $cols["c1"] ), PostgreSQL::C_DATATYPE_TEXT );
110 $writer->addColumn( "testtable", "special", PostgreSQL::C_DATATYPE_SPECIAL_DATE );
112 $cols = $writer->getColumns( "testtable" );
114 asrt( $writer->code( $cols['special'], TRUE ), PostgreSQL::C_DATATYPE_SPECIAL_DATE );
116 asrt( $writer->code( $cols['special'], FALSE ), PostgreSQL::C_DATATYPE_SPECIFIED );
118 $writer->addColumn( "testtable", "special2", PostgreSQL::C_DATATYPE_SPECIAL_DATETIME );
120 $cols = $writer->getColumns( "testtable" );
122 asrt( $writer->code( $cols['special2'], TRUE ), PostgreSQL::C_DATATYPE_SPECIAL_DATETIME );
124 asrt( $writer->code( $cols['special'], FALSE ), PostgreSQL::C_DATATYPE_SPECIFIED );
126 //$id = $writer->insertRecord("testtable", array("c1"), array(array("lorem ipsum")));
128 $id = $writer->updateRecord( "testtable", array( array( "property" => "c1", "value" => "lorem ipsum" ) ) );
130 $row = $writer->queryRecord( "testtable", array( "id" => array( $id ) ) );
132 asrt( $row[0]["c1"], "lorem ipsum" );
134 $writer->updateRecord( "testtable", array( array( "property" => "c1", "value" => "ipsum lorem" ) ), $id );
136 $row = $writer->queryRecord( "testtable", array( "id" => array( $id ) ) );
138 asrt( $row[0]["c1"], "ipsum lorem" );
140 $writer->deleteRecord( "testtable", array( "id" => array( $id ) ) );
142 $row = $writer->queryRecord( "testtable", array( "id" => array( $id ) ) );
144 asrt( empty( $row ), TRUE );
148 * (FALSE should be stored as 0 not as '')
152 public function testZeroIssue()
154 testpack( "Zero issue" );
156 $toolbox = R::getToolBox();
157 $redbean = $toolbox->getRedBean();
159 $bean = $redbean->dispense( "zero" );
162 $bean->title = "bla";
164 $redbean->store( $bean );
166 asrt( count( $redbean->find( "zero", array(), " zero = 0 " ) ), 1 );
168 testpack( "Test ANSI92 issue in clearrelations" );
170 $a = new AssociationManager( $toolbox );
172 $book = $redbean->dispense( "book" );
173 $author1 = $redbean->dispense( "author" );
174 $author2 = $redbean->dispense( "author" );
176 $book->title = "My First Post";
178 $author1->name = "Derek";
179 $author2->name = "Whoever";
181 set1toNAssoc( $a, $book, $author1 );
182 set1toNAssoc( $a, $book, $author2 );
189 * Tests whether writer correctly handles keyword 'group' and SQL state 23000 issue.
190 * These tests remain here to make sure issues 9 and 10 never happen again.
191 * However this bug will probably never re-appear due to changed architecture.
195 public function testIssue9and10()
197 $toolbox = R::getToolBox();
198 $redbean = $toolbox->getRedBean();
199 $adapter = $toolbox->getDatabaseAdapter();
201 $a = new AssociationManager( $toolbox );
203 $book = $redbean->dispense( "book" );
205 $author1 = $redbean->dispense( "author" );
206 $author2 = $redbean->dispense( "author" );
208 $book->title = "My First Post";
210 $author1->name = "Derek";
211 $author2->name = "Whoever";
213 $a->associate( $book, $author1 );
214 $a->associate( $book, $author2 );
218 testpack( "Test Association Issue Group keyword (Issues 9 and 10)" );
222 $group = $redbean->dispense( "group" );
224 $group->name = "mygroup";
226 $redbean->store( $group );
229 $a->associate( $group, $book );
236 // Test issue SQL error 23000
238 $a->associate( $group, $book );
245 asrt( (int) $adapter->getCell( "select count(*) from book_group" ), 1 ); //just 1 rec!
250 * Test various somewhat uncommon trash/unassociate scenarios.
251 * (i.e. unassociate unrelated beans, trash non-persistant beans etc).
252 * Should be handled gracefully - no output checking.
256 public function testVaria()
258 $toolbox = R::getToolBox();
259 $redbean = $toolbox->getRedBean();
261 $a = new AssociationManager( $toolbox );
263 $book = $redbean->dispense( "book" );
264 $author1 = $redbean->dispense( "author" );
265 $author2 = $redbean->dispense( "author" );
267 $book->title = "My First Post";
269 $author1->name = "Derek";
270 $author2->name = "Whoever";
272 $a->unassociate( $book, $author1 );
273 $a->unassociate( $book, $author2 );
277 $redbean->trash( $redbean->dispense( "bla" ) );
281 $bean = $redbean->dispense( "bla" );
286 $redbean->trash( $bean );
292 * Test special types.
296 public function testTypes()
298 testpack( 'Special data types' );
300 $bean = R::dispense( 'bean' );
302 $bean->date = 'someday';
306 $cols = R::getColumns( 'bean' );
308 asrt( $cols['date'], 'text' );
310 $bean = R::dispense( 'bean' );
312 $bean->date = '2011-10-10';
316 $cols = R::getColumns( 'bean' );
318 asrt( $cols['date'], 'text' );
326 public function testTypesDates()
328 $bean = R::dispense( 'bean' );
330 $bean->date = '2011-10-10';
334 $cols = R::getColumns( 'bean' );
336 asrt( $cols['date'], 'date' );
344 public function testTypesDateTimes()
346 $bean = R::dispense( 'bean' );
348 $bean->date = '2011-10-10 10:00:00';
352 $cols = R::getColumns( 'bean' );
354 asrt( $cols['date'], 'timestamp without time zone' );
358 * Test spatial data types.
362 public function testTypesPoints()
364 $bean = R::dispense( 'bean' );
366 $bean->point = '(92,12)';
370 $cols = R::getColumns( 'bean' );
372 asrt( $cols['point'], 'point' );
374 $bean = R::load( 'bean', $bean->id );
376 asrt( $bean->point, '(92,12)' );
378 $bean->note = 'taint';
382 $bean = R::load( 'bean', $bean->id );
384 asrt( $bean->point, '(92,12)' );
392 public function testTypesDecPoints()
394 $bean = R::dispense( 'bean' );
396 $bean->point = '(9.2,1.2)';
400 $cols = R::getColumns( 'bean' );
402 asrt( $cols['point'], 'point' );
404 $bean = R::load( 'bean', $bean->id );
406 asrt( $bean->point, '(9.2,1.2)' );
408 $bean->note = 'taint';
412 $bean = R::load( 'bean', $bean->id );
414 asrt( $bean->point, '(9.2,1.2)' );
422 public function testPolygons()
424 $bean = R::dispense( 'bean' );
425 $bean->polygon = '((0,0),(1,1),(2,0))';
427 $cols = R::getColumns( 'bean' );
428 asrt( $cols['polygon'], 'polygon' );
429 $bean = R::load( 'bean', $bean->id );
430 asrt( $bean->polygon, '((0,0),(1,1),(2,0))' );
431 $bean->note = 'taint';
433 $bean = R::load( 'bean', $bean->id );
434 asrt( $bean->polygon, '((0,0),(1,1),(2,0))' );
435 $bean = R::dispense( 'bean' );
436 $bean->polygon = '((0,0),(1.2,1),(2,0.3))';
438 $cols = R::getColumns( 'bean' );
439 asrt( $cols['polygon'], 'polygon' );
440 $bean = R::load( 'bean', $bean->id );
441 asrt( $bean->polygon, '((0,0),(1.2,1),(2,0.3))' );
442 $bean->note = 'taint';
444 $bean = R::load( 'bean', $bean->id );
445 asrt( $bean->polygon, '((0,0),(1.2,1),(2,0.3))' );
453 public function testTypesMultiDecPoints()
455 $bean = R::dispense( 'bean' );
457 $bean->line = '[(1.2,1.4),(2.2,34)]';
461 $cols = R::getColumns( 'bean' );
463 asrt( $cols['line'], 'lseg' );
465 $bean = R::load( 'bean', $bean->id );
467 asrt( $bean->line, '[(1.2,1.4),(2.2,34)]' );
469 $bean->note = 'taint';
473 $bean = R::load( 'bean', $bean->id );
475 asrt( $bean->line, '[(1.2,1.4),(2.2,34)]' );
483 public function testTypesWeirdPoints()
485 $bean = R::dispense( 'bean' );
487 $bean->circle = '<(9.2,1.2),7.9>';
491 $cols = R::getColumns( 'bean' );
493 asrt( $cols['circle'], 'circle' );
495 $bean = R::load( 'bean', $bean->id );
497 asrt( $bean->circle, '<(9.2,1.2),7.9>' );
499 $bean->note = 'taint';
503 $bean = R::load( 'bean', $bean->id );
505 asrt( $bean->circle, '<(9.2,1.2),7.9>' );
513 public function testTypesMon()
515 $bean = R::dispense( 'bean' );
517 $bean->amount = '22.99';
521 $cols = R::getColumns( 'bean' );
523 asrt( $cols['amount'], 'numeric' );
527 $bean = R::dispense( 'bean' );
529 $bean->amount = '-22.99';
533 $cols = R::getColumns( 'bean' );
535 asrt( $cols['amount'], 'numeric' );
539 * Test money data type.
543 public function testTypesMoney()
545 $bean = R::dispense( 'bean' );
547 $bean->money = '$123.45';
551 $cols = R::getColumns( 'bean' );
553 asrt( $cols['money'], 'money' );
555 $bean = R::load( 'bean', $bean->id );
557 asrt( $bean->money, '$123.45' );
559 $bean->note = 'taint';
563 $bean = R::load( 'bean', $bean->id );
565 asrt( $bean->money, '$123.45' );
567 $bean->money = '$123,455.01';
571 $bean = $bean->fresh();
573 asrt( $bean->money, '$123,455.01' );
578 * Test negative money data type.
582 public function testTypesNegativeMoney()
584 $bean = R::dispense( 'bean' );
586 $bean->money = '-$123.45';
590 $cols = R::getColumns( 'bean' );
592 asrt( $cols['money'], 'money' );
594 $bean = R::load( 'bean', $bean->id );
596 asrt( $bean->money, '-$123.45' );
598 $bean->note = 'taint';
602 $bean = R::load( 'bean', $bean->id );
604 asrt( $bean->money, '-$123.45' );
609 * Redbean is currently picking up bcrypt hashed passwords
610 * (which look like this: $2y$12$85lAS....SnpDNVGPAC7w0G)
611 * as PostgreSQL money types.
612 * Then, once R::store is called on the bean, it chokes and throws the following error:
613 * PHP Fatal error: Uncaught [22P02] - SQLSTATE[22P02]: Invalid text representation: 7 ERROR:
614 * invalid input syntax for type money: ....
618 public function testTypesInvalidMoney()
620 $bean = R::dispense( 'bean' );
622 $bean->nomoney = '$2y$12$85lAS';
626 $cols = R::getColumns( 'bean' );
628 asrt( $cols['nomoney'], 'text' );
630 $bean = R::load( 'bean', $bean->id );
632 asrt( $bean->nomoney, '$2y$12$85lAS' );
634 $bean->note = 'taint';
638 $bean = R::load( 'bean', $bean->id );
640 asrt( $bean->nomoney, '$2y$12$85lAS' );
644 * Test types of strings.
648 public function testTypesStrings()
650 $bean = R::dispense( 'bean' );
652 $bean->data = 'abcdefghijk';
656 $cols = R::getColumns( 'bean' );
658 asrt( $cols['data'], 'text' );
660 $bean = R::load( 'bean', $bean->id );
662 asrt( $bean->data, 'abcdefghijk' );
664 $bean->data = '(1,2)';
668 $cols = R::getColumns( 'bean' );
670 asrt( $cols['data'], 'text' );
672 $bean->data = '[(1.2,1.4),(2.2,34)]';
676 $cols = R::getColumns( 'bean' );
678 asrt( $cols['data'], 'text' );
680 $bean->data = '<(9.2,1.2),7.9>';
684 $cols = R::getColumns( 'bean' );
686 asrt( $cols['data'], 'text' );
692 $cols = R::getColumns( 'bean' );
694 asrt( $cols['data'], 'text' );
696 $bean->data = '2012-10-10 10:00:00';
700 $cols = R::getColumns( 'bean' );
702 asrt( $cols['data'], 'text' );