All Downloads are FREE. Search and download functionalities are using the official Maven repository.

core_relational_sybaseiq.relational.sqlQueryToString.tests.testSybaseIQToSQLString.pure Maven / Gradle / Ivy

The newest version!
// Copyright 2023 Goldman Sachs
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//      http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

import meta::relational::tests::functions::sqlstring::*;
import meta::pure::mapping::*;
import meta::relational::functions::asserts::*;
import meta::relational::mapping::*;
import meta::relational::tests::*;
import meta::external::store::relational::tests::*;
import meta::relational::tests::model::simple::*;
import meta::pure::profiles::*;
import meta::relational::functions::sqlstring::*;
import meta::relational::runtime::*;
import meta::external::store::relational::runtime::*;

function meta::relational::tests::functions::sqlstring::sybaseIQ::testCasesForDocGeneration():TestCase[*]
{
   [
      ^TestCase(
         id ='testToSqlGenerationForBooleanInProject_SybaseIQ_StartsWith',
         query = |Person.all()->project([
            a | $a.firstName->startsWith('tri')
         ],
                                        ['a']),
         mapping = simpleRelationalMapping,
         dbType = DatabaseType.SybaseIQ,
         expectedSql = 'select case when ("root".FIRSTNAME like \'tri%\') then \'true\' else \'false\' end as "a" from personTable as "root"',
         generateUsageFor = [meta::pure::functions::string::startsWith_String_1__String_1__Boolean_1_]
      ),

      ^TestCase(
         id ='testToSQLStringJoinStrings_SybaseIQ',
         query = {|Firm.all()->groupBy([f|$f.legalName],
                                     agg(x|$x.employees.firstName,y|$y->joinStrings('*')),
                                     ['legalName', 'employeesFirstName']
                                  )},
         mapping = meta::relational::tests::simpleRelationalMapping,
         dbType = meta::relational::runtime::DatabaseType.SybaseIQ,
         expectedSql = 'select "root".LEGALNAME as "legalName", list("personTable_d#4_d_m1".FIRSTNAME,\'*\') as "employeesFirstName" from firmTable as "root" left outer join personTable as "personTable_d#4_d_m1" on ("root".ID = "personTable_d#4_d_m1".FIRMID) group by "legalName"',
         generateUsageFor = [meta::pure::functions::string::joinStrings_String_MANY__String_1__String_1_]
      )
   ]
}

