3 namespace RedUNIT\Sqlite;
5 use RedUNIT\Sqlite as Sqlite;
6 use RedBeanPHP\Facade as R;
11 * Tests creation and validity of foreign keys,
12 * foreign key constraints and indexes in SQLite.
13 * Also tests whether the correct contraint action has been selected.
15 * @file RedUNIT/Sqlite/Foreignkeys.php
16 * @desc Tests the creation of foreign keys.
17 * @author Gabor de Mooij and the RedBeanPHP Community
18 * @license New BSD/GPLv2
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.
24 class Foreignkeys extends Sqlite
27 * addIndex should not trigger exception...
31 public function testIndexException()
34 $book = R::dispense( 'book' );
38 R::getWriter()->addIndex( 'book' , '\'', 'title' );
40 } catch( \Exception $e ) {
43 R::getWriter()->addIndex( 'book' , '\'', 'title' );
48 * Test foreign keys with SQLite.
52 public function testForeignKeysWithSQLite()
54 $book = R::dispense( 'book' );
55 $page = R::dispense( 'page' );
56 $cover = R::dispense( 'cover' );
57 list( $g1, $g2 ) = R::dispense( 'genre', 2 );
60 $book->ownPage = array( $page );
61 $book->cover = $cover;
62 $book->sharedGenre = array( $g1, $g2 );
64 $fkbook = R::getAll( 'pragma foreign_key_list(book)' );
65 $fkgenre = R::getAll( 'pragma foreign_key_list(book_genre)' );
66 $fkpage = R::getAll( 'pragma foreign_key_list(page)' );
67 asrt( $fkpage[0]['from'], 'book_id' );
68 asrt( $fkpage[0]['to'], 'id' );
69 asrt( $fkpage[0]['table'], 'book' );
70 asrt( count( $fkgenre ), 2 );
71 if ( $fkgenre[0]['from'] == 'book' ) {
72 asrt( $fkgenre[0]['to'], 'id' );
73 asrt( $fkgenre[0]['table'], 'book' );
75 if ( $fkgenre[0]['from'] == 'genre' ) {
76 asrt( $fkgenre[0]['to'], 'id' );
77 asrt( $fkgenre[0]['table'], 'genre' );
79 asrt( $fkbook[0]['from'], 'cover_id' );
80 asrt( $fkbook[0]['to'], 'id' );
81 asrt( $fkbook[0]['table'], 'cover' );
85 * Constrain test for SQLite Writer.
89 public function testConstrain()
92 $sql = 'CREATE TABLE book ( id INTEGER PRIMARY KEY AUTOINCREMENT ) ';
94 $sql = 'CREATE TABLE page ( id INTEGER PRIMARY KEY AUTOINCREMENT ) ';
96 $sql = 'CREATE TABLE book_page (
97 id INTEGER PRIMARY KEY AUTOINCREMENT,
102 $sql = 'PRAGMA foreign_key_list("book_page")';
103 $fkList = R::getAll( $sql );
104 asrt( count( $fkList), 0 );
105 $writer = R::getWriter();
106 $writer->addFK( 'book_page', 'book', 'book_id', 'id', TRUE );
107 $writer->addFK( 'book_page', 'page', 'page_id', 'id', TRUE );
108 $sql = 'PRAGMA foreign_key_list("book_page")';
109 $fkList = R::getAll( $sql );
110 asrt( count( $fkList), 2 );
111 $writer->addFK( 'book_page', 'book', 'book_id', 'id', TRUE );
112 $writer->addFK( 'book_page', 'page', 'page_id', 'id', TRUE );
113 $sql = 'PRAGMA foreign_key_list("book_page")';
114 $fkList = R::getAll( $sql );
115 asrt( count( $fkList), 2 );
119 * Test adding foreign keys.
123 public function testAddingForeignKeys()
127 $sql = 'CREATE TABLE book ( id INTEGER PRIMARY KEY AUTOINCREMENT ) ';
129 $sql = 'CREATE TABLE page ( id INTEGER PRIMARY KEY AUTOINCREMENT, book_id INTEGER ) ';
131 asrt( count( R::getAll(' PRAGMA foreign_key_list("page") ') ), 0 );
132 $writer = R::getWriter();
133 $writer->addFK('page', 'book', 'book_id', 'id', TRUE);
134 asrt( count( R::getAll(' PRAGMA foreign_key_list("page") ') ), 1 );
135 $writer->addFK('page', 'book', 'book_id', 'id', TRUE);
136 asrt( count( R::getAll(' PRAGMA foreign_key_list("page") ') ), 1 );
137 $writer->addFK('page', 'book', 'book_id', 'id', FALSE);
138 asrt( count( R::getAll(' PRAGMA foreign_key_list("page") ') ), 1 );
140 $sql = 'CREATE TABLE book ( id INTEGER PRIMARY KEY AUTOINCREMENT ) ';
142 $sql = 'CREATE TABLE page ( id INTEGER PRIMARY KEY AUTOINCREMENT, book_id INTEGER ) ';
144 asrt( count( R::getAll(' PRAGMA foreign_key_list("page") ') ), 0 );
145 $writer = R::getWriter();
146 $writer->addFK('page', 'book', 'book_id', 'id', FALSE);
147 asrt( count( R::getAll(' PRAGMA foreign_key_list("page") ') ), 1 );
148 $writer->addFK('page', 'book', 'book_id', 'id', TRUE);
149 asrt( count( R::getAll(' PRAGMA foreign_key_list("page") ') ), 1 );
150 $writer->addFK('page', 'book', 'book_id', 'id', FALSE);
151 asrt( count( R::getAll(' PRAGMA foreign_key_list("page") ') ), 1 );
155 * Test whether we can manually create indexes.
159 public function testAddingIndex()
162 $sql = 'CREATE TABLE song (
163 id INTEGER PRIMARY KEY AUTOINCREMENT,
168 $writer = R::getWriter();
169 $indexes = R::getAll('PRAGMA index_list("song") ');
170 asrt( count( $indexes ), 0 );
171 $writer->addIndex( 'song', 'index1', 'album_id' );
172 $indexes = R::getAll('PRAGMA index_list("song") ');
173 asrt( count( $indexes ), 1 );
174 $writer->addIndex( 'song', 'index1', 'album_id' );
175 $indexes = R::getAll('PRAGMA index_list("song") ');
176 asrt( count( $indexes ), 1 );
177 $writer->addIndex( 'song', 'index2', 'category' );
178 $indexes = R::getAll('PRAGMA index_list("song") ');
179 asrt( count( $indexes ), 2 );
181 $writer->addIndex( 'song', 'index1', 'nonexistant' );
183 } catch ( \Exception $ex ) {
186 $indexes = R::getAll('PRAGMA index_list("song") ');
187 asrt( count( $indexes ), 2 );
189 $writer->addIndex( 'nonexistant', 'index1', 'nonexistant' );
191 } catch ( \Exception $ex ) {
194 $indexes = R::getAll('PRAGMA index_list("song") ');
195 asrt( count( $indexes ), 2 );