diff options
Diffstat (limited to 'test')
| -rw-r--r-- | test/dialect/mysql/test_query.py | 55 | ||||
| -rw-r--r-- | test/dialect/postgresql/test_compiler.py | 2 | ||||
| -rw-r--r-- | test/dialect/postgresql/test_types.py | 147 | ||||
| -rw-r--r-- | test/sql/test_functions.py | 129 | ||||
| -rw-r--r-- | test/sql/test_operators.py | 156 | ||||
| -rw-r--r-- | test/sql/test_selectable.py | 4 | ||||
| -rw-r--r-- | test/sql/test_types.py | 69 |
7 files changed, 528 insertions, 34 deletions
diff --git a/test/dialect/mysql/test_query.py b/test/dialect/mysql/test_query.py index f19177c2a..85513167c 100644 --- a/test/dialect/mysql/test_query.py +++ b/test/dialect/mysql/test_query.py @@ -5,7 +5,6 @@ from sqlalchemy import * from sqlalchemy.testing import fixtures, AssertsCompiledSQL from sqlalchemy import testing - class IdiosyncrasyTest(fixtures.TestBase, AssertsCompiledSQL): __only_on__ = 'mysql' __backend__ = True @@ -177,3 +176,57 @@ class MatchTest(fixtures.TestBase, AssertsCompiledSQL): eq_([1, 3, 5], [r.id for r in results]) +class AnyAllTest(fixtures.TablesTest, AssertsCompiledSQL): + __only_on__ = 'mysql' + __backend__ = True + + @classmethod + def define_tables(cls, metadata): + Table( + 'stuff', metadata, + Column('id', Integer, primary_key=True), + Column('value', Integer) + ) + + @classmethod + def insert_data(cls): + stuff = cls.tables.stuff + testing.db.execute( + stuff.insert(), + [ + {'id': 1, 'value': 1}, + {'id': 2, 'value': 2}, + {'id': 3, 'value': 3}, + {'id': 4, 'value': 4}, + {'id': 5, 'value': 5}, + ] + ) + + def test_any_w_comparator(self): + stuff = self.tables.stuff + stmt = select([stuff.c.id]).where( + stuff.c.value > any_(select([stuff.c.value]))) + + eq_( + testing.db.execute(stmt).fetchall(), + [(2,), (3,), (4,), (5,)] + ) + + def test_all_w_comparator(self): + stuff = self.tables.stuff + stmt = select([stuff.c.id]).where( + stuff.c.value >= all_(select([stuff.c.value]))) + + eq_( + testing.db.execute(stmt).fetchall(), + [(5,)] + ) + + def test_any_literal(self): + stuff = self.tables.stuff + stmt = select([4 == any_(select([stuff.c.value]))]) + + is_( + testing.db.execute(stmt).scalar(), True + ) + diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py index 1489fe24c..0407dcb81 100644 --- a/test/dialect/postgresql/test_compiler.py +++ b/test/dialect/postgresql/test_compiler.py @@ -693,7 +693,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self._test_array_zero_indexes(False) def test_array_literal_type(self): - is_(postgresql.array([1, 2]).type._type_affinity, postgresql.ARRAY) + isinstance(postgresql.array([1, 2]).type, postgresql.ARRAY) is_(postgresql.array([1, 2]).type.item_type._type_affinity, Integer) is_(postgresql.array([1, 2], type_=String). diff --git a/test/dialect/postgresql/test_types.py b/test/dialect/postgresql/test_types.py index 9d5cb4d91..a625e1cee 100644 --- a/test/dialect/postgresql/test_types.py +++ b/test/dialect/postgresql/test_types.py @@ -7,7 +7,7 @@ from sqlalchemy import testing import datetime from sqlalchemy import Table, MetaData, Column, Integer, Enum, Float, select, \ func, DateTime, Numeric, exc, String, cast, REAL, TypeDecorator, Unicode, \ - Text, null, text, column + Text, null, text, column, Array, any_, all_ from sqlalchemy.sql import operators from sqlalchemy import types import sqlalchemy as sa @@ -754,7 +754,6 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase): checkparams={'param_1': 4, 'param_3': 6, 'param_2': 5} ) - def test_array_slice_index(self): col = column('x', postgresql.ARRAY(Integer)) self.assert_compile( @@ -784,13 +783,19 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase): def test_array_index_map_dimensions(self): col = column('x', postgresql.ARRAY(Integer, dimensions=3)) is_( - col[5].type._type_affinity, postgresql.ARRAY + col[5].type._type_affinity, Array + ) + assert isinstance( + col[5].type, postgresql.ARRAY ) eq_( col[5].type.dimensions, 2 ) is_( - col[5][6].type._type_affinity, postgresql.ARRAY + col[5][6].type._type_affinity, Array + ) + assert isinstance( + col[5][6].type, postgresql.ARRAY ) eq_( col[5][6].type.dimensions, 1 @@ -816,8 +821,43 @@ class ArrayTest(AssertsCompiledSQL, fixtures.TestBase): Column('intarr', postgresql.ARRAY(Integer)), Column('strarr', postgresql.ARRAY(String)), ) - is_(arrtable.c.intarr[1:3].type._type_affinity, postgresql.ARRAY) - is_(arrtable.c.strarr[1:3].type._type_affinity, postgresql.ARRAY) + + # type affinity is Array... + is_(arrtable.c.intarr[1:3].type._type_affinity, Array) + is_(arrtable.c.strarr[1:3].type._type_affinity, Array) + + # but the slice returns the actual type + assert isinstance(arrtable.c.intarr[1:3].type, postgresql.ARRAY) + assert isinstance(arrtable.c.strarr[1:3].type, postgresql.ARRAY) + + def test_array_functions_plus_getitem(self): + """test parenthesizing of functions plus indexing, which seems + to be required by Postgresql. + + """ + stmt = select([ + func.array_cat( + array([1, 2, 3]), + array([4, 5, 6]), + type_=postgresql.ARRAY(Integer) + )[2:5] + ]) + self.assert_compile( + stmt, + "SELECT (array_cat(ARRAY[%(param_1)s, %(param_2)s, %(param_3)s], " + "ARRAY[%(param_4)s, %(param_5)s, %(param_6)s]))" + "[%(param_7)s:%(param_8)s] AS anon_1" + ) + + self.assert_compile( + func.array_cat( + array([1, 2, 3]), + array([4, 5, 6]), + type_=postgresql.ARRAY(Integer) + )[3], + "(array_cat(ARRAY[%(param_1)s, %(param_2)s, %(param_3)s], " + "ARRAY[%(param_4)s, %(param_5)s, %(param_6)s]))[%(param_7)s]" + ) class ArrayRoundTripTest(fixtures.TablesTest, AssertsExecutionResults): @@ -876,6 +916,89 @@ class ArrayRoundTripTest(fixtures.TablesTest, AssertsExecutionResults): assert isinstance(tbl.c.intarr.type.item_type, Integer) assert isinstance(tbl.c.strarr.type.item_type, String) + @testing.provide_metadata + def test_array_agg(self): + values_table = Table('values', self.metadata, Column('value', Integer)) + self.metadata.create_all(testing.db) + testing.db.execute( + values_table.insert(), + [{'value': i} for i in range(1, 10)] + ) + + stmt = select([func.array_agg(values_table.c.value)]) + eq_( + testing.db.execute(stmt).scalar(), + list(range(1, 10)) + ) + + stmt = select([func.array_agg(values_table.c.value)[3]]) + eq_( + testing.db.execute(stmt).scalar(), + 3 + ) + + stmt = select([func.array_agg(values_table.c.value)[2:4]]) + eq_( + testing.db.execute(stmt).scalar(), + [2, 3, 4] + ) + + def test_array_index_slice_exprs(self): + """test a variety of expressions that sometimes need parenthesizing""" + + stmt = select([array([1, 2, 3, 4])[2:3]]) + eq_( + testing.db.execute(stmt).scalar(), + [2, 3] + ) + + stmt = select([array([1, 2, 3, 4])[2]]) + eq_( + testing.db.execute(stmt).scalar(), + 2 + ) + + stmt = select([(array([1, 2]) + array([3, 4]))[2:3]]) + eq_( + testing.db.execute(stmt).scalar(), + [2, 3] + ) + + stmt = select([array([1, 2]) + array([3, 4])[2:3]]) + eq_( + testing.db.execute(stmt).scalar(), + [1, 2, 4] + ) + + stmt = select([array([1, 2])[2:3] + array([3, 4])]) + eq_( + testing.db.execute(stmt).scalar(), + [2, 3, 4] + ) + + stmt = select([ + func.array_cat( + array([1, 2, 3]), + array([4, 5, 6]), + type_=postgresql.ARRAY(Integer) + )[2:5] + ]) + eq_( + testing.db.execute(stmt).scalar(), [2, 3, 4, 5] + ) + + def test_any_all_exprs(self): + stmt = select([ + 3 == any_(func.array_cat( + array([1, 2, 3]), + array([4, 5, 6]), + type_=postgresql.ARRAY(Integer) + )) + ]) + eq_( + testing.db.execute(stmt).scalar(), True + ) + def test_insert_array(self): arrtable = self.tables.arrtable arrtable.insert().execute(intarr=[1, 2, 3], strarr=[util.u('abc'), @@ -1587,7 +1710,7 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase): def test_where_defined(self): self._test_where( self.hashcol.defined('foo'), - "defined(test_table.hash, %(param_1)s)" + "defined(test_table.hash, %(defined_1)s)" ) def test_where_contains(self): @@ -1618,7 +1741,7 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase): def test_cols_delete_single_key(self): self._test_cols( self.hashcol.delete('foo'), - "delete(test_table.hash, %(param_1)s) AS delete_1", + "delete(test_table.hash, %(delete_2)s) AS delete_1", True ) @@ -1633,7 +1756,7 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase): def test_cols_delete_matching_pairs(self): self._test_cols( self.hashcol.delete(hstore('1', '2')), - ("delete(test_table.hash, hstore(%(param_1)s, %(param_2)s)) " + ("delete(test_table.hash, hstore(%(hstore_1)s, %(hstore_2)s)) " "AS delete_1"), True ) @@ -1649,7 +1772,7 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase): def test_cols_hstore_pair_text(self): self._test_cols( hstore('foo', '3')['foo'], - "hstore(%(param_1)s, %(param_2)s) -> %(hstore_1)s AS anon_1", + "hstore(%(hstore_1)s, %(hstore_2)s) -> %(hstore_3)s AS anon_1", False ) @@ -1674,14 +1797,14 @@ class HStoreTest(AssertsCompiledSQL, fixtures.TestBase): self._test_cols( self.hashcol.concat(hstore(cast(self.test_table.c.id, Text), '3')), ("test_table.hash || hstore(CAST(test_table.id AS TEXT), " - "%(param_1)s) AS anon_1"), + "%(hstore_1)s) AS anon_1"), True ) def test_cols_concat_op(self): self._test_cols( hstore('foo', 'bar') + self.hashcol, - "hstore(%(param_1)s, %(param_2)s) || test_table.hash AS anon_1", + "hstore(%(hstore_1)s, %(hstore_2)s) || test_table.hash AS anon_1", True ) diff --git a/test/sql/test_functions.py b/test/sql/test_functions.py index ccc9b2dcd..51cfcb919 100644 --- a/test/sql/test_functions.py +++ b/test/sql/test_functions.py @@ -1,8 +1,8 @@ -from sqlalchemy.testing import eq_ +from sqlalchemy.testing import eq_, is_ import datetime from sqlalchemy import func, select, Integer, literal, DateTime, Table, \ Column, Sequence, MetaData, extract, Date, String, bindparam, \ - literal_column + literal_column, Array, Numeric from sqlalchemy.sql import table, column from sqlalchemy import sql, util from sqlalchemy.sql.compiler import BIND_TEMPLATES @@ -52,7 +52,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( fake_func('foo'), "fake_func(%s)" % - bindtemplate % {'name': 'param_1', 'position': 1}, + bindtemplate % {'name': 'fake_func_1', 'position': 1}, dialect=dialect) def test_use_labels(self): @@ -89,7 +89,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): def test_generic_annotation(self): fn = func.coalesce('x', 'y')._annotate({"foo": "bar"}) self.assert_compile( - fn, "coalesce(:param_1, :param_2)" + fn, "coalesce(:coalesce_1, :coalesce_2)" ) def test_custom_default_namespace(self): @@ -140,7 +140,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( func.my_func(1, 2), - "my_func(:param_1, :param_2, :param_3)" + "my_func(:my_func_1, :my_func_2, :my_func_3)" ) def test_custom_registered_identifier(self): @@ -178,7 +178,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( myfunc(1, 2, 3), - "myfunc(:param_1, :param_2, :param_3)" + "myfunc(:myfunc_1, :myfunc_2, :myfunc_3)" ) def test_namespacing_conflicts(self): @@ -188,7 +188,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): assert isinstance(func.count().type, sqltypes.Integer) self.assert_compile(func.count(), 'count(*)') - self.assert_compile(func.count(1), 'count(:param_1)') + self.assert_compile(func.count(1), 'count(:count_1)') c = column('abc') self.assert_compile(func.count(c), 'count(abc)') @@ -378,7 +378,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): def test_funcfilter_empty(self): self.assert_compile( func.count(1).filter(), - "count(:param_1)" + "count(:count_1)" ) def test_funcfilter_criterion(self): @@ -386,7 +386,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): func.count(1).filter( table1.c.name != None ), - "count(:param_1) FILTER (WHERE mytable.name IS NOT NULL)" + "count(:count_1) FILTER (WHERE mytable.name IS NOT NULL)" ) def test_funcfilter_compound_criterion(self): @@ -395,7 +395,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): table1.c.name == None, table1.c.myid > 0 ), - "count(:param_1) FILTER (WHERE mytable.name IS NULL AND " + "count(:count_1) FILTER (WHERE mytable.name IS NULL AND " "mytable.myid > :myid_1)" ) @@ -404,7 +404,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): select([func.count(1).filter( table1.c.description != None ).label('foo')]), - "SELECT count(:param_1) FILTER (WHERE mytable.description " + "SELECT count(:count_1) FILTER (WHERE mytable.description " "IS NOT NULL) AS foo FROM mytable" ) @@ -429,7 +429,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): table1.c.name == 'name' ) ]), - "SELECT count(:param_1) FILTER (WHERE mytable.name = :name_1) " + "SELECT count(:count_1) FILTER (WHERE mytable.name = :name_1) " "AS anon_1 FROM mytable" ) @@ -443,7 +443,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): table1.c.description == 'description' ) ]), - "SELECT count(:param_1) FILTER (WHERE " + "SELECT count(:count_1) FILTER (WHERE " "mytable.name = :name_1 AND mytable.description = :description_1) " "AS anon_1 FROM mytable" ) @@ -477,6 +477,70 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): "AS anon_1 FROM mytable" ) + def test_funcfilter_within_group(self): + stmt = select([ + table1.c.myid, + func.percentile_cont(0.5).within_group( + table1.c.name + ) + ]) + self.assert_compile( + stmt, + "SELECT mytable.myid, percentile_cont(:percentile_cont_1) " + "WITHIN GROUP (ORDER BY mytable.name) " + "AS anon_1 " + "FROM mytable", + {'percentile_cont_1': 0.5} + ) + + def test_funcfilter_within_group_multi(self): + stmt = select([ + table1.c.myid, + func.percentile_cont(0.5).within_group( + table1.c.name, table1.c.description + ) + ]) + self.assert_compile( + stmt, + "SELECT mytable.myid, percentile_cont(:percentile_cont_1) " + "WITHIN GROUP (ORDER BY mytable.name, mytable.description) " + "AS anon_1 " + "FROM mytable", + {'percentile_cont_1': 0.5} + ) + + def test_funcfilter_within_group_desc(self): + stmt = select([ + table1.c.myid, + func.percentile_cont(0.5).within_group( + table1.c.name.desc() + ) + ]) + self.assert_compile( + stmt, + "SELECT mytable.myid, percentile_cont(:percentile_cont_1) " + "WITHIN GROUP (ORDER BY mytable.name DESC) " + "AS anon_1 " + "FROM mytable", + {'percentile_cont_1': 0.5} + ) + + def test_funcfilter_within_group_w_over(self): + stmt = select([ + table1.c.myid, + func.percentile_cont(0.5).within_group( + table1.c.name.desc() + ).over(partition_by=table1.c.description) + ]) + self.assert_compile( + stmt, + "SELECT mytable.myid, percentile_cont(:percentile_cont_1) " + "WITHIN GROUP (ORDER BY mytable.name DESC) " + "OVER (PARTITION BY mytable.description) AS anon_1 " + "FROM mytable", + {'percentile_cont_1': 0.5} + ) + def test_incorrect_none_type(self): class MissingType(FunctionElement): name = 'mt' @@ -490,6 +554,45 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): ) +class ReturnTypeTest(fixtures.TestBase): + + def test_array_agg(self): + expr = func.array_agg(column('data', Integer)) + is_(expr.type._type_affinity, Array) + is_(expr.type.item_type._type_affinity, Integer) + + def test_mode(self): + expr = func.mode(0.5).within_group( + column('data', Integer).desc()) + is_(expr.type._type_affinity, Integer) + + def test_percentile_cont(self): + expr = func.percentile_cont(0.5).within_group(column('data', Integer)) + is_(expr.type._type_affinity, Integer) + + def test_percentile_cont_array(self): + expr = func.percentile_cont(0.5, 0.7).within_group( + column('data', Integer)) + is_(expr.type._type_affinity, Array) + is_(expr.type.item_type._type_affinity, Integer) + + def test_percentile_cont_array_desc(self): + expr = func.percentile_cont(0.5, 0.7).within_group( + column('data', Integer).desc()) + is_(expr.type._type_affinity, Array) + is_(expr.type.item_type._type_affinity, Integer) + + def test_cume_dist(self): + expr = func.cume_dist(0.5).within_group( + column('data', Integer).desc()) + is_(expr.type._type_affinity, Numeric) + + def test_percent_rank(self): + expr = func.percent_rank(0.5).within_group( + column('data', Integer)) + is_(expr.type._type_affinity, Numeric) + + class ExecuteTest(fixtures.TestBase): @engines.close_first diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py index f3dfd2daf..03c0f89be 100644 --- a/test/sql/test_operators.py +++ b/test/sql/test_operators.py @@ -1,7 +1,8 @@ from sqlalchemy.testing import fixtures, eq_, is_, is_not_ from sqlalchemy import testing from sqlalchemy.testing import assert_raises_message -from sqlalchemy.sql import column, desc, asc, literal, collate, null, true, false +from sqlalchemy.sql import column, desc, asc, literal, collate, null, \ + true, false, any_, all_ from sqlalchemy.sql.expression import BinaryExpression, \ ClauseList, Grouping, \ UnaryExpression, select, union, func, tuple_ @@ -14,7 +15,7 @@ from sqlalchemy.sql.elements import _literal_as_text from sqlalchemy.schema import Column, Table, MetaData from sqlalchemy.sql import compiler from sqlalchemy.types import TypeEngine, TypeDecorator, UserDefinedType, \ - Boolean, NullType, MatchType, Indexable, Concatenable + Boolean, NullType, MatchType, Indexable, Concatenable, Array from sqlalchemy.dialects import mysql, firebird, postgresql, oracle, \ sqlite, mssql from sqlalchemy import util @@ -2262,3 +2263,154 @@ class TupleTypingTest(fixtures.TestBase): eq_(len(expr.right.clauses), 2) for elem in expr.right.clauses: self._assert_types(elem) + + +class AnyAllTest(fixtures.TestBase, testing.AssertsCompiledSQL): + __dialect__ = 'default' + + def _fixture(self): + m = MetaData() + + t = Table( + 'tab1', m, + Column('arrval', Array(Integer)), + Column('data', Integer) + ) + return t + + def test_any_array(self): + t = self._fixture() + + self.assert_compile( + 5 == any_(t.c.arrval), + ":param_1 = ANY (tab1.arrval)", + checkparams={"param_1": 5} + ) + + def test_all_array(self): + t = self._fixture() + + self.assert_compile( + 5 == all_(t.c.arrval), + ":param_1 = ALL (tab1.arrval)", + checkparams={"param_1": 5} + ) + + def test_any_comparator_array(self): + t = self._fixture() + + self.assert_compile( + 5 > any_(t.c.arrval), + ":param_1 > ANY (tab1.arrval)", + checkparams={"param_1": 5} + ) + + def test_all_comparator_array(self): + t = self._fixture() + + self.assert_compile( + 5 > all_(t.c.arrval), + ":param_1 > ALL (tab1.arrval)", + checkparams={"param_1": 5} + ) + + def test_any_comparator_array_wexpr(self): + t = self._fixture() + + self.assert_compile( + t.c.data > any_(t.c.arrval), + "tab1.data > ANY (tab1.arrval)", + checkparams={} + ) + + def test_all_comparator_array_wexpr(self): + t = self._fixture() + + self.assert_compile( + t.c.data > all_(t.c.arrval), + "tab1.data > ALL (tab1.arrval)", + checkparams={} + ) + + def test_illegal_ops(self): + t = self._fixture() + + assert_raises_message( + exc.ArgumentError, + "Only comparison operators may be used with ANY/ALL", + lambda: 5 + all_(t.c.arrval) + ) + + # TODO: + # this is invalid but doesn't raise an error, + # as the left-hand side just does its thing. Types + # would need to reject their right-hand side. + self.assert_compile( + t.c.data + all_(t.c.arrval), + "tab1.data + ALL (tab1.arrval)" + ) + + def test_any_array_comparator_accessor(self): + t = self._fixture() + + self.assert_compile( + t.c.arrval.any(5, operator.gt), + ":param_1 > ANY (tab1.arrval)", + checkparams={"param_1": 5} + ) + + def test_all_array_comparator_accessor(self): + t = self._fixture() + + self.assert_compile( + t.c.arrval.all(5, operator.gt), + ":param_1 > ALL (tab1.arrval)", + checkparams={"param_1": 5} + ) + + def test_any_array_expression(self): + t = self._fixture() + + self.assert_compile( + 5 == any_(t.c.arrval[5:6] + postgresql.array([3, 4])), + "%(param_1)s = ANY (tab1.arrval[%(arrval_1)s:%(arrval_2)s] || " + "ARRAY[%(param_2)s, %(param_3)s])", + checkparams={ + 'arrval_2': 6, 'param_1': 5, 'param_3': 4, + 'arrval_1': 5, 'param_2': 3}, + dialect='postgresql' + ) + + def test_all_array_expression(self): + t = self._fixture() + + self.assert_compile( + 5 == all_(t.c.arrval[5:6] + postgresql.array([3, 4])), + "%(param_1)s = ALL (tab1.arrval[%(arrval_1)s:%(arrval_2)s] || " + "ARRAY[%(param_2)s, %(param_3)s])", + checkparams={ + 'arrval_2': 6, 'param_1': 5, 'param_3': 4, + 'arrval_1': 5, 'param_2': 3}, + dialect='postgresql' + ) + + def test_any_subq(self): + t = self._fixture() + + self.assert_compile( + 5 == any_(select([t.c.data]).where(t.c.data < 10)), + ":param_1 = ANY (SELECT tab1.data " + "FROM tab1 WHERE tab1.data < :data_1)", + checkparams={'data_1': 10, 'param_1': 5} + ) + + def test_all_subq(self): + t = self._fixture() + + self.assert_compile( + 5 == all_(select([t.c.data]).where(t.c.data < 10)), + ":param_1 = ALL (SELECT tab1.data " + "FROM tab1 WHERE tab1.data < :data_1)", + checkparams={'data_1': 10, 'param_1': 5} + ) + diff --git a/test/sql/test_selectable.py b/test/sql/test_selectable.py index 4a332a4d1..b9cbbf480 100644 --- a/test/sql/test_selectable.py +++ b/test/sql/test_selectable.py @@ -932,10 +932,10 @@ class AnonLabelTest(fixtures.TestBase): c1 = func.count('*') assert c1.label(None) is not c1 - eq_(str(select([c1])), "SELECT count(:param_1) AS count_1") + eq_(str(select([c1])), "SELECT count(:count_2) AS count_1") c2 = select([c1]).compile() - eq_(str(select([c1.label(None)])), "SELECT count(:param_1) AS count_1") + eq_(str(select([c1.label(None)])), "SELECT count(:count_2) AS count_1") def test_named_labels_named_column(self): c1 = column('x') diff --git a/test/sql/test_types.py b/test/sql/test_types.py index d562c83ce..e32126a18 100644 --- a/test/sql/test_types.py +++ b/test/sql/test_types.py @@ -10,7 +10,7 @@ from sqlalchemy import ( and_, func, Date, LargeBinary, literal, cast, text, Enum, type_coerce, VARCHAR, Time, DateTime, BigInteger, SmallInteger, BOOLEAN, BLOB, NCHAR, NVARCHAR, CLOB, TIME, DATE, DATETIME, TIMESTAMP, SMALLINT, - INTEGER, DECIMAL, NUMERIC, FLOAT, REAL) + INTEGER, DECIMAL, NUMERIC, FLOAT, REAL, Array) from sqlalchemy.sql import ddl from sqlalchemy import inspection from sqlalchemy import exc, types, util, dialects @@ -28,6 +28,7 @@ from sqlalchemy.testing.util import round_decimal from sqlalchemy.testing import fixtures from sqlalchemy.testing import mock + class AdaptTest(fixtures.TestBase): def _all_dialect_modules(self): @@ -138,7 +139,7 @@ class AdaptTest(fixtures.TestBase): for is_down_adaption, typ, target_adaptions in adaptions(): if typ in (types.TypeDecorator, types.TypeEngine, types.Variant): continue - elif typ is dialects.postgresql.ARRAY: + elif issubclass(typ, Array): t1 = typ(String) else: t1 = typ() @@ -188,7 +189,7 @@ class AdaptTest(fixtures.TestBase): for typ in self._all_types(): if typ in (types.TypeDecorator, types.TypeEngine, types.Variant): continue - elif typ is dialects.postgresql.ARRAY: + elif issubclass(typ, Array): t1 = typ(String) else: t1 = typ() @@ -1343,6 +1344,68 @@ class BinaryTest(fixtures.TestBase, AssertsExecutionResults): with open(f, mode='rb') as o: return o.read() + +class ArrayTest(fixtures.TestBase): + + def _myarray_fixture(self): + class MyArray(Array): + pass + return MyArray + + def test_array_index_map_dimensions(self): + col = column('x', Array(Integer, dimensions=3)) + is_( + col[5].type._type_affinity, Array + ) + eq_( + col[5].type.dimensions, 2 + ) + is_( + col[5][6].type._type_affinity, Array + ) + eq_( + col[5][6].type.dimensions, 1 + ) + is_( + col[5][6][7].type._type_affinity, Integer + ) + + def test_array_getitem_single_type(self): + m = MetaData() + arrtable = Table( + 'arrtable', m, + Column('intarr', Array(Integer)), + Column('strarr', Array(String)), + ) + is_(arrtable.c.intarr[1].type._type_affinity, Integer) + is_(arrtable.c.strarr[1].type._type_affinity, String) + + def test_array_getitem_slice_type(self): + m = MetaData() + arrtable = Table( + 'arrtable', m, + Column('intarr', Array(Integer)), + Column('strarr', Array(String)), + ) + is_(arrtable.c.intarr[1:3].type._type_affinity, Array) + is_(arrtable.c.strarr[1:3].type._type_affinity, Array) + + def test_array_getitem_slice_type_dialect_level(self): + MyArray = self._myarray_fixture() + m = MetaData() + arrtable = Table( + 'arrtable', m, + Column('intarr', MyArray(Integer)), + Column('strarr', MyArray(String)), + ) + is_(arrtable.c.intarr[1:3].type._type_affinity, Array) + is_(arrtable.c.strarr[1:3].type._type_affinity, Array) + + # but the slice returns the actual type + assert isinstance(arrtable.c.intarr[1:3].type, MyArray) + assert isinstance(arrtable.c.strarr[1:3].type, MyArray) + + test_table = meta = MyCustomType = MyTypeDec = None |