From 9fb6acad67145d0b0b973a7a074eb5b2baf45086 Mon Sep 17 00:00:00 2001 From: "Ryan P. Kelly" Date: Thu, 20 Nov 2014 14:40:32 -0500 Subject: Report the type of unexpected expression objects --- test/sql/test_defaults.py | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'test/sql') diff --git a/test/sql/test_defaults.py b/test/sql/test_defaults.py index 10e557b76..21d04ea38 100644 --- a/test/sql/test_defaults.py +++ b/test/sql/test_defaults.py @@ -368,7 +368,8 @@ class DefaultTest(fixtures.TestBase): ): assert_raises_message( sa.exc.ArgumentError, - "SQL expression object or string expected.", + "SQL expression object or string expected, got object of type " + " instead", t.select, [const] ) assert_raises_message( -- cgit v1.2.1 From f5ff86983f9cc7914a89b96da1fd2638677d345b Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 4 Dec 2014 18:29:56 -0500 Subject: - The :meth:`.Operators.match` operator is now handled such that the return type is not strictly assumed to be boolean; it now returns a :class:`.Boolean` subclass called :class:`.MatchType`. The type will still produce boolean behavior when used in Python expressions, however the dialect can override its behavior at result time. In the case of MySQL, while the MATCH operator is typically used in a boolean context within an expression, if one actually queries for the value of a match expression, a floating point value is returned; this value is not compatible with SQLAlchemy's C-based boolean processor, so MySQL's result-set behavior now follows that of the :class:`.Float` type. A new operator object ``notmatch_op`` is also added to better allow dialects to define the negation of a match operation. fixes #3263 --- test/sql/test_operators.py | 28 +++++++++++++++++++++++++++- 1 file changed, 27 insertions(+), 1 deletion(-) (limited to 'test/sql') diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py index e8ad88511..f8ac1528f 100644 --- a/test/sql/test_operators.py +++ b/test/sql/test_operators.py @@ -12,7 +12,8 @@ from sqlalchemy import exc from sqlalchemy.engine import default from sqlalchemy.sql.elements import _literal_as_text from sqlalchemy.schema import Column, Table, MetaData -from sqlalchemy.types import TypeEngine, TypeDecorator, UserDefinedType, Boolean +from sqlalchemy.types import TypeEngine, TypeDecorator, UserDefinedType, \ + Boolean, NullType, MatchType from sqlalchemy.dialects import mysql, firebird, postgresql, oracle, \ sqlite, mssql from sqlalchemy import util @@ -1619,6 +1620,31 @@ class MatchTest(fixtures.TestBase, testing.AssertsCompiledSQL): "CONTAINS (mytable.myid, :myid_1)", dialect=oracle.dialect()) + def test_match_is_now_matchtype(self): + expr = self.table1.c.myid.match('somstr') + assert expr.type._type_affinity is MatchType()._type_affinity + assert isinstance(expr.type, MatchType) + + def test_boolean_inversion_postgresql(self): + self.assert_compile( + ~self.table1.c.myid.match('somstr'), + "NOT mytable.myid @@ to_tsquery(%(myid_1)s)", + dialect=postgresql.dialect()) + + def test_boolean_inversion_mysql(self): + # because mysql doesnt have native boolean + self.assert_compile( + ~self.table1.c.myid.match('somstr'), + "NOT MATCH (mytable.myid) AGAINST (%s IN BOOLEAN MODE)", + dialect=mysql.dialect()) + + def test_boolean_inversion_mssql(self): + # because mssql doesnt have native boolean + self.assert_compile( + ~self.table1.c.myid.match('somstr'), + "NOT CONTAINS (mytable.myid, :myid_1)", + dialect=mssql.dialect()) + class ComposedLikeOperatorsTest(fixtures.TestBase, testing.AssertsCompiledSQL): __dialect__ = 'default' -- cgit v1.2.1 From e46c71b4198ee9811ea851dbe037f19a74af0b08 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 4 Dec 2014 19:35:00 -0500 Subject: - Added support for CTEs under Oracle. This includes some tweaks to the aliasing syntax, as well as a new CTE feature :meth:`.CTE.suffix_with`, which is useful for adding in special Oracle-specific directives to the CTE. fixes #3220 --- test/sql/test_cte.py | 30 ++++++++++++++++++++++++++++++ 1 file changed, 30 insertions(+) (limited to 'test/sql') diff --git a/test/sql/test_cte.py b/test/sql/test_cte.py index b907fe649..c7906dcb7 100644 --- a/test/sql/test_cte.py +++ b/test/sql/test_cte.py @@ -462,3 +462,33 @@ class CTETest(fixtures.TestBase, AssertsCompiledSQL): 'FROM "order" JOIN regional_sales AS anon_1 ' 'ON anon_1."order" = "order"."order"' ) + + def test_suffixes(self): + orders = table('order', column('order')) + s = select([orders.c.order]).cte("regional_sales") + s = s.suffix_with("pg suffix", dialect='postgresql') + s = s.suffix_with('oracle suffix', dialect='oracle') + stmt = select([orders]).where(orders.c.order > s.c.order) + + self.assert_compile( + stmt, + 'WITH regional_sales AS (SELECT "order"."order" AS "order" ' + 'FROM "order") SELECT "order"."order" FROM "order", ' + 'regional_sales WHERE "order"."order" > regional_sales."order"' + ) + + self.assert_compile( + stmt, + 'WITH regional_sales AS (SELECT "order"."order" AS "order" ' + 'FROM "order") oracle suffix SELECT "order"."order" FROM "order", ' + 'regional_sales WHERE "order"."order" > regional_sales."order"', + dialect='oracle' + ) + + self.assert_compile( + stmt, + 'WITH regional_sales AS (SELECT "order"."order" AS "order" ' + 'FROM "order") pg suffix SELECT "order"."order" FROM "order", ' + 'regional_sales WHERE "order"."order" > regional_sales."order"', + dialect='postgresql' + ) \ No newline at end of file -- cgit v1.2.1 From 60174146410d4ce2a17faa76cd981f558490db92 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 4 Dec 2014 19:45:14 -0500 Subject: - the refactor of the visit_alias() method in Oracle revealed that quoting should be applied in %(name)s under with_hint. --- test/sql/test_compiler.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'test/sql') diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index 9e99a947b..428fc8986 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -2440,7 +2440,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): """SELECT /*+ "QuotedName" idx1 */ "QuotedName".col1 """ """FROM "QuotedName" WHERE "QuotedName".col1 > :col1_1"""), (s7, oracle_d, - """SELECT /*+ SomeName idx1 */ "SomeName".col1 FROM """ + """SELECT /*+ "SomeName" idx1 */ "SomeName".col1 FROM """ """"QuotedName" "SomeName" WHERE "SomeName".col1 > :col1_1"""), ]: self.assert_compile( -- cgit v1.2.1 From c24423bc2e3fd227bf4a86599e28407bd190ee9e Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sat, 6 Dec 2014 13:29:32 -0500 Subject: - enhance only_on() to work with compound specs - fix "temporary_tables" requirement --- test/sql/test_metadata.py | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'test/sql') diff --git a/test/sql/test_metadata.py b/test/sql/test_metadata.py index 0aa5d7305..52ecf88c5 100644 --- a/test/sql/test_metadata.py +++ b/test/sql/test_metadata.py @@ -1160,9 +1160,10 @@ class InfoTest(fixtures.TestBase): t = Table('x', MetaData(), info={'foo': 'bar'}) eq_(t.info, {'foo': 'bar'}) + class TableTest(fixtures.TestBase, AssertsCompiledSQL): - @testing.requires.temporary_table + @testing.requires.temporary_tables @testing.skip_if('mssql', 'different col format') def test_prefixes(self): from sqlalchemy import Table -- cgit v1.2.1 From d1ac6cb33af3b105db7cdb51411e10ac3bafff1f Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Fri, 19 Dec 2014 12:14:52 -0500 Subject: - Fixed bug where using a :class:`.TypeDecorator` that implemented a type that was also a :class:`.TypeDecorator` would fail with Python's "Cannot create a consistent method resolution order (MRO)" error, when any kind of SQL comparison expression were used against an object using this type. --- test/sql/test_operators.py | 25 +++++++++++++++++++++++++ 1 file changed, 25 insertions(+) (limited to 'test/sql') diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py index f8ac1528f..3b8b20513 100644 --- a/test/sql/test_operators.py +++ b/test/sql/test_operators.py @@ -393,6 +393,31 @@ class TypeDecoratorComparatorTest(_CustomComparatorTests, fixtures.TestBase): return MyInteger +class TypeDecoratorTypeDecoratorComparatorTest( + _CustomComparatorTests, fixtures.TestBase): + + def _add_override_factory(self): + + class MyIntegerOne(TypeDecorator): + impl = Integer + + class comparator_factory(TypeDecorator.Comparator): + + def __init__(self, expr): + self.expr = expr + + def __add__(self, other): + return self.expr.op("goofy")(other) + + def __and__(self, other): + return self.expr.op("goofy_and")(other) + + class MyIntegerTwo(TypeDecorator): + impl = MyIntegerOne + + return MyIntegerTwo + + class TypeDecoratorWVariantComparatorTest( _CustomComparatorTests, fixtures.TestBase): -- cgit v1.2.1 From 8f5e4acbf693a375ad687977188a32bc941fd33b Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 1 Jan 2015 13:24:32 -0500 Subject: - Added a new accessor :attr:`.Table.foreign_key_constraints` to complement the :attr:`.Table.foreign_keys` collection, as well as :attr:`.ForeignKeyConstraint.referred_table`. --- test/sql/test_metadata.py | 40 ++++++++++++++++++++++++++++++++++++++++ 1 file changed, 40 insertions(+) (limited to 'test/sql') diff --git a/test/sql/test_metadata.py b/test/sql/test_metadata.py index 52ecf88c5..cc7d0eb4f 100644 --- a/test/sql/test_metadata.py +++ b/test/sql/test_metadata.py @@ -1196,6 +1196,30 @@ class TableTest(fixtures.TestBase, AssertsCompiledSQL): t.info['bar'] = 'zip' assert t.info['bar'] == 'zip' + def test_foreign_key_constraints_collection(self): + metadata = MetaData() + t1 = Table('foo', metadata, Column('a', Integer)) + eq_(t1.foreign_key_constraints, set()) + + fk1 = ForeignKey('q.id') + fk2 = ForeignKey('j.id') + fk3 = ForeignKeyConstraint(['b', 'c'], ['r.x', 'r.y']) + + t1.append_column(Column('b', Integer, fk1)) + eq_( + t1.foreign_key_constraints, + set([fk1.constraint])) + + t1.append_column(Column('c', Integer, fk2)) + eq_( + t1.foreign_key_constraints, + set([fk1.constraint, fk2.constraint])) + + t1.append_constraint(fk3) + eq_( + t1.foreign_key_constraints, + set([fk1.constraint, fk2.constraint, fk3])) + def test_c_immutable(self): m = MetaData() t1 = Table('t', m, Column('x', Integer), Column('y', Integer)) @@ -1947,6 +1971,22 @@ class ConstraintTest(fixtures.TestBase): assert s1.c.a.references(t1.c.a) assert not s1.c.a.references(t1.c.b) + def test_referred_table_accessor(self): + t1, t2, t3 = self._single_fixture() + fkc = list(t2.foreign_key_constraints)[0] + is_(fkc.referred_table, t1) + + def test_referred_table_accessor_not_available(self): + t1 = Table('t', MetaData(), Column('x', ForeignKey('q.id'))) + fkc = list(t1.foreign_key_constraints)[0] + assert_raises_message( + exc.InvalidRequestError, + "Foreign key associated with column 't.x' could not find " + "table 'q' with which to generate a foreign key to target " + "column 'id'", + getattr, fkc, "referred_table" + ) + def test_related_column_not_present_atfirst_ok(self): m = MetaData() base_table = Table("base", m, -- cgit v1.2.1 From 21f47124ab433cc74fa0a72efcc8a6c1e9c37db5 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 1 Jan 2015 13:47:08 -0500 Subject: - restate sort_tables in terms of a more fine grained sort_tables_and_constraints function. - The DDL generation system of :meth:`.MetaData.create_all` and :meth:`.Metadata.drop_all` has been enhanced to in most cases automatically handle the case of mutually dependent foreign key constraints; the need for the :paramref:`.ForeignKeyConstraint.use_alter` flag is greatly reduced. The system also works for constraints which aren't given a name up front; only in the case of DROP is a name required for at least one of the constraints involved in the cycle. fixes #3282 --- test/sql/test_constraints.py | 207 +++++++++++++++++++++++++++++++++++++------ test/sql/test_ddlemit.py | 67 +++++++++++++- 2 files changed, 247 insertions(+), 27 deletions(-) (limited to 'test/sql') diff --git a/test/sql/test_constraints.py b/test/sql/test_constraints.py index c0b5806ac..604b5efeb 100644 --- a/test/sql/test_constraints.py +++ b/test/sql/test_constraints.py @@ -58,8 +58,77 @@ class ConstraintGenTest(fixtures.TestBase, AssertsExecutionResults): ) ) + @testing.force_drop_names('a', 'b') + def test_fk_cant_drop_cycled_unnamed(self): + metadata = MetaData() + + Table("a", metadata, + Column('id', Integer, primary_key=True), + Column('bid', Integer), + ForeignKeyConstraint(["bid"], ["b.id"]) + ) + Table( + "b", metadata, + Column('id', Integer, primary_key=True), + Column("aid", Integer), + ForeignKeyConstraint(["aid"], ["a.id"])) + metadata.create_all(testing.db) + if testing.db.dialect.supports_alter: + assert_raises_message( + exc.CircularDependencyError, + "Can't sort tables for DROP; an unresolvable foreign key " + "dependency exists between tables: a, b. Please ensure " + "that the ForeignKey and ForeignKeyConstraint objects " + "involved in the cycle have names so that they can be " + "dropped using DROP CONSTRAINT.", + metadata.drop_all, testing.db + ) + else: + + with self.sql_execution_asserter() as asserter: + metadata.drop_all(testing.db, checkfirst=False) + + asserter.assert_( + AllOf( + CompiledSQL("DROP TABLE a"), + CompiledSQL("DROP TABLE b") + ) + ) + + @testing.provide_metadata + def test_fk_table_auto_alter_constraint_create(self): + metadata = self.metadata + + Table("a", metadata, + Column('id', Integer, primary_key=True), + Column('bid', Integer), + ForeignKeyConstraint(["bid"], ["b.id"]) + ) + Table( + "b", metadata, + Column('id', Integer, primary_key=True), + Column("aid", Integer), + ForeignKeyConstraint(["aid"], ["a.id"], name="bfk")) + self._assert_cyclic_constraint(metadata, auto=True) + + @testing.provide_metadata + def test_fk_column_auto_alter_constraint_create(self): + metadata = self.metadata + + Table("a", metadata, + Column('id', Integer, primary_key=True), + Column('bid', Integer, ForeignKey("b.id")), + ) + Table("b", metadata, + Column('id', Integer, primary_key=True), + Column("aid", Integer, + ForeignKey("a.id", name="bfk") + ), + ) + self._assert_cyclic_constraint(metadata, auto=True) + @testing.provide_metadata - def test_cyclic_fk_table_constraint_create(self): + def test_fk_table_use_alter_constraint_create(self): metadata = self.metadata Table("a", metadata, @@ -75,7 +144,7 @@ class ConstraintGenTest(fixtures.TestBase, AssertsExecutionResults): self._assert_cyclic_constraint(metadata) @testing.provide_metadata - def test_cyclic_fk_column_constraint_create(self): + def test_fk_column_use_alter_constraint_create(self): metadata = self.metadata Table("a", metadata, @@ -90,45 +159,104 @@ class ConstraintGenTest(fixtures.TestBase, AssertsExecutionResults): ) self._assert_cyclic_constraint(metadata) - def _assert_cyclic_constraint(self, metadata): - assertions = [ - CompiledSQL('CREATE TABLE b (' + def _assert_cyclic_constraint(self, metadata, auto=False): + table_assertions = [] + if auto: + if testing.db.dialect.supports_alter: + table_assertions.append( + CompiledSQL('CREATE TABLE b (' + 'id INTEGER NOT NULL, ' + 'aid INTEGER, ' + 'PRIMARY KEY (id)' + ')' + ) + ) + else: + table_assertions.append( + CompiledSQL( + 'CREATE TABLE b (' 'id INTEGER NOT NULL, ' 'aid INTEGER, ' + 'PRIMARY KEY (id), ' + 'CONSTRAINT bfk FOREIGN KEY(aid) REFERENCES a (id)' + ')' + ) + ) + + if testing.db.dialect.supports_alter: + table_assertions.append( + CompiledSQL( + 'CREATE TABLE a (' + 'id INTEGER NOT NULL, ' + 'bid INTEGER, ' 'PRIMARY KEY (id)' ')' - ), - CompiledSQL('CREATE TABLE a (' + ) + ) + else: + table_assertions.append( + CompiledSQL( + 'CREATE TABLE a (' 'id INTEGER NOT NULL, ' 'bid INTEGER, ' 'PRIMARY KEY (id), ' 'FOREIGN KEY(bid) REFERENCES b (id)' ')' - ), - ] + ) + ) + else: + table_assertions.append( + CompiledSQL('CREATE TABLE b (' + 'id INTEGER NOT NULL, ' + 'aid INTEGER, ' + 'PRIMARY KEY (id)' + ')' + ) + ) + table_assertions.append( + CompiledSQL( + 'CREATE TABLE a (' + 'id INTEGER NOT NULL, ' + 'bid INTEGER, ' + 'PRIMARY KEY (id), ' + 'FOREIGN KEY(bid) REFERENCES b (id)' + ')' + ) + ) + + assertions = [AllOf(*table_assertions)] if testing.db.dialect.supports_alter: - assertions.append( + fk_assertions = [] + fk_assertions.append( CompiledSQL('ALTER TABLE b ADD CONSTRAINT bfk ' 'FOREIGN KEY(aid) REFERENCES a (id)') ) - self.assert_sql_execution( - testing.db, - lambda: metadata.create_all(checkfirst=False), - *assertions - ) + if auto: + fk_assertions.append( + CompiledSQL('ALTER TABLE a ADD ' + 'FOREIGN KEY(bid) REFERENCES b (id)') + ) + assertions.append(AllOf(*fk_assertions)) + + with self.sql_execution_asserter() as asserter: + metadata.create_all(checkfirst=False) + asserter.assert_(*assertions) - assertions = [] if testing.db.dialect.supports_alter: - assertions.append(CompiledSQL('ALTER TABLE b DROP CONSTRAINT bfk')) - assertions.extend([ - CompiledSQL("DROP TABLE a"), - CompiledSQL("DROP TABLE b"), - ]) - self.assert_sql_execution( - testing.db, - lambda: metadata.drop_all(checkfirst=False), - *assertions - ) + assertions = [ + CompiledSQL('ALTER TABLE b DROP CONSTRAINT bfk'), + CompiledSQL("DROP TABLE a"), + CompiledSQL("DROP TABLE b") + ] + else: + assertions = [AllOf( + CompiledSQL("DROP TABLE a"), + CompiledSQL("DROP TABLE b") + )] + + with self.sql_execution_asserter() as asserter: + metadata.drop_all(checkfirst=False), + asserter.assert_(*assertions) @testing.requires.check_constraints @testing.provide_metadata @@ -542,6 +670,33 @@ class ConstraintCompilationTest(fixtures.TestBase, AssertsCompiledSQL): "REFERENCES tbl (a) MATCH SIMPLE" ) + def test_create_table_omit_fks(self): + fkcs = [ + ForeignKeyConstraint(['a'], ['remote.id'], name='foo'), + ForeignKeyConstraint(['b'], ['remote.id'], name='bar'), + ForeignKeyConstraint(['c'], ['remote.id'], name='bat'), + ] + m = MetaData() + t = Table( + 't', m, + Column('a', Integer), + Column('b', Integer), + Column('c', Integer), + *fkcs + ) + Table('remote', m, Column('id', Integer, primary_key=True)) + + self.assert_compile( + schema.CreateTable(t, include_foreign_key_constraints=[]), + "CREATE TABLE t (a INTEGER, b INTEGER, c INTEGER)" + ) + self.assert_compile( + schema.CreateTable(t, include_foreign_key_constraints=fkcs[0:2]), + "CREATE TABLE t (a INTEGER, b INTEGER, c INTEGER, " + "CONSTRAINT foo FOREIGN KEY(a) REFERENCES remote (id), " + "CONSTRAINT bar FOREIGN KEY(b) REFERENCES remote (id))" + ) + def test_deferrable_unique(self): factory = lambda **kw: UniqueConstraint('b', **kw) self._test_deferrable(factory) diff --git a/test/sql/test_ddlemit.py b/test/sql/test_ddlemit.py index 825f8228b..e191beed3 100644 --- a/test/sql/test_ddlemit.py +++ b/test/sql/test_ddlemit.py @@ -1,6 +1,6 @@ from sqlalchemy.testing import fixtures from sqlalchemy.sql.ddl import SchemaGenerator, SchemaDropper -from sqlalchemy import MetaData, Table, Column, Integer, Sequence +from sqlalchemy import MetaData, Table, Column, Integer, Sequence, ForeignKey from sqlalchemy import schema from sqlalchemy.testing.mock import Mock @@ -42,6 +42,31 @@ class EmitDDLTest(fixtures.TestBase): for i in range(1, 6) ) + def _use_alter_fixture_one(self): + m = MetaData() + + t1 = Table( + 't1', m, Column('id', Integer, primary_key=True), + Column('t2id', Integer, ForeignKey('t2.id')) + ) + t2 = Table( + 't2', m, Column('id', Integer, primary_key=True), + Column('t1id', Integer, ForeignKey('t1.id')) + ) + return m, t1, t2 + + def _fk_fixture_one(self): + m = MetaData() + + t1 = Table( + 't1', m, Column('id', Integer, primary_key=True), + Column('t2id', Integer, ForeignKey('t2.id')) + ) + t2 = Table( + 't2', m, Column('id', Integer, primary_key=True), + ) + return m, t1, t2 + def _table_seq_fixture(self): m = MetaData() @@ -172,6 +197,32 @@ class EmitDDLTest(fixtures.TestBase): self._assert_drop_tables([t1, t2, t3, t4, t5], generator, m) + def test_create_metadata_auto_alter_fk(self): + m, t1, t2 = self._use_alter_fixture_one() + generator = self._mock_create_fixture( + False, [t1, t2] + ) + self._assert_create_w_alter( + [t1, t2] + + list(t1.foreign_key_constraints) + + list(t2.foreign_key_constraints), + generator, + m + ) + + def test_create_metadata_inline_fk(self): + m, t1, t2 = self._fk_fixture_one() + generator = self._mock_create_fixture( + False, [t1, t2] + ) + self._assert_create_w_alter( + [t1, t2] + + list(t1.foreign_key_constraints) + + list(t2.foreign_key_constraints), + generator, + m + ) + def _assert_create_tables(self, elements, generator, argument): self._assert_ddl(schema.CreateTable, elements, generator, argument) @@ -188,6 +239,16 @@ class EmitDDLTest(fixtures.TestBase): (schema.DropTable, schema.DropSequence), elements, generator, argument) + def _assert_create_w_alter(self, elements, generator, argument): + self._assert_ddl( + (schema.CreateTable, schema.CreateSequence, schema.AddConstraint), + elements, generator, argument) + + def _assert_drop_w_alter(self, elements, generator, argument): + self._assert_ddl( + (schema.DropTable, schema.DropSequence, schema.DropConstraint), + elements, generator, argument) + def _assert_ddl(self, ddl_cls, elements, generator, argument): generator.traverse_single(argument) for call_ in generator.connection.execute.mock_calls: @@ -196,4 +257,8 @@ class EmitDDLTest(fixtures.TestBase): assert c.element in elements, "element %r was not expected"\ % c.element elements.remove(c.element) + if getattr(c, 'include_foreign_key_constraints', None) is not None: + elements[:] = [ + e for e in elements + if e not in set(c.include_foreign_key_constraints)] assert not elements, "elements remain in list: %r" % elements -- cgit v1.2.1 From 315db703a63f5fe5fecf6417f78ff513ff091966 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sun, 4 Jan 2015 01:53:42 -0500 Subject: - start trying to move things into __slots__. This seems to reduce the size of the many per-column objects we're hitting, but somehow the overall memory is hardly being reduced at all in initial testing --- test/sql/test_operators.py | 16 +++++++++------- 1 file changed, 9 insertions(+), 7 deletions(-) (limited to 'test/sql') diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py index 3b8b20513..0985020d1 100644 --- a/test/sql/test_operators.py +++ b/test/sql/test_operators.py @@ -361,7 +361,7 @@ class CustomComparatorTest(_CustomComparatorTests, fixtures.TestBase): class comparator_factory(TypeEngine.Comparator): def __init__(self, expr): - self.expr = expr + super(MyInteger.comparator_factory, self).__init__(expr) def __add__(self, other): return self.expr.op("goofy")(other) @@ -382,7 +382,7 @@ class TypeDecoratorComparatorTest(_CustomComparatorTests, fixtures.TestBase): class comparator_factory(TypeDecorator.Comparator): def __init__(self, expr): - self.expr = expr + super(MyInteger.comparator_factory, self).__init__(expr) def __add__(self, other): return self.expr.op("goofy")(other) @@ -404,7 +404,7 @@ class TypeDecoratorTypeDecoratorComparatorTest( class comparator_factory(TypeDecorator.Comparator): def __init__(self, expr): - self.expr = expr + super(MyIntegerOne.comparator_factory, self).__init__(expr) def __add__(self, other): return self.expr.op("goofy")(other) @@ -429,7 +429,9 @@ class TypeDecoratorWVariantComparatorTest( class comparator_factory(TypeEngine.Comparator): def __init__(self, expr): - self.expr = expr + super( + SomeOtherInteger.comparator_factory, + self).__init__(expr) def __add__(self, other): return self.expr.op("not goofy")(other) @@ -443,7 +445,7 @@ class TypeDecoratorWVariantComparatorTest( class comparator_factory(TypeDecorator.Comparator): def __init__(self, expr): - self.expr = expr + super(MyInteger.comparator_factory, self).__init__(expr) def __add__(self, other): return self.expr.op("goofy")(other) @@ -464,7 +466,7 @@ class CustomEmbeddedinTypeDecoratorTest( class comparator_factory(TypeEngine.Comparator): def __init__(self, expr): - self.expr = expr + super(MyInteger.comparator_factory, self).__init__(expr) def __add__(self, other): return self.expr.op("goofy")(other) @@ -486,7 +488,7 @@ class NewOperatorTest(_CustomComparatorTests, fixtures.TestBase): class comparator_factory(TypeEngine.Comparator): def __init__(self, expr): - self.expr = expr + super(MyInteger.comparator_factory, self).__init__(expr) def foob(self, other): return self.expr.op("foob")(other) -- cgit v1.2.1 From 67778e20624bfb63990ff67598f911768867e439 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Tue, 6 Jan 2015 11:34:52 -0500 Subject: - add a close here --- test/sql/test_join_rewriting.py | 1 + 1 file changed, 1 insertion(+) (limited to 'test/sql') diff --git a/test/sql/test_join_rewriting.py b/test/sql/test_join_rewriting.py index ced65d7f1..f99dfda4e 100644 --- a/test/sql/test_join_rewriting.py +++ b/test/sql/test_join_rewriting.py @@ -650,6 +650,7 @@ class JoinExecTest(_JoinRewriteTestBase, fixtures.TestBase): def _test(self, selectable, assert_): result = testing.db.execute(selectable) + result.close() for col in selectable.inner_columns: assert col in result._metadata._keymap -- cgit v1.2.1 From f4b7b02e31e6b49195c21da7221bcbda0bad02b9 Mon Sep 17 00:00:00 2001 From: Dimitris Theodorou Date: Mon, 12 Jan 2015 02:40:50 +0100 Subject: Add native_enum flag to Enum's repr() result Needed for alembic autogenerate rendering. --- test/sql/test_types.py | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'test/sql') diff --git a/test/sql/test_types.py b/test/sql/test_types.py index 26dc6c842..0212499c4 100644 --- a/test/sql/test_types.py +++ b/test/sql/test_types.py @@ -1157,8 +1157,8 @@ class EnumTest(AssertsCompiledSQL, fixtures.TestBase): def test_repr(self): e = Enum( "x", "y", name="somename", convert_unicode=True, quote=True, - inherit_schema=True) - eq_(repr(e), "Enum('x', 'y', name='somename', inherit_schema=True)") + inherit_schema=True, native_enum=False) + eq_(repr(e), "Enum('x', 'y', name='somename', inherit_schema=True, native_enum=False)") binary_table = MyPickleType = metadata = None -- cgit v1.2.1 From dc55ff6f99098450f20aa702a55ece30b7e5fc7c Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Mon, 12 Jan 2015 13:27:34 -0500 Subject: repair formatting --- test/sql/test_types.py | 5 ++++- 1 file changed, 4 insertions(+), 1 deletion(-) (limited to 'test/sql') diff --git a/test/sql/test_types.py b/test/sql/test_types.py index 0212499c4..6ffd88d78 100644 --- a/test/sql/test_types.py +++ b/test/sql/test_types.py @@ -1158,7 +1158,10 @@ class EnumTest(AssertsCompiledSQL, fixtures.TestBase): e = Enum( "x", "y", name="somename", convert_unicode=True, quote=True, inherit_schema=True, native_enum=False) - eq_(repr(e), "Enum('x', 'y', name='somename', inherit_schema=True, native_enum=False)") + eq_( + repr(e), + "Enum('x', 'y', name='somename', " + "inherit_schema=True, native_enum=False)") binary_table = MyPickleType = metadata = None -- cgit v1.2.1 From 92cc232726a01dd3beff762ebccd326a9659e8b9 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Tue, 13 Jan 2015 14:33:33 -0500 Subject: - The multi-values version of :meth:`.Insert.values` has been repaired to work more usefully with tables that have Python- side default values and/or functions, as well as server-side defaults. The feature will now work with a dialect that uses "positional" parameters; a Python callable will also be invoked individually for each row just as is the case with an "executemany" style invocation; a server- side default column will no longer implicitly receive the value explicitly specified for the first row, instead refusing to invoke without an explicit value. fixes #3288 --- test/sql/test_defaults.py | 22 ++++++-- test/sql/test_insert.py | 136 ++++++++++++++++++++++++++++++++++++++-------- 2 files changed, 129 insertions(+), 29 deletions(-) (limited to 'test/sql') diff --git a/test/sql/test_defaults.py b/test/sql/test_defaults.py index 10e557b76..b7893d5f1 100644 --- a/test/sql/test_defaults.py +++ b/test/sql/test_defaults.py @@ -336,13 +336,7 @@ class DefaultTest(fixtures.TestBase): [(54, 'imthedefault', f, ts, ts, ctexec, True, False, 12, today, None, 'hi')]) - @testing.fails_on('firebird', 'Data type unknown') def test_insertmany(self): - # MySQL-Python 1.2.2 breaks functions in execute_many :( - if (testing.against('mysql+mysqldb') and - testing.db.dialect.dbapi.version_info[:3] == (1, 2, 2)): - return - t.insert().execute({}, {}, {}) ctexec = currenttime.scalar() @@ -356,6 +350,22 @@ class DefaultTest(fixtures.TestBase): (53, 'imthedefault', f, ts, ts, ctexec, True, False, 12, today, 'py', 'hi')]) + @testing.requires.multivalues_inserts + def test_insert_multivalues(self): + + t.insert().values([{}, {}, {}]).execute() + + ctexec = currenttime.scalar() + l = t.select().execute() + today = datetime.date.today() + eq_(l.fetchall(), + [(51, 'imthedefault', f, ts, ts, ctexec, True, False, + 12, today, 'py', 'hi'), + (52, 'imthedefault', f, ts, ts, ctexec, True, False, + 12, today, 'py', 'hi'), + (53, 'imthedefault', f, ts, ts, ctexec, True, False, + 12, today, 'py', 'hi')]) + def test_no_embed_in_sql(self): """Using a DefaultGenerator, Sequence, DefaultClause in the columns, where clause of a select, or in the values diff --git a/test/sql/test_insert.py b/test/sql/test_insert.py index bd4eaa3e2..8a41d4be7 100644 --- a/test/sql/test_insert.py +++ b/test/sql/test_insert.py @@ -1,12 +1,12 @@ #! coding:utf-8 from sqlalchemy import Column, Integer, MetaData, String, Table,\ - bindparam, exc, func, insert, select, column + bindparam, exc, func, insert, select, column, text from sqlalchemy.dialects import mysql, postgresql from sqlalchemy.engine import default from sqlalchemy.testing import AssertsCompiledSQL,\ assert_raises_message, fixtures - +from sqlalchemy.sql import crud class _InsertTestBase(object): @@ -19,6 +19,12 @@ class _InsertTestBase(object): Table('myothertable', metadata, Column('otherid', Integer, primary_key=True), Column('othername', String(30))) + Table('table_w_defaults', metadata, + Column('id', Integer, primary_key=True), + Column('x', Integer, default=10), + Column('y', Integer, server_default=text('5')), + Column('z', Integer, default=lambda: 10) + ) class InsertTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): @@ -565,6 +571,36 @@ class MultirowTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): checkpositional=checkpositional, dialect=dialect) + def test_positional_w_defaults(self): + table1 = self.tables.table_w_defaults + + values = [ + {'id': 1}, + {'id': 2}, + {'id': 3} + ] + + checkpositional = (1, None, None, 2, None, None, 3, None, None) + + dialect = default.DefaultDialect() + dialect.supports_multivalues_insert = True + dialect.paramstyle = 'format' + dialect.positional = True + + self.assert_compile( + table1.insert().values(values), + "INSERT INTO table_w_defaults (id, x, z) VALUES " + "(%s, %s, %s), (%s, %s, %s), (%s, %s, %s)", + checkpositional=checkpositional, + check_prefetch=[ + table1.c.x, table1.c.z, + crud._multiparam_column(table1.c.x, 0), + crud._multiparam_column(table1.c.z, 0), + crud._multiparam_column(table1.c.x, 1), + crud._multiparam_column(table1.c.z, 1) + ], + dialect=dialect) + def test_inline_default(self): metadata = MetaData() table = Table('sometable', metadata, @@ -597,6 +633,74 @@ class MultirowTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): checkparams=checkparams, dialect=postgresql.dialect()) + def test_python_scalar_default(self): + metadata = MetaData() + table = Table('sometable', metadata, + Column('id', Integer, primary_key=True), + Column('data', String), + Column('foo', Integer, default=10)) + + values = [ + {'id': 1, 'data': 'data1'}, + {'id': 2, 'data': 'data2', 'foo': 15}, + {'id': 3, 'data': 'data3'}, + ] + + checkparams = { + 'id_0': 1, + 'id_1': 2, + 'id_2': 3, + 'data_0': 'data1', + 'data_1': 'data2', + 'data_2': 'data3', + 'foo': None, # evaluated later + 'foo_1': 15, + 'foo_2': None # evaluated later + } + + self.assert_compile( + table.insert().values(values), + 'INSERT INTO sometable (id, data, foo) VALUES ' + '(%(id_0)s, %(data_0)s, %(foo)s), ' + '(%(id_1)s, %(data_1)s, %(foo_1)s), ' + '(%(id_2)s, %(data_2)s, %(foo_2)s)', + checkparams=checkparams, + dialect=postgresql.dialect()) + + def test_python_fn_default(self): + metadata = MetaData() + table = Table('sometable', metadata, + Column('id', Integer, primary_key=True), + Column('data', String), + Column('foo', Integer, default=lambda: 10)) + + values = [ + {'id': 1, 'data': 'data1'}, + {'id': 2, 'data': 'data2', 'foo': 15}, + {'id': 3, 'data': 'data3'}, + ] + + checkparams = { + 'id_0': 1, + 'id_1': 2, + 'id_2': 3, + 'data_0': 'data1', + 'data_1': 'data2', + 'data_2': 'data3', + 'foo': None, # evaluated later + 'foo_1': 15, + 'foo_2': None, # evaluated later + } + + self.assert_compile( + table.insert().values(values), + "INSERT INTO sometable (id, data, foo) VALUES " + "(%(id_0)s, %(data_0)s, %(foo)s), " + "(%(id_1)s, %(data_1)s, %(foo_1)s), " + "(%(id_2)s, %(data_2)s, %(foo_2)s)", + checkparams=checkparams, + dialect=postgresql.dialect()) + def test_sql_functions(self): metadata = MetaData() table = Table('sometable', metadata, @@ -684,24 +788,10 @@ class MultirowTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): {'id': 3, 'data': 'data3', 'foo': 'otherfoo'}, ] - checkparams = { - 'id_0': 1, - 'id_1': 2, - 'id_2': 3, - 'data_0': 'data1', - 'data_1': 'data2', - 'data_2': 'data3', - 'foo_0': 'plainfoo', - 'foo_2': 'otherfoo', - } - - # note the effect here is that the first set of params - # takes effect for the rest of them, when one is absent - self.assert_compile( - table.insert().values(values), - 'INSERT INTO sometable (id, data, foo) VALUES ' - '(%(id_0)s, %(data_0)s, %(foo_0)s), ' - '(%(id_1)s, %(data_1)s, %(foo_0)s), ' - '(%(id_2)s, %(data_2)s, %(foo_2)s)', - checkparams=checkparams, - dialect=postgresql.dialect()) + assert_raises_message( + exc.CompileError, + "INSERT value for column sometable.foo is explicitly rendered " + "as a boundparameter in the VALUES clause; a Python-side value or " + "SQL expression is required", + table.insert().values(values).compile + ) -- cgit v1.2.1 From f3a892a3ef666e299107a990bf4eae7ed9a953ae Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Fri, 16 Jan 2015 20:03:33 -0500 Subject: - Custom dialects that implement :class:`.GenericTypeCompiler` can now be constructed such that the visit methods receive an indication of the owning expression object, if any. Any visit method that accepts keyword arguments (e.g. ``**kw``) will in most cases receive a keyword argument ``type_expression``, referring to the expression object that the type is contained within. For columns in DDL, the dialect's compiler class may need to alter its ``get_column_specification()`` method to support this as well. The ``UserDefinedType.get_col_spec()`` method will also receive ``type_expression`` if it provides ``**kw`` in its argument signature. fixes #3074 --- test/sql/test_types.py | 63 ++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 63 insertions(+) (limited to 'test/sql') diff --git a/test/sql/test_types.py b/test/sql/test_types.py index 6ffd88d78..38b3ced13 100644 --- a/test/sql/test_types.py +++ b/test/sql/test_types.py @@ -10,6 +10,8 @@ from sqlalchemy import ( type_coerce, VARCHAR, Time, DateTime, BigInteger, SmallInteger, BOOLEAN, BLOB, NCHAR, NVARCHAR, CLOB, TIME, DATE, DATETIME, TIMESTAMP, SMALLINT, INTEGER, DECIMAL, NUMERIC, FLOAT, REAL) +from sqlalchemy.sql import ddl + from sqlalchemy import exc, types, util, dialects for name in dialects.__all__: __import__("sqlalchemy.dialects.%s" % name) @@ -309,6 +311,24 @@ class UserDefinedTest(fixtures.TablesTest, AssertsCompiledSQL): literal_binds=True ) + def test_kw_colspec(self): + class MyType(types.UserDefinedType): + def get_col_spec(self, **kw): + return "FOOB %s" % kw['type_expression'].name + + class MyOtherType(types.UserDefinedType): + def get_col_spec(self): + return "BAR" + + self.assert_compile( + ddl.CreateColumn(Column('bar', MyType)), + "bar FOOB bar" + ) + self.assert_compile( + ddl.CreateColumn(Column('bar', MyOtherType)), + "bar BAR" + ) + def test_typedecorator_literal_render_fallback_bound(self): # fall back to process_bind_param for literal # value rendering. @@ -1642,6 +1662,49 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): def test_decimal_scale(self): self.assert_compile(types.DECIMAL(2, 4), 'DECIMAL(2, 4)') + def test_kwarg_legacy_typecompiler(self): + from sqlalchemy.sql import compiler + + class SomeTypeCompiler(compiler.GenericTypeCompiler): + # transparently decorated w/ kw decorator + def visit_VARCHAR(self, type_): + return "MYVARCHAR" + + # not affected + def visit_INTEGER(self, type_, **kw): + return "MYINTEGER %s" % kw['type_expression'].name + + dialect = default.DefaultDialect() + dialect.type_compiler = SomeTypeCompiler(dialect) + self.assert_compile( + ddl.CreateColumn(Column('bar', VARCHAR(50))), + "bar MYVARCHAR", + dialect=dialect + ) + self.assert_compile( + ddl.CreateColumn(Column('bar', INTEGER)), + "bar MYINTEGER bar", + dialect=dialect + ) + + +class TestKWArgPassThru(AssertsCompiledSQL, fixtures.TestBase): + __backend__ = True + + def test_user_defined(self): + """test that dialects pass the column through on DDL.""" + + class MyType(types.UserDefinedType): + def get_col_spec(self, **kw): + return "FOOB %s" % kw['type_expression'].name + + m = MetaData() + t = Table('t', m, Column('bar', MyType)) + self.assert_compile( + ddl.CreateColumn(t.c.bar), + "bar FOOB bar" + ) + class NumericRawSQLTest(fixtures.TestBase): -- cgit v1.2.1 From f5d4f2685f30817af493c32d2cf0ac77715bdb46 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sun, 18 Jan 2015 20:57:26 -0500 Subject: - rework assertsql system, fixes #3293 --- test/sql/test_constraints.py | 12 ++++++------ 1 file changed, 6 insertions(+), 6 deletions(-) (limited to 'test/sql') diff --git a/test/sql/test_constraints.py b/test/sql/test_constraints.py index 604b5efeb..2603f67a3 100644 --- a/test/sql/test_constraints.py +++ b/test/sql/test_constraints.py @@ -9,7 +9,7 @@ from sqlalchemy import testing from sqlalchemy.engine import default from sqlalchemy.testing import engines from sqlalchemy.testing import eq_ -from sqlalchemy.testing.assertsql import AllOf, RegexSQL, ExactSQL, CompiledSQL +from sqlalchemy.testing.assertsql import AllOf, RegexSQL, CompiledSQL from sqlalchemy.sql import table, column @@ -417,13 +417,13 @@ class ConstraintGenTest(fixtures.TestBase, AssertsExecutionResults): lambda: events.create(testing.db), RegexSQL("^CREATE TABLE events"), AllOf( - ExactSQL('CREATE UNIQUE INDEX ix_events_name ON events ' + CompiledSQL('CREATE UNIQUE INDEX ix_events_name ON events ' '(name)'), - ExactSQL('CREATE INDEX ix_events_location ON events ' + CompiledSQL('CREATE INDEX ix_events_location ON events ' '(location)'), - ExactSQL('CREATE UNIQUE INDEX sport_announcer ON events ' + CompiledSQL('CREATE UNIQUE INDEX sport_announcer ON events ' '(sport, announcer)'), - ExactSQL('CREATE INDEX idx_winners ON events (winner)') + CompiledSQL('CREATE INDEX idx_winners ON events (winner)'), ) ) @@ -441,7 +441,7 @@ class ConstraintGenTest(fixtures.TestBase, AssertsExecutionResults): lambda: t.create(testing.db), CompiledSQL('CREATE TABLE sometable (id INTEGER NOT NULL, ' 'data VARCHAR(50), PRIMARY KEY (id))'), - ExactSQL('CREATE INDEX myindex ON sometable (data DESC)') + CompiledSQL('CREATE INDEX myindex ON sometable (data DESC)') ) -- cgit v1.2.1 From f94d75ede5f5d2ed28d72ff98ca7caca016e5506 Mon Sep 17 00:00:00 2001 From: Shaun Stanworth Date: Sun, 9 Nov 2014 14:52:31 +0000 Subject: Added psycopg2cffi dialect --- test/sql/test_types.py | 1 + 1 file changed, 1 insertion(+) (limited to 'test/sql') diff --git a/test/sql/test_types.py b/test/sql/test_types.py index 38b3ced13..5e1542853 100644 --- a/test/sql/test_types.py +++ b/test/sql/test_types.py @@ -952,6 +952,7 @@ class UnicodeTest(fixtures.TestBase): expected = (testing.db.name, testing.db.driver) in \ ( ('postgresql', 'psycopg2'), + ('postgresql', 'psycopg2cffi'), ('postgresql', 'pypostgresql'), ('postgresql', 'pg8000'), ('postgresql', 'zxjdbc'), -- cgit v1.2.1 From 62f87749067684696dca32cacac17f7d33066d8b Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Mon, 26 Jan 2015 22:45:12 -0500 Subject: - fix this test for py3k --- test/sql/test_defaults.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'test/sql') diff --git a/test/sql/test_defaults.py b/test/sql/test_defaults.py index ce11a2603..48505dd8c 100644 --- a/test/sql/test_defaults.py +++ b/test/sql/test_defaults.py @@ -379,7 +379,7 @@ class DefaultTest(fixtures.TestBase): assert_raises_message( sa.exc.ArgumentError, "SQL expression object or string expected, got object of type " - " instead", + "<.* 'list'> instead", t.select, [const] ) assert_raises_message( -- cgit v1.2.1