www.aleph1.co.uk Git - yaffs-website/blob - vendor/gabordemooij/redbean/testing/RedUNIT/Mysql/Foreignkeys.php

   1 <?php
   2
   3 namespace RedUNIT\Mysql;
   4
   5 use RedUNIT\Mysql as Mysql;
   6 use RedBeanPHP\Facade as R;
   7
   8 /**
   9  * Foreignkeys
  10  *
  11  * Tests creation and validity of foreign keys,
  12  * foreign key constraints and indexes in Mysql/MariaDB.
  13  * Also tests whether the correct contraint action has been selected.
  14  *
  15  * @file    RedUNIT/Mysql/Foreignkeys.php
  16  * @desc    Tests creation of foreign keys.
  17  * @author  Gabor de Mooij and the RedBeanPHP Community
  18  * @license New BSD/GPLv2
  19  *
  20  * (c) G.J.G.T. (Gabor) de Mooij and the RedBeanPHP Community.
  21  * This source file is subject to the New BSD/GPLv2 License that is bundled
  22  * with this source code in the file license.txt.
  23  */
  24 class Foreignkeys extends Mysql
  25 {
  26         /**
  27          * Test whether we can use foreign keys with keywords.
  28          *
  29          * @return void
  30          */
  31         public function testKWConflicts()
  32         {
  33                 R::nuke();
  34                 $metrics = R::dispense( 'metrics' );
  35                 $constraint = R::dispense( 'constraint' );
  36                 $constraint->xownMetrics[] = $metrics;
  37                 R::store( $constraint );
  38                 asrt( 1, R::count( 'metrics' ) );
  39                 R::trash($constraint);
  40                 asrt( 0, R::count( 'metrics') );
  41         }
  42
  43         /**
  44          * Basic FK tests.
  45          *
  46          * @return void
  47          */
  48         public function testFKS()
  49         {
  50                 $book  = R::dispense( 'book' );
  51                 $page  = R::dispense( 'page' );
  52                 $cover = R::dispense( 'cover' );
  53
  54                 list( $g1, $g2 ) = R::dispense( 'genre', 2 );
  55
  56                 $g1->name = '1';
  57                 $g2->name = '2';
  58
  59                 $book->ownPage = array( $page );
  60
  61                 $book->cover = $cover;
  62
  63                 $book->sharedGenre = array( $g1, $g2 );
  64
  65                 R::store( $book );
  66
  67                 $fkbook  = R::getAll( 'describe book' );
  68                 $fkgenre = R::getAll( 'describe book_genre' );
  69                 $fkpage  = R::getAll( 'describe cover' );
  70
  71                 $j = json_encode( R::getAll( 'SELECT
  72                 ke.referenced_table_name parent,
  73                 ke.table_name child,
  74                 ke.constraint_name
  75                 FROM
  76                 information_schema.KEY_COLUMN_USAGE ke
  77                 WHERE
  78                 ke.referenced_table_name IS NOT NULL
  79                 AND ke.CONSTRAINT_SCHEMA="oodb"
  80                 ORDER BY
  81                 constraint_name;' ) );
  82
  83                 $json = '[
  84                         {
  85                                 "parent": "genre",
  86                                 "child": "book_genre",
  87                                 "constraint_name": "c_fk_book_genre_genre_id"
  88                         },
  89                         {
  90                                 "parent": "book",
  91                                 "child": "book_genre",
  92                                 "constraint_name": "c_fk_book_genre_book_id"
  93                         },
  94                         {
  95                                 "parent": "cover",
  96                                 "child": "book",
  97                                 "constraint_name": "c_fk_book_cover_id"
  98                         },
  99                         {
 100                                 "parent": "book",
 101                                 "child": "page",
 102                                 "constraint_name": "c_fk_page_book_id"
 103                         }
 104                 ]';
 105
 106                 $j1 = json_decode( $j, TRUE );
 107
 108                 $j2 = json_decode( $json, TRUE );
 109
 110                 foreach ( $j1 as $jrow ) {
 111                         $s = json_encode( $jrow );
 112
 113                         $found = 0;
 114
 115                         foreach ( $j2 as $k => $j2row ) {
 116
 117                                 if ( json_encode( $j2row ) === $s ) {
 118                                         pass();
 119
 120                                         unset( $j2[$k] );
 121
 122                                         $found = 1;
 123                                         break;
 124                                 }
 125                         }
 126
 127                         if ( !$found ) fail();
 128                 }
 129         }
 130
 131         /**
 132          * Test widen for constraint.
 133          *
 134          * @return void
 135          */
 136         public function testWideningColumnForConstraint()
 137         {
 138                 testpack( 'widening column for constraint' );
 139
 140                 $bean1 = R::dispense( 'project' );
 141                 $bean2 = R::dispense( 'invoice' );
 142
 143                 $bean3 = R::getRedBean()->dispense( 'invoice_project' );
 144
 145                 $bean3->project_id = false;
 146                 $bean3->invoice_id = true;
 147
 148                 R::store( $bean3 );
 149
 150                 $cols = R::getColumns( 'invoice_project' );
 151
 152                 asrt( $cols['project_id'], "int(11) unsigned" );
 153                 asrt( $cols['invoice_id'], "int(11) unsigned" );
 154         }
 155
 156         /**
 157          * Test adding of constraints directly by invoking
 158          * the writer method.
 159          *
 160          * @return void
 161          */
 162         public function testContrain()
 163         {
 164                 R::nuke();
 165
 166                 $sql   = '
 167                         CREATE TABLE book (
 168                                 id INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
 169                                 PRIMARY KEY ( id )
 170                         )
 171                         ENGINE = InnoDB
 172                 ';
 173
 174                 R::exec( $sql );
 175
 176                 $sql   = '
 177                         CREATE TABLE page (
 178                                 id INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
 179                                 PRIMARY KEY ( id )
 180                         )
 181                         ENGINE = InnoDB
 182                 ';
 183
 184                 R::exec( $sql );
 185
 186                 $sql   = '
 187                         CREATE TABLE book_page (
 188                                 id INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
 189                                 book_id INT( 11 ) UNSIGNED NOT NULL,
 190                                 page_id INT( 11 ) UNSIGNED NOT NULL,
 191                                 PRIMARY KEY ( id )
 192                         )
 193                         ENGINE = InnoDB
 194                 ';
 195
 196                 R::exec( $sql );
 197
 198                 $numOfFKS = R::getCell('
 199                         SELECT COUNT(*)
 200                         FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
 201                         WHERE TABLE_NAME = "book_page" AND DELETE_RULE = "CASCADE"');
 202
 203                 asrt( (int) $numOfFKS, 0 );
 204
 205                 $writer = R::getWriter();
 206
 207                 $writer->addFK( 'book_page', 'book', 'book_id', 'id', TRUE );
 208                 $writer->addFK( 'book_page', 'page', 'page_id', 'id', TRUE );
 209
 210                 $numOfFKS = R::getCell('
 211                         SELECT COUNT(*)
 212                         FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
 213                         WHERE TABLE_NAME = "book_page" AND DELETE_RULE = "CASCADE"');
 214
 215                 asrt( (int) $numOfFKS, 2 );
 216
 217                 $writer->addFK( 'book_page', 'book', 'book_id', 'id', TRUE );
 218                 $writer->addFK( 'book_page', 'page', 'page_id', 'id', TRUE );
 219
 220                 $numOfFKS = R::getCell('
 221                         SELECT COUNT(*)
 222                         FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
 223                         WHERE TABLE_NAME = "book_page" AND DELETE_RULE = "CASCADE"');
 224
 225                 asrt( (int) $numOfFKS, 2 );
 226         }
 227
 228         /**
 229          * Test adding foreign keys.
 230          *
 231          * @return void
 232          */
 233         public function testAddingForeignKey()
 234         {
 235                 R::nuke();
 236
 237                 $sql   = '
 238                         CREATE TABLE book (
 239                                 id INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
 240                                 PRIMARY KEY ( id )
 241                         )
 242                         ENGINE = InnoDB
 243                 ';
 244
 245                 R::exec( $sql );
 246
 247                 $sql   = '
 248                         CREATE TABLE page (
 249                                 id INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
 250                                 book_id INT( 11 ) UNSIGNED NOT NULL,
 251                                 PRIMARY KEY ( id )
 252                         )
 253                         ENGINE = InnoDB
 254                 ';
 255
 256                 R::exec( $sql );
 257
 258                 $numOfFKS = R::getCell('
 259                         SELECT COUNT(*)
 260                         FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
 261                         WHERE TABLE_NAME = "page" AND DELETE_RULE = "CASCADE"');
 262
 263                 asrt( (int) $numOfFKS, 0 );
 264
 265                 $writer = R::getWriter();
 266
 267                 //Can we add a foreign key with cascade?
 268                 $writer->addFK('page', 'book', 'book_id', 'id', TRUE);
 269
 270                 $numOfFKS = R::getCell('
 271                         SELECT COUNT(*)
 272                         FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
 273                         WHERE TABLE_NAME = "page" AND DELETE_RULE = "CASCADE"');
 274
 275                 asrt( (int) $numOfFKS, 1 );
 276
 277                 //dont add it twice
 278                 $writer->addFK('page', 'book', 'book_id', 'id', TRUE);
 279
 280                 $numOfFKS = R::getCell('
 281                         SELECT COUNT(*)
 282                         FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
 283                         WHERE TABLE_NAME = "page" AND DELETE_RULE = "CASCADE"');
 284
 285                 asrt( (int) $numOfFKS, 1 );
 286
 287                 //even if different
 288                 $writer->addFK('page', 'book', 'book_id', 'id', FALSE);
 289
 290                 $numOfFKS = R::getCell('
 291                         SELECT COUNT(*)
 292                         FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
 293                         WHERE TABLE_NAME = "page" ');
 294
 295                 asrt( (int) $numOfFKS, 1 );
 296
 297                 //Now add non-dep key
 298                 R::nuke();
 299
 300                 $sql   = '
 301                         CREATE TABLE book (
 302                                 id INT( 11 ) UNSIGNED NULL AUTO_INCREMENT,
 303                                 PRIMARY KEY ( id )
 304                         )
 305                         ENGINE = InnoDB
 306                 ';
 307
 308                 R::exec( $sql );
 309
 310                 $sql   = '
 311                         CREATE TABLE page (
 312                                 id INT( 11 ) UNSIGNED AUTO_INCREMENT,
 313                                 book_id INT( 11 ) UNSIGNED NULL,
 314                                 PRIMARY KEY ( id )
 315                         )
 316                         ENGINE = InnoDB
 317                 ';
 318
 319                 R::exec( $sql );
 320
 321                 $numOfFKS = R::getCell('
 322                         SELECT COUNT(*)
 323                         FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
 324                         WHERE TABLE_NAME = "page" AND DELETE_RULE = "CASCADE"');
 325
 326                 asrt( (int) $numOfFKS, 0 );
 327
 328                 //even if different
 329                 $writer->addFK('page', 'book', 'book_id', 'id', FALSE);
 330
 331                 $numOfFKS = R::getCell('
 332                         SELECT COUNT(*)
 333                         FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
 334                         WHERE TABLE_NAME = "page" AND DELETE_RULE = "CASCADE"');
 335
 336                 asrt( (int) $numOfFKS, 0 );
 337
 338                 $numOfFKS = R::getCell('
 339                         SELECT COUNT(*)
 340                         FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
 341                         WHERE TABLE_NAME = "page" AND DELETE_RULE = "SET NULL"');
 342
 343                 asrt( (int) $numOfFKS, 1 );
 344
 345                 $writer->addFK('page', 'book', 'book_id', 'id', TRUE);
 346
 347                 $numOfFKS = R::getCell('
 348                         SELECT COUNT(*)
 349                         FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
 350                         WHERE TABLE_NAME = "page" ');
 351         }
 352
 353         /**
 354          * Test whether we can manually create indexes.
 355          *
 356          * @return void
 357          */
 358         public function testAddingIndex()
 359         {
 360                 R::nuke();
 361
 362                 $sql   = '
 363                         CREATE TABLE song (
 364                                 id INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
 365                                 album_id INT( 11 ) UNSIGNED NOT NULL,
 366                                 category VARCHAR( 255 ),
 367                                 PRIMARY KEY ( id )
 368                         )
 369                         ENGINE = InnoDB
 370                 ';
 371
 372                 R::exec( $sql );
 373
 374                 $sql = 'SHOW INDEX FROM song';
 375
 376                 $indexes = R::getAll( $sql );
 377
 378                 asrt( count( $indexes ), 1 );
 379                 asrt( $indexes[0]['Table'], 'song' );
 380                 asrt( $indexes[0]['Key_name'], 'PRIMARY' );
 381
 382                 $writer = R::getWriter();
 383
 384                 $writer->addIndex('song', 'index1', 'album_id');
 385
 386                 $indexes = R::getAll( 'SHOW INDEX FROM song' );
 387
 388                 asrt( count( $indexes ), 2 );
 389                 asrt( $indexes[0]['Table'], 'song' );
 390                 asrt( $indexes[0]['Key_name'], 'PRIMARY' );
 391                 asrt( $indexes[1]['Table'], 'song' );
 392                 asrt( $indexes[1]['Key_name'], 'index1' );
 393
 394                 //Cant add the same index twice
 395                 $writer->addIndex('song', 'index2', 'category');
 396
 397                 $indexes = R::getAll( 'SHOW INDEX FROM song' );
 398
 399                 asrt( count( $indexes ), 3 );
 400
 401                 //Dont fail, just dont
 402                 try {
 403                         $writer->addIndex('song', 'index3', 'nonexistant');
 404                         pass();
 405                 } catch( \Exception $e ) {
 406                         fail();
 407                 }
 408
 409                 asrt( count( $indexes ), 3 );
 410
 411                 try {
 412                         $writer->addIndex('nonexistant', 'index4', 'nonexistant');
 413                         pass();
 414                 } catch( \Exception $e ) {
 415                         fail();
 416                 }
 417
 418                 asrt( count( $indexes ), 3 );
 419
 420                 try {
 421                         $writer->addIndex('nonexistant', '', 'nonexistant');
 422                         pass();
 423                 } catch( \Exception $e ) {
 424                         fail();
 425                 }
 426
 427                 asrt( count( $indexes ), 3 );
 428         }
 429 }