Re: many to many relation with foreign key to composite primary key
Hi Mike,
Thank you for the time you gave me.
The other problem I had to do was that my models were distributed in
different files. Alembic could not access their class and therefore could
not auto-generate his file.
Best regards,
Le jeu. 13 déc. 2018 à 19:48, Mike Bayer a
écrit :
> I can't reproduce any problem.
>
> Filling out your mappings from your first email, these look like:
>
> from sqlalchemy import *
> from sqlalchemy.orm import relationship
> from datetime import datetime
>
> from sqlalchemy.ext.declarative import declarative_base as db
> target_metadata = MetaData()
>
> DeclarativeBase = db(metadata=target_metadata)
>
> users_roles = Table(
> 'users_roles', target_metadata,
> Column('users_id', ForeignKey('users.id'), primary_key=True),
> Column('roles_id', ForeignKey('roles.id'), primary_key=True),
> )
>
> roles_permissions = Table(
> 'roles_permissions', target_metadata,
> Column('permissions_id', ForeignKey('permissions.id'),
> primary_key=True),
> Column('roles_id', ForeignKey('roles.id'), primary_key=True),
> )
> users_permissions = Table(
> 'users_permissions', target_metadata,
> Column('users_id', ForeignKey('users.id'), primary_key=True),
> Column('permissions_id', ForeignKey('permissions.id'),
> primary_key=True),
> )
>
>
> 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,
> onupdate=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 "" %
> (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 "" % 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 "" %
> (self.name, self.description)
>
>
> then i run autogenerate, migrations are generated as:
>
> def upgrade():
> # ### commands auto generated by Alembic - please adjust! ###
> op.create_table('permissions',
> sa.Column('id', sa.BigInteger(), nullable=False),
> sa.Column('name', sa.String(length=100), nullable=False),
> sa.Column('description', sa.String(length=255), nullable=True),
> sa.PrimaryKeyConstraint('id'),
> sa.UniqueConstraint('name')
> )
> op.create_table('roles',
> sa.Column('id', sa.BigInteger(), nullable=False),
> sa.Column('name', sa.String(length=100), nullable=False),
> sa.PrimaryKeyConstraint('id'),
> sa.UniqueConstraint('name')
> )
> op.create_table('users',
> sa.Column('id', sa.BigInteger(), nullable=False),
> sa.Column('email', sa.String(length=100), nullable=False),
> sa.Column('name', sa.String(length=100), nullable=False),
> sa.Column('hashed_password', sa.String(length=100), nullable=False),
> sa.Column('is_admin', sa.BOOLEAN(), nullable=True),
> sa.Column('is_active', sa.BOOLEAN(), nullable=True),
> sa.Column('created', sa.DateTime(), nullable=True),
> sa.Column('modified', sa.DateTime(), nullable=True),
> sa.PrimaryKeyConstraint('id'),
> sa.UniqueConstraint('email')
> )
> op.create_table('roles_permissions',
> sa.Column('permissions_id', sa.BigInteger(), nullable=False),
> sa.Column('roles_id', sa.BigInteger(), nullable=False),
> sa.ForeignKeyConstraint(['permissions_id'], ['permissions.id'], ),
> sa.ForeignKeyConstraint(['roles_id'], ['roles.id'], ),
> sa.PrimaryKeyConstraint('permissions_id', 'roles_id')
> )
>
Re: many to many relation with foreign key to composite primary key
I can't reproduce any problem.
Filling out your mappings from your first email, these look like:
from sqlalchemy import *
from sqlalchemy.orm import relationship
from datetime import datetime
from sqlalchemy.ext.declarative import declarative_base as db
target_metadata = MetaData()
DeclarativeBase = db(metadata=target_metadata)
users_roles = Table(
'users_roles', target_metadata,
Column('users_id', ForeignKey('users.id'), primary_key=True),
Column('roles_id', ForeignKey('roles.id'), primary_key=True),
)
roles_permissions = Table(
'roles_permissions', target_metadata,
Column('permissions_id', ForeignKey('permissions.id'), primary_key=True),
Column('roles_id', ForeignKey('roles.id'), primary_key=True),
)
users_permissions = Table(
'users_permissions', target_metadata,
Column('users_id', ForeignKey('users.id'), primary_key=True),
Column('permissions_id', ForeignKey('permissions.id'), primary_key=True),
)
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, onupdate=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 "" %
(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 "" % 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 "" %
(self.name, self.description)
then i run autogenerate, migrations are generated as:
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('permissions',
sa.Column('id', sa.BigInteger(), nullable=False),
sa.Column('name', sa.String(length=100), nullable=False),
sa.Column('description', sa.String(length=255), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name')
)
op.create_table('roles',
sa.Column('id', sa.BigInteger(), nullable=False),
sa.Column('name', sa.String(length=100), nullable=False),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name')
)
op.create_table('users',
sa.Column('id', sa.BigInteger(), nullable=False),
sa.Column('email', sa.String(length=100), nullable=False),
sa.Column('name', sa.String(length=100), nullable=False),
sa.Column('hashed_password', sa.String(length=100), nullable=False),
sa.Column('is_admin', sa.BOOLEAN(), nullable=True),
sa.Column('is_active', sa.BOOLEAN(), nullable=True),
sa.Column('created', sa.DateTime(), nullable=True),
sa.Column('modified', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('email')
)
op.create_table('roles_permissions',
sa.Column('permissions_id', sa.BigInteger(), nullable=False),
sa.Column('roles_id', sa.BigInteger(), nullable=False),
sa.ForeignKeyConstraint(['permissions_id'], ['permissions.id'], ),
sa.ForeignKeyConstraint(['roles_id'], ['roles.id'], ),
sa.PrimaryKeyConstraint('permissions_id', 'roles_id')
)
op.create_table('users_permissions',
sa.Column('users_id', sa.BigInteger(), nullable=False),
sa.Column('permissions_id', sa.BigInteger(), nullable=False),
sa.ForeignKeyConstraint(['permissions_id'], ['permissions.id'], ),
sa.ForeignKeyConstraint(['users_id'], ['users.id'], ),
sa.PrimaryKeyConstraint('users_id', 'permissions_id')
)
op.create_table('users_roles',
sa.Column('users_id', sa.BigInteger(), nullable=False),
sa.Column('roles_id', sa.BigInteger(), nullable=False),
Re: many to many relation with foreign key to composite primary key
Hi Mike, The message has been truncated. The CREATE TABLE statements is : CREATE TABLE users ( id BIGSERIAL NOT NULL, email VARCHAR(100) NOT NULL, name VARCHAR(100) NOT NULL, hashed_password VARCHAR(100) NOT NULL, is_admin BOOLEAN, is_active BOOLEAN, created TIMESTAMP WITHOUT TIME ZONE, modified TIMESTAMP WITHOUT TIME ZONE, 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 à 18:12, patrick payet a écrit : > 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 a > écrit : > >> On Thu, Dec 13, 2018 at 7:11 AM patrick payet 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.
Re: many to many relation with foreign key to composite primary key
On Thu, Dec 13, 2018 at 7:11 AM patrick payet 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 a écrit :
>>
>> On Wed, Dec 12, 2018 at 1:01 PM patrick payet wrote:
>> >
>> > I had a SQLAlchemy model like -
>> >
>> > 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 "" %
>> > (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 "" % 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 "" % (self.name,
>> > self.description)
>> >
>> > However, Alembic is not generating the correct upgrade, i try to make it
>> >
>> >
>> > 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')
>>
Re: many to many relation with foreign key to composite primary key
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 a
écrit :
> On Wed, Dec 12, 2018 at 1:01 PM patrick payet wrote:
> >
> > I had a SQLAlchemy model like -
> >
> > 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 "" % (
> 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 "" % 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 "" % (self.name,
> self.description)
> >
> > However, Alembic is not generating the correct upgrade, i try to make it
> >
> >
> > 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'],
>
Re: many to many relation with foreign key to composite primary key
On Wed, Dec 12, 2018 at 1:01 PM patrick payet wrote:
>
> I had a SQLAlchemy model like -
>
> 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 "" %
> (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 "" % 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 "" % (self.name,
> self.description)
>
> However, Alembic is not generating the correct upgrade, i try to make it
>
>
> 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,
>
