On 10 Apr 2015, at 00:56, Mike Bayer <[email protected]> wrote:
> On 4/9/15 3:38 PM, Giovanni Torres wrote: >> >> Hello All! >> >> I'm facing the well known problem of maintaining constraints, when using >> sqlalchemy (v0.9.8), alembic (v0.7.4) and MySQL (v5.5.41) on Ubuntu >> (v14.04.2). >> >> I'm talking specifically about these two links: >> >> o >> http://docs.sqlalchemy.org/en/rel_0_9/core/constraints.html#constraint-naming-conventions >> >> o http://alembic.readthedocs.org/en/latest/naming.html >> >> The suggested naming conventions, do not work for me with MySQL and I was >> hoping to hear how others are dealing with the issues. The main problem is >> the following: >> >> o MySQL's 64 character limit on constrain names: >> https://bugs.mysql.com/bug.php?id=13942. I hit it when using the recommended >> foreign key naming convention: >> fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s > > For that issue, ultimately the naming convention feature should have some > more pluggability in that special tokens can be provided for such as a > truncated version of the naming convention. > > However, if you have just specific ForeignKey or ForeignKeyConstraint objects > that are hitting this limit, I recommend you give them a "name" explicitly > that is the name you want them to have, not following the convention. This will work, sounds good to me. I’ll try first using the “after_parent_attach” method and if I have issues with it, I’ll try this one then. >> o The fact the primary constraint in MySQL *is always* called PRIMARY, >> regardless of what you specify. > I'm not familiar with that issue. Given this test: > > from sqlalchemy import * > > convention = { > "ix": 'ix_%(column_0_label)s', > "uq": "uq_%(table_name)s_%(column_0_name)s", > "ck": "ck_%(table_name)s_%(constraint_name)s", > "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s", > "pk": "pk_%(table_name)s" > } > > metadata = MetaData(naming_convention=convention) > > t = Table('foobar', metadata, > Column('id', Integer, primary_key=True) > ) > > e = create_engine("mysql://scott:tiger@localhost/test", echo=True) > metadata.create_all(e) > > the output is: > > > 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: 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
