PageRenderTime 48ms CodeModel.GetById 20ms RepoModel.GetById 0ms app.codeStats 0ms

/lib/Cake/Test/Case/Model/Datasource/Database/SqlserverTest.php

https://bitbucket.org/udeshika/fake_twitter
PHP | 659 lines | 374 code | 68 blank | 217 comment | 1 complexity | aa96327fd84ba9420d04cfeb63956843 MD5 | raw file
  1. <?php
  2. /**
  3. * SqlserverTest file
  4. *
  5. * PHP 5
  6. *
  7. * CakePHP(tm) : Rapid Development Framework (http://cakephp.org)
  8. * Copyright 2005-2011, Cake Software Foundation, Inc. (http://cakefoundation.org)
  9. *
  10. * Licensed under The MIT License
  11. * Redistributions of files must retain the above copyright notice.
  12. *
  13. * @copyright Copyright 2005-2011, Cake Software Foundation, Inc. (http://cakefoundation.org)
  14. * @link http://cakephp.org CakePHP(tm) Project
  15. * @package Cake.Test.Case.Model.Datasource.Database
  16. * @since CakePHP(tm) v 1.2.0
  17. * @license MIT License (http://www.opensource.org/licenses/mit-license.php)
  18. */
  19. App::uses('Model', 'Model');
  20. App::uses('AppModel', 'Model');
  21. App::uses('Sqlserver', 'Model/Datasource/Database');
  22. require_once dirname(dirname(dirname(__FILE__))) . DS . 'models.php';
  23. /**
  24. * SqlserverTestDb class
  25. *
  26. * @package Cake.Test.Case.Model.Datasource.Database
  27. */
  28. class SqlserverTestDb extends Sqlserver {
  29. /**
  30. * simulated property
  31. *
  32. * @var array
  33. */
  34. public $simulated = array();
  35. /**
  36. * execute results stack
  37. *
  38. * @var array
  39. */
  40. public $executeResultsStack = array();
  41. /**
  42. * execute method
  43. *
  44. * @param mixed $sql
  45. * @param mixed $params
  46. * @param mixed $prepareOptions
  47. * @return mixed
  48. */
  49. protected function _execute($sql, $params = array(), $prepareOptions = array()) {
  50. $this->simulated[] = $sql;
  51. return empty($this->executeResultsStack) ? null : array_pop($this->executeResultsStack);
  52. }
  53. /**
  54. * fetchAll method
  55. *
  56. * @param mixed $sql
  57. * @return void
  58. */
  59. protected function _matchRecords($model, $conditions = null) {
  60. return $this->conditions(array('id' => array(1, 2)));
  61. }
  62. /**
  63. * getLastQuery method
  64. *
  65. * @return string
  66. */
  67. public function getLastQuery() {
  68. return $this->simulated[count($this->simulated) - 1];
  69. }
  70. /**
  71. * getPrimaryKey method
  72. *
  73. * @param mixed $model
  74. * @return string
  75. */
  76. public function getPrimaryKey($model) {
  77. return parent::_getPrimaryKey($model);
  78. }
  79. /**
  80. * clearFieldMappings method
  81. *
  82. * @return void
  83. */
  84. public function clearFieldMappings() {
  85. $this->_fieldMappings = array();
  86. }
  87. /**
  88. * describe method
  89. *
  90. * @param object $model
  91. * @return void
  92. */
  93. public function describe($model) {
  94. return empty($this->describe) ? parent::describe($model) : $this->describe;
  95. }
  96. }
  97. /**
  98. * SqlserverTestModel class
  99. *
  100. * @package Cake.Test.Case.Model.Datasource.Database
  101. */
  102. class SqlserverTestModel extends Model {
  103. /**
  104. * name property
  105. *
  106. * @var string 'SqlserverTestModel'
  107. */
  108. public $name = 'SqlserverTestModel';
  109. /**
  110. * useTable property
  111. *
  112. * @var bool false
  113. */
  114. public $useTable = false;
  115. /**
  116. * _schema property
  117. *
  118. * @var array
  119. */
  120. protected $_schema = array(
  121. 'id' => array('type' => 'integer', 'null' => '', 'default' => '', 'length' => '8', 'key' => 'primary'),
  122. 'client_id' => array('type' => 'integer', 'null' => '', 'default' => '0', 'length' => '11'),
  123. 'name' => array('type' => 'string', 'null' => '', 'default' => '', 'length' => '255'),
  124. 'login' => array('type' => 'string', 'null' => '', 'default' => '', 'length' => '255'),
  125. 'passwd' => array('type' => 'string', 'null' => '1', 'default' => '', 'length' => '255'),
  126. 'addr_1' => array('type' => 'string', 'null' => '1', 'default' => '', 'length' => '255'),
  127. 'addr_2' => array('type' => 'string', 'null' => '1', 'default' => '', 'length' => '25'),
  128. 'zip_code' => array('type' => 'string', 'null' => '1', 'default' => '', 'length' => '155'),
  129. 'city' => array('type' => 'string', 'null' => '1', 'default' => '', 'length' => '155'),
  130. 'country' => array('type' => 'string', 'null' => '1', 'default' => '', 'length' => '155'),
  131. 'phone' => array('type' => 'string', 'null' => '1', 'default' => '', 'length' => '155'),
  132. 'fax' => array('type' => 'string', 'null' => '1', 'default' => '', 'length' => '155'),
  133. 'url' => array('type' => 'string', 'null' => '1', 'default' => '', 'length' => '255'),
  134. 'email' => array('type' => 'string', 'null' => '1', 'default' => '', 'length' => '155'),
  135. 'comments' => array('type' => 'text', 'null' => '1', 'default' => '', 'length' => ''),
  136. 'last_login'=> array('type' => 'datetime', 'null' => '1', 'default' => '', 'length' => ''),
  137. 'created' => array('type' => 'date', 'null' => '1', 'default' => '', 'length' => ''),
  138. 'updated' => array('type' => 'datetime', 'null' => '1', 'default' => '', 'length' => null)
  139. );
  140. /**
  141. * belongsTo property
  142. *
  143. * @var array
  144. */
  145. public $belongsTo = array(
  146. 'SqlserverClientTestModel' => array(
  147. 'foreignKey' => 'client_id'
  148. )
  149. );
  150. /**
  151. * find method
  152. *
  153. * @param mixed $conditions
  154. * @param mixed $fields
  155. * @param mixed $order
  156. * @param mixed $recursive
  157. * @return void
  158. */
  159. public function find($conditions = null, $fields = null, $order = null, $recursive = null) {
  160. return $conditions;
  161. }
  162. }
  163. /**
  164. * SqlserverClientTestModel class
  165. *
  166. * @package Cake.Test.Case.Model.Datasource.Database
  167. */
  168. class SqlserverClientTestModel extends Model {
  169. /**
  170. * name property
  171. *
  172. * @var string 'SqlserverAssociatedTestModel'
  173. */
  174. public $name = 'SqlserverClientTestModel';
  175. /**
  176. * useTable property
  177. *
  178. * @var bool false
  179. */
  180. public $useTable = false;
  181. /**
  182. * _schema property
  183. *
  184. * @var array
  185. */
  186. protected $_schema = array(
  187. 'id' => array('type' => 'integer', 'null' => '', 'default' => '', 'length' => '8', 'key' => 'primary'),
  188. 'name' => array('type' => 'string', 'null' => '', 'default' => '', 'length' => '255'),
  189. 'email' => array('type' => 'string', 'null' => '1', 'default' => '', 'length' => '155'),
  190. 'created' => array('type' => 'datetime', 'null' => '1', 'default' => '', 'length' => ''),
  191. 'updated' => array('type' => 'datetime', 'null' => '1', 'default' => '', 'length' => null)
  192. );
  193. }
  194. /**
  195. * SqlserverTestResultIterator class
  196. *
  197. * @package Cake.Test.Case.Model.Datasource.Database
  198. */
  199. class SqlserverTestResultIterator extends ArrayIterator {
  200. /**
  201. * closeCursor method
  202. *
  203. * @return void
  204. */
  205. public function closeCursor() {}
  206. }
  207. /**
  208. * SqlserverTest class
  209. *
  210. * @package Cake.Test.Case.Model.Datasource.Database
  211. */
  212. class SqlserverTest extends CakeTestCase {
  213. /**
  214. * The Dbo instance to be tested
  215. *
  216. * @var DboSource
  217. */
  218. public $db = null;
  219. /**
  220. * autoFixtures property
  221. *
  222. * @var bool false
  223. */
  224. public $autoFixtures = false;
  225. /**
  226. * fixtures property
  227. *
  228. * @var array
  229. */
  230. public $fixtures = array('core.user', 'core.category', 'core.author', 'core.post');
  231. /**
  232. * Sets up a Dbo class instance for testing
  233. *
  234. */
  235. public function setUp() {
  236. $this->Dbo = ConnectionManager::getDataSource('test');
  237. if (!($this->Dbo instanceof Sqlserver)) {
  238. $this->markTestSkipped('Please configure the test datasource to use SQL Server.');
  239. }
  240. $this->db = new SqlserverTestDb($this->Dbo->config);
  241. $this->model = new SqlserverTestModel();
  242. }
  243. /**
  244. * tearDown method
  245. *
  246. * @return void
  247. */
  248. public function tearDown() {
  249. unset($this->Dbo);
  250. unset($this->model);
  251. }
  252. /**
  253. * testQuoting method
  254. *
  255. * @return void
  256. */
  257. public function testQuoting() {
  258. $expected = "1.200000";
  259. $result = $this->db->value(1.2, 'float');
  260. $this->assertSame($expected, $result);
  261. $expected = "'1,2'";
  262. $result = $this->db->value('1,2', 'float');
  263. $this->assertSame($expected, $result);
  264. $expected = 'NULL';
  265. $result = $this->db->value('', 'integer');
  266. $this->assertSame($expected, $result);
  267. $expected = 'NULL';
  268. $result = $this->db->value('', 'float');
  269. $this->assertSame($expected, $result);
  270. $expected = "''";
  271. $result = $this->db->value('', 'binary');
  272. $this->assertSame($expected, $result);
  273. }
  274. /**
  275. * testFields method
  276. *
  277. * @return void
  278. */
  279. public function testFields() {
  280. $fields = array(
  281. '[SqlserverTestModel].[id] AS [SqlserverTestModel__id]',
  282. '[SqlserverTestModel].[client_id] AS [SqlserverTestModel__client_id]',
  283. '[SqlserverTestModel].[name] AS [SqlserverTestModel__name]',
  284. '[SqlserverTestModel].[login] AS [SqlserverTestModel__login]',
  285. '[SqlserverTestModel].[passwd] AS [SqlserverTestModel__passwd]',
  286. '[SqlserverTestModel].[addr_1] AS [SqlserverTestModel__addr_1]',
  287. '[SqlserverTestModel].[addr_2] AS [SqlserverTestModel__addr_2]',
  288. '[SqlserverTestModel].[zip_code] AS [SqlserverTestModel__zip_code]',
  289. '[SqlserverTestModel].[city] AS [SqlserverTestModel__city]',
  290. '[SqlserverTestModel].[country] AS [SqlserverTestModel__country]',
  291. '[SqlserverTestModel].[phone] AS [SqlserverTestModel__phone]',
  292. '[SqlserverTestModel].[fax] AS [SqlserverTestModel__fax]',
  293. '[SqlserverTestModel].[url] AS [SqlserverTestModel__url]',
  294. '[SqlserverTestModel].[email] AS [SqlserverTestModel__email]',
  295. '[SqlserverTestModel].[comments] AS [SqlserverTestModel__comments]',
  296. 'CONVERT(VARCHAR(20), [SqlserverTestModel].[last_login], 20) AS [SqlserverTestModel__last_login]',
  297. '[SqlserverTestModel].[created] AS [SqlserverTestModel__created]',
  298. 'CONVERT(VARCHAR(20), [SqlserverTestModel].[updated], 20) AS [SqlserverTestModel__updated]'
  299. );
  300. $result = $this->db->fields($this->model);
  301. $expected = $fields;
  302. $this->assertEquals($expected, $result);
  303. $this->db->clearFieldMappings();
  304. $result = $this->db->fields($this->model, null, 'SqlserverTestModel.*');
  305. $expected = $fields;
  306. $this->assertEquals($expected, $result);
  307. $this->db->clearFieldMappings();
  308. $result = $this->db->fields($this->model, null, array('*', 'AnotherModel.id', 'AnotherModel.name'));
  309. $expected = array_merge($fields, array(
  310. '[AnotherModel].[id] AS [AnotherModel__id]',
  311. '[AnotherModel].[name] AS [AnotherModel__name]'));
  312. $this->assertEquals($expected, $result);
  313. $this->db->clearFieldMappings();
  314. $result = $this->db->fields($this->model, null, array('*', 'SqlserverClientTestModel.*'));
  315. $expected = array_merge($fields, array(
  316. '[SqlserverClientTestModel].[id] AS [SqlserverClientTestModel__id]',
  317. '[SqlserverClientTestModel].[name] AS [SqlserverClientTestModel__name]',
  318. '[SqlserverClientTestModel].[email] AS [SqlserverClientTestModel__email]',
  319. 'CONVERT(VARCHAR(20), [SqlserverClientTestModel].[created], 20) AS [SqlserverClientTestModel__created]',
  320. 'CONVERT(VARCHAR(20), [SqlserverClientTestModel].[updated], 20) AS [SqlserverClientTestModel__updated]'));
  321. $this->assertEquals($expected, $result);
  322. }
  323. /**
  324. * testDistinctFields method
  325. *
  326. * @return void
  327. */
  328. public function testDistinctFields() {
  329. $result = $this->db->fields($this->model, null, array('DISTINCT Car.country_code'));
  330. $expected = array('DISTINCT [Car].[country_code] AS [Car__country_code]');
  331. $this->assertEquals($expected, $result);
  332. $result = $this->db->fields($this->model, null, 'DISTINCT Car.country_code');
  333. $expected = array('DISTINCT [Car].[country_code] AS [Car__country_code]');
  334. $this->assertEquals($expected, $result);
  335. }
  336. /**
  337. * testDistinctWithLimit method
  338. *
  339. * @return void
  340. */
  341. public function testDistinctWithLimit() {
  342. $this->db->read($this->model, array(
  343. 'fields' => array('DISTINCT SqlserverTestModel.city', 'SqlserverTestModel.country'),
  344. 'limit' => 5
  345. ));
  346. $result = $this->db->getLastQuery();
  347. $this->assertRegExp('/^SELECT DISTINCT TOP 5/', $result);
  348. }
  349. /**
  350. * testDescribe method
  351. *
  352. * @return void
  353. */
  354. public function testDescribe() {
  355. $SqlserverTableDescription = new SqlserverTestResultIterator(array(
  356. (object) array(
  357. 'Default' => '((0))',
  358. 'Field' => 'count',
  359. 'Key' => 0,
  360. 'Length' => '4',
  361. 'Null' => 'NO',
  362. 'Type' => 'integer'
  363. ),
  364. (object) array(
  365. 'Default' => '',
  366. 'Field' => 'body',
  367. 'Key' => 0,
  368. 'Length' => '-1',
  369. 'Null' => 'YES',
  370. 'Type' => 'nvarchar'
  371. ),
  372. (object) array(
  373. 'Default' => '',
  374. 'Field' => 'published',
  375. 'Key' => 0,
  376. 'Type' => 'datetime2',
  377. 'Length' => 8,
  378. 'Null' => 'YES',
  379. 'Size' => ''
  380. ),
  381. (object) array(
  382. 'Default' => '',
  383. 'Field' => 'id',
  384. 'Key' => 1,
  385. 'Type' => 'nchar',
  386. 'Length' => 72,
  387. 'Null' => 'NO',
  388. 'Size' => ''
  389. )
  390. ));
  391. $this->db->executeResultsStack = array($SqlserverTableDescription);
  392. $dummyModel = $this->model;
  393. $result = $this->db->describe($dummyModel);
  394. $expected = array(
  395. 'count' => array(
  396. 'type' => 'integer',
  397. 'null' => false,
  398. 'default' => '0',
  399. 'length' => 4
  400. ),
  401. 'body' => array(
  402. 'type' => 'text',
  403. 'null' => true,
  404. 'default' => null,
  405. 'length' => null
  406. ),
  407. 'published' => array(
  408. 'type' => 'datetime',
  409. 'null' => true,
  410. 'default' => '',
  411. 'length' => null
  412. ),
  413. 'id' => array(
  414. 'type' => 'string',
  415. 'null' => false,
  416. 'default' => '',
  417. 'length' => 36,
  418. 'key' => 'primary'
  419. )
  420. );
  421. $this->assertEquals($expected, $result);
  422. }
  423. /**
  424. * testBuildColumn
  425. *
  426. * @return void
  427. */
  428. public function testBuildColumn() {
  429. $column = array('name' => 'id', 'type' => 'integer', 'null' => false, 'default' => '', 'length' => '8', 'key' => 'primary');
  430. $result = $this->db->buildColumn($column);
  431. $expected = '[id] int IDENTITY (1, 1) NOT NULL';
  432. $this->assertEquals($expected, $result);
  433. $column = array('name' => 'client_id', 'type' => 'integer', 'null' => false, 'default' => '0', 'length' => '11');
  434. $result = $this->db->buildColumn($column);
  435. $expected = '[client_id] int DEFAULT 0 NOT NULL';
  436. $this->assertEquals($expected, $result);
  437. $column = array('name' => 'client_id', 'type' => 'integer', 'null' => true);
  438. $result = $this->db->buildColumn($column);
  439. $expected = '[client_id] int NULL';
  440. $this->assertEquals($expected, $result);
  441. // 'name' => 'type' format for columns
  442. $column = array('type' => 'integer', 'name' => 'client_id');
  443. $result = $this->db->buildColumn($column);
  444. $expected = '[client_id] int NULL';
  445. $this->assertEquals($expected, $result);
  446. $column = array('type' => 'string', 'name' => 'name');
  447. $result = $this->db->buildColumn($column);
  448. $expected = '[name] nvarchar(255) NULL';
  449. $this->assertEquals($expected, $result);
  450. $column = array('name' => 'name', 'type' => 'string', 'null' => false, 'default' => '', 'length' => '255');
  451. $result = $this->db->buildColumn($column);
  452. $expected = '[name] nvarchar(255) DEFAULT \'\' NOT NULL';
  453. $this->assertEquals($expected, $result);
  454. $column = array('name' => 'name', 'type' => 'string', 'null' => false, 'length' => '255');
  455. $result = $this->db->buildColumn($column);
  456. $expected = '[name] nvarchar(255) NOT NULL';
  457. $this->assertEquals($expected, $result);
  458. $column = array('name' => 'name', 'type' => 'string', 'null' => false, 'default' => null, 'length' => '255');
  459. $result = $this->db->buildColumn($column);
  460. $expected = '[name] nvarchar(255) NOT NULL';
  461. $this->assertEquals($expected, $result);
  462. $column = array('name' => 'name', 'type' => 'string', 'null' => true, 'default' => null, 'length' => '255');
  463. $result = $this->db->buildColumn($column);
  464. $expected = '[name] nvarchar(255) NULL';
  465. $this->assertEquals($expected, $result);
  466. $column = array('name' => 'name', 'type' => 'string', 'null' => true, 'default' => '', 'length' => '255');
  467. $result = $this->db->buildColumn($column);
  468. $expected = '[name] nvarchar(255) DEFAULT \'\'';
  469. $this->assertEquals($expected, $result);
  470. $column = array('name' => 'body', 'type' => 'text');
  471. $result = $this->db->buildColumn($column);
  472. $expected = '[body] nvarchar(MAX)';
  473. $this->assertEquals($expected, $result);
  474. }
  475. /**
  476. * testBuildIndex method
  477. *
  478. * @return void
  479. */
  480. public function testBuildIndex() {
  481. $indexes = array(
  482. 'PRIMARY' => array('column' => 'id', 'unique' => 1),
  483. 'client_id' => array('column' => 'client_id', 'unique' => 1)
  484. );
  485. $result = $this->db->buildIndex($indexes, 'items');
  486. $expected = array(
  487. 'PRIMARY KEY ([id])',
  488. 'ALTER TABLE items ADD CONSTRAINT client_id UNIQUE([client_id]);'
  489. );
  490. $this->assertEquals($expected, $result);
  491. $indexes = array('client_id' => array('column' => 'client_id'));
  492. $result = $this->db->buildIndex($indexes, 'items');
  493. $this->assertEquals($result, array());
  494. $indexes = array('client_id' => array('column' => array('client_id', 'period_id'), 'unique' => 1));
  495. $result = $this->db->buildIndex($indexes, 'items');
  496. $expected = array('ALTER TABLE items ADD CONSTRAINT client_id UNIQUE([client_id], [period_id]);');
  497. $this->assertEquals($expected, $result);
  498. }
  499. /**
  500. * testUpdateAllSyntax method
  501. *
  502. * @return void
  503. */
  504. public function testUpdateAllSyntax() {
  505. $fields = array('SqlserverTestModel.client_id' => '[SqlserverTestModel].[client_id] + 1');
  506. $conditions = array('SqlserverTestModel.updated <' => date('2009-01-01 00:00:00'));
  507. $this->db->update($this->model, $fields, null, $conditions);
  508. $result = $this->db->getLastQuery();
  509. $this->assertNotRegExp('/SqlserverTestModel/', $result);
  510. $this->assertRegExp('/^UPDATE \[sqlserver_test_models\]/', $result);
  511. $this->assertRegExp('/SET \[client_id\] = \[client_id\] \+ 1/', $result);
  512. }
  513. /**
  514. * testGetPrimaryKey method
  515. *
  516. * @return void
  517. */
  518. public function testGetPrimaryKey() {
  519. $schema = $this->model->schema();
  520. $this->db->describe = $schema;
  521. $result = $this->db->getPrimaryKey($this->model);
  522. $this->assertEquals($result, 'id');
  523. unset($schema['id']['key']);
  524. $this->db->describe = $schema;
  525. $result = $this->db->getPrimaryKey($this->model);
  526. $this->assertNull($result);
  527. }
  528. /**
  529. * testInsertMulti
  530. *
  531. * @return void
  532. */
  533. public function testInsertMulti() {
  534. $this->db->describe = $this->model->schema();
  535. $fields = array('id', 'name', 'login');
  536. $values = array(
  537. array(1, 'Larry', 'PhpNut'),
  538. array(2, 'Renan', 'renan.saddam'));
  539. $this->db->simulated = array();
  540. $this->db->insertMulti($this->model, $fields, $values);
  541. $result = $this->db->simulated;
  542. $expected = array(
  543. 'SET IDENTITY_INSERT [sqlserver_test_models] ON',
  544. "INSERT INTO [sqlserver_test_models] ([id], [name], [login]) VALUES (1, N'Larry', N'PhpNut')",
  545. "INSERT INTO [sqlserver_test_models] ([id], [name], [login]) VALUES (2, N'Renan', N'renan.saddam')",
  546. 'SET IDENTITY_INSERT [sqlserver_test_models] OFF'
  547. );
  548. $this->assertEquals($expected, $result);
  549. $fields = array('name', 'login');
  550. $values = array(
  551. array('Larry', 'PhpNut'),
  552. array('Renan', 'renan.saddam'));
  553. $this->db->simulated = array();
  554. $this->db->insertMulti($this->model, $fields, $values);
  555. $result = $this->db->simulated;
  556. $expected = array(
  557. "INSERT INTO [sqlserver_test_models] ([name], [login]) VALUES (N'Larry', N'PhpNut')",
  558. "INSERT INTO [sqlserver_test_models] ([name], [login]) VALUES (N'Renan', N'renan.saddam')",
  559. );
  560. $this->assertEquals($expected, $result);
  561. }
  562. /**
  563. * SQL server < 11 doesn't have proper limit/offset support, test that our hack works.
  564. *
  565. * @return void
  566. */
  567. public function testLimitOffsetHack() {
  568. $this->loadFixtures('Author', 'Post', 'User');
  569. $query = array(
  570. 'limit' => 2,
  571. 'page' => 1,
  572. 'order' => 'User.user ASC',
  573. );
  574. $User = ClassRegistry::init('User');
  575. $results = $User->find('all', $query);
  576. $this->assertEquals(2, count($results));
  577. $this->assertEquals('garrett', $results[0]['User']['user']);
  578. $this->assertEquals('larry', $results[1]['User']['user']);
  579. $query = array(
  580. 'limit' => 2,
  581. 'page' => 2,
  582. 'order' => 'User.user ASC',
  583. );
  584. $User = ClassRegistry::init('User');
  585. $results = $User->find('all', $query);
  586. $this->assertEquals(2, count($results));
  587. $this->assertFalse(isset($results[0][0]));
  588. $this->assertEquals('mariano', $results[0]['User']['user']);
  589. $this->assertEquals('nate', $results[1]['User']['user']);
  590. }
  591. }