Re: Alter boolean column on Oracle

[Ofir Herzas]

Hi Mike,
I've just checked my code and I noticed that I already use existing_type 
(not sure what was wrong with the documentation in that aspect).

Can you elaborate a bit on specifying custom rules as you mentioned?
I tried issuing a drop_constraint before calling the alter_column but that 
doesn't stop alter_column from trying to remove the check constraint by 
itself, leading to the same exception.
On the other hand, I didn't see any parameter in the alter_column method 
that accepts a constraint name

Thanks,
Ofir


On Thursday, December 10, 2015 at 3:39:42 PM UTC+2, Ofir Herzas wrote:
>
> I'm trying to run an alter_column operation from a Boolean type column to 
> SmallInteger on Oracle.
>
> According to the documentation: "...Type changes which are against the 
> SQLAlchemy “schema” types Boolean 
> 
>  and Enum 
>  
> may 
> also add or drop constraints which accompany those types on backends that 
> don’t support them natively. The existing_server_default argument is used 
> in this case as well to remove a previous constraint"
>
> Does this mean that the alter column should work out-of-the-box or do I 
> have to remove the constraint myself because I'm getting the following 
> error on alembic 0.8.3 (sqlalchemy 1.0.9):
>
> sqlalchemy.exc.CompileError: Can't emit DROP CONSTRAINT for constraint 
> CheckConstraint( 0x28a9b10>, name='_unnamed_', table=Table('t_message', 
> MetaData(bind=None), Column('is_bulletin', Boolean(), table=), 
> schema=None), 
> _create_rule= 0x289ac68>, _type_bound=True); it has no name
>
>
> And on a previous version (alembic 0.6.5, sqlalchemy 0.9.7) I got the 
> following error:
>
> sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-02443: Cannot drop 
> constraint  - nonexistent constraint
>  'ALTER TABLE t_message DROP CONSTRAINT None' {}
>
>
> Your help will be appreciated...
>

-- 
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-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: Alter boolean column on Oracle

[Mike Bayer]

On 12/11/2015 04:39 PM, Ofir Herzas wrote:
> Hi Mike,
> I've just checked my code and I noticed that I already use existing_type
> (not sure what was wrong with the documentation in that aspect).
> 
> Can you elaborate a bit on specifying custom rules as you mentioned?
> I tried issuing a drop_constraint before calling the alter_column but
> that doesn't stop alter_column from trying to remove the check
> constraint by itself, leading to the same exception.
> On the other hand, I didn't see any parameter in the alter_column method
> that accepts a constraint name

You can call the drop_constraint(), then when you do the alter_column,
inside the existing_type put "create_constraint=False", so that it sees
it as an un-constrainted type.


drop_contraint('constraint_name')
alter_column('table', 'column', type=NewType(),
existing_type=Boolean(create_constraint=False))




> 
> Thanks,
> Ofir
> 
> 
> On Thursday, December 10, 2015 at 3:39:42 PM UTC+2, Ofir Herzas wrote:
> 
> I'm trying to run an alter_column operation from a Boolean type
> column to SmallInteger on Oracle.
> 
> According to the documentation: "...Type changes which are against
> the SQLAlchemy “schema” types |Boolean|
> 
> 
>  and |Enum|
> 
>  
> may
> also add or drop constraints which accompany those types on backends
> that don’t support them natively.
> The |existing_server_default| argument is used in this case as well
> to remove a previous constraint"
> 
> Does this mean that the alter column should work out-of-the-box or
> do I have to remove the constraint myself because I'm getting the
> following error on alembic 0.8.3 (sqlalchemy 1.0.9):
> 
> |
> sqlalchemy.exc.CompileError:Can't emit DROP CONSTRAINT for
> constraint CheckConstraint( object at 0x28a9b10>, name='_unnamed_', table=Table('t_message',
> MetaData(bind=None), Column('is_bulletin', Boolean(),
> table=), schema=None),
> _create_rule= object at 0x289ac68>, _type_bound=True); it has no name
> |
> 
> 
> And on a previous version (alembic 0.6.5, sqlalchemy 0.9.7) I got
> the following error:
> 
> |
> sqlalchemy.exc.DatabaseError:(DatabaseError)ORA-02443:Cannotdrop
> constraint  -nonexistent constraint
>  'ALTER TABLE t_message DROP CONSTRAINT None'{}
> |
> 
> 
> Your help will be appreciated...
> 
> -- 
> 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-alembic+unsubscr...@googlegroups.com
> .
> For more options, visit https://groups.google.com/d/optout.

