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     // If user supplies wildcards, then ignore mode.
 108     foreach (array(
 109                'simple',
 110                'wildphrase',
 111                'wildwords',
 112                'wildwords-suffix',
 113              ) as $mode) {
 114       $cases[] = array('first% second', 'like', $mode, 'first% second', array(3, 7));
 115       $cases[] = array('first% second', 'fts', $mode, 'first* second', array(3, 7));
 116       $cases[] = array('first% second', 'ftsbool', $mode, '+first* +second', array(3, 7));
 117       $cases[] = array('first% second', 'solr', $mode, 'first* second', NULL);
 118       $cases[] = array('first second%', 'like', $mode, 'first second%', array(3));
 119       $cases[] = array('first second%', 'fts', $mode, 'first second*', array(3));
 120       $cases[] = array('first second%', 'ftsbool', $mode, '+first +second*', array(3));
 121       $cases[] = array('first second%', 'solr', $mode, 'first second*', NULL);
 122     }
 123
 124     return $cases;
 125   }
 126
 127   /**
 128    * Test format.
 129    *
 130    * @param string $text
 131    * @param string $language
 132    * @param string $mode
 133    * @param string $expectedText
 134    *
 135    * @dataProvider dataProvider
 136    */
 137   public function testFormat($text, $language, $mode, $expectedText, $expectedRowIds) {
 138     $formatter = new CRM_Utils_QueryFormatter($mode);
 139     $actualText = $formatter->format($text, $language);
 140     $this->assertEquals($expectedText, $actualText);
 141
 142     if ($expectedRowIds !== NULL) {
 143       if ($language === 'like') {
 144         $this->createExampleTable();
 145         $this->assertSqlIds($expectedRowIds, "SELECT id FROM civicrm_fts_example WHERE " . $formatter->formatSql('civicrm_fts_example', 'name', $text));
 146       }
 147       elseif (in_array($language, array('fts', 'ftsbool'))) {
 148         if ($this->supportsFts()) {
 149           $this->createExampleTable();
 150           $this->assertSqlIds($expectedRowIds, "SELECT id FROM civicrm_fts_example WHERE " . $formatter->formatSql('civicrm_fts_example', 'name', $text));
 151         }
 152       }
 153       elseif ($language === 'solr') {
 154         // Skip. Don't have solr test harness.
 155       }
 156       else {
 157         $this->fail("Cannot asset expectedRowIds with unrecognized language $language");
 158       }
 159     }
 160   }
 161
 162   public static function supportsFts() {
 163     return version_compare(CRM_Core_DAO::singleValueQuery('SELECT VERSION()'), '5.6.0', '>=');
 164   }
 165
 166   /**
 167    * @param array $expectedRowIds
 168    * @param string $sql
 169    */
 170   private function assertSqlIds($expectedRowIds, $sql) {
 171     $actualRowIds = CRM_Utils_Array::collect('id',
 172       CRM_Core_DAO::executeQuery($sql)->fetchAll());
 173     sort($actualRowIds);
 174     sort($expectedRowIds);
 175     $this->assertEquals($expectedRowIds, $actualRowIds);
 176   }
 177
 178 }