def test_lower_than(dbsession):
q = ZdbQuery(Products, session=dbsession)
q = q.filter(Products.price <= 1500)
sql = query_to_sql(q)
assert validate_sql(sql,
target="""
SELECT products.id, products.name, products.keywords, products.short_summary, products.long_description, products.price, products.inventory_count, products.discontinued, products.availability_date, products.author
FROM products
WHERE zdb('products', ctid) ==> 'price <= 1500'
""") is True
results = q.all()
assert len(results) == 1
assert results[0].id == 2
#
q = ZdbQuery(Products, session=dbsession)
q = q.filter(Products.price <= 10000)
q = q.filter(Products.inventory_count <= 50)
sql = query_to_sql(q)
assert validate_sql(sql,
target="""
SELECT products.id, products.name, products.keywords, products.short_summary, products.long_description, products.price, products.inventory_count, products.discontinued, products.availability_date, products.author
FROM products
WHERE zdb('products', ctid) ==> 'price <= 10000' AND products.inventory_count <= 50
""") is True
results = q.all()
assert len(results) == 2
assert results[0].id == 1
def test_between(dbsession):
# integers
q = ZdbQuery(Products, session=dbsession)
q = q.filter(Products.price.between(9000, 20000))
sql = query_to_sql(q)
assert validate_sql(sql,
target="""
SELECT products.id, products.name, products.keywords, products.short_summary, products.long_description, products.price, products.inventory_count, products.discontinued, products.availability_date, products.author
FROM products
WHERE zdb('products', ctid) ==> 'price:9000 /to/ 20000'
""") is True
results = q.all()
assert len(results) == 2
assert results[0].price == 9900
assert results[1].price == 17000
# floats
q = ZdbQuery(Products, session=dbsession)
q = q.filter(Products.price.between(9899.5, 9901.75))
sql = query_to_sql(q)
assert validate_sql(sql,
target="""
SELECT products.id, products.name, products.keywords, products.short_summary, products.long_description, products.price, products.inventory_count, products.discontinued, products.availability_date, products.author
FROM products
WHERE zdb('products', ctid) ==> 'price:9899.5 /to/ 9901.75'
""") is True
results = q.all()
assert len(results) == 1
assert results[0].price == 9900
# invalid input
q = ZdbQuery(Products, session=dbsession)
q = q.filter(Products.price.between(9899.5, "10000"))
with pytest.raises(InvalidParameterException):
q.all()
def test_like_regexp(dbsession):
q = ZdbQuery(Products, session=dbsession)
q = q.filter(Products.long_description.like(re.compile(r"c[a-z]pable")))
sql = query_to_sql(q)
assert validate_sql(sql, target="""
SELECT products.id, products.name, products.keywords, products.short_summary, products.long_description, products.price, products.inventory_count, products.discontinued, products.availability_date, products.author
FROM products
WHERE zdb('products', ctid) ==> 'long_description:~"c[a-z]pable"'
""") is True
results = q.all()
assert len(results) == 1
assert "capable" in results[0].long_description
def test_equals_not(dbsession):
q = ZdbQuery(Products, session=dbsession)
q = q.filter(Products.author != "foo")
sql = query_to_sql(q)
assert validate_sql(sql,
target="""
SELECT products.id, products.name, products.keywords, products.short_summary, products.long_description, products.price, products.inventory_count, products.discontinued, products.availability_date, products.author
FROM products
WHERE zdb('products', ctid) ==> 'author != "foo"'
""") is True
results = q.all()
assert len(results) == 2
assert results[0].author == "admin"