def test_nested_array_join(self): table = self._make_table( 't1', Column('x', types.Int32, primary_key=True), Column( 'parent', types.Nested( Column('child1', types.Int32), Column('child2', types.String), ))) query = select([ table.c.parent.child1, table.c.parent.child2, ]).array_join(table.c.parent) self.assertEqual( self.compile(query), 'SELECT t1.parent.child1, t1.parent.child2 ' 'FROM t1 ' 'ARRAY JOIN t1.parent') query = select([ table.c.parent.child1, table.c.parent.child2, ]).array_join(table.c.parent.child1, ) self.assertEqual( self.compile(query), 'SELECT t1.parent.child1, t1.parent.child2 ' 'FROM t1 ' 'ARRAY JOIN t1.parent.child1') query = select([ table.c.parent.child1, table.c.parent.child2, ]).array_join( table.c.parent.child1, table.c.parent.child2, ) self.assertEqual( self.compile(query), 'SELECT t1.parent.child1, t1.parent.child2 ' 'FROM t1 ' 'ARRAY JOIN t1.parent.child1, t1.parent.child2') parent_labeled = table.c.parent.label('p') query = select([ table.c.parent.child1, table.c.parent.child2, ]).array_join(parent_labeled) self.assertEqual( self.compile(query), 'SELECT t1.parent.child1, t1.parent.child2 ' 'FROM t1 ' 'ARRAY JOIN t1.parent AS p') query = select([ parent_labeled.child1, parent_labeled.child2, table.c.parent.child1, ]).array_join(parent_labeled) self.assertEqual( self.compile(query), 'SELECT p.child1, p.child2, t1.parent.child1 AS child1_1 ' 'FROM t1 ' 'ARRAY JOIN t1.parent AS p')
def test_group_by_with_totals(self): table = self.create_table() query = select([table.c.x]).group_by(table.c.x) self.assertEqual(self.compile(query), 'SELECT x FROM t1 GROUP BY x') query = select([table.c.x]).group_by(table.c.x).with_totals() self.assertEqual(self.compile(query), 'SELECT x FROM t1 GROUP BY x WITH TOTALS')
def test_final(self): table = self._make_table() query = select([table.c.x]).final().group_by(table.c.x) self.assertEqual( self.compile(query), 'SELECT t1.x FROM t1 FINAL GROUP BY t1.x' )
def test_sample(self): table = self.create_table() query = select([table.c.x]).sample(0.1).group_by(table.c.x) self.assertEqual(self.compile(query), 'SELECT x FROM t1 SAMPLE %(param_1)s GROUP BY x') self.assertEqual(self.compile(query, literal_binds=True), 'SELECT x FROM t1 SAMPLE 0.1 GROUP BY x')
def test_limit_by(self): table = self._make_table() query = select([table.c.x]).order_by(table.c.x)\ .limit_by([table.c.x], limit=1) self.assertEqual( self.compile(query), 'SELECT t1.x FROM t1 ORDER BY t1.x LIMIT %(param_1)s BY t1.x') self.assertEqual(self.compile(query, literal_binds=True), 'SELECT t1.x FROM t1 ORDER BY t1.x LIMIT 1 BY t1.x')
def test_join_same_column_name(self): table_1 = self.create_table( 'table_1', Column('x', types.UInt32, primary_key=True)) table_2 = self.create_table( 'table_2', Column('x', types.UInt32, primary_key=True)) join = table_1.join(table_2, table_1.c.x == table_2.c.x, type='INNER') self.assertEqual( self.compile(select([table_1.c.x]).select_from(join)), 'SELECT table_1.x FROM table_1 ' 'INNER JOIN table_2 ON table_1.x = table_2.x')
def make_statement(type=None, strictness=None, distribution=None, full=False, isouter=False): join = table_1.join(table_2, table_2.c.y == table_1.c.x, isouter=isouter, full=full, type=type, strictness=strictness, distribution=distribution) return select([table_1.c.x]).select_from(join)
def test_nested_array_join_left(self): table = self._make_table( 't1', Column('x', types.Int32, primary_key=True), Column( 'parent', types.Nested( Column('child1', types.Int32), Column('child2', types.String), ))) query = select([ table.c.parent.child1, table.c.parent.child2, ]).array_join(table.c.parent, left=True) self.assertEqual( self.compile(query), 'SELECT t1.parent.child1, t1.parent.child2 ' 'FROM t1 ' 'LEFT ARRAY JOIN t1.parent')
def test_nested_type(self): table = self.create_table( 't1', Column('x', types.Int32, primary_key=True), Column( 'parent', types.Nested( Column('child1', types.Int32), Column('child2', types.String), ))) query = select([table.c.parent.child1]).where( and_(table.c.parent.child1 == [1, 2], table.c.parent.child2 == ['foo', 'bar'])) self.assertEqual( self.compile(query, literal_binds=True), 'SELECT parent.child1 FROM t1 ' 'WHERE parent.child1 = [1, 2] ' 'AND parent.child2 = [\'foo\', \'bar\']')