odoo-mobile/www/lib/squel/test/select.test.coffee

###
Copyright (c) 2014 Ramesh Nair (hiddentao.com)

Permission is hereby granted, free of charge, to any person
obtaining a copy of this software and associated documentation
files (the "Software"), to deal in the Software without
restriction, including without limitation the rights to use,
copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the
Software is furnished to do so, subject to the following
conditions:

The above copyright notice and this permission notice shall be
included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
OTHER DEALINGS IN THE SOFTWARE.
###


squel = require "../dist/squel-basic"
{_, testCreator, assert, expect, should} = require './testbase'
test = testCreator()



test['SELECT builder'] =
  beforeEach: ->
    @func = squel.select
    @inst = @func()

  'instanceof QueryBuilder': ->
    assert.instanceOf @inst, squel.cls.QueryBuilder

  'constructor':
    'override options': ->
      @inst = squel.select
        usingValuePlaceholders: true
        dummy: true

      expectedOptions = _.extend {}, squel.cls.DefaultQueryBuilderOptions,
        usingValuePlaceholders: true
        dummy: true

      for block in @inst.blocks
        if (block instanceof squel.cls.FromTableBlock)
          assert.same _.extend({}, expectedOptions, { prefix: 'FROM'}), block.options
        else if (block instanceof squel.cls.WhereBlock)
          assert.same _.extend({}, expectedOptions, { verb: 'WHERE'}), block.options
        else if (block instanceof squel.cls.HavingBlock)
          assert.same _.extend({}, expectedOptions, { verb: 'HAVING'}), block.options
        else
          assert.same expectedOptions, block.options

    'override blocks': ->
      block = new squel.cls.StringBlock('SELECT')
      @inst = @func {}, [block]
      assert.same [block], @inst.blocks

  'build query':
    'no need to call from() first': ->
      @inst.toString()

    '>> function(1)':
      beforeEach: -> @inst.function('1')
      toString: ->
        assert.same @inst.toString(), 'SELECT 1'
      toParam: ->
        assert.same @inst.toParam(), { text: 'SELECT 1', values: [] }

    '>> function(MAX(?,?), 3, 5)':
      beforeEach: -> @inst.function('MAX(?, ?)', 3, 5)
      toString: ->
        assert.same @inst.toString(), 'SELECT MAX(3, 5)'
      toParam: ->
        assert.same @inst.toParam(), { text: 'SELECT MAX(?, ?)', values: [3, 5] }

    '>> from(table).from(table2, alias2)':
      beforeEach: -> @inst.from('table').from('table2', 'alias2')
      toString: ->
        assert.same @inst.toString(), 'SELECT * FROM table, table2 `alias2`'

      '>> field(squel.select().field("MAX(score)").FROM("scores"), fa1)':
        beforeEach: -> @inst.field(squel.select().field("MAX(score)").from("scores"), 'fa1')
        toString: ->
          assert.same @inst.toString(), 'SELECT (SELECT MAX(score) FROM scores) AS "fa1" FROM table, table2 `alias2`'

      '>> field(squel.case().when(score > ?, 1).then(1), fa1)':
        beforeEach: -> @inst.field(squel.case().when("score > ?", 1).then(1), 'fa1')
        toString: ->
          assert.same @inst.toString(), 'SELECT CASE WHEN (score > 1) THEN 1 ELSE NULL END AS "fa1" FROM table, table2 `alias2`'
        toParam: ->
          assert.same @inst.toParam(), { text: 'SELECT CASE WHEN (score > ?) THEN 1 ELSE NULL END AS "fa1" FROM table, table2 `alias2`', values: [1] }

      '>> field( squel.str(SUM(?), squel.case().when(score > ?, 1).then(1) ), fa1)':
        beforeEach: -> @inst.field( squel.str('SUM(?)', squel.case().when("score > ?", 1).then(1)), 'fa1')
        toString: ->
          assert.same @inst.toString(), 'SELECT (SUM((CASE WHEN (score > 1) THEN 1 ELSE NULL END))) AS "fa1" FROM table, table2 `alias2`'
        toParam: ->
          assert.same @inst.toParam(), { text: 'SELECT (SUM(CASE WHEN (score > ?) THEN 1 ELSE NULL END)) AS "fa1" FROM table, table2 `alias2`', values: [1] }

      '>> field(field1, fa1) >> field(field2)':
        beforeEach: -> @inst.field('field1', 'fa1').field('field2')
        toString: ->
          assert.same @inst.toString(), 'SELECT field1 AS "fa1", field2 FROM table, table2 `alias2`'

        '>> distinct()':
          beforeEach: -> @inst.distinct()
          toString: ->
            assert.same @inst.toString(), 'SELECT DISTINCT field1 AS "fa1", field2 FROM table, table2 `alias2`'

          '>> group(field) >> group(field2)':
            beforeEach: -> @inst.group('field').group('field2')
            toString: ->
              assert.same @inst.toString(), 'SELECT DISTINCT field1 AS "fa1", field2 FROM table, table2 `alias2` GROUP BY field, field2'

            '>> where(a = ?, squel.select().field("MAX(score)").from("scores"))':
              beforeEach: ->
                @subQuery = squel.select().field("MAX(score)").from("scores")
                @inst.where('a = ?', @subQuery)
              toString: ->
                assert.same @inst.toString(), 'SELECT DISTINCT field1 AS "fa1", field2 FROM table, table2 `alias2` WHERE (a = (SELECT MAX(score) FROM scores)) GROUP BY field, field2'
              toParam: ->
                assert.same @inst.toParam(), {
                  text: 'SELECT DISTINCT field1 AS "fa1", field2 FROM table, table2 `alias2` WHERE (a = (SELECT MAX(score) FROM scores)) GROUP BY field, field2'
                  values: []
                }

            '>> where(squel.expr().and(a = ?, 1).and( expr().or(b = ?, 2).or(c = ?, 3) ))':
              beforeEach: -> @inst.where(squel.expr().and("a = ?", 1).and(squel.expr().or("b = ?", 2).or("c = ?", 3)))
              toString: ->
                assert.same @inst.toString(), 'SELECT DISTINCT field1 AS "fa1", field2 FROM table, table2 `alias2` WHERE (a = 1 AND (b = 2 OR c = 3)) GROUP BY field, field2'
              toParam: ->
                assert.same @inst.toParam(), {
                  text: 'SELECT DISTINCT field1 AS "fa1", field2 FROM table, table2 `alias2` WHERE (a = ? AND (b = ? OR c = ?)) GROUP BY field, field2'
                  values: [1, 2, 3]
                }

            '>> where(squel.expr().and(a = ?, QueryBuilder).and( expr().or(b = ?, 2).or(c = ?, 3) ))':
              beforeEach: ->
                subQuery = squel.select().field('field1').from('table1').where('field2 = ?', 10)
                @inst.where(squel.expr().and("a = ?", subQuery).and(squel.expr().or("b = ?", 2).or("c = ?", 3)))
              toString: ->
                assert.same @inst.toString(), 'SELECT DISTINCT field1 AS "fa1", field2 FROM table, table2 `alias2` WHERE (a = (SELECT field1 FROM table1 WHERE (field2 = 10)) AND (b = 2 OR c = 3)) GROUP BY field, field2'
              toParam: ->
                assert.same @inst.toParam(), {
                  text: 'SELECT DISTINCT field1 AS "fa1", field2 FROM table, table2 `alias2` WHERE (a = (SELECT field1 FROM table1 WHERE (field2 = ?)) AND (b = ? OR c = ?)) GROUP BY field, field2'
                  values: [10, 2, 3]
                }

            '>> having(squel.expr().and(a = ?, QueryBuilder).and( expr().or(b = ?, 2).or(c = ?, 3) ))':
              beforeEach: ->
                subQuery = squel.select().field('field1').from('table1').having('field2 = ?', 10)
                @inst.having(squel.expr().and("a = ?", subQuery).and(squel.expr().or("b = ?", 2).or("c = ?", 3)))
              toString: ->
                assert.same @inst.toString(), 'SELECT DISTINCT field1 AS "fa1", field2 FROM table, table2 `alias2` GROUP BY field, field2 HAVING (a = (SELECT field1 FROM table1 HAVING (field2 = 10)) AND (b = 2 OR c = 3))'
              toParam: ->
                assert.same @inst.toParam(), {
                  text: 'SELECT DISTINCT field1 AS "fa1", field2 FROM table, table2 `alias2` GROUP BY field, field2 HAVING (a = (SELECT field1 FROM table1 HAVING (field2 = ?)) AND (b = ? OR c = ?))'
                  values: [10, 2, 3]
                }

            '>> where(a = ?, null)':
              beforeEach: -> @inst.where('a = ?', null)
              toString: ->
                assert.same @inst.toString(), 'SELECT DISTINCT field1 AS "fa1", field2 FROM table, table2 `alias2` WHERE (a = NULL) GROUP BY field, field2'
              toParam: ->
                assert.same @inst.toParam(), {
                  text: 'SELECT DISTINCT field1 AS "fa1", field2 FROM table, table2 `alias2` WHERE (a = ?) GROUP BY field, field2'
                  values: [null]
                }
                
            '>> where(a = ?, 1)':
              beforeEach: -> @inst.where('a = ?', 1)
              toString: ->
                assert.same @inst.toString(), 'SELECT DISTINCT field1 AS "fa1", field2 FROM table, table2 `alias2` WHERE (a = 1) GROUP BY field, field2'
              toParam: ->
                assert.same @inst.toParam(), {
                  text: 'SELECT DISTINCT field1 AS "fa1", field2 FROM table, table2 `alias2` WHERE (a = ?) GROUP BY field, field2'
                  values: [1]
                }

              '>> join(other_table)':
                beforeEach: -> @inst.join('other_table')
                toString: ->
                  assert.same @inst.toString(), 'SELECT DISTINCT field1 AS "fa1", field2 FROM table, table2 `alias2` INNER JOIN other_table WHERE (a = 1) GROUP BY field, field2'

                '>> order(a)':
                  beforeEach: -> @inst.order('a')
                  toString: ->
                    assert.same @inst.toString(), 'SELECT DISTINCT field1 AS "fa1", field2 FROM table, table2 `alias2` INNER JOIN other_table WHERE (a = 1) GROUP BY field, field2 ORDER BY a ASC'

                '>> order(a, null)':
                  beforeEach: -> @inst.order('a', null)
                  toString: ->
                    assert.same @inst.toString(), 'SELECT DISTINCT field1 AS "fa1", field2 FROM table, table2 `alias2` INNER JOIN other_table WHERE (a = 1) GROUP BY field, field2 ORDER BY a'

                '>> order(a, true)':
                  beforeEach: -> @inst.order('a', true)
                  toString: ->
                    assert.same @inst.toString(), 'SELECT DISTINCT field1 AS "fa1", field2 FROM table, table2 `alias2` INNER JOIN other_table WHERE (a = 1) GROUP BY field, field2 ORDER BY a ASC'

                  '>> limit(2)':
                    beforeEach: -> @inst.limit(2)
                    toString: ->
                      assert.same @inst.toString(), 'SELECT DISTINCT field1 AS "fa1", field2 FROM table, table2 `alias2` INNER JOIN other_table WHERE (a = 1) GROUP BY field, field2 ORDER BY a ASC LIMIT 2'

                    '>> offset(3)':
                      beforeEach: -> @inst.offset(3)
                      toString: ->
                        assert.same @inst.toString(), 'SELECT DISTINCT field1 AS "fa1", field2 FROM table, table2 `alias2` INNER JOIN other_table WHERE (a = 1) GROUP BY field, field2 ORDER BY a ASC LIMIT 2 OFFSET 3'

                '>> order(DIST(?,?), true, 2, 3)':
                  beforeEach: -> @inst.order('DIST(?, ?)', true, 2, false)
                  toString: ->
                    assert.same @inst.toString(), 'SELECT DISTINCT field1 AS "fa1", field2 FROM table, table2 `alias2` INNER JOIN other_table WHERE (a = 1) GROUP BY field, field2 ORDER BY DIST(2, FALSE) ASC'
                  toParam: ->
                    assert.same @inst.toParam(), {
                      text: 'SELECT DISTINCT field1 AS "fa1", field2 FROM table, table2 `alias2` INNER JOIN other_table WHERE (a = ?) GROUP BY field, field2 ORDER BY DIST(?, ?) ASC'
                      values: [1, 2, false]
                    }

                '>> order(a)':
                  beforeEach: -> @inst.order('a')
                  toString: ->
                    assert.same @inst.toString(), 'SELECT DISTINCT field1 AS "fa1", field2 FROM table, table2 `alias2` INNER JOIN other_table WHERE (a = 1) GROUP BY field, field2 ORDER BY a ASC'

                '>> order(b, null)':
                  beforeEach: -> @inst.order('b', null)
                  toString: ->
                    assert.same @inst.toString(), 'SELECT DISTINCT field1 AS "fa1", field2 FROM table, table2 `alias2` INNER JOIN other_table WHERE (a = 1) GROUP BY field, field2 ORDER BY b'

              '>> join(other_table, condition = expr())':
                beforeEach: ->
                  subQuery = squel.select().field('abc').from('table1').where('adf = ?', 'today1')
                  subQuery2 = squel.select().field('xyz').from('table2').where('adf = ?', 'today2')
                  expr = squel.expr().and('field1 = ?', subQuery)
                  @inst.join('other_table', null, expr)
                  @inst.where('def IN ?', subQuery2)
                toString: ->
                  assert.same @inst.toString(), "SELECT DISTINCT field1 AS \"fa1\", field2 FROM table, table2 `alias2` INNER JOIN other_table ON (field1 = (SELECT abc FROM table1 WHERE (adf = 'today1'))) WHERE (a = 1) AND (def IN (SELECT xyz FROM table2 WHERE (adf = 'today2'))) GROUP BY field, field2"
                toParam: ->
                  assert.same @inst.toParam(), { text: 'SELECT DISTINCT field1 AS "fa1", field2 FROM table, table2 `alias2` INNER JOIN other_table ON (field1 = (SELECT abc FROM table1 WHERE (adf = ?))) WHERE (a = ?) AND (def IN (SELECT xyz FROM table2 WHERE (adf = ?))) GROUP BY field, field2', values: ["today1",1,"today2"] }


    'nested queries':
      'basic': ->
        inner1 = squel.select().from('students')
        inner2 = squel.select().from('scores')

        @inst.from(inner1).from(inner2, 'scores')

        assert.same @inst.toString(), "SELECT * FROM (SELECT * FROM students), (SELECT * FROM scores) `scores`"
      'deep nesting': ->
        inner1 = squel.select().from('students')
        inner2 = squel.select().from(inner1)

        @inst.from(inner2)

        assert.same @inst.toString(), "SELECT * FROM (SELECT * FROM (SELECT * FROM students))"

      'nesting in JOINs': ->
        inner1 = squel.select().from('students')
        inner2 = squel.select().from(inner1)

        @inst.from('schools').join(inner2, 'meh', 'meh.ID = ID')

        assert.same @inst.toString(), "SELECT * FROM schools INNER JOIN (SELECT * FROM (SELECT * FROM students)) `meh` ON (meh.ID = ID)"

      'nesting in JOINs with params': ->
        inner1 = squel.select().from('students').where('age = ?', 6)
        inner2 = squel.select().from(inner1)

        @inst.from('schools').where('school_type = ?', 'junior').join(inner2, 'meh', 'meh.ID = ID')

        assert.same @inst.toString(), "SELECT * FROM schools INNER JOIN (SELECT * FROM (SELECT * FROM students WHERE (age = 6))) `meh` ON (meh.ID = ID) WHERE (school_type = 'junior')"
        assert.same @inst.toParam(), { "text": "SELECT * FROM schools INNER JOIN (SELECT * FROM (SELECT * FROM students WHERE (age = ?))) `meh` ON (meh.ID = ID) WHERE (school_type = ?)", "values": [6,'junior'] }
        assert.same @inst.toParam({ "numberedParameters": true}), { "text": "SELECT * FROM schools INNER JOIN (SELECT * FROM (SELECT * FROM students WHERE (age = $1))) `meh` ON (meh.ID = ID) WHERE (school_type = $2)", "values": [6,'junior'] }

  'Complex table name, e.g. LATERAL (#230)':
    beforeEach: ->
      @inst = squel.select().from('foo').from(squel.str('LATERAL(?)', squel.select().from('bar').where('bar.id = ?', 2)), 'ss')
    'toString': ->
      assert.same @inst.toString(), 'SELECT * FROM foo, (LATERAL((SELECT * FROM bar WHERE (bar.id = 2)))) `ss`', 
    'toParam': ->
      assert.same @inst.toParam(), {
        text: 'SELECT * FROM foo, (LATERAL((SELECT * FROM bar WHERE (bar.id = ?)))) `ss`'
        values: [2]
      }

  'cloning':
    'basic': ->
      newinst = @inst.from('students').limit(10).clone()
      newinst.limit(20)

      assert.same 'SELECT * FROM students LIMIT 10', @inst.toString()
      assert.same 'SELECT * FROM students LIMIT 20', newinst.toString()

    'with expressions (ticket #120)': ->
      expr = squel.expr().and('a = 1')
      newinst = @inst.from('table').left_join('table_2', 't', expr)
        .clone()
        .where('c = 1')
      
      expr.and('b = 2')

      assert.same 'SELECT * FROM table LEFT JOIN table_2 `t` ON (a = 1 AND b = 2)', @inst.toString()
      assert.same 'SELECT * FROM table LEFT JOIN table_2 `t` ON (a = 1) WHERE (c = 1)', newinst.toString()

    'with sub-queries (ticket #120)': ->
      newinst = @inst.from(squel.select().from('students')).limit(30)
        .clone()
        .where('c = 1')
        .limit(35)
      
      assert.same 'SELECT * FROM (SELECT * FROM students) LIMIT 30', @inst.toString()
      assert.same 'SELECT * FROM (SELECT * FROM students) WHERE (c = 1) LIMIT 35', newinst.toString()

    'with complex expressions': ->
      expr = squel.expr().and(
        squel.expr().or('b = 2').or(
          squel.expr().and('c = 3').and('d = 4')
        )
      ).and('a = 1')

      newinst = @inst.from('table').left_join('table_2', 't', expr)
        .clone()
        .where('c = 1')
      
      expr.and('e = 5')

      assert.same @inst.toString(), 'SELECT * FROM table LEFT JOIN table_2 `t` ON ((b = 2 OR (c = 3 AND d = 4)) AND a = 1 AND e = 5)'
      assert.same newinst.toString(), 'SELECT * FROM table LEFT JOIN table_2 `t` ON ((b = 2 OR (c = 3 AND d = 4)) AND a = 1) WHERE (c = 1)'



  'can specify block separator': ->
    assert.same( squel.select({separator: '\n'})
      .field('thing')
      .from('table')
      .toString(), """
        SELECT
        thing
        FROM table
      """
    )

  '#242 - auto-quote table names':
    beforeEach: ->
      @inst = squel
        .select({ autoQuoteTableNames: true })
        .field('name')
        .where('age > ?', 15)      

    'using string': 
      beforeEach: ->
        @inst.from('students', 's')

      toString: ->
        assert.same @inst.toString(), """
        SELECT name FROM `students` `s` WHERE (age > 15)
        """

      toParam: ->
        assert.same @inst.toParam(), {
          "text": "SELECT name FROM `students` `s` WHERE (age > ?)"
          "values": [15]
        }

    'using query builder': 
      beforeEach: ->
        @inst.from(squel.select().from('students'), 's')

      toString: ->
        assert.same @inst.toString(), """
        SELECT name FROM (SELECT * FROM students) `s` WHERE (age > 15)
        """

      toParam: ->
        assert.same @inst.toParam(), {
          "text": "SELECT name FROM (SELECT * FROM students) `s` WHERE (age > ?)"
          "values": [15]
        }


  'UNION JOINs':
    'Two Queries NO Params':
      beforeEach: ->
        @qry1 = squel.select().field('name').from('students').where('age > 15')
        @qry2 = squel.select().field('name').from('students').where('age < 6')
        @qry1.union(@qry2)

      toString: ->
        assert.same @qry1.toString(), """
        SELECT name FROM students WHERE (age > 15) UNION (SELECT name FROM students WHERE (age < 6))
        """
      toParam: ->
        assert.same @qry1.toParam(), {
          "text": "SELECT name FROM students WHERE (age > 15) UNION (SELECT name FROM students WHERE (age < 6))"
          "values": [
          ]
        }

    'Two Queries with Params':
      beforeEach: ->
        @qry1 = squel.select().field('name').from('students').where('age > ?', 15)
        @qry2 = squel.select().field('name').from('students').where('age < ?', 6)
        @qry1.union(@qry2)

      toString: ->
        assert.same @qry1.toString(), """
        SELECT name FROM students WHERE (age > 15) UNION (SELECT name FROM students WHERE (age < 6))
        """
      toParam: ->
        assert.same @qry1.toParam(), {
          "text": "SELECT name FROM students WHERE (age > ?) UNION (SELECT name FROM students WHERE (age < ?))"
          "values": [
            15
            6
          ]
        }

    'Three Queries':
      beforeEach: ->
        @qry1 = squel.select().field('name').from('students').where('age > ?', 15)
        @qry2 = squel.select().field('name').from('students').where('age < 6')
        @qry3 = squel.select().field('name').from('students').where('age = ?', 8)
        @qry1.union(@qry2)
        @qry1.union(@qry3)

      toParam: ->
        assert.same @qry1.toParam(), {
          "text": "SELECT name FROM students WHERE (age > ?) UNION (SELECT name FROM students WHERE (age < 6)) UNION (SELECT name FROM students WHERE (age = ?))"
          "values": [
            15
            8
          ]
        }
      'toParam(2)': ->
        assert.same @qry1.toParam({ "numberedParameters": true, "numberedParametersStartAt": 2}), {
          "text": "SELECT name FROM students WHERE (age > $2) UNION (SELECT name FROM students WHERE (age < 6)) UNION (SELECT name FROM students WHERE (age = $3))"
          "values": [
            15
            8
          ]
        }

    'Multi-Parameter Query':
      beforeEach: ->
        @qry1 = squel.select().field('name').from('students').where('age > ?', 15)
        @qry2 = squel.select().field('name').from('students').where('age < ?', 6)
        @qry3 = squel.select().field('name').from('students').where('age = ?', 8)
        @qry4 = squel.select().field('name').from('students').where('age IN [?, ?]', 2, 10)
        @qry1.union(@qry2)
        @qry1.union(@qry3)
        @qry4.union_all(@qry1)

      toString: ->
        assert.same @qry4.toString(), """
        SELECT name FROM students WHERE (age IN [2, 10]) UNION ALL (SELECT name FROM students WHERE (age > 15) UNION (SELECT name FROM students WHERE (age < 6)) UNION (SELECT name FROM students WHERE (age = 8)))
        """
      toParam: ->
        assert.same @qry4.toParam({ "numberedParameters": true}), {
          "text": "SELECT name FROM students WHERE (age IN [$1, $2]) UNION ALL (SELECT name FROM students WHERE (age > $3) UNION (SELECT name FROM students WHERE (age < $4)) UNION (SELECT name FROM students WHERE (age = $5)))"
          "values": [
            2
            10
            15
            6
            8
          ]
        }


module?.exports[require('path').basename(__filename)] = test