oracle wrongly assumes transactiona ddl

[sqlalchemy-bot]

Migrated issue, originally created by Ronny Pfannschmidt (@RonnyPfannschmidt)

oracle will commit before/after each ddl statement

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

cant reproduce (edit: do you mean, you want tranasctional DDL, or you dont? right now we have it as transactional DDL is supported, so there is not a COMMIT after each statement. though I'm not recalling if Oracle supports it or not). given:

"""initial rev

Revision ID: 44b1ab87838
Revises: None
Create Date: 2014-04-02 13:06:38.312421

"""

# revision identifiers, used by Alembic.
revision = '44b1ab87838'
down_revision = None

from alembic import op
import sqlalchemy as sa


def upgrade():
    op.create_table('table1', sa.Column('id', sa.Integer))


def downgrade():
    op.drop_table('table1')


"""rev two

Revision ID: 4acc7ee009f4
Revises: 44b1ab87838
Create Date: 2014-04-02 13:10:01.752407

"""

# revision identifiers, used by Alembic.
revision = '4acc7ee009f4'
down_revision = '44b1ab87838'

from alembic import op
import sqlalchemy as sa


def upgrade():
    op.create_table('table2', sa.Column('q', sa.Integer))
    op.create_table('table3', sa.Column('q', sa.Integer))


def downgrade():
    op.drop_table('table2')
    op.drop_table('table3')

url is sqlalchemy.url = oracle://scott:tiger@xe

output of a full upgrade:

#!

python -m alembic.config upgrade head
INFO  [sqlalchemy.engine.base.Engine] SELECT USER FROM DUAL
INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] SELECT CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL
INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] SELECT CAST('test unicode returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL
INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [alembic.migration] Context impl OracleImpl.
INFO  [alembic.migration] Will assume transactional DDL.
INFO  [sqlalchemy.engine.base.Engine] BEGIN (implicit)
INFO  [sqlalchemy.engine.base.Engine] SELECT table_name FROM all_tables WHERE table_name = :name AND owner = :schema_name
INFO  [sqlalchemy.engine.base.Engine] {'name': u'ALEMBIC_VERSION', 'schema_name': u'SCOTT'}
INFO  [sqlalchemy.engine.base.Engine] SELECT alembic_version.version_num 
FROM alembic_version
INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [alembic.migration] Running upgrade None -> 44b1ab87838, initial rev
INFO  [sqlalchemy.engine.base.Engine] 
CREATE TABLE table1 (
	id INTEGER
)


INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [alembic.migration] Running upgrade 44b1ab87838 -> 4acc7ee009f4, rev two
INFO  [sqlalchemy.engine.base.Engine] 
CREATE TABLE table2 (
	q INTEGER
)


INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] 
CREATE TABLE table3 (
	q INTEGER
)


INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] INSERT INTO alembic_version (version_num) VALUES ('4acc7ee009f4')
INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] COMMIT

there's no COMMIT after each statement. only one at the end.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

plus you can set it to whichever one you want: http://alembic.readthedocs.org/en/rel_0_6/api.html#alembic.environment.EnvironmentContext.configure.params.transactional_ddl

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

when you say "before" and "after", I think you are actually just observing what Oracle does. That's not us. see https://asktom.oracle.com/pls/asktom/f?p=100:11:0%3A%3A%3A%3AP11_QUESTION_ID:7072180788422.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

and then we finally see how you've really taken up a lot of my time by being so terse. you're saying you want our flag to match theirs. thanks for the clarity.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

The Oracle dialect sets "transactional DDL" to False by default,
as Oracle does not support transactional DDL.
fixes #245

→ 9f5ebb1

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed