tests/phpunit/includes/db/DatabaseSQLTest.php

   1 <?php
   2
   3 /**
   4  * Test the abstract database layer
   5  * Using Mysql for the sql at the moment TODO
   6  *
   7  * @group Database
   8  */
   9 class DatabaseSQLTest extends MediaWikiTestCase {
  10
  11         protected function setUp() {
  12                 // TODO support other DBMS or find another way to do it
  13                 if ( $this->db->getType() !== 'mysql' ) {
  14                         $this->markTestSkipped( 'No mysql database' );
  15                 }
  16         }
  17
  18         /**
  19          * @dataProvider provideSelectSQLText
  20          */
  21         function testSelectSQLText( $sql, $sqlText ) {
  22                 $this->assertEquals( trim( $this->db->selectSQLText(
  23                         isset( $sql['tables'] ) ? $sql['tables'] : array(),
  24                         isset( $sql['fields'] ) ? $sql['fields'] : array(),
  25                         isset( $sql['conds'] ) ? $sql['conds'] : array(),
  26                         __METHOD__,
  27                         isset( $sql['options'] ) ? $sql['options'] : array(),
  28                         isset( $sql['join_conds'] ) ? $sql['join_conds'] : array()
  29                 ) ), $sqlText );
  30         }
  31
  32         public static function provideSelectSQLText() {
  33                 return array(
  34                         array(
  35                                 array(
  36                                         'tables' => 'table',
  37                                         'fields' => array( 'field', 'alias' => 'field2' ),
  38                                         'conds' => array( 'alias' => 'text' ),
  39                                 ),
  40                                 "SELECT  field,field2 AS alias  " .
  41                                 "FROM `unittest_table`  " .
  42                                 "WHERE alias = 'text'"
  43                         ),
  44                         array(
  45                                 array(
  46                                         'tables' => 'table',
  47                                         'fields' => array( 'field', 'alias' => 'field2' ),
  48                                         'conds' => array( 'alias' => 'text' ),
  49                                         'options' => array( 'LIMIT' => 1, 'ORDER BY' => 'field' ),
  50                                 ),
  51                                 "SELECT  field,field2 AS alias  " .
  52                                 "FROM `unittest_table`  " .
  53                                 "WHERE alias = 'text'  " .
  54                                 "ORDER BY field " .
  55                                 "LIMIT 1"
  56                         ),
  57                         array(
  58                                 array(
  59                                         'tables' => array( 'table', 't2' => 'table2' ),
  60                                         'fields' => array( 'tid', 'field', 'alias' => 'field2', 't2.id' ),
  61                                         'conds' => array( 'alias' => 'text' ),
  62                                         'options' => array( 'LIMIT' => 1, 'ORDER BY' => 'field' ),
  63                                         'join_conds' => array( 't2' => array(
  64                                                 'LEFT JOIN', 'tid = t2.id'
  65                                         )),
  66                                 ),
  67                                 "SELECT  tid,field,field2 AS alias,t2.id  " .
  68                                 "FROM `unittest_table` LEFT JOIN `unittest_table2` `t2` ON ((tid = t2.id))  " .
  69                                 "WHERE alias = 'text'  " .
  70                                 "ORDER BY field " .
  71                                 "LIMIT 1"
  72                         ),
  73                         array(
  74                                 array(
  75                                         'tables' => array( 'table', 't2' => 'table2' ),
  76                                         'fields' => array( 'tid', 'field', 'alias' => 'field2', 't2.id' ),
  77                                         'conds' => array( 'alias' => 'text' ),
  78                                         'options' => array( 'LIMIT' => 1, 'GROUP BY' => 'field', 'HAVING' => 'COUNT(*) > 1' ),
  79                                         'join_conds' => array( 't2' => array(
  80                                                 'LEFT JOIN', 'tid = t2.id'
  81                                         )),
  82                                 ),
  83                                 "SELECT  tid,field,field2 AS alias,t2.id  " .
  84                                 "FROM `unittest_table` LEFT JOIN `unittest_table2` `t2` ON ((tid = t2.id))  " .
  85                                 "WHERE alias = 'text'  " .
  86                                 "GROUP BY field HAVING COUNT(*) > 1 " .
  87                                 "LIMIT 1"
  88                         ),
  89                         array(
  90                                 array(
  91                                         'tables' => array( 'table', 't2' => 'table2' ),
  92                                         'fields' => array( 'tid', 'field', 'alias' => 'field2', 't2.id' ),
  93                                         'conds' => array( 'alias' => 'text' ),
  94                                         'options' => array( 'LIMIT' => 1, 'GROUP BY' => array( 'field', 'field2' ), 'HAVING' => array( 'COUNT(*) > 1', 'field' => 1 ) ),
  95                                         'join_conds' => array( 't2' => array(
  96                                                 'LEFT JOIN', 'tid = t2.id'
  97                                         )),
  98                                 ),
  99                                 "SELECT  tid,field,field2 AS alias,t2.id  " .
 100                                 "FROM `unittest_table` LEFT JOIN `unittest_table2` `t2` ON ((tid = t2.id))  " .
 101                                 "WHERE alias = 'text'  " .
 102                                 "GROUP BY field,field2 HAVING (COUNT(*) > 1) AND field = '1' " .
 103                                 "LIMIT 1"
 104                         ),
 105                 );
 106         }
 107
 108         /**
 109          * @dataProvider provideConditional
 110          */
 111         function testConditional( $sql, $sqlText ) {
 112                 $this->assertEquals( trim( $this->db->conditional(
 113                         $sql['conds'],
 114                         $sql['true'],
 115                         $sql['false']
 116                 ) ), $sqlText );
 117         }
 118
 119         public static function provideConditional() {
 120                 return array(
 121                         array(
 122                                 array(
 123                                         'conds' => array( 'field' => 'text' ),
 124                                         'true' => 1,
 125                                         'false' => 'NULL',
 126                                 ),
 127                                 "(CASE WHEN field = 'text' THEN 1 ELSE NULL END)"
 128                         ),
 129                         array(
 130                                 array(
 131                                         'conds' => array( 'field' => 'text', 'field2' => 'anothertext' ),
 132                                         'true' => 1,
 133                                         'false' => 'NULL',
 134                                 ),
 135                                 "(CASE WHEN field = 'text' AND field2 = 'anothertext' THEN 1 ELSE NULL END)"
 136                         ),
 137                         array(
 138                                 array(
 139                                         'conds' => 'field=1',
 140                                         'true' => 1,
 141                                         'false' => 'NULL',
 142                                 ),
 143                                 "(CASE WHEN field=1 THEN 1 ELSE NULL END)"
 144                         ),
 145                 );
 146         }
 147 }