tests/include/database/MssqlManagerTest.php

   1 <?php
   2 /*********************************************************************************
   3  * SugarCRM Community Edition is a customer relationship management program developed by
   4  * SugarCRM, Inc. Copyright (C) 2004-2013 SugarCRM Inc.
   5  *
   6  * This program is free software; you can redistribute it and/or modify it under
   7  * the terms of the GNU Affero General Public License version 3 as published by the
   8  * Free Software Foundation with the addition of the following permission added
   9  * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
  10  * IN WHICH THE COPYRIGHT IS OWNED BY SUGARCRM, SUGARCRM DISCLAIMS THE WARRANTY
  11  * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
  12  *
  13  * This program is distributed in the hope that it will be useful, but WITHOUT
  14  * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
  15  * FOR A PARTICULAR PURPOSE.  See the GNU Affero General Public License for more
  16  * details.
  17  *
  18  * You should have received a copy of the GNU Affero General Public License along with
  19  * this program; if not, see http://www.gnu.org/licenses or write to the Free
  20  * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
  21  * 02110-1301 USA.
  22  *
  23  * You can contact SugarCRM, Inc. headquarters at 10050 North Wolfe Road,
  24  * SW2-130, Cupertino, CA 95014, USA. or at email address contact@sugarcrm.com.
  25  *
  26  * The interactive user interfaces in modified source and object code versions
  27  * of this program must display Appropriate Legal Notices, as required under
  28  * Section 5 of the GNU Affero General Public License version 3.
  29  *
  30  * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
  31  * these Appropriate Legal Notices must retain the display of the "Powered by
  32  * SugarCRM" logo. If the display of the logo is not reasonably feasible for
  33  * technical reasons, the Appropriate Legal Notices must display the words
  34  * "Powered by SugarCRM".
  35  ********************************************************************************/
  36
  37 require_once 'include/database/MssqlManager.php';
  38
  39 class MssqlManagerTest extends Sugar_PHPUnit_Framework_TestCase
  40 {
  41     /**
  42      * @var MssqlManager
  43      */
  44     private $_db = null;
  45
  46     static public function setupBeforeClass()
  47     {
  48         $GLOBALS['current_user'] = SugarTestUserUtilities::createAnonymousUser();
  49         $GLOBALS['app_strings'] = return_application_language($GLOBALS['current_language']);
  50     }
  51
  52     static public function tearDownAfterClass()
  53     {
  54         SugarTestUserUtilities::removeAllCreatedAnonymousUsers();
  55         unset($GLOBALS['current_user']);
  56         unset($GLOBALS['app_strings']);
  57     }
  58
  59     public function setUp()
  60     {
  61         $this->_db = new MssqlManager();
  62     }
  63
  64     public function testQuote()
  65     {
  66         $string = "'dog eat ";
  67         $this->assertEquals($this->_db->quote($string),"''dog eat ");
  68     }
  69
  70     public function testArrayQuote()
  71     {
  72         $string = array("'dog eat ");
  73         $this->_db->arrayQuote($string);
  74         $this->assertEquals($string,array("''dog eat "));
  75     }
  76
  77     public function providerConvert()
  78     {
  79         $returnArray = array(
  80                 array(
  81                     array('foo','today'),
  82                     'GETDATE()'
  83                     ),
  84                 array(
  85                     array('foo','left'),
  86                     'LEFT(foo)'
  87                     ),
  88                 array(
  89                     array('foo','left',array('1','2','3')),
  90                     'LEFT(foo,1,2,3)'
  91                     ),
  92                 array(
  93                     array('foo','date_format'),
  94                     'LEFT(CONVERT(varchar(10),foo,120),10)'
  95                     ),
  96                 array(
  97                     array('foo','date_format',array('1','2','3')),
  98                     'LEFT(CONVERT(varchar(10),foo,120),10)'
  99                     ),
 100                 array(
 101                     array('foo','date_format',array("'%Y-%m'")),
 102                     'LEFT(CONVERT(varchar(7),foo,120),7)'
 103                     ),
 104                 array(
 105                     array('foo','IFNULL'),
 106                     'ISNULL(foo,\'\')'
 107                     ),
 108                 array(
 109                     array('foo','IFNULL',array('1','2','3')),
 110                     'ISNULL(foo,1,2,3)'
 111                     ),
 112                 array(
 113                     array('foo','CONCAT',array('1','2','3')),
 114                     'foo+1+2+3'
 115                     ),
 116                 array(
 117                     array(array('1','2','3'),'CONCAT'),
 118                     '1+2+3'
 119                     ),
 120                 array(
 121                     array(array('1','2','3'),'CONCAT',array('foo', 'bar')),
 122                     '1+2+3+foo+bar'
 123                     ),
 124                 array(
 125                     array('foo','text2char'),
 126                     'CAST(foo AS varchar(8000))'
 127                 ),
 128                 array(
 129                     array('foo','length'),
 130                     "LEN(foo)"
 131                 ),
 132                 array(
 133                     array('foo','month'),
 134                     "MONTH(foo)"
 135                 ),
 136                 array(
 137                     array('foo','quarter'),
 138                     "DATENAME(quarter, foo)"
 139                 ),
 140                 array(
 141                     array('foo','add_date',array(1,'day')),
 142                     "DATEADD(day,1,foo)"
 143                 ),
 144                 array(
 145                     array('foo','add_date',array(2,'week')),
 146                     "DATEADD(week,2,foo)"
 147                 ),
 148                 array(
 149                     array('foo','add_date',array(3,'month')),
 150                     "DATEADD(month,3,foo)"
 151                 ),
 152                 array(
 153                     array('foo','add_date',array(4,'quarter')),
 154                     "DATEADD(quarter,4,foo)"
 155                 ),
 156                 array(
 157                     array('foo','add_date',array(5,'year')),
 158                     "DATEADD(year,5,foo)"
 159                 ),
 160         );
 161         return $returnArray;
 162     }
 163
 164     /**
 165      * @ticket 33283
 166      * @dataProvider providerConvert
 167      */
 168     public function testConvert(array $parameters, $result)
 169     {
 170         $this->assertEquals($result, call_user_func_array(array($this->_db, "convert"), $parameters));
 171      }
 172
 173      /**
 174       * @ticket 33283
 175       */
 176      public function testConcat()
 177      {
 178          $ret = $this->_db->concat('foo',array('col1','col2','col3'));
 179          $this->assertEquals("LTRIM(RTRIM(ISNULL(foo.col1,'')+' '+ISNULL(foo.col2,'')+' '+ISNULL(foo.col3,'')))", $ret);
 180      }
 181
 182      public function providerFromConvert()
 183      {
 184          $returnArray = array(
 185              array(
 186                  array('foo','nothing'),
 187                  'foo'
 188                  ),
 189                  array(
 190                      array('2009-01-01 12:00:00','date'),
 191                      '2009-01-01'
 192                      ),
 193                  array(
 194                      array('2009-01-01 12:00:00','time'),
 195                      '12:00:00'
 196                      )
 197                  );
 198
 199          return $returnArray;
 200      }
 201
 202      /**
 203       * @ticket 33283
 204       * @dataProvider providerFromConvert
 205       */
 206      public function testFromConvert(
 207          array $parameters,
 208          $result
 209          )
 210      {
 211          $this->assertEquals(
 212              $this->_db->fromConvert($parameters[0],$parameters[1]),
 213              $result);
 214     }
 215
 216     /**
 217      * @group bug50024 - connect fails when not passed a db_name config option
 218      */
 219     public function testConnectWithNoDbName()
 220     {
 221         if ( ($GLOBALS['db']->dbType != 'mssql') || !function_exists('mssql_connect'))
 222             $this->markTestSkipped('Only applies to SQL Server legacy driver.');
 223
 224         // set up a connection w/o a db_name
 225         $configOptions = array(
 226             'db_host_name' => $GLOBALS['db']->connectOptions['db_host_name'],
 227             'db_host_instance' => $GLOBALS['db']->connectOptions['db_host_instance'],
 228             'db_user_name' => $GLOBALS['db']->connectOptions['db_user_name'],
 229             'db_password' => $GLOBALS['db']->connectOptions['db_password'],
 230         );
 231
 232         $this->assertTrue($this->_db->connect($configOptions));
 233     }
 234
 235     public function providerFullTextQuery()
 236     {
 237         return array(
 238             array(array('word1'), array(), array(),
 239                 "CONTAINS(unittest, '(\"word1\")')"),
 240             array(array("'word1'"), array(), array(),
 241                 "CONTAINS(unittest, '(\"''word1''\")')"),
 242             array(array("\"word1\""), array(), array(),
 243                 "CONTAINS(unittest, '(\"word1\")')"),
 244             array(array('word1', 'word2'), array(), array(),
 245                 "CONTAINS(unittest, '(\"word1\" | \"word2\")')"),
 246             array(array('word1', 'word2'), array('mustword'), array(),
 247                 "CONTAINS(unittest, '\"mustword\" AND (\"word1\" | \"word2\")')"),
 248             array(array('word1', 'word2'), array('mustword', 'mustword2'), array(),
 249                 "CONTAINS(unittest, '\"mustword\" AND \"mustword2\" AND (\"word1\" | \"word2\")')"),
 250             array(array(), array('mustword', 'mustword2'), array(),
 251                 "CONTAINS(unittest, '\"mustword\" AND \"mustword2\"')"),
 252             array(array('word1'), array(), array('notword'),
 253                 "CONTAINS(unittest, '(\"word1\") AND  NOT \"notword\"')"),
 254             array(array('word1'), array(), array('notword', 'notword2'),
 255                 "CONTAINS(unittest, '(\"word1\") AND  NOT \"notword\" AND  NOT \"notword2\"')"),
 256             array(array('word1', 'word2'), array('mustword', 'mustword2'), array('notword', 'notword2'),
 257                 "CONTAINS(unittest, '\"mustword\" AND \"mustword2\" AND (\"word1\" | \"word2\") AND  NOT \"notword\" AND  NOT \"notword2\"')"),
 258         );
 259     }
 260
 261     /**
 262      * @ticket 37435
 263      * @dataProvider providerFullTextQuery
 264      * @param array $terms
 265      * @param string $result
 266      */
 267     public function testFullTextQuery($terms, $must_terms, $exclude_terms, $result)
 268     {
 269         $this->assertEquals($result,
 270                         $this->_db->getFulltextQuery('unittest', $terms, $must_terms, $exclude_terms));
 271     }
 272
 273     /**
 274      * Test checks order by string in different queries
 275      *
 276      * @group 54990
 277      * @dataProvider getQueriesForReturnOrderBy
 278      */
 279     public function testReturnOrderBy($query, $start, $count, $expected)
 280     {
 281         $actual = $this->_db->limitQuery($query, $start, $count, false, '', false);
 282         $this->assertContains($expected, $actual, 'Order By is incorrect');
 283     }
 284
 285     /**
 286      * Data provider for testReturnOrderBy
 287      * Returns queries with different functions, offsets & aliases
 288      *
 289      * @return array
 290      */
 291     static public function getQueriesForReturnOrderBy()
 292     {
 293         return array(
 294             array(
 295                 "SELECT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY a1 ASC",
 296                 0,
 297                 1,
 298                 "(ORDER BY t1.f1 ASC)"
 299             ),
 300             array(
 301                 "SELECT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY a2 ASC",
 302                 0,
 303                 1,
 304                 "(ORDER BY t1.f2 ASC)"
 305             ),
 306             array(
 307                 "SELECT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY t1.f1 ASC",
 308                 0,
 309                 1,
 310                 "(ORDER BY t1.f1 ASC)"
 311             ),
 312             array(
 313                 "SELECT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY t1.f2 ASC",
 314                 0,
 315                 1,
 316                 "(ORDER BY t1.f2 ASC)"
 317             ),
 318
 319             array(
 320                 "SELECT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY a1 ASC",
 321                 1,
 322                 1,
 323                 "(ORDER BY t1.f1 ASC)"
 324             ),
 325             array(
 326                 "SELECT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY a2 ASC",
 327                 1,
 328                 1,
 329                 "(ORDER BY t1.f2 ASC)"
 330             ),
 331             array(
 332                 "SELECT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY t1.f1 ASC",
 333                 1,
 334                 1,
 335                 "(ORDER BY t1.f1 ASC)"
 336             ),
 337             array(
 338                 "SELECT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY t1.f2 ASC",
 339                 1,
 340                 1,
 341                 "(ORDER BY t1.f2 ASC)"
 342             ),
 343
 344             array(
 345                 "SELECT DISTINCT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY a1 ASC",
 346                 0,
 347                 1,
 348                 "ORDER BY a1 ASC"
 349             ),
 350             array(
 351                 "SELECT DISTINCT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY a2 ASC",
 352                 0,
 353                 1,
 354                 "ORDER BY a2 ASC"
 355             ),
 356             array(
 357                 "SELECT DISTINCT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY t1.f1 ASC",
 358                 0,
 359                 1,
 360                 "ORDER BY t1.f1 ASC"
 361             ),
 362             array(
 363                 "SELECT DISTINCT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY t1.f2 ASC",
 364                 0,
 365                 1,
 366                 "ORDER BY t1.f2 ASC"
 367             ),
 368
 369             array(
 370                 "SELECT DISTINCT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY a1 ASC",
 371                 1,
 372                 1,
 373                 "(ORDER BY t1.f1 ASC)"
 374             ),
 375             array(
 376                 "SELECT DISTINCT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY a2 ASC",
 377                 1,
 378                 1,
 379                 "(ORDER BY t1.f2 ASC)"
 380             ),
 381             array(
 382                 "SELECT DISTINCT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY t1.f1 ASC",
 383                 1,
 384                 1,
 385                 "(ORDER BY t1.f1 ASC)"
 386             ),
 387             array(
 388                 "SELECT DISTINCT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY t1.f2 ASC",
 389                 1,
 390                 1,
 391                 "(ORDER BY t1.f2 ASC)"
 392             ),
 393
 394             array(
 395                 "SELECT ISNULL( t1.f1, '' ) a1, ISNULL( t1.f2, '' ) a2 FROM table1 t1 WHERE 1 = 1 ORDER BY a1 ASC",
 396                 0,
 397                 1,
 398                 "(ORDER BY isnull( t1.f1, '' ) ASC)"
 399             ),
 400             array(
 401                 "SELECT ISNULL( t1.f1, '' ) a1, ISNULL( t1.f2, '' ) a2 FROM table1 t1 WHERE 1 = 1 ORDER BY a2 ASC",
 402                 0,
 403                 1,
 404                 "(ORDER BY isnull( t1.f2, '' ) ASC)"
 405             ),
 406             array(
 407                 "SELECT ISNULL( t1.f1, '' ) a1, ISNULL( t1.f2, '' ) a2 FROM table1 t1 WHERE 1 = 1 ORDER BY t1.f1 ASC",
 408                 0,
 409                 1,
 410                 "(ORDER BY t1.f1 ASC)"
 411             ),
 412             array(
 413                 "SELECT ISNULL( t1.f1, '' ) a1, ISNULL( t1.f2, '' ) a2 FROM table1 t1 WHERE 1 = 1 ORDER BY t1.f2 ASC",
 414                 0,
 415                 1,
 416                 "(ORDER BY t1.f2 ASC)"
 417             ),
 418
 419             array(
 420                 "SELECT ISNULL( t1.f1, '' ) a1, ISNULL(  t1.f2, '' ) a2 FROM table1 t1 WHERE 1 = 1 ORDER BY a1 ASC",
 421                 1,
 422                 1,
 423                 "(ORDER BY isnull( t1.f1, '' ) ASC)"
 424             ),
 425             array(
 426                 "SELECT ISNULL( t1.f1, '' ) a1, ISNULL( t1.f2, '' ) a2 FROM table1 t1 WHERE 1 = 1 ORDER BY a2 ASC",
 427                 1,
 428                 1,
 429                 "(ORDER BY isnull( t1.f2, '' ) ASC)"
 430             ),
 431             array(
 432                 "SELECT ISNULL( t1.f1, '' ) a1, ISNULL( t1.f2, '' ) a2 FROM table1 t1 WHERE 1 = 1 ORDER BY t1.f1 ASC",
 433                 1,
 434                 1,
 435                 "(ORDER BY t1.f1 ASC)"
 436             ),
 437             array(
 438                 "SELECT ISNULL( t1.f1, '' ) a1, ISNULL( t1.f2, '' ) a2 FROM table1 t1 WHERE 1 = 1 ORDER BY t1.f2 ASC",
 439                 1,
 440                 1,
 441                 "(ORDER BY t1.f2 ASC)"
 442             ),
 443
 444             array(
 445                 "SELECT
 446                     ISNULL(accounts.id,'') primaryid,
 447                     ISNULL(accounts.name,'') accounts_name,
 448                     ISNULL(l2.id,'') l2_id,
 449                     l2.email_address l2_email_address
 450                 FROM
 451                     accounts
 452                 INNER JOIN
 453                     accounts_contacts l1_1
 454                 ON
 455                     accounts.id=l1_1.account_id
 456                     AND l1_1.deleted=0
 457                 INNER JOIN
 458                     contacts l1
 459                 ON
 460                     l1.id=l1_1.contact_id
 461                     AND l1.deleted=0
 462                     AND l1.team_set_id IN (
 463                         SELECT
 464                             tst.team_set_id
 465                         from
 466                             team_sets_teams tst
 467                         INNER JOIN
 468                             team_memberships team_memberships
 469                         ON
 470                             tst.team_id = team_memberships.team_id
 471                             AND team_memberships.user_id = '5a409dc7-1cdb-278b-2222-511e6952dac8'
 472                             AND team_memberships.deleted=0
 473                     )
 474                 INNER JOIN
 475                     email_addr_bean_rel l2_1
 476                 ON
 477                     l1.id=l2_1.bean_id
 478                     AND l2_1.deleted=0
 479                     AND l2_1.primary_address = '1'
 480                 INNER JOIN
 481                     email_addresses l2
 482                 ON
 483                     l2.id=l2_1.email_address_id
 484                     AND l2.deleted=0
 485                 WHERE
 486                     ((1=1)
 487                     AND accounts.team_set_id IN (
 488                         SELECT
 489                             tst.team_set_id
 490                         FROM
 491                             team_sets_teams tst
 492                         INNER JOIN
 493                             team_memberships team_memberships
 494                         ON
 495                             tst.team_id = team_memberships.team_id
 496                             AND team_memberships.user_id = '5a409dc7-1cdb-278b-2222-511e6952dac8'
 497                             AND team_memberships.deleted=0
 498                     ))
 499                     AND accounts.deleted=0
 500                 ORDER BY
 501                     l2_email_address ASC
 502                 ",
 503                 1,
 504                 1,
 505                 "(ORDER BY l2.email_address ASC)"
 506             ),
 507
 508             array(
 509                 "SELECT
 510                     ISNULL(accounts.id,'') primaryid,
 511                     ISNULL(accounts.name,'') accounts_name,
 512                     ISNULL(l2.id,'') l2_id,
 513                     l2.email_address l2_email_address
 514                 FROM
 515                     accounts
 516                 INNER JOIN
 517                     accounts_contacts l1_1
 518                 ON
 519                     accounts.id=l1_1.account_id
 520                     AND l1_1.deleted=0
 521                 INNER JOIN
 522                     contacts l1
 523                 ON
 524                     l1.id=l1_1.contact_id
 525                     AND l1.deleted=0
 526                     AND l1.team_set_id IN (
 527                         SELECT
 528                             tst.team_set_id
 529                         from
 530                             team_sets_teams tst
 531                         INNER JOIN
 532                             team_memberships team_memberships
 533                         ON
 534                             tst.team_id = team_memberships.team_id
 535                             AND team_memberships.user_id = 'c71f4b54-2058-5d8b-1d17-511e6b730b27'
 536                             AND team_memberships.deleted=0
 537                     )
 538                 INNER JOIN
 539                     email_addr_bean_rel l2_1
 540                 ON
 541                     l1.id=l2_1.bean_id
 542                     AND l2_1.deleted=0
 543                     AND l2_1.primary_address = '1'
 544                 INNER JOIN
 545                     email_addresses l2
 546                 ON
 547                     l2.id=l2_1.email_address_id
 548                     AND l2.deleted=0
 549                 WHERE
 550                     ((1=1)
 551                     AND accounts.team_set_id IN (
 552                         SELECT
 553                             tst.team_set_id
 554                         FROM
 555                             team_sets_teams tst
 556                         INNER JOIN
 557                             team_memberships team_memberships
 558                         ON
 559                             tst.team_id = team_memberships.team_id
 560                             AND team_memberships.user_id = 'c71f4b54-2058-5d8b-1d17-511e6b730b27'
 561                             AND team_memberships.deleted=0
 562                     ))
 563                     AND  accounts.deleted=0
 564                 ORDER BY
 565                     accounts_name ASC
 566                 ",
 567                 1,
 568                 1,
 569                 "(ORDER BY isnull(accounts.name,'') ASC)"
 570             ),
 571
 572         );
 573     }
 574 }