def test_subquery():
Query.update_forward_refs()
my_subquery = Query(entity='users',
properties=[
Property(name='users.name'),
],
group=[
Property(name='users.birthday'),
],
alias='s')
my_query = Query(
entity=my_subquery,
properties=[
Count(property=Constant(value='*')),
],
)
sql = "SELECT COUNT('*') FROM (SELECT users.name FROM users GROUP BY users.birthday) AS s"
assert str(my_query) == sql
json_string = my_query.json()
clone = Query.parse_raw(json_string)
assert clone == my_query
assert str(clone) == sql
def test_json_field_parser():
json_property = Property.parse_raw(
'{"obj": "properties.Property", "name": "users.additional_data ->> title", "alias": "title"}'
)
assert json_property.name == "users.additional_data ->> title"
assert str(
json_property) == "users.additional_data ->> 'title' AS \"title\""
def test_cast():
my_order = Order(property=CastOperator(property=Property(
name='users.additional_data -> custom_fields ->> age'),
to=CastOperator.DataType.INT))
assert str(
my_order
) == "(users.additional_data -> 'custom_fields' ->> 'age')::int ASC"
def test_simple():
my_structure = CastOperator(
property=Property(name='users.age'),
to=CastOperator.DataType.VARCHAR,
alias='age_as_string'
)
assert str(my_structure) == 'users.age::varchar AS age_as_string'
def test_json_field():
my_structure = CastOperator(
property=Property(name='organisations.statistics->campaigns_count'),
to=CastOperator.DataType.INT,
alias='campaigns_count'
)
assert str(my_structure) == '(organisations.statistics ->> \'campaigns_count\')::int AS campaigns_count'
def test_json_nested_field_parser():
json_property = Property.parse_raw(
'{"obj": "properties.Property", "name": "users.additional_data -> custom_fields ->> age", "alias": "age"}'
)
assert json_property.name == "users.additional_data -> custom_fields ->> age"
assert str(
json_property
) == "users.additional_data -> 'custom_fields' ->> 'age' AS \"age\""
def test_group_by_json():
Query.update_forward_refs()
my_query = Query(
entity='signatories',
properties=[
Property(name='signatories.additional_data->organisation->>name'),
Count(property=Property(
name='signatories.additional_data->organisation->>name'))
],
group=[
Property(name='signatories.additional_data->organisation->>name')
])
sql = "SELECT signatories.additional_data -> 'organisation' ->> 'name', COUNT(signatories.additional_data -> " \
"'organisation' ->> 'name') FROM signatories GROUP BY signatories.additional_data -> 'organisation' ->> " \
"'name';"
assert str(my_query == sql)
def test_simple():
my_structure = Comparision(properties=[
Property(name='users.id'),
Array(values=[Constant(
value=1), Constant(value=2),
Constant(value=3)])
],
operation=Comparision.Operation.IN)
assert str(my_structure) == "(users.id IN (1, 2, 3))"
def test_simple():
my_structure = Between(
property=Property(name='transactions.happened_at'),
values=[
Constant(value='2020-01-01'),
Constant(value='2020-02-01')
]
)
assert str(my_structure) == "transactions.happened_at BETWEEN '2020-01-01' AND '2020-02-01'"
def test_simple():
my_join = Join(
entity='transactions',
type=Join.Type.LEFT,
on=Operator(operation=Operator.Operation.AND,
properties=[
Comparision(properties=[
Property(name='t.user_id'),
Property(name='users.id')
],
operation=Comparision.Operation.EQUAL),
Comparision(properties=[
Property(name='t.creator_id'),
Property(name='users.id')
],
operation=Comparision.Operation.NOT_EQUAL),
]),
alias="t")
sql = "LEFT JOIN transactions t ON ((t.user_id = users.id) AND (t.creator_id != users.id))"
assert str(my_join) == sql
def test_simple():
my_operator = Operator(
operation=Operator.Operation.AND,
properties=[
Comparision(
properties=[Property(name='users.age'),
Constant(value=15)],
operation=Comparision.Operation.GREATER_EQUAL),
Comparision(properties=[
Property(name='users.city'),
Array(values=[
Constant(value='Martin'),
Constant(value='Bratislava')
])
],
operation=Comparision.Operation.IN)
])
assert str(
my_operator
) == "((users.age >= 15) AND (users.city IN ('Martin', 'Bratislava')))"
def test_short_example():
my_query = Query(
entity='users',
properties=[
Property(name='users.name'),
Property(name='users.surname')
],
conditions=Comparision(
properties=[Property(name='users.age'),
Constant(value=15)],
operation=Comparision.Operation.GREATER_EQUAL))
sql = 'SELECT users.name, users.surname FROM users WHERE (users.age >= 15)'
assert str(my_query) == sql
json_string = my_query.json()
clone = Query.parse_raw(json_string)
assert clone == my_query
assert str(clone) == sql
def test_having():
Query.update_forward_refs()
my_query = Query(
entity='organisations',
properties=[
Property(name='organisations.id'),
Count(property=Property(name='campaigns.id'))
],
joins=[
Join(type=Join.Type.LEFT,
entity='campaigns',
on=Comparision(operation=Comparision.Operation.EQUAL,
properties=[
Property(name='organisations.id'),
Property(name='campaigns.organisation_id')
]))
],
conditions=Comparision(
operation=Comparision.Operation.LOWER,
properties=[
Property(name='organisations.created_at'),
Operator(operation=Operator.Operation.SUBTRACTION,
properties=[
Now(),
Interval(value=1, unit=Interval.Unit.MONTH)
])
]),
group=[Property(name='organisations.id')],
having=Comparision(operation=Comparision.Operation.EQUAL,
properties=[
Count(property=Property(name='campaigns.id')),
Constant(value=0)
]))
sql = "SELECT organisations.id, COUNT(campaigns.id) FROM organisations LEFT JOIN campaigns ON " \
"(organisations.id = campaigns.organisation_id) WHERE (organisations.created_at < " \
"(NOW() - INTERVAL '1 MONTH')) GROUP BY organisations.id HAVING (COUNT(campaigns.id) = 0)"
assert str(my_query) == sql
json_string = my_query.json()
clone = Query.parse_raw(json_string)
assert clone == my_query
assert str(clone) == sql
def test_example():
Query.update_forward_refs()
Operator.update_forward_refs()
my_query = Query(
entity='users',
properties=[
Concat(properties=[
Property(name='users.name'),
Constant(value=' '),
Property(name='users.surname')
],
alias='full_name'),
Avg(property=Property(name='transactions.value'),
alias='average_transaction_value')
],
joins=[
Join(entity='transactions',
type=Join.Type.LEFT,
on=Comparision(properties=[
Property(name='transactions.user_id'),
Property(name='users.id')
],
operation=Comparision.Operation.EQUAL))
],
conditions=Comparision(
properties=[Property(name='users.age'),
Constant(value=15)],
operation=Comparision.Operation.GREATER_EQUAL),
group=[
Property(name='users.id'),
],
)
sql = "SELECT CONCAT(users.name, ' ', users.surname) AS full_name, AVG(transactions.value) AS " \
'average_transaction_value FROM users LEFT JOIN transactions ON (transactions.user_id = users.id) WHERE ' \
'(users.age >= 15) GROUP BY users.id'
assert str(my_query) == sql
json_string = my_query.json()
clone = Query.parse_raw(json_string)
assert clone == my_query
assert str(clone) == sql
def test_json_parse(valid_instance: Property):
assert valid_instance.json(
) == '{"obj": "properties.Property", "name": "users.name", "alias": "name"}'
def test_simple():
Query.update_forward_refs()
my_query = Query(
entity='users',
properties=[
Property(name='users.name', alias='users_name'),
ConvertTimezone(property=Property(name='users.created_at'),
date_from=Constant(value='+00:00'),
date_to=Constant(value='Europe/Bratislava'),
alias='valid_timezone')
],
joins=[
Join(entity='transactions',
type=Join.Type.LEFT,
on=Operator(
operation=Operator.Operation.AND,
properties=[
Comparision(properties=[
Property(name='transactions.user_id'),
Property(name='users.id')
],
operation=Comparision.Operation.EQUAL),
Comparision(
properties=[
Property(name='transactions.creator_id'),
Property(name='users.id')
],
operation=Comparision.Operation.NOT_EQUAL),
]))
],
conditions=Operator(
operation=Operator.Operation.AND,
properties=[
Comparision(properties=[
Property(name='users.age'),
Constant(value=15)
],
operation=Comparision.Operation.GREATER_EQUAL),
Comparision(properties=[
Property(name='users.city'),
Array(values=[
Constant(value='Martin'),
Constant(value='Bratislava')
])
],
operation=Comparision.Operation.IN)
]),
group=[Property(name='users.email'),
Property(name='users.id')],
having=Comparision(
operation=Comparision.Operation.GREATER,
properties=[
Sum(property=Property(name='transactions.value')),
Constant(value=420)
]),
order=[
Order(property=Property(name='users.surname')),
Order(property=Property(name='users.name'),
kind=Order.Direction.DESC)
],
limit=Limit(limit=10, offset=4),
alias="my_query")
sql = "(SELECT users.name AS \"users_name\", CONVERT_TZ(users.created_at, '+00:00', 'Europe/Bratislava') " \
"AS valid_timezone FROM users " \
"LEFT JOIN transactions ON ((transactions.user_id = users.id) AND (transactions.creator_id != users.id)) " \
"WHERE ((users.age >= 15) AND (users.city IN ('Martin', 'Bratislava'))) " \
"GROUP BY users.email, users.id " \
"HAVING (SUM(transactions.value) > 420) " \
"ORDER BY users.surname ASC, users.name DESC " \
"LIMIT 10 OFFSET 4) AS my_query"
assert str(my_query) == sql
json_string = my_query.json()
clone = Query.parse_raw(json_string)
assert clone == my_query
assert str(clone) == sql
def test_parse_error():
with pytest.raises(ParseError):
Distinct(property=Property(name='users.name', alias="my_name"))
def test_escape():
my_property = Property(name='users."name', alias='--name')
assert str(my_property) == 'users.name AS "name"'
assert my_property.json(
) == '{"obj": "properties.Property", "name": "users.name", "alias": "name"}'
def valid_instance() -> Property:
return Property(name='users.name', alias='"name')
def test_simple():
my_order = Order(property=Property(name='users.name'))
assert str(my_order) == 'users.name ASC'
def test_simple():
my_distinct = Distinct(property=Property(name='users.name'))
assert str(my_distinct) == 'DISTINCT users.name'
def test_json_field():
json_property = Property(name='users.additional_data ->> title')
assert str(json_property) == "users.additional_data ->> 'title'"
def test_json_nested_field():
json_property = Property(
name='users.additional_data -> custom_fields ->> age')
assert str(
json_property) == "users.additional_data -> 'custom_fields' ->> 'age'"
def test_json_serialize(valid_instance: Property):
assert valid_instance == Property.parse_raw(
'{"obj": "properties.Property", "name": "users.name", "alias": "name"}'
)
def test_descending():
my_order = Order(property=Property(name='users.name'),
kind=Order.Direction.DESC)
assert str(my_order) == 'users.name DESC'