tests/phpunit/CRM/Utils/QueryFormatterTest.php

   1 <?php
   2
   3 /**
   4  * Class CRM_Utils_QueryFormatterTest
   5  * @group headless
   6  */
   7 class CRM_Utils_QueryFormatterTest extends CiviUnitTestCase {
   8
   9   public function createExampleTable() {
  10     CRM_Core_DAO::executeQuery('
  11       DROP TABLE IF EXISTS civicrm_fts_example
  12     ');
  13     CRM_Core_DAO::executeQuery('
  14       CREATE TABLE civicrm_fts_example (
  15         id int(10) unsigned NOT NULL AUTO_INCREMENT,
  16         name varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  17         PRIMARY KEY (id)
  18       )
  19     ');
  20     $idx = new CRM_Core_InnoDBIndexer(self::supportsFts(), array(
  21       'civicrm_contact' => array(
  22         array('first_name', 'last_name'),
  23       ),
  24     ));
  25     $idx->fixSchemaDifferences();
  26     $rows = array(
  27       array(1, 'someone@example.com'),
  28       array(2, 'this is someone@example.com!'),
  29       array(3, 'first second'),
  30       array(4, 'zeroth first second'),
  31       array(5, 'zeroth first second third'),
  32       array(6, 'never say never'),
  33       array(7, 'first someone@example.com second'),
  34       array(8, 'first someone'),
  35       array(9, 'firstly someone'),
  36     );
  37     foreach ($rows as $row) {
  38       CRM_Core_DAO::executeQuery("INSERT INTO civicrm_fts_example (id,name) VALUES (%1, %2)",
  39         array(
  40           1 => array($row[0], 'Int'),
  41           2 => array($row[1], 'String'),
  42         ));
  43     }
  44   }
  45
  46   public static function tearDownAfterClass() {
  47     CRM_Core_DAO::executeQuery('DROP TABLE IF EXISTS civicrm_fts_example');
  48     parent::tearDownAfterClass();
  49   }
  50
  51   /**
  52    * Generate data for tests to iterate through.
  53    *
  54    * Note: These examples are not locked in stone -- but do exercise
  55    * discretion in revising them!
  56    *
  57    * @return array
  58    */
  59   public function dataProvider() {
  60     // Array(0=>$inputText, 1=>$language, 2=>$options, 3=>$expectedText, 4=>$matchingIds).
  61     $cases = array();
  62
  63     $allEmailRows = array(1, 2, 7);
  64
  65     $cases[] = array('someone@example.com', 'like', 'simple', '%someone@example.com%', $allEmailRows);
  66     $cases[] = array('someone@example.com', 'like', 'phrase', '%someone@example.com%', $allEmailRows);
  67     $cases[] = array('someone@example.com', 'like', 'wildphrase', '%someone@example.com%', $allEmailRows);
  68     $cases[] = array('someone@example.com', 'like', 'wildwords', '%someone@example.com%', $allEmailRows);
  69     $cases[] = array('someone@example.com', 'like', 'wildwords-suffix', '%someone@example.com%', $allEmailRows);
  70
  71     $cases[] = array('someone@example.com', 'fts', 'simple', 'someone@example.com', $allEmailRows);
  72     $cases[] = array('someone@example.com', 'fts', 'phrase', '"someone@example.com"', $allEmailRows);
  73     $cases[] = array('someone@example.com', 'fts', 'wildphrase', '"*someone@example.com*"', $allEmailRows);
  74     $cases[] = array('someone@example.com', 'fts', 'wildwords', '*someone* *example*', $allEmailRows);
  75     $cases[] = array('someone@example.com', 'fts', 'wildwords-suffix', 'someone* example*', $allEmailRows);
  76
  77     $cases[] = array('someone@example.com', 'ftsbool', 'simple', '+"someone" +"example"', $allEmailRows);
  78     $cases[] = array('someone@example.com', 'ftsbool', 'phrase', '+"someone@example.com"', $allEmailRows);
  79     $cases[] = array('someone@example.com', 'ftsbool', 'wildphrase', '+"*someone@example.com*"', $allEmailRows);
  80     $cases[] = array('someone@example.com', 'ftsbool', 'wildwords', '+*someone* +*example*', $allEmailRows);
  81     $cases[] = array('someone@example.com', 'ftsbool', 'wildwords-suffix', '+someone* +example*', $allEmailRows);
  82
  83     $cases[] = array('first second', 'like', 'simple', '%first second%', array(3, 4, 5));
  84     $cases[] = array('first second', 'like', 'phrase', '%first second%', array(3, 4, 5));
  85     $cases[] = array('first second', 'like', 'wildphrase', '%first second%', array(3, 4, 5));
  86     $cases[] = array('first second', 'like', 'wildwords', '%first%second%', array(3, 4, 5, 7));
  87     $cases[] = array('first second', 'like', 'wildwords-suffix', '%first%second%', array(3, 4, 5, 7));
  88
  89     $cases[] = array('first second', 'fts', 'simple', 'first second', array(3, 4, 5));
  90     $cases[] = array('first second', 'fts', 'phrase', '"first second"', array(3, 4, 5));
  91     $cases[] = array('first second', 'fts', 'wildphrase', '"*first second*"', array(3, 4, 5));
  92     $cases[] = array('first second', 'fts', 'wildwords', '*first* *second*', array(3, 4, 5, 7));
  93     $cases[] = array('first second', 'fts', 'wildwords-suffix', 'first* second*', array(3, 4, 5, 7));
  94
  95     $cases[] = array('first second', 'ftsbool', 'simple', '+"first" +"second"', array(3, 4, 5));
  96     $cases[] = array('first second', 'ftsbool', 'phrase', '+"first second"', array(3, 4, 5));
  97     $cases[] = array('first second', 'ftsbool', 'wildphrase', '+"*first second*"', array(3, 4, 5));
  98     $cases[] = array('first second', 'ftsbool', 'wildwords', '+*first* +*second*', array(3, 4, 5, 7));
  99     $cases[] = array('first second', 'ftsbool', 'wildwords-suffix', '+first* +second*', array(3, 4, 5, 7));
 100
 101     $cases[] = array('first second', 'solr', 'simple', 'first second', NULL);
 102     $cases[] = array('first second', 'solr', 'phrase', '"first second"', NULL);
 103     $cases[] = array('first second', 'solr', 'wildphrase', '"*first second*"', NULL);
 104     $cases[] = array('first second', 'solr', 'wildwords', '*first* *second*', NULL);
 105     $cases[] = array('first second', 'solr', 'wildwords-suffix', 'first* second*', NULL);
 106
 107     $cases[] = array('someone@', 'ftsbool', 'simple', '+"someone"', $allEmailRows);
 108     $cases[] = array('@example.com', 'ftsbool', 'simple', '+"example.com"', $allEmailRows);
 109
 110     // If user supplies wildcards, then ignore mode.
 111     foreach (array(
 112                'simple',
 113                'wildphrase',
 114                'wildwords',
 115                'wildwords-suffix',
 116              ) as $mode) {
 117       $cases[] = array('first% second', 'like', $mode, 'first% second', array(3, 7));
 118       $cases[] = array('first% second', 'fts', $mode, 'first* second', array(3, 7));
 119       $cases[] = array('first% second', 'ftsbool', $mode, '+first* +second', array(3, 7));
 120       $cases[] = array('first% second', 'solr', $mode, 'first* second', NULL);
 121       $cases[] = array('first second%', 'like', $mode, 'first second%', array(3));
 122       $cases[] = array('first second%', 'fts', $mode, 'first second*', array(3));
 123       $cases[] = array('first second%', 'ftsbool', $mode, '+first +second*', array(3));
 124       $cases[] = array('first second%', 'solr', $mode, 'first second*', NULL);
 125     }
 126
 127     return $cases;
 128   }
 129
 130   /**
 131    * Test format.
 132    *
 133    * @param string $text
 134    * @param string $language
 135    * @param string $mode
 136    * @param string $expectedText
 137    *
 138    * @dataProvider dataProvider
 139    */
 140   public function testFormat($text, $language, $mode, $expectedText, $expectedRowIds) {
 141     $formatter = new CRM_Utils_QueryFormatter($mode);
 142     $actualText = $formatter->format($text, $language);
 143     $this->assertEquals($expectedText, $actualText);
 144
 145     if ($expectedRowIds !== NULL) {
 146       if ($language === 'like') {
 147         $this->createExampleTable();
 148         $this->assertSqlIds($expectedRowIds, "SELECT id FROM civicrm_fts_example WHERE " . $formatter->formatSql('civicrm_fts_example', 'name', $text));
 149       }
 150       elseif (in_array($language, array('fts', 'ftsbool'))) {
 151         if ($this->supportsFts()) {
 152           $this->createExampleTable();
 153           $this->assertSqlIds($expectedRowIds, "SELECT id FROM civicrm_fts_example WHERE " . $formatter->formatSql('civicrm_fts_example', 'name', $text));
 154         }
 155       }
 156       elseif ($language === 'solr') {
 157         // Skip. Don't have solr test harness.
 158       }
 159       else {
 160         $this->fail("Cannot asset expectedRowIds with unrecognized language $language");
 161       }
 162     }
 163   }
 164
 165   public static function supportsFts() {
 166     return version_compare(CRM_Core_DAO::singleValueQuery('SELECT VERSION()'), '5.6.0', '>=');
 167   }
 168
 169   /**
 170    * @param array $expectedRowIds
 171    * @param string $sql
 172    */
 173   private function assertSqlIds($expectedRowIds, $sql) {
 174     $actualRowIds = CRM_Utils_Array::collect('id',
 175       CRM_Core_DAO::executeQuery($sql)->fetchAll());
 176     sort($actualRowIds);
 177     sort($expectedRowIds);
 178     $this->assertEquals($expectedRowIds, $actualRowIds);
 179   }
 180
 181 }