On 11 Apr 2015, at 01:00, Mike Bayer <mike...@zzzcomputing.com> wrote:
> > > On 4/10/15 4:55 PM, Giovanni Torres wrote: >> >> >> CREATE TABLE foobar ( >> id INTEGER NOT NULL AUTO_INCREMENT, >> CONSTRAINT pk_foobar PRIMARY KEY (id) >> ) >> >> need a test case illustrating the failure. >> The main problem with this is that I can’t see a way to modify primary keys >> with alembic in a way that works with MySQL and Postgres. >> >> MySQL lets you create a table with a proper primary key name, but then you >> can’t use the name. See >> http://dev.mysql.com/doc/refman/5.5/en/create-table.html, specifically: >> >> "A PRIMARY KEY is a unique index where all key columns must be defined as >> NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares >> them so implicitly (and silently). A table can have only one PRIMARY KEY. >> The name of a PRIMARY KEY is always PRIMARY, which thus cannot be used as >> the name for any other kind of index.” >> >> Therefore, if I have a table as follows, which I then try to modify the >> primary key: >> >> t = Table('foobar', metadata, >> Column('id', Integer, primary_key=True), >> Column('foo', Integer) >> ) >> >> An Alembic script would look like this *for MySQL*: >> >> def upgrade(): >> op.drop_constraint('pk_foobar', ‘foobar’, type_=‘primary') >> op.create_primary_key(None, 'foobar', ['id', 'foo']) >> >> However, it doesn’t work. I get: (1075, 'Incorrect table definition; there >> can be only one auto column and it must be defined as a key') 'ALTER TABLE >> foobar DROP PRIMARY KEY ' () >> >> alembic —sql upgrade 675e5c38a0b:head shows: >> >> -- Running upgrade 675e5c38a0b -> c37885f5cff >> >> ALTER TABLE foobar DROP PRIMARY KEY; >> >> ALTER TABLE foobar ADD PRIMARY KEY (id, foo); >> >> UPDATE alembic_version SET version_num='c37885f5cff' WHERE >> alembic_version.version_num = '675e5c38a0b’; >> >> Then, I proceed to do it as follows: >> >> def upgrade(): >> op.execute('ALTER TABLE foobar DROP PRIMARY KEY, ADD PRIMARY KEY (id, >> foo)’) >> >> Which works OK, however it doesn’t work in Postgres, it’s invalid SQL, to >> make it work in Postgres I would do: > well like so many openstack scripts I see you'd need to conditional this on > MySQL for now: > > if op.get_bind().name == "mysql": > op.execute(" ... ") > else: > op.drop_constraint(....) > op.create_primary_key(...) > > within Alembic we'd probably need a new op directive that does an in-place > alter of a PK in a backend-agnostic way. Thank you very much Mike. I think this pretty much covers all my doubts. > > > >> >> def upgrade(): >> op.drop_constraint('pk_foobar', 'foobar') >> op.create_primary_key(None, 'foobar', ['id', 'foo']) >> >> Which works very well, alembic —sql upgrade 675e5c38a0b:head shows: >> >> -- Running upgrade 675e5c38a0b -> c37885f5cff >> >> ALTER TABLE foobar DROP CONSTRAINT pk_foobar; >> >> ALTER TABLE foobar ADD CONSTRAINT pk_foobar PRIMARY KEY (id, foo); >> >> UPDATE alembic_version SET version_num='c37885f5cff' WHERE >> alembic_version.version_num = '675e5c38a0b'; >> >> COMMIT; >> >> To conclude, don’t know how to make it work with MySQL and Postgres. >> >>>> o I also seem to be hitting a bug in sqlalchemy similar to this one: >>>> https://bitbucket.org/zzzeek/sqlalchemy/issue/3067/naming-convention-exception-for-boolean. >>>> I get this error: InvalidRequestError: Naming convention including >>>> %(constraint_name)s token requires that constraint is explicitly named. >>> Several approaches to this. >>> >>> The first is that you specify constraint_name for your Boolean type, using >>> the "name" parameter. >>> >>> Second, forego the use of a CHECK constraint with the Boolean type by using >>> create_constraint=False. >>> >>> Third, instead of using %(constraint_name)s in your CHECK constraint, you >>> use %(column_0_name)s. Barbican will have to upgrade to SQLAlchemy 1.0 >>> for this, but the good news is that SQLA 1.0 will be released before the >>> Vancouver summit and you can invite me to a Barbican session there in order >>> to start selling this. >> Thanks for the offer! However, this is a bit over my head at the moment. I’m >> just trying to get my first commit in. But, hopefully the code review >> generates some discussion and I could try to push it there. I also know one >> of the core members, which might help. >> >>> Fourth, essentially emulate 1.0's behavior by removing "ck_" from the >>> naming convention and using a straight "after_parent_attach" event to set >>> up the name; this is how naming conventions were done before the feature >>> was added. This is illustrated here: >>> https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/NamingConventions >>> . This is also a potential approach to the foreign key issue as well. >> Thanks, this seems to me the way to go. I could fix the constraint_name >> issue and the 64 character limit in one go. >> >>> I'm zzzeek on #openstack-dev if you want to chat further! >> Thanks for all the help! >> >>>> All this is compounded by the fact that we're also trying to support >>>> Postgres (which works very well by way), so we can't only cater to MySQL's >>>> nuisances. >>>> >>>> The bug I'm trying to fix is in a future OpenStack component: >>>> https://bugs.launchpad.net/barbican/+bug/1415869 >>>> >>>> Any suggestion or shared experiences about how to deal with any of these >>>> issues is welcome! >>>> >>>> -- >>>> Giovanni -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
