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

   1 <?php
   2
   3 namespace RedUNIT\Postgres;
   4
   5 use RedUNIT\Postgres as Postgres;
   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 PostgreSQL.
  13  * Also tests whether the correct contraint action has been selected.
  14  *
  15  * @file    RedUNIT/Postgres/Foreignkeys.php
  16  * @desc    Tests the 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 Postgres
  25 {
  26         /**
  27          * Test foreign keys with postgres.
  28          */
  29         public function testForeignKeysWithPostgres()
  30         {
  31                 testpack( 'Test Postgres Foreign keys' );
  32                 $a = R::getWriter()->addFK( 'a', 'b', 'c', 'd' ); //must fail
  33                 pass(); //survive without exception
  34                 asrt( $a, FALSE ); //must return false
  35                 $book  = R::dispense( 'book' );
  36                 $page  = R::dispense( 'page' );
  37                 $cover = R::dispense( 'cover' );
  38                 list( $g1, $g2 ) = R::dispense( 'genre', 2 );
  39                 $g1->name = '1';
  40                 $g2->name = '2';
  41                 $book->ownPage     = array( $page );
  42                 $book->cover       = $cover;
  43                 $book->sharedGenre = array( $g1, $g2 );
  44                 R::store( $book );
  45                 $sql = "SELECT
  46                     tc.constraint_name, tc.table_name, kcu.column_name,
  47                     ccu.table_name AS foreign_table_name,
  48                     ccu.column_name AS foreign_column_name
  49                 FROM
  50                     information_schema.table_constraints AS tc
  51                     JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
  52                     JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
  53                 WHERE constraint_type = 'FOREIGN KEY' AND (tc.table_name='book' OR tc.table_name='book_genre' OR tc.table_name='page');";
  54                 $fks = R::getAll( $sql );
  55                 $json = '[
  56                         {
  57                                 "constraint_name": "book_cover_id_fkey",
  58                                 "table_name": "book",
  59                                 "column_name": "cover_id",
  60                                 "foreign_table_name": "cover",
  61                                 "foreign_column_name": "id"
  62                         },
  63                         {
  64                                 "constraint_name": "page_book_id_fkey",
  65                                 "table_name": "page",
  66                                 "column_name": "book_id",
  67                                 "foreign_table_name": "book",
  68                                 "foreign_column_name": "id"
  69                         },
  70                         {
  71                                 "constraint_name": "book_genre_genre_id_fkey",
  72                                 "table_name": "book_genre",
  73                                 "column_name": "genre_id",
  74                                 "foreign_table_name": "genre",
  75                                 "foreign_column_name": "id"
  76                         },
  77                         {
  78                                 "constraint_name": "book_genre_book_id_fkey",
  79                                 "table_name": "book_genre",
  80                                 "column_name": "book_id",
  81                                 "foreign_table_name": "book",
  82                                 "foreign_column_name": "id"
  83                         }
  84                 ]';
  85                 $j  = json_encode( $fks );
  86                 $j1 = json_decode( $j, TRUE );
  87                 $j2 = json_decode( $json, TRUE );
  88                 foreach ( $j1 as $jrow ) {
  89                         $s = json_encode( $jrow );
  90                         $found = 0;
  91                         foreach ( $j2 as $k => $j2row ) {
  92                                 if ( json_encode( $j2row ) === $s ) {
  93                                         pass();
  94                                         unset( $j2[$k] );
  95                                         $found = 1;
  96                                 }
  97                         }
  98                         if ( !$found ) fail();
  99                 }
 100         }
 101
 102         /**
 103          * Test constraint function directly in Writer.
 104          *
 105          * @return void
 106          */
 107         public function testConstraint()
 108         {
 109                 R::nuke();
 110                 $database = R::getCell('SELECT current_database()');
 111                 $sql = 'CREATE TABLE book (id SERIAL PRIMARY KEY)';
 112                 R::exec( $sql );
 113                 $sql = 'CREATE TABLE page (id SERIAL PRIMARY KEY)';
 114                 R::exec( $sql );
 115                 $sql = 'CREATE TABLE book_page (
 116                         id SERIAL PRIMARY KEY,
 117                         book_id INTEGER,
 118                         page_id INTEGER
 119                 )';
 120                 R::exec( $sql );
 121                 $writer = R::getWriter();
 122                 $sql = "
 123                         SELECT
 124                                 COUNT(*)
 125                         FROM information_schema.key_column_usage AS k
 126                         LEFT JOIN information_schema.table_constraints AS c ON c.constraint_name = k.constraint_name
 127                         WHERE k.table_catalog = '$database'
 128                                 AND k.table_schema = 'public'
 129                                 AND k.table_name = 'book_page'
 130                                 AND c.constraint_type = 'FOREIGN KEY'";
 131                 $numFKS = R::getCell( $sql );
 132                 asrt( (int) $numFKS, 0 );
 133                 $writer->addFK( 'book_page', 'book', 'book_id', 'id', TRUE );
 134                 $writer->addFK( 'book_page', 'page', 'page_id', 'id', TRUE );
 135                 $numFKS = R::getCell( $sql );
 136                 asrt( (int) $numFKS, 2 );
 137                 $writer->addFK( 'book_page', 'book', 'book_id', 'id', TRUE );
 138                 $writer->addFK( 'book_page', 'page', 'page_id', 'id', TRUE );
 139                 $numFKS = R::getCell( $sql );
 140                 asrt( (int) $numFKS, 2 );
 141         }
 142
 143         /**
 144          * Test adding foreign keys.
 145          *
 146          * @return void
 147          */
 148         public function testAddingForeignKey()
 149         {
 150                 R::nuke();
 151                 $database = R::getCell('SELECT current_database()');
 152                 $sql = 'CREATE TABLE book (
 153                         id SERIAL PRIMARY KEY
 154                 )';
 155                 R::exec( $sql );
 156                 $sql = 'CREATE TABLE page (
 157                         id SERIAL PRIMARY KEY,
 158                         book_id INTEGER
 159                 )';
 160                 R::exec( $sql );
 161                 $writer = R::getWriter();
 162                 $sql = "
 163                         SELECT
 164                                 COUNT(*)
 165                         FROM information_schema.key_column_usage AS k
 166                         LEFT JOIN information_schema.table_constraints AS c ON c.constraint_name = k.constraint_name
 167                         WHERE k.table_catalog = '$database'
 168                                 AND k.table_schema = 'public'
 169                                 AND k.table_name = 'page'
 170                                 AND c.constraint_type = 'FOREIGN KEY'";
 171                 $numFKS = R::getCell( $sql );
 172                 asrt( (int) $numFKS, 0 );
 173                 $writer->addFK('page', 'page', 'book_id', 'id', TRUE);
 174                 $sql = "
 175                         SELECT
 176                                 COUNT(*)
 177                         FROM information_schema.key_column_usage AS k
 178                         LEFT JOIN information_schema.table_constraints AS c ON c.constraint_name = k.constraint_name
 179                         WHERE k.table_catalog = '$database'
 180                                 AND k.table_schema = 'public'
 181                                 AND k.table_name = 'page'
 182                                 AND c.constraint_type = 'FOREIGN KEY'";
 183                 $numFKS = R::getCell( $sql );
 184                 asrt( (int) $numFKS, 1 );
 185                 //dont add twice
 186                 $writer->addFK('page', 'page', 'book_id', 'id', TRUE);
 187                 $sql = "
 188                         SELECT
 189                                 COUNT(*)
 190                         FROM information_schema.key_column_usage AS k
 191                         LEFT JOIN information_schema.table_constraints AS c ON c.constraint_name = k.constraint_name
 192                         WHERE k.table_catalog = '$database'
 193                                 AND k.table_schema = 'public'
 194                                 AND k.table_name = 'page'
 195                                 AND c.constraint_type = 'FOREIGN KEY'";
 196                 $numFKS = R::getCell( $sql );
 197                 asrt( (int) $numFKS, 1 );
 198                 //even if it is different
 199                 $writer->addFK('page', 'page', 'book_id', 'id', FALSE);
 200                 $sql = "
 201                         SELECT
 202                                 COUNT(*)
 203                         FROM information_schema.key_column_usage AS k
 204                         LEFT JOIN information_schema.table_constraints AS c ON c.constraint_name = k.constraint_name
 205                         WHERE k.table_catalog = '$database'
 206                                 AND k.table_schema = 'public'
 207                                 AND k.table_name = 'page'
 208                                 AND c.constraint_type = 'FOREIGN KEY'";
 209                 $numFKS = R::getCell( $sql );
 210                 asrt( (int) $numFKS, 1 );
 211                 R::nuke();
 212                 $sql = 'CREATE TABLE book (
 213                         id SERIAL PRIMARY KEY
 214                 )';
 215                 R::exec( $sql );
 216                 $sql = 'CREATE TABLE page (
 217                         id SERIAL PRIMARY KEY,
 218                         book_id INTEGER
 219                 )';
 220                 R::exec( $sql );
 221                 $writer = R::getWriter();
 222                 $sql = "
 223                         SELECT
 224                                 COUNT(*)
 225                         FROM information_schema.key_column_usage AS k
 226                         LEFT JOIN information_schema.table_constraints AS c ON c.constraint_name = k.constraint_name
 227                         WHERE k.table_catalog = '$database'
 228                                 AND k.table_schema = 'public'
 229                                 AND k.table_name = 'page'
 230                                 AND c.constraint_type = 'FOREIGN KEY'";
 231                 $numFKS = R::getCell( $sql );
 232                 asrt( (int) $numFKS, 0 );
 233                 $writer->addFK('page', 'page', 'book_id', 'id', FALSE);
 234                 $sql = "
 235                         SELECT
 236                                 COUNT(*)
 237                         FROM information_schema.key_column_usage AS k
 238                         LEFT JOIN information_schema.table_constraints AS c ON c.constraint_name = k.constraint_name
 239                         WHERE k.table_catalog = '$database'
 240                                 AND k.table_schema = 'public'
 241                                 AND k.table_name = 'page'
 242                                 AND c.constraint_type = 'FOREIGN KEY'";
 243                 $numFKS = R::getCell( $sql );
 244                 asrt( (int) $numFKS, 1 );
 245         }
 246
 247         /**
 248          * Test whether we can manually create indexes.
 249          *
 250          * @return void
 251          */
 252         public function testAddingIndex()
 253         {
 254                 R::nuke();
 255                 $sql = 'CREATE TABLE song (
 256                         id SERIAL PRIMARY KEY,
 257                         album_id INTEGER,
 258                         category VARCHAR(255)
 259                 )';
 260                 R::exec( $sql );
 261                 $indexes = R::getAll( " SELECT * FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'song' ");
 262                 asrt( count( $indexes ), 1 );
 263                 $writer = R::getWriter();
 264                 $writer->addIndex( 'song', 'index1', 'album_id' );
 265                 $indexes = R::getAll( " SELECT * FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'song' ");
 266                 asrt( count( $indexes ), 2 );
 267                 //Cant add the same index twice
 268                 $writer->addIndex( 'song', 'index1', 'album_id' );
 269                 $indexes = R::getAll( " SELECT * FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'song' ");
 270                 asrt( count( $indexes ), 2 );
 271                 $writer->addIndex( 'song', 'index2', 'category' );
 272                 $indexes = R::getAll( " SELECT * FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'song' ");
 273                 asrt( count( $indexes ), 3 );
 274                 //Dont fail, just dont
 275                 try {
 276                         $writer->addIndex( 'song', 'index3', 'nonexistant' );
 277                         pass();
 278                 } catch( \Exception $e ) {
 279                         fail();
 280                 }
 281                 $indexes = R::getAll( " SELECT * FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'song' ");
 282                 asrt( count( $indexes ), 3 );
 283                 try {
 284                         $writer->addIndex( 'nonexistant', 'index4', 'nonexistant' );
 285                         pass();
 286                 } catch( \Exception $e ) {
 287                         fail();
 288                 }
 289                 $indexes = R::getAll( " SELECT * FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'song' ");
 290                 asrt( count( $indexes ), 3 );
 291         }
 292 }