+++ /dev/null
-<?php
-
-namespace RedUNIT\Postgres;
-
-use RedUNIT\Postgres as Postgres;
-use RedBeanPHP\Facade as R;
-
-/**
- * Foreignkeys
- *
- * Tests creation and validity of foreign keys,
- * foreign key constraints and indexes in PostgreSQL.
- * Also tests whether the correct contraint action has been selected.
- *
- * @file RedUNIT/Postgres/Foreignkeys.php
- * @desc Tests the creation of foreign keys.
- * @author Gabor de Mooij and the RedBeanPHP Community
- * @license New BSD/GPLv2
- *
- * (c) G.J.G.T. (Gabor) de Mooij and the RedBeanPHP Community.
- * This source file is subject to the New BSD/GPLv2 License that is bundled
- * with this source code in the file license.txt.
- */
-class Foreignkeys extends Postgres
-{
- /**
- * Test foreign keys with postgres.
- */
- public function testForeignKeysWithPostgres()
- {
- testpack( 'Test Postgres Foreign keys' );
- $a = R::getWriter()->addFK( 'a', 'b', 'c', 'd' ); //must fail
- pass(); //survive without exception
- asrt( $a, FALSE ); //must return false
- $book = R::dispense( 'book' );
- $page = R::dispense( 'page' );
- $cover = R::dispense( 'cover' );
- list( $g1, $g2 ) = R::dispense( 'genre', 2 );
- $g1->name = '1';
- $g2->name = '2';
- $book->ownPage = array( $page );
- $book->cover = $cover;
- $book->sharedGenre = array( $g1, $g2 );
- R::store( $book );
- $sql = "SELECT
- tc.constraint_name, tc.table_name, kcu.column_name,
- ccu.table_name AS foreign_table_name,
- ccu.column_name AS foreign_column_name
- FROM
- information_schema.table_constraints AS tc
- JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
- JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
- WHERE constraint_type = 'FOREIGN KEY' AND (tc.table_name='book' OR tc.table_name='book_genre' OR tc.table_name='page');";
- $fks = R::getAll( $sql );
- $json = '[
- {
- "constraint_name": "book_cover_id_fkey",
- "table_name": "book",
- "column_name": "cover_id",
- "foreign_table_name": "cover",
- "foreign_column_name": "id"
- },
- {
- "constraint_name": "page_book_id_fkey",
- "table_name": "page",
- "column_name": "book_id",
- "foreign_table_name": "book",
- "foreign_column_name": "id"
- },
- {
- "constraint_name": "book_genre_genre_id_fkey",
- "table_name": "book_genre",
- "column_name": "genre_id",
- "foreign_table_name": "genre",
- "foreign_column_name": "id"
- },
- {
- "constraint_name": "book_genre_book_id_fkey",
- "table_name": "book_genre",
- "column_name": "book_id",
- "foreign_table_name": "book",
- "foreign_column_name": "id"
- }
- ]';
- $j = json_encode( $fks );
- $j1 = json_decode( $j, TRUE );
- $j2 = json_decode( $json, TRUE );
- foreach ( $j1 as $jrow ) {
- $s = json_encode( $jrow );
- $found = 0;
- foreach ( $j2 as $k => $j2row ) {
- if ( json_encode( $j2row ) === $s ) {
- pass();
- unset( $j2[$k] );
- $found = 1;
- }
- }
- if ( !$found ) fail();
- }
- }
-
- /**
- * Test constraint function directly in Writer.
- *
- * @return void
- */
- public function testConstraint()
- {
- R::nuke();
- $database = R::getCell('SELECT current_database()');
- $sql = 'CREATE TABLE book (id SERIAL PRIMARY KEY)';
- R::exec( $sql );
- $sql = 'CREATE TABLE page (id SERIAL PRIMARY KEY)';
- R::exec( $sql );
- $sql = 'CREATE TABLE book_page (
- id SERIAL PRIMARY KEY,
- book_id INTEGER,
- page_id INTEGER
- )';
- R::exec( $sql );
- $writer = R::getWriter();
- $sql = "
- SELECT
- COUNT(*)
- FROM information_schema.key_column_usage AS k
- LEFT JOIN information_schema.table_constraints AS c ON c.constraint_name = k.constraint_name
- WHERE k.table_catalog = '$database'
- AND k.table_schema = 'public'
- AND k.table_name = 'book_page'
- AND c.constraint_type = 'FOREIGN KEY'";
- $numFKS = R::getCell( $sql );
- asrt( (int) $numFKS, 0 );
- $writer->addFK( 'book_page', 'book', 'book_id', 'id', TRUE );
- $writer->addFK( 'book_page', 'page', 'page_id', 'id', TRUE );
- $numFKS = R::getCell( $sql );
- asrt( (int) $numFKS, 2 );
- $writer->addFK( 'book_page', 'book', 'book_id', 'id', TRUE );
- $writer->addFK( 'book_page', 'page', 'page_id', 'id', TRUE );
- $numFKS = R::getCell( $sql );
- asrt( (int) $numFKS, 2 );
- }
-
- /**
- * Test adding foreign keys.
- *
- * @return void
- */
- public function testAddingForeignKey()
- {
- R::nuke();
- $database = R::getCell('SELECT current_database()');
- $sql = 'CREATE TABLE book (
- id SERIAL PRIMARY KEY
- )';
- R::exec( $sql );
- $sql = 'CREATE TABLE page (
- id SERIAL PRIMARY KEY,
- book_id INTEGER
- )';
- R::exec( $sql );
- $writer = R::getWriter();
- $sql = "
- SELECT
- COUNT(*)
- FROM information_schema.key_column_usage AS k
- LEFT JOIN information_schema.table_constraints AS c ON c.constraint_name = k.constraint_name
- WHERE k.table_catalog = '$database'
- AND k.table_schema = 'public'
- AND k.table_name = 'page'
- AND c.constraint_type = 'FOREIGN KEY'";
- $numFKS = R::getCell( $sql );
- asrt( (int) $numFKS, 0 );
- $writer->addFK('page', 'page', 'book_id', 'id', TRUE);
- $sql = "
- SELECT
- COUNT(*)
- FROM information_schema.key_column_usage AS k
- LEFT JOIN information_schema.table_constraints AS c ON c.constraint_name = k.constraint_name
- WHERE k.table_catalog = '$database'
- AND k.table_schema = 'public'
- AND k.table_name = 'page'
- AND c.constraint_type = 'FOREIGN KEY'";
- $numFKS = R::getCell( $sql );
- asrt( (int) $numFKS, 1 );
- //dont add twice
- $writer->addFK('page', 'page', 'book_id', 'id', TRUE);
- $sql = "
- SELECT
- COUNT(*)
- FROM information_schema.key_column_usage AS k
- LEFT JOIN information_schema.table_constraints AS c ON c.constraint_name = k.constraint_name
- WHERE k.table_catalog = '$database'
- AND k.table_schema = 'public'
- AND k.table_name = 'page'
- AND c.constraint_type = 'FOREIGN KEY'";
- $numFKS = R::getCell( $sql );
- asrt( (int) $numFKS, 1 );
- //even if it is different
- $writer->addFK('page', 'page', 'book_id', 'id', FALSE);
- $sql = "
- SELECT
- COUNT(*)
- FROM information_schema.key_column_usage AS k
- LEFT JOIN information_schema.table_constraints AS c ON c.constraint_name = k.constraint_name
- WHERE k.table_catalog = '$database'
- AND k.table_schema = 'public'
- AND k.table_name = 'page'
- AND c.constraint_type = 'FOREIGN KEY'";
- $numFKS = R::getCell( $sql );
- asrt( (int) $numFKS, 1 );
- R::nuke();
- $sql = 'CREATE TABLE book (
- id SERIAL PRIMARY KEY
- )';
- R::exec( $sql );
- $sql = 'CREATE TABLE page (
- id SERIAL PRIMARY KEY,
- book_id INTEGER
- )';
- R::exec( $sql );
- $writer = R::getWriter();
- $sql = "
- SELECT
- COUNT(*)
- FROM information_schema.key_column_usage AS k
- LEFT JOIN information_schema.table_constraints AS c ON c.constraint_name = k.constraint_name
- WHERE k.table_catalog = '$database'
- AND k.table_schema = 'public'
- AND k.table_name = 'page'
- AND c.constraint_type = 'FOREIGN KEY'";
- $numFKS = R::getCell( $sql );
- asrt( (int) $numFKS, 0 );
- $writer->addFK('page', 'page', 'book_id', 'id', FALSE);
- $sql = "
- SELECT
- COUNT(*)
- FROM information_schema.key_column_usage AS k
- LEFT JOIN information_schema.table_constraints AS c ON c.constraint_name = k.constraint_name
- WHERE k.table_catalog = '$database'
- AND k.table_schema = 'public'
- AND k.table_name = 'page'
- AND c.constraint_type = 'FOREIGN KEY'";
- $numFKS = R::getCell( $sql );
- asrt( (int) $numFKS, 1 );
- }
-
- /**
- * Test whether we can manually create indexes.
- *
- * @return void
- */
- public function testAddingIndex()
- {
- R::nuke();
- $sql = 'CREATE TABLE song (
- id SERIAL PRIMARY KEY,
- album_id INTEGER,
- category VARCHAR(255)
- )';
- R::exec( $sql );
- $indexes = R::getAll( " SELECT * FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'song' ");
- asrt( count( $indexes ), 1 );
- $writer = R::getWriter();
- $writer->addIndex( 'song', 'index1', 'album_id' );
- $indexes = R::getAll( " SELECT * FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'song' ");
- asrt( count( $indexes ), 2 );
- //Cant add the same index twice
- $writer->addIndex( 'song', 'index1', 'album_id' );
- $indexes = R::getAll( " SELECT * FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'song' ");
- asrt( count( $indexes ), 2 );
- $writer->addIndex( 'song', 'index2', 'category' );
- $indexes = R::getAll( " SELECT * FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'song' ");
- asrt( count( $indexes ), 3 );
- //Dont fail, just dont
- try {
- $writer->addIndex( 'song', 'index3', 'nonexistant' );
- pass();
- } catch( \Exception $e ) {
- fail();
- }
- $indexes = R::getAll( " SELECT * FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'song' ");
- asrt( count( $indexes ), 3 );
- try {
- $writer->addIndex( 'nonexistant', 'index4', 'nonexistant' );
- pass();
- } catch( \Exception $e ) {
- fail();
- }
- $indexes = R::getAll( " SELECT * FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'song' ");
- asrt( count( $indexes ), 3 );
- }
-}
projects / yaffs-website / blobdiff