PageRenderTime 2ms CodeModel.GetById 5ms app.highlight 11ms RepoModel.GetById 1ms app.codeStats 0ms

/core/src/test/php/net/xp_framework/unittest/rdbms/SQLFunctionTest.class.php

http://github.com/xp-framework/xp-framework
PHP | 395 lines | 337 code | 16 blank | 42 comment | 0 complexity | 167ad77349cddd1513a262e47ea87d5a MD5 | raw file
  1<?php namespace net\xp_framework\unittest\rdbms;
  2
  3use unittest\TestCase;
  4use util\Date;
  5use rdbms\sybase\SybaseConnection;
  6use rdbms\mysql\MySQLConnection;
  7use rdbms\pgsql\PostgreSQLConnection;
  8use rdbms\sqlite\SQLiteConnection;
  9use rdbms\criterion\Restrictions;
 10use rdbms\SQLFunctions;
 11use net\xp_framework\unittest\rdbms\dataset\Job;
 12
 13/**
 14 * TestCase
 15 *
 16 * @see   xp://rdbms.SQLFunction
 17 */
 18class SQLFunctionTest extends TestCase {
 19  public
 20    $syconn = null,
 21    $myconn = null,
 22    $sqconn = null,
 23    $pgconn = null,
 24    $peer   = null;
 25    
 26  /**
 27   * Sets up a Database Object for the test
 28   *
 29   */
 30  public function setUp() {
 31    $this->syconn= new SybaseConnection(new \rdbms\DSN('sybase://localhost:1999/'));
 32    $this->myconn= new MySQLConnection(new \rdbms\DSN('mysql://localhost/'));
 33    $this->pgconn= new PostgreSQLConnection(new \rdbms\DSN('pgsql://localhost/'));
 34    $this->sqconn= new SQliteConnection(new \rdbms\DSN('sqlite://tmpdir/tmpdb'));
 35    $this->peer= Job::getPeer();
 36  }
 37  
 38  /**
 39   * Helper method that will call toSQL() on the passed criteria and
 40   * compare the resulting string to the expected string.
 41   *
 42   * @param   string mysql
 43   * @param   string sysql
 44   * @param   string pgsql
 45   * @param   string sqlite
 46   * @param   rdbms.Criteria criteria
 47   * @throws  unittest.AssertionFailedError
 48   */
 49  protected function assertSql($mysql, $sysql, $pgsql, $sqlite, $criteria) {
 50    $this->assertEquals('mysql: '.$mysql,  'mysql: '.trim($criteria->toSQL($this->myconn, $this->peer), ' '));
 51    $this->assertEquals('sybase: '.$sysql, 'sybase: '.trim($criteria->toSQL($this->syconn, $this->peer), ' '));
 52    $this->assertEquals('pgsql: '.$pgsql, 'pgsql: '.trim($criteria->toSQL($this->pgconn, $this->peer), ' '));
 53    $this->assertEquals('sqlite: '.$sqlite, 'sqlite: '.trim($criteria->toSQL($this->sqconn, $this->peer), ' '));
 54  }
 55  
 56  /**
 57   * Helper method that will call projection() on the passed criteria and
 58   * compare the resulting string to the expected string.
 59   *
 60   * @param   string mysql
 61   * @param   string sysql
 62   * @param   string pgsql
 63   * @param   string sqlite
 64   * @param   rdbms.Criteria criteria
 65   * @throws  unittest.AssertionFailedError
 66   */
 67  protected function assertProjection($mysql, $sysql, $pgsql, $sqlite, $criteria) {
 68    $this->assertEquals('mysql: '.$mysql,  'mysql: '.trim($criteria->projections($this->myconn, $this->peer), ' '));
 69    $this->assertEquals('sybase: '.$sysql, 'sybase: '.trim($criteria->projections($this->syconn, $this->peer), ' '));
 70    $this->assertEquals('pgsql: '.$pgsql, 'pgsql: '.trim($criteria->projections($this->pgconn, $this->peer), ' '));
 71    $this->assertEquals('sqlite: '.$sqlite, 'sqlite: '.trim($criteria->projections($this->sqconn, $this->peer), ' '));
 72  }
 73  
 74  #[@test]
 75  function columnTest() {
 76    $this->assertEquals(
 77      'job_id',
 78      Job::column('job_id')->getName()
 79    );
 80  }
 81
 82  #[@test]
 83  function projectionTest() {
 84    $this->assertProjection(
 85      'day(valid_from)',
 86      'day(valid_from)',
 87      'day(valid_from)',
 88      'php(\'idate\', \'d\', php(\'strtotime\', valid_from))',
 89      create(new \rdbms\Criteria())->setProjection(SQLFunctions::day(Job::column('valid_from')))
 90    );
 91  }
 92    
 93  #[@test]
 94  function prepareProjectionTest() {
 95    $this->assertEquals(
 96      '- datepart(hour, valid_from) -',
 97      $this->syconn->prepare('- %s -', SQLFunctions::datepart('hour', Job::column('valid_from')))
 98    );
 99    $this->assertEquals(
100      '- extract(hour from valid_from) -',
101      $this->myconn->prepare('- %s -', SQLFunctions::datepart('hour', Job::column('valid_from')))
102    );
103    $this->assertEquals(
104      '- datepart(hour, valid_from) -',
105      $this->pgconn->prepare('- %s -', SQLFunctions::datepart('hour', Job::column('valid_from')))
106    );
107    $this->assertEquals(
108      '- php(\'idate\', "H", php(\'strtotime\', valid_from)) -',
109      $this->sqconn->prepare('- %s -', SQLFunctions::datepart('hour', Job::column('valid_from')))
110    );
111  }
112    
113  #[@test]
114  function stringFunctionTest() {
115    $this->assertProjection(
116      'ascii(\'a\') as `asciiTest`',
117      'ascii(\'a\') as \'asciiTest\'',
118      'ascii(\'a\') as "asciiTest"',
119      'php(\'ord\', \'a\') as \'asciiTest\'',
120      create(new \rdbms\Criteria())->setProjection(SQLFunctions::ascii('a'), 'asciiTest')
121    );
122    $this->assertProjection(
123      'char(97)',
124      'char(97)',
125      'char(97)',
126      'php(\'chr\', 97)',
127      create(new \rdbms\Criteria())->setProjection(SQLFunctions::char('97'))
128    );
129    $this->assertProjection(
130      'length(\'aaaaaaa\')',
131      'len(\'aaaaaaa\')',
132      'len(\'aaaaaaa\')',
133      'php(\'strlen\', \'aaaaaaa\')',
134      create(new \rdbms\Criteria())->setProjection(SQLFunctions::len('aaaaaaa'))
135    );
136    $this->assertProjection(
137      'reverse(\'abcdefg\')',
138      'reverse(\'abcdefg\')',
139      'reverse(\'abcdefg\')',
140      'php(\'strrev\', \'abcdefg\')',
141      create(new \rdbms\Criteria())->setProjection(SQLFunctions::reverse('abcdefg'))
142    );
143    $this->assertProjection(
144      'space(4)',
145      'space(4)',
146      'space(4)',
147      'php(\'str_repeat\', \' \', 4)',
148      create(new \rdbms\Criteria())->setProjection(SQLFunctions::space(4))
149    );
150    $this->assertProjection(
151      'soundex(\'kawabanga\')',
152      'soundex(\'kawabanga\')',
153      'soundex(\'kawabanga\')',
154      'php(\'soundex\', \'kawabanga\')',
155      create(new \rdbms\Criteria())->setProjection(SQLFunctions::soundex('kawabanga'))
156    );
157  }
158    
159  #[@test]
160  function concatStringTest() {
161    $this->assertProjection(
162      'concat(\'aa\', cast(sysdate() as char), \'cc\') as `concatTest`',
163      '(\'aa\' + convert(varchar, getdate()) + \'cc\') as \'concatTest\'',
164      '(\'aa\' || str(getdate()) || \'cc\') as "concatTest"',
165      '\'aa\' || php(\'strval\', php(\'date\', \'Y-m-d H:i:s\', php(\'time\'))) || \'cc\' as \'concatTest\'',
166      create(new \rdbms\Criteria())->setProjection(SQLFunctions::concat('aa', SQLFunctions::str(SQLFunctions::getdate()), 'cc'), 'concatTest')
167    );
168  }
169    
170  #[@test]
171  function dateFunctionTest() {
172    $date= new Date();
173    $myDate= $date->toString($this->myconn->getFormatter()->dialect->dateFormat);
174    $syDate= $date->toString($this->syconn->getFormatter()->dialect->dateFormat);
175    $pgDate= $date->toString($this->pgconn->getFormatter()->dialect->dateFormat);
176    $sqDate= $date->toString($this->sqconn->getFormatter()->dialect->dateFormat);
177    $this->assertProjection(
178      'cast(sysdate() as char)',
179      'convert(varchar, getdate())',
180      'str(getdate())',
181      'php(\'strval\', php(\'date\', \'Y-m-d H:i:s\', php(\'time\')))',
182      create(new \rdbms\Criteria())->setProjection(SQLFunctions::str(SQLFunctions::getdate()))
183    );
184    $this->assertProjection(
185      'cast(timestampadd(month, -4, sysdate()) as char)',
186      'convert(varchar, dateadd(month, -4, getdate()))',
187      'str(dateadd(month, -4, getdate()))',
188      'php(\'strval\', dateadd("m", -4, php(\'date\', \'Y-m-d H:i:s\', php(\'time\'))))',
189      create(new \rdbms\Criteria())->setProjection(SQLFunctions::str(SQLFunctions::dateadd('month', '-4', SQLFunctions::getdate())))
190    );
191    $this->assertProjection(
192      'timestampdiff(second, timestampadd(day, -4, sysdate()), sysdate())',
193      'datediff(second, dateadd(day, -4, getdate()), getdate())',
194      'datediff(second, dateadd(day, -4, getdate()), getdate())',
195      'datediff_not_implemented',
196      create(new \rdbms\Criteria())->setProjection(SQLFunctions::datediff('second', SQLFunctions::dateadd('day', '-4', SQLFunctions::getdate()), SQLFunctions::getdate()))
197    );
198    $this->assertProjection(
199      'cast(extract(hour from sysdate()) as char)',
200      'datename(hour, getdate())',
201      'datename(hour, getdate())',
202      'php(\'strval\', php(\'idate\', "H", php(\'strtotime\', php(\'date\', \'Y-m-d H:i:s\', php(\'time\')))))',
203      create(new \rdbms\Criteria())->setProjection(SQLFunctions::datename('hour', SQLFunctions::getdate()))
204    );
205    $this->assertProjection(
206      'extract(hour from \''.$myDate.'\')',
207      'datepart(hour, \''.$syDate.'\')',
208      'datepart(hour, \''.$pgDate.'\')',
209      'php(\'idate\', "H", php(\'strtotime\', \''.$sqDate.'\'))',
210      create(new \rdbms\Criteria())->setProjection(SQLFunctions::datepart('hour', $date))
211    );
212  }
213    
214  #[@test]
215  function mathArithFunctionTest() {
216    $this->assertProjection(
217      'abs(-6)',
218      'abs(-6)',
219      'abs(-6)',
220      'php(\'abs\', -6)',
221      create(new \rdbms\Criteria())->setProjection(SQLFunctions::abs(-6))
222    );
223    $this->assertProjection(
224      'ceil(5.1)',
225      'ceiling(5.1)',
226      'ceil(5.1)',
227      'php(\'ceil\', 5.1)',
228      create(new \rdbms\Criteria())->setProjection(SQLFunctions::ceil(5.1))
229    );
230    $this->assertProjection(
231      'floor(5.7)',
232      'floor(5.7)',
233      'floor(5.7)',
234      'php(\'floor\', 5.7)',
235      create(new \rdbms\Criteria())->setProjection(SQLFunctions::floor(5.7))
236    );
237    $this->assertProjection(
238      'exp(log(1))',
239      'exp(log(1))',
240      'exp(log(1))',
241      'php(\'exp\', php(\'log\', 1))',
242      create(new \rdbms\Criteria())->setProjection(SQLFunctions::exp(SQLFunctions::log(1)))
243    );
244    $this->assertProjection(
245      'log10(power(10, 5))',
246      'log10(power(10, 5))',
247      'log10(power(10, 5))',
248      'php(\'log10\', php(\'pow\', 10, 5))',
249      create(new \rdbms\Criteria())->setProjection(SQLFunctions::log10(SQLFunctions::power(10, 5)))
250    );
251    $this->assertProjection(
252      'power(10, log10(5))',
253      'power(10, log10(5))',
254      'power(10, log10(5))',
255      'php(\'pow\', 10, php(\'log10\', 5))',
256      create(new \rdbms\Criteria())->setProjection(SQLFunctions::power(10, SQLFunctions::log10(5)))
257    );
258    $this->assertProjection(
259      'round(1.5, 0) as `roundtest1`, round(1.49, 0) as `roundtest2`, round(1.49, 1) as `roundtest3`',
260      'round(1.5, 0) as \'roundtest1\', round(1.49, 0) as \'roundtest2\', round(1.49, 1) as \'roundtest3\'',
261      'round(1.5, 0) as "roundtest1", round(1.49, 0) as "roundtest2", round(1.49, 1) as "roundtest3"',
262      'php(\'round\', 1.5, 0) as \'roundtest1\', php(\'round\', 1.49, 0) as \'roundtest2\', php(\'round\', 1.49, 1) as \'roundtest3\'',
263      create(new \rdbms\Criteria())->setProjection(\rdbms\criterion\Projections::ProjectionList()
264        ->add(SQLFunctions::round(1.50),    'roundtest1')
265        ->add(SQLFunctions::round(1.49),    'roundtest2')
266        ->add(SQLFunctions::round(1.49, 1), 'roundtest3')
267      )
268    );
269    $this->assertProjection(
270      'sign(-7) as `signTest1`, sign(0) as `signTest2`, sign(4) as `signTest3`',
271      'convert(int, sign(-7)) as \'signTest1\', convert(int, sign(0)) as \'signTest2\', convert(int, sign(4)) as \'signTest3\'',
272      'sign(-7) as "signTest1", sign(0) as "signTest2", sign(4) as "signTest3"',
273      'sign(-7) as \'signTest1\', sign(0) as \'signTest2\', sign(4) as \'signTest3\'',
274      create(new \rdbms\Criteria())->setProjection(\rdbms\criterion\Projections::ProjectionList()
275        ->add(SQLFunctions::sign(-7), 'signTest1')
276        ->add(SQLFunctions::sign(0),  'signTest2')
277        ->add(SQLFunctions::sign(4),  'signTest3')
278      )
279    );
280  }
281    
282  #[@test]
283  function mathTrigFunctionTest() {
284    $this->assertProjection(
285      'cot(45)',
286      'cot(45)',
287      'cot(45)',
288      'php(\'tan\', php(\'pi\') / 2 - 45)',
289      create(new \rdbms\Criteria())->setProjection(SQLFunctions::cot(45))
290    );
291    $this->assertProjection(
292      'pi()',
293      'pi()',
294      'pi()',
295      'php(\'pi\')',
296      create(new \rdbms\Criteria())->setProjection(SQLFunctions::pi())
297    );
298    $this->assertProjection(
299      'acos(cos(0.125))',
300      'acos(cos(0.125))',
301      'acos(cos(0.125))',
302      'php(\'acos\', php(\'cos\', 0.125))',
303      create(new \rdbms\Criteria())->setProjection(SQLFunctions::acos(SQLFunctions::cos(0.125)))
304    );
305    $this->assertProjection(
306      'asin(sin(0.125))',
307      'asin(sin(0.125))',
308      'asin(sin(0.125))',
309      'php(\'asin\', php(\'sin\', 0.125))',
310      create(new \rdbms\Criteria())->setProjection(SQLFunctions::asin(SQLFunctions::sin(0.125)))
311    );
312    $this->assertProjection(
313      'atan(tan(0.125))',
314      'atan(tan(0.125))',
315      'atan(tan(0.125))',
316      'php(\'atan\', php(\'tan\', 0.125))',
317      create(new \rdbms\Criteria())->setProjection(SQLFunctions::atan(SQLFunctions::tan(0.125)))
318    );
319    $this->assertProjection(
320      'atan2(tan(0.125), 0)',
321      'atn2(tan(0.125), 0)',
322      'atan2(tan(0.125), 0)',
323      'php(\'atan2\', php(\'tan\', 0.125), 0)',
324      create(new \rdbms\Criteria())->setProjection(SQLFunctions::atan(SQLFunctions::tan(0.125), 0))
325    );
326    $this->assertProjection(
327      'degrees(pi())',
328      'convert(float, degrees(pi()))',
329      'degrees(pi())',
330      'php(\'rad2deg\', php(\'pi\'))',
331      create(new \rdbms\Criteria())->setProjection(SQLFunctions::degrees(SQLFunctions::pi()))
332    );
333    $this->assertProjection(
334      'radians(degrees(90))',
335      'convert(float, radians(convert(float, degrees(90))))',
336      'radians(degrees(90))',
337      'php(\'deg2rad\', php(\'rad2deg\', 90))',
338      create(new \rdbms\Criteria())->setProjection(SQLFunctions::radians(SQLFunctions::degrees(90)))
339    );
340    $this->assertProjection(
341      'radians(degrees(90))',
342      'convert(float, radians(convert(float, degrees(90))))',
343      'radians(degrees(90))',
344      'php(\'deg2rad\', php(\'rad2deg\', 90))',
345      create(new \rdbms\Criteria())->setProjection(SQLFunctions::radians(SQLFunctions::degrees(90)))
346    );
347  }
348    
349  #[@test]
350  function randFunctionTest() {
351    $this->assertProjection(
352      'rand()',
353      'rand()',
354      'random()',
355      'php(\'rand\')',
356      create(new \rdbms\Criteria())->setProjection(SQLFunctions::rand())
357    );
358  }
359    
360  #[@test]
361  function castFunctionTest() {
362    $this->assertProjection(
363      'cast(\'345\' as decimal)',
364      'convert(decimal, \'345\')',
365      'cast(\'345\' as decimal)',
366      'cast(\'345\' as decimal)',
367      create(new \rdbms\Criteria())->setProjection(SQLFunctions::cast('345', 'decimal'))
368    );
369    $this->assertProjection(
370      'cast(job_id as char)',
371      'convert(char, job_id)',
372      'cast(job_id as char)',
373      'cast(job_id as char)',
374      create(new \rdbms\Criteria())->setProjection(SQLFunctions::cast(Job::column('job_id'), 'char'))
375    );
376  }
377    
378  #[@test]
379  function restrictionTest() {
380    $this->assertSQL(
381      'where job_id = ceil(asin(sin(0.125)))',
382      'where job_id = ceiling(asin(sin(0.125)))',
383      'where job_id = ceil(asin(sin(0.125)))',
384      'where job_id = php(\'ceil\', php(\'asin\', php(\'sin\', 0.125)))',
385      create(new \rdbms\Criteria())->add(Restrictions::equal('job_id', SQLFunctions::ceil(SQLFunctions::asin(SQLFunctions::sin(0.125)))))
386    );
387    $this->assertSQL(
388      'where job_id = ceil(asin(sin(0.125)))',
389      'where job_id = ceiling(asin(sin(0.125)))',
390      'where job_id = ceil(asin(sin(0.125)))',
391      'where job_id = php(\'ceil\', php(\'asin\', php(\'sin\', 0.125)))',
392      create(new \rdbms\Criteria())->add(Restrictions::equal(Job::column('job_id'), SQLFunctions::ceil(SQLFunctions::asin(SQLFunctions::sin(0.125)))))
393    );
394  }
395}