def test_aggregation_project_fields():
result = parse('AGGREGATE article PROJECT title = 1, author = 1')
assert isinstance(result, AggregateOperation)
assert result.collection == 'article'
assert result.to_mongo()['pipeline'] == [
{'$project': {'title': 1, 'author': 1}}
]
result = parse('AGGREGATE article PROJECT _id = 0, title = 1, author = 1')
assert isinstance(result, AggregateOperation)
assert result.collection == 'article'
assert result.to_mongo()['pipeline'] == [
{'$project': {'_id': 0, 'title': 1, 'author': 1}}
]
def test_parsing_all_parts():
query = """
SELECT field, field1, field2
FROM mycollection
WHERE field == 'value'
LIMIT 100 SKIP 20
SORT field1 ASC, field2 DESC
"""
parsed = parse(query)
assert isinstance(parsed, SelectOperation)
assert parsed.collection == 'mycollection'
assert parsed.fields == ['field', 'field1', 'field2']
assert isinstance(parsed.query, Comparison)
assert parsed.query.to_mongo() == {'field': 'value'}
assert parsed.limit == 100
assert parsed.skip == 20
assert parsed.sort == {'field1': 1, 'field2': -1}
class FakeCollection(object):
def find(self, **kwargs):
assert set(kwargs.keys()) == set((
'spec', 'fields', 'limit', 'skip', 'sort'))
assert kwargs['spec'] == {'field': 'value'}
assert kwargs['fields'] == ['field', 'field1', 'field2']
assert kwargs['limit'] == 100
assert kwargs['skip'] == 20
assert kwargs['sort'] == {'field1': 1, 'field2': -1}
parsed.apply({'mycollection': FakeCollection()})
def test_parsing_all_parts():
query = """
SELECT field, field1, field2
FROM mycollection
WHERE field == 'value'
LIMIT 100 SKIP 20
SORT field1 ASC, field2 DESC
"""
parsed = parse(query)
assert isinstance(parsed, SelectOperation)
assert parsed.collection == 'mycollection'
assert parsed.fields == ['field', 'field1', 'field2']
assert isinstance(parsed.query, Comparison)
assert parsed.query.to_mongo() == {'field': 'value'}
assert parsed.limit == 100
assert parsed.skip == 20
assert parsed.sort == {'field1': 1, 'field2': -1}
class FakeCollection(object):
def find(self, **kwargs):
assert set(kwargs.keys()) == set(
('spec', 'fields', 'limit', 'skip', 'sort'))
assert kwargs['spec'] == {'field': 'value'}
assert kwargs['fields'] == ['field', 'field1', 'field2']
assert kwargs['limit'] == 100
assert kwargs['skip'] == 20
assert kwargs['sort'] == {'field1': 1, 'field2': -1}
parsed.apply({'mycollection': FakeCollection()})
def test_aggregation_project_computed_field():
result = parse(
'AGGREGATE article PROJECT title = 1, '
'doctoredPageViews = "$pageViews" + 10')
assert isinstance(result, AggregateOperation)
assert result.collection == 'article'
assert result.to_mongo()['pipeline'] == [
{'$project': {
'title': 1,
'doctoredPageViews': {
'$add': ['$pageViews', 10],
}}}]
def test_aggregation_project_fields():
result = parse('AGGREGATE article PROJECT title = 1, author = 1')
assert isinstance(result, AggregateOperation)
assert result.collection == 'article'
assert result.to_mongo()['pipeline'] == [{
'$project': {
'title': 1,
'author': 1
}
}]
result = parse('AGGREGATE article PROJECT _id = 0, title = 1, author = 1')
assert isinstance(result, AggregateOperation)
assert result.collection == 'article'
assert result.to_mongo()['pipeline'] == [{
'$project': {
'_id': 0,
'title': 1,
'author': 1
}
}]
def test_parsing_expressions():
res = parse('SELECT * FROM coll WHERE foo == "Spam" AND bar == "Eggs"')
# assert res.query.to_mongo() == {
# 'foo': 'Spam',
# 'bar': 'Eggs',
# }
assert res.query.to_mongo() == {
'$and': [
{'foo': 'Spam'},
{'bar': 'Eggs'},
],
}
pass
def test_aggregation_project_computed_field():
result = parse('AGGREGATE article PROJECT title = 1, '
'doctoredPageViews = "$pageViews" + 10')
assert isinstance(result, AggregateOperation)
assert result.collection == 'article'
assert result.to_mongo()['pipeline'] == [{
'$project': {
'title': 1,
'doctoredPageViews': {
'$add': ['$pageViews', 10],
}
}
}]
def test_aggregation_project_rename_fields():
result = parse("""
AGGREGATE article
PROJECT title = 1,
page_views = '$pageViews',
bar = '$other.foo'
""")
assert isinstance(result, AggregateOperation)
assert result.collection == 'article'
assert result.to_mongo()['pipeline'] == [
{'$project': {
'title': 1,
'page_views': '$pageViews',
'bar': '$other.foo',
}}]
def test_simple_parsing():
query = "SELECT * FROM mycollection WHERE field == 'value'"
parsed = parse(query)
assert isinstance(parsed, SelectOperation)
assert parsed.collection == 'mycollection'
assert parsed.fields is None
assert isinstance(parsed.query, Comparison)
assert parsed.query.to_mongo() == {'field': 'value'}
class FakeCollection(object):
def find(self, **kwargs):
assert kwargs.keys() == ['spec']
assert kwargs['spec'] == {'field': 'value'}
parsed.apply({'mycollection': FakeCollection()})
pass
def test_simple_parsing():
query = "SELECT * FROM mycollection WHERE field == 'value'"
parsed = parse(query)
assert isinstance(parsed, SelectOperation)
assert parsed.collection == 'mycollection'
assert parsed.fields is None
assert isinstance(parsed.query, Comparison)
assert parsed.query.to_mongo() == {'field': 'value'}
class FakeCollection(object):
def find(self, **kwargs):
assert kwargs.keys() == ['spec']
assert kwargs['spec'] == {'field': 'value'}
parsed.apply({'mycollection': FakeCollection()})
pass
def test_aggregation_project_rename_fields():
result = parse("""
AGGREGATE article
PROJECT title = 1,
page_views = '$pageViews',
bar = '$other.foo'
""")
assert isinstance(result, AggregateOperation)
assert result.collection == 'article'
assert result.to_mongo()['pipeline'] == [{
'$project': {
'title': 1,
'page_views': '$pageViews',
'bar': '$other.foo',
}
}]
def test_parsing_expressions():
res = parse('SELECT * FROM coll WHERE foo == "Spam" AND bar == "Eggs"')
# assert res.query.to_mongo() == {
# 'foo': 'Spam',
# 'bar': 'Eggs',
# }
assert res.query.to_mongo() == {
'$and': [
{
'foo': 'Spam'
},
{
'bar': 'Eggs'
},
],
}
pass
def test_aggregation_project_subdocument():
result = parse("""
AGGREGATE article
PROJECT title = 1,
stats = {
pv = '$pageViews',
foo = '$other.foo',
dpv = '$pageViews' + 10,
}
""")
assert isinstance(result, AggregateOperation)
assert result.collection == 'article'
assert result.to_mongo()['pipeline'] == [
{'$project': {
'title': 1,
'stats': {
'pv': "$pageViews",
'foo': "$other.foo",
'dpv': {'$add': ["$pageViews", 10]}
},
}}]
def test_aggregation_project_subdocument():
result = parse("""
AGGREGATE article
PROJECT title = 1,
stats = {
pv = '$pageViews',
foo = '$other.foo',
dpv = '$pageViews' + 10,
}
""")
assert isinstance(result, AggregateOperation)
assert result.collection == 'article'
assert result.to_mongo()['pipeline'] == [{
'$project': {
'title': 1,
'stats': {
'pv': "$pageViews",
'foo': "$other.foo",
'dpv': {
'$add': ["$pageViews", 10]
}
},
}
}]
def sql(self, query):
return parse(query).apply(self)
def test_simple_match(mongodb):
objects = [
{'item': 'apple', 'price': 10},
{'item': 'banana', 'price': 5},
{'item': 'pear', 'price': 12},
{'item': 'pineapple', 'price': 50},
{'item': 'grapes', 'price': 20},
]
for item in objects:
mongodb.mycollection.save(item)
q = parse('SELECT * FROM mycollection')
result = q.apply(mongodb)
data = list(result)
## Objects got modified, they now have an _id
## and thus should match exactly the returned data!
assert data == objects
## Now with a "WHERE" clause
q = parse('SELECT * FROM mycollection WHERE item == "apple"')
result = q.apply(mongodb)
data = list(result)
assert data == [objects[0]]
## Now with a "WHERE" clause
q = parse("""
SELECT * FROM mycollection
WHERE item == "apple" OR item == "banana"
""")
result = q.apply(mongodb)
data = list(result)
assert data == [objects[0], objects[1]]
## Let's try the IN operator
q = parse("""
SELECT * FROM mycollection
WHERE item IN ["apple", "banana"]
""")
assert q.query.to_mongo() == {'item': {'$in': ['apple', 'banana']}}
result = q.apply(mongodb)
data = list(result)
assert data == [objects[0], objects[1]]
## Let's try the GT operator
q = parse("SELECT * FROM mycollection WHERE price > 12")
assert q.query.to_mongo() == {'price': {'$gt': 12}}
result = q.apply(mongodb)
data = list(result)
assert data == [objects[3], objects[4]]
## Let's try the GTE operator
q = parse("SELECT * FROM mycollection WHERE price >= 12")
assert q.query.to_mongo() == {'price': {'$gte': 12}}
result = q.apply(mongodb)
data = list(result)
assert data == [objects[2], objects[3], objects[4]]
## Let's try the LT operator
q = parse("SELECT * FROM mycollection WHERE price < 12")
assert q.query.to_mongo() == {'price': {'$lt': 12}}
result = q.apply(mongodb)
data = list(result)
assert data == [objects[0], objects[1]]
## Let's try the LTE operator
q = parse("SELECT * FROM mycollection WHERE price <= 12")
assert q.query.to_mongo() == {'price': {'$lte': 12}}
result = q.apply(mongodb)
data = list(result)
assert data == [objects[0], objects[1], objects[2]]
## Let's try with an OR
q = parse("SELECT * FROM mycollection WHERE price <= 10 or price > 18")
assert q.query.to_mongo() == {'$or': [
{'price': {'$lte': 10}},
{'price': {'$gt': 18}},
]}
result = q.apply(mongodb)
data = list(result)
assert data == [objects[0], objects[1], objects[3], objects[4]]
