def test_select__multiple_subqueries(self): subquery0 = Query.from_(self.table_abc).select("foo") subquery1 = Query.from_(self.table_efg).select("bar") q = Query \ .from_(subquery0) \ .from_(subquery1) \ .select(subquery0.foo, subquery1.bar) self.assertEqual('SELECT "sq0"."foo","sq1"."bar" ' 'FROM (SELECT "foo" FROM "abc") "sq0",' '(SELECT "bar" FROM "efg") "sq1"', str(q))
def test_select__subquery(self): subquery = Query.from_(self.table_abc).select("*") q = Query.from_(subquery) \ .select(subquery.foo, subquery.bar) self.assertEqual('SELECT "sq0"."foo","sq0"."bar" ' 'FROM (SELECT * FROM "abc") "sq0"', str(q))
def test_select_from_nested_query_with_join(self): subquery1 = Query.from_(self.table_abc).select( self.table_abc.foo, fn.Sum(self.table_abc.fizz + self.table_abc.buzz).as_('fizzbuzz'), ).groupby( self.table_abc.foo ) subquery2 = Query.from_(self.table_efg).select( self.table_efg.foo.as_('foo_two'), self.table_efg.bar, ) query = Query.from_(subquery1).select( subquery1.foo, subquery1.fizzbuzz ).join(subquery2).on(subquery1.foo == subquery2.foo_two).select( subquery2.foo_two, subquery2.bar ) self.assertEqual('SELECT ' '"sq0"."foo","sq0"."fizzbuzz",' '"sq1"."foo_two","sq1"."bar" ' 'FROM (' 'SELECT ' '"foo",SUM("fizz"+"buzz") "fizzbuzz" ' 'FROM "abc" ' 'GROUP BY "foo"' ') "sq0" JOIN (' 'SELECT ' '"foo" "foo_two","bar" ' 'FROM "efg"' ') "sq1" ON "sq0"."foo"="sq1"."foo_two"', str(query))
def test_where__in(self): main, nested = Tables('abc', 'efg') q = Query.from_(main).select('*').where(main.foo.isin( Query.from_(nested).select(nested.foo).where(nested.bar == 0) )) self.assertEqual('SELECT * FROM abc WHERE foo IN (SELECT foo FROM efg WHERE bar=0)', str(q))
def test_union_as_subquery(self): abc, efg = Tables('abc', 'efg') hij = Query.from_(abc).select(abc.t).union(Query.from_(efg).select(efg.t)) q = Query.from_(hij).select(fn.Avg(hij.t)) self.assertEqual('SELECT AVG("sq0"."t") FROM ((SELECT "t" FROM "abc") UNION (SELECT "t" FROM "efg")) "sq0"', str(q))
def test_inner_join(self): query = Query.from_(self.table0).join(self.table1).on( self.table0.foo == self.table1.bar).select('*') query_explicit = Query.from_(self.table0).join(self.table1, how=JoinType.inner).on( self.table0.foo == self.table1.bar).select('*') self.assertEqual('SELECT * FROM "abc" JOIN "efg" ON "abc"."foo"="efg"."bar"', str(query)) self.assertEqual('SELECT * FROM "abc" JOIN "efg" ON "abc"."foo"="efg"."bar"', str(query_explicit))
def test_left_join(self): query2 = Query.from_(self.table0).join(self.table1).on( self.table0.foo == self.table1.bar).select('*') q2 = Query.from_(self.table0).join(self.table1, how=JoinType.left).on( self.table0.foo == self.table1.bar).select('*') self.assertEqual('SELECT * FROM "abc" "t0" JOIN "efg" "t1" ON "t0"."foo"="t1"."bar"', str(query2)) self.assertEqual('SELECT * FROM "abc" "t0" JOIN "efg" "t1" ON "t0"."foo"="t1"."bar"', str(q2))
def test_immutable__tables(self): query1 = Query.from_(self.table0).select(self.table0.foo) query2 = Query.from_(self.table0).join(self.table1).on(self.table0.foo == self.table1.bar).select( self.table0.foo, self.table1.buz) self.assertEqual('SELECT "t0"."foo","t1"."buz" FROM "abc" "t0" ' 'JOIN "efg" "t1" ON "t0"."foo"="t1"."bar"', str(query2)) self.assertEqual('SELECT "foo" FROM "abc"', str(query1))
def test_where__equality(self): subquery = Query.from_('efg').select('fiz').where(F('buz') == 0) query = Query.from_(self.table_abc).select( self.table_abc.foo, self.table_abc.bar ).where(self.table_abc.bar == subquery) self.assertEqual('SELECT "foo","bar" FROM "abc" ' 'WHERE "bar"=(SELECT "fiz" FROM "efg" WHERE "buz"=0)', str(query))
def test_cross_join_on_subquery(self): table_a, table_b = Tables('a', 'b') q_a = Query.from_(table_a).select('*') q_b = Query.from_(table_b).select('*').join(q_a).cross().select('*') self.assertEqual( 'SELECT * FROM "b" CROSS JOIN (SELECT * FROM "a") "sq0"', str(q_b) )
def test_immutable__tables(self): query1 = Query.from_(self.table_abc).select(self.table_abc.foo) query2 = Query.from_(self.table_abc).join(self.table_efg).on(self.table_abc.foo == self.table_efg.bar).select( self.table_abc.foo, self.table_efg.buz) self.assertEqual('SELECT "abc"."foo","efg"."buz" FROM "abc" ' 'JOIN "efg" ON "abc"."foo"="efg"."bar"', str(query2)) self.assertEqual('SELECT "foo" FROM "abc"', str(query1))
def test_join(self): subquery = Query.from_('efg').select('fiz', 'buz').where(F('buz') == 0) q = Query.from_(self.t).join(subquery).on( self.t.bar == subquery.buz ).select(self.t.foo, subquery.fiz) self.assertEqual('SELECT t0.foo,t1.fiz FROM abc t0 ' 'JOIN (SELECT fiz,buz FROM efg WHERE buz=0) t1 ' 'ON t0.bar=t1.buz', str(q))
def test_join(self): subquery = Query.from_('efg').select('fiz', 'buz').where(F('buz') == 0) q = Query.from_(self.table_abc).join(subquery).on( self.table_abc.bar == subquery.buz ).select(self.table_abc.foo, subquery.fiz) self.assertEqual('SELECT "abc"."foo","sq0"."fiz" FROM "abc" ' 'JOIN (SELECT "fiz","buz" FROM "efg" WHERE "buz"=0) "sq0" ' 'ON "abc"."bar"="sq0"."buz"', str(q))
def test_union_with_order_by_with_aliases(self): query1 = Query.from_(self.table1).select(self.table1.foo.as_('a')).as_('a') query2 = Query.from_(self.table2).select(self.table2.bar.as_('a')).as_('b') union_query = (query1 + query2).orderby(query1.field('a')) self.assertEqual('(SELECT "foo" "a" FROM "abc") "a" ' 'UNION ALL ' '(SELECT "bar" "a" FROM "efg") "b" ' 'ORDER BY "a"."a"', union_query)
def test_from_subquery_without_alias(self): subquery = Query.from_(self.table_efg) \ .select(self.table_efg.base_id.as_('x'), self.table_efg.fizz, self.table_efg.buzz) test_query = Query.from_(subquery) \ .select(subquery.x, subquery.fizz, subquery.buzz) self.assertEqual('SELECT "sq0"."x","sq0"."fizz","sq0"."buzz" ' 'FROM (' 'SELECT "base_id" "x","fizz","buzz" FROM "efg"' ') "sq0"', str(test_query))
def test_union_with_order_by_use_union_query_field(self): query1 = Query.from_(self.table1).select(self.table1.foo.as_('a')) query2 = Query.from_(self.table2).select(self.table2.bar.as_('a')) union_query = (query1 + query2) union_query = union_query.orderby(union_query.field('a')) self.assertEqual('(SELECT "foo" "a" FROM "abc") ' 'UNION ALL ' '(SELECT "bar" "a" FROM "efg") ' 'ORDER BY "a"', union_query)
def test_no_rollup_after_rollup(self): with self.assertRaises(AttributeError): Query.from_(self.table).select( self.table.foo, self.table.fiz, fn.Sum(self.table.bar) ).rollup( self.table.foo, vendor='mysql' ).rollup( self.table.fiz, vendor='mysql' )
def test_join_query_without_alias(self): subquery = Query.from_(self.table_efg) \ .select(self.table_efg.base_id.as_('x'), self.table_efg.fizz, self.table_efg.buzz) test_query = Query.from_(self.table_abc) \ .join(subquery) \ .on(subquery.x == self.table_abc.id) \ .select(self.table_abc.foo, subquery.fizz, subquery.buzz) self.assertEqual('SELECT "abc"."foo","sq0"."fizz","sq0"."buzz" ' 'FROM "abc" JOIN (' 'SELECT "base_id" "x","fizz","buzz" FROM "efg"' ') "sq0" ' 'ON "sq0"."x"="abc"."id"', str(test_query))
def test_select_from_nested_query(self): subquery = Query.from_(self.table_abc).select( self.table_abc.foo, self.table_abc.bar, (self.table_abc.fizz + self.table_abc.buzz).as_('fizzbuzz'), ) query = Query.from_(subquery).select(subquery.foo, subquery.bar, subquery.fizzbuzz) self.assertEqual('SELECT "sq0"."foo","sq0"."bar","sq0"."fizzbuzz" ' 'FROM (' 'SELECT "foo","bar","fizz"+"buzz" "fizzbuzz" ' 'FROM "abc"' ') "sq0"', str(query))
def test_select__nested_subquery(self): subquery0 = Query.from_(self.table_abc).select("*") subquery1 = Query.from_(subquery0) \ .select(subquery0.foo, subquery0.bar) subquery2 = Query.from_(subquery1) \ .select(subquery1.foo) q = Query.from_(subquery2) \ .select(subquery2.foo) self.assertEqual('SELECT "sq2"."foo" ' 'FROM (SELECT "sq1"."foo" ' 'FROM (SELECT "sq0"."foo","sq0"."bar" ' 'FROM (SELECT * FROM "abc") "sq0") "sq1") "sq2"', str(q))
def test_join_on_field_multi_with_extra_join(self): query = Query.from_(self.table0)\ .join(self.hij).on_field("buzz")\ .join(self.table1).on_field("foo", "bar").select('*') self.assertEqual('SELECT * FROM "abc" JOIN "hij" ON "abc"."buzz"="hij"."buzz" ' 'JOIN "efg" ON "abc"."foo"="efg"."foo" AND "abc"."bar"="efg"."bar"', str(query))
def test_tuple_in_using_mixed_tuples(self): q = Query.from_(self.table_abc) \ .select(self.table_abc.foo, self.table_abc.bar) \ .where(Tuple(self.table_abc.foo, self.table_abc.bar).isin([(1, 1), Tuple(2, 2), (3, 3)])) self.assertEqual('SELECT "foo","bar" FROM "abc" ' 'WHERE ("foo","bar") IN ((1,1),(2,2),(3,3))', str(q))
def test_select__multiple_tables(self): q = Query.from_(self.table_abc) \ .select(self.table_abc.foo) \ .from_(self.table_efg) \ .select(self.table_efg.bar) self.assertEqual('SELECT "abc"."foo","efg"."bar" FROM "abc","efg"', str(q))
def test_tuple_equality_tuple_on_left(self): q = Query.from_(self.table_abc) \ .select(self.table_abc.foo, self.table_abc.bar) \ .where(Tuple(self.table_abc.foo, self.table_abc.bar) == (1, 2)) self.assertEqual('SELECT "foo","bar" FROM "abc" ' 'WHERE ("foo","bar")=(1,2)', str(q))
def test_join_same_table_with_prefixes(self): table1 = Table('abc', alias='x') table2 = Table('abc', alias='y') q = Query.from_(table1).join(table2).on(table1.foo == table2.bar).select(table1.foo, table2.buz) self.assertEqual('SELECT "x"."foo","y"."buz" FROM "abc" "x" ' 'JOIN "abc" "y" ON "x"."foo"="y"."bar"', str(q))
def test_join_same_table(self): table1 = Table('abc') table2 = Table('abc') q = Query.from_(table1).join(table2).on(table1.foo == table2.bar).select(table1.foo, table2.buz) self.assertEqual('SELECT "abc"."foo","abc2"."buz" FROM "abc" ' 'JOIN "abc" "abc2" ON "abc"."foo"="abc2"."bar"', str(q))
def test_groupby__alias(self): bar = self.t.bar.as_('bar01') q = Query.from_(self.t) \ .select(fn.Sum(self.t.foo), bar) \ .groupby(bar) self.assertEqual('SELECT SUM("foo"),"bar" "bar01" FROM "abc" GROUP BY "bar01"', str(q))
def test_join_on_complex_criteria(self): q = Query.from_(self.table0).join(self.table1, how=JoinType.right).on( (self.table0.foo == self.table1.fiz) & (self.table0.bar == self.table1.buz) ).select('*') self.assertEqual('SELECT * FROM "abc" ' 'RIGHT JOIN "efg" ON "abc"."foo"="efg"."fiz" AND "abc"."bar"="efg"."buz"', str(q))
def test_select__fields_after_table_star(self): q = Query.from_(self.table0).join(self.table1).on(self.table0.foo == self.table1.bar).select(self.table0.star, self.table1.bar).select( self.table0.foo) self.assertEqual('SELECT "t0".*,"t1"."bar" FROM "abc" "t0" JOIN "efg" "t1" ON "t0"."foo"="t1"."bar"', str(q))
def _build_query(self, table, joins, metrics, dimensions, mfilters, dfilters, references, rollup): # Initialize query query = Query.from_(table) # Add join tables for join_table, criterion, join_type in joins: query = query.join(join_table, how=join_type).on(criterion) # Select dimensions dx = [dimension.as_(key) for key, dimension in dimensions.items() if key not in rollup] rx = [dimension.as_(key) for key, dimension in dimensions.items() if key in rollup] if dx: query = query.select(*dx).groupby(*dx) if rx: query = query.select(*rx).rollup(*rx) # Select metrics mx = [metric.as_(key) for key, metric in metrics.items()] query = query.select(*mx) # Add comparisons for reference_key, dimension_key in references.items(): criterion_f, field_f = self._get_reference_mappers(reference_key) reference_query = copy.deepcopy(query) cx = self._build_reference_join_criterion(reference_query, dimensions, criterion_f, dimension_key) query = query.join(reference_query).on(cx) # Select all of the comparison dimensions # TODO maybe just select the dimensions that is different if dimensions: query = query.select(*[reference_query.field(key).as_('%s_%s' % (key, reference_key)) for key, dimension in dimensions.items()]) # Select all of the comparison metrics query = query.select(*[field_f(metric, reference_query.field(key)).as_('%s_%s' % (key, reference_key)) for key, metric in metrics.items()]) # Add ordering if dx: query = query.orderby(*dx) if rx: query = query.orderby(*rx) # Add filters if dfilters: for dfx in dfilters: query = query.where(dfx) if mfilters: for mfx in mfilters: query = query.having(mfx) return query
def test__cast__as(self): q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.UNSIGNED)) self.assertEqual("SELECT CAST(\"foo\" AS UNSIGNED) FROM \"abc\"", str(q))
def test__count__star(self): q = Q.from_('abc').select(fn.Count('*')) self.assertEqual('SELECT COUNT(*) FROM \"abc\"', str(q))
def test__avg(self): q = Q.from_('abc').select(fn.Avg(F('foo'))) self.assertEqual('SELECT AVG(\"foo\") FROM \"abc\"', str(q))
def test__coalesce(self): q = Q.from_('abc').select(fn.Coalesce(F('foo'), 0)) self.assertEqual('SELECT COALESCE(\"foo\",0) FROM \"abc\"', str(q))
def test__bin__field(self): q = Q.from_(self.t).select(fn.Bin(self.t.foo)) self.assertEqual("SELECT BIN(\"foo\") FROM \"abc\"", str(q))
def test__addition__fields(self): q1 = Q.from_('abc').select(F('a') + F('b')) q2 = Q.from_(self.t).select(self.t.a + self.t.b) self.assertEqual('SELECT \"a\"+\"b\" FROM \"abc\"', str(q1)) self.assertEqual('SELECT \"a\"+\"b\" FROM \"abc\"', str(q2))
def test__subtraction__fields(self): q1 = Q.from_('abc').select(F('a') - F('b')) q2 = Q.from_(self.t).select(self.t.a - self.t.b) self.assertEqual('SELECT \"a\"-\"b\" FROM \"abc\"', str(q1)) self.assertEqual('SELECT \"a\"-\"b\" FROM \"abc\"', str(q2))
def test__cast__date(self): q1 = Q.from_(self.t).select(fn.Date(self.t.foo)) q2 = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.DATE)) self.assertEqual("SELECT DATE(\"foo\") FROM \"abc\"", str(q1)) self.assertEqual("SELECT CAST(\"foo\" AS DATE) FROM \"abc\"", str(q2))
def test__convert__utf8(self): q = Q.from_(self.t).select(fn.Convert(self.t.foo, SqlTypes.utf8)) self.assertEqual("SELECT CONVERT(\"foo\" USING utf8) FROM \"abc\"", str(q))
def test__addition__right(self): q1 = Q.from_('abc').select(1 + F('a')) q2 = Q.from_(self.t).select(1 + self.t.a) self.assertEqual('SELECT 1+\"a\" FROM \"abc\"', str(q1)) self.assertEqual('SELECT 1+\"a\" FROM \"abc\"', str(q2))
def test__case__raw(self): q = Q.from_('abc').select(Case().when(F('foo') == 1, 'a').else_('b')) self.assertEqual( "SELECT CASE WHEN \"foo\"=1 THEN 'a' ELSE 'b' END FROM \"abc\"", str(q))
def test__ascii__field(self): q = Q.from_(self.t).select(fn.Ascii(self.t.foo)) self.assertEqual("SELECT ASCII(\"foo\") FROM \"abc\"", str(q))
def test__stddev(self): q = Q.from_('abc').select(fn.StdDev(F('foo'))) self.assertEqual('SELECT STDDEV(\"foo\") FROM \"abc\"', str(q))
def test__case__no_else(self): with self.assertRaises(CaseException): q = Q.from_('abc').select(Case().when(F('foo') > 0, F('fiz'))) str(q)
def test__max(self): q = Q.from_('abc').select(fn.Max(F('foo'))) self.assertEqual('SELECT MAX(\"foo\") FROM \"abc\"', str(q))
def test__addition__decimal(self): q1 = Q.from_('abc').select(F('a') + 1.0) q2 = Q.from_(self.t).select(self.t.a + 1.0) self.assertEqual('SELECT \"a\"+1.0 FROM \"abc\"', str(q1)) self.assertEqual('SELECT \"a\"+1.0 FROM \"abc\"', str(q2))
def test__sum(self): q = Q.from_('abc').select(fn.Sum(F('foo'))) self.assertEqual('SELECT SUM(\"foo\") FROM \"abc\"', str(q))
def test__case__no_cases(self): with self.assertRaises(CaseException): q = Q.from_('abc').select(Case()) str(q)
def test__count(self): q = Q.from_('abc').select(fn.Count(F('foo'))) self.assertEqual('SELECT COUNT(\"foo\") FROM \"abc\"', str(q))
def test__exponent__decimal(self): q1 = Q.from_('abc').select(F('a')**0.5) q2 = Q.from_(self.t).select(self.t.a**0.5) self.assertEqual('SELECT POW(\"a\",0.5) FROM \"abc\"', str(q1)) self.assertEqual('SELECT POW(\"a\",0.5) FROM \"abc\"', str(q2))
def test__modulus__number(self): q1 = Q.from_('abc').select(F('a') % 2) q2 = Q.from_(self.t).select(self.t.a % 2) self.assertEqual('SELECT MOD(\"a\",2) FROM \"abc\"', str(q1)) self.assertEqual('SELECT MOD(\"a\",2) FROM \"abc\"', str(q2))
def test__arithmetic_with_function(self): q1 = Q.from_('abc').select(fn.Sum(F('foo')) + 1) q2 = Q.from_(self.t).select(fn.Sum(self.t.foo) + 1) self.assertEqual('SELECT SUM(\"foo\")+1 FROM \"abc\"', str(q1)) self.assertEqual('SELECT SUM(\"foo\")+1 FROM \"abc\"', str(q2))
def test__complex_op_div_parentheses(self): q1 = Q.from_('abc').select((F('a') + 1) / (F('b') - 5)) q2 = Q.from_(self.t).select((self.t.a + 1) / (self.t.b - 5)) self.assertEqual('SELECT (\"a\"+1)/(\"b\"-5) FROM \"abc\"', str(q1)) self.assertEqual('SELECT (\"a\"+1)/(\"b\"-5) FROM \"abc\"', str(q2))
def test__length__field(self): q = Q.from_(self.t).select(fn.Length(self.t.foo)) self.assertEqual("SELECT LENGTH(\"foo\") FROM \"abc\"", str(q))
def test__exponent__number(self): q1 = Q.from_('abc').select(F('a')**2) q2 = Q.from_(self.t).select(self.t.a**2) self.assertEqual('SELECT POW(\"a\",2) FROM \"abc\"', str(q1)) self.assertEqual('SELECT POW(\"a\",2) FROM \"abc\"', str(q2))
def test__insert__field(self): q = Q.from_(self.t).select(fn.Insert(self.t.foo, 3, 4, self.t.bar)) self.assertEqual("SELECT INSERT(\"foo\",3,4,\"bar\") FROM \"abc\"", str(q))
def test__arithmetic_equality(self): q1 = Q.from_('abc').select(F('a') / 2 == 2) q2 = Q.from_(self.t).select(self.t.a / 2 == 2) self.assertEqual('SELECT "a"/2=2 FROM \"abc\"', str(q1)) self.assertEqual('SELECT \"a\"/2=2 FROM \"abc\"', str(q2))
def test__lower__field(self): q = Q.from_(self.t).select(fn.Lower(self.t.foo)) self.assertEqual("SELECT LOWER(\"foo\") FROM \"abc\"", str(q))
def test__complex_op(self): q1 = Q.from_('abc').select(2 + 1 / F('a') - 5) q2 = Q.from_(self.t).select(2 + 1 / self.t.a - 5) self.assertEqual('SELECT 2+1/\"a\"-5 FROM \"abc\"', str(q1)) self.assertEqual('SELECT 2+1/\"a\"-5 FROM \"abc\"', str(q2))
def test__concat__field(self): q = Q.from_(self.t).select(fn.Concat(self.t.foo, self.t.bar)) self.assertEqual("SELECT CONCAT(\"foo\",\"bar\") FROM \"abc\"", str(q))