def test_build_query_with_rollup_multiple_dimensions(self): queries = ( mock_dataset.query() .widget(f.ReactTable(mock_dataset.fields.votes)) .dimension( f.day(mock_dataset.fields.timestamp), f.Rollup(mock_dataset.fields["candidate-id"]), f.Rollup(mock_dataset.fields.political_party), ) .sql ) self.assertEqual(len(queries), 3) with self.subTest("base query is same as without references or rollup"): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' '"candidate_id" "$candidate-id",' '"political_party" "$political_party",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp","$candidate-id","$political_party" ' 'ORDER BY "$timestamp","$candidate-id","$political_party"', str(queries[0]), ) with self.subTest( "in first rollup dimension's query, the dimension is replaced with NULL" ): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' '"candidate_id" "$candidate-id",' 'NULL "$political_party",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp","$candidate-id" ' 'ORDER BY "$timestamp","$candidate-id","$political_party"', str(queries[1]), ) with self.subTest( "in the second rollup dimension's query, rollup dimension and all following dimensions " "are replaced with NULL" ): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'NULL "$candidate-id",' 'NULL "$political_party",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp","$candidate-id","$political_party"', str(queries[2]), )
def test_build_query_with_rollup_dimensions_and_filter_applied_on_correct_rollup_dimension( self, ): queries = (mock_dataset.query().widget( f.ReactTable(mock_dataset.fields.votes)).dimension( f.Rollup(mock_dataset.fields.political_party), f.Rollup(f.day(mock_dataset.fields.timestamp)), ).filter( f.OmitFromRollup( mock_dataset.fields.timestamp.between( date(2018, 1, 1), date(2019, 1, 1)))).sql) self.assertEqual(len(queries), 3) with self.subTest("base query is same as without rollup"): self.assertEqual( "SELECT " '"political_party" "$political_party",' 'TRUNC("timestamp",\'DD\') "$timestamp",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' "WHERE \"timestamp\" BETWEEN '2018-01-01' AND '2019-01-01' " 'GROUP BY "$political_party","$timestamp" ' 'ORDER BY "$political_party","$timestamp" ' 'LIMIT 200000', str(queries[0]), ) with self.subTest( "base rollup query is same as base query minus the timestamp rollup dimension" ): self.assertEqual( "SELECT " '"political_party" "$political_party",' "'_FIREANT_ROLLUP_VALUE_' \"$timestamp\"," 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$political_party","$timestamp" ' 'ORDER BY "$political_party","$timestamp" ' 'LIMIT 200000', str(queries[1]), ) with self.subTest( "base rollup query is same as base query minus the political party rollup dimension" ): self.assertEqual( "SELECT " "'_FIREANT_ROLLUP_VALUE_' \"$political_party\"," "'_FIREANT_ROLLUP_VALUE_' \"$timestamp\"," 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$political_party","$timestamp" ' 'ORDER BY "$political_party","$timestamp" ' 'LIMIT 200000', str(queries[2]), )
def test_build_query_with_single_rollup_dimension(self): queries = (mock_dataset.query().widget( f.ReactTable(mock_dataset.fields.votes)).dimension( f.Rollup(mock_dataset.fields.political_party)).sql) self.assertEqual(len(queries), 2) with self.subTest( "base query is same as without references or totals"): self.assertEqual( "SELECT " '"political_party" "$political_party",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$political_party" ' 'ORDER BY "$political_party" ' 'LIMIT 200000', str(queries[0]), ) with self.subTest( "totals dimension is replaced with _FIREANT_ROLLUP_VALUE_"): self.assertEqual( "SELECT " "'_FIREANT_ROLLUP_VALUE_' \"$political_party\"," 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'ORDER BY "$political_party" ' 'LIMIT 200000', str(queries[1]), )
def test_dimension_with_dimension_modifier_is_replaced_by_default_when_result_set_filter_is_present( self, ): queries = (ds.query.widget(f.Pandas(ds.fields.aggr_number)).dimension( ds.fields.date).dimension(f.Rollup(ds.fields.boolean)).filter( f.ResultSet(ds.fields.boolean == True)).sql) self.assertEqual(len(queries), 2) with self.subTest('base query is the same as without totals'): self.assertEqual( "SELECT " '"date" "$date",' "CASE WHEN \"boolean\"=true THEN 'set(boolean=true)' ELSE 'complement(boolean=true)' END \"$boolean\"," 'SUM("number") "$aggr_number" ' 'FROM "test" ' 'GROUP BY "$date","$boolean" ' 'ORDER BY "$date","$boolean" ' 'LIMIT 200000', str(queries[0]), ) with self.subTest( 'totals dimension is replaced with _FIREANT_ROLLUP_VALUE_'): self.assertEqual( "SELECT " '"date" "$date",' '\'_FIREANT_ROLLUP_VALUE_\' "$boolean",' 'SUM("number") "$aggr_number" ' 'FROM "test" ' 'GROUP BY "$date" ' 'ORDER BY "$date","$boolean" ' 'LIMIT 200000', str(queries[1]), )
def test_build_query_with_rollup_dimension_and_two_filters_only_one_applied_to_rollup( self, ): queries = ( mock_dataset.query() .widget(f.ReactTable(mock_dataset.fields.votes)) .dimension( mock_dataset.fields.political_party, f.Rollup(f.day(mock_dataset.fields.timestamp)), ) .filter( f.OmitFromRollup( mock_dataset.fields.timestamp.between( date(2018, 1, 1), date(2019, 1, 1) ) ), mock_dataset.fields.timestamp.between( date(2018, 3, 1), date(2019, 9, 1) ), ) .sql ) self.assertEqual(len(queries), 2) with self.subTest("base query is same as without rollup with both filters"): self.assertEqual( "SELECT " '"political_party" "$political_party",' 'TRUNC("timestamp",\'DD\') "$timestamp",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' "WHERE \"timestamp\" BETWEEN '2018-01-01' AND '2019-01-01' " "AND \"timestamp\" BETWEEN '2018-03-01' AND '2019-09-01' " 'GROUP BY "$political_party","$timestamp" ' 'ORDER BY "$political_party","$timestamp"', str(queries[0]), ) with self.subTest( "base rollup query is same as base query minus the rollup dimension with one filter" ): self.assertEqual( "SELECT " '"political_party" "$political_party",' 'NULL "$timestamp",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' "WHERE \"timestamp\" BETWEEN '2018-03-01' AND '2019-09-01' " 'GROUP BY "$political_party" ' 'ORDER BY "$political_party","$timestamp"', str(queries[1]), )
def test_does_not_group_rollup_column(self): mssql_mock_dataset = copy.deepcopy(mock_dataset) mssql_mock_dataset.database = MSSQLDatabase() queries = (mssql_mock_dataset.query().widget( f.ReactTable(mock_dataset.fields.votes)).dimension( f.Rollup(mock_dataset.fields.political_party)).sql) self.assertEqual( "SELECT " "'_FIREANT_ROLLUP_VALUE_' \"$political_party\"," 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'ORDER BY "$political_party" ' 'OFFSET 0 ROWS ' 'FETCH NEXT 200000 ROWS ONLY', str(queries[1]), )
def test_build_query_with_totals_on_dimension_and_subsequent_dimensions( self): queries = (mock_dataset.query().widget( f.ReactTable(mock_dataset.fields.votes)).dimension( f.day(mock_dataset.fields.timestamp), f.Rollup(mock_dataset.fields["candidate-id"]), mock_dataset.fields.political_party, ).sql) self.assertEqual(len(queries), 2) with self.subTest( "base query is same as without references or totals"): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' '"candidate_id" "$candidate-id",' '"political_party" "$political_party",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp","$candidate-id","$political_party" ' 'ORDER BY "$timestamp","$candidate-id","$political_party" ' 'LIMIT 200000', str(queries[0]), ) with self.subTest( "all dimensions after the rolled up dimension are _FIREANT_ROLLUP_VALUE_" ): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' "'_FIREANT_ROLLUP_VALUE_' \"$candidate-id\"," "'_FIREANT_ROLLUP_VALUE_' \"$political_party\"," 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp","$candidate-id","$political_party" ' 'LIMIT 200000', str(queries[1]), )
def test_build_query_with_rollup_cat_dimension_with_references_and_date_filters( self, ): queries = (mock_dataset.query().widget( f.ReactTable(mock_dataset.fields.votes)).dimension( f.day(mock_dataset.fields.timestamp)).dimension( f.Rollup(mock_dataset.fields.political_party)).reference( f.DayOverDay(mock_dataset.fields.timestamp)).filter( mock_dataset.fields.timestamp.between( date(2018, 1, 1), date(2019, 1, 1))).sql) self.assertEqual(len(queries), 4) with self.subTest( "base query is same as without references or rollup"): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' '"political_party" "$political_party",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' "WHERE \"timestamp\" BETWEEN '2018-01-01' AND '2019-01-01' " 'GROUP BY "$timestamp","$political_party" ' 'ORDER BY "$timestamp","$political_party" ' 'LIMIT 200000', str(queries[0]), ) with self.subTest("reference query is shifted"): self.assertEqual( "SELECT " "TRUNC(TIMESTAMPADD(day,1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\"," '"political_party" "$political_party",' 'SUM("votes") "$votes_dod" ' 'FROM "politics"."politician" ' "WHERE \"timestamp\" BETWEEN TIMESTAMPADD(day,-1,'2018-01-01') " "AND TIMESTAMPADD(day,-1,'2019-01-01') " 'GROUP BY "$timestamp","$political_party" ' 'ORDER BY "$timestamp","$political_party" ' 'LIMIT 200000', str(queries[1]), ) with self.subTest( "base rollup query is same as base query minus the rollup dimension" ): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' "'_FIREANT_ROLLUP_VALUE_' \"$political_party\"," 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' "WHERE \"timestamp\" BETWEEN '2018-01-01' AND '2019-01-01' " 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp","$political_party" ' 'LIMIT 200000', str(queries[2]), ) with self.subTest( "reference total query is shifted without the rollup dimension" ): self.assertEqual( "SELECT " "TRUNC(TIMESTAMPADD(day,1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\"," "'_FIREANT_ROLLUP_VALUE_' \"$political_party\"," 'SUM("votes") "$votes_dod" ' 'FROM "politics"."politician" ' "WHERE \"timestamp\" BETWEEN TIMESTAMPADD(day,-1,'2018-01-01') " "AND TIMESTAMPADD(day,-1,'2019-01-01') " 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp","$political_party" ' 'LIMIT 200000', str(queries[3]), )
def test_build_query_with_rollup_dimension_and_a_reference(self): queries = ( mock_dataset.query() .widget(f.ReactTable(mock_dataset.fields.votes)) .dimension( f.day(mock_dataset.fields.timestamp), f.Rollup(mock_dataset.fields.political_party), ) .reference(f.DayOverDay(mock_dataset.fields.timestamp)) .sql ) self.assertEqual(len(queries), 4) with self.subTest("base query is same as without references or rollup"): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' '"political_party" "$political_party",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp","$political_party" ' 'ORDER BY "$timestamp","$political_party"', str(queries[0]), ) with self.subTest("reference query is shifted"): self.assertEqual( "SELECT " "TRUNC(TIMESTAMPADD('day',1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\"," '"political_party" "$political_party",' 'SUM("votes") "$votes_dod" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp","$political_party" ' 'ORDER BY "$timestamp","$political_party"', str(queries[1]), ) with self.subTest( "base rollup query is same as base query minus the rollup dimension" ): self.assertEqual( "SELECT " 'TRUNC("timestamp",\'DD\') "$timestamp",' 'NULL "$political_party",' 'SUM("votes") "$votes" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp","$political_party"', str(queries[2]), ) with self.subTest( "reference total query is shifted without the rollup dimension" ): self.assertEqual( "SELECT " "TRUNC(TIMESTAMPADD('day',1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\"," 'NULL "$political_party",' 'SUM("votes") "$votes_dod" ' 'FROM "politics"."politician" ' 'GROUP BY "$timestamp" ' 'ORDER BY "$timestamp","$political_party"', str(queries[3]), )