- adjust the previous checkin a bit. need to transfer sequences over from pickle

even if they are *not* restated.  its necessary here to do a "replacement"
scheme when an incoming sequence replaces the previous.  Theoretically
we could do what Table does here, i.e. use a singleton constructor, but
this is heavyhanded.   The most recent sequence placed in is the winner
is likely the most expected behavior.

3 files changed, 45 insertions, 9 deletions

diff --git a/lib/sqlalchemy/engine/ddl.py b/lib/sqlalchemy/engine/ddl.py
index dca183f9a..79958baae 100644
--- a/lib/sqlalchemy/engine/ddl.py
+++ b/lib/sqlalchemy/engine/ddl.py
@@ -51,7 +51,7 @@ class SchemaGenerator(DDLBase):
             tables = metadata.tables.values()
         collection = [t for t in sql_util.sort_tables(tables) 
                         if self._can_create_table(t)]
-        seq_coll = [s for s in metadata._sequences 
+        seq_coll = [s for s in metadata._sequences.values() 
                         if s.column is None and self._can_create_sequence(s)]
 
         metadata.dispatch.before_create(metadata, self.connection,
@@ -112,7 +112,7 @@ class SchemaDropper(DDLBase):
             tables = metadata.tables.values()
         collection = [t for t in reversed(sql_util.sort_tables(tables)) 
                                 if self._can_drop_table(t)]
-        seq_coll = [s for s in metadata._sequences 
+        seq_coll = [s for s in metadata._sequences.values() 
                                 if s.column is None and self._can_drop_sequence(s)]
 
         metadata.dispatch.before_drop(metadata, self.connection,
diff --git a/lib/sqlalchemy/schema.py b/lib/sqlalchemy/schema.py
index c8d00f407..1da43c6b1 100644
--- a/lib/sqlalchemy/schema.py
+++ b/lib/sqlalchemy/schema.py
@@ -1486,6 +1486,7 @@ class Sequence(DefaultGenerator):
         self.quote = quote
         self.schema = schema
         self.metadata = metadata
+        self._key = _get_table_key(name, schema)
         if metadata:
             self._set_metadata(metadata)
 
@@ -1520,7 +1521,7 @@ class Sequence(DefaultGenerator):
 
     def _set_metadata(self, metadata):
         self.metadata = metadata
-        self.metadata._sequences.add(self)
+        self.metadata._sequences[self._key] = self
 
     @property
     def bind(self):
@@ -2099,7 +2100,7 @@ class MetaData(SchemaItem):
         """
         self.tables = util.immutabledict()
         self._schemas = set()
-        self._sequences = set()
+        self._sequences = {}
         self.bind = bind
         if reflect:
             if not bind:
@@ -2131,12 +2132,13 @@ class MetaData(SchemaItem):
                                 if t.schema is not None])
 
     def __getstate__(self):
-        return {'tables': self.tables, 'schemas':self._schemas}
+        return {'tables': self.tables, 'schemas':self._schemas, 
+                'sequences':self._sequences}
 
     def __setstate__(self, state):
         self.tables = state['tables']
         self._bind = None
-        self._sequences = set()
+        self._sequences = state['sequences']
         self._schemas = state['schemas']
 
     def is_bound(self):
diff --git a/test/sql/test_metadata.py b/test/sql/test_metadata.py
index 04bc0c345..09f1869a4 100644
--- a/test/sql/test_metadata.py
+++ b/test/sql/test_metadata.py
@@ -288,7 +288,7 @@ class MetaDataTest(TestBase, ComparesTables):
         finally:
             meta.drop_all(testing.db)
 
-    def test_pickle_metadata_sequence(self):
+    def test_pickle_metadata_sequence_restated(self):
         m1 = MetaData()
         Table('a',m1,
              Column('id',Integer,primary_key=True),
@@ -296,12 +296,46 @@ class MetaDataTest(TestBase, ComparesTables):
 
         m2 = pickle.loads(pickle.dumps(m1))
 
+        s2 = Sequence("x_seq")
         t2 = Table('a', m2, 
              Column('id',Integer,primary_key=True),
-             Column('x', Integer, Sequence("x_seq")),
+             Column('x', Integer, s2),
              useexisting=True)
 
-        eq_(m2._sequences, set([t2.c.x.default]))
+        assert m2._sequences['x_seq'] is t2.c.x.default
+        assert m2._sequences['x_seq'] is s2
+
+
+    def test_sequence_restated_replaced(self):
+        """Test restatement of Sequence replaces."""
+
+        m1 = MetaData()
+        s1 = Sequence("x_seq")
+        t = Table('a', m1, 
+             Column('x', Integer, s1)
+        )
+        assert m1._sequences['x_seq'] is s1
+
+        s2 = Sequence('x_seq')
+        t2 = Table('a', m1,
+             Column('x', Integer, s2),
+             useexisting=True
+        )
+        assert t.c.x.default is s2
+        assert m1._sequences['x_seq'] is s2
+
+
+    def test_pickle_metadata_sequence_implicit(self):
+        m1 = MetaData()
+        Table('a',m1,
+             Column('id',Integer,primary_key=True),
+             Column('x', Integer, Sequence("x_seq")))
+
+        m2 = pickle.loads(pickle.dumps(m1))
+
+        t2 = Table('a', m2, useexisting=True)
+
+        eq_(m2._sequences, {'x_seq':t2.c.x.default})
 
     def test_pickle_metadata_schema(self):
         m1 = MetaData()