function meta::relational::tests::functions::sqlstring::sybaseIQ::runTestCaseById(testCaseId: String[1]): Boolean[1]
{
   let filtered = meta::relational::tests::functions::sqlstring::sybaseIQ::testCasesForDocGeneration()->filter(c|$c.id==$testCaseId);
   assert($filtered->size()==1, 'Number of test cases found is not 1.');
   let testCase = $filtered->toOne();

   let result = toSQLString($testCase.query, $testCase.mapping, $testCase.dbType, meta::relational::extension::relationalExtensions());
   assertEquals($testCase.expectedSql, $result, '\nSQL not as expected for \'%s\'\n\nexpected: %s\nactual:   %s', [$testCase.id, $testCase.expectedSql, $result]);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSQLStringWithConditionalProjectSybaseIQ():Boolean[1]
{
   let s = toSQLString(|Person.all()->project(p|$p.firstName == 'John', 'isJohn'), meta::relational::tests::simpleRelationalMapping, meta::relational::runtime::DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select case when ("root".FIRSTNAME = \'John\') then \'true\' else \'false\' end as "isJohn" from personTable as "root"', $s);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSQLStringJoinStrings():Boolean[1]
{
    meta::relational::tests::functions::sqlstring::sybaseIQ::runTestCaseById('testToSQLStringJoinStrings_SybaseIQ');
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSQLStringJoinStringsSimpleConcat():Boolean[1]
{
   let fn = {|Person.all()->project([p | $p.firstName + '_' + $p.lastName], ['firstName_lastName'])};
   let sybaseSql = toSQLString($fn, meta::relational::tests::simpleRelationalMapping, meta::relational::runtime::DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select "root".FIRSTNAME+\'_\'+"root".LASTNAME as "firstName_lastName" from personTable as "root"', $sybaseSql);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testProcessLiteralForIQ():Boolean[1]
{
   let result = toSQLString(|Person.all()->project([
                                                      a | 'String',
                                                      b | %2016-03-01,
                                                      c | %2016-03-01T12:18:18.976+0200,
                                                      d | 1,
                                                      e | 1.1
                                                   ],
                                                   ['a','b','c','d', 'e'])->take(0),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   print($result);
   assertEquals('select top 0 \'String\' as "a", convert(DATE, \'2016-03-01\', 121) as "b", convert(DATETIME, \'2016-03-01 10:18:18.976\', 121) as "c", 1 as "d", 1.1 as "e" from personTable as "root"', $result);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSQLStringWithLength():Boolean[1]
{
   [DatabaseType.SybaseIQ]->map(db|
      let s = toSQLString(|Person.all()->project(p|length($p.firstName), 'nameLength'), simpleRelationalMapping, $db, meta::relational::extension::relationalExtensions());
      assertEquals('select char_length("root".FIRSTNAME) as "nameLength" from personTable as "root"', $s);
   );
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSQLStringWithPosition():Boolean[1]
{
   [DatabaseType.SybaseIQ]->map(db|
      let s = toSQLString(
              |meta::relational::tests::mapping::propertyfunc::model::domain::Person.all()->project(p|$p.firstName, 'firstName'),
               meta::relational::tests::mapping::propertyfunc::model::mapping::PropertyfuncMapping, $db, meta::relational::extension::relationalExtensions());

      assertEquals('select substring("root".FULLNAME, 0, charindex(\',\', "root".FULLNAME)-1) as "firstName" from personTable as "root"', $s);
   );
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSQLStringWithStdDevSample():Boolean[1]
{
   [DatabaseType.SybaseIQ]->map(db|
      let s = toSQLString(
              |meta::relational::tests::mapping::sqlFunction::model::domain::SqlFunctionDemo.all()->project(p|$p.float1StdDevSample, 'stdDevSample'),
               meta::relational::tests::mapping::sqlFunction::model::mapping::testMapping, $db, meta::relational::extension::relationalExtensions());

      assertEquals('select stddev_samp("root".int1) as "stdDevSample" from dataTable as "root"', $s);
   )->distinct() == [true];
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSQLStringWithStdDevPopulation():Boolean[1]
{
   [DatabaseType.SybaseIQ]->map(db|
      let s = toSQLString(
              |meta::relational::tests::mapping::sqlFunction::model::domain::SqlFunctionDemo.all()->project(p|$p.float1StdDevPopulation, 'stdDevPopulation'),
               meta::relational::tests::mapping::sqlFunction::model::mapping::testMapping, $db, meta::relational::extension::relationalExtensions());

      assertEquals('select stddev_pop("root".int1) as "stdDevPopulation" from dataTable as "root"', $s);
   )->distinct() == [true];
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testGenerateDateDiffExpressionForSybaseIQForDifferenceInYears():Boolean[1]
{
   let result = toSQLString(|Trade.all()->project([
                                                     t | dateDiff($t.settlementDateTime, now(), DurationUnit.YEARS)
                                                  ],
                                                  ['DiffYears']),
                            simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select datediff(yy,"root".settlementDateTime,now()) as "DiffYears" from tradeTable as "root"', $result);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testGenerateDateDiffExpressionForSybaseIQForDifferenceInMonths():Boolean[1]
{
   let result = toSQLString(|Trade.all()->project([
                                                     t | dateDiff($t.settlementDateTime, now(), DurationUnit.MONTHS)
                                                  ],
                                                  ['DiffMonths']),
                            simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select datediff(mm,"root".settlementDateTime,now()) as "DiffMonths" from tradeTable as "root"', $result);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testGenerateDateDiffExpressionForSybaseIQForDifferenceInWeeks():Boolean[1]
{
   let result = toSQLString(|Trade.all()->project([
                                                     t | dateDiff($t.settlementDateTime, now(), DurationUnit.WEEKS)
                                                  ],
                                                  ['DiffWeeks']),
                            simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select datediff(wk,"root".settlementDateTime,now()) as "DiffWeeks" from tradeTable as "root"', $result);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSqlGenerationFirstDayOfTimePeriod():Boolean[1]
{
    let result = toSQLString(
     |Trade.all()
        ->project([
          col(t|$t.date->firstHourOfDay(), 'day'),
          col(t|$t.date->firstMinuteOfHour(), 'hour'),
          col(t|$t.date->firstSecondOfMinute(), 'minute'),
          col(t|$t.date->firstMillisecondOfSecond(), 'second')
        ]),
        simpleRelationalMapping,
        DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());

    assertEquals('select datetime(date("root".tradeDate)) as "day", dateadd(hour, datepart(hour, "root".tradeDate), date("root".tradeDate)) as "hour", dateadd(minute, datepart(minute, "root".tradeDate), dateadd(hour, datepart(hour, "root".tradeDate), date("root".tradeDate))) as "minute", dateadd(microsecond, -(datepart(microsecond, "root".tradeDate)), "root".tradeDate) as "second" from tradeTable as "root"', $result);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testGenerateDateDiffExpressionForSybaseIQForDifferenceInDays():Boolean[1]
{
   let result = toSQLString(|Trade.all()->project([
                                                     t | dateDiff($t.settlementDateTime, now(), DurationUnit.DAYS)
                                                  ],
                                                  ['DiffDays']),
                            simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select datediff(dd,"root".settlementDateTime,now()) as "DiffDays" from tradeTable as "root"', $result);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testGenerateDateDiffExpressionForSybaseIQForDifferenceInHours():Boolean[1]
{
   let result = toSQLString(|Trade.all()->project([
                                                     t | dateDiff($t.settlementDateTime, now(), DurationUnit.HOURS)
                                                  ],
                                                  ['DiffHours']),
                            simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select datediff(hh,"root".settlementDateTime,now()) as "DiffHours" from tradeTable as "root"', $result);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testGenerateDateDiffExpressionForSybaseIQForDifferenceInMinutes():Boolean[1]
{
   let result = toSQLString(|Trade.all()->project([
                                                     t | dateDiff($t.settlementDateTime, now(), DurationUnit.MINUTES)
                                                  ],
                                                  ['DiffMinutes']),
                            simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select datediff(mi,"root".settlementDateTime,now()) as "DiffMinutes" from tradeTable as "root"', $result);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testGenerateDateDiffExpressionForSybaseIQForDifferenceInSeconds():Boolean[1]
{
   let result = toSQLString(|Trade.all()->project([
                                                     t | dateDiff($t.settlementDateTime, now(), DurationUnit.SECONDS)
                                                  ],
                                                  ['DiffSeconds']),
                            simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select datediff(ss,"root".settlementDateTime,now()) as "DiffSeconds" from tradeTable as "root"', $result);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testGenerateDateDiffExpressionForSybaseIQForDifferenceInMilliseconds():Boolean[1]
{
   let result = toSQLString(|Trade.all()->project([
                                                     t | dateDiff($t.settlementDateTime, now(), DurationUnit.MILLISECONDS)
                                                  ],
                                                  ['DiffMilliseconds']),
                            simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select datediff(ms,"root".settlementDateTime,now()) as "DiffMilliseconds" from tradeTable as "root"', $result);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testDayOfYear():Boolean[1]
{
   let expected = [
      pair(DatabaseType.SybaseIQ, 'select datepart(DAYOFYEAR,"root".tradeDate) as "doy" from tradeTable as "root"')
   ];

   $expected->map(p|
      let driver = $p.first;
      let expectedSql = $p.second;

      let result = toSQLString(
         |Trade.all()
            ->project(col(t|$t.date->dayOfYear(), 'doy')),
         simpleRelationalMapping,
         $driver, meta::relational::extension::relationalExtensions());

      assertEquals($expectedSql, $result, '\nSQL not as expected for %s\n\nexpected: %s\nactual:   %s', [$driver, $expectedSql, $result]);
   )->distinct() == [true];
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testTrim():Boolean[1]
{
  let common = 'select ltrim("root".FIRSTNAME) as "ltrim", trim("root".FIRSTNAME) as "trim", rtrim("root".FIRSTNAME) as "rtrim" from personTable as "root"';

   let expected = [
    pair(DatabaseType.SybaseIQ, $common)
  ];

   $expected->map(p|
      let driver = $p.first;
      let expectedSql = $p.second;

      let result = toSQLString(
        |Person.all()->project([
          a | $a.firstName->ltrim(),
          a | $a.firstName->trim(),
          a | $a.firstName->rtrim()
         ],
         ['ltrim', 'trim', 'rtrim']),
         simpleRelationalMapping,
         $driver, meta::relational::extension::relationalExtensions());

      assertEquals($expectedSql, $result, '\nSQL not as expected for %s\n\nexpected: %s\nactual:   %s', [$driver, $expectedSql, $result]);
   )->distinct() == [true];
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testCbrt():Boolean[1]
{
  let common = 'select cbrt("root".quantity) as "cbrt" from tradeTable as "root"';

   let expected = [
    pair(DatabaseType.SybaseIQ, $common)
   ];

   $expected->map(p|
      let driver = $p.first;
      let expectedSql = $p.second;

      let result = toSQLString(
        |Trade.all()->project([
          a | $a.quantity->cbrt()
         ],
         ['cbrt']),
         simpleRelationalMapping,
         $driver, meta::relational::extension::relationalExtensions());

      assertEquals($expectedSql, $result, '\nSQL not as expected for %s\n\nexpected: %s\nactual:   %s', [$driver, $expectedSql, $result]);
   )->distinct() == [true];
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSqlGenerationForBooleanInProject_SybaseIQ_StartsWith():Boolean[1]
{
    meta::relational::tests::functions::sqlstring::sybaseIQ::runTestCaseById('testToSqlGenerationForBooleanInProject_SybaseIQ_StartsWith');
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSqlGenerationForBooleanInFilter_SybaseIQ():Boolean[1]
{
   let result = toSQLString(|Interaction.all()->filter(a | $a.active)->project([i | $i.id],['id']),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select "root".ID as "id" from interactionTable as "root" where case when "root"."active" = \'Y\' then \'true\' else \'false\' end = \'true\'', $result);
}


function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSqlGenerationForBooleanInFilterWithAnd_SybaseIQ():Boolean[1]
{
   let result = toSQLString(|Interaction.all()->filter(a | $a.id == 1 && $a.active)->project([i | $i.id],['id']),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select "root".ID as "id" from interactionTable as "root" where ("root".ID = 1 and case when "root"."active" = \'Y\' then \'true\' else \'false\' end = \'true\')', $result);
}


function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSqlGenerationForBooleanInFilterWithAnd_WithDistinct_SybaseIQ():Boolean[1]
{
   let result = toSQLString(|Interaction.all()->filter(a | $a.id == 1 && $a.active)->project([i | $i.id],['id'])->distinct(),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select distinct "root".ID as "id" from interactionTable as "root" where ("root".ID = 1 and case when "root"."active" = \'Y\' then \'true\' else \'false\' end = \'true\')', $result);
}


function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSqlGenerationForBooleanInFilterWithAndIsNull_SybaseIQ():Boolean[1]
{
   let result = toSQLString(|Interaction.all()->filter(a | $a.id->isEmpty() && $a.active)->project([i | $i.id],['id']),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select "root".ID as "id" from interactionTable as "root" where ("root".ID is null and case when "root"."active" = \'Y\' then \'true\' else \'false\' end = \'true\')', $result);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSqlGenerationForBooleanInFilterWithAndNotEqual_SybaseIQ():Boolean[1]
{
   let result = toSQLString(|Interaction.all()->filter(a | $a.id != 1 && $a.active)->project([i | $i.id],['id']),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select "root".ID as "id" from interactionTable as "root" where (("root".ID <> 1 OR "root".ID is null) and case when "root"."active" = \'Y\' then \'true\' else \'false\' end = \'true\')', $result);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSqlGenerationForConstanNumInFilterWithNotEqual_SybaseIQ():Boolean[1]
{
   let result = toSQLString(|Synonym.all()->filter(s | $s.type != 'ISIN')->project([s | $s.name],['name']),
                              simpleRelationalMappingWithEnumConstant, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select "root".NAME as "name" from productSchema.synonymTable as "root" where (\'CUSIP\' <> \'ISIN\')', $result);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSqlGenerationForBooleanInProject_SybaseIQ_IsEmpty():Boolean[1]
{
   let result = toSQLString(|Person.all()->project([
                                                      a | $a.firstName->isEmpty()
                                                   ],
                                                   ['a']),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select case when ("root".FIRSTNAME is null) then \'true\' else \'false\' end as "a" from personTable as "root"', $result);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSqlGenerationForBooleanInProject_SybaseIQ_And():Boolean[1]
{
   let result = toSQLString(|Person.all()->project([
                                                      a | $a.firstName == 'A' && $a.lastName == 'B'
                                                   ],
                                                   ['a']),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select case when (("root".FIRSTNAME = \'A\' and "root".LASTNAME = \'B\')) then \'true\' else \'false\' end as "a" from personTable as "root"', $result);
}


function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSqlGenerationForBooleanInProject_SybaseIQ_If():Boolean[1]
{
   let result = toSQLString(|Person.all()->project([
                                                      a | if ($a.firstName == 'A', | true, | false)
                                                   ],
                                                   ['a']),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select case when "root".FIRSTNAME = \'A\' then \'true\' else \'false\' end as "a" from personTable as "root"', $result);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSqlGenerationForBooleanInFilter_SybaseIQ_If():Boolean[1]
{
   let result = toSQLString(|Person.all()->filter(a | if ($a.firstName == 'A', | true, | false)),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select "root".ID as "pk_0", "root".FIRSTNAME as "firstName", "root".AGE as "age", "root".LASTNAME as "lastName" from personTable as "root" where case when "root".FIRSTNAME = \'A\' then \'true\' else \'false\' end = \'true\'', $result);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSqlGenerationForBooleanInProject_SybaseIQ_NestedIf():Boolean[1]
{
   let result = toSQLString(|Person.all()->project([
                                                      a | if ( if ($a.firstName == 'B', | true, | false), | true, | false)
                                                   ],
                                                   ['a']),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select case when case when "root".FIRSTNAME = \'B\' then \'true\' else \'false\' end = \'true\' then \'true\' else \'false\' end as "a" from personTable as "root"', $result);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSqlGenerationForBooleanInProject_SybaseIQ_Or():Boolean[1]
{
   let result = toSQLString(|Person.all()->project([
                                                      a | true || false
                                                   ],
                                                   ['a']),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select case when ((\'true\' = \'true\' or \'false\' = \'true\')) then \'true\' else \'false\' end as "a" from personTable as "root"', $result);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testSqlGenerationForDatePartForSybaseIQ():Boolean[1]
{
   let result = toSQLString(|Location.all()->project([
                                                      a | $a.censusdate->toOne()->datePart()
                                                   ],
                                                   ['a']),
                              simpleRelationalMappingInc, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select date("root"."date") as "a" from locationTable as "root"', $result);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSqlGenerationForProjectLambdaWithSquareBrackets_SybaseIQ():Boolean[1]
{
   let result = toSQLString(
      |Person.all()->project([a|$a.firstName->startsWith('Dummy [With Sq Brackets]')], ['a']),
      simpleRelationalMapping,
      DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());

   assertEquals('select case when ("root".FIRSTNAME like \'Dummy \\[With Sq Brackets]%\' escape \'\\\') then \'true\' else \'false\' end as "a" from personTable as "root"', $result);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSqlGenerationForFilterWithSquareBrackets_SybaseIQ():Boolean[1]
{
   let result = toSQLString(
      |Person.all()
         ->project([#/Person/firstName!name#])
         ->filter(a|$a.getString('name')->startsWith('Dummy [With Sq Brackets]')),
      simpleRelationalMapping,
      DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());

   assertEquals('select "root".FIRSTNAME as "name" from personTable as "root" where "root".FIRSTNAME like \'Dummy \\[With Sq Brackets]%\' escape \'\\\'', $result);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSqlGenerationFirstDayOfMonth():Boolean[1]
{
   let expected = [
      pair(DatabaseType.SybaseIQ, 'select dateadd(DAY, -(day("root".tradeDate) - 1), "root".tradeDate) as "date" from tradeTable as "root"')
   ];

   $expected->map(p|
      let driver = $p.first;
      let expectedSql = $p.second;

      let result = toSQLString(
         |Trade.all()
            ->project(col(t|$t.date->firstDayOfMonth(), 'date')),
         simpleRelationalMapping,
         $driver, meta::relational::extension::relationalExtensions());

      assertEquals($expectedSql, $result, '\nSQL not as expected for %s\n\nexpected: %s\nactual:   %s', [$driver, $expectedSql, $result]);
   )->distinct() == [true];
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSqlGenerationFirstDayOfYear():Boolean[1]
{
   let expected = [
      pair(DatabaseType.SybaseIQ, 'select dateadd(DAY, -(datepart(dayofyear, "root".tradeDate) - 1), "root".tradeDate) as "date" from tradeTable as "root"')
   ];

   $expected->map(p|
      let driver = $p.first;
      let expectedSql = $p.second;

      let result = toSQLString(
         |Trade.all()
            ->project(col(t|$t.date->firstDayOfYear(), 'date')),
         simpleRelationalMapping,
         $driver, meta::relational::extension::relationalExtensions());

      assertEquals($expectedSql, $result, '\nSQL not as expected for %s\n\nexpected: %s\nactual:   %s', [$driver, $expectedSql, $result]);
   )->distinct() == [true];
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSqlGenerationFirstDayOfThisYear():Boolean[1]
{
   let expected = [
      pair(DatabaseType.SybaseIQ, 'select dateadd(DAY, -(datepart(dayofyear, today()) - 1), today()) as "date" from tradeTable as "root"')
   ];

   $expected->map(p|
      let driver = $p.first;
      let expectedSql = $p.second;

      let result = toSQLString(
         |Trade.all()
            ->project(col(t|firstDayOfThisYear(), 'date')),
         simpleRelationalMapping,
         $driver, meta::relational::extension::relationalExtensions());

      assertEquals($expectedSql, $result, '\nSQL not as expected for %s\n\nexpected: %s\nactual:   %s', [$driver, $expectedSql, $result]);
   )->distinct() == [true];
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSqlGenerationFirstDayOfQuarter_SybaseIQ():Boolean[1]
{
   testToSqlGenerationFirstDayOfQuarter(DatabaseType.SybaseIQ, 'select dateadd(QUARTER, quarter("root".tradeDate) - 1, dateadd(DAY, -(datepart(dayofyear, "root".tradeDate) - 1), "root".tradeDate)) as "date" from tradeTable as "root"');
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSqlGenerationFirstDayOfWeek():Boolean[1]
{
   let expected = [
      pair(DatabaseType.SybaseIQ, 'select dateadd(DAY, -(mod(datepart(weekday, "root".tradeDate)+5, 7)), "root".tradeDate) as "date" from tradeTable as "root"')
   ];

   $expected->map(p|
      let driver = $p.first;
      let expectedSql = $p.second;

      let result = toSQLString(
         |Trade.all()
            ->project(col(t|$t.date->firstDayOfWeek(), 'date')),
         simpleRelationalMapping,
         $driver, meta::relational::extension::relationalExtensions());

      assertEquals($expectedSql, $result, '\nSQL not as expected for %s\n\nexpected: %s\nactual:   %s', [$driver, $expectedSql, $result]);
   )->distinct() == [true];
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testSqlGenerationForBooleanProject_IQ():Boolean[1]
{

   let result1a = toSQLString(|Interaction.all()->project(col(p|true, 'active')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select \'true\' as "active" from interactionTable as "root"', $result1a);

   let result1b = toSQLString(|Interaction.all()->project(col(p|!true, 'active')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select case when (not \'true\' = \'true\') then \'true\' else \'false\' end as "active" from interactionTable as "root"', $result1b);

   let result1c = toSQLString(|Interaction.all()->project(col(p|false, 'active')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select \'false\' as "active" from interactionTable as "root"', $result1c);

   let result1d = toSQLString(|Interaction.all()->project(col(p|!false, 'active')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select case when (not \'false\' = \'true\') then \'true\' else \'false\' end as "active" from interactionTable as "root"', $result1d);

   let result2 = toSQLString(|Interaction.all()->project(col(p|$p.active, 'active')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select case when "root"."active" = \'Y\' then \'true\' else \'false\' end as "active" from interactionTable as "root"', $result2);

   let result3 = toSQLString(|Interaction.all()->project(col(p|$p.active == true, 'active')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select case when (case when "root"."active" = \'Y\' then \'true\' else \'false\' end = \'true\') then \'true\' else \'false\' end as "active" from interactionTable as "root"', $result3);

   let result4 = toSQLString(|Interaction.all()->project(col(p|$p.active && true, 'active')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select case when ((case when "root"."active" = \'Y\' then \'true\' else \'false\' end = \'true\' and \'true\' = \'true\')) then \'true\' else \'false\' end as "active" from interactionTable as "root"', $result4);

   let result5 = toSQLString(|Interaction.all()->project(col(p|if($p.active, |1, |0), 'active')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select case when case when "root"."active" = \'Y\' then \'true\' else \'false\' end = \'true\' then 1 else 0 end as "active" from interactionTable as "root"', $result5);

   let result6 = toSQLString(|Interaction.all()->project(col(p|$p.active->in(true), 'active')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select case when (case when "root"."active" = \'Y\' then \'true\' else \'false\' end = \'true\') then \'true\' else \'false\' end as "active" from interactionTable as "root"', $result6);

   let result7 = toSQLString(|Interaction.all()->project(col(p|$p.target.firstName->isEmpty(), 'active')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select case when ("personTable_d#5_d_m1".FIRSTNAME is null) then \'true\' else \'false\' end as "active" from interactionTable as "root" left outer join personTable as "personTable_d#5_d_m1" on ("root".targetId = "personTable_d#5_d_m1".ID)', $result7);

   let result10 = toSQLString(|Interaction.all()->project(col(p|!$p.target.firstName->isEmpty(), 'active')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select case when (not "personTable_d#6_d_m1".FIRSTNAME is null) then \'true\' else \'false\' end as "active" from interactionTable as "root" left outer join personTable as "personTable_d#6_d_m1" on ("root".targetId = "personTable_d#6_d_m1".ID)', $result10);

   let result13 = toSQLString(|Interaction.all()->project(col(p|($p.target.firstName->isEmpty() || $p.target.firstName->isEmpty()), 'active')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select case when (("personTable_d#2_dy0_d#4_d_m1".FIRSTNAME is null or "personTable_d#2_dy0_d#4_d_m1".FIRSTNAME is null)) then \'true\' else \'false\' end as "active" from interactionTable as "root" left outer join personTable as "personTable_d#2_dy0_d#4_d_m1" on ("root".targetId = "personTable_d#2_dy0_d#4_d_m1".ID)', $result13);

   let result14 = toSQLString(|Interaction.all()->project(col(p|($p.target.firstName->isEmpty() && $p.target.firstName->isEmpty()), 'active')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select case when (("personTable_d#2_dy0_d#4_d_m1".FIRSTNAME is null and "personTable_d#2_dy0_d#4_d_m1".FIRSTNAME is null)) then \'true\' else \'false\' end as "active" from interactionTable as "root" left outer join personTable as "personTable_d#2_dy0_d#4_d_m1" on ("root".targetId = "personTable_d#2_dy0_d#4_d_m1".ID)', $result14);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testSqlGenerationForBooleanProject_IQ2():Boolean[1]
{
   let result8 = toSQLString(|Interaction.all()->project(col(p|$p.target.firstName->isEmpty() == true, 'active')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select case when ("personTable_d#6_d_m1".FIRSTNAME is null) then \'true\' else \'false\' end as "active" from interactionTable as "root" left outer join personTable as "personTable_d#6_d_m1" on ("root".targetId = "personTable_d#6_d_m1".ID)', $result8);

   let result9 = toSQLString(|Interaction.all()->project(col(p|$p.target.firstName->isEmpty() == false, 'active')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select case when (not "personTable_d#6_d_m1".FIRSTNAME is null) then \'true\' else \'false\' end as "active" from interactionTable as "root" left outer join personTable as "personTable_d#6_d_m1" on ("root".targetId = "personTable_d#6_d_m1".ID)', $result9);

   let result11 = toSQLString(|Interaction.all()->project(col(p|$p.target.firstName->isEmpty()->in(false), 'active')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select case when (not "personTable_d#6_d_m1".FIRSTNAME is null) then \'true\' else \'false\' end as "active" from interactionTable as "root" left outer join personTable as "personTable_d#6_d_m1" on ("root".targetId = "personTable_d#6_d_m1".ID)', $result11);

   let result12 = toSQLString(|Interaction.all()->project(col(p|!($p.target.firstName->isEmpty()->in(false)), 'active')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select case when (("personTable_d#7_d_m1".FIRSTNAME is null)) then \'true\' else \'false\' end as "active" from interactionTable as "root" left outer join personTable as "personTable_d#7_d_m1" on ("root".targetId = "personTable_d#7_d_m1".ID)', $result12);
}


function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testSqlGenerationForBooleanFilter_IQ():Boolean[1]
{
   let result1a = toSQLString(|Interaction.all()->filter(p|true)->project(col(p|$p.id, 'id')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select "root".ID as "id" from interactionTable as "root" where \'true\' = \'true\'', $result1a);

   let result1b = toSQLString(|Interaction.all()->filter(p|!true)->project(col(p|$p.id, 'id')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select "root".ID as "id" from interactionTable as "root" where not \'true\' = \'true\'', $result1b);

   let result1c = toSQLString(|Interaction.all()->filter(p|false)->project(col(p|$p.id, 'id')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select "root".ID as "id" from interactionTable as "root" where \'false\' = \'true\'', $result1c);

   let result1d = toSQLString(|Interaction.all()->filter(p|!false)->project(col(p|$p.id, 'id')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select "root".ID as "id" from interactionTable as "root" where not \'false\' = \'true\'', $result1d);

   let result2 = toSQLString(|Interaction.all()->filter(p|$p.active)->project(col(p|$p.id, 'id')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select "root".ID as "id" from interactionTable as "root" where case when "root"."active" = \'Y\' then \'true\' else \'false\' end = \'true\'', $result2);

   let result3 = toSQLString(|Interaction.all()->filter(p|$p.active == true)->project(col(p|$p.id, 'id')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select "root".ID as "id" from interactionTable as "root" where case when "root"."active" = \'Y\' then \'true\' else \'false\' end = \'true\'', $result3);

   let result4 = toSQLString(|Interaction.all()->filter(p|$p.active && true)->project(col(p|$p.id, 'id')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select "root".ID as "id" from interactionTable as "root" where (case when "root"."active" = \'Y\' then \'true\' else \'false\' end = \'true\' and \'true\' = \'true\')', $result4);

   let result5 = toSQLString(|Interaction.all()->filter(p|if($p.active, |1, |0) == 1)->project(col(p|$p.id, 'id')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select "root".ID as "id" from interactionTable as "root" where case when case when "root"."active" = \'Y\' then \'true\' else \'false\' end = \'true\' then 1 else 0 end = 1', $result5);

   let result6 = toSQLString(|Interaction.all()->filter(p|$p.active->in(true))->project(col(p|$p.id, 'id')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select "root".ID as "id" from interactionTable as "root" where case when "root"."active" = \'Y\' then \'true\' else \'false\' end = \'true\'', $result6);

   let result7 = toSQLString(|Interaction.all()->filter(p|$p.target.firstName->isEmpty())->project(col(p|$p.id, 'id')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select "root".ID as "id" from interactionTable as "root" left outer join personTable as "personTable_d#6_d#2_m1" on ("root".targetId = "personTable_d#6_d#2_m1".ID) where "personTable_d#6_d#2_m1".FIRSTNAME is null', $result7);

   let result10 = toSQLString(|Interaction.all()->filter(p|!$p.target.firstName->isEmpty())->project(col(p|$p.id, 'id')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select "root".ID as "id" from interactionTable as "root" left outer join personTable as "personTable_d#7_d#2_m1" on ("root".targetId = "personTable_d#7_d#2_m1".ID) where not "personTable_d#7_d#2_m1".FIRSTNAME is null', $result10);

   let result13 = toSQLString(|Interaction.all()->filter(p|$p.target.firstName->isEmpty() || $p.target.firstName->isEmpty())->project(col(p|$p.id, 'id')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select "root".ID as "id" from interactionTable as "root" left outer join personTable as "personTable_d#3_dy0_d#4_d#2_m1" on ("root".targetId = "personTable_d#3_dy0_d#4_d#2_m1".ID) where ("personTable_d#3_dy0_d#4_d#2_m1".FIRSTNAME is null or "personTable_d#3_dy0_d#4_d#2_m1".FIRSTNAME is null)', $result13);

   let result14 = toSQLString(|Interaction.all()->filter(p|$p.target.firstName->isEmpty() && $p.target.firstName->isEmpty())->project(col(p|$p.id, 'id')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select "root".ID as "id" from interactionTable as "root" left outer join personTable as "personTable_d#3_dy0_d#4_d#2_m1" on ("root".targetId = "personTable_d#3_dy0_d#4_d#2_m1".ID) where ("personTable_d#3_dy0_d#4_d#2_m1".FIRSTNAME is null and "personTable_d#3_dy0_d#4_d#2_m1".FIRSTNAME is null)', $result14);
}


function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testSqlGenerationForBooleanFilter_IQ2():Boolean[1]
{
   let result8 = toSQLString(|Interaction.all()->filter(p|$p.target.firstName->isEmpty() == true)->project(col(p|$p.id, 'id')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select "root".ID as "id" from interactionTable as "root" left outer join personTable as "personTable_d#7_d#2_m1" on ("root".targetId = "personTable_d#7_d#2_m1".ID) where "personTable_d#7_d#2_m1".FIRSTNAME is null', $result8);

   let result9 = toSQLString(|Interaction.all()->filter(p|$p.target.firstName->isEmpty() == false)->project(col(p|$p.id, 'id')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select "root".ID as "id" from interactionTable as "root" left outer join personTable as "personTable_d#7_d#2_m1" on ("root".targetId = "personTable_d#7_d#2_m1".ID) where not "personTable_d#7_d#2_m1".FIRSTNAME is null', $result9);

   let result11 = toSQLString(|Interaction.all()->filter(p|$p.target.firstName->isEmpty()->in(false))->project(col(p|$p.id, 'id')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select "root".ID as "id" from interactionTable as "root" left outer join personTable as "personTable_d#7_d#2_m1" on ("root".targetId = "personTable_d#7_d#2_m1".ID) where not "personTable_d#7_d#2_m1".FIRSTNAME is null', $result11);

   let result12 = toSQLString(|Interaction.all()->filter(p|!$p.target.firstName->isEmpty()->in(false))->project(col(p|$p.id, 'id')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select "root".ID as "id" from interactionTable as "root" left outer join personTable as "personTable_d#8_d#2_m1" on ("root".targetId = "personTable_d#8_d#2_m1".ID) where ("personTable_d#8_d#2_m1".FIRSTNAME is null)', $result12);

   let result15 = toSQLString(|Interaction.all()->filter(p|isTrue($p.target.firstName == 'Andrew')),
                              simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertEquals('select "root".ID as "id" from interactionTable as "root" left outer join personTable as "personTable_d#7_d#2_m1" on ("root".targetId = "personTable_d#7_d#2_m1".ID) where ("personTable_d#7_d#2_m1".FIRSTNAME is null and "personTable_d#7_d#2_m1".FIRSTNAME is null)', $result15);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSqlGenerationMinuteSecond():Boolean[1]
{
   let expected = [
      pair(DatabaseType.SybaseIQ, 'select minute("root".settlementDateTime) as "settlementDateTimeMinute", second("root".settlementDateTime) as "settlementDateTimeSecond" from tradeTable as "root"')
   ];

   $expected->map(p|
      let driver = $p.first;
      let expectedSql = $p.second;

      let result = toSQLString(
         |Trade.all()->project([
                                                     t | $t.settlementDateTime->cast(@Date)->toOne()->minute(),
                                                     t | $t.settlementDateTime->cast(@Date)->toOne()->second()
                                                  ],
                                                  ['settlementDateTimeMinute', 'settlementDateTimeSecond']),
         simpleRelationalMapping,
         $driver, meta::relational::extension::relationalExtensions());

      assertEquals($expectedSql, $result, '\nSQL not as expected for %s\n\nexpected: %s\nactual:   %s', [$driver, $expectedSql, $result]);
   )->distinct() == [true];
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSQLStringWithReplace():Boolean[1]
{
    let sybaseSql = toSQLString(|Person.all()->project(p|$p.firstName->replace('A', 'a'), 'lowerA'), simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
    assertEquals('select replace("root".FIRSTNAME, \'A\', \'a\') as "lowerA" from personTable as "root"', $sybaseSql);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testSybaseKeyWordInSubSelect():Boolean[1]
{
   let date=%2018-03-05;
   let result = meta::relational::functions::sqlstring::toSQLString(|Firm.all()->project([f|$f.legalName, f|$f.employees.locations->filter(o|$o.censusdate == $date).censusdate], ['firm','employee address census date']), simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertSameSQL('select "root".LEGALNAME as "firm", "locationTable_d#5_f_d_d_m2_r"."date" as "employee address census date" from firmTable as "root" left outer join personTable as "personTable_d#7_d_m2" on ("root".ID = "personTable_d#7_d_m2".FIRMID) left outer join (select "locationTable_d#5_f_d".PERSONID as PERSONID, "locationTable_d#5_f_d"."date" as "date" from locationTable as "locationTable_d#5_f_d" where "locationTable_d#5_f_d"."date" = convert(DATE, \'2018-03-05\', 121)) as "locationTable_d#5_f_d_d_m2_r" on ("personTable_d#7_d_m2".ID = "locationTable_d#5_f_d_d_m2_r".PERSONID)', $result);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testSybaseDistinctTake():Boolean[1]
{
   let iq = meta::relational::functions::sqlstring::toSQLString(|Person.all()->project(f|$f.firstName, 'firstName')->distinct()->take(10), simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   let ase = meta::relational::functions::sqlstring::toSQLString(|Person.all()->project(f|$f.firstName, 'firstName')->distinct()->take(10), simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());

   let sql = 'select distinct top 10 "root".FIRSTNAME as "firstName" from personTable as "root"';

   assertSameSQL($sql, $iq);
   assertSameSQL($sql, $ase);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testSqlGenerationDivide_AllDBs():Boolean[1]
{
   let query = {|Trade.all()->filter(t | $t.id == 2)->map(t | $t.quantity->divide(1000000))};
   let expectedSQL = 'select ((1.0 * "root".quantity) / 1000000) from tradeTable as "root" left outer join (select "root".trade_id as trade_id, max("root".eventDate) as maxTradeEventDate from tradeEventTable as "root" group by "root".trade_id) as "tradeEventViewMaxTradeEventDate_d#4_d#4_m5" on ("root".ID = "tradeEventViewMaxTradeEventDate_d#4_d#4_m5".trade_id) where "root".ID = 2';

   let resultSybaseIQ = meta::relational::functions::sqlstring::toSQLString($query, simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertSameSQL($expectedSQL, $resultSybaseIQ);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testIsDistinctSQLGeneration():Boolean[1]
{
   let func = {|Firm.all()->groupBy(
      [t|$t.legalName],
      [agg(x|$x.employees.firstName,y|$y->isDistinct())],
      ['LegalName', 'IsDistinctFirstName']
   )};

   let iq = toSQLString($func, simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());
   assertSameSQL('select "root".LEGALNAME as "LegalName", case when (count(distinct("personTable_d#4_d_m1".FIRSTNAME)) = count("personTable_d#4_d_m1".FIRSTNAME)) then \'true\' else \'false\' end as "IsDistinctFirstName" from firmTable as "root" left outer join personTable as "personTable_d#4_d_m1" on ("root".ID = "personTable_d#4_d_m1".FIRMID) group by "LegalName"', $iq);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSqlGenerationDayOfMonth_SybaseIQ():Boolean[1]
{
   let result = toSQLString(
      |Trade.all()
         ->project(col(t|$t.date->dayOfMonth(), 'date')),
      simpleRelationalMapping,
      DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());

   assertEquals('select datepart(DAY,"root".tradeDate) as "date" from tradeTable as "root"', $result);
}

function <> meta::relational::tests::functions::sqlstring::sybaseIQ::testToSQLStringToNumericCasts():Boolean[1]
{
    let result = toSQLString(
     |Trade.all()
        ->project([
          col(t|$t.quantity->toDecimal(), 'decimal'),
          col(t|$t.quantity->toFloat(), 'float')
        ]), simpleRelationalMapping, DatabaseType.SybaseIQ, meta::relational::extension::relationalExtensions());

    assertEquals('select cast("root".quantity as decimal) as "decimal", cast("root".quantity as double) as "float" from tradeTable as "root"', $result);
}




© 2015 - 2024 Weber Informatics LLC | Privacy Policy