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.
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.
