Added a new argument to :class:`.Enum` and its base

:class:`.SchemaType` ``inherit_schema``.  When set to ``True``,
the type will set its ``schema`` attribute of that of the
:class:`.Table` to which it is associated.  This also occurs
during a :meth:`.Table.tometadata` operation; the :class:`.SchemaType`
is now copied in all cases when :meth:`.Table.tometadata` happens,
and if ``inherit_schema=True``, the type will take on the new
schema name passed to the method.   The ``schema`` is important
when used with the Postgresql backend, as the type results in
a ``CREATE TYPE`` statement. [ticket:2657]

5 files changed, 178 insertions, 8 deletions

diff --git a/doc/build/changelog/changelog_08.rst b/doc/build/changelog/changelog_08.rst
index bcdd77fa1..e6cc169b1 100644
--- a/doc/build/changelog/changelog_08.rst
+++ b/doc/build/changelog/changelog_08.rst
@@ -7,6 +7,21 @@
     :version: 0.8.0
 
     .. change::
+        :tags: feature, sql
+        :tickets: 2657
+
+      Added a new argument to :class:`.Enum` and its base
+      :class:`.SchemaType` ``inherit_schema``.  When set to ``True``,
+      the type will set its ``schema`` attribute of that of the
+      :class:`.Table` to which it is associated.  This also occurs
+      during a :meth:`.Table.tometadata` operation; the :class:`.SchemaType`
+      is now copied in all cases when :meth:`.Table.tometadata` happens,
+      and if ``inherit_schema=True``, the type will take on the new
+      schema name passed to the method.   The ``schema`` is important
+      when used with the Postgresql backend, as the type results in
+      a ``CREATE TYPE`` statement.
+
+    .. change::
         :tags: feature, postgresql
         :pullreq: 40
 
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 5f324e5dd..566a71da7 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1061,6 +1061,7 @@ class ENUM(sqltypes.Enum, _StringType):
         kw.pop('name', None)
         kw.pop('quote', None)
         kw.pop('native_enum', None)
+        kw.pop('inherit_schema', None)
         _StringType.__init__(self, length=length, **kw)
         sqltypes.Enum.__init__(self, *enums)
 
diff --git a/lib/sqlalchemy/schema.py b/lib/sqlalchemy/schema.py
index b9ee55abf..9d14bd3ca 100644
--- a/lib/sqlalchemy/schema.py
+++ b/lib/sqlalchemy/schema.py
@@ -634,16 +634,26 @@ class Table(SchemaItem, expression.TableClause):
 
         E.g.::
 
+            some_engine = create_engine("sqlite:///some.db")
+
             # create two metadata
-            meta1 = MetaData('sqlite:///querytest.db')
+            meta1 = MetaData()
             meta2 = MetaData()
 
             # load 'users' from the sqlite engine
-            users_table = Table('users', meta1, autoload=True)
+            users_table = Table('users', meta1, autoload=True,
+                                    autoload_with=some_engine)
 
             # create the same Table object for the plain metadata
             users_table_2 = users_table.tometadata(meta2)
 
+        :param metadata: Target :class:`.MetaData` object.
+        :param schema: Optional string name of a target schema, or
+         ``None`` for no schema.  The :class:`.Table` object will be
+         given this schema name upon copy.   Defaults to the special
+         symbol :attr:`.RETAIN_SCHEMA` which indicates no change should be
+         made to the schema name of the resulting :class:`.Table`.
+
         """
 
         if schema is RETAIN_SCHEMA:
@@ -1094,9 +1104,13 @@ class Column(SchemaItem, expression.ColumnClause):
             [c.copy(**kw) for c in self.constraints] + \
             [c.copy(**kw) for c in self.foreign_keys if not c.constraint]
 
+        type_ = self.type
+        if isinstance(type_, sqltypes.SchemaType):
+            type_ = type_.copy(**kw)
+
         c = self._constructor(
                 name=self.name,
-                type_=self.type,
+                type_=type_,
                 key=self.key,
                 primary_key=self.primary_key,
                 nullable=self.nullable,
diff --git a/lib/sqlalchemy/types.py b/lib/sqlalchemy/types.py
index ce64bb83e..b9f7b9444 100644
--- a/lib/sqlalchemy/types.py
+++ b/lib/sqlalchemy/types.py
@@ -1791,6 +1791,13 @@ class SchemaType(events.SchemaEventTarget):
     surrounding the association of the type object with a parent
     :class:`.Column`.
 
+    .. seealso::
+
+        :class:`.Enum`
+
+        :class:`.Boolean`
+
+
     """
 
     def __init__(self, **kw):
