def test_postgres(self): from sqlalchemy.dialects import postgres as diale_ dialect = diale_.dialect() query = str(self.group_concat_md.compile(dialect=dialect)) expected = ( '''SELECT metric_dimension.dimension_set_id, STRING_AGG(metric_dimension.name ''' '''|| '=' || metric_dimension.value, ',' ) AS dimensions ''' ''' FROM metric_dimension GROUP BY metric_dimension.dimension_set_id''') self.assertEqual(expected, query) query = str(self.group_concat_md_order.compile(dialect=dialect)) expected = ( '''SELECT metric_dimension.dimension_set_id, STRING_AGG(metric_dimension.name ''' '''|| '=' || metric_dimension.value, ',' ORDER BY metric_dimension.name ASC) ''' '''AS dimensions ''' ''' FROM metric_dimension GROUP BY metric_dimension.dimension_set_id''') self.assertEqual(expected, query) expected = ( """SELECT metric_dimension.dimension_set_id \n""" """FROM metric_dimension ORDER BY CASE WHEN metric_dimension.dimension_set_id='A'""" """ THEN 0 WHEN metric_dimension.dimension_set_id='B' THEN 1 WHEN""" """ metric_dimension.dimension_set_id='C' THEN 2 ELSE 3 END ASC""") query = str(self.order_by_field.compile(dialect=dialect)) self.assertEqual(expected, query)
def test_mysql(self): from sqlalchemy.dialects import mysql as diale_ dialect = diale_.dialect() query = str(self.group_concat_md.compile(dialect=dialect)) expected = ( '''SELECT metric_dimension.dimension_set_id, GROUP_CONCAT(concat(concat(metric_dimension.name, ''' ''''='), metric_dimension.value) SEPARATOR ',') AS dimensions ''' ''' FROM metric_dimension GROUP BY metric_dimension.dimension_set_id''') self.assertEqual(expected, query) query = str(self.group_concat_md_order.compile(dialect=dialect)) expected = ( '''SELECT metric_dimension.dimension_set_id, GROUP_CONCAT(concat(concat(metric_dimension.name, ''' ''''='), metric_dimension.value) ORDER BY metric_dimension.name ASC ''' '''SEPARATOR ',') AS dimensions ''' ''' FROM metric_dimension GROUP BY metric_dimension.dimension_set_id''') self.assertEqual(expected, query) expected = ( '''SELECT metric_dimension.dimension_set_id \n''' '''FROM metric_dimension ORDER BY FIELD(metric_dimension.dimension_set_id, 'A', 'B', 'C') ASC''' ) query = str(self.order_by_field.compile(dialect=dialect)) self.assertEqual(expected, query)
def test_postgres(self): from sqlalchemy.dialects import postgres as diale_ dialect = diale_.dialect() query = str(self.group_concat_md.compile(dialect=dialect)) expected = ('''SELECT metric_dimension.dimension_set_id, STRING_AGG(metric_dimension.name ''' '''|| '=' || metric_dimension.value, ',' ) AS dimensions ''' ''' FROM metric_dimension GROUP BY metric_dimension.dimension_set_id''') self.assertEqual(expected, query) query = str(self.group_concat_md_order.compile(dialect=dialect)) expected = ('''SELECT metric_dimension.dimension_set_id, STRING_AGG(metric_dimension.name ''' '''|| '=' || metric_dimension.value, ',' ORDER BY metric_dimension.name ASC) ''' '''AS dimensions ''' ''' FROM metric_dimension GROUP BY metric_dimension.dimension_set_id''') self.assertEqual(expected, query) expected = ("""SELECT metric_dimension.dimension_set_id \n""" """FROM metric_dimension ORDER BY CASE WHEN metric_dimension.dimension_set_id='A'""" """ THEN 0 WHEN metric_dimension.dimension_set_id='B' THEN 1 WHEN""" """ metric_dimension.dimension_set_id='C' THEN 2 ELSE 3 END ASC""") query = str(self.order_by_field.compile(dialect=dialect)) self.assertEqual(expected, query)
def test_mysql(self): from sqlalchemy.dialects import mysql as diale_ dialect = diale_.dialect() query = str(self.group_concat_md.compile(dialect=dialect)) expected = ('''SELECT metric_dimension.dimension_set_id, GROUP_CONCAT(concat(concat(metric_dimension.name, ''' ''''='), metric_dimension.value) SEPARATOR ',') AS dimensions ''' ''' FROM metric_dimension GROUP BY metric_dimension.dimension_set_id''') self.assertEqual(expected, query) query = str(self.group_concat_md_order.compile(dialect=dialect)) expected = ('''SELECT metric_dimension.dimension_set_id, GROUP_CONCAT(concat(concat(metric_dimension.name, ''' ''''='), metric_dimension.value) ORDER BY metric_dimension.name ASC ''' '''SEPARATOR ',') AS dimensions ''' ''' FROM metric_dimension GROUP BY metric_dimension.dimension_set_id''') self.assertEqual(expected, query) expected = ('''SELECT metric_dimension.dimension_set_id \n''' '''FROM metric_dimension ORDER BY FIELD(metric_dimension.dimension_set_id, 'A', 'B', 'C') ASC''') query = str(self.order_by_field.compile(dialect=dialect)) self.assertEqual(expected, query)
def get_dialect(): return postgres.dialect()