Mike -
Thanks for the response (and the all work you've put towards SQLalchemy
and Alembic). I've played around with the reflect_args and that didn't
seem to have much affect. When I remove those, the CHECK constraints
become unnamed. Regardless, I'll continue to mess around with it and see
if I can get it working. If I still have problems, I'll bug you again.
Worse case scenario, I'll consider migrating to a database that supports
the ALTER. I planned on doing it at some point in the future anyway and
it might make a few things easier anyway.
Thanks again for all the help.
On 4/12/17 8:26 AM, mike bayer wrote:
this area of batch migrations is not well developed, has to deal with
some impossible situations due to SQLite's poor design (allowing unnamed
constraints, not to mention that they refuse to implement ALTER), and is
also in flux (as Alembic is relying upon a buggy behavior in SQLAlchemy
w.r.t the naming convention feature here).
IIRC the naming convention linkage with batch migrations only has to do
with constraints that are unnamed when reflected from the database. That
might also be buggy.
I do notice that you have some reflect_args set up to try helping it
with the Boolean datatype and that might be throwing things off, not
sure. Or, you can add the constraint_name argument to each of those
Boolean types which would hopefully force it to be the right name (see
http://alembic.zzzcomputing.com/en/latest/batch.html#changing-the-type-of-boolean-enum-and-other-implicit-check-datatypes)
Current SQLAlchemy (1.1) *will* reflect CHECK constraints on SQLite, the
alembic docs are out of date on this. In theory, your naming
convention should have no impact because these constraints are coming
back with names already. I'd confirm that these names include the
naming convention already in the database, then try running without any
of the reflect_args added. in theory these boolean datatypes /
constraints should work without any intervention.
for further help I'd need to build this up here and there are likely
more bugs on the Alembic side to be fixed.
On 04/11/2017 10:58 PM, Peter Erickson wrote:
I'm sure that I'm missing a subtle point with batch migrations and
naming conventions for SQLite databases, but I can't figure out what I'm
doing wrong. After renaming a table, I'm using a batch migration to
update a foreign key in a child table. However, after recreating the
child table 2 CHECK constraints (being created from a Boolean) aren't
being named according to the specified naming convention.
To ensure that this post doesn't get too long, here are the parts I
think are relevant. I can add more if needed.
naming_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"
}
class RulesetMatch(Base):
__tablename__ = 'ruleset_matches'
...
viewed = Column(Boolean(name='view_bool'), default=False)
hide = Column(Boolean(name='hide'), default=False)
# sqlite3 data-dev.sqlite .schema
CREATE TABLE ruleset_matches (
...
viewed BOOLEAN,
hide BOOLEAN,
CONSTRAINT pk_ruleset_matches PRIMARY KEY (id),
CONSTRAINT ck_ruleset_matches_view_bool CHECK (viewed IN (0, 1)),
CONSTRAINT ck_ruleset_matches_hide CHECK (hide IN (0, 1))
);
# migration script
def upgrade():
with op.batch_alter_table(
'ruleset_matches',
naming_convention=naming_convention,
reflect_args=[
Column('viewed', Boolean(name='view_bool')),
Column('hide', Boolean(name='hide'))]) as batch_op:
# drop_constraint
# create_foreign_key
# sqlite3 data-dev.sqlite .schema
CREATE TABLE IF NOT EXISTS "ruleset_matches" (
...
viewed BOOLEAN,
hide BOOLEAN,
CONSTRAINT pk_ruleset_matches PRIMARY KEY (id),
CONSTRAINT view_bool CHECK (viewed IN (0, 1)),
CONSTRAINT hide CHECK (hide IN (0, 1)),
...
);
As shown in the last schema, the CHECKS are named "view_bool" and "hide"
unlike what it was previously "ck_ruleset_matches_view_bool" and
"ck_ruleset_matches_hide." If I remove the "name" attribute in
reflect_args or remove reflect_args all together, the CHECK constraints
are unnamed. Removing the naming_convention doesn't seem to affect
anything. What do I need to do to ensure that the constraints are named
appropriately?
alembic 0.9.1
sqlalchemy 1.1.9
Thanks in advance.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
