def test_filter_with_join(self):
query_without_params = '''
SELECT (1) AS a
FROM change_request
INNER JOIN employment_type_change
ON ( change_request.employmentTypeChange_id = employment_type_change.id )
WHERE (change_request.employee_id = %s AND change_request.isApplied = %s
AND employment_type_change.employmentType = %s)
LIMIT 1
'''
table_names, _, where_equality_key = _parse_sql_for_tables_and_eq(
query_without_params)
self.assertListEqual(table_names,
['change_request', 'employment_type_change'])
self.assertEqual(where_equality_key, '')
django_stack_trace = [
StackTraceElement('django.db.models.sql.compiler', 'execute_sql',
None),
StackTraceElement('django.db.models.sql.compiler', 'has_results',
None),
StackTraceElement('django.db.models.sql.query', 'has_results',
None),
StackTraceElement('django.db.models.query', 'exists', None)
]
query_signature: QuerySignature = QuerySignature(
query_without_params, (), django_stack_trace, 'default')
self.assertEqual(query_signature.analysis,
QuerySignatureAnalyzeResult.FILTER)
def test_filter(self):
query_without_params = '''
SELECT company_rate_version.id, company_rate_version.planId, company_rate_version.companyHealthEnrollmentId,
company_rate_version.companyId, company_rate_version.quoteParams
FROM company_rate_version
WHERE (company_rate_version.companyId IN (%s) AND company_rate_version.lineOfCoverage = %s
AND company_rate_version.planId IN (%s))
'''
table_names, _, where_equality_key = _parse_sql_for_tables_and_eq(
query_without_params)
self.assertListEqual(table_names, ['company_rate_version'])
self.assertEqual(where_equality_key, '')
django_stack_trace = [
StackTraceElement('django.db.models.sql.compiler', 'execute_sql',
None),
StackTraceElement('django.db.models.sql.compiler', 'results_iter',
None),
StackTraceElement('django.db.models.query', 'iterator', None),
StackTraceElement('django.db.models.query', '_fetch_all', None),
StackTraceElement('django.db.models.query', '__iter__', None)
]
query_signature: QuerySignature = QuerySignature(
query_without_params, (), django_stack_trace, 'default')
self.assertEqual(query_signature.analysis,
QuerySignatureAnalyzeResult.FILTER)
def test_filter_exists(self):
query_without_params = '''
SELECT (1) AS a FROM company_health_enrollment
WHERE (company_health_enrollment.lineOfCoverage = %s AND company_health_enrollment.company_id = %s
AND company_health_enrollment.isActive = %s AND company_health_enrollment.isEnrollmentComplete = %s)
LIMIT 1
'''
table_names, _, where_equality_key = _parse_sql_for_tables_and_eq(
query_without_params)
self.assertListEqual(table_names, ['company_health_enrollment'])
self.assertEqual(where_equality_key, '')
django_stack_trace = (StackTraceElement(
'django.db.models.sql.compiler', 'execute_sql', None),
StackTraceElement(
'django.db.models.sql.compiler',
'has_results', None),
StackTraceElement('django.db.models.sql.query',
'has_results', None),
StackTraceElement('django.db.models.query',
'exists', None))
query_signature: QuerySignature = QuerySignature(
query_without_params, (), django_stack_trace, 'default')
self.assertEqual(query_signature.analysis,
QuerySignatureAnalyzeResult.FILTER)
def test_select_related_2(self):
query_without_params = '''
SELECT carrier.id, carrier.carrierID, carrier.name, carrier.displayName, carrier.state,
carrier.newHireApprovalProcessingDays
FROM carrier WHERE carrier.id = %s
LIMIT 21
'''
table_names, _, where_equality_key = _parse_sql_for_tables_and_eq(
query_without_params)
self.assertListEqual(table_names, ['carrier'])
self.assertEqual(where_equality_key, 'carrier.id')
django_stack_trace = [
StackTraceElement('django.db.models.sql.compiler', 'execute_sql',
None),
StackTraceElement('django.db.models.sql.compiler', 'results_iter',
None),
StackTraceElement('django.db.models.query', 'iterator', None),
StackTraceElement('django.db.models.query', '_fetch_all', None),
StackTraceElement('django.db.models.query', '__len__', None),
StackTraceElement('django.db.models.query', 'get', None),
StackTraceElement('django.db.models.fields.related_descriptors',
'get_object', None)
]
query_signature: QuerySignature = QuerySignature(
query_without_params, (), django_stack_trace, 'default')
self.assertEqual(query_signature.analysis,
QuerySignatureAnalyzeResult.MISSING_SELECT_RELATED)
def test_filter_first(self):
query_without_params = '''
SELECT employee_health_enrollment.id, employee_health_enrollment.employee_id,
employee_health_enrollment.createdAt, employee_health_enrollment.version_id,
employee_health_enrollment.premiumsMap, employee_health_enrollment.progress
FROM employee_health_enrollment
WHERE (employee_health_enrollment.employee_id = %s AND employee_health_enrollment.type = %s
AND employee_health_enrollment.isActive = %s AND employee_health_enrollment.coverage_type = %s)
ORDER BY employee_health_enrollment.id ASC LIMIT 1
'''
table_names, _, where_equality_key = _parse_sql_for_tables_and_eq(
query_without_params)
self.assertListEqual(table_names, ['employee_health_enrollment'])
self.assertEqual(where_equality_key, '')
django_stack_trace = [
StackTraceElement('django.db.models.sql.compiler', 'execute_sql',
None),
StackTraceElement('django.db.models.sql.compiler', 'results_iter',
None),
StackTraceElement('django.db.models.query', 'iterator', None),
StackTraceElement('django.db.models.query', '_fetch_all', None),
StackTraceElement('django.db.models.query', '__iter__', None),
StackTraceElement('django.db.models.query', '__getitem__', None),
StackTraceElement('django.db.models.query', 'first', None)
]
query_signature: QuerySignature = QuerySignature(
query_without_params, (), django_stack_trace, 'default')
self.assertEqual(query_signature.analysis,
QuerySignatureAnalyzeResult.FILTER)
def test_prefetch_related_2(self):
query_without_params = '''
SELECT employee_health_enrollment.id, employee_health_enrollment.employee_id,
employee_health_enrollment.progress
FROM employee_health_enrollment
WHERE employee_health_enrollment.employee_id = %s
'''
table_names, _, where_equality_key = _parse_sql_for_tables_and_eq(
query_without_params)
self.assertListEqual(table_names, ['employee_health_enrollment'])
self.assertEqual(where_equality_key,
'employee_health_enrollment.employee_id')
django_stack_trace = [
StackTraceElement('django.db.models.sql.compiler', 'execute_sql',
None),
StackTraceElement('django.db.models.sql.compiler', 'results_iter',
None),
StackTraceElement('django.db.models.query', 'iterator', None),
StackTraceElement('django.db.models.query', '_fetch_all', None),
StackTraceElement('django.db.models.query', '__iter__', None)
]
query_signature: QuerySignature = QuerySignature(
query_without_params, (), django_stack_trace, 'default')
self.assertEqual(query_signature.analysis,
QuerySignatureAnalyzeResult.MISSING_PREFETCH_RELATED)
def test_select_related(self):
query_without_params = '''
SELECT employee_settings.id, employee_settings.employee_id, employee_settings.groupID,
employee_settings.dentalGroupID, employee_settings.visionGroupID,
employee_settings.dentalCompanyHealthCarrier_id_is_set
FROM employee_settings
WHERE employee_settings.employee_id = %s
'''
table_names, _, where_equality_key = _parse_sql_for_tables_and_eq(
query_without_params)
self.assertListEqual(table_names, ['employee_settings'])
self.assertEqual(where_equality_key, 'employee_settings.employee_id')
django_stack_trace = [
StackTraceElement('django.db.models.sql.compiler', 'execute_sql',
None),
StackTraceElement('django.db.models.sql.compiler', 'results_iter',
None),
StackTraceElement('django.db.models.query', 'iterator', None),
StackTraceElement('django.db.models.query', '_fetch_all', None),
StackTraceElement('django.db.models.query', '__len__', None),
StackTraceElement('django.db.models.query', 'get', None),
StackTraceElement('django.db.models.manager', 'manager_method',
None),
StackTraceElement('django.db.models.fields.related_descriptors',
'get_object', None)
]
query_signature: QuerySignature = QuerySignature(
query_without_params, (), django_stack_trace, 'default')
self.assertEqual(query_signature.analysis,
QuerySignatureAnalyzeResult.MISSING_SELECT_RELATED)
def test_get(self):
query_without_params = '''
SELECT register_company_employee.id, register_company_employee.version_id,
register_company_employee.user_id, register_company_employee.company_id,
register_company_employee.isHighlyCompensated, register_company_employee.middleInitial
FROM register_company_employee
WHERE register_company_employee.id = %s
LIMIT 21
'''
table_names, _, where_equality_key = _parse_sql_for_tables_and_eq(
query_without_params)
self.assertListEqual(table_names, ['register_company_employee'])
self.assertEqual(where_equality_key, 'register_company_employee.id')
django_stack_trace = [
StackTraceElement('django.db.models.sql.compiler', 'execute_sql',
None),
StackTraceElement('django.db.models.sql.compiler', 'results_iter',
None),
StackTraceElement('django.db.models.query', 'iterator', None),
StackTraceElement('django.db.models.query', '_fetch_all', None),
StackTraceElement('django.db.models.query', '__len__', None),
StackTraceElement('django.db.models.query', 'get', None),
StackTraceElement('django.db.models.manager', 'manager_method',
None)
]
query_signature: QuerySignature = QuerySignature(
query_without_params, (), django_stack_trace, 'default')
self.assertEqual(query_signature.analysis,
QuerySignatureAnalyzeResult.GET)
def test_select_distinct_postgres(self):
"""
This test validates SELECT DISTINCT ON that is allowable in Postgres
See https://github.com/django-query-profiler/django-query-profiler/issues/21 for more details
"""
query_without_params = '''
SELECT DISTINCT ON (url) url, request_duration
FROM logs
ORDER BY url, timestamp DESC
'''
self.assertRaises(ParseException, lambda: _parse_sql_for_tables_and_eq(query_without_params))
django_stack_trace = [StackTraceElement('django.db.models.sql.compiler', 'execute_sql', None),
StackTraceElement('django.db.models.sql.compiler', 'results_iter', None),
StackTraceElement('django.db.models.query', 'iterator', None),
StackTraceElement('django.db.models.query', '_fetch_all', None),
StackTraceElement('django.db.models.query', '__iter__', None),
StackTraceElement('django.db.models.query', '__getitem__', None),
StackTraceElement('django.db.models.query', 'first', None)]
query_signature: QuerySignature = QuerySignature(query_without_params, (), django_stack_trace, 'default')
self.assertEqual(query_signature.analysis, QuerySignatureAnalyzeResult.UNKNOWN)
class QueryProfiledDataTest(TestCase):
"""
Tests for checking if "QueryProfiledData" class has the correct code for calculating summary, and if it is additive
"""
query_without_params = "SELECT * FROM table WHERE id=%s"
params = "1"
django_stack_trace = [
StackTraceElement('django.db', 'find', None),
StackTraceElement('django.models', 'get', None),
StackTraceElement('django.core', 'wsgi', None),
]
app_stack_trace = [
StackTraceElement('mysite.food', 'find_restaurant', 14),
StackTraceElement('mysite.food', 'find_restaurant', 15),
StackTraceElement('mysite.restaurant', 'get_restaurant', 15),
]
target_db = 'master'
query_signature_1 = QuerySignature(
query_without_params=query_without_params,
app_stack_trace=tuple(app_stack_trace),
django_stack_trace=tuple(django_stack_trace),
target_db=target_db)
query_signature_statistics_1 = QuerySignatureStatistics(
frequency=1,
query_execution_time_in_micros=11,
db_row_count=111,
)
query_signature_2 = QuerySignature(
query_without_params=query_without_params,
app_stack_trace=tuple(app_stack_trace[1:]),
django_stack_trace=tuple(django_stack_trace[1:]),
target_db=target_db)
query_signature_statistics_2 = QuerySignatureStatistics(
frequency=2, query_execution_time_in_micros=12, db_row_count=112)
def test_query_profiled_data_summary(self):
query_profiled_data = QueryProfiledData(
query_signature_to_query_signature_statistics={
self.query_signature_1: self.query_signature_statistics_1,
self.query_signature_2: self.query_signature_statistics_2
},
_query_params_db_hash_counter=Counter(_Some_Hash_=3))
query_profiled_summary_data = query_profiled_data.summary
expected_query_profiled_summary_data = QueryProfiledSummaryData(
sql_statement_type_counter=Counter({SqlStatement.SELECT: 3}),
exact_query_duplicates=3,
total_query_execution_time_in_micros=11 + 12,
total_db_row_count=111 + 112,
potential_n_plus1_query_count=2)
self.assertEqual(query_profiled_summary_data,
expected_query_profiled_summary_data)
self.assertIsNotNone(str(expected_query_profiled_summary_data)
) # No exception thrown is the test here
self.assertIsNotNone(str(
self.query_signature_1)) # No exception thrown is the test here
def test_query_profiled_data_addition_no_overlapping(self):
""" Query signatures are unique in both query profiled data """
query_profiled_data_1 = QueryProfiledData(
query_signature_to_query_signature_statistics={
self.query_signature_1: self.query_signature_statistics_1
},
_query_params_db_hash_counter=Counter(_Some_Hash_=1))
query_profiled_data_2 = QueryProfiledData(
query_signature_to_query_signature_statistics={
self.query_signature_2: self.query_signature_statistics_2
},
_query_params_db_hash_counter=Counter(_Some_Hash_=2))
combined_query_profiled_data = query_profiled_data_1 + query_profiled_data_2
expected_query_profiled_data = QueryProfiledData(
query_signature_to_query_signature_statistics={
self.query_signature_1: self.query_signature_statistics_1,
self.query_signature_2: self.query_signature_statistics_2
},
_query_params_db_hash_counter=Counter(_Some_Hash_=3))
self.assertEqual(combined_query_profiled_data,
expected_query_profiled_data)
self.assertEqual(sum([query_profiled_data_1, query_profiled_data_2]),
expected_query_profiled_data)
def test_query_profiled_data_addition_overlapping(self):
""" Query signature is shared between both query profiled data """
query_profiled_data_1 = QueryProfiledData(
query_signature_to_query_signature_statistics={
self.query_signature_1: self.query_signature_statistics_1
},
_query_params_db_hash_counter=Counter(_Some_Hash_=1))
query_profiled_data_2 = QueryProfiledData(
query_signature_to_query_signature_statistics={
self.query_signature_1: self.query_signature_statistics_2
},
_query_params_db_hash_counter=Counter(_Some_Hash_=2))
combined_query_profiled_data = query_profiled_data_1 + query_profiled_data_2
expected_query_profiled_data = QueryProfiledData(
query_signature_to_query_signature_statistics={
self.query_signature_1:
self.query_signature_statistics_1 +
self.query_signature_statistics_2
},
_query_params_db_hash_counter=Counter(_Some_Hash_=3))
self.assertEqual(combined_query_profiled_data,
expected_query_profiled_data)
self.assertEqual(sum([query_profiled_data_1, query_profiled_data_2]),
expected_query_profiled_data)