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