On Wed, Dec 12, 2018 at 1:01 PM patrick payet <papa...@gmail.com> wrote:
>
> I had a SQLAlchemy model like -
> <code>
> class User(DeclarativeBase):
> __tablename__ = 'users'
>
> id = Column(BigInteger, primary_key=True)
> email = Column(String(100), unique=True, nullable=False)
> name = Column(String(100), nullable=False)
> hashed_password = Column(String(100), nullable=False)
> is_admin = Column(BOOLEAN, default=False)
> is_active = Column(BOOLEAN, default=True)
> created = Column(DateTime, default=datetime.now)
> modified = Column(DateTime, default=datetime.now,
> onpudate=datetime.datetime.now)
> roles = relationship('Role', secondary=users_roles,
> back_populates='users', cascade="all, delete-orphan")
> permissions = relationship('Permission', secondary=users_permissions,
> back_populates='users',
> cascade="all, delete-orphan")
>
> def __repr__(self):
> return "<User(name='%s', email='%s', hashed_password='%s')>" %
> (self.name, self.email, self.hashed_password)
>
>
> class Role(DeclarativeBase):
> __tablename__ = 'roles'
>
> id = Column(BigInteger, primary_key=True)
> name = Column(String(100), unique=True, nullable=False)
> users = relationship('User', secondary=users_roles,
> back_populates='roles',cascade="all, delete-orphan")
> permissions = relationship('Permission', secondary=roles_permissions,
> back_populates='roles',
> cascade="all, delete-orphan")
>
> def __repr__(self):
> return "<Role(name='%s')>" % self.name
>
>
> class Permission(DeclarativeBase):
> __tablename__ = 'permissions'
>
> id = Column(BigInteger, primary_key=True)
> name = Column(String(100), unique=True, nullable=False)
> description = Column(String(255))
> users = relationship('User', secondary=users_permissions,
> back_populates='permissions',
> cascade="all, delete-orphan")
> roles = relationship('Role', secondary=roles_permissions,
> back_populates='permissions',
> cascade="all, delete-orphan")
>
> def __repr__(self):
> return "<Permission(name='%s', description='%s')>" % (self.name,
> self.description)
> </code>
> However, Alembic is not generating the correct upgrade, i try to make it
> <code>
>
> def upgrade():
> # ### commands auto generated by Alembic - please adjust! ###
> # op.drop_table('users')
> # ### end Alembic commands ###
>
> op.create_table('roles',
> sa.Column('id', sa.BIGINT(), autoincrement=True,
> nullable=False),
> sa.Column('name', sa.String(100), autoincrement=False,
> nullable=False),
> sa.Column('users', BigInteger),
> sa.Column('permissions', BigInteger),
> sa.PrimaryKeyConstraint('id'),
> sa.UniqueConstraint('name', name='roles_name_key')
> # sa.ForeignKeyConstraint(['users'],
> ['users_roles.users_id'], ondelete='CASCADE'),
> # sa.ForeignKeyConstraint(['permissions'],
> ['roles_permissions.permissions_id'], ondelete='CASCADE')
> )
> op.create_table('permissions',
> sa.Column('id', sa.BIGINT(), autoincrement=True,
> nullable=False),
> sa.Column('name', sa.String(100), autoincrement=False,
> nullable=False),
> sa.Column('description', sa.String(255)),
> sa.Column('users', BigInteger),
> sa.Column('roles', BigInteger),
> sa.PrimaryKeyConstraint('id')
> # sa.ForeignKeyConstraint(['users'],
> ['users_permissions.users_id'], ondelete='CASCADE'),
> # sa.ForeignKeyConstraint(['roles'],
> ['role_permissions.roles_id'], ondelete='CASCADE')
> )
> op.create_table('users_roles',
> sa.Column('users_id', BigInteger,
> sa.ForeignKey('users.id'), primary_key=True),
> sa.Column('roles_id', BigInteger,
> sa.ForeignKey('roles.id'), primary_key=True)
> )
> op.create_table('users_permissions',
> sa.Column('users_id', BigInteger,
> sa.ForeignKey('users.id'), primary_key=True),
> sa.Column('permissions_id', BigInteger,
> sa.ForeignKey('permissions.id'), primary_key=True)
> )
> op.create_table('roles_permissions',
> sa.Column('roles_id', BigInteger,
> sa.ForeignKey('roles.id'), primary_key=True),
> sa.Column('permissions_id', BigInteger,
> sa.ForeignKey('permissions.id'), primary_key=True)
> )
> op.drop_constraint('users_name_key', 'users')
> op.add_column('users', sa.Column('is_admin', sa.BOOLEAN,
> autoincrement=False, nullable=False, default=False))
> op.add_column('users', sa.Column('is_active', sa.BOOLEAN,
> autoincrement=False, nullable=False, default=True))
> op.add_column('users', sa.Column('created', sa.DateTime,
> autoincrement=False, nullable=False,
> default=datetime.datetime.now))
> op.add_column('users', sa.Column('modified', sa.DateTime,
> autoincrement=False, nullable=False,
> default=datetime.datetime.now))
> op.add_column('users', sa.Column('roles', BigInteger))
> op.add_column('users', sa.Column('permissions', BigInteger))
>
> op.create_foreign_key(constraint_name="users_roles_fk",
> source_table="users", referent_table="users_roles",
> local_cols=["roles"], remote_cols=["roles_id"],
> ondelete='CASCADE')
> op.create_foreign_key("users_permissions_fk", "users",
> "users_permissions", ["permissions"], ["permissions_id"],
> ondelete='CASCADE')
> op.create_foreign_key("permissions_users_fk", "permissions",
> "users_permissions", ["users"], ["users_id"],
> ondelete='CASCADE')
> op.create_foreign_key("permissions_roles_fk", "permissions",
> "roles_permissions", ["roles"], ["roles_id"],
> ondelete='CASCADE')
> op.create_foreign_key("roles_users_fk", "roles", "users_roles",
> ["users"], ["users_id"],
> ondelete='CASCADE')
> op.create_foreign_key("roles_permissions_fk", "roles",
> "roles_permissions", ["permissions"], ["permissions_id"],
> ondelete='CASCADE')
> </code>
> i have an error, primary key for users_roles is a composite keys and i dont
> know how declare it, how make that
it's not clear here if you're saying alembic is adding foreign keys
where they dont belong during autogenerate, or if you dont know why
you are getting the error you are getting. The error is because you
don't need FOREIGN KEY constraints on roles, permissions, or users -
the FOREIGN KEY is only on the many-to-many tables, in this case
users_roles, users_permissions, roles_permissions.
otherwise if you can illustrate the exact CREATE TABLE statements
you'd like to see, I can illustrate a correct model for that which
Alembic will generate.
> <code>
> sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) there is no
> unique constraint matching given keys for referenced table "users_roles"
> [SQL: 'ALTER TABLE users ADD CONSTRAINT users_roles_fk FOREIGN KEY(roles)
> REFERENCES users_roles (roles_id) ON DELETE CASCADE'] (Background on this
> error at: http://sqlalche.me/e/f405)
> </code>
>
> --
> 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.