-- 
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-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

RE: Alter boolean column on Oracle

[Ofir Herzas]

Thanks Mike. It seems that I'm in the worst case :)

I've started using Sqlalchemy before the naming convention feature was 
introduced and so I didn't use it.
A while back when I wanted to port my code to using it, I didn't find an easy 
way to do it and so I decided not to do it.
My application uses 45 tables with 115 foreign keys on multiple platforms 
(mainly Mysql and Oracle) - Is there a way I can automate the porting through a 
script using inspection or something similar? Do you have a recipe for 
something of that sort?

Thanks again,
Ofir


I'll try 

-Original Message-
From: sqlalchemy-alembic@googlegroups.com 
[mailto:sqlalchemy-alembic@googlegroups.com] On Behalf Of Mike Bayer
Sent: Thursday, December 10, 2015 19:24
To: sqlalchemy-alembic@googlegroups.com
Subject: Re: Alter boolean column on Oracle



On 12/10/2015 08:39 AM, Ofir Herzas wrote:
> I'm trying to run an alter_column operation from a Boolean type column
> to SmallInteger on Oracle.
> 
> According to the documentation: "...Type changes which are against the
> SQLAlchemy “schema” types |Boolean|
> <http://www.sqlalchemy.org/docs/core/type_basics.html#sqlalchemy.types.Boolean>
>  and |Enum|
> <http://www.sqlalchemy.org/docs/core/type_basics.html#sqlalchemy.types.Enum> 
> may
> also add or drop constraints which accompany those types on backends
> that don’t support them natively. The |existing_server_default| argument
> is used in this case as well to remove a previous constraint"

OK that document is wrong.  At least in 0.8 and maybe previously, that
should be "existing_type", because the current source code is looking at
existing_type to determine what previous type-bound constraints need to
be dropped.  I've committed that and it should be on readthedocs shortly.


> 
> Does this mean that the alter column should work out-of-the-box or do I
> have to remove the constraint myself because I'm getting the following
> error on alembic 0.8.3 (sqlalchemy 1.0.9):

Well, the constraint needs to have a name.  At least in the
existing_type that you send, for Boolean you'd need to also include the
"name" field that is the name of the constraint.

If this is an existing production DB, the constraint already has some
name and on Oracle it will be something like SYS12345.  But
unfortunately that name is not portable so this depends on if you are
targeting a lot of databases, or just one.

If you're just working out how these migrations would run from start to
finish, the real way you'd want to address this is at the very least
ensuring that any Boolean or Enum type you use where you've also used
create_constraint=True (the default) also has a "name".If you want
that to be automatic, you can use naming conventions.  The whole story
here is documented at http://alembic.readthedocs.org/en/latest/naming.html.

In the worst case, you in fact have production DBs, there are multiple,
you didn't give the constraints a specific name up front, and this
constraint probably has a different name on each one.Your options
there are either to manually rename constraints to something predictable
on these DBs and then use the full naming approach, *or* you'd need to
write custom rules in your migrations that do a live search of the
Oracle catalog tables, locate these constraints dynamically, and drops them.





> |
> sqlalchemy.exc.CompileError:Can't emit DROP CONSTRAINT for constraint
> CheckConstraint( 0x28a9b10>, name='_unnamed_', table=Table('t_message',
> MetaData(bind=None), Column('is_bulletin', Boolean(),
> table=), schema=None),
> _create_rule= at 0x289ac68>, _type_bound=True); it has no name
> |
> 
> 
> And on a previous version (alembic 0.6.5, sqlalchemy 0.9.7) I got the
> following error:
> 
> |
> sqlalchemy.exc.DatabaseError:(DatabaseError)ORA-02443:Cannotdrop
> constraint  -nonexistent constraint
>  'ALTER TABLE t_message DROP CONSTRAINT None'{}
> |
> 
> 
> Your help will be appreciated...
> 
> -- 
> 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-alembic+unsubscr...@googlegroups.com
> <mailto:sqlalchemy-alembic+unsubscr...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

