From a23264e1dc43b1250b9b5de541ff27bd49a2b2c1 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Ilja=20Everil=C3=A4?= Date: Wed, 10 Sep 2014 11:33:49 +0300 Subject: tests for FILTER (WHERE ...) --- test/sql/test_compiler.py | 64 +++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 64 insertions(+) (limited to 'test/sql') diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index d47b58f1f..6e730ad50 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -2190,6 +2190,70 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): "(ORDER BY mytable.myid + :myid_1) AS anon_1 FROM mytable" ) + def test_aggregate_filter(self): + self.assert_compile( + func.count(1).filter(), + "count(:param_1)" + ) + self.assert_compile( + func.count(1).filter( + table1.c.name != None + ), + "count(:param_1) FILTER (WHERE mytable.name IS NOT NULL)" + ) + self.assert_compile( + func.count(1).filter( + table1.c.name == None, + table1.c.myid > 0 + ), + "count(:param_1) FILTER (WHERE mytable.name IS NULL AND " + "mytable.myid > :myid_1)" + ) + + self.assert_compile( + select([func.count(1).filter( + table1.c.description != None + ).label('foo')]), + "SELECT count(:param_1) FILTER (WHERE mytable.description " + "IS NOT NULL) AS foo FROM mytable" + ) + + # test from_obj generation. + # from func: + self.assert_compile( + select([ + func.max(table1.c.name).filter( + literal_column('description') != None + ) + ]), + "SELECT max(mytable.name) FILTER (WHERE description " + "IS NOT NULL) AS anon_1 FROM mytable" + ) + # from criterion: + self.assert_compile( + select([ + func.count(1).filter( + table1.c.name == 'name' + ) + ]), + "SELECT count(:param_1) FILTER (WHERE mytable.name = :name_1) " + "AS anon_1 FROM mytable" + ) + + # test chaining: + self.assert_compile( + select([ + func.count(1).filter( + table1.c.name == 'name' + ).filter( + table1.c.description == 'description' + ) + ]), + "SELECT count(:param_1) FILTER (WHERE " + "mytable.name = :name_1 AND mytable.description = :description_1) " + "AS anon_1 FROM mytable" + ) + def test_date_between(self): import datetime table = Table('dt', metadata, -- cgit v1.2.1 From a3cd517c95e5f341c711c41474de14db97bb7778 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Ilja=20Everil=C3=A4?= Date: Wed, 10 Sep 2014 12:07:38 +0300 Subject: add ClauseTest for aggregatefilter --- test/sql/test_generative.py | 5 +++++ 1 file changed, 5 insertions(+) (limited to 'test/sql') diff --git a/test/sql/test_generative.py b/test/sql/test_generative.py index 013ba8082..1b67ab68c 100644 --- a/test/sql/test_generative.py +++ b/test/sql/test_generative.py @@ -539,6 +539,11 @@ class ClauseTest(fixtures.TestBase, AssertsCompiledSQL): expr2 = CloningVisitor().traverse(expr) assert str(expr) == str(expr2) + def test_aggregatefilter(self): + expr = func.count(1).filter(t1.c.col1 > 1) + expr2 = CloningVisitor().traverse(expr) + assert str(expr) == str(expr2) + def test_adapt_union(self): u = union( t1.select().where(t1.c.col1 == 4), -- cgit v1.2.1 From ab1c25266dd49f087b5fff316b6ba6fb610b1d35 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Ilja=20Everil=C3=A4?= Date: Thu, 11 Sep 2014 15:29:33 +0300 Subject: renamed aggregatefilter to funcfilter, since it is that --- test/sql/test_compiler.py | 2 +- test/sql/test_generative.py | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) (limited to 'test/sql') diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index 6e730ad50..7bba29563 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -2190,7 +2190,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): "(ORDER BY mytable.myid + :myid_1) AS anon_1 FROM mytable" ) - def test_aggregate_filter(self): + def test_funcfilter(self): self.assert_compile( func.count(1).filter(), "count(:param_1)" diff --git a/test/sql/test_generative.py b/test/sql/test_generative.py index 1b67ab68c..6044cecb0 100644 --- a/test/sql/test_generative.py +++ b/test/sql/test_generative.py @@ -539,7 +539,7 @@ class ClauseTest(fixtures.TestBase, AssertsCompiledSQL): expr2 = CloningVisitor().traverse(expr) assert str(expr) == str(expr2) - def test_aggregatefilter(self): + def test_funcfilter(self): expr = func.count(1).filter(t1.c.col1 > 1) expr2 = CloningVisitor().traverse(expr) assert str(expr) == str(expr2) -- cgit v1.2.1 From 52a095ba6675f5f5807a1dc655b4ae32b9999f27 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Ilja=20Everil=C3=A4?= Date: Thu, 11 Sep 2014 15:39:56 +0300 Subject: allow windowing filtered functions --- test/sql/test_compiler.py | 27 +++++++++++++++++++++++++++ 1 file changed, 27 insertions(+) (limited to 'test/sql') diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index 7bba29563..fc33db184 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -2254,6 +2254,33 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): "AS anon_1 FROM mytable" ) + # test filtered windowing: + self.assert_compile( + select([ + func.rank().filter( + table1.c.name > 'foo' + ).over( + order_by=table1.c.name + ) + ]), + "SELECT rank() FILTER (WHERE mytable.name > :name_1) " + "OVER (ORDER BY mytable.name) AS anon_1 FROM mytable" + ) + + self.assert_compile( + select([ + func.rank().filter( + table1.c.name > 'foo' + ).over( + order_by=table1.c.name, + partition_by=['description'] + ) + ]), + "SELECT rank() FILTER (WHERE mytable.name > :name_1) " + "OVER (PARTITION BY mytable.description ORDER BY mytable.name) " + "AS anon_1 FROM mytable" + ) + def test_date_between(self): import datetime table = Table('dt', metadata, -- cgit v1.2.1 From f82f6d55dc05daf2ba0881ded98f5715b70ae3e3 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 18 Sep 2014 11:44:48 -0400 Subject: - Added new method :meth:`.Select.with_statement_hint` and ORM method :meth:`.Query.with_statement_hint` to support statement-level hints that are not specific to a table. fixes #3206 --- test/sql/test_compiler.py | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) (limited to 'test/sql') diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index d47b58f1f..3e6b87351 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -2420,6 +2420,23 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): dialect=dialect ) + def test_statement_hints(self): + + stmt = select([table1.c.myid]).\ + with_statement_hint("test hint one").\ + with_statement_hint("test hint two", 'mysql') + + self.assert_compile( + stmt, + "SELECT mytable.myid FROM mytable test hint one", + ) + + self.assert_compile( + stmt, + "SELECT mytable.myid FROM mytable test hint one test hint two", + dialect='mysql' + ) + def test_literal_as_text_fromstring(self): self.assert_compile( and_(text("a"), text("b")), -- cgit v1.2.1 From 19ec6c33681ee4d7793b7c6a9301425cd34abb0b Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 25 Sep 2014 21:16:13 -0400 Subject: - "column already assigned" message has been updated in 7f82c55f --- test/sql/test_metadata.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'test/sql') diff --git a/test/sql/test_metadata.py b/test/sql/test_metadata.py index 304e2cc5f..6b8e1bb40 100644 --- a/test/sql/test_metadata.py +++ b/test/sql/test_metadata.py @@ -2124,7 +2124,7 @@ class ColumnDefinitionTest(AssertsCompiledSQL, fixtures.TestBase): assert_raises_message( exc.ArgumentError, - "Column object already assigned to Table 't'", + "Column object 'x' already assigned to Table 't'", Table, 'q', MetaData(), c) def test_incomplete_key(self): -- cgit v1.2.1 From 7e0835409cf5ab7f7ac27dc1f619773d84bfe53d Mon Sep 17 00:00:00 2001 From: ndparker Date: Sat, 4 Oct 2014 00:51:01 +0200 Subject: add simple tests for new name argument for Table.tometadata() --- test/sql/test_metadata.py | 44 ++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 44 insertions(+) (limited to 'test/sql') diff --git a/test/sql/test_metadata.py b/test/sql/test_metadata.py index 304e2cc5f..0e845d555 100644 --- a/test/sql/test_metadata.py +++ b/test/sql/test_metadata.py @@ -678,6 +678,50 @@ class ToMetaDataTest(fixtures.TestBase, ComparesTables): eq_(str(table_c.join(table2_c).onclause), 'myschema.mytable.myid = myschema.othertable.myid') + def test_change_name_retain_metadata(self): + meta = MetaData() + + table = Table('mytable', meta, + Column('myid', Integer, primary_key=True), + Column('name', String(40), nullable=True), + Column('description', String(30), + CheckConstraint("description='hi'")), + UniqueConstraint('name'), + schema='myschema', + ) + + table2 = table.tometadata(table.metadata, name='newtable') + table3 = table.tometadata(table.metadata, schema='newschema', + name='newtable') + + assert table.metadata is table2.metadata + assert table.metadata is table3.metadata + eq_((table.name, table2.name, table3.name), + ('mytable', 'newtable', 'newtable')) + eq_((table.key, table2.key, table3.key), + ('myschema.mytable', 'myschema.newtable', 'newschema.newtable')) + + def test_change_name_change_metadata(self): + meta = MetaData() + meta2 = MetaData() + + table = Table('mytable', meta, + Column('myid', Integer, primary_key=True), + Column('name', String(40), nullable=True), + Column('description', String(30), + CheckConstraint("description='hi'")), + UniqueConstraint('name'), + schema='myschema', + ) + + table2 = table.tometadata(meta2, name='newtable') + + assert table.metadata is not table2.metadata + eq_((table.name, table2.name), + ('mytable', 'newtable')) + eq_((table.key, table2.key), + ('myschema.mytable', 'myschema.newtable')) + def _assert_fk(self, t2, schema, expected, referred_schema_fn=None): m2 = MetaData() existing_schema = t2.schema -- cgit v1.2.1 From 49e750a1d788710b89764c4dd9c0ddbf9b1f38ad Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sat, 4 Oct 2014 12:18:20 -0400 Subject: - changelog, migration for pr github:134 --- test/sql/test_compiler.py | 91 ------------------------------------ test/sql/test_functions.py | 112 ++++++++++++++++++++++++++++++++++++++++++++- 2 files changed, 111 insertions(+), 92 deletions(-) (limited to 'test/sql') diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index ed13e8455..3e6b87351 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -2190,97 +2190,6 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): "(ORDER BY mytable.myid + :myid_1) AS anon_1 FROM mytable" ) - def test_funcfilter(self): - self.assert_compile( - func.count(1).filter(), - "count(:param_1)" - ) - self.assert_compile( - func.count(1).filter( - table1.c.name != None - ), - "count(:param_1) FILTER (WHERE mytable.name IS NOT NULL)" - ) - self.assert_compile( - func.count(1).filter( - table1.c.name == None, - table1.c.myid > 0 - ), - "count(:param_1) FILTER (WHERE mytable.name IS NULL AND " - "mytable.myid > :myid_1)" - ) - - self.assert_compile( - select([func.count(1).filter( - table1.c.description != None - ).label('foo')]), - "SELECT count(:param_1) FILTER (WHERE mytable.description " - "IS NOT NULL) AS foo FROM mytable" - ) - - # test from_obj generation. - # from func: - self.assert_compile( - select([ - func.max(table1.c.name).filter( - literal_column('description') != None - ) - ]), - "SELECT max(mytable.name) FILTER (WHERE description " - "IS NOT NULL) AS anon_1 FROM mytable" - ) - # from criterion: - self.assert_compile( - select([ - func.count(1).filter( - table1.c.name == 'name' - ) - ]), - "SELECT count(:param_1) FILTER (WHERE mytable.name = :name_1) " - "AS anon_1 FROM mytable" - ) - - # test chaining: - self.assert_compile( - select([ - func.count(1).filter( - table1.c.name == 'name' - ).filter( - table1.c.description == 'description' - ) - ]), - "SELECT count(:param_1) FILTER (WHERE " - "mytable.name = :name_1 AND mytable.description = :description_1) " - "AS anon_1 FROM mytable" - ) - - # test filtered windowing: - self.assert_compile( - select([ - func.rank().filter( - table1.c.name > 'foo' - ).over( - order_by=table1.c.name - ) - ]), - "SELECT rank() FILTER (WHERE mytable.name > :name_1) " - "OVER (ORDER BY mytable.name) AS anon_1 FROM mytable" - ) - - self.assert_compile( - select([ - func.rank().filter( - table1.c.name > 'foo' - ).over( - order_by=table1.c.name, - partition_by=['description'] - ) - ]), - "SELECT rank() FILTER (WHERE mytable.name > :name_1) " - "OVER (PARTITION BY mytable.description ORDER BY mytable.name) " - "AS anon_1 FROM mytable" - ) - def test_date_between(self): import datetime table = Table('dt', metadata, diff --git a/test/sql/test_functions.py b/test/sql/test_functions.py index 9b7649e63..ec8d9b5c0 100644 --- a/test/sql/test_functions.py +++ b/test/sql/test_functions.py @@ -1,7 +1,8 @@ from sqlalchemy.testing import eq_ import datetime from sqlalchemy import func, select, Integer, literal, DateTime, Table, \ - Column, Sequence, MetaData, extract, Date, String, bindparam + Column, Sequence, MetaData, extract, Date, String, bindparam, \ + literal_column from sqlalchemy.sql import table, column from sqlalchemy import sql, util from sqlalchemy.sql.compiler import BIND_TEMPLATES @@ -15,6 +16,13 @@ from sqlalchemy.testing import fixtures, AssertsCompiledSQL, engines from sqlalchemy.dialects import sqlite, postgresql, mysql, oracle +table1 = table('mytable', + column('myid', Integer), + column('name', String), + column('description', String), + ) + + class CompileTest(fixtures.TestBase, AssertsCompiledSQL): __dialect__ = 'default' @@ -367,6 +375,108 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): expr = func.rows("foo").alias('bar') assert len(expr.c) + def test_funcfilter_empty(self): + self.assert_compile( + func.count(1).filter(), + "count(:param_1)" + ) + + def test_funcfilter_criterion(self): + self.assert_compile( + func.count(1).filter( + table1.c.name != None + ), + "count(:param_1) FILTER (WHERE mytable.name IS NOT NULL)" + ) + + def test_funcfilter_compound_criterion(self): + self.assert_compile( + func.count(1).filter( + table1.c.name == None, + table1.c.myid > 0 + ), + "count(:param_1) FILTER (WHERE mytable.name IS NULL AND " + "mytable.myid > :myid_1)" + ) + + def test_funcfilter_label(self): + self.assert_compile( + select([func.count(1).filter( + table1.c.description != None + ).label('foo')]), + "SELECT count(:param_1) FILTER (WHERE mytable.description " + "IS NOT NULL) AS foo FROM mytable" + ) + + def test_funcfilter_fromobj_fromfunc(self): + # test from_obj generation. + # from func: + self.assert_compile( + select([ + func.max(table1.c.name).filter( + literal_column('description') != None + ) + ]), + "SELECT max(mytable.name) FILTER (WHERE description " + "IS NOT NULL) AS anon_1 FROM mytable" + ) + + def test_funcfilter_fromobj_fromcriterion(self): + # from criterion: + self.assert_compile( + select([ + func.count(1).filter( + table1.c.name == 'name' + ) + ]), + "SELECT count(:param_1) FILTER (WHERE mytable.name = :name_1) " + "AS anon_1 FROM mytable" + ) + + def test_funcfilter_chaining(self): + # test chaining: + self.assert_compile( + select([ + func.count(1).filter( + table1.c.name == 'name' + ).filter( + table1.c.description == 'description' + ) + ]), + "SELECT count(:param_1) FILTER (WHERE " + "mytable.name = :name_1 AND mytable.description = :description_1) " + "AS anon_1 FROM mytable" + ) + + def test_funcfilter_windowing_orderby(self): + # test filtered windowing: + self.assert_compile( + select([ + func.rank().filter( + table1.c.name > 'foo' + ).over( + order_by=table1.c.name + ) + ]), + "SELECT rank() FILTER (WHERE mytable.name > :name_1) " + "OVER (ORDER BY mytable.name) AS anon_1 FROM mytable" + ) + + def test_funcfilter_windowing_orderby_partitionby(self): + self.assert_compile( + select([ + func.rank().filter( + table1.c.name > 'foo' + ).over( + order_by=table1.c.name, + partition_by=['description'] + ) + ]), + "SELECT rank() FILTER (WHERE mytable.name > :name_1) " + "OVER (PARTITION BY mytable.description ORDER BY mytable.name) " + "AS anon_1 FROM mytable" + ) + class ExecuteTest(fixtures.TestBase): -- cgit v1.2.1 From 81d1e0455a406560be468d1aacc37aa63bb4d717 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 9 Oct 2014 17:20:30 -0400 Subject: - Fixed bug where a fair number of SQL elements within the sql package would fail to ``__repr__()`` successfully, due to a missing ``description`` attribute that would then invoke a recursion overflow when an internal AttributeError would then re-invoke ``__repr__()``. fixes #3195 --- test/sql/test_selectable.py | 24 ++++++++++++++++++++++++ 1 file changed, 24 insertions(+) (limited to 'test/sql') diff --git a/test/sql/test_selectable.py b/test/sql/test_selectable.py index a3b2b0e93..99d0cbe76 100644 --- a/test/sql/test_selectable.py +++ b/test/sql/test_selectable.py @@ -5,6 +5,7 @@ from sqlalchemy.testing import eq_, assert_raises, \ from sqlalchemy import * from sqlalchemy.testing import fixtures, AssertsCompiledSQL, \ AssertsExecutionResults +from sqlalchemy.sql import elements from sqlalchemy import testing from sqlalchemy.sql import util as sql_util, visitors, expression from sqlalchemy import exc @@ -1934,6 +1935,29 @@ class AnnotationsTest(fixtures.TestBase): assert (c2 == 5).left._annotations == {"foo": "bar", "bat": "hoho"} +class ReprTest(fixtures.TestBase): + def test_ensure_repr_elements(self): + for obj in [ + elements.Cast(1, 2), + elements.TypeClause(String()), + elements.ColumnClause('x'), + elements.BindParameter('q'), + elements.Null(), + elements.True_(), + elements.False_(), + elements.ClauseList(), + elements.BooleanClauseList.and_(), + elements.Tuple(), + elements.Case([]), + elements.Extract('foo', column('x')), + elements.UnaryExpression(column('x')), + elements.Grouping(column('x')), + elements.Over(func.foo()), + elements.Label('q', column('x')), + ]: + repr(obj) + + class WithLabelsTest(fixtures.TestBase): def _assert_labels_warning(self, s): -- cgit v1.2.1 From 95be42c06ff4e5f3528de42bb04dcba228ea74c2 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Fri, 10 Oct 2014 17:15:19 -0400 Subject: - :meth:`.Insert.from_select` now includes Python and SQL-expression defaults if otherwise unspecified; the limitation where non- server column defaults aren't included in an INSERT FROM SELECT is now lifted and these expressions are rendered as constants into the SELECT statement. --- test/sql/test_defaults.py | 65 +++++++++++++++++++++++++++++++++++++ test/sql/test_insert.py | 82 +++++++++++++++++++++++++++++++++++++++++++++-- 2 files changed, 145 insertions(+), 2 deletions(-) (limited to 'test/sql') diff --git a/test/sql/test_defaults.py b/test/sql/test_defaults.py index abce600df..10e557b76 100644 --- a/test/sql/test_defaults.py +++ b/test/sql/test_defaults.py @@ -14,6 +14,7 @@ from sqlalchemy.dialects import sqlite from sqlalchemy.testing import fixtures from sqlalchemy.util import u, b from sqlalchemy import util +import itertools t = f = f2 = ts = currenttime = metadata = default_generator = None @@ -1278,3 +1279,67 @@ class UnicodeDefaultsTest(fixtures.TestBase): "foobar", Unicode(32), default=default ) + + +class InsertFromSelectTest(fixtures.TestBase): + __backend__ = True + + def _fixture(self): + data = Table( + 'data', self.metadata, + Column('x', Integer), + Column('y', Integer) + ) + data.create() + testing.db.execute(data.insert(), {'x': 2, 'y': 5}, {'x': 7, 'y': 12}) + return data + + @testing.provide_metadata + def test_insert_from_select_override_defaults(self): + data = self._fixture() + + table = Table('sometable', self.metadata, + Column('x', Integer), + Column('foo', Integer, default=12), + Column('y', Integer)) + + table.create() + + sel = select([data.c.x, data.c.y]) + + ins = table.insert().\ + from_select(["x", "y"], sel) + testing.db.execute(ins) + + eq_( + testing.db.execute(table.select().order_by(table.c.x)).fetchall(), + [(2, 12, 5), (7, 12, 12)] + ) + + @testing.provide_metadata + def test_insert_from_select_fn_defaults(self): + data = self._fixture() + + counter = itertools.count(1) + + def foo(ctx): + return next(counter) + + table = Table('sometable', self.metadata, + Column('x', Integer), + Column('foo', Integer, default=foo), + Column('y', Integer)) + + table.create() + + sel = select([data.c.x, data.c.y]) + + ins = table.insert().\ + from_select(["x", "y"], sel) + testing.db.execute(ins) + + # counter is only called once! + eq_( + testing.db.execute(table.select().order_by(table.c.x)).fetchall(), + [(2, 1, 5), (7, 1, 12)] + ) diff --git a/test/sql/test_insert.py b/test/sql/test_insert.py index 232c5758b..bd4eaa3e2 100644 --- a/test/sql/test_insert.py +++ b/test/sql/test_insert.py @@ -183,7 +183,7 @@ class InsertTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): checkparams={"name_1": "foo"} ) - def test_insert_from_select_select_no_defaults(self): + def test_insert_from_select_no_defaults(self): metadata = MetaData() table = Table('sometable', metadata, Column('id', Integer, primary_key=True), @@ -191,7 +191,7 @@ class InsertTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): table1 = self.tables.mytable sel = select([table1.c.myid]).where(table1.c.name == 'foo') ins = table.insert().\ - from_select(["id"], sel) + from_select(["id"], sel, include_defaults=False) self.assert_compile( ins, "INSERT INTO sometable (id) SELECT mytable.myid " @@ -199,6 +199,84 @@ class InsertTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): checkparams={"name_1": "foo"} ) + def test_insert_from_select_with_sql_defaults(self): + metadata = MetaData() + table = Table('sometable', metadata, + Column('id', Integer, primary_key=True), + Column('foo', Integer, default=func.foobar())) + table1 = self.tables.mytable + sel = select([table1.c.myid]).where(table1.c.name == 'foo') + ins = table.insert().\ + from_select(["id"], sel) + self.assert_compile( + ins, + "INSERT INTO sometable (id, foo) SELECT " + "mytable.myid, foobar() AS foobar_1 " + "FROM mytable WHERE mytable.name = :name_1", + checkparams={"name_1": "foo"} + ) + + def test_insert_from_select_with_python_defaults(self): + metadata = MetaData() + table = Table('sometable', metadata, + Column('id', Integer, primary_key=True), + Column('foo', Integer, default=12)) + table1 = self.tables.mytable + sel = select([table1.c.myid]).where(table1.c.name == 'foo') + ins = table.insert().\ + from_select(["id"], sel) + self.assert_compile( + ins, + "INSERT INTO sometable (id, foo) SELECT " + "mytable.myid, :foo AS anon_1 " + "FROM mytable WHERE mytable.name = :name_1", + # value filled in at execution time + checkparams={"name_1": "foo", "foo": None} + ) + + def test_insert_from_select_override_defaults(self): + metadata = MetaData() + table = Table('sometable', metadata, + Column('id', Integer, primary_key=True), + Column('foo', Integer, default=12)) + table1 = self.tables.mytable + sel = select( + [table1.c.myid, table1.c.myid.label('q')]).where( + table1.c.name == 'foo') + ins = table.insert().\ + from_select(["id", "foo"], sel) + self.assert_compile( + ins, + "INSERT INTO sometable (id, foo) SELECT " + "mytable.myid, mytable.myid AS q " + "FROM mytable WHERE mytable.name = :name_1", + checkparams={"name_1": "foo"} + ) + + def test_insert_from_select_fn_defaults(self): + metadata = MetaData() + + def foo(ctx): + return 12 + + table = Table('sometable', metadata, + Column('id', Integer, primary_key=True), + Column('foo', Integer, default=foo)) + table1 = self.tables.mytable + sel = select( + [table1.c.myid]).where( + table1.c.name == 'foo') + ins = table.insert().\ + from_select(["id"], sel) + self.assert_compile( + ins, + "INSERT INTO sometable (id, foo) SELECT " + "mytable.myid, :foo AS anon_1 " + "FROM mytable WHERE mytable.name = :name_1", + # value filled in at execution time + checkparams={"name_1": "foo", "foo": None} + ) + def test_insert_mix_select_values_exception(self): table1 = self.tables.mytable sel = select([table1.c.myid, table1.c.name]).where( -- cgit v1.2.1 From 50d2432a9efa65c9798ef207e3f887cb5c0071e1 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sun, 12 Oct 2014 20:14:32 -0400 Subject: - Mysqlconnector as of version 2.0, probably as a side effect of the python 3 merge, now does not expect percent signs (e.g. as used as the modulus operator and others) to be doubled, even when using the "pyformat" bound parameter format (this change is not documented by Mysqlconnector). The dialect now checks for py2k and for mysqlconnector less than version 2.0 when detecting if the modulus operator should be rendered as ``%%`` or ``%``. - Unicode SQL is now passed for MySQLconnector version 2.0 and above; for Py2k and MySQL < 2.0, strings are encoded. Note that mysqlconnector as of 2.0.1 appears to have a bug with unicode DDL on py2k, so the tests here are skipping until we observe it's fixed. - take out profiling on mysqlconnector, callcounts vary too much with its current development speed --- test/sql/test_query.py | 6 ------ 1 file changed, 6 deletions(-) (limited to 'test/sql') diff --git a/test/sql/test_query.py b/test/sql/test_query.py index 430c3fe7c..0ae1b0b34 100644 --- a/test/sql/test_query.py +++ b/test/sql/test_query.py @@ -570,9 +570,6 @@ class QueryTest(fixtures.TestBase): ): eq_(expr.execute().fetchall(), result) - @testing.fails_if( - lambda: util.py3k and testing.against('mysql+mysqlconnector'), - "bug in mysqlconnector") @testing.requires.mod_operator_as_percent_sign @testing.emits_warning('.*now automatically escapes.*') def test_percents_in_text(self): @@ -2510,9 +2507,6 @@ class OperatorTest(fixtures.TestBase): metadata.drop_all() # TODO: seems like more tests warranted for this setup. - @testing.fails_if( - lambda: util.py3k and testing.against('mysql+mysqlconnector'), - "bug in mysqlconnector") def test_modulo(self): eq_( select([flds.c.intcol % 3], -- cgit v1.2.1 From 198917237e6051a02e6fd681de9200a3ab737fa8 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Mon, 13 Oct 2014 12:37:29 -0400 Subject: - additional issues fixed in mysqlconnector 2.0.1 --- test/sql/test_query.py | 15 --------------- 1 file changed, 15 deletions(-) (limited to 'test/sql') diff --git a/test/sql/test_query.py b/test/sql/test_query.py index 0ae1b0b34..fc040dfed 100644 --- a/test/sql/test_query.py +++ b/test/sql/test_query.py @@ -295,9 +295,6 @@ class QueryTest(fixtures.TestBase): l.append(row) self.assert_(len(l) == 3) - @testing.fails_if( - lambda: util.py3k and testing.against('mysql+mysqlconnector'), - "bug in mysqlconnector") @testing.requires.subqueries def test_anonymous_rows(self): users.insert().execute( @@ -509,9 +506,6 @@ class QueryTest(fixtures.TestBase): lambda: row[accessor] ) - @testing.fails_if( - lambda: util.py3k and testing.against('mysql+mysqlconnector'), - "bug in mysqlconnector") @testing.requires.boolean_col_expressions def test_or_and_as_columns(self): true, false = literal(True), literal(False) @@ -620,9 +614,6 @@ class QueryTest(fixtures.TestBase): c = testing.db.connect() assert c.execute(s, id=7).fetchall()[0]['user_id'] == 7 - @testing.fails_if( - lambda: util.py3k and testing.against('mysql+mysqlconnector'), - "bug in mysqlconnector") def test_repeated_bindparams(self): """Tests that a BindParam can be used more than once. @@ -1316,9 +1307,6 @@ class QueryTest(fixtures.TestBase): # Null values are not outside any set assert len(r) == 0 - @testing.fails_if( - lambda: util.py3k and testing.against('mysql+mysqlconnector'), - "bug in mysqlconnector") @testing.emits_warning('.*empty sequence.*') @testing.fails_on('firebird', "uses sql-92 rules") @testing.fails_on('sybase', "uses sql-92 rules") @@ -1345,9 +1333,6 @@ class QueryTest(fixtures.TestBase): r = s.execute(search_key=None).fetchall() assert len(r) == 0 - @testing.fails_if( - lambda: util.py3k and testing.against('mysql+mysqlconnector'), - "bug in mysqlconnector") @testing.emits_warning('.*empty sequence.*') def test_literal_in(self): """similar to test_bind_in but use a bind with a value.""" -- cgit v1.2.1 From 6f40eb37cbdcdae032d73c3537df1e01d2e9e67c Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Fri, 17 Oct 2014 19:37:45 -0400 Subject: - Exception messages have been spiffed up a bit. The SQL statement and parameters are not displayed if None, reducing confusion for error messages that weren't related to a statement. The full module and classname for the DBAPI-level exception is displayed, making it clear that this is a wrapped DBAPI exception. The statement and parameters themselves are bounded within a bracketed sections to better isolate them from the error message and from each other. fixes #3172 --- test/sql/test_query.py | 7 +++---- 1 file changed, 3 insertions(+), 4 deletions(-) (limited to 'test/sql') diff --git a/test/sql/test_query.py b/test/sql/test_query.py index fc040dfed..2f13486eb 100644 --- a/test/sql/test_query.py +++ b/test/sql/test_query.py @@ -81,11 +81,10 @@ class QueryTest(fixtures.TestBase): assert_raises_message( exc.StatementError, - r"A value is required for bind parameter 'user_name', in " + r"\(sqlalchemy.exc.InvalidRequestError\) A value is required for " + "bind parameter 'user_name', in " "parameter group 2 " - "\(original cause: (sqlalchemy.exc.)?InvalidRequestError: A " - "value is required for bind parameter 'user_name', in " - "parameter group 2\) u?'INSERT INTO query_users", + r"\[SQL: u?'INSERT INTO query_users", users.insert().execute, {'user_id': 7, 'user_name': 'jack'}, {'user_id': 8, 'user_name': 'ed'}, -- cgit v1.2.1 From ade27f35cb4911306404dcc74cce8bbf6f7d37bb Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sun, 19 Oct 2014 18:26:14 -0400 Subject: - Reversing a change that was made in 0.9, the "singleton" nature of the "constants" :func:`.null`, :func:`.true`, and :func:`.false` has been reverted. These functions returning a "singleton" object had the effect that different instances would be treated as the same regardless of lexical use, which in particular would impact the rendering of the columns clause of a SELECT statement. fixes #3170 --- test/sql/test_operators.py | 21 ++++++++++++++++++++- 1 file changed, 20 insertions(+), 1 deletion(-) (limited to 'test/sql') diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py index 5c401845b..e8ad88511 100644 --- a/test/sql/test_operators.py +++ b/test/sql/test_operators.py @@ -1,4 +1,4 @@ -from sqlalchemy.testing import fixtures, eq_, is_ +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 @@ -778,6 +778,25 @@ class ConjunctionTest(fixtures.TestBase, testing.AssertsCompiledSQL): "SELECT x WHERE NOT NULL" ) + def test_constant_non_singleton(self): + is_not_(null(), null()) + is_not_(false(), false()) + is_not_(true(), true()) + + def test_constant_render_distinct(self): + self.assert_compile( + select([null(), null()]), + "SELECT NULL AS anon_1, NULL AS anon_2" + ) + self.assert_compile( + select([true(), true()]), + "SELECT true AS anon_1, true AS anon_2" + ) + self.assert_compile( + select([false(), false()]), + "SELECT false AS anon_1, false AS anon_2" + ) + class OperatorPrecedenceTest(fixtures.TestBase, testing.AssertsCompiledSQL): __dialect__ = 'default' -- cgit v1.2.1 From 56d5732fbdf09508784df6dc4c04e5b39ac6be85 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Tue, 21 Oct 2014 17:58:51 -0400 Subject: - changelog for pullreq github:139 - add support for self-referential foreign keys to move over as well when the table name is changed. --- test/sql/test_metadata.py | 36 ++++++++++++++++++++++++++++++++++++ 1 file changed, 36 insertions(+) (limited to 'test/sql') diff --git a/test/sql/test_metadata.py b/test/sql/test_metadata.py index 2328e7f73..3c55242fd 100644 --- a/test/sql/test_metadata.py +++ b/test/sql/test_metadata.py @@ -722,6 +722,42 @@ class ToMetaDataTest(fixtures.TestBase, ComparesTables): eq_((table.key, table2.key), ('myschema.mytable', 'myschema.newtable')) + def test_change_name_selfref_fk_moves(self): + meta = MetaData() + + referenced = Table('ref', meta, + Column('id', Integer, primary_key=True), + ) + table = Table('mytable', meta, + Column('id', Integer, primary_key=True), + Column('parent_id', ForeignKey('mytable.id')), + Column('ref_id', ForeignKey('ref.id')) + ) + + table2 = table.tometadata(table.metadata, name='newtable') + assert table.metadata is table2.metadata + assert table2.c.ref_id.references(referenced.c.id) + assert table2.c.parent_id.references(table2.c.id) + + def test_change_name_selfref_fk_moves_w_schema(self): + meta = MetaData() + + referenced = Table('ref', meta, + Column('id', Integer, primary_key=True), + ) + table = Table('mytable', meta, + Column('id', Integer, primary_key=True), + Column('parent_id', ForeignKey('mytable.id')), + Column('ref_id', ForeignKey('ref.id')) + ) + + table2 = table.tometadata( + table.metadata, name='newtable', schema='newschema') + ref2 = referenced.tometadata(table.metadata, schema='newschema') + assert table.metadata is table2.metadata + assert table2.c.ref_id.references(ref2.c.id) + assert table2.c.parent_id.references(table2.c.id) + def _assert_fk(self, t2, schema, expected, referred_schema_fn=None): m2 = MetaData() existing_schema = t2.schema -- cgit v1.2.1 From 7bf5ac9c1e814c999d4930941935e1d5cfd236bf Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Fri, 31 Oct 2014 20:00:42 -0400 Subject: - ensure kwargs are passed for limit clause on a compound select as well, further fixes for #3034 --- test/sql/test_compiler.py | 16 ++++++++++++++++ 1 file changed, 16 insertions(+) (limited to 'test/sql') diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index 3e6b87351..bfafed599 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -238,6 +238,22 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): checkparams=params ) + def test_limit_offset_select_literal_binds(self): + stmt = select([1]).limit(5).offset(6) + self.assert_compile( + stmt, + "SELECT 1 LIMIT 5 OFFSET 6", + literal_binds=True + ) + + def test_limit_offset_compound_select_literal_binds(self): + stmt = select([1]).union(select([2])).limit(5).offset(6) + self.assert_compile( + stmt, + "SELECT 1 UNION SELECT 2 LIMIT 5 OFFSET 6", + literal_binds=True + ) + def test_select_precol_compile_ordering(self): s1 = select([column('x')]).select_from(text('a')).limit(5).as_scalar() s2 = select([s1]).limit(10) -- cgit v1.2.1 From edec583b459e955a30d40b5c5d8baaed0a2ec1c6 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Wed, 5 Nov 2014 04:22:30 -0500 Subject: - Fixed bug regarding expression mutations which could express itself as a "Could not locate column" error when using :class:`.Query` to select from multiple, anonymous column entities when querying against SQLite, as a side effect of the "join rewriting" feature used by the SQLite dialect. fixes #3241 --- test/sql/test_generative.py | 13 +++++++++++++ test/sql/test_join_rewriting.py | 25 ++++++++++++++++++++++++- 2 files changed, 37 insertions(+), 1 deletion(-) (limited to 'test/sql') diff --git a/test/sql/test_generative.py b/test/sql/test_generative.py index 6044cecb0..6b86614e6 100644 --- a/test/sql/test_generative.py +++ b/test/sql/test_generative.py @@ -132,6 +132,19 @@ class TraversalTest(fixtures.TestBase, AssertsExecutionResults): assert struct == s2 assert struct.is_other(s2) + def test_clone_anon_label(self): + from sqlalchemy.sql.elements import Grouping + c1 = Grouping(literal_column('q')) + s1 = select([c1]) + + class Vis(CloningVisitor): + def visit_grouping(self, elem): + pass + + vis = Vis() + s2 = vis.traverse(s1) + eq_(list(s2.inner_columns)[0].anon_label, c1.anon_label) + def test_change_in_place(self): struct = B(A("expr1"), A("expr2"), B(A("expr1b"), A("expr2b")), A("expr3")) diff --git a/test/sql/test_join_rewriting.py b/test/sql/test_join_rewriting.py index c8b24e2f2..ced65d7f1 100644 --- a/test/sql/test_join_rewriting.py +++ b/test/sql/test_join_rewriting.py @@ -251,6 +251,16 @@ class _JoinRewriteTestBase(AssertsCompiledSQL): self._f_b1a_where_in_b2a ) + def test_anon_scalar_subqueries(self): + s1 = select([1]).as_scalar() + s2 = select([2]).as_scalar() + + s = select([s1, s2]).apply_labels() + self._test( + s, + self._anon_scalar_subqueries + ) + class JoinRewriteTest(_JoinRewriteTestBase, fixtures.TestBase): @@ -389,6 +399,10 @@ class JoinRewriteTest(_JoinRewriteTestBase, fixtures.TestBase): "FROM a JOIN b2 ON a.id = b2.a_id)" ) + _anon_scalar_subqueries = ( + "SELECT (SELECT 1) AS anon_1, (SELECT 2) AS anon_2" + ) + class JoinPlainTest(_JoinRewriteTestBase, fixtures.TestBase): @@ -497,6 +511,10 @@ class JoinPlainTest(_JoinRewriteTestBase, fixtures.TestBase): "FROM a JOIN b2 ON a.id = b2.a_id)" ) + _anon_scalar_subqueries = ( + "SELECT (SELECT 1) AS anon_1, (SELECT 2) AS anon_2" + ) + class JoinNoUseLabelsTest(_JoinRewriteTestBase, fixtures.TestBase): @@ -605,6 +623,10 @@ class JoinNoUseLabelsTest(_JoinRewriteTestBase, fixtures.TestBase): "FROM a JOIN b2 ON a.id = b2.a_id)" ) + _anon_scalar_subqueries = ( + "SELECT (SELECT 1) AS anon_1, (SELECT 2) AS anon_2" + ) + class JoinExecTest(_JoinRewriteTestBase, fixtures.TestBase): @@ -615,7 +637,8 @@ class JoinExecTest(_JoinRewriteTestBase, fixtures.TestBase): _a_bc = _a_bc_comma_a1_selbc = _a__b_dc = _a_bkeyassoc = \ _a_bkeyassoc_aliased = _a_atobalias_balias_c_w_exists = \ _a_atobalias_balias = _b_ab1_union_c_ab2 = \ - _b_a_id_double_overlap_annotated = _f_b1a_where_in_b2a = None + _b_a_id_double_overlap_annotated = _f_b1a_where_in_b2a = \ + _anon_scalar_subqueries = None @classmethod def setup_class(cls): -- cgit v1.2.1