From e35dcee6ca9c12e5e78702ae7679796cbe8365f2 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Tue, 3 Nov 2009 18:33:57 +0000 Subject: [PATCH] - The "start" and "increment" attributes on Sequence now generate "START WITH" and "INCREMENT BY" by default, on Oracle and Postgresql. Firebird doesn't support these keywords right now. [ticket:1545] --- CHANGES | 7 ++- lib/sqlalchemy/dialects/firebird/base.py | 9 ++- lib/sqlalchemy/dialects/oracle/base.py | 6 -- lib/sqlalchemy/dialects/postgresql/base.py | 6 -- lib/sqlalchemy/sql/compiler.py | 11 ++++ test/sql/test_defaults.py | 68 ++++++++++++++++++++-- 6 files changed, 89 insertions(+), 18 deletions(-) diff --git a/CHANGES b/CHANGES index ee1f894b25..cc56b3a18b 100644 --- a/CHANGES +++ b/CHANGES @@ -303,7 +303,12 @@ CHANGES - PrimaryKeyConstraint.remove() These should be constructed declaratively (i.e. in one construction). - + + - The "start" and "increment" attributes on Sequence now + generate "START WITH" and "INCREMENT BY" by default, + on Oracle and Postgresql. Firebird doesn't support + these keywords right now. [ticket:1545] + - UniqueConstraint, Index, PrimaryKeyConstraint all accept lists of column names or column objects as arguments. diff --git a/lib/sqlalchemy/dialects/firebird/base.py b/lib/sqlalchemy/dialects/firebird/base.py index 232583c39e..921c70e980 100644 --- a/lib/sqlalchemy/dialects/firebird/base.py +++ b/lib/sqlalchemy/dialects/firebird/base.py @@ -271,7 +271,14 @@ class FBDDLCompiler(sql.compiler.DDLCompiler): def visit_create_sequence(self, create): """Generate a ``CREATE GENERATOR`` statement for the sequence.""" - + + # no syntax for these + # http://www.firebirdsql.org/manual/generatorguide-sqlsyntax.html + if create.element.start is not None: + raise NotImplemented("Firebird SEQUENCE doesn't support START WITH") + if create.element.increment is not None: + raise NotImplemented("Firebird SEQUENCE doesn't support INCREMENT BY") + if self.dialect._version_two: return "CREATE SEQUENCE %s" % self.preparer.format_sequence(create.element) else: diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 7b141ed235..22ba2ce934 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -441,12 +441,6 @@ class OracleCompiler(compiler.SQLCompiler): class OracleDDLCompiler(compiler.DDLCompiler): - def visit_create_sequence(self, create): - return "CREATE SEQUENCE %s" % self.preparer.format_sequence(create.element) - - def visit_drop_sequence(self, drop): - return "DROP SEQUENCE %s" % self.preparer.format_sequence(drop.element) - def define_constraint_cascades(self, constraint): text = "" if constraint.ondelete is not None: diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index bc8cff9052..97108b3cbb 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -340,12 +340,6 @@ class PGDDLCompiler(compiler.DDLCompiler): colspec += " NOT NULL" return colspec - def visit_create_sequence(self, create): - return "CREATE SEQUENCE %s" % self.preparer.format_sequence(create.element) - - def visit_drop_sequence(self, drop): - return "DROP SEQUENCE %s" % self.preparer.format_sequence(drop.element) - def visit_enum_constraint(self, constraint): if not constraint.type.native_enum: return super(PGDDLCompiler, self).visit_enum_constraint(constraint) diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 088ca19695..6802bfbefc 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -1024,7 +1024,18 @@ class DDLCompiler(engine.Compiled): self.preparer.format_table(create.element.table), self.process(create.element) ) + + def visit_create_sequence(self, create): + text = "CREATE SEQUENCE %s" % self.preparer.format_sequence(create.element) + if create.element.increment is not None: + text += " INCREMENT BY %d" % create.element.increment + if create.element.start is not None: + text += " START WITH %d" % create.element.start + return text + def visit_drop_sequence(self, drop): + return "DROP SEQUENCE %s" % self.preparer.format_sequence(drop.element) + def visit_drop_constraint(self, drop): preparer = self.preparer return "ALTER TABLE %s DROP CONSTRAINT %s%s" % ( diff --git a/test/sql/test_defaults.py b/test/sql/test_defaults.py index 04809b48ae..092c7640e3 100644 --- a/test/sql/test_defaults.py +++ b/test/sql/test_defaults.py @@ -1,6 +1,7 @@ from sqlalchemy.test.testing import eq_, assert_raises, assert_raises_message import datetime from sqlalchemy import Sequence, Column, func +from sqlalchemy.schema import CreateSequence, DropSequence from sqlalchemy.sql import select, text import sqlalchemy as sa from sqlalchemy.test import testing, engines @@ -535,10 +536,10 @@ class AutoIncrementTest(_base.TablesTest): nonai.insert().execute(id=1, data='row 1') -class SequenceTest(testing.TestBase): - __requires__ = ('sequences',) +class SequenceTest(testing.TestBase, testing.AssertsCompiledSQL): @classmethod + @testing.requires.sequences def setup_class(cls): global cartitems, sometable, metadata metadata = MetaData(testing.db) @@ -556,7 +557,60 @@ class SequenceTest(testing.TestBase): metadata.create_all() - def testseqnonpk(self): + + def test_compile(self): + self.assert_compile( + CreateSequence(Sequence('foo_seq')), + "CREATE SEQUENCE foo_seq", + use_default_dialect=True, + ) + + self.assert_compile( + CreateSequence(Sequence('foo_seq', start=5)), + "CREATE SEQUENCE foo_seq START WITH 5", + use_default_dialect=True, + ) + + self.assert_compile( + CreateSequence(Sequence('foo_seq', increment=2)), + "CREATE SEQUENCE foo_seq INCREMENT BY 2", + use_default_dialect=True, + ) + + self.assert_compile( + CreateSequence(Sequence('foo_seq', increment=2, start=5)), + "CREATE SEQUENCE foo_seq INCREMENT BY 2 START WITH 5", + use_default_dialect=True, + ) + + self.assert_compile( + DropSequence(Sequence('foo_seq')), + "DROP SEQUENCE foo_seq", + use_default_dialect=True, + ) + + + @testing.fails_on('firebird', 'no FB support for start/increment') + @testing.requires.sequences + def test_start_increment(self): + for seq in ( + Sequence('foo_seq'), + Sequence('foo_seq', start=8), + Sequence('foo_seq', increment=5)): + seq.create(testing.db) + try: + values = [ + testing.db.execute(seq) for i in range(3) + ] + start = seq.start or 1 + inc = seq.increment or 1 + assert values == list(xrange(start, start + inc * 3, inc)) + + finally: + seq.drop(testing.db) + + @testing.requires.sequences + def test_seq_nonpk(self): """test sequences fire off as defaults on non-pk columns""" engine = engines.testing_engine(options={'implicit_returning':False}) @@ -576,7 +630,8 @@ class SequenceTest(testing.TestBase): (3, "name3", 3), (4, "name4", 4)]) - def testsequence(self): + @testing.requires.sequences + def test_sequence(self): cartitems.insert().execute(description='hi') cartitems.insert().execute(description='there') r = cartitems.insert().execute(description='lala') @@ -594,6 +649,7 @@ class SequenceTest(testing.TestBase): @testing.fails_on('maxdb', 'FIXME: unknown') # maxdb db-api seems to double-execute NEXTVAL internally somewhere, # throwing off the numbers for these tests... + @testing.requires.sequences def test_implicit_sequence_exec(self): s = Sequence("my_sequence", metadata=MetaData(testing.db)) s.create() @@ -604,6 +660,7 @@ class SequenceTest(testing.TestBase): s.drop() @testing.fails_on('maxdb', 'FIXME: unknown') + @testing.requires.sequences def teststandalone_explicit(self): s = Sequence("my_sequence") s.create(bind=testing.db) @@ -613,6 +670,7 @@ class SequenceTest(testing.TestBase): finally: s.drop(testing.db) + @testing.requires.sequences def test_checkfirst(self): s = Sequence("my_sequence") s.create(testing.db, checkfirst=False) @@ -621,11 +679,13 @@ class SequenceTest(testing.TestBase): s.drop(testing.db, checkfirst=True) @testing.fails_on('maxdb', 'FIXME: unknown') + @testing.requires.sequences def teststandalone2(self): x = cartitems.c.cart_id.default.execute() self.assert_(1 <= x <= 4) @classmethod + @testing.requires.sequences def teardown_class(cls): metadata.drop_all() -- 2.47.3