def test_prepare_sql_stmt_force(self):
"""
Given a SQL statement has already been prepared in the database
When prepare_sql_stmt is called for the same function
And force is False
Then a StatementAlreadyPreparedException error will be raised
---
Given a SQL statement has already been prepared in the database
When prepare_sql_stmt is called for the same function
And force is True
Then the existing statement will be deallocated
And the statement will be re-prepared
"""
psc = PreparedStatementController()
psc.register_sql("gen_sql", lambda: None)
with patch.object(PreparedStatement, "prepare", return_value=None):
psc.prepare_sql_stmt("gen_sql", force=False)
self.assertTrue("gen_sql" in psc.prepared_statements)
with self.assertRaises(StatementAlreadyPreparedException):
psc.prepare_sql_stmt("gen_sql", force=False)
with patch.object(PreparedStatement, "prepare",
return_value=None) as mock_prepare:
with patch.object(PreparedStatement,
"deallocate",
return_value=None) as mock_deallocate:
psc.prepare_sql_stmt("gen_sql", force=True)
mock_deallocate.assert_called_once()
mock_prepare.assert_called_once()
def test_related_values_list_on_result(self):
"""
Given an ORM query is prepared and executed
When .values_list() is called on the resulting query set
And the named parameter is on a related model
And flat=True
Then only the requested values should be returned in a flattened list
"""
Animal.objects.update_or_create(name="Tony", species=self.tiger)
Animal.objects.update_or_create(name="Sheer Kahn", species=self.tiger)
def all_animal_species():
return Animal.prepare.order_by("pk")
PreparedStatementController().register_qs("all_animal_species",
all_animal_species)
PreparedStatementController().prepare_qs_stmt("all_animal_species",
force=True)
qs = execute_stmt("all_animal_species")
qs = qs.values_list("species_id", flat=True)
self.assertTrue(isinstance(qs, PreparedStatementQuerySet))
self.assertEqual(len(qs), 2)
self.assertEqual(qs[0], self.tiger.id)
self.assertEqual(qs[1], self.tiger.id)
def test_related_id_filter(self):
"""
Given an ORM query is prepared with a filter on the id of a related model
And the filter is a Placeholder
When the prepared statement is executed with a keyword argument for the filter
And the filter is a related model id
Then only the records which match the filter will be returned in a query set
"""
Animal.objects.update_or_create(name="Tigger", species=self.tiger)
Animal.objects.update_or_create(name="Jack Daw", species=self.crow)
Animal.objects.update_or_create(name="C. Orvid", species=self.crow)
Animal.objects.update_or_create(name="Koi", species=self.carp)
def filter_animals():
return Animal.prepare.filter(
species_id=Placeholder("species_id")).order_by("id")
PreparedStatementController().register_qs("filter_animals",
filter_animals)
PreparedStatementController().prepare_qs_stmt("filter_animals",
force=True)
qs = execute_stmt("filter_animals", species_id=self.crow.id)
self.assertTrue(isinstance(qs, PreparedStatementQuerySet))
self.assertEqual(len(qs), 2)
self.assertEqual(qs[0].name, "Jack Daw")
self.assertEqual(qs[1].name, "C. Orvid")
def test_reprepare_query_in_transaction(self):
"""
Given an ORM query is prepared
And a database transaction is started
And the query has been deallocated in the database
When the query is executed
Then it should re-prepare without errors
And it should execute as expected
"""
def all_species():
return Species.prepare.all()
PreparedStatementController().register_qs("all_species", all_species)
PreparedStatementController().prepare_qs_stmt("all_species",
force=True)
# deallocate the query to simulate changing database session
PreparedStatementController(
).prepared_statements["all_species"].deallocate()
with transaction.atomic():
qs = execute_stmt("all_species")
cxn = transaction.get_connection()
self.assertTrue(cxn.in_atomic_block)
self.assertEqual(len(qs), 3)
def test_prefetch_related_on_result(self):
"""
Given an ORM query is prepared
And it has been succesfully executed
When prefetch_related() is called on the resulting query set
Then an extra query will be run to prefetvh the related objects
And no further queries will be run when the related objects are accessed from the original query set
"""
Animal.objects.update_or_create(name="Tony", species=self.tiger)
Animal.objects.update_or_create(name="Sheer Kahn", species=self.tiger)
def qry():
return Species.prepare.filter(name=Placeholder("name"))
PreparedStatementController().register_qs("qry", qry)
PreparedStatementController().prepare_qs_stmt("qry", force=True)
qs = execute_stmt("qry", name="Tiger")
# Now add the prefetch related, which should execute one query.
with self.assertNumQueries(1):
qs = qs.prefetch_related("animal_set")
# Access the animals on the tiger species - as they are prefetched no more queries should be run!
with self.assertNumQueries(0):
tigers = qs[0].animal_set.all()
self.assertEqual(len(tigers), 2)
self.assertEqual(set([tigers[0].name, tigers[1].name]),
set(["Tony", "Sheer Kahn"]))
def test_doubly_related_id_filter(self):
"""
Given an ORM query is prepared with a filter on the id of a related model
And the filter is a Placeholder
When the prepared statement is executed with a keyword argument for the filter
And the filter is related by two foreign keys
Then only the records which match the filter will be returned in a query set
"""
a1 = Animal.objects.create(name="Jack Daw", species=self.crow)
a2 = Animal.objects.create(name="C. Orvid", species=self.crow)
a3 = Animal.objects.create(name="Tigger", species=self.tiger)
Items.objects.update_or_create(description="bird cage", animal=a1)
Items.objects.update_or_create(description="whistle", animal=a1)
Items.objects.update_or_create(description="bag of seeds", animal=a2)
Items.objects.update_or_create(description="honey", animal=a3)
def filter_items():
return Items.prepare.filter(
animal__species_id=Placeholder("species_id")).order_by("id")
PreparedStatementController().register_qs("filter_items", filter_items)
PreparedStatementController().prepare_qs_stmt("filter_items",
force=True)
qs = execute_stmt("filter_items", species_id=self.crow.id)
self.assertTrue(isinstance(qs, PreparedStatementQuerySet))
self.assertEqual(len(qs), 3)
self.assertEqual(qs[0].description, "bird cage")
self.assertEqual(qs[1].description, "whistle")
self.assertEqual(qs[2].description, "bag of seeds")
def test_is_singleton(self):
"""
Given a PreparedStatementController is instantiated
When another PreparedStatementController is instantiated
Then they should both point to the same singleton instance
"""
psc1 = PreparedStatementController()
psc2 = PreparedStatementController()
self.assertTrue(psc1 is psc2)
def test_prepare_qs_stmt_unregistered(self):
"""
Given a function that generates an ORM query has not been registered with the PreparedStatementController
When prepare_sql_stmt is called for that function
Then a StatementNotRegistered error will be raised
"""
psc = PreparedStatementController()
with self.assertRaises(StatementNotRegistered):
psc.prepare_qs_stmt("unregistered_qs", force=False)
def test_execute_unprepared_stmt(self):
"""
Given a statement has not been prepared in the database
When execute() is called for that statement
Then a StatementNotPreparedException error will be raised
"""
psc = PreparedStatementController()
self.assertFalse("gen_sql" in psc.prepared_statements)
with self.assertRaises(StatementNotPreparedException):
psc.execute("gen_qs", force=False)
def test_count_prepared_stmt_result(self):
"""
Given an ORM query is prepared
And it has been succesfully executed
When count() is called on the resulting query set
Then the number of rows in the query set is returned
"""
PreparedStatementController().register_qs(
"all_species", lambda: Species.prepare.all())
PreparedStatementController().prepare_qs_stmt("all_species",
force=True)
qs = execute_stmt("all_species")
self.assertEqual(qs.count(), 3)
def test_prepare_prefetch_related(self):
"""
Given an ORM query is written with a prefetch_related() function
When the query is prepared
Then a PreparedQueryNotSupported error will be raised
"""
def will_fail():
return Species.prepare.all().prefetch_related("animal_set")
PreparedStatementController().register_qs("will_fail", will_fail)
with self.assertRaises(PreparedQueryNotSupported):
PreparedStatementController().prepare_qs_stmt("will_fail",
force=True)
def test_register_qs(self):
"""
Given a function that returns an ORM query
When register_qs is called with that function as one of the arguments
Then that function should be added to the qs_generating_functions dict
"""
def gen_qs():
pass
psc = PreparedStatementController()
psc.register_qs("gen_qs", gen_qs)
self.assertFalse("gen_qs" in psc.sql_generating_functions)
self.assertTrue("gen_qs" in psc.qs_generating_functions)
self.assertTrue(psc.qs_generating_functions["gen_qs"] is gen_qs)
def test_last_prepared_stmt_result(self):
"""
Given an ORM query is prepared
And it has been succesfully executed
When last() is called on the resulting query set
Then the last record relative to the ordering of the prepared query is returned as a model instance
"""
PreparedStatementController().register_qs(
"all_species", lambda: Species.prepare.all().order_by("pk"))
PreparedStatementController().prepare_qs_stmt("all_species",
force=True)
qs = execute_stmt("all_species")
last = qs.last()
self.assertEqual(last.name, self.crow.name)
def test_prepare_values_list(self):
"""
Given an ORM query is prepared with a values_list() function
When the query is prepared
Then an PreparedQueryNotSupported exception should be raised
"""
def all_species():
return Species.prepare.order_by("pk").values_list("name",
flat=True)
with self.assertRaises(PreparedQueryNotSupported):
PreparedStatementController().register_qs("all_species",
all_species)
PreparedStatementController().prepare_qs_stmt("all_species",
force=True)
def test_filter_with_constant(self):
"""
Given an ORM query is prepared with a filter that has no placeholders
When the prepared statement is executed without any keyword arguments
Then only the records which match the filter will be returned in a query set
"""
def filter_species():
return Species.prepare.filter(pk=self.crow.pk)
PreparedStatementController().register_qs("filter_species",
filter_species)
PreparedStatementController().prepare_qs_stmt("filter_species",
force=True)
qs = execute_stmt("filter_species")
self.assertEqual(qs[0].name, self.crow.name)
def test_prepare_count(self):
"""
Given an ORM query is prepared with a count() function
When the prepared statement is executed
Then an integer value will be returned
And it will be the number of rows that match the query
"""
def count_species():
return Species.prepare.count()
PreparedStatementController().register_qs("count", count_species)
PreparedStatementController().prepare_qs_stmt("count", force=True)
qs = execute_stmt("count")
self.assertTrue(isinstance(qs, int))
self.assertEqual(qs, 3)
def test_prepare_last(self):
"""
Given an ORM query is prepared with a last() function
When the prepared statement is executed
Then a single model instance will be returned
And it will be the model instance with the highest primary key value
"""
def last_species():
return Species.prepare.last()
PreparedStatementController().register_qs("last", last_species)
PreparedStatementController().prepare_qs_stmt("last", force=True)
qs = execute_stmt("last")
self.assertTrue(isinstance(qs, Species))
self.assertEqual(qs.name, self.crow.name)
def prepare_sql(func):
"""
Prepare an SQL statement in the database. The function supplied must return an SQL query which will be prepared in
the database. The function return will be overwritten to supply the name of prepared statement so it can easily
be called:
@prepare_sql
def count_trades():
return "select count(*) from trades_parent_trade;"
rows = execute_stmt(count_trades())
"""
stmt_name = _make_stmt_name(func)
PreparedStatementController().register_sql(stmt_name, func)
PreparedStatementController().prepare_sql_stmt(stmt_name, force=False)
return lambda: stmt_name
def test_query_in_transaction(self):
"""
Given an ORM query is prepared
And a database transaction is started
When the query is executed
Then it should execute as expected
"""
def all_species():
return Species.prepare.all()
PreparedStatementController().register_qs("all_species", all_species)
PreparedStatementController().prepare_qs_stmt("all_species",
force=True)
with transaction.atomic():
qs = execute_stmt("all_species")
self.assertEqual(len(qs), 3)
def prepare_qs(func):
"""
Prepare an ORM statement in the database. The function supplied must return a django queryset query which will be
prepared in the database. The function return will be overwritten to supply the name of prepared statement so it
can easily be called:
@prepare_qs
def get_trades():
return Parent_trades.objects.filter(id__in=PLACEHOLDER_LIST)
rows = execute_stmt(get_trades(), [1, 2, 3])
"""
stmt_name = _make_stmt_name(func)
PreparedStatementController().register_qs(stmt_name, func)
PreparedStatementController().prepare_qs_stmt(stmt_name, force=False)
return lambda: stmt_name
def test_prepare_get(self):
"""
Given an ORM query is prepared with a get() function
When the prepared statement is executed
Then a single model instance will be returned
"""
def get_species():
return Species.prepare.get(name=Placeholder("name"))
PreparedStatementController().register_qs("get_species", get_species)
PreparedStatementController().prepare_qs_stmt("get_species",
force=True)
qs = execute_stmt("get_species", name="Carp")
self.assertTrue(isinstance(qs, Species))
self.assertEqual(qs.name, self.carp.name)
def test_prepare_all(self):
"""
Given an ORM query is prepared with the all() function
When the prepared statement is executed
Then all records from the model will be returned in a query set
"""
def all_species():
return Species.prepare.all().order_by("pk")
PreparedStatementController().register_qs("all_species", all_species)
PreparedStatementController().prepare_qs_stmt("all_species",
force=True)
qs = execute_stmt("all_species")
self.assertTrue(isinstance(qs, PreparedStatementQuerySet))
self.assertEqual(len(qs), 3)
self.assertTrue(isinstance(qs[0], Species))
self.assertEqual(qs[0].name, self.tiger.name)
def test_prepare_icontains(self):
"""
Given an ORM query is prepared with an `__icontains` filter
When the prepared statement is executed with a keyword argument for the filter
Then only the records which contain the filter will be returned in a query set
"""
def filter_species_like():
return Species.prepare.filter(name__icontains=Placeholder("name"))
PreparedStatementController().register_qs("filter_species_like",
filter_species_like)
PreparedStatementController().prepare_qs_stmt("filter_species_like",
force=True)
qs = execute_stmt("filter_species_like", name="car")
self.assertEqual(len(qs), 1)
self.assertTrue(isinstance(qs[0], Species))
self.assertEqual(qs[0].name, self.carp.name)
def test_filter_wth_mixed_params(self):
"""
Given an ORM query is prepared with a filter that has both placeholders and contant value filters
When the prepared statement is executed with a keyword arguments for placehlder filters
Then only the records which match alls filter will be returned in a query set
"""
def filter_species():
return Species.prepare.filter(
Q(pk=self.crow.pk) | Q(pk=Placeholder("pk"))).order_by("pk")
PreparedStatementController().register_qs("filter_species",
filter_species)
PreparedStatementController().prepare_qs_stmt("filter_species",
force=True)
qs = execute_stmt("filter_species", pk=self.tiger.pk)
self.assertEqual(len(qs), 2)
self.assertEqual(qs[0].name, self.tiger.name)
self.assertEqual(qs[1].name, self.crow.name)
def test_all_params_have_unique_names(self):
"""
Given an ORM query is created with a filter that has multiple placeholders with non-unique names
When the query is prepared
Then a NameError will be raised
And the error message will be "Repeated placeholder name: {}. All placeholders in a query must have unique names."
"""
def filter_species():
return Species.prepare.filter(
Q(pk=Placeholder("pk")) | Q(pk=Placeholder("pk")))
PreparedStatementController().register_qs("filter_species",
filter_species)
with self.assertRaises(NameError) as ctx:
PreparedStatementController().prepare_qs_stmt("filter_species",
force=True)
self.assertEqual(
str(ctx.exception),
"Repeated placeholder name: pk. All placeholders in a query must have unique names."
)
def test_prepare_all(self):
"""
Given a set of sql and qs generating functions that have been registered with the PreparedStatementController
When prepare_all() is called
Then prepare_sql_stmt and prepare_qs_stmt should be called for each registered function as appropriate
"""
psc = PreparedStatementController()
psc.register_sql("gen_sql1", lambda: None)
psc.register_sql("gen_sql2", lambda: None)
psc.register_sql("gen_sql3", lambda: None)
psc.register_qs("gen_qs1", lambda: Species.prepare.all())
psc.register_qs("gen_qs2", lambda: Species.prepare.all())
with patch.object(PreparedORMStatement, "prepare",
return_value=None) as mock_orm_prepare:
with patch.object(PreparedStatement, "prepare",
return_value=None) as mock_sql_prepare:
psc.prepare_all()
self.assertEqual(mock_sql_prepare.call_count, 3)
self.assertEqual(mock_orm_prepare.call_count, 2)
def test_prepare_filter(self):
"""
Given an ORM query is prepared with a filter
And the filter is a Placeholder
When the prepared statement is executed with a keyword argument for the filter
Then only the records which match the filter will be returned in a query set
"""
def filter_species():
return Species.prepare.filter(name=Placeholder("name"))
PreparedStatementController().register_qs("filter_species",
filter_species)
PreparedStatementController().prepare_qs_stmt("filter_species",
force=True)
qs = execute_stmt("filter_species", name="Carp")
self.assertTrue(isinstance(qs, PreparedStatementQuerySet))
self.assertEqual(len(qs), 1)
self.assertTrue(isinstance(qs[0], Species))
self.assertEqual(qs[0].name, self.carp.name)
def test_filter_too_many_params(self):
"""
Given an ORM query is prepared with a filter that has one or more placeholders
When the prepared statement is executed with keywoird arguments that do not match the given placeholder names
Then a ValueError will be raised
And the error message will be "Unknown parameters supplied for prepared statement: {}"
"""
def filter_species():
return Species.prepare.filter(
Q(pk=self.crow.pk) | Q(pk=Placeholder("pk"))).order_by("pk")
PreparedStatementController().register_qs("filter_species",
filter_species)
PreparedStatementController().prepare_qs_stmt("filter_species",
force=True)
with self.assertRaises(ValueError) as ctx:
qs = execute_stmt("filter_species", pk=1, pk2=2, pk3=3)
self.assertEqual(
str(ctx.exception),
"Unknown parameters supplied for prepared statement: pk2 , pk3")
def test_filter_not_enough_params(self):
"""
Given an ORM query is prepared with a filter that has placeholders
When the prepared statement is executed without any keyword arguments
Then a ValueError will be raised
And the error message will be "Not enough parameters supplied to execute prepared statement"
"""
def filter_species():
return Species.prepare.filter(
Q(pk=self.crow.pk) | Q(pk=Placeholder("pk")))
PreparedStatementController().register_qs("filter_species",
filter_species)
PreparedStatementController().prepare_qs_stmt("filter_species",
force=True)
# And again with no params
with self.assertRaises(ValueError) as ctx:
qs = execute_stmt("filter_species")
self.assertEqual(
str(ctx.exception),
"Not enough parameters supplied to execute prepared statement")
def test_named_values_list_on_result(self):
"""
Given an ORM query is prepared and executed
When .values_list() is called on the resulting query set
And named=True
Then only the requested values should be returned as a list of named tuples
"""
def all_species():
return Species.prepare.order_by("pk")
PreparedStatementController().register_qs("all_species", all_species)
PreparedStatementController().prepare_qs_stmt("all_species",
force=True)
qs = execute_stmt("all_species")
qs = qs.values_list("name", named=True)
self.assertTrue(isinstance(qs, PreparedStatementQuerySet))
self.assertEqual(len(qs), 3)
self.assertEqual(qs[0].name, self.tiger.name)
self.assertEqual(qs[1].name, self.carp.name)
self.assertEqual(qs[2].name, self.crow.name)
