Mercurial > hg > Members > shoshi > webvirt

comparison cake/tests/cases/libs/model/datasources/dbo/dbo_mssql.test.php @ 0:261e66bd5a0c

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
hg init
author Shoshi TAMAKI <shoshi@cr.ie.u-ryukyu.ac.jp>
date Sun, 24 Jul 2011 21:08:31 +0900
parents
children
comparison
equal deleted inserted replaced
-1:000000000000 0:261e66bd5a0c
1 <?php
2 /**
3 * DboMssqlTest file
4 *
5 * PHP versions 4 and 5
6 *
7 * CakePHP(tm) : Rapid Development Framework (http://cakephp.org)
8 * Copyright 2005-2010, 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-2010, Cake Software Foundation, Inc. (http://cakefoundation.org)
14 * @link http://cakephp.org CakePHP(tm) Project
15 * @package cake
16 * @subpackage cake.cake.libs
17 * @since CakePHP(tm) v 1.2.0
18 * @license MIT License (http://www.opensource.org/licenses/mit-license.php)
19 */
20 if (!defined('CAKEPHP_UNIT_TEST_EXECUTION')) {
21 define('CAKEPHP_UNIT_TEST_EXECUTION', 1);
22 }
23 require_once LIBS.'model'.DS.'model.php';
24 require_once LIBS.'model'.DS.'datasources'.DS.'datasource.php';
25 require_once LIBS.'model'.DS.'datasources'.DS.'dbo_source.php';
26 require_once LIBS.'model'.DS.'datasources'.DS.'dbo'.DS.'dbo_mssql.php';
27
28 /**
29 * DboMssqlTestDb class
30 *
31 * @package cake
32 * @subpackage cake.tests.cases.libs.model.datasources.dbo
33 */
34 class DboMssqlTestDb extends DboMssql {
35
36 /**
37 * simulated property
38 *
39 * @var array
40 * @access public
41 */
42 var $simulated = array();
43
44 /**
45 * simalate property
46 *
47 * @var array
48 * @access public
49 */
50 var $simulate = true;
51 /**
52 * fetchAllResultsStack
53 *
54 * @var array
55 * @access public
56 */
57 var $fetchAllResultsStack = array();
58
59 /**
60 * execute method
61 *
62 * @param mixed $sql
63 * @access protected
64 * @return void
65 */
66 function _execute($sql) {
67 if ($this->simulate) {
68 $this->simulated[] = $sql;
69 return null;
70 } else {
71 return parent::_execute($sql);
72 }
73 }
74
75 /**
76 * fetchAll method
77 *
78 * @param mixed $sql
79 * @access protected
80 * @return void
81 */
82 function _matchRecords(&$model, $conditions = null) {
83 return $this->conditions(array('id' => array(1, 2)));
84 }
85
86 /**
87 * fetchAll method
88 *
89 * @param mixed $sql
90 * @access protected
91 * @return void
92 */
93 function fetchAll($sql, $cache = true, $modelName = null) {
94 $result = parent::fetchAll($sql, $cache, $modelName);
95 if (!empty($this->fetchAllResultsStack)) {
96 return array_pop($this->fetchAllResultsStack);
97 }
98 return $result;
99 }
100
101 /**
102 * getLastQuery method
103 *
104 * @access public
105 * @return void
106 */
107 function getLastQuery() {
108 return $this->simulated[count($this->simulated) - 1];
109 }
110
111 /**
112 * getPrimaryKey method
113 *
114 * @param mixed $model
115 * @access public
116 * @return void
117 */
118 function getPrimaryKey($model) {
119 return parent::_getPrimaryKey($model);
120 }
121 /**
122 * clearFieldMappings method
123 *
124 * @access public
125 * @return void
126 */
127 function clearFieldMappings() {
128 $this->__fieldMappings = array();
129 }
130 }
131
132 /**
133 * MssqlTestModel class
134 *
135 * @package cake
136 * @subpackage cake.tests.cases.libs.model.datasources
137 */
138 class MssqlTestModel extends Model {
139
140 /**
141 * name property
142 *
143 * @var string 'MssqlTestModel'
144 * @access public
145 */
146 var $name = 'MssqlTestModel';
147
148 /**
149 * useTable property
150 *
151 * @var bool false
152 * @access public
153 */
154 var $useTable = false;
155
156 /**
157 * _schema property
158 *
159 * @var array
160 * @access protected
161 */
162 var $_schema = array(
163 'id' => array('type' => 'integer', 'null' => '', 'default' => '', 'length' => '8', 'key' => 'primary'),
164 'client_id' => array('type' => 'integer', 'null' => '', 'default' => '0', 'length' => '11'),
165 'name' => array('type' => 'string', 'null' => '', 'default' => '', 'length' => '255'),
166 'login' => array('type' => 'string', 'null' => '', 'default' => '', 'length' => '255'),
167 'passwd' => array('type' => 'string', 'null' => '1', 'default' => '', 'length' => '255'),
168 'addr_1' => array('type' => 'string', 'null' => '1', 'default' => '', 'length' => '255'),
169 'addr_2' => array('type' => 'string', 'null' => '1', 'default' => '', 'length' => '25'),
170 'zip_code' => array('type' => 'string', 'null' => '1', 'default' => '', 'length' => '155'),
171 'city' => array('type' => 'string', 'null' => '1', 'default' => '', 'length' => '155'),
172 'country' => array('type' => 'string', 'null' => '1', 'default' => '', 'length' => '155'),
173 'phone' => array('type' => 'string', 'null' => '1', 'default' => '', 'length' => '155'),
174 'fax' => array('type' => 'string', 'null' => '1', 'default' => '', 'length' => '155'),
175 'url' => array('type' => 'string', 'null' => '1', 'default' => '', 'length' => '255'),
176 'email' => array('type' => 'string', 'null' => '1', 'default' => '', 'length' => '155'),
177 'comments' => array('type' => 'text', 'null' => '1', 'default' => '', 'length' => ''),
178 'last_login'=> array('type' => 'datetime', 'null' => '1', 'default' => '', 'length' => ''),
179 'created' => array('type' => 'date', 'null' => '1', 'default' => '', 'length' => ''),
180 'updated' => array('type' => 'datetime', 'null' => '1', 'default' => '', 'length' => null)
181 );
182
183 /**
184 * belongsTo property
185 *
186 * @var array
187 * @access public
188 */
189 var $belongsTo = array(
190 'MssqlClientTestModel' => array(
191 'foreignKey' => 'client_id'
192 )
193 );
194 /**
195 * find method
196 *
197 * @param mixed $conditions
198 * @param mixed $fields
199 * @param mixed $order
200 * @param mixed $recursive
201 * @access public
202 * @return void
203 */
204 function find($conditions = null, $fields = null, $order = null, $recursive = null) {
205 return $conditions;
206 }
207
208 /**
209 * findAll method
210 *
211 * @param mixed $conditions
212 * @param mixed $fields
213 * @param mixed $order
214 * @param mixed $recursive
215 * @access public
216 * @return void
217 */
218 function findAll($conditions = null, $fields = null, $order = null, $recursive = null) {
219 return $conditions;
220 }
221
222 /**
223 * setSchema method
224 *
225 * @param array $schema
226 * @access public
227 * @return void
228 */
229 function setSchema($schema) {
230 $this->_schema = $schema;
231 }
232 }
233
234 /**
235 * MssqlClientTestModel class
236 *
237 * @package cake
238 * @subpackage cake.tests.cases.libs.model.datasources
239 */
240 class MssqlClientTestModel extends Model {
241 /**
242 * name property
243 *
244 * @var string 'MssqlAssociatedTestModel'
245 * @access public
246 */
247 var $name = 'MssqlClientTestModel';
248 /**
249 * useTable property
250 *
251 * @var bool false
252 * @access public
253 */
254 var $useTable = false;
255 /**
256 * _schema property
257 *
258 * @var array
259 * @access protected
260 */
261 var $_schema = array(
262 'id' => array('type' => 'integer', 'null' => '', 'default' => '', 'length' => '8', 'key' => 'primary'),
263 'name' => array('type' => 'string', 'null' => '', 'default' => '', 'length' => '255'),
264 'email' => array('type' => 'string', 'null' => '1', 'default' => '', 'length' => '155'),
265 'created' => array('type' => 'datetime', 'null' => '1', 'default' => '', 'length' => ''),
266 'updated' => array('type' => 'datetime', 'null' => '1', 'default' => '', 'length' => null)
267 );
268 }
269 /**
270 * DboMssqlTest class
271 *
272 * @package cake
273 * @subpackage cake.tests.cases.libs.model.datasources.dbo
274 */
275 class DboMssqlTest extends CakeTestCase {
276
277 /**
278 * The Dbo instance to be tested
279 *
280 * @var DboSource
281 * @access public
282 */
283 var $db = null;
284
285 /**
286 * autoFixtures property
287 *
288 * @var bool false
289 * @access public
290 */
291 var $autoFixtures = false;
292 /**
293 * fixtures property
294 *
295 * @var array
296 * @access public
297 */
298 var $fixtures = array('core.category');
299 /**
300 * Skip if cannot connect to mssql
301 *
302 * @access public
303 */
304 function skip() {
305 $this->_initDb();
306 $this->skipUnless($this->db->config['driver'] == 'mssql', '%s SQL Server connection not available');
307 }
308
309 /**
310 * Make sure all fixtures tables are being created
311 *
312 * @access public
313 */
314 function start() {
315 $this->db->simulate = false;
316 parent::start();
317 $this->db->simulate = true;
318 }
319 /**
320 * Make sure all fixtures tables are being dropped
321 *
322 * @access public
323 */
324 function end() {
325 $this->db->simulate = false;
326 parent::end();
327 $this->db->simulate = true;
328 }
329 /**
330 * Sets up a Dbo class instance for testing
331 *
332 * @access public
333 */
334 function setUp() {
335 $db = ConnectionManager::getDataSource('test_suite');
336 $this->db = new DboMssqlTestDb($db->config);
337 $this->model = new MssqlTestModel();
338 }
339
340 /**
341 * tearDown method
342 *
343 * @access public
344 * @return void
345 */
346 function tearDown() {
347 unset($this->model);
348 }
349
350 /**
351 * testQuoting method
352 *
353 * @access public
354 * @return void
355 */
356 function testQuoting() {
357 $expected = "1.2";
358 $result = $this->db->value(1.2, 'float');
359 $this->assertIdentical($expected, $result);
360
361 $expected = "'1,2'";
362 $result = $this->db->value('1,2', 'float');
363 $this->assertIdentical($expected, $result);
364
365 $expected = 'NULL';
366 $result = $this->db->value('', 'integer');
367 $this->assertIdentical($expected, $result);
368
369 $expected = 'NULL';
370 $result = $this->db->value('', 'float');
371 $this->assertIdentical($expected, $result);
372
373 $expected = 'NULL';
374 $result = $this->db->value('', 'binary');
375 $this->assertIdentical($expected, $result);
376 }
377 /**
378 * testFields method
379 *
380 * @access public
381 * @return void
382 */
383 function testFields() {
384 $fields = array(
385 '[MssqlTestModel].[id] AS [MssqlTestModel__0]',
386 '[MssqlTestModel].[client_id] AS [MssqlTestModel__1]',
387 '[MssqlTestModel].[name] AS [MssqlTestModel__2]',
388 '[MssqlTestModel].[login] AS [MssqlTestModel__3]',
389 '[MssqlTestModel].[passwd] AS [MssqlTestModel__4]',
390 '[MssqlTestModel].[addr_1] AS [MssqlTestModel__5]',
391 '[MssqlTestModel].[addr_2] AS [MssqlTestModel__6]',
392 '[MssqlTestModel].[zip_code] AS [MssqlTestModel__7]',
393 '[MssqlTestModel].[city] AS [MssqlTestModel__8]',
394 '[MssqlTestModel].[country] AS [MssqlTestModel__9]',
395 '[MssqlTestModel].[phone] AS [MssqlTestModel__10]',
396 '[MssqlTestModel].[fax] AS [MssqlTestModel__11]',
397 '[MssqlTestModel].[url] AS [MssqlTestModel__12]',
398 '[MssqlTestModel].[email] AS [MssqlTestModel__13]',
399 '[MssqlTestModel].[comments] AS [MssqlTestModel__14]',
400 'CONVERT(VARCHAR(20), [MssqlTestModel].[last_login], 20) AS [MssqlTestModel__15]',
401 '[MssqlTestModel].[created] AS [MssqlTestModel__16]',
402 'CONVERT(VARCHAR(20), [MssqlTestModel].[updated], 20) AS [MssqlTestModel__17]'
403 );
404
405 $result = $this->db->fields($this->model);
406 $expected = $fields;
407 $this->assertEqual($result, $expected);
408
409 $this->db->clearFieldMappings();
410 $result = $this->db->fields($this->model, null, 'MssqlTestModel.*');
411 $expected = $fields;
412 $this->assertEqual($result, $expected);
413
414 $this->db->clearFieldMappings();
415 $result = $this->db->fields($this->model, null, array('*', 'AnotherModel.id', 'AnotherModel.name'));
416 $expected = array_merge($fields, array(
417 '[AnotherModel].[id] AS [AnotherModel__18]',
418 '[AnotherModel].[name] AS [AnotherModel__19]'));
419 $this->assertEqual($result, $expected);
420
421 $this->db->clearFieldMappings();
422 $result = $this->db->fields($this->model, null, array('*', 'MssqlClientTestModel.*'));
423 $expected = array_merge($fields, array(
424 '[MssqlClientTestModel].[id] AS [MssqlClientTestModel__18]',
425 '[MssqlClientTestModel].[name] AS [MssqlClientTestModel__19]',
426 '[MssqlClientTestModel].[email] AS [MssqlClientTestModel__20]',
427 'CONVERT(VARCHAR(20), [MssqlClientTestModel].[created], 20) AS [MssqlClientTestModel__21]',
428 'CONVERT(VARCHAR(20), [MssqlClientTestModel].[updated], 20) AS [MssqlClientTestModel__22]'));
429 $this->assertEqual($result, $expected);
430 }
431
432 /**
433 * testDistinctFields method
434 *
435 * @access public
436 * @return void
437 */
438 function testDistinctFields() {
439 $result = $this->db->fields($this->model, null, array('DISTINCT Car.country_code'));
440 $expected = array('DISTINCT [Car].[country_code] AS [Car__0]');
441 $this->assertEqual($result, $expected);
442
443 $result = $this->db->fields($this->model, null, 'DISTINCT Car.country_code');
444 $expected = array('DISTINCT [Car].[country_code] AS [Car__1]');
445 $this->assertEqual($result, $expected);
446 }
447
448 /**
449 * testDistinctWithLimit method
450 *
451 * @access public
452 * @return void
453 */
454 function testDistinctWithLimit() {
455 $this->db->read($this->model, array(
456 'fields' => array('DISTINCT MssqlTestModel.city', 'MssqlTestModel.country'),
457 'limit' => 5
458 ));
459 $result = $this->db->getLastQuery();
460 $this->assertPattern('/^SELECT DISTINCT TOP 5/', $result);
461 }
462
463 /**
464 * testDescribe method
465 *
466 * @access public
467 * @return void
468 */
469 function testDescribe() {
470 $MssqlTableDescription = array(
471 0 => array(
472 0 => array(
473 'Default' => '((0))',
474 'Field' => 'count',
475 'Key' => 0,
476 'Length' => '4',
477 'Null' => 'NO',
478 'Type' => 'integer',
479 )
480 )
481 );
482 $this->db->fetchAllResultsStack = array($MssqlTableDescription);
483 $dummyModel = $this->model;
484 $result = $this->db->describe($dummyModel);
485 $expected = array(
486 'count' => array(
487 'type' => 'integer',
488 'null' => false,
489 'default' => '0',
490 'length' => 4
491 )
492 );
493 $this->assertEqual($result, $expected);
494 }
495 /**
496 * testBuildColumn
497 *
498 * @return unknown_type
499 * @access public
500 */
501 function testBuildColumn() {
502 $column = array('name' => 'id', 'type' => 'integer', 'null' => '', 'default' => '', 'length' => '8', 'key' => 'primary');
503 $result = $this->db->buildColumn($column);
504 $expected = '[id] int IDENTITY (1, 1) NOT NULL';
505 $this->assertEqual($result, $expected);
506
507 $column = array('name' => 'client_id', 'type' => 'integer', 'null' => '', 'default' => '0', 'length' => '11');
508 $result = $this->db->buildColumn($column);
509 $expected = '[client_id] int DEFAULT 0 NOT NULL';
510 $this->assertEqual($result, $expected);
511
512 $column = array('name' => 'client_id', 'type' => 'integer', 'null' => true);
513 $result = $this->db->buildColumn($column);
514 $expected = '[client_id] int NULL';
515 $this->assertEqual($result, $expected);
516
517 // 'name' => 'type' format for columns
518 $column = array('type' => 'integer', 'name' => 'client_id');
519 $result = $this->db->buildColumn($column);
520 $expected = '[client_id] int NULL';
521 $this->assertEqual($result, $expected);
522
523 $column = array('type' => 'string', 'name' => 'name');
524 $result = $this->db->buildColumn($column);
525 $expected = '[name] varchar(255) NULL';
526 $this->assertEqual($result, $expected);
527
528 $column = array('name' => 'name', 'type' => 'string', 'null' => '', 'default' => '', 'length' => '255');
529 $result = $this->db->buildColumn($column);
530 $expected = '[name] varchar(255) DEFAULT \'\' NOT NULL';
531 $this->assertEqual($result, $expected);
532
533 $column = array('name' => 'name', 'type' => 'string', 'null' => false, 'length' => '255');
534 $result = $this->db->buildColumn($column);
535 $expected = '[name] varchar(255) NOT NULL';
536 $this->assertEqual($result, $expected);
537
538 $column = array('name' => 'name', 'type' => 'string', 'null' => false, 'default' => null, 'length' => '255');
539 $result = $this->db->buildColumn($column);
540 $expected = '[name] varchar(255) NOT NULL';
541 $this->assertEqual($result, $expected);
542
543 $column = array('name' => 'name', 'type' => 'string', 'null' => true, 'default' => null, 'length' => '255');
544 $result = $this->db->buildColumn($column);
545 $expected = '[name] varchar(255) NULL';
546 $this->assertEqual($result, $expected);
547
548 $column = array('name' => 'name', 'type' => 'string', 'null' => true, 'default' => '', 'length' => '255');
549 $result = $this->db->buildColumn($column);
550 $expected = '[name] varchar(255) DEFAULT \'\'';
551 $this->assertEqual($result, $expected);
552 }
553 /**
554 * testBuildIndex method
555 *
556 * @return void
557 * @access public
558 */
559 function testBuildIndex() {
560 $indexes = array(
561 'PRIMARY' => array('column' => 'id', 'unique' => 1),
562 'client_id' => array('column' => 'client_id', 'unique' => 1)
563 );
564 $result = $this->db->buildIndex($indexes, 'items');
565 $expected = array(
566 'PRIMARY KEY ([id])',
567 'ALTER TABLE items ADD CONSTRAINT client_id UNIQUE([client_id]);'
568 );
569 $this->assertEqual($result, $expected);
570
571 $indexes = array('client_id' => array('column' => 'client_id'));
572 $result = $this->db->buildIndex($indexes, 'items');
573 $this->assertEqual($result, array());
574
575 $indexes = array('client_id' => array('column' => array('client_id', 'period_id'), 'unique' => 1));
576 $result = $this->db->buildIndex($indexes, 'items');
577 $expected = array('ALTER TABLE items ADD CONSTRAINT client_id UNIQUE([client_id], [period_id]);');
578 $this->assertEqual($result, $expected);
579 }
580 /**
581 * testUpdateAllSyntax method
582 *
583 * @return void
584 * @access public
585 */
586 function testUpdateAllSyntax() {
587 $fields = array('MssqlTestModel.client_id' => '[MssqlTestModel].[client_id] + 1');
588 $conditions = array('MssqlTestModel.updated <' => date('2009-01-01 00:00:00'));
589 $this->db->update($this->model, $fields, null, $conditions);
590
591 $result = $this->db->getLastQuery();
592 $this->assertNoPattern('/MssqlTestModel/', $result);
593 $this->assertPattern('/^UPDATE \[mssql_test_models\]/', $result);
594 $this->assertPattern('/SET \[client_id\] = \[client_id\] \+ 1/', $result);
595 }
596
597 /**
598 * testGetPrimaryKey method
599 *
600 * @return void
601 * @access public
602 */
603 function testGetPrimaryKey() {
604 // When param is a model
605 $result = $this->db->getPrimaryKey($this->model);
606 $this->assertEqual($result, 'id');
607
608 $schema = $this->model->schema();
609 unset($schema['id']['key']);
610 $this->model->setSchema($schema);
611 $result = $this->db->getPrimaryKey($this->model);
612 $this->assertNull($result);
613
614 // When param is a table name
615 $this->db->simulate = false;
616 $this->loadFixtures('Category');
617 $result = $this->db->getPrimaryKey('categories');
618 $this->assertEqual($result, 'id');
619 }
620
621 /**
622 * testInsertMulti
623 *
624 * @return void
625 * @access public
626 */
627 function testInsertMulti() {
628 $fields = array('id', 'name', 'login');
629 $values = array('(1, \'Larry\', \'PhpNut\')', '(2, \'Renan\', \'renan.saddam\')');
630 $this->db->simulated = array();
631 $this->db->insertMulti($this->model, $fields, $values);
632 $result = $this->db->simulated;
633 $expected = array(
634 'SET IDENTITY_INSERT [mssql_test_models] ON',
635 'INSERT INTO [mssql_test_models] ([id], [name], [login]) VALUES (1, \'Larry\', \'PhpNut\')',
636 'INSERT INTO [mssql_test_models] ([id], [name], [login]) VALUES (2, \'Renan\', \'renan.saddam\')',
637 'SET IDENTITY_INSERT [mssql_test_models] OFF'
638 );
639 $this->assertEqual($result, $expected);
640
641 $fields = array('name', 'login');
642 $values = array('(\'Larry\', \'PhpNut\')', '(\'Renan\', \'renan.saddam\')');
643 $this->db->simulated = array();
644 $this->db->insertMulti($this->model, $fields, $values);
645 $result = $this->db->simulated;
646 $expected = array(
647 'INSERT INTO [mssql_test_models] ([name], [login]) VALUES (\'Larry\', \'PhpNut\')',
648 'INSERT INTO [mssql_test_models] ([name], [login]) VALUES (\'Renan\', \'renan.saddam\')'
649 );
650 $this->assertEqual($result, $expected);
651 }
652 /**
653 * testLastError
654 *
655 * @return void
656 * @access public
657 */
658 function testLastError() {
659 $debug = Configure::read('debug');
660 Configure::write('debug', 0);
661
662 $this->db->simulate = false;
663 $query = 'SELECT [name] FROM [categories]';
664 $this->assertTrue($this->db->execute($query) !== false);
665 $this->assertNull($this->db->lastError());
666
667 $query = 'SELECT [inexistent_field] FROM [categories]';
668 $this->assertFalse($this->db->execute($query));
669 $this->assertNotNull($this->db->lastError());
670
671 $query = 'SELECT [name] FROM [categories]';
672 $this->assertTrue($this->db->execute($query) !== false);
673 $this->assertNull($this->db->lastError());
674
675 Configure::write('debug', $debug);
676 }
677 }