Hi Mike,
Thanks for your answer.
My problem it’s a classical problem for application development, I want to
make authentication and authorisation for a web application.
I want to understand why Alembic can’t create the model in autogeneration
mode and how modify this model to resolve this problem.
With this model I have a join table to provide a many-to-many relationship
between users and roles (users group) for example.
This allows me to access all roles for one user. For this, I declare a
variable roles in users class and I want to make a foreign key with this
join table
to reach the roles.
This joins table has a composite primary key (users.id foreign key and
roles.id foreign key) and I don’t know how to make the link (foreign key)
with alembic for the users.roles and this composite primary key. When I
declare just one element of this primary key, I have the error quoted
previously ( there is no unique constraint matching given keys for
referenced table "users_roles").
I will send you the CREATE TABLE statements by email in a moment.
Best regards,
Patrick
Le mer. 12 déc. 2018 à 19:40, Mike Bayer <mike...@zzzcomputing.com> a
écrit :
> 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.
>
--
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.
