2 /*********************************************************************************
3 * SugarCRM Community Edition is a customer relationship management program developed by
4 * SugarCRM, Inc. Copyright (C) 2004-2013 SugarCRM Inc.
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.
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
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
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.
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.
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 ********************************************************************************/
37 require_once 'include/database/MssqlManager.php';
39 class MssqlManagerTest extends Sugar_PHPUnit_Framework_TestCase
46 static public function setupBeforeClass()
48 $GLOBALS['current_user'] = SugarTestUserUtilities::createAnonymousUser();
49 $GLOBALS['app_strings'] = return_application_language($GLOBALS['current_language']);
52 static public function tearDownAfterClass()
54 SugarTestUserUtilities::removeAllCreatedAnonymousUsers();
55 unset($GLOBALS['current_user']);
56 unset($GLOBALS['app_strings']);
59 public function setUp()
61 $this->_db = new MssqlManager();
64 public function testQuote()
66 $string = "'dog eat ";
67 $this->assertEquals($this->_db->quote($string),"''dog eat ");
70 public function testArrayQuote()
72 $string = array("'dog eat ");
73 $this->_db->arrayQuote($string);
74 $this->assertEquals($string,array("''dog eat "));
77 public function providerConvert()
89 array('foo','left',array('1','2','3')),
93 array('foo','date_format'),
94 'LEFT(CONVERT(varchar(10),foo,120),10)'
97 array('foo','date_format',array('1','2','3')),
98 'LEFT(CONVERT(varchar(10),foo,120),10)'
101 array('foo','date_format',array("'%Y-%m'")),
102 'LEFT(CONVERT(varchar(7),foo,120),7)'
105 array('foo','IFNULL'),
109 array('foo','IFNULL',array('1','2','3')),
113 array('foo','CONCAT',array('1','2','3')),
117 array(array('1','2','3'),'CONCAT'),
121 array(array('1','2','3'),'CONCAT',array('foo', 'bar')),
125 array('foo','text2char'),
126 'CAST(foo AS varchar(8000))'
129 array('foo','length'),
133 array('foo','month'),
137 array('foo','quarter'),
138 "DATENAME(quarter, foo)"
141 array('foo','add_date',array(1,'day')),
145 array('foo','add_date',array(2,'week')),
146 "DATEADD(week,2,foo)"
149 array('foo','add_date',array(3,'month')),
150 "DATEADD(month,3,foo)"
153 array('foo','add_date',array(4,'quarter')),
154 "DATEADD(quarter,4,foo)"
157 array('foo','add_date',array(5,'year')),
158 "DATEADD(year,5,foo)"
166 * @dataProvider providerConvert
168 public function testConvert(array $parameters, $result)
170 $this->assertEquals($result, call_user_func_array(array($this->_db, "convert"), $parameters));
176 public function testConcat()
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);
182 public function providerFromConvert()
184 $returnArray = array(
186 array('foo','nothing'),
190 array('2009-01-01 12:00:00','date'),
194 array('2009-01-01 12:00:00','time'),
204 * @dataProvider providerFromConvert
206 public function testFromConvert(
212 $this->_db->fromConvert($parameters[0],$parameters[1]),
217 * @group bug50024 - connect fails when not passed a db_name config option
219 public function testConnectWithNoDbName()
221 if ( ($GLOBALS['db']->dbType != 'mssql') || !function_exists('mssql_connect'))
222 $this->markTestSkipped('Only applies to SQL Server legacy driver.');
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'],
232 $this->assertTrue($this->_db->connect($configOptions));
235 public function providerFullTextQuery()
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\"')"),
263 * @dataProvider providerFullTextQuery
264 * @param array $terms
265 * @param string $result
267 public function testFullTextQuery($terms, $must_terms, $exclude_terms, $result)
269 $this->assertEquals($result,
270 $this->_db->getFulltextQuery('unittest', $terms, $must_terms, $exclude_terms));
274 * Test checks order by string in different queries
277 * @dataProvider getQueriesForReturnOrderBy
279 public function testReturnOrderBy($query, $start, $count, $expected)
281 $actual = $this->_db->limitQuery($query, $start, $count, false, '', false);
282 $this->assertContains($expected, $actual, 'Order By is incorrect');
286 * Data provider for testReturnOrderBy
287 * Returns queries with different functions, offsets & aliases
291 static public function getQueriesForReturnOrderBy()
295 "SELECT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY a1 ASC",
298 "(ORDER BY t1.f1 ASC)"
301 "SELECT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY a2 ASC",
304 "(ORDER BY t1.f2 ASC)"
307 "SELECT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY t1.f1 ASC",
310 "(ORDER BY t1.f1 ASC)"
313 "SELECT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY t1.f2 ASC",
316 "(ORDER BY t1.f2 ASC)"
320 "SELECT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY a1 ASC",
323 "(ORDER BY t1.f1 ASC)"
326 "SELECT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY a2 ASC",
329 "(ORDER BY t1.f2 ASC)"
332 "SELECT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY t1.f1 ASC",
335 "(ORDER BY t1.f1 ASC)"
338 "SELECT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY t1.f2 ASC",
341 "(ORDER BY t1.f2 ASC)"
345 "SELECT DISTINCT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY a1 ASC",
351 "SELECT DISTINCT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY a2 ASC",
357 "SELECT DISTINCT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY t1.f1 ASC",
363 "SELECT DISTINCT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY t1.f2 ASC",
370 "SELECT DISTINCT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY a1 ASC",
373 "(ORDER BY t1.f1 ASC)"
376 "SELECT DISTINCT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY a2 ASC",
379 "(ORDER BY t1.f2 ASC)"
382 "SELECT DISTINCT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY t1.f1 ASC",
385 "(ORDER BY t1.f1 ASC)"
388 "SELECT DISTINCT t1.f1 a1, t1.f2 a2 FROM table1 t1 WHERE 1 = 1 ORDER BY t1.f2 ASC",
391 "(ORDER BY t1.f2 ASC)"
395 "SELECT ISNULL( t1.f1, '' ) a1, ISNULL( t1.f2, '' ) a2 FROM table1 t1 WHERE 1 = 1 ORDER BY a1 ASC",
398 "(ORDER BY isnull( t1.f1, '' ) ASC)"
401 "SELECT ISNULL( t1.f1, '' ) a1, ISNULL( t1.f2, '' ) a2 FROM table1 t1 WHERE 1 = 1 ORDER BY a2 ASC",
404 "(ORDER BY isnull( t1.f2, '' ) ASC)"
407 "SELECT ISNULL( t1.f1, '' ) a1, ISNULL( t1.f2, '' ) a2 FROM table1 t1 WHERE 1 = 1 ORDER BY t1.f1 ASC",
410 "(ORDER BY t1.f1 ASC)"
413 "SELECT ISNULL( t1.f1, '' ) a1, ISNULL( t1.f2, '' ) a2 FROM table1 t1 WHERE 1 = 1 ORDER BY t1.f2 ASC",
416 "(ORDER BY t1.f2 ASC)"
420 "SELECT ISNULL( t1.f1, '' ) a1, ISNULL( t1.f2, '' ) a2 FROM table1 t1 WHERE 1 = 1 ORDER BY a1 ASC",
423 "(ORDER BY isnull( t1.f1, '' ) ASC)"
426 "SELECT ISNULL( t1.f1, '' ) a1, ISNULL( t1.f2, '' ) a2 FROM table1 t1 WHERE 1 = 1 ORDER BY a2 ASC",
429 "(ORDER BY isnull( t1.f2, '' ) ASC)"
432 "SELECT ISNULL( t1.f1, '' ) a1, ISNULL( t1.f2, '' ) a2 FROM table1 t1 WHERE 1 = 1 ORDER BY t1.f1 ASC",
435 "(ORDER BY t1.f1 ASC)"
438 "SELECT ISNULL( t1.f1, '' ) a1, ISNULL( t1.f2, '' ) a2 FROM table1 t1 WHERE 1 = 1 ORDER BY t1.f2 ASC",
441 "(ORDER BY t1.f2 ASC)"
446 ISNULL(accounts.id,'') primaryid,
447 ISNULL(accounts.name,'') accounts_name,
448 ISNULL(l2.id,'') l2_id,
449 l2.email_address l2_email_address
453 accounts_contacts l1_1
455 accounts.id=l1_1.account_id
460 l1.id=l1_1.contact_id
462 AND l1.team_set_id IN (
468 team_memberships team_memberships
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
475 email_addr_bean_rel l2_1
479 AND l2_1.primary_address = '1'
483 l2.id=l2_1.email_address_id
487 AND accounts.team_set_id IN (
493 team_memberships team_memberships
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
499 AND accounts.deleted=0
505 "(ORDER BY l2.email_address ASC)"
510 ISNULL(accounts.id,'') primaryid,
511 ISNULL(accounts.name,'') accounts_name,
512 ISNULL(l2.id,'') l2_id,
513 l2.email_address l2_email_address
517 accounts_contacts l1_1
519 accounts.id=l1_1.account_id
524 l1.id=l1_1.contact_id
526 AND l1.team_set_id IN (
532 team_memberships team_memberships
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
539 email_addr_bean_rel l2_1
543 AND l2_1.primary_address = '1'
547 l2.id=l2_1.email_address_id
551 AND accounts.team_set_id IN (
557 team_memberships team_memberships
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
563 AND accounts.deleted=0
569 "(ORDER BY isnull(accounts.name,'') ASC)"
573 "SELECT DISTINCT meetings.id,
574 LTRIM(RTRIM(ISNULL(jt0.first_name,'')+' '+ISNULL(jt0.last_name,''))) assigned_user_name,
575 'Users' assigned_user_name_mod,
576 meetings.date_entered
581 meetings.assigned_user_id=jt0.id
587 sfav.module ='Meetings'
588 AND sfav.record_id=meetings.id
589 AND sfav.created_by='1'
592 (meetings.status IN ('Planned'))
593 AND (meetings.assigned_user_id IN ('1','seed_chris_id','seed_jim_id'))
594 ) AND meetings.deleted=0
596 meetings.date_entered DESC
600 "group by meetings.id, LTRIM(RTRIM(ISNULL(jt0.first_name,'')+' '+ISNULL(jt0.last_name,''))), meetings.date_entered"
604 "SELECT DISTINCT m1.id,
614 rt.meeting_id = m1.id
626 "(ORDER BY m1.id, m1.name, m1.date_start, m1.date_end, m1.assigned_user_id)"
630 "SELECT DISTINCT rt.id,
640 rt.meeting_id = m1.id
652 "(ORDER BY rt.id, m1.name, m1.date_start, m1.date_end, m1.assigned_user_id)"