def test_build_query_with_multiple_visualizations(self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.widget(f.DataTablesJS(slicer.metrics.wins)) \
.queries
self.assertEqual(len(queries), 1)
self.assertEqual(
'SELECT '
'SUM("votes") "$m$votes",'
'SUM("is_winner") "$m$wins" '
'FROM "politics"."politician"', str(queries[0]))
def test_dimension_with_multiple_joins_includes_joins_ordered__in_query(
self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes,
slicer.metrics.voters)) \
.dimension(slicer.dimensions.timestamp) \
.dimension(slicer.dimensions.district) \
.queries
self.assertEqual(len(queries), 1)
self.assertEqual(
'SELECT '
'TRUNC("politician"."timestamp",\'DD\') "$d$timestamp",'
'"politician"."district_id" "$d$district",'
'"district"."district_name" "$d$district_display",'
'SUM("politician"."votes") "$m$votes",'
'COUNT("voter"."id") "$m$voters" '
'FROM "politics"."politician" '
'JOIN "politics"."voter" '
'ON "politician"."id"="voter"."politician_id" '
'OUTER JOIN "locations"."district" '
'ON "politician"."district_id"="district"."id" '
'GROUP BY "$d$timestamp","$d$district","$d$district_display" '
'ORDER BY "$d$timestamp","$d$district_display"', str(queries[0]))
def test_build_query_with_totals_dimension_and_total_filter_not_applied(
self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.dimension(slicer.dimensions.political_party) \
.dimension(slicer.dimensions.timestamp.rollup()) \
.filter(slicer.dimensions.timestamp.between(date(2018, 1, 1), date(2019, 1, 1)), apply_to_totals=False) \
.queries
self.assertEqual(len(queries), 2)
with self.subTest('base query is same as without totals'):
self.assertEqual(
'SELECT '
'"political_party" "$d$political_party",'
'TRUNC("timestamp",\'DD\') "$d$timestamp",'
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'WHERE "timestamp" BETWEEN \'2018-01-01\' AND \'2019-01-01\' '
'GROUP BY "$d$political_party","$d$timestamp" '
'ORDER BY "$d$political_party","$d$timestamp"',
str(queries[0]))
with self.subTest(
'base totals query is same as base query minus the totals dimension without filter'
):
self.assertEqual(
'SELECT '
'"political_party" "$d$political_party",'
'NULL "$d$timestamp",'
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'GROUP BY "$d$political_party" '
'ORDER BY "$d$political_party","$d$timestamp"',
str(queries[1]))
def test_build_query_with_totals_uni_dimension(self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.dimension(slicer.dimensions.candidate.rollup()) \
.queries
self.assertEqual(len(queries), 2)
with self.subTest(
'base query is same as without references or totals'):
self.assertEqual(
'SELECT '
'"candidate_id" "$d$candidate",'
'"candidate_name" "$d$candidate_display",'
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'GROUP BY "$d$candidate","$d$candidate_display" '
'ORDER BY "$d$candidate_display"', str(queries[0]))
with self.subTest('totals dimension is replaced with NULL'):
self.assertEqual(
'SELECT '
'NULL "$d$candidate",'
'NULL "$d$candidate_display",'
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'ORDER BY "$d$candidate_display"', str(queries[1]))
def test_build_query_with_totals_cat_dimension_with_references(self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.dimension(slicer.dimensions.timestamp,
slicer.dimensions.political_party.rollup()) \
.reference(f.DayOverDay(slicer.dimensions.timestamp)) \
.queries
self.assertEqual(len(queries), 4)
with self.subTest(
'base query is same as without references or totals'):
self.assertEqual(
'SELECT '
'TRUNC("timestamp",\'DD\') "$d$timestamp",'
'"political_party" "$d$political_party",'
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'GROUP BY "$d$timestamp","$d$political_party" '
'ORDER BY "$d$timestamp","$d$political_party"',
str(queries[0]))
with self.subTest('reference query is shifted'):
self.assertEqual(
'SELECT '
'TRUNC(TIMESTAMPADD(\'day\',1,"timestamp"),\'DD\') "$d$timestamp",'
'"political_party" "$d$political_party",'
'SUM("votes") "$m$votes_dod" '
'FROM "politics"."politician" '
'GROUP BY "$d$timestamp","$d$political_party" '
'ORDER BY "$d$timestamp","$d$political_party"',
str(queries[1]))
with self.subTest(
'base totals query is same as base query minus the totals dimension'
):
self.assertEqual(
'SELECT '
'TRUNC("timestamp",\'DD\') "$d$timestamp",'
'NULL "$d$political_party",'
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'GROUP BY "$d$timestamp" '
'ORDER BY "$d$timestamp","$d$political_party"',
str(queries[2]))
with self.subTest(
'reference total query is shifted without the totals dimension'
):
self.assertEqual(
'SELECT '
'TRUNC(TIMESTAMPADD(\'day\',1,"timestamp"),\'DD\') "$d$timestamp",'
'NULL "$d$political_party",'
'SUM("votes") "$m$votes_dod" '
'FROM "politics"."politician" '
'GROUP BY "$d$timestamp" '
'ORDER BY "$d$timestamp","$d$political_party"',
str(queries[3]))
def test_build_query_with_filter_not_like_display_dim(self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.filter(slicer.dimensions.candidate_display.not_like('%Trump')) \
.queries
self.assertEqual('SELECT '
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'WHERE NOT LOWER("candidate_name") LIKE LOWER(\'%Trump\')', str(queries[0]))
def test_build_query_with_filter_isin_unique_dim(self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.filter(slicer.dimensions.candidate.isin([1])) \
.queries
self.assertEqual('SELECT '
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'WHERE "candidate_id" IN (1)', str(queries[0]))
def test_build_query_with_filter_not_like_unique_dim_multiple_patterns(self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.filter(slicer.dimensions.candidate.not_like('%Trump', '%Clinton')) \
.queries
self.assertEqual('SELECT '
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'WHERE NOT (LOWER("candidate_name") LIKE LOWER(\'%Trump\') '
'OR LOWER("candidate_name") LIKE LOWER(\'%Clinton\'))', str(queries[0]))
def test_build_query_with_single_metric(self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.queries
self.assertEqual(len(queries), 1)
self.assertEqual(
'SELECT '
'SUM("votes") "$m$votes" '
'FROM "politics"."politician"', str(queries[0]))
def test_build_query_with_filter_not_like_categorical_dim(self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.filter(slicer.dimensions.political_party.not_like('Rep%')) \
.queries
self.assertEqual(len(queries), 1)
self.assertEqual('SELECT '
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'WHERE NOT LOWER("political_party") LIKE LOWER(\'Rep%\')', str(queries[0]))
def test_build_query_with_filter_range_datetime_dimension(self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.filter(slicer.dimensions.timestamp.between(date(2009, 1, 20), date(2017, 1, 20))) \
.queries
self.assertEqual(len(queries), 1)
self.assertEqual('SELECT '
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'WHERE "timestamp" BETWEEN \'2009-01-20\' AND \'2017-01-20\'', str(queries[0]))
def test_build_query_with_filter_isin_unique_dim_display(self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.filter(slicer.dimensions.candidate.display.isin(['Donald Trump'])) \
.queries
self.assertEqual(len(queries), 1)
self.assertEqual('SELECT '
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'WHERE "candidate_name" IN (\'Donald Trump\')', str(queries[0]))
def test_build_query_with_filter_boolean_false(self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.filter(slicer.dimensions.winner.is_(False)) \
.queries
self.assertEqual(len(queries), 1)
self.assertEqual('SELECT '
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'WHERE NOT "is_winner"', str(queries[0]))
def test_build_query_with_filter_notin_categorical_dim(self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.filter(slicer.dimensions.political_party.notin(['d'])) \
.queries
self.assertEqual(len(queries), 1)
self.assertEqual('SELECT '
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'WHERE "political_party" NOT IN (\'d\')', str(queries[0]))
def test_build_query_with_metric_filter_gte(self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.filter(slicer.metrics.votes >= 5) \
.queries
self.assertEqual(len(queries), 1)
self.assertEqual(
'SELECT '
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'HAVING SUM("votes")>=5', str(queries[0]))
def test_dimension_filter_with_join_on_display_definition_does_not_include_join_in_query(
self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.filter(slicer.dimensions.district.isin([1])) \
.queries
self.assertEqual(len(queries), 1)
self.assertEqual(
'SELECT '
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'WHERE "district_id" IN (1)', str(queries[0]))
def test_build_query_with_totals_on_multiple_dimensions_dimension(self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.dimension(slicer.dimensions.timestamp,
slicer.dimensions.candidate.rollup(),
slicer.dimensions.political_party.rollup()) \
.queries
self.assertEqual(len(queries), 3)
with self.subTest(
'base query is same as without references or totals'):
self.assertEqual(
'SELECT '
'TRUNC("timestamp",\'DD\') "$d$timestamp",'
'"candidate_id" "$d$candidate",'
'"candidate_name" "$d$candidate_display",'
'"political_party" "$d$political_party",'
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'GROUP BY "$d$timestamp","$d$candidate","$d$candidate_display","$d$political_party" '
'ORDER BY "$d$timestamp","$d$candidate_display","$d$political_party"',
str(queries[0]))
with self.subTest('first totals dimension is replaced with null'):
self.assertEqual(
'SELECT '
'TRUNC("timestamp",\'DD\') "$d$timestamp",'
'"candidate_id" "$d$candidate",'
'"candidate_name" "$d$candidate_display",'
'NULL "$d$political_party",'
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'GROUP BY "$d$timestamp","$d$candidate","$d$candidate_display" '
'ORDER BY "$d$timestamp","$d$candidate_display","$d$political_party"',
str(queries[1]))
with self.subTest('all totals dimension are replaced with null'):
self.assertEqual(
'SELECT '
'TRUNC("timestamp",\'DD\') "$d$timestamp",'
'NULL "$d$candidate",'
'NULL "$d$candidate_display",'
'NULL "$d$political_party",'
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'GROUP BY "$d$timestamp" '
'ORDER BY "$d$timestamp","$d$candidate_display","$d$political_party"',
str(queries[2]))
def test_dimension_filter_with_recursive_join_includes_join_in_query(self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.filter(slicer.dimensions.state.isin([1])) \
.queries
self.assertEqual(len(queries), 1)
self.assertEqual(
'SELECT '
'SUM("politician"."votes") "$m$votes" '
'FROM "politics"."politician" '
'OUTER JOIN "locations"."district" '
'ON "politician"."district_id"="district"."id" '
'WHERE "district"."state_id" IN (1)', str(queries[0]))
def test_build_query_with_datetime_dimension_quarterly(self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.dimension(slicer.dimensions.timestamp(f.quarterly)) \
.queries
self.assertEqual(len(queries), 1)
self.assertEqual(
'SELECT '
'TRUNC("timestamp",\'Q\') "$d$timestamp",'
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'GROUP BY "$d$timestamp" '
'ORDER BY "$d$timestamp"', str(queries[0]))
def test_build_query_with_categorical_dimension(self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.dimension(slicer.dimensions.political_party) \
.queries
self.assertEqual(len(queries), 1)
self.assertEqual(
'SELECT '
'"political_party" "$d$political_party",'
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'GROUP BY "$d$political_party" '
'ORDER BY "$d$political_party"', str(queries[0]))
def test_build_query_with_rollingmean_operation(self):
queries = slicer.data \
.widget(f.DataTablesJS(f.RollingMean(slicer.metrics.votes, 3, 3))) \
.dimension(slicer.dimensions.timestamp) \
.queries
self.assertEqual(len(queries), 1)
self.assertEqual(
'SELECT '
'TRUNC("timestamp",\'DD\') "$d$timestamp",'
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'GROUP BY "$d$timestamp" '
'ORDER BY "$d$timestamp"', str(queries[0]))
def test_build_query_with_boolean_dimension(self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.dimension(slicer.dimensions.winner) \
.queries
self.assertEqual(len(queries), 1)
self.assertEqual(
'SELECT '
'"is_winner" "$d$winner",'
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'GROUP BY "$d$winner" '
'ORDER BY "$d$winner"', str(queries[0]))
def test_build_query_order_by_metric_asc(self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.dimension(slicer.dimensions.timestamp) \
.orderby(slicer.metrics.votes, orientation=Order.asc) \
.queries
self.assertEqual(len(queries), 1)
self.assertEqual(
'SELECT '
'TRUNC("timestamp",\'DD\') "$d$timestamp",'
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'GROUP BY "$d$timestamp" '
'ORDER BY "$m$votes" ASC', str(queries[0]))
def test_build_query_with_unique_dimension(self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.dimension(slicer.dimensions.election) \
.queries
self.assertEqual(len(queries), 1)
self.assertEqual(
'SELECT '
'"election_id" "$d$election",'
'"election_year" "$d$election_display",'
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'GROUP BY "$d$election","$d$election_display" '
'ORDER BY "$d$election_display"', str(queries[0]))
def test_dimension_filter_display_field_with_join_includes_join_in_query(
self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.filter(slicer.dimensions.district.display.isin(['District 4'])) \
.queries
self.assertEqual(len(queries), 1)
self.assertEqual(
'SELECT '
'SUM("politician"."votes") "$m$votes" '
'FROM "politics"."politician" '
'OUTER JOIN "locations"."district" '
'ON "politician"."district_id"="district"."id" '
'WHERE "district"."district_name" IN (\'District 4\')',
str(queries[0]))
def test_metric_with_join_includes_join_in_query(self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.voters)) \
.dimension(slicer.dimensions.political_party) \
.queries
self.assertEqual(len(queries), 1)
self.assertEqual(
'SELECT '
'"politician"."political_party" "$d$political_party",'
'COUNT("voter"."id") "$m$voters" '
'FROM "politics"."politician" '
'JOIN "politics"."voter" '
'ON "politician"."id"="voter"."politician_id" '
'GROUP BY "$d$political_party" '
'ORDER BY "$d$political_party"', str(queries[0]))
def test_build_query_order_by_dimension_display(self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.dimension(slicer.dimensions.candidate) \
.orderby(slicer.dimensions.candidate_display) \
.queries
self.assertEqual(len(queries), 1)
self.assertEqual(
'SELECT '
'"candidate_id" "$d$candidate",'
'"candidate_name" "$d$candidate_display",'
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'GROUP BY "$d$candidate","$d$candidate_display" '
'ORDER BY "$d$candidate_display"', str(queries[0]))
def test_build_query_with_multiple_dimensions(self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.dimension(slicer.dimensions.timestamp) \
.dimension(slicer.dimensions.candidate) \
.queries
self.assertEqual(len(queries), 1)
self.assertEqual(
'SELECT '
'TRUNC("timestamp",\'DD\') "$d$timestamp",'
'"candidate_id" "$d$candidate",'
'"candidate_name" "$d$candidate_display",'
'SUM("votes") "$m$votes" '
'FROM "politics"."politician" '
'GROUP BY "$d$timestamp","$d$candidate","$d$candidate_display" '
'ORDER BY "$d$timestamp","$d$candidate_display"', str(queries[0]))
def test_build_query_with_multiple_dimensions_and_visualizations(self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes, slicer.metrics.wins)) \
.widget(f.HighCharts()
.axis(f.HighCharts.LineSeries(slicer.metrics.votes))
.axis(f.HighCharts.LineSeries(slicer.metrics.wins))) \
.dimension(slicer.dimensions.timestamp) \
.dimension(slicer.dimensions.political_party) \
.queries
self.assertEqual(len(queries), 1)
self.assertEqual(
'SELECT '
'TRUNC("timestamp",\'DD\') "$d$timestamp",'
'"political_party" "$d$political_party",'
'SUM("votes") "$m$votes",'
'SUM("is_winner") "$m$wins" '
'FROM "politics"."politician" '
'GROUP BY "$d$timestamp","$d$political_party" '
'ORDER BY "$d$timestamp","$d$political_party"', str(queries[0]))
def test_dimension_with_recursive_join_joins_all_join_tables(self):
queries = slicer.data \
.widget(f.DataTablesJS(slicer.metrics.votes)) \
.dimension(slicer.dimensions.timestamp) \
.dimension(slicer.dimensions.state) \
.queries
self.assertEqual(len(queries), 1)
self.assertEqual(
'SELECT '
'TRUNC("politician"."timestamp",\'DD\') "$d$timestamp",'
'"district"."state_id" "$d$state",'
'"state"."state_name" "$d$state_display",'
'SUM("politician"."votes") "$m$votes" '
'FROM "politics"."politician" '
'OUTER JOIN "locations"."district" '
'ON "politician"."district_id"="district"."id" '
'JOIN "locations"."state" '
'ON "district"."state_id"="state"."id" '
'GROUP BY "$d$timestamp","$d$state","$d$state_display" '
'ORDER BY "$d$timestamp","$d$state_display"', str(queries[0]))