-- 
You received this message because you are subscribed to a topic in the Google 
Groups "sqlalchemy-alembic" group.
To unsubscribe from this topic, visit 
https://groups.google.com/d/topic/sqlalchemy-alembic/OPctuividc0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to 
sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

-- 
You received this message because you are subscri

RE: Alter boolean column on Oracle

[Ofir Herzas]

BTW, A little bit off topic but how does the naming convention support 
character limitations such as the 30 characters max length for the foreign keys 
in Oracle? Does it provide any hooks?


-Original Message-
From: sqlalchemy-alembic@googlegroups.com 
[mailto:sqlalchemy-alembic@googlegroups.com] On Behalf Of Mike Bayer
Sent: Thursday, December 10, 2015 19:24
To: sqlalchemy-alembic@googlegroups.com
Subject: Re: Alter boolean column on Oracle



On 12/10/2015 08:39 AM, Ofir Herzas wrote:
> I'm trying to run an alter_column operation from a Boolean type column
> to SmallInteger on Oracle.
> 
> According to the documentation: "...Type changes which are against the
> SQLAlchemy “schema” types |Boolean|
> <http://www.sqlalchemy.org/docs/core/type_basics.html#sqlalchemy.types.Boolean>
>  and |Enum|
> <http://www.sqlalchemy.org/docs/core/type_basics.html#sqlalchemy.types.Enum> 
> may
> also add or drop constraints which accompany those types on backends
> that don’t support them natively. The |existing_server_default| argument
> is used in this case as well to remove a previous constraint"

OK that document is wrong.  At least in 0.8 and maybe previously, that
should be "existing_type", because the current source code is looking at
existing_type to determine what previous type-bound constraints need to
be dropped.  I've committed that and it should be on readthedocs shortly.


> 
> Does this mean that the alter column should work out-of-the-box or do I
> have to remove the constraint myself because I'm getting the following
> error on alembic 0.8.3 (sqlalchemy 1.0.9):

Well, the constraint needs to have a name.  At least in the
existing_type that you send, for Boolean you'd need to also include the
"name" field that is the name of the constraint.

If this is an existing production DB, the constraint already has some
name and on Oracle it will be something like SYS12345.  But
unfortunately that name is not portable so this depends on if you are
targeting a lot of databases, or just one.

If you're just working out how these migrations would run from start to
finish, the real way you'd want to address this is at the very least
ensuring that any Boolean or Enum type you use where you've also used
create_constraint=True (the default) also has a "name".If you want
that to be automatic, you can use naming conventions.  The whole story
here is documented at http://alembic.readthedocs.org/en/latest/naming.html.

In the worst case, you in fact have production DBs, there are multiple,
you didn't give the constraints a specific name up front, and this
constraint probably has a different name on each one.Your options
there are either to manually rename constraints to something predictable
on these DBs and then use the full naming approach, *or* you'd need to
write custom rules in your migrations that do a live search of the
Oracle catalog tables, locate these constraints dynamically, and drops them.





> |
> sqlalchemy.exc.CompileError:Can't emit DROP CONSTRAINT for constraint
> CheckConstraint( 0x28a9b10>, name='_unnamed_', table=Table('t_message',
> MetaData(bind=None), Column('is_bulletin', Boolean(),
> table=), schema=None),
> _create_rule= at 0x289ac68>, _type_bound=True); it has no name
> |
> 
> 
> And on a previous version (alembic 0.6.5, sqlalchemy 0.9.7) I got the
> following error:
> 
> |
> sqlalchemy.exc.DatabaseError:(DatabaseError)ORA-02443:Cannotdrop
> constraint  -nonexistent constraint
>  'ALTER TABLE t_message DROP CONSTRAINT None'{}
> |
> 
> 
> Your help will be appreciated...
> 
> -- 
> 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-alembic+unsubscr...@googlegroups.com
> <mailto:sqlalchemy-alembic+unsubscr...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

-- 
You received this message because you are subscribed to a topic in the Google 
Groups "sqlalchemy-alembic" group.
To unsubscribe from this topic, visit 
https://groups.google.com/d/topic/sqlalchemy-alembic/OPctuividc0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to 
sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

