PRIMARY KEY (id),
UNIQUE (email)
)
CREATE TABLE permissions (
id BIGSERIAL NOT NULL,
name VARCHAR(100) NOT NULL,
description VARCHAR(255),
PRIMARY KEY (id),
UNIQUE (name)
)
CREATE TABLE roles (
id BIGSERIAL NOT NULL,
name VARCHAR(100) NOT NULL,
PRIMARY KEY (id),
UNIQUE (name)
)
CREATE TABLE users_roles (
users_id BIGINT NOT NULL,
roles_id BIGINT NOT NULL,
PRIMARY KEY (users_id, roles_id),
FOREIGN KEY(users_id) REFERENCES users (id),
FOREIGN KEY(roles_id) REFERENCES roles (id)
)
CREATE TABLE roles_permissions (
roles_id BIGINT NOT NULL,
permissions_id BIGINT NOT NULL,
PRIMARY KEY (roles_id, permissions_id),
FOREIGN KEY(roles_id) REFERENCES roles (id),
FOREIGN KEY(permissions_id) REFERENCES permissions (id)
)
CREATE TABLE users_permissions (
users_id BIGINT NOT NULL,
permissions_id BIGINT NOT NULL,
PRIMARY KEY (users_id, permissions_id),
FOREIGN KEY(users_id) REFERENCES users (id),
FOREIGN KEY(permissions_id) REFERENCES permissions (id)
)
My mistake is to have mixed in my mind the information of the object
mapping and the relational constraints of the database.
But why Alembic can’t create this model in auto-generation mode?
Best regards
Le jeu. 13 déc. 2018 à 14:44, Mike Bayer <mike...@zzzcomputing.com> a
écrit :
> 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.
>
--
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.
