+++ /dev/null
-<?php
-
-namespace RedUNIT\Mysql;
-
-use RedUNIT\Mysql as Mysql;
-use RedBeanPHP\Facade as R;
-
-/**
- * Foreignkeys
- *
- * Tests creation and validity of foreign keys,
- * foreign key constraints and indexes in Mysql/MariaDB.
- * Also tests whether the correct contraint action has been selected.
- *
- * @file RedUNIT/Mysql/Foreignkeys.php
- * @desc Tests 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 Mysql
-{
- /**
- * Test whether we can use foreign keys with keywords.
- *
- * @return void
- */
- public function testKWConflicts()
- {
- R::nuke();
- $metrics = R::dispense( 'metrics' );
- $constraint = R::dispense( 'constraint' );
- $constraint->xownMetrics[] = $metrics;
- R::store( $constraint );
- asrt( 1, R::count( 'metrics' ) );
- R::trash($constraint);
- asrt( 0, R::count( 'metrics') );
- }
-
- /**
- * Basic FK tests.
- *
- * @return void
- */
- public function testFKS()
- {
- $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 );
-
- $fkbook = R::getAll( 'describe book' );
- $fkgenre = R::getAll( 'describe book_genre' );
- $fkpage = R::getAll( 'describe cover' );
-
- $j = json_encode( R::getAll( 'SELECT
- ke.referenced_table_name parent,
- ke.table_name child,
- ke.constraint_name
- FROM
- information_schema.KEY_COLUMN_USAGE ke
- WHERE
- ke.referenced_table_name IS NOT NULL
- AND ke.CONSTRAINT_SCHEMA="oodb"
- ORDER BY
- constraint_name;' ) );
-
- $json = '[
- {
- "parent": "genre",
- "child": "book_genre",
- "constraint_name": "c_fk_book_genre_genre_id"
- },
- {
- "parent": "book",
- "child": "book_genre",
- "constraint_name": "c_fk_book_genre_book_id"
- },
- {
- "parent": "cover",
- "child": "book",
- "constraint_name": "c_fk_book_cover_id"
- },
- {
- "parent": "book",
- "child": "page",
- "constraint_name": "c_fk_page_book_id"
- }
- ]';
-
- $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;
- break;
- }
- }
-
- if ( !$found ) fail();
- }
- }
-
- /**
- * Test widen for constraint.
- *
- * @return void
- */
- public function testWideningColumnForConstraint()
- {
- testpack( 'widening column for constraint' );
-
- $bean1 = R::dispense( 'project' );
- $bean2 = R::dispense( 'invoice' );
-
- $bean3 = R::getRedBean()->dispense( 'invoice_project' );
-
- $bean3->project_id = false;
- $bean3->invoice_id = true;
-
- R::store( $bean3 );
-
- $cols = R::getColumns( 'invoice_project' );
-
- asrt( $cols['project_id'], "int(11) unsigned" );
- asrt( $cols['invoice_id'], "int(11) unsigned" );
- }
-
- /**
- * Test adding of constraints directly by invoking
- * the writer method.
- *
- * @return void
- */
- public function testContrain()
- {
- R::nuke();
-
- $sql = '
- CREATE TABLE book (
- id INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
- PRIMARY KEY ( id )
- )
- ENGINE = InnoDB
- ';
-
- R::exec( $sql );
-
- $sql = '
- CREATE TABLE page (
- id INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
- PRIMARY KEY ( id )
- )
- ENGINE = InnoDB
- ';
-
- R::exec( $sql );
-
- $sql = '
- CREATE TABLE book_page (
- id INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
- book_id INT( 11 ) UNSIGNED NOT NULL,
- page_id INT( 11 ) UNSIGNED NOT NULL,
- PRIMARY KEY ( id )
- )
- ENGINE = InnoDB
- ';
-
- R::exec( $sql );
-
- $numOfFKS = R::getCell('
- SELECT COUNT(*)
- FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
- WHERE TABLE_NAME = "book_page" AND DELETE_RULE = "CASCADE"');
-
- asrt( (int) $numOfFKS, 0 );
-
- $writer = R::getWriter();
-
- $writer->addFK( 'book_page', 'book', 'book_id', 'id', TRUE );
- $writer->addFK( 'book_page', 'page', 'page_id', 'id', TRUE );
-
- $numOfFKS = R::getCell('
- SELECT COUNT(*)
- FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
- WHERE TABLE_NAME = "book_page" AND DELETE_RULE = "CASCADE"');
-
- asrt( (int) $numOfFKS, 2 );
-
- $writer->addFK( 'book_page', 'book', 'book_id', 'id', TRUE );
- $writer->addFK( 'book_page', 'page', 'page_id', 'id', TRUE );
-
- $numOfFKS = R::getCell('
- SELECT COUNT(*)
- FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
- WHERE TABLE_NAME = "book_page" AND DELETE_RULE = "CASCADE"');
-
- asrt( (int) $numOfFKS, 2 );
- }
-
- /**
- * Test adding foreign keys.
- *
- * @return void
- */
- public function testAddingForeignKey()
- {
- R::nuke();
-
- $sql = '
- CREATE TABLE book (
- id INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
- PRIMARY KEY ( id )
- )
- ENGINE = InnoDB
- ';
-
- R::exec( $sql );
-
- $sql = '
- CREATE TABLE page (
- id INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
- book_id INT( 11 ) UNSIGNED NOT NULL,
- PRIMARY KEY ( id )
- )
- ENGINE = InnoDB
- ';
-
- R::exec( $sql );
-
- $numOfFKS = R::getCell('
- SELECT COUNT(*)
- FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
- WHERE TABLE_NAME = "page" AND DELETE_RULE = "CASCADE"');
-
- asrt( (int) $numOfFKS, 0 );
-
- $writer = R::getWriter();
-
- //Can we add a foreign key with cascade?
- $writer->addFK('page', 'book', 'book_id', 'id', TRUE);
-
- $numOfFKS = R::getCell('
- SELECT COUNT(*)
- FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
- WHERE TABLE_NAME = "page" AND DELETE_RULE = "CASCADE"');
-
- asrt( (int) $numOfFKS, 1 );
-
- //dont add it twice
- $writer->addFK('page', 'book', 'book_id', 'id', TRUE);
-
- $numOfFKS = R::getCell('
- SELECT COUNT(*)
- FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
- WHERE TABLE_NAME = "page" AND DELETE_RULE = "CASCADE"');
-
- asrt( (int) $numOfFKS, 1 );
-
- //even if different
- $writer->addFK('page', 'book', 'book_id', 'id', FALSE);
-
- $numOfFKS = R::getCell('
- SELECT COUNT(*)
- FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
- WHERE TABLE_NAME = "page" ');
-
- asrt( (int) $numOfFKS, 1 );
-
- //Now add non-dep key
- R::nuke();
-
- $sql = '
- CREATE TABLE book (
- id INT( 11 ) UNSIGNED NULL AUTO_INCREMENT,
- PRIMARY KEY ( id )
- )
- ENGINE = InnoDB
- ';
-
- R::exec( $sql );
-
- $sql = '
- CREATE TABLE page (
- id INT( 11 ) UNSIGNED AUTO_INCREMENT,
- book_id INT( 11 ) UNSIGNED NULL,
- PRIMARY KEY ( id )
- )
- ENGINE = InnoDB
- ';
-
- R::exec( $sql );
-
- $numOfFKS = R::getCell('
- SELECT COUNT(*)
- FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
- WHERE TABLE_NAME = "page" AND DELETE_RULE = "CASCADE"');
-
- asrt( (int) $numOfFKS, 0 );
-
- //even if different
- $writer->addFK('page', 'book', 'book_id', 'id', FALSE);
-
- $numOfFKS = R::getCell('
- SELECT COUNT(*)
- FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
- WHERE TABLE_NAME = "page" AND DELETE_RULE = "CASCADE"');
-
- asrt( (int) $numOfFKS, 0 );
-
- $numOfFKS = R::getCell('
- SELECT COUNT(*)
- FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
- WHERE TABLE_NAME = "page" AND DELETE_RULE = "SET NULL"');
-
- asrt( (int) $numOfFKS, 1 );
-
- $writer->addFK('page', 'book', 'book_id', 'id', TRUE);
-
- $numOfFKS = R::getCell('
- SELECT COUNT(*)
- FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
- WHERE TABLE_NAME = "page" ');
- }
-
- /**
- * Test whether we can manually create indexes.
- *
- * @return void
- */
- public function testAddingIndex()
- {
- R::nuke();
-
- $sql = '
- CREATE TABLE song (
- id INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
- album_id INT( 11 ) UNSIGNED NOT NULL,
- category VARCHAR( 255 ),
- PRIMARY KEY ( id )
- )
- ENGINE = InnoDB
- ';
-
- R::exec( $sql );
-
- $sql = 'SHOW INDEX FROM song';
-
- $indexes = R::getAll( $sql );
-
- asrt( count( $indexes ), 1 );
- asrt( $indexes[0]['Table'], 'song' );
- asrt( $indexes[0]['Key_name'], 'PRIMARY' );
-
- $writer = R::getWriter();
-
- $writer->addIndex('song', 'index1', 'album_id');
-
- $indexes = R::getAll( 'SHOW INDEX FROM song' );
-
- asrt( count( $indexes ), 2 );
- asrt( $indexes[0]['Table'], 'song' );
- asrt( $indexes[0]['Key_name'], 'PRIMARY' );
- asrt( $indexes[1]['Table'], 'song' );
- asrt( $indexes[1]['Key_name'], 'index1' );
-
- //Cant add the same index twice
- $writer->addIndex('song', 'index2', 'category');
-
- $indexes = R::getAll( 'SHOW INDEX FROM song' );
-
- asrt( count( $indexes ), 3 );
-
- //Dont fail, just dont
- try {
- $writer->addIndex('song', 'index3', 'nonexistant');
- pass();
- } catch( \Exception $e ) {
- fail();
- }
-
- asrt( count( $indexes ), 3 );
-
- try {
- $writer->addIndex('nonexistant', 'index4', 'nonexistant');
- pass();
- } catch( \Exception $e ) {
- fail();
- }
-
- asrt( count( $indexes ), 3 );
-
- try {
- $writer->addIndex('nonexistant', '', 'nonexistant');
- pass();
- } catch( \Exception $e ) {
- fail();
- }
-
- asrt( count( $indexes ), 3 );
- }
-}
projects / yaffs-website / blobdiff