Autogenerate ALWAYS assumes an empty schema with Oracle 11.2

[Nana Okyere]

Disclosure: I'm a newbie :-)

Starting with an empty table, I autogenerate and then do upgrade and it works fine the first time. When I make a small change to the model(s) like adding a  new column, then I do autogenerate, alembic generates the upgrade and downgrade code as if starting from scratch. It is supposed to only generate code for what has changed or the difference between the objects in the schema and the models. But subsequent attempted migrations always generates ALL tables again whether or not they already existed on the schema. Hence when I do 'upgrade', I get a message like:

name is already used by an existing object

 [SQL: '\nCREATE TABLE dut_departments (\n\tid INTEGER NOT NULL, \n\tname VARCHAR2(120 CHAR) NOT NULL, \n\tanother_col VARCHAR2(120 CHAR), \n\tPRIMARY KEY (id), \n\tUNIQUE (another_col), \n\tUNIQUE (name)\n)\n\n']

Clearly, the upgrade is issuing commands to create tables that already exist on the schema with the same name. I'm at a complete stop now. As a control test, I kept my models and did the autogenerate and upgrade on sqlite. It works as expected. Only changes from last migration are seen in the revision file. Is there something special about oracle I need to know to get autogenerate and upgrade to only pick up changes to models? Thanks.

[Mike Bayer]

On 01/02/2016 01:18 PM, Nana Okyere wrote:
> Disclosure: I'm a newbie :-)
>
> Starting with an empty table, I autogenerate and then do upgrade and it
> works fine the first time. When I make a small change to the model(s)
> like adding a new column, then I do autogenerate, alembic generates the
> upgrade and downgrade code as if starting from scratch.

this would indicate it is seeing your Table objects (e.g. the "model"),
but is not seeing any tables in the database.

Are these tables located in explicitly named schemas? If so, you'd need
to turn on the "include_schemas" option:

http://alembic.readthedocs.org/en/latest/api/runtime.html?highlight=include_schema#alembic.runtime.environment.EnvironmentContext.configure.params.include_schemas


otherwise, send along what logging is coming up with. For detailed SQL
logging of the commands used to inspect your database set the SQLAlchemy
logger debug level to DEBUG inside of alembic.ini:

[logger_sqlalchemy]
level = DEBUG
handlers =
qualname = sqlalchemy.engine

this will emit all the queries used to look for tables in the oracle
database as well as what results come back.

It is supposed
> to only generate code for what has changed or the difference between the
> objects in the schema and the models. But subsequent attempted
> migrations always generates ALL tables again whether or not they already
> existed on the schema. Hence when I do 'upgrade', I get a message like:
>
> name is already used by an existing object
>
> [SQL: '\nCREATE TABLE dut_departments (\n\tid INTEGER NOT NULL,
> \n\tname VARCHAR2(120 CHAR) NOT NULL, \n\tanother_col VARCHAR2(120
> CHAR), \n\tPRIMARY KEY (id), \n\tUNIQUE (another_col), \n\tUNIQUE
> (name)\n)\n\n']
>
>
> Clearly, the upgrade is issuing commands to create tables that already
> exist on the schema with the same name. I'm at a complete stop now. As a
> control test, I kept my models and did the autogenerate and upgrade on
> sqlite. It works as expected. Only changes from last migration are seen
> in the revision file. Is there something special about oracle I need to
> know to get autogenerate and upgrade to only pick up changes to models?
> Thanks.
>

> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy-alem...@googlegroups.com
> <mailto:sqlalchemy-alem...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

[Nana Okyere]

The tables are not in an explicitly named schema. By default, it uses the schema of the user logged in. Looking at the debug output on the console, I can see that it uses the correct schema i.e. SYSTEM.

I set the logging to debug and went through the whole process. I'll attach the entire output. I'm using flask-migrate, which just puts a nice wrapper around alembic for flask development purposes. So the 'migrate' command is a proxy an alias for autogenerate. 'upgrade' and 'downgrade' are the same. So please see the output attached. Thank you.

Nana

[Mike Bayer]

On 01/02/2016 07:24 PM, Nana Okyere wrote:
> The tables are not in an explicitly named schema. By default, it uses
> the schema of the user logged in. Looking at the debug output on the
> console, I can see that it uses the correct schema i.e. SYSTEM.

that's the problem; it's not appropriate to create end-user tables in
SYSTEM on Oracle, that account / schema is for administrative tables
only. You can see that SQLAlchemy's reflection queries explicitly skip
over SYSTEM so that these administrative tables aren't included in table
lists.

See http://www.dba-oracle.com/t_sys_sstem_dictionary_schemas.htm for
details on the SYSTEM schema.

I'm assuming you're using Oracle because this is part of a job. If
you're strictly learning about databases and don't actually need to use
Oracle, it's one of the worst databases to practice on as it's very old,
"enterprise" and loaded with esoterics; I'd start out with a Postgresql
or MySQL.

>
> I set the logging to debug and went through the whole process. I'll
> attach the entire output. I'm using flask-migrate, which just puts a
> nice wrapper around alembic for flask development purposes. So the
> 'migrate' command is a proxy an alias for autogenerate. 'upgrade' and
> 'downgrade' are the same. So please see the output attached. Thank you.
>
> Nana
>

[Nana Okyere]

My goodness! That was it! I created and used a standard user in Oracle and things worked as expected. Thanks a bunch. The more I run into some of these things, the more I dislike Oracle. Right on Michael, I learn, test, develop against Oracle XE because we use Oracle at work so I have to live through some of these things. I've used mysql before but not postgresql. Anyway, thanks. Now, I know not to use the system or sys user accounts and schema in Oracle for any kind of development.