On Thu, Dec 13, 2018 at 7:11 AM patrick payet <papa...@gmail.com> wrote:
>
> 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.
the link is the user_roles table and the FOREIGN KEY constraints it
has between users and roles tables. you can't locate a unique row
in user_roles given only the user table because it has no column that
refers to user_roles.roles_id. at least I'm trying to parse your
words as given. however CREATE TABLE statements will show exactly
what you mean in case it is something different that just happens to
sound the same when described.
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.
yup
> 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.
--
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.
