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]))