Example #1
0
def test_execute():
  dataset = mock_data_set()
  qb = QueryBuilder(dataset).frm('employees')
  eq_(
    list(qb.execute()),
    list(dataset.frm('employees').execute())
  )
def test_select():
    dataset = mock_data_set()

    qb = QueryBuilder(dataset)
    eq_(qb.column_exps, '*')
    qb_w_select = qb.select('x,y')

    assert_is_not(qb, qb_w_select)
    eq_(qb_w_select.column_exps, 'x,y')

    qb_w_select_and_from = qb_w_select.frm('bogus')

    q = qb_w_select_and_from.query

    assert_is_instance(q, Query)

    assert_sequence_equal(q.schema.fields, [
        Field(name="x", type="INTEGER", schema_name="bogus"),
        Field(name="y", type="INTEGER", schema_name="bogus")
    ])

    compare(q.operations, ProjectionOp(LoadOp('bogus'), Var('x'), Var('y')))

    qb_select_y_from_bogus = qb.select('y').frm('bogus')
    eq_(qb_select_y_from_bogus.column_exps, 'y')

    assert_sequence_equal(
        qb_select_y_from_bogus.query.schema.fields,
        [Field(name="y", type="INTEGER", schema_name="bogus")])

    compare(qb_select_y_from_bogus.query.operations,
            ProjectionOp(LoadOp('bogus'), Var('y')))
def test_from_bogus():
    dataset = mock_data_set()
    qb = QueryBuilder(dataset)

    qb_w_from = qb.frm('bogus')
    # ensure we maintain immutability by only returning
    # nev versions
    assert_is_not(qb, qb_w_from)

    q = qb_w_from.query
    assert_is_instance(q, Query)

    assert_equal(q.schema, dataset.get_schema('bogus'))

    compare(q.operations, LoadOp('bogus'))
def test_offset_and_limit():

    dataset = mock_data_set()

    qb = QueryBuilder(dataset).frm('employees').offset(1).limit(1)

    assert_equal(qb.query.operations, SliceOp(LoadOp('employees'), 1, 2))
def test_where():
    dataset = mock_data_set()
    qb = QueryBuilder(dataset).frm('employees').where('employee_id = 123')

    assert_equal(
        qb.query.operations,
        SelectionOp(LoadOp('employees'),
                    EqOp(Var('employee_id'), NumberConst(123))))
def test_order_by_multiple():
    dataset = mock_data_set()
    qb = QueryBuilder(dataset).frm('employees').order_by(
        'employee_id, full_name Desc, 123')

    assert_equal(
        qb.query.operations,
        OrderByOp(LoadOp('employees'), Var('employee_id'),
                  Desc(Var('full_name')), NumberConst(123)))
Example #7
0
def test_select():
  dataset = mock_data_set()

  qb = QueryBuilder(dataset)
  eq_(qb.column_exps, '*')
  qb_w_select = qb.select('x,y')

  assert_is_not(qb, qb_w_select)
  eq_(qb_w_select.column_exps, 'x,y')

  qb_w_select_and_from = qb_w_select.frm('bogus')

  q =  qb_w_select_and_from.query

  assert_is_instance(q, Query)

  assert_sequence_equal(
    q.schema.fields, 
    [
      Field(name="x", type="INTEGER", schema_name="bogus"),
      Field(name="y", type="INTEGER", schema_name="bogus")
    ]
  )

  compare(
    q.operations,
    ProjectionOp(LoadOp('bogus'), Var('x'), Var('y'))
  )

  qb_select_y_from_bogus = qb.select('y').frm('bogus')
  eq_(qb_select_y_from_bogus.column_exps, 'y')

  assert_sequence_equal(
    qb_select_y_from_bogus.query.schema.fields, 
    [
      Field(name="y", type="INTEGER", schema_name="bogus")
    ]
  )

  compare(
    qb_select_y_from_bogus.query.operations,
    ProjectionOp(LoadOp('bogus'),Var('y'))
  )
def test_join_subselect():
    dataset = mock_data_set()
    managers = QueryBuilder(dataset).select(
        'employee_id as id, full_name as manager').frm('employees as manager')

    query = QueryBuilder(dataset).select('employee.*,  manager').frm(
        'employees as employee').join(
            managers, on="manager.id = employee.manager_id").query

    operations = ProjectionOp(
        JoinOp(
            AliasOp('employee', LoadOp('employees')),
            ProjectionOp(AliasOp('manager', LoadOp('employees')),
                         RenameOp("id", Var("employee_id")),
                         RenameOp("manager", Var("full_name"))),
            EqOp(Var('manager.id'), Var('employee.manager_id'))),
        SelectAllExpr('employee'), Var('manager'))

    eq_(query.operations, operations)
def test_all_aggregates():
    """Aggregation is possible if all the selected columns are aggregate functions"""

    dataset = mock_data_set()

    qb = QueryBuilder(dataset).select('count()').frm('employees')

    assert_equal(
        qb.query.operations,
        GroupByOp(ProjectionOp(LoadOp('employees'), Function('count'))))
Example #10
0
def test_group_by():
    dataset = mock_data_set()

    qb = QueryBuilder(dataset).select('manager_id, count()').frm(
        'employees').group_by('manager_id')

    assert_equal(
        qb.query.operations,
        GroupByOp(
            ProjectionOp(LoadOp('employees'), Var('manager_id'),
                         Function('count')), Var('manager_id')))
Example #11
0
def test_from_bogus():
  dataset = mock_data_set()
  qb = QueryBuilder(dataset)

  qb_w_from = qb.frm('bogus')
  # ensure we maintain immutability by only returning
  # nev versions
  assert_is_not(qb, qb_w_from)

  q  = qb_w_from.query
  assert_is_instance(q, Query)

  assert_equal(
    q.schema,
    dataset.get_schema('bogus')
  )

  compare(
    q.operations,
    LoadOp('bogus')
  )
Example #12
0
def test_projection_and_selection():
    dataset = mock_data_set()

    qb = QueryBuilder(dataset).select('full_name').frm('employees').where(
        'employee_id = 123')

    query = qb.query
    assert_equal(
        query.operations,
        ProjectionOp(
            SelectionOp(LoadOp('employees'),
                        EqOp(Var('employee_id'), NumberConst(123))),
            Var('full_name')))

    assert_sequence_equal(
        query.schema.fields,
        [Field(name="full_name", type="STRING", schema_name="employees")])
Example #13
0
def test_order_by():
    dataset = mock_data_set()
    qb = QueryBuilder(dataset).frm('employees').order_by('employee_id')

    assert_equal(qb.query.operations,
                 OrderByOp(LoadOp('employees'), Var('employee_id')))
Example #14
0
def test_execute():
    dataset = mock_data_set()
    qb = QueryBuilder(dataset).frm('employees')
    eq_(list(qb.execute()), list(dataset.frm('employees').execute()))