@@ -1798,6 +1805,7 @@ class SchemaType(events.SchemaEventTarget):
         self.quote = kw.pop('quote', None)
         self.schema = kw.pop('schema', None)
         self.metadata = kw.pop('metadata', None)
+        self.inherit_schema = kw.pop('inherit_schema', False)
         if self.metadata:
             event.listen(
                 self.metadata,
@@ -1814,6 +1822,9 @@ class SchemaType(events.SchemaEventTarget):
         column._on_table_attach(util.portable_instancemethod(self._set_table))
 
     def _set_table(self, column, table):
+        if self.inherit_schema:
+            self.schema = table.schema
+
         event.listen(
             table,
             "before_create",
@@ -1839,6 +1850,20 @@ class SchemaType(events.SchemaEventTarget):
                 util.portable_instancemethod(self._on_metadata_drop)
             )
 
+    def copy(self, **kw):
+        return self.adapt(self.__class__)
+
+    def adapt(self, impltype, **kw):
+        schema = kw.pop('schema', self.schema)
+        metadata = kw.pop('metadata', self.metadata)
+        return impltype(name=self.name,
+                    quote=self.quote,
+                    schema=schema,
+                    metadata=metadata,
+                    inherit_schema=self.inherit_schema,
+                    **kw
+                    )
+
     @property
     def bind(self):
         return self.metadata and self.metadata.bind or None
@@ -1891,7 +1916,7 @@ class Enum(String, SchemaType):
     By default, uses the backend's native ENUM type if available,
     else uses VARCHAR + a CHECK constraint.
 
-    See also:
+    .. seealso::
 
         :class:`~.postgresql.ENUM` - PostgreSQL-specific type,
         which has additional functionality.
@@ -1933,16 +1958,31 @@ class Enum(String, SchemaType):
            available. Defaults to True. When False, uses VARCHAR + check
            constraint for all backends.
 
-        :param schema: Schemaname of this type. For types that exist on the
+        :param schema: Schema name of this type. For types that exist on the
            target database as an independent schema construct (Postgresql),
            this parameter specifies the named schema in which the type is
            present.
 
+           .. note::
+
+                The ``schema`` of the :class:`.Enum` type does not
+                by default make use of the ``schema`` established on the
+                owning :class:`.Table`.  If this behavior is desired,
+                set the ``inherit_schema`` flag to ``True``.
+
         :param quote: Force quoting to be on or off on the type's name. If
            left as the default of `None`, the usual schema-level "case
            sensitive"/"reserved name" rules are used to determine if this
            type's name should be quoted.
 
+        :param inherit_schema: When ``True``, the "schema" from the owning
+           :class:`.Table` will be copied to the "schema" attribute of this
+           :class:`.Enum`, replacing whatever value was passed for the
+           ``schema`` attribute.   This also takes effect when using the
+           :meth:`.Table.tometadata` operation.
+
+           .. versionadded:: 0.8
+
         """
         self.enums = enums
         self.native_enum = kw.pop('native_enum', True)
@@ -1988,13 +2028,16 @@ class Enum(String, SchemaType):
         table.append_constraint(e)
 
     def adapt(self, impltype, **kw):
+        schema = kw.pop('schema', self.schema)
+        metadata = kw.pop('metadata', self.metadata)
         if issubclass(impltype, Enum):
             return impltype(name=self.name,
                         quote=self.quote,
-                        schema=self.schema,
-                        metadata=self.metadata,
+                        schema=schema,
+                        metadata=metadata,
                         convert_unicode=self.convert_unicode,
                         native_enum=self.native_enum,
+                        inherit_schema=self.inherit_schema,
                         *self.enums,
                         **kw
                         )
diff --git a/test/sql/test_metadata.py b/test/sql/test_metadata.py
index f8256bca7..1b8068f22 100644
--- a/test/sql/test_metadata.py
+++ b/test/sql/test_metadata.py
@@ -6,7 +6,7 @@ import pickle
 from sqlalchemy import Integer, String, UniqueConstraint, \
     CheckConstraint, ForeignKey, MetaData, Sequence, \
     ForeignKeyConstraint, ColumnDefault, Index, event,\
-    events, Unicode
+    events, Unicode, types as sqltypes
 from sqlalchemy.testing.schema import Table, Column
 from sqlalchemy import schema, exc
 import sqlalchemy as tsa
@@ -361,6 +361,7 @@ class MetaDataTest(fixtures.TestBase, ComparesTables):
         a2 = a.tometadata(m2)
         assert b2.c.y.references(a2.c.x)
 
+
     def test_pickle_metadata_sequence_restated(self):
         m1 = MetaData()
         Table('a', m1,
@@ -748,6 +749,102 @@ class TableTest(fixtures.TestBase, AssertsCompiledSQL):
         )
         is_(t._autoincrement_column, t.c.id)
 
+class SchemaTypeTest(fixtures.TestBase):
+    class MyType(sqltypes.SchemaType, sqltypes.TypeEngine):
+        column = None
+        table = None
+        evt_targets = ()
+
+        def _set_table(self, column, table):
+            super(SchemaTypeTest.MyType, self)._set_table(column, table)
+            self.column = column
+            self.table = table
+
+        def _on_table_create(self, target, bind, **kw):
+            self.evt_targets += (target,)
+
+    def test_independent_schema(self):
+        m = MetaData()
+        type_ = self.MyType(schema="q")
+        t1 = Table('x', m, Column("y", type_), schema="z")
+        eq_(t1.c.y.type.schema, "q")
+
+    def test_inherit_schema(self):
+        m = MetaData()
+        type_ = self.MyType(schema="q", inherit_schema=True)
+        t1 = Table('x', m, Column("y", type_), schema="z")
+        eq_(t1.c.y.type.schema, "z")
+
+    def test_independent_schema_enum(self):
+        m = MetaData()
+        type_ = sqltypes.Enum("a", schema="q")
+        t1 = Table('x', m, Column("y", type_), schema="z")
+        eq_(t1.c.y.type.schema, "q")
+
+    def test_inherit_schema_enum(self):
+        m = MetaData()
+        type_ = sqltypes.Enum("a", "b", "c", schema="q", inherit_schema=True)
+        t1 = Table('x', m, Column("y", type_), schema="z")
+        eq_(t1.c.y.type.schema, "z")
+
+    def test_tometadata_copy_type(self):
+        m1 = MetaData()
+
+        type_ = self.MyType()
+        t1 = Table('x', m1, Column("y", type_))
+
+        m2 = MetaData()
+        t2 = t1.tometadata(m2)
+
+        # metadata isn't set
+        is_(t2.c.y.type.metadata, None)
+
+        # our test type sets table, though
+        is_(t2.c.y.type.table, t2)
+
+    def test_tometadata_independent_schema(self):
+        m1 = MetaData()
+
+        type_ = self.MyType()
+        t1 = Table('x', m1, Column("y", type_))
+
+        m2 = MetaData()
+        t2 = t1.tometadata(m2, schema="bar")
+
+        eq_(t2.c.y.type.schema, None)
+
+    def test_tometadata_inherit_schema(self):
+        m1 = MetaData()
+
+        type_ = self.MyType(inherit_schema=True)
+        t1 = Table('x', m1, Column("y", type_))
+
+        m2 = MetaData()
+        t2 = t1.tometadata(m2, schema="bar")
+
+        eq_(t1.c.y.type.schema, None)
+        eq_(t2.c.y.type.schema, "bar")
+
+    def test_tometadata_independent_events(self):
+        m1 = MetaData()
+
+        type_ = self.MyType()
+        t1 = Table('x', m1, Column("y", type_))
+
+        m2 = MetaData()
+        t2 = t1.tometadata(m2)
+
+        t1.dispatch.before_create(t1, testing.db)
+        eq_(t1.c.y.type.evt_targets, (t1,))
+        eq_(t2.c.y.type.evt_targets, ())
+
+        t2.dispatch.before_create(t2, testing.db)
+        t2.dispatch.before_create(t2, testing.db)
+        eq_(t1.c.y.type.evt_targets, (t1,))
+        eq_(t2.c.y.type.evt_targets, (t2, t2))
+
+
+
 class SchemaTest(fixtures.TestBase, AssertsCompiledSQL):
 
     def test_default_schema_metadata_fk(self):