-- 
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-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: Alter boolean column on Oracle

[Mike Bayer]

On 12/10/2015 08:39 AM, Ofir Herzas wrote:
> I'm trying to run an alter_column operation from a Boolean type column
> to SmallInteger on Oracle.
> 
> According to the documentation: "...Type changes which are against the
> SQLAlchemy “schema” types |Boolean|
> 
>  and |Enum|
>  
> may
> also add or drop constraints which accompany those types on backends
> that don’t support them natively. The |existing_server_default| argument
> is used in this case as well to remove a previous constraint"

OK that document is wrong.  At least in 0.8 and maybe previously, that
should be "existing_type", because the current source code is looking at
existing_type to determine what previous type-bound constraints need to
be dropped.  I've committed that and it should be on readthedocs shortly.


> 
> Does this mean that the alter column should work out-of-the-box or do I
> have to remove the constraint myself because I'm getting the following
> error on alembic 0.8.3 (sqlalchemy 1.0.9):

Well, the constraint needs to have a name.  At least in the
existing_type that you send, for Boolean you'd need to also include the
"name" field that is the name of the constraint.

If this is an existing production DB, the constraint already has some
name and on Oracle it will be something like SYS12345.  But
unfortunately that name is not portable so this depends on if you are
targeting a lot of databases, or just one.

If you're just working out how these migrations would run from start to
finish, the real way you'd want to address this is at the very least
ensuring that any Boolean or Enum type you use where you've also used
create_constraint=True (the default) also has a "name".If you want
that to be automatic, you can use naming conventions.  The whole story
here is documented at http://alembic.readthedocs.org/en/latest/naming.html.

In the worst case, you in fact have production DBs, there are multiple,
you didn't give the constraints a specific name up front, and this
constraint probably has a different name on each one.Your options
there are either to manually rename constraints to something predictable
on these DBs and then use the full naming approach, *or* you'd need to
write custom rules in your migrations that do a live search of the
Oracle catalog tables, locate these constraints dynamically, and drops them.





> |
> sqlalchemy.exc.CompileError:Can't emit DROP CONSTRAINT for constraint
> CheckConstraint( 0x28a9b10>, name='_unnamed_', table=Table('t_message',
> MetaData(bind=None), Column('is_bulletin', Boolean(),
> table=), schema=None),
> _create_rule= at 0x289ac68>, _type_bound=True); it has no name
> |
> 
> 
> And on a previous version (alembic 0.6.5, sqlalchemy 0.9.7) I got the
> following error:
> 
> |
> sqlalchemy.exc.DatabaseError:(DatabaseError)ORA-02443:Cannotdrop
> constraint  -nonexistent constraint
>  'ALTER TABLE t_message DROP CONSTRAINT None'{}
> |
> 
> 
> Your help will be appreciated...
> 
> -- 
> 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-alembic+unsubscr...@googlegroups.com
> .
> For more options, visit https://groups.google.com/d/optout.

-- 
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-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Alter boolean column on Oracle

[Ofir Herzas]

I'm trying to run an alter_column operation from a Boolean type column to 
SmallInteger on Oracle.

According to the documentation: "...Type changes which are against the 
SQLAlchemy “schema” types Boolean 

 and Enum 
 
may 
also add or drop constraints which accompany those types on backends that 
don’t support them natively. The existing_server_default argument is used 
in this case as well to remove a previous constraint"

Does this mean that the alter column should work out-of-the-box or do I 
have to remove the constraint myself because I'm getting the following 
error on alembic 0.8.3 (sqlalchemy 1.0.9):

sqlalchemy.exc.CompileError: Can't emit DROP CONSTRAINT for constraint 
CheckConstraint(, name='_unnamed_', table=Table('t_message', MetaData(bind=None), 
Column('is_bulletin', Boolean(), table=), schema=None), 
_create_rule=, _type_bound=True); it has no name


And on a previous version (alembic 0.6.5, sqlalchemy 0.9.7) I got the 
following error:

sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-02443: Cannot drop 
constraint  - nonexistent constraint
 'ALTER TABLE t_message DROP CONSTRAINT None' {}


Your help will be appreciated...

-- 
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-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.