def test_high_lvl_join(self):
ACL.set_user(ACL.Users.get(username='******'))
customers = [
CustomersModel(id=1, name='Will Smith',
phone_number='111-222-333'),
CustomersModel(id=2, name='Tom Hanks', phone_number='999-888-777')
]
self.session.add_all(customers)
self.session.commit()
ACL.unset_user()
ACL.set_user(ACL.Users.get(username='******'))
orders = [
OrdersModel(id=1, customer_id=1, order_date='07-31-1998'),
OrdersModel(id=2, customer_id=1, order_date='08-31-1998'),
OrdersModel(id=3, customer_id=2, order_date='07-15-1998'),
OrdersModel(id=4, customer_id=2, order_date='08-15-1998'),
OrdersModel(id=5, customer_id=3, order_date='08-28-1998')
]
self.session.add_all(orders)
self.session.commit()
client = CustomersModel(id=3, name='James Bond', phone_number='007')
self.session.add(client)
self.session.commit()
result = self.session.query(CustomersModel, OrdersModel).join(
OrdersModel, CustomersModel.id == OrdersModel.customer_id).all()
self.assertEqual(result, [(client, orders[4])])
ACL.unset_user()
def test_same_lvl_groupby(self):
ACL.set_user(ACL.Users.get(username='******'))
customers = [
CustomersModel(id=1, name='Will Smith',
phone_number='111-222-333'),
CustomersModel(id=2, name='Tom Hanks', phone_number='999-888-777')
]
self.session.add_all(customers)
self.session.commit()
ACL.unset_user()
ACL.set_user(ACL.Users.get(username='******'))
orders = [
OrdersModel(id=1, customer_id=1, order_date='07-31-1998'),
OrdersModel(id=2, customer_id=1, order_date='08-31-1998'),
OrdersModel(id=3, customer_id=2, order_date='07-15-1998'),
OrdersModel(id=4, customer_id=2, order_date='08-15-1998'),
OrdersModel(id=5, customer_id=2, order_date='08-28-1998')
]
self.session.add_all(orders)
self.session.commit()
result = self.session.query(
CustomersModel.id, func.count(CustomersModel.id)).join(
OrdersModel,
CustomersModel.id == OrdersModel.customer_id).group_by(
CustomersModel.id).all()
self.assertEqual(result, [(1, 2), (2, 3)])
ACL.unset_user()
def test_low_lvl_join(self):
ACL.set_user(ACL.Users.get(username='******'))
customers = [
CustomersModel(id=1, name='Will Smith',
phone_number='111-222-333'),
CustomersModel(id=2, name='Tom Hanks', phone_number='999-888-777')
]
self.session.add_all(customers)
self.session.commit()
ACL.unset_user()
ACL.set_user(ACL.Users.get(username='******'))
orders = [
OrdersModel(id=1, customer_id=1, order_date='07-31-1998'),
OrdersModel(id=2, customer_id=1, order_date='08-31-1998'),
OrdersModel(id=3, customer_id=2, order_date='07-15-1998'),
OrdersModel(id=4, customer_id=2, order_date='08-15-1998'),
OrdersModel(id=5, customer_id=3,
order_date='08-28-1998') # dla zmyły
]
self.session.add_all(orders)
self.session.commit()
ACL.set_user(ACL.Users.get(username='******'))
join = [(customers[0], orders[0]), (customers[0], orders[1]),
(customers[1], orders[2]), (customers[1], orders[3])]
result = self.session.query(CustomersModel, OrdersModel).join(
OrdersModel, CustomersModel.id == OrdersModel.customer_id).all()
self.assertEqual(result, join)
ACL.unset_user()
def test_authorized_delete(self):
ACL.set_user(ACL.Users.get(username='******'))
low_level_objects = [
ExemplaryModel(id=1,
string_field='some_string',
integer_field=randrange(100000)),
ExemplaryModel(id=2,
string_field='some_string',
integer_field=randrange(100000)),
ExemplaryModel(id=3,
string_field='some_string',
integer_field=randrange(100000)),
ExemplaryModel(id=4,
string_field='some_string',
integer_field=randrange(100000)),
]
self.session.add_all(low_level_objects)
self.session.commit()
ACL.set_user(ACL.Users.get(username='******'))
object = self.session.query(ExemplaryModel).first()
self.session.delete(object)
self.session.commit()
ACL.unset_user()
ACL.set_user(ACL.Users.get(username='******'))
self.assertEqual(
self.session.query(ExemplaryModel).all(), low_level_objects[1:])
ACL.unset_user()
def test_delete_object_without_select(self):
# objects associated with root access level
root_level_objects = [
ExemplaryModel(id=1,
string_field='some_string',
integer_field=randrange(100000)),
ExemplaryModel(id=2,
string_field='some_string',
integer_field=randrange(100000)),
ExemplaryModel(id=3,
string_field='some_string',
integer_field=randrange(100000)),
ExemplaryModel(id=4,
string_field='some_string',
integer_field=randrange(100000)),
]
self.session.add_all(root_level_objects)
self.session.commit()
ACL.set_user(ACL.Users.get(username='******'))
# delete object with id = 1
self.session.query(ExemplaryModel).filter_by(id=1).delete()
self.session.commit()
after_deletion = self.session.query(ExemplaryModel).filter(
ExemplaryModel.id.in_([2, 3, 4]))
self.assertEqual(set(after_deletion),
set(self.session.query(ExemplaryModel).all()))
ACL.unset_user()
def test_delete_object_with_select(self):
# objects associated with root access level
root_level_objects = [
ExemplaryModel(id=1,
string_field='some_string',
integer_field=randrange(100000)),
ExemplaryModel(id=2,
string_field='some_string',
integer_field=randrange(100000)),
ExemplaryModel(id=3,
string_field='some_string',
integer_field=randrange(100000)),
ExemplaryModel(id=4,
string_field='some_string',
integer_field=randrange(100000)),
]
self.session.add_all(root_level_objects)
self.session.commit()
ACL.set_user(ACL.Users.get(username='******'))
# get first object (object with id = 1)
object = self.session.query(ExemplaryModel).get(1)
# delete object and commit changes to database
self.session.delete(object)
self.session.commit()
# create set corresponding to initial list without first object
after_deletion = set(root_level_objects) - {object}
# assert with select query result
self.assertEqual(after_deletion,
set(self.session.query(ExemplaryModel).all()))
ACL.unset_user()
def test_same_lvl_aggr(self):
ACL.set_user(ACL.Users.get(username='******'))
orders = [
OrdersModel(id=1, customer_id=1, order_date='07-31-1998'),
OrdersModel(id=2, customer_id=1, order_date='08-31-1998'),
OrdersModel(id=3, customer_id=2, order_date='07-15-1998'),
OrdersModel(id=4, customer_id=2, order_date='08-15-1998'),
OrdersModel(id=5, customer_id=3, order_date='08-28-1998')
]
self.session.add_all(orders)
self.session.commit()
ACL.unset_user()
ACL.set_user(ACL.Users.get(username='******'))
self.assertEqual(
self.session.query(func.count(OrdersModel.id)).scalar(), 5)
ACL.unset_user()
def test_same_lvl_update(self):
ACL.set_user(ACL.Users.get(username='******'))
customers = [
CustomersModel(id=1, name='Will Smith',
phone_number='111-222-333'),
CustomersModel(id=2, name='Tom Hanks', phone_number='999-888-777')
]
self.session.add_all(customers)
self.session.commit()
ACL.unset_user()
ACL.set_user(ACL.Users.get(username='******'))
self.session.query(CustomersModel).filter(
CustomersModel.id == 1).update({'name': 'Smill With'})
self.session.commit()
result = self.session.query(CustomersModel).first()
self.assertEqual(result.name, 'Smill With')
ACL.unset_user()
def test_high_lvl_having(self):
ACL.set_user(ACL.Users.get(username='******'))
orders = [
OrdersModel(id=1, customer_id=1, order_date='07-31-1998'),
OrdersModel(id=2, customer_id=1, order_date='08-31-1998'),
OrdersModel(id=3, customer_id=2, order_date='07-15-1998'),
OrdersModel(id=4, customer_id=2, order_date='08-15-1998')
]
self.session.add_all(orders)
self.session.commit()
ACL.unset_user()
ACL.set_user(ACL.Users.get(username='******'))
orders = [
OrdersModel(id=5, customer_id=2, order_date='09-15-1998'),
OrdersModel(id=6, customer_id=2, order_date='10-15-1998'),
]
self.session.add_all(orders)
self.session.commit()
ACL.unset_user()
ACL.set_user(ACL.Users.get(username='******'))
query = self.session.query(OrdersModel.customer_id, func.count(OrdersModel.customer_id)).\
group_by(OrdersModel.customer_id).having(func.count(OrdersModel.customer_id) > 2).all()
self.assertEqual(query, [])
ACL.unset_user()
def test_filter(self):
root_level_objects = [
ExemplaryModel(id=1,
string_field='some_string',
integer_field=randrange(100000)),
ExemplaryModel(id=2,
string_field='some_string',
integer_field=randrange(100000)),
ExemplaryModel(id=3,
string_field='some_string',
integer_field=randrange(100000)),
ExemplaryModel(id=4,
string_field='some_string',
integer_field=randrange(100000)),
]
self.session.add_all(root_level_objects)
self.session.commit()
ACL.set_user(ACL.Users.get(username='******'))
low_level_objects = [
ExemplaryModel(id=5,
string_field='some_string',
integer_field=randrange(100000)),
ExemplaryModel(id=6,
string_field='some_string',
integer_field=randrange(100000))
]
self.session.add_all(low_level_objects)
self.session.commit()
ACL.unset_user()
ACL.set_user(ACL.Users.get(username='******'))
self.assertEqual(
self.session.query(ExemplaryModel).filter(
ExemplaryModel.id > 2).all(), low_level_objects)
ACL.unset_user()
def test_low_lvl_subquery(self):
ACL.set_user(ACL.Users.get(username='******'))
customers = [
CustomersModel(id=1, name='Will Smith',
phone_number='111-222-333'),
CustomersModel(id=2, name='Tom Hanks', phone_number='999-888-777')
]
self.session.add_all(customers)
self.session.commit()
orders = [
OrdersModel(id=1, customer_id=1, order_date='07-31-1998'),
OrdersModel(id=2, customer_id=1, order_date='08-31-1998'),
OrdersModel(id=3, customer_id=2, order_date='07-15-1998'),
OrdersModel(id=4, customer_id=2, order_date='08-15-1998')
]
self.session.add_all(orders)
self.session.commit()
ACL.unset_user()
ACL.set_user(ACL.Users.get(username='******'))
orders = [
OrdersModel(id=5, customer_id=1, order_date='10-31-1998'),
OrdersModel(id=6, customer_id=2, order_date='10-15-1998'),
]
self.session.add_all(orders)
self.session.commit()
ACL.unset_user()
ACL.set_user(ACL.Users.get(username='******'))
subquery = self.session.query(
OrdersModel.customer_id,
func.max(OrdersModel.order_date).label('latest_order')).group_by(
OrdersModel.customer_id).subquery()
query = self.session.query(
CustomersModel.name, subquery.c.latest_order).join(
subquery, CustomersModel.id == subquery.c.customer_id).all()
self.assertEqual(query, [('Will Smith', '10-31-1998'),
('Tom Hanks', '10-15-1998')])
ACL.unset_user()
def test_join2(self):
ACL.set_user(ACL.Users.get(username='******'))
customers = [
CustomersModel(id=1, name='Will Smith',
phone_number='111-222-333'),
CustomersModel(id=2, name='Tom Hanks', phone_number='999-888-777')
]
orders = [
OrdersModel(id=1, customer_id=1, order_date='2021-01-01'),
OrdersModel(id=2, customer_id=2, order_date='2021-01-01'),
OrdersModel(id=3, customer_id=1, order_date='2021-01-01')
]
articles = [
ArticlesModel(id=1, order_id=1, box_id=1, quantity=10),
ArticlesModel(id=2, order_id=1, box_id=4, quantity=5),
ArticlesModel(id=3, order_id=2, box_id=2, quantity=20),
ArticlesModel(id=4, order_id=3, box_id=2, quantity=12),
]
self.session.add_all(customers)
self.session.commit()
self.session.add_all(orders)
self.session.commit()
self.session.add_all(articles)
self.session.commit()
ACL.unset_user()
ACL.set_user(ACL.Users.get(username='******'))
customers = [
CustomersModel(id=3, name='Mel Gibson', phone_number='444-555-666')
]
orders = [
OrdersModel(id=4, customer_id=3, order_date='2021-01-01'),
OrdersModel(id=5, customer_id=3, order_date='2021-01-01'),
OrdersModel(id=6, customer_id=3, order_date='2021-01-01'),
OrdersModel(id=7, customer_id=2, order_date='2021-01-01')
]
articles = [
ArticlesModel(id=5, order_id=4, box_id=1, quantity=10),
ArticlesModel(id=6, order_id=5, box_id=4, quantity=5),
ArticlesModel(id=7, order_id=6, box_id=2, quantity=15),
ArticlesModel(id=8, order_id=7, box_id=2, quantity=100),
]
self.session.add_all(customers)
self.session.commit()
self.session.add_all(orders)
self.session.commit()
self.session.add_all(articles)
self.session.commit()
ACL.unset_user()
ACL.set_user(ACL.Users.get(username='******'))
result = self.session.query(CustomersModel.name, func.avg(ArticlesModel.quantity)).\
join(OrdersModel, CustomersModel.id == OrdersModel.customer_id).\
join(ArticlesModel, OrdersModel.id == ArticlesModel.order_id).\
group_by(CustomersModel.id).all()
self.assertEqual(result, [('Will Smith', 9), ('Tom Hanks', 20)])
ACL.unset_user()
ACL.set_user(ACL.Users.get(username='******'))
result = self.session.query(CustomersModel.name, func.avg(ArticlesModel.quantity)).\
join(OrdersModel, CustomersModel.id == OrdersModel.customer_id).\
join(ArticlesModel, OrdersModel.id == ArticlesModel.order_id).\
group_by(CustomersModel.id).all()
self.assertEqual(result, [('Will Smith', 9), ('Tom Hanks', 60),
('Mel Gibson', 10)])
ACL.unset_user()
def test_get_objects(self):
# objects associated with root access level
root_level_objects = [
ExemplaryModel(id=1,
string_field='some_string',
integer_field=randrange(100000)),
ExemplaryModel(id=2,
string_field='some_string',
integer_field=randrange(100000)),
ExemplaryModel(id=3,
string_field='some_string',
integer_field=randrange(100000)),
ExemplaryModel(id=4,
string_field='some_string',
integer_field=randrange(100000)),
]
self.session.add_all(root_level_objects)
self.session.commit()
# user at one of lowest access-levels
some_user = ACL.Users.get(username='******')
other_level_objects = [
ExemplaryModel(id=5,
string_field='some_string',
integer_field=randrange(100000)),
ExemplaryModel(id=6,
string_field='some_string',
integer_field=randrange(100000)),
ExemplaryModel(id=7,
string_field='some_string',
integer_field=randrange(100000)),
ExemplaryModel(id=8,
string_field='some_string',
integer_field=randrange(100000)),
ExemplaryModel(id=9,
string_field='some_string',
integer_field=randrange(100000)),
]
ACL.set_user(some_user)
self.session.add_all(other_level_objects)
self.session.commit()
ACL.unset_user()
# set admin user
ACL.set_user(ACL.Users.get(username='******'))
# check if all added entries are accessible for admin (root access-level user)
self.assertEqual(
self.session.query(ExemplaryModel).all(),
root_level_objects + other_level_objects)
ACL.unset_user()
# set exemplary user
ACL.set_user(some_user)
# check if entries added by exemplary user are accessible for him
self.assertEqual(
self.session.query(ExemplaryModel).all(), other_level_objects)
ACL.unset_user()
# set other exemplary user at same access-level
ACL.set_user(ACL.Users.get(username='******'))
self.assertEqual(
self.session.query(ExemplaryModel).all(), other_level_objects)
ACL.unset_user()
# set other exemplary user at different access-level
ACL.set_user(ACL.Users.get(username='******'))
# this user shouldn't have access to any entries
self.assertEqual(self.session.query(ExemplaryModel).all(), [])
self.assertNotEqual(self.session.query(ExemplaryModel),
other_level_objects)
ACL.unset_user()
def decorated_function(*args, **kwargs):
credentials = http_auth.current_user()
ACL.set_user(ACL.Users.get(**credentials))
response = f(*args, **kwargs)
ACL.unset_user()
return response